On table rename `supabase db pull` creates migration to DROP table, not RENAME

[d9k]

Actions

1. rename table in supabase admin panel

2. create get migration from remote

> supabase db pull                                                              
Connecting to remote database...                                                          
Setting up initial schema....                                                             
Applying migration 20231129030618_remote_schema.sql...                                    
Applying migration 20231129093821_remote_schema.sql...                                    
Applying migration 20231129114012_user_management_starter.sql...                          
Schema written to supabase/migrations/20231207142347_remote_schema.sql                    
Update remote migration history table? [Y/n] n                                            
Finished supabase db pull.

Result

auto-generated migration SQL code:

alter table "public"."town" drop constraint "town_country_id_fkey";

alter table "public"."country" drop constraint "country_pkey";

drop index if exists "public"."country_pkey";

drop table "public"."country";

create table "public"."countries" (
    "id" bigint generated by default as identity not null,
    "name" text,
    "created_at" timestamp with time zone not null default now(),
    "updated_at" timestamp with time zone default now(),
    "found_year" bigint,
    "next_rename_year" bigint
);


CREATE UNIQUE INDEX country_pkey ON public.countries USING btree (id);

alter table "public"."countries" add constraint "country_pkey" PRIMARY KEY using index "country_pkey";

alter table "public"."town" add constraint "town_country_id_fkey" FOREIGN KEY (country_id) REFERENCES countries(id) ON UPDATE CASCADE not valid;

alter table "public"."town" validate constraint "town_country_id_fkey";

Such migration will result in all information stored in table to be lost.

Why rename table https://www.geeksforgeeks.org/postgresql-rename-table/ statement was not used?!

Version

> supabase --version                              
1.113.3

[sweatybridge]

Another problem worth noting is that migra generates drop columns instead of alter table ... rename column. I will add a warning to cli when drop statements are generated so that users are aware of such issues.

[d9k]

@sweatybridge, thanks, but it doesn't solve an issue — just makes it more obvious. Why close?

[sweatybridge]

I closed it because the warning statement made it obvious that manual edits to the migration file is required in such cases.

Why rename table https://www.geeksforgeeks.org/postgresql-rename-table/ statement was not used?!

To answer your question above, schema diff cannot distinguish a table with dropped + added columns from one with renamed column. Consider these tables

before:

create table my_table(
  id bigserial PRIMARY KEY,
  name text
)

after:

create table my_table(
  id bigserial PRIMARY KEY,
  first_name text,
  last_name text
)

How can we know from inspecting pg_catalog whether the name column was renamed to first_name or last_name? What if users want another step in between like splitting the name field by spaces into first and last name?

Unless someone has a better suggestion, I'm inclined to close this as won't fix. If you are facing a lot of friction writing custom SQL statements for data migration, perhaps the AI SQL editor could help.

[d9k]

@sweatybridge, thanks for the answer! AI SQL editor is a good suggestion.