Database Strategies for Development

Version control, migrations, and deployment best practices

Why Database Strategies Matter

Database schema changes are some of the riskiest changes in software development. A bad migration can bring down production, corrupt data, or cause data loss. Professional teams treat database changes with the same rigor as code: version control, code reviews, automated testing, and careful deployment. This lesson covers the essential strategies for managing database evolution safely across development, staging, and production environments.

The Problem: Manual Schema Changes

Without a formal strategy, teams often make schema changes manually in each environment.

The Wrong Way (Manual Changes)

Developer A makes changes in dev database:
  - Adds 'phone_number' column to users table
  - Forgets to document the change

Developer B pulls latest code:
  - Code expects 'phone_number' column
  - Their local database doesn't have it
  - Application crashes with "column does not exist" error

Production deployment:
  - Someone manually runs ALTER TABLE in production
  - Typo in the command causes downtime
  - No rollback plan prepared
  - Customer data at risk

❌ Result: Broken environments, production incidents, data loss risk

The Right Way (Version Controlled Migrations)

Developer A creates migration file:
  - 001_add_phone_number.py (versioned in git)
  - Includes both upgrade and downgrade logic
  - Commits to repository

Developer B pulls latest code:
  - Runs 'alembic upgrade head'
  - Migration automatically applied to local database
  - Application works immediately

Production deployment:
  - CI/CD pipeline runs migration automatically
  - Same tested migration that worked in dev/staging
  - Rollback available if needed
  - Zero downtime with proper planning

✅ Result: Consistent databases, safe deployments, happy team

The Key Difference: Schema changes are treated as code, versioned, reviewed, tested, and deployed automatically, not done manually in each environment.

Database Migrations with Alembic

Alembic is a database migration tool for SQLAlchemy that provides version control for your database schema. Think of it as "git for your database structure."

Setting Up Alembic

First, install Alembic and initialize it in your project.

# Install Alembic
pip install alembic sqlalchemy psycopg2-binary

# Initialize Alembic in your project
alembic init alembic

✅ Output: Creates Alembic directory structure

my_project/
├── alembic/
│   ├── versions/          # Migration files go here
│   ├── env.py            # Alembic configuration
│   └── script.py.mako    # Template for new migrations
├── alembic.ini           # Alembic settings
└── models.py             # Your SQLAlchemy models

Configure Database Connection

Update alembic.ini with your database URL.

# alembic.ini

# Development database
sqlalchemy.url = postgresql://user:password@localhost/myapp_dev

# Or use environment variable (recommended)
# sqlalchemy.url = driver://user:pass@localhost/dbname

✅ Better: Use environment variables

In alembic/env.py:
from os import environ
config.set_main_option('sqlalchemy.url',
                       environ.get('DATABASE_URL'))

Then set DATABASE_URL in your environment:
  Development:  postgresql://user:pass@localhost/myapp_dev
  Staging:      postgresql://user:pass@staging-db/myapp_staging
  Production:   postgresql://user:pass@prod-db/myapp_prod

Creating Your First Migration

Define your models first, then create a migration.

# models.py
from sqlalchemy import Column, Integer, String, DateTime
from sqlalchemy.ext.declarative import declarative_base
from datetime import datetime

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    email = Column(String(255), unique=True, nullable=False)
    username = Column(String(100), nullable=False)
    created_at = Column(DateTime, default=datetime.utcnow)

# Generate migration automatically from models
alembic revision --autogenerate -m "create users table"

✅ Alembic creates: alembic/versions/001_create_users_table.py

"""create users table

Revision ID: abc123
Revises:
Create Date: 2024-01-15 10:30:00

"""
from alembic import op
import sqlalchemy as sa

def upgrade():
    op.create_table('users',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('email', sa.String(255), nullable=False),
        sa.Column('username', sa.String(100), nullable=False),
        sa.Column('created_at', sa.DateTime(), nullable=True),
        sa.PrimaryKeyConstraint('id'),
        sa.UniqueConstraint('email')
    )

def downgrade():
    op.drop_table('users')

Applying Migrations

# Apply all pending migrations
alembic upgrade head

# Upgrade to a specific revision
alembic upgrade abc123

# Show current version
alembic current

# Show migration history
alembic history

✅ Output when running 'alembic upgrade head':

INFO  [alembic.runtime.migration] Running upgrade  -> abc123, create users table

Database Changes Applied:
✓ users table created
✓ id, email, username, created_at columns added
✓ Primary key constraint on id
✓ Unique constraint on email

Current revision: abc123

Rolling Back Migrations

If something goes wrong, you can rollback to a previous state.

# Rollback one migration
alembic downgrade -1

