Opinionated coding rules, conventions, and best practices for designing, writing, testing, and operating database migration scripts and pipelines.
Database migrations shouldn't keep you up at night. Yet here you are, debugging failed production deployments, dealing with data corruption, or worse—explaining to stakeholders why the system's been down for hours.
These Cursor Rules transform database migrations from high-stakes gambling into predictable, automated workflows that actually work.
Every developer knows the drill: migrations work perfectly in dev, pass staging tests, then explode spectacularly in production. You're stuck with:
Sound familiar? These rules solve these exact problems with battle-tested patterns used by teams managing petabyte-scale migrations.
This rulebook treats database schema as first-class code with the same rigor you apply to application development. Every change becomes an immutable, version-controlled migration script that's deterministic, recoverable, and fully automated.
Here's what changes immediately:
Before: Manual SQL scripts run via command line, fingers crossed
-- Someone's "quick fix" script
ALTER TABLE users ADD COLUMN email VARCHAR(255);
UPDATE users SET email = '[email protected]' WHERE email IS NULL;
ALTER TABLE users ALTER COLUMN email SET NOT NULL;
After: Structured, validated migrations with built-in safety checks
-- V20241215143022__add-user-email-field.sql
BEGIN;
-- Safety check: ensure prerequisites exist
SELECT CASE WHEN NOT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'users')
THEN RAISE EXCEPTION 'users table not found' END;
-- Two-phase approach for zero downtime
ALTER TABLE users ADD COLUMN email VARCHAR(255);
-- Backfill in chunks to avoid long locks
UPDATE users SET email = COALESCE(profile_email, '[email protected]')
WHERE email IS NULL AND user_id BETWEEN 1 AND 10000;
COMMIT;
Time Saved: 2-3 hours per deployment → 5 minutes automated
Incidents Prevented: ~80% of database-related production issues
Bug Detection: 90% of migration issues caught in CI/CD
You need to add a subscription_tier column to a 50M row users table without downtime.
Standard Approach (risky):
ALTER TABLE users ADD COLUMN subscription_tier VARCHAR(20) NOT NULL DEFAULT 'free';
Result: Table locked for 20+ minutes, application timeouts, angry users.
With These Rules:
-- V20241215143022__add-subscription-tier-phase1.sql
BEGIN;
ALTER TABLE users ADD COLUMN subscription_tier VARCHAR(20) DEFAULT 'free';
COMMIT;
-- V20241215143023__add-subscription-tier-phase2.sql
BEGIN;
-- Backfill in chunks
UPDATE users SET subscription_tier = 'free'
WHERE subscription_tier IS NULL AND user_id % 100000 = 0;
-- Continue chunked updates...
-- After backfill complete
ALTER TABLE users ALTER COLUMN subscription_tier SET NOT NULL;
COMMIT;
Result: Zero downtime, predictable performance, automatic rollback available.
Moving user preferences from JSON column to normalized tables.
With Framework Integration:
# liquibase-changelog.xml
- changeSet:
id: normalize-user-preferences
author: dev-team
labels: data-migration
validCheckSum: any
changes:
- sqlFile:
path: migrations/extract-preferences.sql
stripComments: true
rollback:
- sqlFile:
path: rollbacks/restore-json-preferences.sql
The rules ensure this migration is tested in isolated containers, validates data integrity, and maintains full rollback capability.
Choose your tool based on your stack:
For PostgreSQL/MySQL with existing CI/CD:
# Flyway setup
flyway -url=jdbc:postgresql://localhost/mydb -user=dbuser -password=secret migrate
For Multi-Database Applications:
<!-- Liquibase approach -->
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog">
<include file="migrations/001-initial-schema.xml"/>
</databaseChangeLog>
/db
/migration
V20241215143000__initial-schema.sql
V20241215143001__add-user-indexes.sql
R__update-user-permissions.sql
/rollback
V20241215143001__rollback-user-indexes.sql
/callback
beforeMigrate.sql
# GitHub Actions example
- name: Validate Migrations
run: |
sqlfluff lint db/migration/ --rules=L003,L016
flyway info -url=$DATABASE_URL
- name: Test in Container
run: |
docker run -d --name test-db postgres:15
flyway migrate -url=jdbc:postgresql://test-db/testdb
# Run smoke tests
# Automated deployment with safety checks
flyway info -url=$PROD_URL # Verify target state
flyway migrate -url=$PROD_URL -validateOnMigrate=true
# Automatic post-migration verification runs
A team using these rules migrated their 500M row order history table across three different schema changes in production with:
Your database migrations can be this reliable. These rules give you the framework that companies like Stripe and Shopify use to deploy schema changes hundreds of times per day without breaking production.
Stop treating database changes like dangerous manual procedures. Make them as reliable and predictable as the rest of your engineering workflow.
You are an expert in SQL (ANSI-92, PostgreSQL, MySQL, SQL Server, Oracle), Flyway, Liquibase, AWS/GCP/Azure Database Migration Services, Striim, Airbyte, Fivetran, Talend, Bash, Docker, Kubernetes.
Key Principles
- Treat the database schema as code: every change is an immutable, version-controlled migration script.
- Prefer additive, backward-compatible changes (e.g., new columns, nullable fields) to destructive ones.
- Migrations must be deterministic, idempotent when possible, and fully recoverable via automated rollback.
- Automate everything: generation, validation, deployment, verification, and monitoring inside CI/CD.
- Zero data loss and minimal downtime are mandatory success metrics.
- Security, compliance, and auditability are first-class concerns—encrypt in transit & at rest, least-privilege roles, full audit trail.
SQL
- File naming: `V<yyyyMMddHHmmss>__<short-kebab-desc>.sql` for versioned, `R__<short-kebab-desc>.sql` for repeatable scripts.
- One DDL/DML concern per file. Never mix structural changes with data patching in the same script.
- Always wrap changes in explicit transactions:
```sql
BEGIN;
-- migration statements
COMMIT;
```
- Include an upfront safety check:
```sql
--#assert table_exists('users'); -- Flyway placeholder example
```
- Avoid `SELECT *`; specify columns.
- Use qualified identifiers (`schema.table`) to avoid search-path surprises.
- Write forward & backward scripts; store rollbacks under `/rollback` using matching version stamp.
- Enforce referential integrity with foreign keys; never disable constraints for convenience.
- Use `CHECK` constraints instead of code-level validations where possible.
- For large tables, migrate in chunks:
```sql
UPDATE big_tbl SET processed=true WHERE processed=false LIMIT 10000;
```
and loop via driver script to avoid long locks.
- Prefer `ALTER TABLE ... ADD COLUMN` + backfill + `SET NOT NULL` (two-phase) for zero downtime.
Error Handling and Validation
- Fail fast: the first statement failure aborts the whole migration; never `SET XACT_ABORT OFF`.
- Validate prerequisites at top using `RAISE EXCEPTION` (Postgres) or `THROW` (SQL Server) with clear message.
- CI pipeline runs `sqlfluff lint` + `sqlfluff fix --rules=L003,L016` to catch style & common errors.
- Automated smoke test after deploy: checksum row counts, referential integrity queries, business KPIs.
Framework-Specific Rules
Flyway
- Place migration folders under `/db/migration`. Keep callbacks (`beforeMigrate`, `afterEachMigrate`) under `/db/callback`.
- `flyway.clean` is DISALLOWED in prod; require `-Dflyway.prohibit.clean=true`.
- Use `flyway.info` in CI to assert target version equals application semantic version.
- Prefer `repeatable` scripts for views, functions, grants.
Liquibase
- Use XML/YAML changelogs—never SQL—in multi-DB products to stay engine-agnostic.
- Each `<changeSet>` must include `id`, `author`, and `labels` (`prod`, `test`, `seed`).
- Require `runOnChange="true"` only for non-destructive items (views, procedures).
- Disallow `dropTable` unless `environment="dev"`.
Cloud DMS (AWS, Azure, GCP)
- Always enable continuous replication (CDC) while full load is running.
- Use task-level pre-migration assessments; block deployment if risk score > 0.
- Encrypt endpoints with TLS; store secrets in KMS/KeyVault/Secret Manager.
Additional Sections
Testing
- Spin up disposable DB containers (e.g., Testcontainers) per PR; run full migration chain from baseline to HEAD.
- Use `pgTAP`/`tSQLt` for asserting schema and data expectations post-migration.
Performance Optimization
- Create indexes AFTER bulk data load to speed inserts.
- For large table renames, create a new table and dual-write via application until cut-over.
- Analyze/collect statistics after heavy DML: `ANALYZE VERBOSE table_name;`.
Security
- Do not embed credentials in scripts; reference secrets via environment variables or Vault.
- Sanitise PII during test migrations (e.g., hash emails, mask SSNs).
- Grant migration tool role only `ALTER`, `CREATE`, `INSERT`, `UPDATE`; never `SELECT *` on sensitive tables unless required.
Documentation & Observability
- Auto-generate ER diagrams from live schema after each successful pipeline run.
- Push `flyway.info -json` / `liquibase history --format json` to observability stack (Grafana/Elastic) for audit.
- Maintain `MIGRATION_RUNBOOK.md` per service with steps, expected duration, and rollback playbook.