db-migration

name	db-migration
description	Run database migrations safely with automatic tool detection, dry-run preview, destructive change warnings, backup, and rollback support. Trigger phrases: "migration", "schema change", "veritabani guncelle", "add column", "create table", "alter table"

Database Migration

Run database migrations safely across any ORM or migration tool with automatic detection, dry-run previews, destructive change protection, mandatory production backups, and rollback support.

Overview

This skill handles the full migration lifecycle:

1. Detect migration tool from project files
2. Check migration status
3. Analyze the change (additive / destructive / ambiguous)
4. Safety gate — require confirmation on destructive changes
5. Backup the database (mandatory in production)
6. Dry-run the migration (ON by default)
7. Apply the migration
8. Verify schema state
9. Rollback if something goes wrong
10. Raw SQL support when no ORM is detected

Pre-Migration Checklist

• Migration tool detected
• Migration status checked — pending migrations identified
• Change classified (additive / destructive / ambiguous)
• Destructive changes confirmed by user (if any)
• Database backed up (mandatory in production)
• Dry-run completed and reviewed
• User confirmed apply
• Migration applied
• Schema verified post-migration
• Rollback plan ready

---

Step 1: Detect Migration Tool

Scan the project for known files and patterns to determine which migration tool is in use.

Detection table:

File / Pattern	Tool
prisma/schema.prisma	Prisma
knexfile.js or knexfile.ts	Knex
.sequelizerc or config/config.json + models/ directory	Sequelize
ormconfig.ts or data-source.ts with typeorm import	TypeORM
drizzle.config.ts	Drizzle
alembic.ini or alembic/ directory	Alembic
manage.py + django in requirements file	Django
Gemfile with rails + db/migrate/ directory	Rails ActiveRecord

Detection logic:

# Prisma
[ -f prisma/schema.prisma ] && TOOL="prisma"

# Knex
([ -f knexfile.js ] || [ -f knexfile.ts ]) && TOOL="knex"

# Sequelize
([ -f .sequelizerc ] || ([ -f config/config.json ] && [ -d models ])) && TOOL="sequelize"

# TypeORM
([ -f ormconfig.ts ] || ([ -f data-source.ts ] && grep -q "typeorm" data-source.ts)) && TOOL="typeorm"

# Drizzle
[ -f drizzle.config.ts ] && TOOL="drizzle"

# Alembic
([ -f alembic.ini ] || [ -d alembic ]) && TOOL="alembic"

