db diff reproduction

This repository reproduced the issue with Declarative db schemas in Supabase.

How to reproduce manually

1. There are two schema files in supabase/schemas, ordered with numerical prefixes:

• 00_function.sql - contains a user-defined function
• 01_table.sql - contains a table with check constraint using the above function

2. Generate a diff using npx supabase db diff -f initial

Creating local database from declarative schemas:
 • supabase/schemas/00_function.sql
 • supabase/schemas/01_table.sql
Initialising schema...
Seeding globals from roles.sql...
Seeding globals from 00_function.sql...
Seeding globals from 01_table.sql...
Connecting to local database...
Creating shadow database...
Initialising schema...
Seeding globals from roles.sql...
Diffing schemas: extensions,public
Finished supabase db diff on branch main.

WARNING: The diff tool is not foolproof, so you may need to manually rearrange and modify the generated migration.
Run supabase db reset to verify that the new migration does not generate errors.

3. The resulting migration file switches the order of those two source files:

• table definition comes before the function definition
• which makes it fail, because the function is not defined yet

4. Running npx supabase migration up fails with:

ERROR: function only_letters(text) does not exist (SQLSTATE 42883)
At statement 1:
alter table "public"."people" add constraint "people_name_check" CHECK (only_letters(name)) not valid

Warning

setting db.migrations.schema_paths in config.toml does not matter for this issue

Notes

Using --use-pg-schema flag does not really help, because **pg-schema-diff*8 does not support user defined functions in check constraints:

When running npx supabase@latest db diff -f initial --use-pg-schema, you get:

WARNING: --use-pg-schema flag is experimental and may not include all entities, such as RLS policies, enums, and grants.
Creating local database from declarative schemas:
 • supabase/schemas/00_function.sql
 • supabase/schemas/01_table.sql
Initialising schema...
Seeding globals from roles.sql...
Seeding globals from 00_function.sql...
Seeding globals from 01_table.sql...
Connecting to local database...
Creating shadow database...
Initialising schema...
Seeding globals from roles.sql...
Diffing schemas: extensions,public
failed to generate plan: generating plan statements: generating migration statements: resolving table diff: generating add statements for "public"."people": generating sql: generating add check constraint statements for check constraint people_name_check: check constraints that depend on UDFs: not implemented
Try rerunning the command with --debug to troubleshoot the error.