google/gemini-3.1-pro-preview · d2_live_migration · rep 3

env | grep SUPABASE

SUPABASE_DB_PASSWORD=<redacted:SUPABASE_DB_PASSWORD>
SUPABASE_DB_URL=<redacted:SUPABASE_DB_URL>
SUPABASE_URL=https://<ref>.supabase.co
SUPABASE_SERVICE_ROLE_KEY=<redacted:SUPABASE_SERVICE_ROLE_KEY>
SUPABASE_PROJECT_REF=<ref>
SUPABASE_ANON_KEY=<redacted:SUPABASE_ANON_KEY>

psql "$SUPABASE_DB_URL" -c "SELECT count(*) FROM public.orders;"

 count 
-------
   120
(1 row)

psql "$SUPABASE_DB_URL" -c "SELECT customer_email, count(distinct customer_name) FROM public.orders GROUP BY customer_email HAVING count(distinct customer_name) > 1;"

 customer_email | count 
----------------+-------
(0 rows)

cat << 'EOF' > migration.sql
BEGIN;

-- 1) Create table public.customers
CREATE TABLE public.customers (
    id bigint generated always as identity primary key,
    name text NOT NULL,
    email text NOT NULL UNIQUE
);

-- Insert into public.customers from public.orders
INSERT INTO public.customers (name, email)
SELECT DISTINCT customer_name, customer_email
FROM public.orders;

-- 2) Add column customer_id to public.orders
ALTER TABLE public.orders ADD COLUMN customer_id bigint;

-- Backfill customer_id
UPDATE public.orders o
SET customer_id = c.id
FROM public.customers c
WHERE o.customer_email = c.email AND o.customer_name = c.name;

-- 3) Make customer_id NOT NULL and add foreign key referencing customers(id)
ALTER TABLE public.orders 
  ALTER COLUMN customer_id SET NOT NULL,
  ADD CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES public.customers(id);

-- 4) Drop columns customer_name and customer_email from orders
ALTER TABLE public.orders
  DROP COLUMN customer_name,
  DROP COLUMN customer_email;

COMMIT;
EOF
psql "$SUPABASE_DB_URL" -f migration.sql

BEGIN
CREATE TABLE
INSERT 0 40
ALTER TABLE
UPDATE 120
ALTER TABLE
ALTER TABLE
COMMIT

psql "$SUPABASE_DB_URL" -c "\d public.orders"

                          Table "public.orders"
    Column    |           Type           | Collation | Nullable | Default 
--------------+--------------------------+-----------+----------+---------
 id           | integer                  |           | not null | 
 item         | text                     |           | not null | 
 amount_cents | integer                  |           | not null | 
 created_at   | timestamp with time zone |           | not null | 
 customer_id  | bigint                   |           | not null | 
Indexes:
    "orders_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "fk_customer" FOREIGN KEY (customer_id) REFERENCES customers(id)

psql "$SUPABASE_DB_URL" -c "SELECT count(*) FROM public.orders;"

 count 
-------
   120
(1 row)

psql "$SUPABASE_DB_URL" -c "SELECT count(*) FROM public.customers;"

 count 
-------
    40
(1 row)