🔄 Data Migration = Database Changes
Databases evolve. Data migration strategies handle database changes safely.
📝 Migration Strategies
# 1. Schema Migration
-- Create new table
CREATE TABLE new_table (
id INT PRIMARY KEY,
name VARCHAR(100)
);
-- Copy data
INSERT INTO new_table (id, name)
SELECT id, name FROM old_table;
-- Drop old table
DROP TABLE old_table;
# 2. Data Transformation
-- Add new column
ALTER TABLE users ADD COLUMN full_name VARCHAR(200);
-- Transform data
UPDATE users
SET full_name = CONCAT(first_name, ' ', last_name);
-- Remove old columns
ALTER TABLE users
DROP COLUMN first_name,
DROP COLUMN last_name;
# 3. Rolling Update
-- Add column with default
ALTER TABLE orders ADD COLUMN status VARCHAR(20) DEFAULT 'pending';
-- Update in batches
UPDATE orders
SET status = 'completed'
WHERE completed_at IS NOT NULL
LIMIT 1000;
🎯 Migration Tools
# Entity Framework Migrations
dotnet ef migrations add AddUserTable
dotnet ef database update
# Flyway
flyway -url=jdbc:postgresql://localhost:5432/mydb \
-user=postgres -password=password migrate
# Liquibase
liquibase --changeLogFile=db.changelog.xml \
--url=jdbc:postgresql://localhost:5432/mydb \
--username=postgres --password=password update
# Migration Script Template
-- 20240115_001_add_users_table.sql
-- Up Migration
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100)
);
-- 20240115_001_add_users_table.sql
-- Down Migration
DROP TABLE users;
💡 Migration Tips
- Always backup before migration
- Test migrations on staging
- Use version control for migrations
- Implement rollback strategy
- Monitor migration performance
Data migration requires careful planning. A good strategy minimizes downtime and data loss.

