From skillry-database-and-data
Use when you need to plan non-destructive data migrations, backups, idempotency, rollback, and local-only resets.
How this skill is triggered — by the user, by Claude, or both
Slash command
/skillry-database-and-data:37-data-migration-safetyThe summary Claude sees in its skill listing — used to decide when to auto-load this skill
Plan and review schema and data migrations so they are reversible, low-downtime, and idempotent. Apply the expand-contract pattern for breaking changes, verify backfill strategy, and confirm rollback paths before any migration touches a production database.
Plan and review schema and data migrations so they are reversible, low-downtime, and idempotent. Apply the expand-contract pattern for breaking changes, verify backfill strategy, and confirm rollback paths before any migration touches a production database.
NOT NULL to a column that already has rows.prisma migrate deploy vs prisma migrate dev is the right command for a given environment.Before anything else, label every statement in the migration file:
| Statement | Risk | Reason |
|---|---|---|
CREATE TABLE | Safe | No lock contention |
ADD COLUMN NULL | Safe | Instant metadata change |
ADD COLUMN NOT NULL DEFAULT | Postgres 11+: Safe | Default stored in catalog, no rewrite |
ADD COLUMN NOT NULL (no default) | Unsafe | Table rewrite + lock |
DROP COLUMN | Destructive | Irreversible without restore |
RENAME COLUMN | Breaking | Breaks live code reading old name |
ALTER TYPE | Unsafe | Often requires table rewrite |
CREATE INDEX (no CONCURRENTLY) | Unsafe | Write lock on table |
CREATE INDEX CONCURRENTLY | Safe | No write lock, cannot be in transaction |
TRUNCATE | Destructive | Irreversible |
DELETE FROM (unbounded) | Destructive | Irreversible |
For Supabase projects:
# Trigger a manual backup via Supabase CLI before running migrations
supabase db dump -f backup_$(date +%Y%m%d_%H%M%S).sql --data-only
supabase db dump -f schema_$(date +%Y%m%d_%H%M%S).sql
For self-managed Postgres:
pg_dump -Fc -d $DATABASE_URL -f backup_$(date +%Y%m%d_%H%M%S).pgc
Do not proceed to production migration without a restorable backup less than 1 hour old.
Never rename or drop in a single deploy. Use three separate deploys:
Phase 1 — Expand (add new, keep old):
ALTER TABLE orders ADD COLUMN customer_id UUID REFERENCES customers(id);
-- Backfill (see step 4)
UPDATE orders SET customer_id = (SELECT id FROM customers WHERE email = orders.user_email) WHERE customer_id IS NULL;
-- Add NOT NULL only after backfill is complete and verified
ALTER TABLE orders ALTER COLUMN customer_id SET NOT NULL;
Deploy application code that writes BOTH user_email and customer_id.
Phase 2 — Migrate reads:
Deploy application code that reads customer_id only. Keep writing both columns.
Phase 3 — Contract (remove old column):
-- Only after Phase 2 has been stable in production for at least one deploy cycle
ALTER TABLE orders DROP COLUMN user_email;
Never run a single UPDATE on millions of rows — it holds a lock and fills WAL:
-- Batched backfill: process 1000 rows at a time with a small sleep
DO $$
DECLARE
batch_size INT := 1000;
offset_val INT := 0;
rows_updated INT;
BEGIN
LOOP
UPDATE orders
SET customer_id = (
SELECT id FROM customers WHERE email = orders.user_email
)
WHERE customer_id IS NULL
AND id IN (
SELECT id FROM orders WHERE customer_id IS NULL
ORDER BY id
LIMIT batch_size
);
GET DIAGNOSTICS rows_updated = ROW_COUNT;
EXIT WHEN rows_updated = 0;
PERFORM pg_sleep(0.05); -- 50ms pause between batches
END LOOP;
END $$;
For Prisma projects, run the backfill as a standalone script (ts-node scripts/backfill-customer-id.ts) outside of the migration, so the migration file itself contains only schema DDL.
The single-step approach causes a full table scan and ACCESS EXCLUSIVE lock:
-- UNSAFE on large tables:
ALTER TABLE orders ALTER COLUMN customer_id SET NOT NULL;
Safe pattern using a constraint that is validated in the background:
-- Step 1: add NOT VALID constraint (fast, no scan)
ALTER TABLE orders ADD CONSTRAINT orders_customer_id_not_null
CHECK (customer_id IS NOT NULL) NOT VALID;
-- Step 2: validate in background (SHARE UPDATE EXCLUSIVE, allows reads+writes)
ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_id_not_null;
-- Step 3 (optional, Postgres 12+): promote to actual NOT NULL
-- Only if you need pg_attribute.attnotnull = true
ALTER TABLE orders ALTER COLUMN customer_id SET NOT NULL;
ALTER TABLE orders DROP CONSTRAINT orders_customer_id_not_null;
Steps 1 and 2 happen in separate transactions. Step 2 can run during business hours.
Every migration must be safe to run twice without error:
-- Column already exists guard
DO $$ BEGIN
IF NOT EXISTS (
SELECT 1 FROM information_schema.columns
WHERE table_name = 'orders' AND column_name = 'customer_id'
) THEN
ALTER TABLE orders ADD COLUMN customer_id UUID;
END IF;
END $$;
-- Index already exists guard
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_customer_id ON orders(customer_id);
-- Constraint already exists guard
DO $$ BEGIN
IF NOT EXISTS (
SELECT 1 FROM pg_constraint WHERE conname = 'orders_customer_id_fk'
) THEN
ALTER TABLE orders ADD CONSTRAINT orders_customer_id_fk
FOREIGN KEY (customer_id) REFERENCES customers(id);
END IF;
END $$;
# Prisma: check what would run without applying
npx prisma migrate diff \
--from-schema-datasource prisma/schema.prisma \
--to-schema-datamodel prisma/schema.prisma \
--script
# Supabase: show pending migrations
supabase db diff
# Direct psql dry-run: wrap in a transaction and roll back
psql $DATABASE_URL -c "BEGIN; $(cat migration.sql); ROLLBACK;"
| Command | Use for | Effect |
|---|---|---|
prisma migrate dev | Local development only | Creates migration file, applies it, regenerates client |
prisma migrate deploy | CI/CD and production | Applies pending migrations only, never creates new ones |
prisma migrate reset | Local dev only | Drops + recreates the entire database |
Never run prisma migrate dev or prisma migrate reset against a production or staging database.
UPDATE statementNOT NULL on existing column uses CHECK ... NOT VALID + VALIDATE CONSTRAINTCREATE INDEX CONCURRENTLY IF NOT EXISTSprisma migrate deploy (not dev) scheduled for productionDropping a column while old code is still deployed. Even after deploying new code, the old version may still be running (blue/green, canary). Always wait a full deploy cycle before the contract phase removes columns.
Mixing schema DDL and data DML in one migration. Schema locks and data locks interact badly. Separate them: schema migration first, then a standalone backfill script, then the NOT NULL constraint.
Using RENAME TABLE or RENAME COLUMN without a view. Create a view with the old name that selects from the new name, so old code keeps working during the transition.
Forgetting to update sequences after a data import. If you INSERT rows with explicit IDs, the sequence is not advanced:
SELECT setval(pg_get_serial_sequence('orders', 'id'), MAX(id)) FROM orders;
Running migrations inside application startup. prisma migrate deploy in a Docker entrypoint on a multi-replica deployment causes race conditions. Run migrations as a one-off job/init container before scaling up replicas.
For each migration reviewed, produce:
ACCESS EXCLUSIVE locks and for how long.prisma migrate dev, prisma migrate reset, or DROP DATABASE against non-local environments.TRUNCATE as equivalent to DROP TABLE + recreate — it is irreversible without a restore.ROLLBACK, not COMMIT.Creates, edits, and optimizes skills for Claude Code, including drafting, evaluating with test prompts, iterating on performance, and improving skill descriptions for better triggering accuracy.
npx claudepluginhub fluxonlab/skillry --plugin skillry-database-and-data