Database Migrations in Deployments

Your database schema is a shared, stateful asset, and treating it like a stateless code deploy is a one-way ticket to downtime. You've built a flawless pipeline that builds, tests, and deploys your application — but the moment it runs a new query against an outdated table, everything breaks. This lesson shows you how to handle database migrations in deployments so that schema changes and application code roll out together — safely, repeatably, and without manual SSH sessions.

The problem this lesson solves

Imagine your team ships a new feature that relies on a users.email_verified column. The code lands in production, but the database still has the old schema. Every query that references that column throws an error, and your endpoint starts returning 500s. You scramble to run the migration by hand — and hope you remember the exact command.

This is the classic schema drift problem. Your application code and your database schema have evolved independently, and the deployment has become a fragile choreography of manual steps. The pain gets worse as you scale:

• Multiple environments (staging, QA, production) each need the same migration applied in the same order.
• Rollbacks become guesswork when you don't know which migration is live.
• Concurrent deploys from different developers can conflict and corrupt the schema.

Pro tip: If you've ever heard "it works on my machine" — database migrations are the production-grade version of that. The schema is shared state, and without an automated process, every deploy is a gamble.

Core concept / mental model

Think of your database schema as a time machine for your data. Each migration is a small, versioned step that transforms the schema from one state to the next. The migration tool keeps a ledger — a table like schema_migrations — that records which steps have been applied. On deploy, the tool compares the ledger to the migration files in your codebase and applies only the missing ones, in order.

This is the same principle as version control for your database. Instead of one giant, irreversible change, you get a series of small, reversible steps. The deployment pipeline becomes:

code + migrations -> build -> test -> deploy code -> run migrations

The mental model is simple: the database is a state machine, and migrations are its state transitions. Each transition is deterministic — the same input (schema + migration files) always produces the same output (updated schema).

Key insight: Running migrations during deployment is not an optional extra; it's a core part of the release process, as critical as compiling code or running tests.

How it works step by step

Here's the high-level workflow for handling migrations in a deployment:

1. Write the migration — Add a new migration file to your codebase, with a unique version number (often a timestamp).
2. Run migrations in your CI pipeline — Before or after the app code is deployed, your CI job executes the migration command (e.g., alembic upgrade head).
3. Track applied migrations — The tool updates its ledger table in the database, recording the migration version.
4. Idempotence — Running the same migration twice is a no-op; the tool sees it's already applied and skips it.
5. Forward-only in production — Migrations are written to move the schema forward. Rollbacks are handled by deploying a previous version of the code, not by rewriting history.

Let's walk through a concrete example with Alembic, the Python migration tool for SQLAlchemy.

Step 1: Create a migration

# In your project root
alembic init migrations
# ... configure alembic.ini with your database URL
alembic revision -m "add email_verified column"

The generated file looks like this:

# migrations/versions/20240601120000_add_email_verified.py
from alembic import op
import sqlalchemy as sa

def upgrade():
    op.add_column('users', sa.Column('email_verified', sa.Boolean(), nullable=False, server_default='false'))

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

Step 2: Run migrations in your CI/CD pipeline

In GitHub Actions, you can add a job that runs after the build and before the app deploy:

jobs:
  deploy:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - name: Set up Python
        uses: actions/setup-python@v5
        with:
          python-version: '3.11'
      - name: Install dependencies
        run: pip install -r requirements.txt
      - name: Run database migrations
        env:
          DATABASE_URL: ${{ secrets.DATABASE_URL }}
        run: alembic upgrade head
      - name: Deploy application
        run: ./deploy.sh

The migration runs before the new code is live, ensuring the schema is ready for the new queries.

Pro tip: For zero-downtime deployments, consider running migrations before the code deploy, and design migrations to be backward-compatible — the old code can still run while the migration is in progress.

Hands-on walkthrough

Let's build a minimal but complete example. We'll use SQLite (already in Python) and Alembic.

Setup

mkdir mig-demo && cd mig-demo
python -m venv venv && source venv/bin/activate
pip install alembic sqlalchemy

Create alembic.ini and migrations/env.py (run alembic init migrations and adjust the URL). Then create a schema and a migration:

# models.py
from sqlalchemy import Column, Integer, String, Boolean, create_engine
from sqlalchemy.orm import declarative_base

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    email = Column(String, unique=True, nullable=False)
    email_verified = Column(Boolean, default=False)

Now create and run the migration:

alembic revision --autogenerate -m "add email_verified to users"
alembic upgrade head

To inspect the ledger:

sqlite3 users.db "SELECT * FROM alembic_version;"
# Output: (20240601120000,)

Now simulate a deployment — run the migration again:

alembic upgrade head
# Output: Running upgrade None -> 20240601120000, add email_verified to users
# (only runs once; second time it says "Running upgrade None -> ..." is not repeated)

Actually, the second run is a no-op because Alembic sees the version is already applied. The output shows nothing changes.

Full pipeline example

Here's a complete deploy script that handles both migrations and app start:

#!/bin/bash
set -e  # fail on any error

echo "Running migrations..."
alembic upgrade head

echo "Starting application..."
python app.py &

wait $!

This ensures the schema is updated before the app serves traffic.

Compare options / when to choose what

Different tools and strategies exist; here's when to choose which.

When to choose what:

• Alembic is the go-to for Python backends because it integrates with SQLAlchemy models and supports autogeneration.
• Flyway is excellent if you're working in a polyglot environment or you prefer writing SQL directly.
• Liquibase shines in larger enterprises with complex compliance needs.
• Avoid create_all in production — it doesn't track versions and will fail when you try to add a column.
• Manual SQL is only for emergencies; never a routine process.

Pro tip: Use expand-and-contract migrations for zero-downtime: first add new columns/constraints (backward-compatible), then deploy code, then in a later release remove old schema. This is more advanced but crucial for high-traffic systems.

Troubleshooting & edge cases

Here are common problems and their fixes.

1. Migration fails because a column already exists

ERROR: duplicate column name: email_verified

Fix: Check if the migration was partially applied. Use alembic current to see the actual state, and manually repair the version table if needed.

2. Migration order is wrong

When two developers create migrations with the same base revision, you get a branch. Solve by using alembic merge or by ensuring each migration is based on the latest head before pushing.

3. Running migrations in parallel causes deadlocks

If two deploy instances run the same migration simultaneously, you get duplicate errors. Use advisory locks (PostgreSQL) or ensure a single deploy job runs migrations.

4. Schema drift in staging vs. production

Staging has one schema, production another. Use alembic history and alembic current to verify, and always run alembic upgrade head from a clean checkout.

5. Downgrade doesn't work

If you didn't write a downgrade() function, alembic downgrade will fail. Always implement downgrades for reversible migrations.

6. Environment variables not set

KeyError: 'DATABASE_URL'

Ensure your CI job has the right secrets and env vars. Never hardcode database credentials in migration files.

Pro tip: Always run migrations in isolated jobs that have network access to the database, not on the same box that runs arbitrary code from PRs.

What you learned & what's next

You now understand the core problem of schema drift and the mental model of migrations as versioned state transitions. You saw the step-by-step workflow, went hands-on with Alembic, compared tools, and troubleshooted edge cases. You can apply this to any Python project and any CI/CD platform.

Next step: In the next lesson, you'll explore database rollback strategies — how to safely revert both code and schema when something goes wrong. That's the natural follow-up to mastering migrations.

Keep your migrations in version control, automate them in CI, and always run them idempotently. Your future self (and your users) will thank you.