# Rollback to specific revision
alembic downgrade abc123

# Rollback all migrations
alembic downgrade base

✅ Output when running 'alembic downgrade -1':

INFO  [alembic.runtime.migration] Running downgrade abc123 ->

Database Changes Reverted:
✓ users table dropped
✓ All data in users table removed (be careful!)

Current revision: base (no migrations applied)

Advanced Migration Patterns

Adding a Column with Data Migration

When adding a column, you often need to populate it with data.

# Create migration
alembic revision -m "add user status column"

# alembic/versions/002_add_user_status_column.py
from alembic import op
import sqlalchemy as sa

def upgrade():
    # Add the column (nullable first to avoid errors)
    op.add_column('users',
        sa.Column('status', sa.String(20), nullable=True)
    )

    # Populate existing rows with default value
    op.execute("UPDATE users SET status = 'active' WHERE status IS NULL")

    # Now make it non-nullable
    op.alter_column('users', 'status', nullable=False)

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

✅ Three-step process for safe column addition:

1. Add column as nullable
   - Existing rows won't break

2. Populate with data
   - All existing users get 'active' status

3. Make non-nullable
   - Future rows must have a status

This pattern prevents errors when table has existing data.

Renaming a Column Safely

Renaming requires careful coordination with application code.

# Migration 003: Step 1 - Add new column
def upgrade():
    op.add_column('users',
        sa.Column('full_name', sa.String(200), nullable=True)
    )
    # Copy data from old column
    op.execute("UPDATE users SET full_name = username")
    op.alter_column('users', 'full_name', nullable=False)

# Migration 004: Step 2 - Drop old column (deploy later)
def upgrade():
    op.drop_column('users', 'username')

✅ Safe rename process:

Deploy 1: Migration 003
  - Add 'full_name' column
  - Copy data from 'username'
  - Update code to write to BOTH columns
  - Monitor for issues

Deploy 2: After traffic is stable
  - Update code to only use 'full_name'
  - Stop writing to 'username'

Deploy 3: After monitoring
  - Migration 004: Drop 'username' column

Why this matters: Zero downtime! Old code keeps working.

Large Table Migrations

Adding indexes or columns to tables with millions of rows requires special care.

# DON'T do this on a large table:
def upgrade():
    op.create_index('idx_users_email', 'users', ['email'])
    # This locks the table and can take hours!

# DO this instead (PostgreSQL):
def upgrade():
    # Create index concurrently - no table lock
    op.create_index(
        'idx_users_email',
        'users',
        ['email'],
        postgresql_concurrently=True
    )

# Note: Must run outside transaction
def upgrade():
    connection = op.get_bind()
    connection.execute('COMMIT')  # End transaction
    connection.execute(
        'CREATE INDEX CONCURRENTLY idx_users_email ON users(email)'
    )

✅ Results:

Regular CREATE INDEX:
  - Locks table for writes
  - 10M row table = 30 minutes of downtime
  - ❌ Unacceptable for production

CREATE INDEX CONCURRENTLY:
  - No table lock
  - Takes longer but app keeps running
  - ✅ Zero downtime

Always use CONCURRENTLY for production large tables!

Environment Separation

Professional teams maintain separate databases for Development, Staging, and Production. This prevents developers from breaking production and allows safe testing.

Three-Tier Environment Setup

# config.py - Environment-based configuration
import os

class Config:
    # Base configuration
    SQLALCHEMY_TRACK_MODIFICATIONS = False

class DevelopmentConfig(Config):
    DEBUG = True
    DATABASE_URL = 'postgresql://localhost/myapp_dev'
    # Use local PostgreSQL for development

class StagingConfig(Config):
    DEBUG = False
    DATABASE_URL = os.environ.get('STAGING_DATABASE_URL')
    # Use staging database (could be Aurora, RDS, etc.)

class ProductionConfig(Config):
    DEBUG = False
    DATABASE_URL = os.environ.get('DATABASE_URL')
    # Use production database (Aurora, RDS, etc.)

# Select config based on environment
config = {
    'development': DevelopmentConfig,
    'staging': StagingConfig,
    'production': ProductionConfig
}

current_config = config[os.environ.get('APP_ENV', 'development')]

✅ Environment characteristics:

Development (Local):
  - Database: PostgreSQL on laptop
  - Data: Fake/test data
  - Purpose: Day-to-day coding
  - Migrations: Run freely, experiment

Staging (Cloud):
  - Database: AWS RDS or Aurora (same as prod)
  - Data: Copy of production data (sanitized)
  - Purpose: Test before production
  - Migrations: Exactly as they'll run in prod

