Bug: types.gen generated without any modification in database because view references twice same table

[littleannie]

Bug report

• [ X] I confirm this is a bug with Supabase, not with my own application.
• [X ] I confirm I have searched the Docs, GitHub Discussions, and Discord.

Describe the bug

Hi there,

We have been experiencing a recurring issue since updating to Supabase version 1.183.5, specifically when generating the types.gen.ts file.

We have a view that references the same table twice but points to different records. Consequently, we have two distinct references.

When generating the types.gen.ts file, differences are created even though we have not made any modifications to our database schema.

We do not understand why the order is generated differently in a seemingly random manner. As a result, we are encountering numerous issues with our GitHub CI.

Please feel free to reach out if you need more information.

To Reproduce

1. create a view referencing twice the same table
2. Commit
3. generate types.gen many times without any db schema modification

Expected behavior

The types.gen should not be edited if no modification made on the db

Screenshots

System information

• OS: [e.g. macOS, Windows] : macOS
• Browser (if applies) [e.g. chrome, safari] : Chrome
• Version of supabase-js: [e.g. 6.0.2] : ^2.44.4
• Version of Node.js: [e.g. 10.10.0] : 20.16.0

Additional context

Re-open issue : supabase/supabase#28212

[Hallidayo]

Hi @littleannie - Thank you for opening this issue, Are you still experiencing this on the current version?

[saint-james-fr]

Hey there, had the same bug needs to test this on monday with newer version.

[Hallidayo]

Hi @saint-james-fr - Just checking if you had tested on the newer version?

[saint-james-fr]

hey @Hallidayo Just bumped to the 2.12.0 of CLI, let's see how it goes. Thanks

[saint-james-fr]

@Hallidayo Actually same error, this brings me back to my initial Issue on pg_meta
#831
This is very annoying and I can't figure out why this is changing.
We have a check step in our CI to see if types match the schema and this keeps changing randomly...

[saint-james-fr]

I've come to the conclusion that running supabase db reset on the same schema then supabase gen types does not produce the same output everytime.

[saint-james-fr]

@Hallidayo
I post this here as well. If i have like in my case a view referencing twice the same table with the same foreign key, the sort won't be deterministic so we need to add another sort to determine this

Relationships: [
                    ${relationships
                      .filter(
                        (relationship) =>
                          relationship.schema === table.schema &&
                          relationship.referenced_schema === table.schema &&
                          relationship.relation === table.name
                      )
                      .sort(
                        (a, b) => {
                          // First sort by foreign key name
                          const fkCompare = a.foreign_key_name.localeCompare(b.foreign_key_name);
                          if (fkCompare !== 0) return fkCompare;
                          
                          // Then by referenced relation
                          const relCompare = a.referenced_relation.localeCompare(b.referenced_relation);
                          if (relCompare !== 0) return relCompare;
                          
                          // Finally, ensure stable order of referenced columns by sorting alphabetically
                          return a.referenced_columns[0].localeCompare(b.referenced_columns[0]);
                        }
                      )
                      .map(
                        (relationship) => `{
                        foreignKeyName: ${JSON.stringify(relationship.foreign_key_name)}
                        columns: ${JSON.stringify(relationship.columns)}
                        ${
                          detectOneToOneRelationships
                            ? `isOneToOne: ${relationship.is_one_to_one};`
                            : ''
                        }referencedRelation: ${JSON.stringify(relationship.referenced_relation)}
                        referencedColumns: ${JSON.stringify(relationship.referenced_columns)}
                      }`
                      )}
                  ]
                }`
                  )
            }
          }

[saint-james-fr]

I've opened a PR on this subject.

[Hallidayo]

Hi @saint-james-fr , Thank you so much for the PR. I'll transfer this issue over to the postgres-meta repo but again thank you!

[soedirgo]

Fixed in #903