Skip to content

Database Migration Strategies — Alembic, Flyway, Prisma, and Knex

DodaTech Updated 2026-06-22 10 min read

In this tutorial, you'll learn about Database Migration Strategies. We cover key concepts, practical examples, and best practices to help you understand and apply this topic effectively.

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'll Learn

By the end of this tutorial, you will implement database migrations with Alembic (Python), Flyway (Java), Prisma Migrate (TypeScript), and Knex (Node.js), handle rollbacks, design zero-downtime migrations, and integrate migrations into CI/CD pipelines.

Why It Matters

Manual schema changes cause inconsistencies between environments, lost data, and deployment failures. Automated migrations ensure that every environment (dev, staging, production) has an identical schema that matches the application code version.

Real-World Use

Doda Browser's backend team runs Alembic migrations as part of their CI/CD pipeline. Each deployment automatically applies pending migrations before starting the new application version. Failed migrations trigger automatic rollback and alert the team.

Migration Flow

sequenceDiagram
    participant Dev as Developer
    participant App as Application
    participant DB as Database
    participant CI as CI/CD

    Dev->>Dev: Write migration script
    Dev->>Dev: Test locally
    Dev->>CI: Push to repository
    CI->>DB: Run migration (up)
    DB-->>CI: Schema updated
    CI->>App: Deploy new version
    App->>DB: Use new schema
    Note over CI,DB: If migration fails, rollback
    CI->>DB: Run migration (downgrade)

Each Migration is a reversible script (up/down) that is applied in order. The Migration tool tracks which migrations have been applied in a metadata table.

Table: Migration Tools

Tool Language Database Support Rollback Auto-generation
Alembic Python PostgreSQL, MySQL, SQLite Yes (downgrade) Partial
Flyway Java (any JVM) All major Yes (undo) No
Prisma Migrate TypeScript PostgreSQL, MySQL, SQLite Yes (drift) Full
Knex Node.js PostgreSQL, MySQL, SQLite Yes (rollback) No

Python: Alembic Migrations

# alembic/env.py
# Alembic configuration for async PostgreSQL
from logging.config import fileConfig
from sqlalchemy import engine_from_config, pool
from alembic import context
import asyncio
from sqlalchemy.ext.asyncio import AsyncEngine

config = context.config
if config.config_file_name is not None:
    fileConfig(config.config_file_name)

from app.models import Base
target_metadata = Base.metadata

def run_migrations_offline():
    """Run migrations in 'offline' mode."""
    url = config.get_main_option("sqlalchemy.url")
    context.configure(
        url=url,
        target_metadata=target_metadata,
        literal_binds=True,
        dialect_opts={"paramstyle": "named"},
    )
    with context.begin_transaction():
        context.run_migrations()

def run_migrations_online():
    """Run migrations in 'online' mode with async engine."""
    connectable = AsyncEngine(
        engine_from_config(
            config.get_section(config.config_ini_section, {}),
            prefix="sqlalchemy.",
            poolclass=pool.NullPool,
        )
    )
    asyncio.run(run_async_migrations(connectable))

async def run_async_migrations(connectable):
    async with connectable.connect() as connection:
        await connection.run_sync(do_run_migrations)

def do_run_migrations(connection):
    context.configure(connection=connection, target_metadata=target_metadata)
    with context.begin_transaction():
        context.run_migrations()

context.run_migrations()

# alembic/versions/0001_create_users_table.py
# Migration: Create users table
"""Create users table

Revision ID: 0001
Revises: None
Create Date: 2026-06-22 10:00:00
"""
from alembic import op
import sqlalchemy as sa

revision = '0001'
down_revision = None
branch_labels = None
depends_on = None

def upgrade():
    """Add users table with initial schema."""
    op.create_table(
        'users',
        sa.Column('id', sa.Integer(), primary_key=True),
        sa.Column('username', sa.String(50), nullable=False, unique=True),
        sa.Column('email', sa.String(255), nullable=False, unique=True),
        sa.Column('password_hash', sa.String(255), nullable=False),
        sa.Column('is_active', sa.Boolean(), default=True),
        sa.Column('created_at', sa.DateTime(), server_default=sa.func.now()),
        sa.Column('updated_at', sa.DateTime(), onupdate=sa.func.now()),
    )
    op.create_index('idx_users_email', 'users', ['email'])

def downgrade():
    """Remove users table."""
    op.drop_index('idx_users_email')
    op.drop_table('users')

# alembic/versions/0002_add_user_profile.py
# Migration: Add profile columns
"""Add profile columns to users table

Revision ID: 0002
Revises: 0001
Create Date: 2026-06-22 11:00:00
"""
from alembic import op
import sqlalchemy as sa

revision = '0002'
down_revision = '0001'