Production (Cloud):
  - Database: AWS Aurora (high availability)
  - Data: Real customer data
  - Purpose: Live application
  - Migrations: Only after staging success

Cost-Effective Development Setup

You don't need to run expensive cloud databases for every developer.

Cost-effective approach:

Local Development:
  💰 Cost: $0
  🔧 Tool: Docker + PostgreSQL
  📝 Setup: docker run -p 5432:5432 -e POSTGRES_PASSWORD=postgres postgres:15

  Each developer has their own database
  No cloud costs during development
  Fast iteration and testing

Shared Staging:
  💰 Cost: ~$50-200/month
  🔧 Tool: AWS RDS PostgreSQL (db.t3.small)

  One database shared by team
  Testing ground before production
  Production-like but not production

Production:
  💰 Cost: $500-5000+/month
  🔧 Tool: AWS Aurora PostgreSQL (multi-AZ)

  High availability, automatic failover
  Backups, monitoring, performance tuning
  This is where you invest money

Alternative: Use PostgreSQL locally and in staging,
             only use Aurora for production.
             PostgreSQL is compatible with Aurora!

Database Schema Separation

Within a single database, you can use schemas to separate environments.

-- Create separate schemas in one database
CREATE SCHEMA dev;
CREATE SCHEMA staging;
CREATE SCHEMA prod;

-- Users table in each environment
CREATE TABLE dev.users (...);
CREATE TABLE staging.users (...);
CREATE TABLE prod.users (...);

-- Set search path based on environment
SET search_path TO dev;  -- Development
SET search_path TO staging;  -- Staging
SET search_path TO prod;  -- Production

✅ Schema separation benefits:

Pros:
  - Single database server to manage
  - Lower cost than separate databases
  - Easy to copy data between environments

Cons:
  - Less isolation (one database failure affects all)
  - More complex permission management
  - Not recommended for production

Best for: Small teams, early stage projects
Use separate databases when: You have budget and need isolation

CI/CD Integration

Automate your database migrations in your CI/CD pipeline for consistent, safe deployments.

GitHub Actions Example

Automatically run migrations on every deployment.

# .github/workflows/deploy.yml
name: Deploy to Production

on:
  push:
    branches: [main]

jobs:
  deploy:
    runs-on: ubuntu-latest

    steps:
      - name: Checkout code
        uses: actions/checkout@v3

      - name: Set up Python
        uses: actions/setup-python@v4
        with:
          python-version: '3.11'

      - name: Install dependencies
        run: |
          pip install -r requirements.txt

      - name: Run migrations
        env:
          DATABASE_URL: ${{ secrets.PRODUCTION_DATABASE_URL }}
        run: |
          alembic upgrade head

      - name: Deploy application
        run: |
          # Your deployment commands here
          echo "Deploying application..."

✅ What happens on every push to main:

1. Code is checked out
2. Python environment set up
3. Dependencies installed
4. Migrations run automatically
   - Connects to production database
   - Applies any pending migrations
   - Fails if migration has errors
5. Application deployed only if migrations succeed

If migration fails:
  ❌ Deployment stops immediately
  ❌ Application not updated
  ✅ Production remains stable

Database and code always in sync!

Multi-Stage Deployment Pipeline

Test migrations in staging before production.

# .github/workflows/deploy-staged.yml
name: Staged Deployment

on:
  push:
    branches: [main]

jobs:
  deploy-staging:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v3
      - uses: actions/setup-python@v4

      - name: Deploy to Staging
        env:
          DATABASE_URL: ${{ secrets.STAGING_DATABASE_URL }}
        run: |
          pip install -r requirements.txt
          alembic upgrade head
          # Deploy to staging server

      - name: Run integration tests
        run: |
          pytest tests/integration/

  deploy-production:
    needs: deploy-staging
    runs-on: ubuntu-latest
    environment: production  # Requires approval

    steps:
      - uses: actions/checkout@v3
      - uses: actions/setup-python@v4

      - name: Deploy to Production
        env:
          DATABASE_URL: ${{ secrets.PRODUCTION_DATABASE_URL }}
        run: |
          pip install -r requirements.txt
          alembic upgrade head
          # Deploy to production server

✅ Safe deployment process:

Step 1: Staging Deployment (Automatic)
  → Run migrations on staging database
  → Deploy code to staging
  → Run integration tests
  → If tests pass, continue
  → If tests fail, stop (production protected)

Step 2: Production Deployment (Requires Approval)
  → Team reviews staging results
  → Manual approval required
  → Same migrations run on production
  → Same code deployed
  → Confidence high because staging succeeded

Time delay between stages: 15 minutes to 24 hours
Gives time to catch issues before production!