# Django
([ -f manage.py ] && (grep -q "django" requirements.txt 2>/dev/null || grep -q "django" requirements/*.txt 2>/dev/null || grep -q "Django" Pipfile 2>/dev/null || grep -q "django" pyproject.toml 2>/dev/null)) && TOOL="django"

# Rails ActiveRecord
([ -d db/migrate ] && grep -q "rails" Gemfile 2>/dev/null) && TOOL="rails"

Rules:

• If no tool is detected, fall back to raw SQL mode (see Step 10).
• If multiple tools are detected, ask the user which one to use.

---

Step 2: Check Migration Status

Run the tool-specific status command to show pending and applied migrations.

Tool	Status Command
Prisma	npx prisma migrate status
Knex	npx knex migrate:status
Sequelize	npx sequelize-cli db:migrate:status
TypeORM	npx typeorm migration:show
Drizzle	npx drizzle-kit status
Alembic	alembic current and alembic history
Django	python manage.py showmigrations
Rails	bin/rails db:migrate:status

Rules:

• Show the user which migrations are pending before proceeding.
• If no pending migrations are found, inform the user and stop unless they are creating a new migration.

---

Step 3: Analyze Change

Classify the migration as additive, destructive, or ambiguous.

Additive (Safe)

These operations add new structures without affecting existing data:

• CREATE TABLE
• ADD COLUMN (with nullable or default value)
• CREATE INDEX
• ADD CONSTRAINT (non-destructive)
• INSERT INTO (seed data)

Destructive (Dangerous)

These operations can cause data loss and always require user confirmation:

• DROP TABLE
• DROP COLUMN
• ALTER TYPE / ALTER COLUMN ... TYPE (type changes)
• DROP INDEX (potential performance impact)
• DROP CONSTRAINT
• TRUNCATE
• DELETE FROM (without specific WHERE clause)
• RENAME TABLE (can break application references)
• RENAME COLUMN (can break application references)

Ambiguous (Requires Review)

These operations need human judgment:

• ALTER TABLE ... ALTER COLUMN (depends on the change)
• ADD COLUMN ... NOT NULL (without default — fails on non-empty tables)
• UPDATE statements (depends on scope)
• Any migration with both additive and destructive operations

How to classify:

• For Prisma: compare prisma/schema.prisma changes against prisma migrate diff --preview
• For Knex/Sequelize/TypeORM: read the migration file contents
• For Alembic: read the upgrade() function in the migration file
• For Django: run python manage.py sqlmigrate <app> <migration_name> to see the generated SQL
• For Rails: read the change / up method in the migration file
• For Drizzle: run npx drizzle-kit generate and inspect the SQL output

---

Step 4: Safety Gate

Mandatory rules for destructive changes:

1. Warn the user explicitly. List every destructive operation found in the migration.
2. Require confirmation. Do not proceed without explicit user approval.
3. Never auto-apply destructive migrations. Even if the user said "run it" for the overall flow, destructive changes must be confirmed individually.

Warning format:

WARNING: This migration contains destructive changes:

  - DROP TABLE "users_backup"
  - DROP COLUMN "legacy_email" from table "users"
  - ALTER TYPE on column "status" in table "orders" (varchar -> integer)

These changes CANNOT be undone without a backup.
Database: production (myapp_db @ db.example.com)

Do you want to proceed? (yes/no)

Rules:

• If the target is a production database, always include the database name and host in the warning.
• If the user declines, stop immediately and suggest alternatives (e.g., create a new column instead of altering type).

---

Step 5: Backup

Backup the database before applying any migration. Mandatory in production.

PostgreSQL

# Full database backup
pg_dump -h $DB_HOST -U $DB_USER -d $DB_NAME -F c -f backup_$(date +%Y%m%d_%H%M%S).dump

# Schema-only backup (faster, for quick rollback reference)
pg_dump -h $DB_HOST -U $DB_USER -d $DB_NAME --schema-only -f schema_backup_$(date +%Y%m%d_%H%M%S).sql

# Specific table backup (when migration affects known tables)
pg_dump -h $DB_HOST -U $DB_USER -d $DB_NAME -t $TABLE_NAME -F c -f table_backup_$(date +%Y%m%d_%H%M%S).dump

MySQL

# Full database backup
mysqldump -h $DB_HOST -u $DB_USER -p$DB_PASS $DB_NAME > backup_$(date +%Y%m%d_%H%M%S).sql

# Schema-only backup
mysqldump -h $DB_HOST -u $DB_USER -p$DB_PASS --no-data $DB_NAME > schema_backup_$(date +%Y%m%d_%H%M%S).sql

# Specific table backup
mysqldump -h $DB_HOST -u $DB_USER -p$DB_PASS $DB_NAME $TABLE_NAME > table_backup_$(date +%Y%m%d_%H%M%S).sql

SQLite

# Full database backup (simple file copy)
cp $DB_PATH backup_$(date +%Y%m%d_%H%M%S).db

# Using SQLite backup command (safer for concurrent access)
sqlite3 $DB_PATH ".backup 'backup_$(date +%Y%m%d_%H%M%S).db'"

Rules:

• In production, backup is mandatory. Never skip it.
• In development, backup is recommended but can be skipped with user confirmation.
• Verify the backup file was created and is non-empty before proceeding.
• Store backups in a known directory and report the path to the user.

# Verify backup
BACKUP_FILE="backup_$(date +%Y%m%d_%H%M%S).dump"
if [ ! -s "$BACKUP_FILE" ]; then
  echo "ERROR: Backup file is empty or was not created. Aborting migration."
  exit 1
fi
echo "Backup created: $BACKUP_FILE ($(du -h $BACKUP_FILE | cut -f1))"

---

Step 6: Dry-Run

Preview the migration without applying it. ON by default.

Tool	Dry-Run / Preview Command
Prisma	npx prisma migrate diff --from-schema-datamodel prisma/schema.prisma --to-schema-datasource prisma/schema.prisma --script
Knex	npx knex migrate:latest --dry-run (or read the migration file and display the SQL)
Sequelize	Read the migration file and display the up function logic
TypeORM	npx typeorm migration:generate -n Preview --dry-run (or read the pending migration file)
Drizzle	npx drizzle-kit generate --custom then inspect the generated SQL
Alembic	alembic upgrade head --sql
Django	python manage.py sqlmigrate <app_label> <migration_name>
Rails	bin/rails db:migrate:status then read the pending migration file content

Rules:

• Always show the dry-run output to the user before applying.
• If dry-run is not natively supported by the tool, read the migration file and display its contents as the preview.
• The user must confirm after reviewing the dry-run output.
• To skip dry-run, the user must explicitly request it.

Dry-run output format:

DRY-RUN PREVIEW:

Migration: 20240115_add_user_email_verified
Tool: Prisma

SQL to be executed:
  ALTER TABLE "users" ADD COLUMN "email_verified" BOOLEAN NOT NULL DEFAULT false;
  CREATE INDEX "idx_users_email_verified" ON "users" ("email_verified");

Classification: ADDITIVE (safe)

Proceed with apply? (yes/no)

---

Step 7: Apply Migration

Run the migration after backup and dry-run are complete.

Tool	Apply Command
Prisma	npx prisma migrate deploy
Knex	npx knex migrate:latest
Sequelize	npx sequelize-cli db:migrate
TypeORM	npx typeorm migration:run
Drizzle	npx drizzle-kit push
Alembic	alembic upgrade head
Django	python manage.py migrate
Rails	bin/rails db:migrate

For new migrations (creating a migration file first):

Tool	Generate Command
Prisma	npx prisma migrate dev --name <name>
Knex	npx knex migrate:make <name>
Sequelize	npx sequelize-cli migration:generate --name <name>
TypeORM	npx typeorm migration:generate -n <name>
Drizzle	npx drizzle-kit generate
Alembic	alembic revision --autogenerate -m "<name>"
Django	python manage.py makemigrations
Rails	bin/rails generate migration <name>

Rules:

• Never apply without completing the dry-run step (unless explicitly skipped by user).
• Never apply destructive migrations without passing the safety gate (Step 4).
• Capture and display the full output of the apply command.
• If the apply command fails, display the error and suggest rollback.

---

Step 8: Verify

Check the database schema state after migration to confirm it was applied correctly.

Tool	Verify Command
Prisma	npx prisma migrate status (should show no pending migrations)
Knex	npx knex migrate:status
Sequelize	npx sequelize-cli db:migrate:status
TypeORM	npx typeorm migration:show
Drizzle	npx drizzle-kit status
Alembic	alembic current (should match head revision)
Django	python manage.py showmigrations (all should be [X])
Rails	bin/rails db:migrate:status (all should be up)

Additional verification:

# PostgreSQL — verify table/column exists
psql -h $DB_HOST -U $DB_USER -d $DB_NAME -c "\d $TABLE_NAME"

# MySQL — verify table/column exists
mysql -h $DB_HOST -u $DB_USER -p$DB_PASS $DB_NAME -e "DESCRIBE $TABLE_NAME;"

# SQLite — verify table/column exists
sqlite3 $DB_PATH ".schema $TABLE_NAME"

Rules:

• Always run verification after apply.
• Compare the actual schema state with the expected state from the migration.
• If verification fails, warn the user and suggest rollback.

---

Step 9: Rollback

Undo the last migration using the tool's built-in rollback, or restore from backup as a fallback.

Tool-Specific Rollback

Tool	Rollback Command
Prisma	npx prisma migrate resolve --rolled-back <migration_name> then restore from backup
Knex	npx knex migrate:rollback
Sequelize	npx sequelize-cli db:migrate:undo
TypeORM	npx typeorm migration:revert
Drizzle	npx drizzle-kit drop (limited — prefer backup restore)
Alembic	alembic downgrade -1
Django	python manage.py migrate <app_label> <previous_migration_name>
Rails	bin/rails db:rollback STEP=1

Backup Restore Fallback

When tool-specific rollback is unavailable or fails, restore from the backup taken in Step 5.

PostgreSQL:

# Restore from custom-format dump
pg_restore -h $DB_HOST -U $DB_USER -d $DB_NAME --clean --if-exists $BACKUP_FILE

# Restore from SQL dump
psql -h $DB_HOST -U $DB_USER -d $DB_NAME < $BACKUP_FILE

MySQL:

# Restore from SQL dump
mysql -h $DB_HOST -u $DB_USER -p$DB_PASS $DB_NAME < $BACKUP_FILE

SQLite:

# Restore from file copy
cp $BACKUP_FILE $DB_PATH

Rules:

• Always attempt tool-specific rollback first.
• If tool-specific rollback fails, fall back to backup restore.
• After rollback, run verification (Step 8) to confirm the schema is in the expected state.
• If both rollback and backup restore fail, alert the user immediately with full error details.

---

Step 10: Raw SQL Support

When no ORM or migration tool is detected, support raw SQL migration files.

File Structure

Create a migrations/ directory with numbered up/down SQL files:

migrations/
  001_create_users.up.sql
  001_create_users.down.sql
  002_add_email_verified.up.sql
  002_add_email_verified.down.sql
  003_create_orders.up.sql
  003_create_orders.down.sql

Up Migration Template

-- migrations/001_create_users.up.sql
-- Description: Create users table
-- Created: 2024-01-15

BEGIN;

CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_users_email ON users (email);

COMMIT;

Down Migration Template

-- migrations/001_create_users.down.sql
-- Description: Drop users table
-- Created: 2024-01-15

BEGIN;

DROP INDEX IF EXISTS idx_users_email;
DROP TABLE IF EXISTS users;

COMMIT;

Migration Tracking Table

Create a tracking table to record which migrations have been applied:

CREATE TABLE IF NOT EXISTS _migrations (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL UNIQUE,
    applied_at TIMESTAMP NOT NULL DEFAULT NOW()
);

Apply Raw SQL Migrations

# PostgreSQL
for f in migrations/*.up.sql; do
  MIGRATION_NAME=$(basename "$f" .up.sql)
  # Check if already applied
  APPLIED=$(psql -h $DB_HOST -U $DB_USER -d $DB_NAME -tAc \
    "SELECT COUNT(*) FROM _migrations WHERE name = '$MIGRATION_NAME'")
  if [ "$APPLIED" = "0" ]; then
    echo "Applying: $MIGRATION_NAME"
    psql -h $DB_HOST -U $DB_USER -d $DB_NAME -f "$f"
    psql -h $DB_HOST -U $DB_USER -d $DB_NAME -c \
      "INSERT INTO _migrations (name) VALUES ('$MIGRATION_NAME')"
  else
    echo "Skipping (already applied): $MIGRATION_NAME"
  fi
done

Rollback Raw SQL Migrations

# Get the last applied migration
LAST=$(psql -h $DB_HOST -U $DB_USER -d $DB_NAME -tAc \
  "SELECT name FROM _migrations ORDER BY applied_at DESC LIMIT 1")

if [ -n "$LAST" ]; then
  DOWN_FILE="migrations/${LAST}.down.sql"
  if [ -f "$DOWN_FILE" ]; then
    echo "Rolling back: $LAST"
    psql -h $DB_HOST -U $DB_USER -d $DB_NAME -f "$DOWN_FILE"
    psql -h $DB_HOST -U $DB_USER -d $DB_NAME -c \
      "DELETE FROM _migrations WHERE name = '$LAST'"
  else
    echo "ERROR: Down migration file not found: $DOWN_FILE"
    echo "Falling back to backup restore."
  fi
else
  echo "No migrations to rollback."
fi

Rules:

• Every up migration must have a corresponding down migration.
• All SQL files must be wrapped in BEGIN / COMMIT for transactional safety.
• File naming must use sequential numbering with descriptive names.
• The _migrations tracking table must be created automatically if it does not exist.

---

Safety Rules Summary

These rules are mandatory and must never be skipped:

1. Dry-run is ON by default. Always preview the migration before applying. The user must explicitly opt out.
2. Destructive changes require confirmation. DROP TABLE, DROP COLUMN, ALTER TYPE, TRUNCATE, and RENAME operations must be confirmed by the user.
3. Production backup is mandatory. Never apply a migration in production without a verified backup.
4. Never auto-apply destructive migrations. Even in automated flows, destructive changes must pause for human confirmation.
5. Always verify after apply. Run the status/verification command after every migration.
6. Always have a rollback plan. Either a tool-specific rollback or a backup restore must be available before applying.
7. Warn on ambiguous changes. If a change cannot be clearly classified as additive or destructive, treat it as potentially destructive and warn the user.
8. Show the database target. Always display which database (name, host, environment) the migration will run against before applying.