def upgrade():
    """Add profile fields non-nullable with defaults."""
    op.add_column('users', sa.Column('display_name', sa.String(100), nullable=True))
    op.add_column('users', sa.Column('bio', sa.Text(), nullable=True))
    op.add_column('users', sa.Column('avatar_url', sa.String(500), nullable=True))
    op.add_column('users', sa.Column('role', sa.String(20),
                  server_default='viewer', nullable=False))

def downgrade():
    """Remove profile columns."""
    op.drop_column('users', 'role')
    op.drop_column('users', 'avatar_url')
    op.drop_column('users', 'bio')
    op.drop_column('users', 'display_name')

Node.js: Knex Migrations

// knexfile.js
// Knex configuration for migrations
module.exports = {
  development: {
    client: 'postgresql',
    connection: {
      host: 'localhost',
      database: 'myapp_dev',
      user: 'postgres',
      password: 'postgres',
    },
    migrations: {
      directory: './migrations',
      tableName: 'knex_migrations',
    },
    seeds: {
      directory: './seeds',
    },
  },

  production: {
    client: 'postgresql',
    connection: {
      host: process.env.DB_HOST,
      database: process.env.DB_NAME,
      user: process.env.DB_USER,
      password: process.env.DB_PASSWORD,
      ssl: { rejectUnauthorized: false },
    },
    pool: {
      min: 2,
      max: 10,
    },
    migrations: {
      directory: './migrations',
      tableName: 'knex_migrations',
    },
  },
};

// migrations/20260622100001_create_users.js
// Knex migration: create users table
exports.up = function (knex) {
  return knex.schema
    .createTable('users', (table) => {
      table.increments('id').primary();
      table.string('username', 50).notNullable().unique();
      table.string('email', 255).notNullable().unique();
      table.string('password_hash', 255).notNullable();
      table.boolean('is_active').defaultTo(true);
      table.timestamp('created_at').defaultTo(knex.fn.now());
      table.timestamp('updated_at').defaultTo(knex.fn.now());
      table.index('email', 'idx_users_email');
    })
    .createTable('posts', (table) => {
      table.increments('id').primary();
      table.integer('user_id').unsigned().references('id').inTable('users');
      table.string('title', 200).notNullable();
      table.text('content').notNullable();
      table.timestamp('published_at').nullable();
      table.timestamps(true, true);
    });
};

exports.down = function (knex) {
  return knex.schema.dropTableIfExists('posts').dropTableIfExists('users');
};

Zero-Downtime Migration Pattern

# zero_downtime.py
# Zero-downtime migration strategy using expand-migrate-contract
"""
Zero-downtime migration follows three phases:

Phase 1 - Expand:
  - Add new columns as nullable
  - Create new tables
  - Deploy application code that writes to both old and new fields

Phase 2 - Migrate:
  - Backfill data into new columns
  - Verify data consistency
  - Deploy application code that reads from new fields only

Phase 3 - Contract:
  - Remove old columns
  - Drop old tables
  - Deploy final cleanup
"""

def expansion_migration():
    """Phase 1: Add new columns without breaking existing code."""
    op.add_column('users', sa.Column('email_verified', sa.Boolean(),
                  server_default=sa.text('false'), nullable=False))
    op.add_column('users', sa.Column('email_verified_at', sa.DateTime(),
                  nullable=True))

def backfill_migration():
    """Phase 2: Backfill data for existing rows."""
    connection = op.get_bind()
    connection.execute(
        sa.text("""
            UPDATE users
            SET email_verified = CASE
                WHEN email IS NOT NULL THEN true
                ELSE false
            END,
            email_verified_at = CASE
                WHEN email IS NOT NULL THEN NOW()
                ELSE NULL
            END
            WHERE email_verified IS NULL
        """)
    )

def contraction_migration():
    """Phase 3: Remove old columns after verifying new ones work."""
    op.drop_column('users', 'old_email_status')

Common Errors

1. Running Migrations During Peak Traffic

Running schema changes (especially ALTER TABLE with locks) during high traffic causes downtime. PostgreSQL's ADD COLUMN with a default value locks the table. Use SET DEFAULT separately and add columns without defaults.

2. Not Testing Rollbacks

A Migration without a tested rollback function is irreversible. Always write and test downgrade() before running upgrade(). Test rollbacks on a staging database with real data volume.

3. Skipping Migration Numbers

Migration tools apply scripts in order. Skipping a number or branching the sequence causes confusion. Use timestamps or sequential integers. Never reorder or delete applied migrations from the Repository.

4. Modifying Applied Migrations

Editing an already-applied Migration creates hash mismatches and breaks the Migration chain. If you need to change the schema, create a new Migration. Applied migrations are immutable history.

5. Not Locking Migrations in CI

Two parallel CI pipelines running migrations simultaneously cause race conditions. Use database-level advisory locks or a Migration Orchestration tool that acquires a lock before running migrations.

