Plan and execute database schema migrations with up/down scripts, zero-downtime DDL patterns, seed data management, rollback procedures, and ORM vs raw SQL decision framework. Covers Postgres, MySQL, and SQLite.
From sanpx claudepluginhub javimontano/jm-adk --plugin sovereign-architectThis skill is limited to using the following tools:
agents/schema-migration-strategy-agent.mdevals/evals.jsonexamples/sample-output.mdprompts/use-case-prompts.mdreferences/body-of-knowledge.mdreferences/knowledge-graph.mmdreferences/state-of-the-art.md"A migration without a rollback plan is a gamble on your deployment always succeeding — plan for failure first."
Five-step procedure to design, execute, and validate database schema migrations: migration file naming, up/down pattern, zero-downtime DDL for large tables, seed data separation, and automated rollback triggers in CI/CD.
_prisma_migrations, flyway_schema_history, etc.)
for failed or missing migrations.[HECHO] confirmed, [SUPUESTO] if production is not accessible.migrations/
├── 001_create_users.sql
├── 002_create_posts.sql
├── 003_add_users_role.sql # Additive — safe
├── 004_backfill_users_role.sql # Data migration — run separately
├── 005_make_users_role_not_null.sql # Constraint — after backfill
└── 006_drop_users_legacy_col.sql # Destructive — last step
-- migrations/003_add_users_role.sql
-- UP
ALTER TABLE users ADD COLUMN role VARCHAR(50) NOT NULL DEFAULT 'user';
CREATE INDEX CONCURRENTLY idx_users_role ON users(role);
-- DOWN
DROP INDEX CONCURRENTLY IF EXISTS idx_users_role;
ALTER TABLE users DROP COLUMN IF EXISTS role;
# Development
npx prisma migrate dev --name add_users_role
# Preview what will run without applying
npx prisma migrate diff \
--from-schema-datasource prisma/schema.prisma \
--to-schema-datamodel prisma/schema.prisma \
--script
# Production (CI/CD only)
npx prisma migrate deploy
# Check migration status
npx prisma migrate status
Schema changes on tables with millions of rows can lock tables for minutes. Use these patterns to avoid downtime.
-- Safe — adds nullable column, no lock
ALTER TABLE orders ADD COLUMN notes TEXT;
-- Unsafe — NOT NULL without default requires full table rewrite in older Postgres
-- ALTER TABLE orders ADD COLUMN status VARCHAR(20) NOT NULL; -- AVOID
-- Safe — add nullable first, backfill, then add constraint
ALTER TABLE orders ADD COLUMN status VARCHAR(20); -- Step 1
UPDATE orders SET status = 'pending' WHERE status IS NULL; -- Step 2 (batched)
ALTER TABLE orders ALTER COLUMN status SET NOT NULL; -- Step 3
-- Step 1: Add new column (deploy app that writes both)
ALTER TABLE users ADD COLUMN full_name VARCHAR(255);
-- Step 2: Backfill
UPDATE users SET full_name = name WHERE full_name IS NULL;
-- Step 3: Make NOT NULL (after backfill)
ALTER TABLE users ALTER COLUMN full_name SET NOT NULL;
-- Step 4: Deploy app that reads new column only
-- Step 5: Drop old column
ALTER TABLE users DROP COLUMN name;
-- PostgreSQL — CREATE INDEX CONCURRENTLY
-- Does not hold a lock, takes longer but safe in production
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);
CREATE INDEX CONCURRENTLY idx_orders_status_created ON orders(status, created_at DESC);
-- Note: CONCURRENTLY cannot run inside a transaction
-- Note: Takes 2-10x longer than regular CREATE INDEX
-- Never UPDATE all rows in one transaction — use batches
DO $$
DECLARE
batch_size INT := 1000;
offset_val INT := 0;
rows_updated INT;
BEGIN
LOOP
WITH batch AS (
SELECT id FROM users
WHERE role IS NULL
LIMIT batch_size FOR UPDATE SKIP LOCKED
)
UPDATE users SET role = 'user'
WHERE id IN (SELECT id FROM batch);
GET DIAGNOSTICS rows_updated = ROW_COUNT;
EXIT WHEN rows_updated = 0;
PERFORM pg_sleep(0.1); -- Brief pause between batches
END LOOP;
END $$;
// prisma/seed.ts — Reference data vs. test data
import { PrismaClient } from '@prisma/client';
const db = new PrismaClient();
async function main() {
// Reference data — idempotent upserts
await db.role.upsert({
where: { name: 'admin' },
update: {},
create: { name: 'admin', permissions: ['read', 'write', 'delete'] },
});
await db.role.upsert({
where: { name: 'user' },
update: {},
create: { name: 'user', permissions: ['read'] },
});
// Development-only seed data
if (process.env.NODE_ENV !== 'production') {
await db.user.upsert({
where: { email: 'admin@example.com' },
update: {},
create: {
email: 'admin@example.com',
name: 'Admin User',
role: 'admin',
},
});
}
}
main()
.catch((e) => { console.error(e); process.exit(1); })
.finally(() => db.$disconnect());
// package.json
{
"prisma": { "seed": "ts-node prisma/seed.ts" }
}
# .github/workflows/deploy.yml
- name: Run migrations
id: migrate
run: npx prisma migrate deploy
- name: Rollback on failure
if: failure() && steps.migrate.conclusion == 'failure'
run: |
# Restore from pre-migration snapshot (RDS, Supabase point-in-time restore)
echo "Migration failed — triggering point-in-time restore"
aws rds restore-db-instance-to-point-in-time \
--source-db-instance-identifier $DB_INSTANCE \
--target-db-instance-identifier $DB_INSTANCE-rollback \
--restore-time $(date -u -v-5M +"%Y-%m-%dT%H:%M:%SZ")
CREATE TABLE IF NOT EXISTS, ADD COLUMN IF NOT EXISTS.CONCURRENTLY — No production table locks during index creation.prisma migrate status returns zero pending migrations.CREATE INDEX CONCURRENTLY cannot run inside a transaction block.Searches, retrieves, and installs Agent Skills from prompts.chat registry using MCP tools like search_skills and get_skill. Activates for finding skills, browsing catalogs, or extending Claude.