Bug: Remote migrations don't seem to include `create schema`

[ziad-saab]

Bug report

Describe the bug

Remote migrations don't seem to include create schema.

To Reproduce

1. Create a new project on app.supabase.io
2. Run the following SQL:

   CREATE SCHEMA myschema;
   CREATE FUNCTION myschema.myfunction() RETURNS boolean LANGUAGE plpgsql AS
   $$
   BEGIN
     RETURN true;
   END;
   $$
   

3. Create a new local project with supabase init
4. Run supabase db remote set <REMOTE_URL> -- grab the remote URL from your app.supabase.io dashboard
5. Run supabase db remote commit
6. Run supabase db reset

At this point you'll get the following error: Error: Error resetting database: ERROR: schema "myschema" does not exist

Expected behavior

The generated migration contains a CREATE SCHEMA myschema; command.

System information

• OS: macOS 12.0.1 (M1 Mac)
• Version of supabase-cli: 0.15.15

[soedirgo]

Thanks for the bug report. Unfortunately this is a limitation of the diff tool as it doesn't output schema DDL. The workaround is to add a CREATE SCHEMA my_schema; line after running supabase db remote commit - i.e. it will only be run on the local database and subsequent supabase db push.

[sweatybridge]

With cli v1, you can use supabase db diff —use-migra to generate schema DDL. Pass it —schema flag to include all schema you want to diff.

Feel free to open this again if it doesn’t work for you.