>
Data Migration Scripts
Table of Contents
Overview
When to Use
Quick Start
Reference Guides
Best Practices
Overview
Create robust, safe, and reversible data migration scripts for database schema changes and data transformations with minimal downtime.
When to Use
Database schema changes
Adding/removing/modifying columns
Migrating between database systems
Data transformations and cleanup
Splitting or merging tables
Changing data types
Adding indexes and constraints
Backfilling data
Multi-tenant data migrations
Quick Start
Minimal working example:
import { Knex } from "knex";
// migrations/20240101000000_add_user_preferences.ts
export async function up(knex: Knex): Promise<void> {
// Create new table
await knex.schema.createTable("user_preferences", (table) => {
table.uuid("id").primary().defaultTo(knex.raw("gen_random_uuid()"));
table
.uuid("user_id")
.notNullable()
.references("id")
.inTable("users")
.onDelete("CASCADE");
table.jsonb("preferences").defaultTo("{}");
table.timestamp("created_at").defaultTo(knex.fn.now());
table.timestamp("updated_at").defaultTo(knex.fn.now());
table.index("user_id");
});
// Migrate existing data
await knex.raw(`
INSERT INTO user_preferences (user_id, preferences)
SELECT id, jsonb_build_object(
'theme', COALESCE(theme, 'light'),
// ... (see reference guides for full implementation)
Reference Guides
Detailed implementations in the references/ directory:
Guide
Contents
Knex.js Migrations (Node.js)
Knex.js Migrations (Node.js)
Alembic Migrations (Python/SQLAlchemy)
Alembic Migrations (Python/SQLAlchemy)
Large Data Migration with Batching
Large Data Migration with Batching
Zero-Downtime Migration Pattern
Zero-Downtime Migration Pattern
Migration Validation
Migration Validation
Cross-Database Migration
Cross-Database Migration
Best Practices
✅ DO
Always write both up and down migrations
Test migrations on production-like data
Use transactions for atomic operations
Process large datasets in batches
Add indexes after data insertion
Validate data after migration
Log progress and errors
Use feature flags for application code changes
Back up database before running migrations
Test rollback procedures
Document migration side effects
Version control all migrations
Use idempotent operations
❌ DON'T
Run untested migrations on production
Make breaking changes without backwards compatibility
Process millions of rows in single transaction
Skip rollback implementation
Ignore migration failures
Modify old migrations
Delete data without backups
Run migrations manually in production
1d:[don't have the plugin yet? install it then click "run inline in claude" again.