Migration not being applied and no error is being logged

[jaredramirez]

trafficstars

Bug report

Describe the bug

We currently have an existing Supabase project and we're interested in using the supabase CLI to improve our development environment. To get the local db migrations setup to match the existing prod environment I've:

1. pg_dump --schema-only the current prod DB
2. Copied this output to the supabase/migrations/xxxx_init.sql
3. Prepended a handful of create extension xxx at the top of the dump
4. Run supabase start to setup the DB and run migrations
5. Now here's the problem, when I connect to the local DB the migration hasn't been run and none of the DB objects have been created.

To Reproduce

I'm not sure what part of the PG dump is the problem. If I simplify the migration file to a simple CREATE TABLE, supabase start works as expected. I'm not even sure where to begin to figure out what in the dump is causing the problem, as it just over 10K lines long.

Expected behavior

The migration should be applied OR if it fails an error should be logged.

Interestingly, if I replace the migration file with invalid SQL (like ashtashtasht), running supabase start does log an error:

$ supabase start
Error: Error starting database: ERROR:  syntax error at or near "ashtashtasht"
LINE 1: ashtashtasht

Screenshots

System information

• OS: MacOS
• Version of CLI: v0.26.2

Additional context

Add any other context about the problem here.

[kiwicopple]

hey @jaredramirez - did you try the db remote set command? That should handle the initial migrations set up. Some docs here:

https://supabase.com/docs/guides/local-development#linking-your-project

[jaredramirez]

Hey good to know, I somehow missed the line You'll notice that supabase/migrations is now populated with a migration in ..._remote_commit.sql. This migration captures any changes required for your local database to match the schema of your remote Supabase project. when reading the docs.

Unfortunately, the file generated has the same result as the pg_dump file. Interestingly, in our DB we use many different schemas but the file generated by db remote commit doesn't include any CREATE SCHEMA lines. I would think this would cause an error but nothing is logged when running start

[sweatybridge]

Interestingly, in our DB we use many different schemas but the file generated by db remote commit doesn't include any CREATE SCHEMA lines.

I believe this is a similar issue as #136.

In cli v1, we have updated the db remote commit command to better handle the initial schema dump.

Appreciate your feedback on whether this new change works.

[sweatybridge]

file generated by db remote commit doesn't include any CREATE SCHEMA lines

I have checked in v1.5.4 that schemas are now included the output of db remote commit.

--
-- Name: test_schema; Type: SCHEMA; Schema: -; Owner: supabase_admin
--

CREATE SCHEMA "test_schema";


ALTER SCHEMA "test_schema" OWNER TO "supabase_admin";

Feel free to reopen this if you run into further issues with the CLI.