Rollback Strategy in CI/CD

# Manual rollback script
# scripts/rollback.sh

#!/bin/bash
set -e

echo "Rolling back last migration..."

# Get current revision
CURRENT=$(alembic current | grep -o '[a-f0-9]\{12\}')
echo "Current revision: $CURRENT"

# Rollback one step
alembic downgrade -1

echo "Rollback complete!"
echo "New revision: $(alembic current)"

# Restart application
echo "Restarting application..."
# Your restart command here

✅ When to rollback:

Immediate rollback needed if:
  - Migration caused production errors
  - Data corruption detected
  - Application crashes after deployment

How to execute:
  $ ./scripts/rollback.sh

  Rolling back last migration...
  Current revision: def456
  Running downgrade def456 -> abc123
  Rollback complete!
  New revision: abc123
  Restarting application...

Keep rollback scripts ready and tested!
Practice rollbacks in staging regularly.

Database Development Best Practices

✅ Always Use Migrations

Never make manual schema changes. Every change must go through a versioned migration file. This ensures all environments stay in sync and changes are reviewable.

✅ Write Both Upgrade and Downgrade

Every migration must have a downgrade path. If something breaks in production, you need to be able to roll back quickly. Test your downgrade functions!

✅ Test Migrations on Production-Like Data

A migration that works on 100 rows might fail on 10 million rows. Always test with staging data that matches production volume and characteristics.

✅ Use Transactions

Wrap migrations in transactions so they automatically rollback if anything fails. Exception: Operations like CREATE INDEX CONCURRENTLY must run outside transactions.

✅ Small, Incremental Changes

Don't bundle 10 schema changes into one migration. Each migration should do one thing. This makes debugging easier and rollbacks safer.

✅ Separate Environments

Use different databases for dev, staging, and production. Never test directly in production. Even read queries can impact performance during peak hours.

✅ Automate Everything

Manual deployments lead to mistakes. Let CI/CD run your migrations automatically. Humans are for review and approval, not running commands.

✅ Monitor After Deployment

Watch your application for at least 30 minutes after running a migration. Check error rates, response times, and database performance metrics.

✅ Have a Backup Plan

Always have a recent backup before running migrations. Know your recovery time objective (RTO) and recovery point objective (RPO). Practice disaster recovery.

✅ Code Review Migrations

Treat migration files like critical code. Require peer review. Have a senior developer or DBA review any destructive operations (DROP, ALTER, DELETE).

Common Pitfalls to Avoid

❌ Skipping Migrations "Just This Once"

"I'll just add this column manually in prod, it's quick!"

Why it's bad: Now dev and prod are out of sync. Next developer pulls code expecting the column and their app crashes. Always use migrations, no exceptions.

❌ Editing Old Migrations

"I'll just edit the migration that's already in production."

Why it's bad: That migration already ran in prod. Editing it won't change production, but will break new environments. Create a new migration instead.

❌ Not Testing Rollbacks

"I wrote the downgrade function but never tested it."

Why it's bad: When production breaks at 3am, your rollback will fail too. Test both upgrade and downgrade paths before deploying.

❌ Dropping Columns Too Quickly

"Renaming username to full_name in one migration."

Why it's bad: The moment the migration runs, old code breaks. Use the multi-step pattern: add new column, deploy code, wait, then remove old column.

❌ Locking Large Tables

"Adding an index to a 50 million row table without CONCURRENTLY."

Why it's bad: Table locks for hours. All writes fail. Users can't sign up, can't checkout, revenue lost. Always use CONCURRENTLY for large tables.

❌ No Backup Before Migration

"Our daily backup is enough, right?"

Why it's bad: Migration corrupts data at 2pm. Last backup was midnight. You've lost 14 hours of customer data. Always take a fresh backup right before risky migrations.

Real-World Example: Adding Email Verification

Let's walk through a complete example of adding email verification to an existing users table with millions of rows, deployed safely to production.

Step 1: Plan the Change

Requirements:
  - Add 'email_verified' boolean column
  - Add 'verification_token' string column
  - Add 'verified_at' timestamp column

Challenges:
  - Table has 5 million existing users
  - Can't lock table (production traffic)
  - Need to set defaults for existing users
  - Need to coordinate with code changes

Strategy:
  - Use CONCURRENTLY for indexes
  - Add columns with defaults
  - Deploy in stages

Step 2: Create Migration

# alembic revision -m "add email verification"
# alembic/versions/005_add_email_verification.py

from alembic import op
import sqlalchemy as sa

