Error after running supabase db diff command

[RishabhPachori]

Describe the bug After running npx supabase start command I run npx supabase db diff

To Reproduce Steps to reproduce the behavior:

1. Run npx supabase db diff command

Expected behavior It should create the migrations

System information

• Version of OS: [e.g. Ubuntu 22.04]
• Version of CLI: Tried with 1.183.5 and also with beta version 1.186.2
• Version of Docker: 27.0.3

Error log: error diffing schema: error running container: exit 1: Traceback (most recent call last): File "/usr/local/bin/migra", line 8, in sys.exit(do_command()) File "/usr/local/lib/python3.9/site-packages/migra/command.py", line 121, in do_command status = run(args) File "/usr/local/lib/python3.9/site-packages/migra/command.py", line 98, in run m.add_all_changes(privileges=args.with_privileges) File "/usr/local/lib/python3.9/site-packages/migra/migra.py", line 107, in add_all_changes self.add(self.changes.tables_only_selectables()) File "/usr/local/lib/python3.9/site-packages/migra/changes.py", line 496, in get_selectable_changes statements += get_table_changes( File "/usr/local/lib/python3.9/site-packages/migra/changes.py", line 233, in get_table_changes rls_alter = v.alter_rls_statement File "/usr/local/lib/python3.9/site-packages/schemainspect/pg/obj.py", line 228, in alter_rls_statement return self.alter_table_statement(self.alter_rls_clause) File "/usr/local/lib/python3.9/site-packages/schemainspect/pg/obj.py", line 145, in alter_table_statement raise NotImplementedError

[RishabhPachori]

@sweatybridge

I am running supabase db diff command to create migrations Node js version : 18.17.0 Supabase cli version: Tried with 1.183.5 and also with beta version 1.186.2 docker version: 27.0.3

After running npx supabase start command I run npx supabase db diff

error diffing schema: error running container: exit 1: Traceback (most recent call last): File "/usr/local/bin/migra", line 8, in sys.exit(do_command()) File "/usr/local/lib/python3.9/site-packages/migra/command.py", line 121, in do_command status = run(args) File "/usr/local/lib/python3.9/site-packages/migra/command.py", line 98, in run m.add_all_changes(privileges=args.with_privileges) File "/usr/local/lib/python3.9/site-packages/migra/migra.py", line 107, in add_all_changes self.add(self.changes.tables_only_selectables()) File "/usr/local/lib/python3.9/site-packages/migra/changes.py", line 496, in get_selectable_changes statements += get_table_changes( File "/usr/local/lib/python3.9/site-packages/migra/changes.py", line 233, in get_table_changes rls_alter = v.alter_rls_statement File "/usr/local/lib/python3.9/site-packages/schemainspect/pg/obj.py", line 228, in alter_rls_statement return self.alter_table_statement(self.alter_rls_clause) File "/usr/local/lib/python3.9/site-packages/schemainspect/pg/obj.py", line 145, in alter_table_statement raise NotImplementedError

could you please help me to fix this issue ??

[RishabhPachori]

@sweatybridge

I understand what you mention why this issue occured

That problem is due to a limitation of migra tool which doesn't support generating alter rls statement.

Can you please tell me to resolve this issue ?? So, that i can create migrations successfully.

[RishabhPachori]

I have tried with use pg schema flag as well npx supabase db diff --db-url DB_URL --use-pg-schema

With this flag it is showing this error

ERROR: prepared statement "lrupsc_1_0" already exists

[sweatybridge]

ERROR: prepared statement "lrupsc_1_0" already exists

Can you double check your db url specifies port 5432? It cannot use port 6543 because that runs migrations in transaction mode.

[RishabhPachori]

ERROR: prepared statement "lrupsc_1_0" already exists

Can you double check your db url specifies port 5432? It cannot use port 6543 because that runs migrations in transaction mode.

Yes i have checked it. I am using port 5432 in the db url.

[RishabhPachori]

I have tried with use pg schema flag as well npx supabase db diff --db-url DB_URL --use-pg-schema

With this flag it is showing this error

ERROR: prepared statement "lrupsc_1_0" already exists

I have verified it again with --use-pg-schema it is showing this error failed to generate plan: generating plan statements: generating migration statements: resolving table sql graphs: generating SQL for add "public"."messages_2024_05_01_2024_05_31": policies on partitions: not implemented

This error is occured may be because i have created partition tables in my database.

[sweatybridge]

Ok, thanks. Could you try the following workaround instead

1. run supabase start (or skip this if it's already running or diffing against remote database)
2. comment out any create policy ... statements from your local migrations, ie. supabase/migrations/*.sql
3. run supabase db diff -f name

If step 3 succeeds, then you can manually edit the new migration file to change create policy ... to alter policy ..., and also undo the changes in step 2.

[RishabhPachori]

Ok, thanks. Could you try the following workaround instead

1. run supabase start (or skip this if it's already running or diffing against remote database)
2. comment out any create policy ... statements from your local migrations, ie. supabase/migrations/*.sql
3. run supabase db diff -f name

If step 3 succeeds, then you can manually edit the new migration file to change create policy ... to alter policy ..., and also undo the changes in step 2.

@sweatybridge I am creating setup of my database in local from scratch. So, there is no migrations at all in my local. I am diffing against for one of my project which is on supabase.com in local for the first time. There is no migration file in my local. How i am able to comment out any create policy ... statements ??

[sweatybridge]

I am creating setup of my database in local from scratch. So, there is no migrations at all in my local.

If that's the case, then the workaround is simpler.

supabase db dump --db-url '<...>' | supabase migrations new remote_schema

[RishabhPachori]

I am creating setup of my database in local from scratch. So, there is no migrations at all in my local.

If that's the case, then the workaround is simpler.

supabase db dump --db-url '<...>' | supabase migrations new remote_schema

Thanks @sweatybridge. This workaround is working for now.