6. Forgetting to Backfill Data

Adding a non-nullable column with no default value fails on existing rows. Always add nullable columns first, backfill data, then add the NOT NULL constraint. This prevents Migration failures on large tables.

Practice Questions

1. What is the difference between a Migration and a seed?

A Migration changes the database schema (tables, columns, indexes). A seed populates the database with sample or reference data (default users, lookup tables). Migrations are applied in order; seeds are run independently.

2. How does Alembic track which migrations have been applied?

Alembic stores applied Migration revision IDs in the alembic_version table. When you run alembic upgrade head, Alembic reads this table, compares it to the Migration files, and applies any unapplied migrations in order.

3. What is a squashed Migration?

A squashed Migration combines many small migrations into a single Migration file. This simplifies the Migration chain for new environments (they apply one squash instead of 100+ individual migrations) while preserving the Migration history.

4. How do you handle data migrations along with schema migrations?

Use a data Migration script that runs after the schema Migration but within the same Transaction. For large datasets, use batch processing to avoid long-running locks. Always test data migrations with production-scale data volumes.

5. Challenge: Design a Migration strategy for renaming a column from username to handle in a users table with 10 million rows. The Migration must be zero-downtime. Implement the expand-migrate-contract pattern: (1) add handle column and dual-write application logic (2) backfill handle from username in batches (3) remove username column after verifying consistency. Include rollback procedures for each phase.

Mini Project: CI/CD Integration with Alembic

# .github/workflows/migrate.yml
# GitHub Actions workflow for database migrations
name: Database Migration

on:
  deployment:
    branches: [main]

jobs:
  migrate:
    runs-on: Ubuntu-latest
    environment: production

    services:
      postgres:
        image: postgres:16
        env:
          POSTGRES_DB: myapp_test
          POSTGRES_USER: postgres
          POSTGRES_PASSWORD: postgres
        ports:
          - 5432:5432
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5

    steps:
      - uses: actions/checkout@v4

      - name: Set up Python
        uses: actions/setup-Python@v5
        with:
          Python-version: '3.12'

      - name: Install dependencies
        run: |
          Python -m Pip install --upgrade Pip
          Pip install alembic SQLAlchemy psycopg2-binary

      - name: Test migrations
        env:
          DATABASE_URL: PostgreSQL://postgres:postgres@localhost:5432/myapp_test
        run: |
          alembic upgrade head
          echo "Migration applied successfully"

      - name: Test rollback
        env:
          DATABASE_URL: PostgreSQL://postgres:postgres@localhost:5432/myapp_test
        run: |
          alembic downgrade -1
          echo "Rollback successful"
          alembic upgrade head

      - name: Deploy to production
        if: github.ref == 'refs/heads/main'
        env:
          DATABASE_URL: ${{ secrets.DATABASE_URL }}
        run: |
          alembic upgrade head
          echo "Production Migration complete"

FAQ

Should I use ORM-based migrations or raw SQL? ORM-based migrations (Alembic, Prisma) handle type mappings across databases and auto-generate boilerplate. Raw SQL gives full control over performance (indexes, constraints, Partitioning). Use ORM for simple changes and raw SQL for complex operations.

How do I handle Migration conflicts in a team? Each developer creates migrations from the latest head. If two developers create migrations from the same BASE revision, the second one to Merge must Rebase their Migration. Use alembic merge to combine divergent branches.

What is the best way to handle secrets in Migration scripts? Never hardcode database credentials in Migration files. Use environment variables for the database URL. Store production secrets in a vault (AWS Secrets Manager, HashiCorp Vault) and inject them during CI/CD.

How do I test migrations against production data? Restore an anonymized production backup to a staging environment and run the Migration there. Monitor execution time, row locks, and error logs. This catches performance regressions before they reach production.

How often should I squash migrations? Squash migrations quarterly or when the Migration count exceeds 100. Old Migration files that are no longer relevant (schema versions from months ago) can be consolidated. Keep at least one year of Migration history.

Related Concepts

Prisma ORM
Backend Logging
Environment Configuration

What's Next

You now understand database Migration strategies. Next, learn about Prisma for schema management with auto-generated migrations, then explore environment configuration to manage database URLs across environments.

  • Practice daily — Set up Alembic or Knex in your current project and create your first Migration
  • Build a project — Build a Migration pipeline with GitHub Actions that tests migrations and rollbacks against a Postgres service container
  • Explore related topics — Check out database versioning strategies and schema drift detection tools

Built by the developers of Doda Browser, DodaZIP, and Durga Antivirus Pro.

← Previous API Versioning Strategies — URI, Header, and Query Parameter Approaches Next → Webhook Implementation and Reliability — Delivery, Retries, and Security

Built by the developers of DodaTech

Doda Browser, DodaZIP & Durga Antivirus Pro

Home Browse Backend