def upgrade():
    # Add columns with safe defaults
    op.add_column('users',
        sa.Column('email_verified', sa.Boolean(),
                  nullable=False, server_default='false')
    )
    op.add_column('users',
        sa.Column('verification_token', sa.String(64),
                  nullable=True)
    )
    op.add_column('users',
        sa.Column('verified_at', sa.DateTime(),
                  nullable=True)
    )

    # Mark all existing users as verified
    # (grandfather them in)
    op.execute("""
        UPDATE users
        SET email_verified = true,
            verified_at = created_at
        WHERE email_verified = false
    """)

    # Add index for lookup by token (CONCURRENTLY)
    # Note: Must run outside transaction
    op.execute('COMMIT')
    op.execute("""
        CREATE INDEX CONCURRENTLY
        idx_users_verification_token
        ON users(verification_token)
    """)

def downgrade():
    op.drop_index('idx_users_verification_token')
    op.drop_column('users', 'verified_at')
    op.drop_column('users', 'verification_token')
    op.drop_column('users', 'email_verified')

✅ Safe migration strategy:

1. Add columns with defaults
   - Won't break existing queries
   - New rows automatically get false

2. Backfill existing data
   - Old users marked as verified
   - Prevents forcing verification on existing users

3. Create index CONCURRENTLY
   - No table lock
   - Can take 10-15 minutes on large table
   - Production keeps running

4. Downgrade path ready
   - Can rollback if needed

Step 3: Test in Staging

# Copy production data to staging (sanitized)
pg_dump production_db | pg_restore staging_db

# Run migration in staging
export DATABASE_URL="postgresql://staging-db/myapp_staging"
alembic upgrade head

# Verify
psql staging_db -c "
    SELECT
        COUNT(*) as total_users,
        COUNT(*) FILTER (WHERE email_verified) as verified,
        COUNT(*) FILTER (WHERE NOT email_verified) as unverified
    FROM users
"

✅ Staging results:

Migration completed in 12 minutes
 total_users | verified | unverified
-------------+----------+------------
   5,000,000 | 5,000,000|          0

Checks:
  ✓ All existing users marked verified
  ✓ Index created successfully
  ✓ No table locks during migration
  ✓ Application still works
  ✓ Queries using new columns work

Ready for production!

Step 4: Deploy to Production

Deployment checklist:

Before deployment:
  ✅ Create fresh backup
  ✅ Staging tests passed
  ✅ Team notified of deployment
  ✅ Monitoring dashboard open
  ✅ Rollback script ready

During deployment:
  → CI/CD automatically runs: alembic upgrade head
  → Monitor logs for errors
  → Watch database metrics (CPU, locks, connections)
  → Migration completes in 13 minutes

After deployment:
  ✅ Check error rates (normal)
  ✅ Check response times (normal)
  ✅ Test user registration (works)
  ✅ Test email verification flow (works)
  ✅ Monitor for 30 minutes (stable)

Success! New feature live without downtime.

Step 5: Update Application Code

# models.py - Updated after migration deployed
from sqlalchemy import Column, Boolean, String, DateTime
from datetime import datetime
import secrets

class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    email = Column(String(255), unique=True, nullable=False)
    email_verified = Column(Boolean, default=False)
    verification_token = Column(String(64), nullable=True)
    verified_at = Column(DateTime, nullable=True)

    def generate_verification_token(self):
        self.verification_token = secrets.token_urlsafe(48)
        return self.verification_token

    def verify_email(self):
        self.email_verified = True
        self.verified_at = datetime.utcnow()
        self.verification_token = None

✅ Code deployed after migration:

Sequence:
  1. Migration adds database columns
  2. Deploy code that uses new columns
  3. New users get email verification
  4. Existing users already marked verified

Why this order matters:
  - If code deployed first: crashes (columns don't exist)
  - If migration deployed first: works (code optional)

Always: Database schema first, code second!

Key Takeaways

Version control is mandatory - every schema change must be in a migration file, never manual
Alembic manages migrations - upgrade/downgrade paths, version tracking, safe rollbacks
Separate environments - dev/staging/prod with proper isolation prevents production disasters
Automate deployments - CI/CD runs migrations consistently, humans review and approve
Test in production-like data - staging must match production volume and characteristics
Use transactions - automatic rollback on failure (except CONCURRENTLY operations)
CONCURRENTLY for large tables - avoid locking tables with millions of rows
Multi-step for breaking changes - add new, wait, remove old - never break running code
Always have rollback ready - test downgrade paths, have backups, practice recovery
Monitor after deployment - watch metrics for 30+ minutes, be ready to rollback
Database deployment is the riskiest part of releases,invest time in getting it right and your production will stay stable

Back to "Performance & Optimization"

Database FundamentalsLesson 13

Course Completed!