"supabase db push" fails, generated migration contains schema "_analytics"

[stefan-girlich]

Bug report

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

Describe the bug

I am not able to push a local migration to my linked cloud instance. The migration file contains internal tables from the _analytics schema.

To Reproduce

1. Run supabase db diff -f change_purchase_id_type --use-migra (or without --use-migra)
2. Run supabase db push
3. See error, see created migration file

Expected behavior

• Generated migration file only contains changes from public schema (?)
• supabase db push succeeds

data

Commands and error

➜  backend git:(main) ✗ supabase db push
Connecting to remote database...
Do you want to push these migrations to the remote database?
 • 20240513093701_change_purchase_id_type.sql

 [Y/n] Y
Applying migration 20240513093701_change_purchase_id_type.sql...
ERROR: schema "_analytics" does not exist (SQLSTATE 3F000)
At statement 0: create sequence "_analytics"."billing_accounts_id_seq"
Try rerunning the command with --debug to troubleshoot the error.

Generated migration

migration.zip

• Incorrectly (?) contains ~800 lines of code for _analytics schema
• Correctly contains ~60 lines of code for public schema

System information

• OS: MacOS
• Version supabase CLI: 1.165.0
• Version of Node.js: v21.6.2

[encima]

Hey hey, thanks for opening!

By default, the migrations commands exclude system schemas but it can include them if it is run with -s. Was it ever run to include the _analytics schema or is this included consistently when you run commands like db dump or db pull?

I cannot reproduce but this does not mean it is not a bug! Just want to sanity check first that _analytics was never included. You can check this in your terminal history (ctrl+r) will allow you to search.

[stefan-girlich]

I only used -s public once with supabase db dump, but this did not affect migrations created afterwards. _analytics was never included explicitly. The migration just prior to the faulty one did not contain _analytics.

Happy to share a debug log if there is a way to exclude private/organization data.

[HoekWax]

Hello, I have the same issue, I created a new project online, then linked it to a local projet, pulled the db. I then enabled "analytics" in config.toml then ran "db diff" to create a migration file which should not contain anything because it's a brand new project with nothing added but it does contain the definitions for the table _analytics.

[stefan-girlich]

Just upgraded my local environment to v1.167.4 (CLI); issue still exists.
When I create a new migration (e.g. supabase db diff -f add_output_tables --use-migra), the log output explicitly says that the extra schemas are included:

Diffing schemas: _analytics,_realtime,extensions,public

When I add -s public, the log will state Diffing schemas: public, but the actual migration file will still contain the extra schemas.

Update: It now worked as expected using -s public, still failing without.

[SuperstrongBE]

I have the exact same issue today.
Thank @stefan-girlich for the workaround.
But internal schema should be excluded while creating migration.

[sweatybridge]

I've fixed this regression npx supabase@beta db diff. Apologies for the inconvenience.