Introduction to Database Migrations

In this tutorial, you will learn about Introduction to Database Migrations. We cover key concepts, practical examples, and best practices to help you master this topic.

Database migrations are version-controlled changes to database schemas that allow teams to evolve their data model incrementally, safely, and collaboratively across environments.

What You Learn

You will learn what database migrations are, why they are essential for application development, how they differ from manual schema changes, the key concepts of up and down migrations, and an overview of popular migration tools.

Why It Matters

Without migrations, schema changes are manual, error-prone, and unrepeatable. Team members have different schema versions. Deployments fail because production schema does not match code expectations. Migrations automate schema evolution and keep environments in sync.

Real-World Use

DodaTech's Durga Antivirus Pro uses Alembic migrations to manage schema across 5 environments (dev, test, staging, production, disaster recovery). Each deployment runs migrations automatically. Schema drift is detected and reported. Zero deployment failures due to schema mismatch in the last 12 months.

What Is a Database Migration?

graph LR
    V1[Schema v1] -->|Migration 001| V2[Schema v2]
    V2 -->|Migration 002| V3[Schema v3]
    V3 -->|Migration 003| V4[Schema v4]
    V4 -->|Migration 004| V5[Current Schema]

Each migration is a small, versioned change to the database schema. Migrations are applied in order to move from one schema version to the next.

Manual vs Automated Migrations

-- Manual approach (error-prone)
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
-- Did we run this on production?
-- What about staging?
-- What if we need to rollback?

-- Migration approach (controlled)
-- migration_001_add_phone.py
def upgrade():
    op.add_column('users', sa.Column('phone', sa.String(20)))

def downgrade():
    op.drop_column('users', 'phone')

Expected output: Manual SQL is unrepeatable and untracked. Migration files are version-controlled, reversible, and automatically applied in order across environments.

Migration File Structure

// Typical migration file naming
// 20260628_120000_add_phone_to_users.py
// 20260628_130000_create_orders_table.py
// 20260628_140000_add_foreign_key_to_orders.py

// Version formats
const versionFormats = [
    '001_add_phone.py',           // Sequential numbers
    '20260628_120000.py',         // Timestamps
    'v1.2.3_add_phone.py',        // Semantic version
    'abc123def_add_phone.py',     // Random hash
];

Expected output: Migration files are ordered by version number or timestamp. The migration tool applies them in sequence. Each file contains an upgrade (apply) and downgrade (revert) function.

Up and Down Migrations

# Up migration: apply the change
def upgrade():
    op.create_table(
        'orders',
        sa.Column('id', sa.Integer(), primary_key=True),
        sa.Column('user_id', sa.Integer(), nullable=False),
        sa.Column('total', sa.Float(), nullable=False),
        sa.Column('created_at', sa.DateTime(), server_default=sa.func.now()),
    )
    op.create_foreign_key('fk_orders_users', 'orders', 'users', ['user_id'], ['id'])

# Down migration: revert the change
def downgrade():
    op.drop_constraint('fk_orders_users', 'orders', type_='foreignkey')
    op.drop_table('orders')

Expected output: The upgrade function creates the orders table with a foreign key. The downgrade function drops the foreign key and table. Down migrations enable safe rollback to the previous schema version.

Common Migration Tools

Common Mistakes

1. Not Using Migrations

Skipping migrations and editing schema directly leads to environment drift. What works in development fails in production. Always use migrations for schema changes.

2. Reversing Migrations Without Down

Without down migrations, rollback requires manual schema editing or restoring from backup. Always write down migrations. Test them before deployment.

3. Migrating Without Backup

Migrations can fail mid-way, corrupting data. Always back up the database before running migrations in production. Test migrations on a staging copy first.

4. Editing Historical Migrations

Editing a migration that has already been applied creates inconsistency across environments. Never edit applied migrations. Create a new migration for changes.

5. Not Testing Migrations

Migrating against production without testing causes downtime. Run migrations against a staging copy. Test both up and down. Verify data integrity after migration.

Practice Questions

1. What is a database migration?

A version-controlled, reversible change to a database schema. Migrations are applied in order to evolve the schema from one version to the next, safely and consistently across environments.

2. Why do migrations need both up and down functions?

Up applies the change. Down reverts it. Down functions enable rollback to the previous schema version if a migration fails or needs to be reversed.

3. What is the difference between sequential and timestamp-based migration naming?

Sequential (001, 002) is simple but conflicts with concurrent development. Timestamp-based avoids conflicts but has longer names. Both ensure ordered application.

4. Why should you never edit an applied migration?

Editing an applied migration creates inconsistency. Different environments may have different versions of the same migration. Always create a new migration for subsequent changes.

Challenge

Design a migration Strategy for a new application. Define: naming convention (timestamp-based), migration directory structure, up/down pattern for all changes, review Process for migration PRs, automated testing in CI, and rollback procedure for production failures.

FAQ

Mini Project: Migration Setup

Set up a migration system for a new project: choose a migration tool (Alembic for Python, Flyway for Java, Knex for Node.js), initialize the migration directory, create the first migration (users table), create a second migration (add email verification column), test upgrade and downgrade, and commit to version control.

What's Next

Now that you understand migration basics, explore Why Migrations Matter for deeper insight into the problems migrations solve.