Types generated by supabase db reset and supabase gen types are not consistent

[saint-james-fr]

Bug Description

Problem Summary

When running supabase db reset, I notice unexpected changes in the generated types file, even for columns that were not modified through migrations. This issue causes problems when comparing the generated types, as the differences seem random and inconsistent.

Specifically, the changes appear within a view that references the same table twice, in two Common Table Expressions (CTEs) or subqueries. The columns id from the table are ultimately combined into the view as initial_contract_id and last_contract_id.

Scenario

Imagine a view that aggregates contract data. The view handles two distinct contract types: the initial contract and the last contract, both originating from the same contract table. These contracts are differentiated by their id and reunited based on the contract_key.

Example of the Problem

The issue arises because the supabase gen types command generates different types on these columns even they're not expected - even when no direct changes have been made to the migrations or the underlying table structure. The position of the referencedColumns in the generated types file switches randomly between initial_contract_id and last_contract_id.

Steps to Reproduce

1. Run supabase db reset.
2. Run the command to regenerate the types:

   supabase gen types --lang=typescript --local --schema public,not_public,storage > ../../packages/shared/src/types/supabase.gen.ts

3. Compare the generated types file (supabase.gen.ts), and you'll see the random changes in the order of referencedColumns.

Example Diff:

diff --git a/packages/shared/src/types/supabase.gen.ts b/packages/shared/src/types/supabase.gen.ts
index 40d7ea2..85ac078 100644
--- a/packages/shared/src/types/supabase.gen.ts
+++ b/packages/shared/src/types/supabase.gen.ts
@@ export type Database = {
             columns: ["contract_id"]
             isOneToOne: false
             referencedRelation: "view_contract"
-            referencedColumns: ["last_contract_id"]
+            referencedColumns: ["initial_contract_id"]
           },
           {
             foreignKeyName: "advance_payment_request_contract_id_fkey"
             columns: ["contract_id"]
             isOneToOne: false
             referencedRelation: "view_contract"
-            referencedColumns: ["initial_contract_id"]
+            referencedColumns: ["last_contract_id"]
           },
           {
             foreignKeyName: "advance_payment_request_contract_id_fkey"
@@ -2744,14 +2744,14 @@ export type Database = {
             columns: ["contract_id"]
             isOneToOne: false
             referencedRelation: "view_contract"
-            referencedColumns: ["last_contract_id"]
+            referencedColumns: ["initial_contract_id"]
           },
# Some more similar changes...

Expected Behavior

• Consistent Generated Types: Running supabase db reset or supabase gen types should not introduce random changes in the generated types file. Specifically, the referencedColumns should not switch between initial_contract_id and last_contract_id unless expected.
• Stable Type Generation: The generated types should reflect the actual changes made in the migrations and should not be affected by views or CTEs that don't have any changes in the underlying database schema.

Environment

• Supabase CLI Version: 1.210.1
• Docker Version: 27.2.0, build 3ab4256
• Supabase/Postgres Version: 15.1.1.78
• Supabase/Gotrue Version: v2.158.1
• Supabase/PostgREST Version: v12.2.0
• Supabase/Realtime Version: v2.30.34
• Supabase/Storage API Version: v1.11.13
• Supabase/Edge Runtime Version: v1.59.0
• Supabase/Studio Version: 20241014-c083b3b
• Supabase/Postgres-Meta Version: v0.84.2
• Supabase/Logflare Version: 1.4.0
• Supabase/Supavisor Version: 1.1.56
• @supabase/supabase-js: 2.45.6

Additional Information

Let me know if you need further explanations or examples, as it's quite difficult to clearly explain the issue in writing. The key point is that the random switching between initial_contract_id and last_contract_id in the generated types seems unrelated to actual database schema changes.

[saint-james-fr]

I think part of the solution is here. 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)}
                      }`
                      )}
                  ]
                }`
                  )
            }
          }

[kbsali]

We are having the same issue and it is indeed very annoying
@saint-james-fr how about opening a PR with your proposal?

[saint-james-fr]

@kbsali thanks for motivating me, it's opened here.

[kbsali]

👏 Great! Thanks a lot!! :)

[soedirgo]

Fixed in #903