Data Migrations — Complete Guide
In this tutorial, you will learn about Data Migrations. We cover key concepts, practical examples, and best practices to help you master this topic.
Learn data migrations: transforming existing data alongside schema changes in production databases. Understand batch processing, rollback strategies, and data migration best practices for reliable data transformations.
What You Learn
You will learn what data migrations are, how they differ from schema migrations, when to use them, how to write safe data transformations with batch processing, how to handle rollbacks, and best practices for testing data migrations on production-scale data.
Why It Matters
Schema changes often require data transformations. Adding a column needs a default value for existing rows. Renaming a column requires copying data from old to new. Merging tables requires transforming and moving records. Data migrations handle these transformations safely.
Real-World Use
DodaTech's Durga Antivirus Pro needed to merge customer_accounts and enterprise_accounts tables. A data migration processed 2 million records in batches of 10,000, transformed account types, merged duplicate entries, and completed within a 30-minute maintenance window without data loss.
Schema Migration vs Data Migration
// Schema migration: changes structure
const schemaMigration = {
up: () => op.addColumn('users', 'full_name', { type: 'VARCHAR(200)' }),
down: () => op.dropColumn('users', 'full_name'),
};
// Data migration: transforms existing data
const dataMigration = {
up: async () => {
const users = await db.query('SELECT id, first_name, last_name FROM users');
for (const user of users) {
await db.query(
'UPDATE users SET full_name = $1 WHERE id = $2',
[`${user.first_name} ${user.last_name}`, user.id]
);
}
},
down: async () => {
await db.query('UPDATE users SET full_name = NULL');
},
};
Expected output: Schema migrations add columns. Data migrations populate those columns with transformed values from existing data. Both have up and down functions, but data migrations handle existing records.
Backfilling a New Column
# migrations/20260628_backfill_full_name.py
"""Backfill full_name column from first_name and last_name."""
from alembic import op
import sqlalchemy as sa
revision = 'abc123def456'
down_revision = 'prev_revision'
def upgrade():
connection = op.get_bind()
# Count total rows for progress tracking
total = connection.execute(
sa.text('SELECT COUNT(*) FROM users')
).scalar()
print(f"Backfilling {total} users...")
# Batch update in chunks
batch_size = 1000
offset = 0
while True:
result = connection.execute(
sa.text("""
UPDATE users
SET full_name = CONCAT(first_name, ' ', last_name)
WHERE id IN (
SELECT id FROM users
WHERE full_name IS NULL
LIMIT :batch_size
)
"""),
{'batch_size': batch_size}
)
if result.rowcount == 0:
break
offset += result.rowcount
print(f"Processed {offset}/{total} users")
def downgrade():
op.execute(
sa.text('UPDATE users SET full_name = NULL')
)
Expected output: Data migration backfills the full_name column in batches of 1000. Each batch is a separate Transaction. Progress is logged. The downgrade reverts by setting full_name back to NULL.
Data Transformation with Validation
// migrations/20260628_normalize_phone_numbers.js
exports.up = async (db) => {
const { rows } = await db.query(
'SELECT id, phone FROM customers WHERE phone IS NOT NULL'
);
let updated = 0;
let errors = [];
for (const row of rows) {
try {
const normalized = normalizePhone(row.phone);
await db.query(
'UPDATE customers SET phone = $1 WHERE id = $2',
[normalized, row.id]
);
updated++;
} catch (err) {
errors.push({ id: row.id, phone: row.phone, error: err.message });
}
}
console.log(`Normalized ${updated} phone numbers`);
if (errors.length > 0) {
console.error(`${errors.length} errors:`, errors);
// Log errors but do not fail - manual cleanup needed
}
};
exports.down = async (db) => {
// Cannot revert normalization without original values
// This is an irreversible data migration
console.warn('Phone normalization is irreversible');
};
function normalizePhone(phone) {
// Strip non-digits, format as +1XXXXXXXXXX
const digits = phone.replace(/\D/g, '');
if (digits.length === 10) return `+1${digits}`;
if (digits.length === 11 && digits.startsWith('1')) return `+${digits}`;
throw new Error(`Invalid phone format: ${phone}`);
}
Expected output: Data transformation validates each record. Invalid records are logged but do not stop the migration. This allows partial migrations with manual cleanup for problematic records.
Merging Tables
# migrations/20260628_merge_accounts.py
"""Merge enterprise_accounts into customer_accounts."""
from alembic import op
import sqlalchemy as sa
revision = 'def789ghi012'
down_revision = 'abc123def456'
def upgrade():
connection = op.get_bind()
# Step 1: Add enterprise-specific columns to customers
op.add_column('customers',
sa.Column('account_type', sa.String(20), nullable=True)
)
op.add_column('customers',
sa.Column('company_name', sa.String(200), nullable=True)
)
# Step 2: Copy enterprise accounts into customers
connection.execute(sa.text("""
INSERT INTO customers (
email, account_type, company_name, created_at, updated_at
)
SELECT
contact_email,
'enterprise' AS account_type,
company_name,
created_at,
NOW()
FROM enterprise_accounts
"""))
# Step 3: Update existing customers type
connection.execute(sa.text("""
UPDATE customers SET account_type = 'individual'
WHERE account_type IS NULL
"""))
# Step 4: Make account_type NOT NULL
op.alter_column('customers', 'account_type',
nullable=False
)
def downgrade():
# Reverse: remove enterprise rows and columns
op.execute(
sa.text("DELETE FROM customers WHERE account_type = 'enterprise'")
)
op.drop_column('customers', 'company_name')
op.drop_column('customers', 'account_type')
Expected output: Table merge has multiple steps: add new columns, copy data from source table, update existing records, and enforce constraints. Each step is reversible. The downgrade removes enterprise data and columns.
Batch Processing for Large Tables
// migrations/20260628_batch_transform.js
exports.up = async (db) => {
const BATCH_SIZE = 5000;
// Use a cursor to avoid loading all rows into memory
const cursor = await db.query(
'DECLARE data_cursor CURSOR FOR SELECT id, raw_data FROM events WHERE processed = false'
);
let batch = [];
let totalProcessed = 0;
while (true) {
const { rows } = await db.query(
`FETCH ${BATCH_SIZE} FROM data_cursor`
);
if (rows.length === 0) break;
for (const row of rows) {
const transformed = JSON.stringify({
original: JSON.parse(row.raw_data),
timestamp: new Date().toISOString(),
version: 2,
});
batch.push(db.query(
'UPDATE events SET raw_data = $1, processed = true WHERE id = $2',
[transformed, row.id]
));
}
// Execute batch in parallel
await Promise.all(batch);
totalProcessed += rows.length;
console.log(`Processed ${totalProcessed} events`);
batch = [];
}
await db.query('CLOSE data_cursor');
console.log(`Completed: ${totalProcessed} events transformed`);
};
Expected output: Batch processing uses a database cursor to iterate through large tables without loading all rows into memory. Each batch is processed independently. Progress is logged at each batch.
Dry Run Mode
# migrations/20260628_dry_run_example.py
"""Demonstrate dry run mode for data migrations."""
def upgrade():
connection = op.get_bind()
# Check if dry run mode is enabled
dry_run = os.environ.get('DRY_RUN', 'false').lower() == 'true'
# Count affected rows
total = connection.execute(
sa.text("SELECT COUNT(*) FROM users WHERE full_name IS NULL")
).scalar()
print(f"Would update {total} users")
if dry_run:
print("Dry run mode - no changes applied")
print("SQL that would be executed:")
print("""
UPDATE users
SET full_name = CONCAT(first_name, ' ', last_name)
WHERE full_name IS NULL
""")
return # Do not execute in dry run
# Execute the actual update
connection.execute(sa.text("""
UPDATE users
SET full_name = CONCAT(first_name, ' ', last_name)
WHERE full_name IS NULL
"""))
print(f"Updated {total} users")
Expected output: Dry run mode shows what the migration would do without applying changes. Set DRY_RUN=true to preview. This is useful for reviewing data migrations before production execution.
Common Mistakes
1. Running Data Migrations Without a Transaction
Data migrations that run outside a transaction leave partial changes if the migration fails mid-way. Wrap data migrations in transactions. If the migration is large, use batch processing with checkpoint logic.
2. Not Testing on Production-Sized Data
A data migration that works on 1000 rows may fail on 1 million rows. Test on a production-sized copy. Check memory usage, execution time, and lock contention. Scale batch sizes accordingly.
3. Forgetting the Down Migration
Data migrations without down functions make rollback impossible. Always write down migrations that revert data changes. If reversal is impossible (destructive transformation), document this clearly and get approval.
4. Loading All Data into Memory
Processing all records in memory causes out-of-memory errors on large tables. Use batch processing with cursors or LIMIT/OFFSET. Process records in chunks of 1000-5000. Never load an entire table into application memory.
5. Ignoring Lock Contention
Long-running data updates lock tables and block application queries. Schedule data migrations during maintenance Windows. Use batching with small delays between batches. Consider online tools for large tables.
6. No Progress Logging
Data migrations that run silently give no indication of progress or failure. Log row counts, batch progress, and error details. Include timestamps. Send notifications for long-running migrations.
Practice Questions
1. What is the difference between a schema migration and a data migration?
A schema migration changes the database structure (add column, create table). A data migration transforms existing data (backfill values, normalize formats, merge records). Schema migrations are structural; data migrations are transformational.
2. Why should data migrations use batch processing?
Batch processing prevents memory overflow, reduces lock contention, enables progress tracking, and allows checkpoint recovery. Large datasets processed in a single transaction can lock tables for hours and consume excessive memory.
3. How do you handle irreversible data migrations?
Document the irreversibility clearly in the migration file. Get team approval before deployment. Ensure the down migration either reverts or explains why it cannot. Consider creating a backup before running irreversible migrations.
4. What is a dry run and why is it useful?
A dry run simulates the migration without applying changes. It shows affected rows, SQL statements, and potential issues. Dry runs allow review before production execution, reducing the risk of unexpected data changes.
Challenge
Build a data migration framework that: supports dry run mode, processes records in configurable batch sizes, logs progress with timestamps, handles errors gracefully without stopping, reports summary statistics (rows processed, errors, duration), and includes both up and down functions.
FAQ
Mini Project: Data Migration Pipeline
Build a data migration pipeline that: creates a new column via schema migration, backfills the column from existing data in batches of 5000, validates the transformed data, logs progress and errors, supports dry run mode, includes a tested down migration, and reports completion statistics.
What's Next
Now that you understand data migrations, learn about Rollbacks for reverting schema and data changes safely.
Built by the developers of DodaTech
Doda Browser, DodaZIP & Durga Antivirus Pro