How to Manage Database Schema Migrations Safely

Deploy database changes without downtime. Covers blue-green migrations, backward compatibility, rollback strategies, and migration testing.

Database schema migrations are the process of evolving a database’s structure—adding tables, altering columns, creating indexes, changing constraints—across multiple environments and deployments. They matter most when multiple teams deploy application code while the database must stay available and consistent. A failed migration can stall a release, corrupt data, or cause downtime. This guide covers how to manage schema migrations safely: from development to production, with real commands, real failures, and real practices that prevent common pitfalls.

Plan and Version Migrations

Schema migrations must be versioned and reversible. Every change to the database schema must be a standalone, idempotent script, stored in version control. Use a consistent naming convention: YYYYMMDDHHMMSS-description.sql, where YYYYMMDDHHMMSS is a timestamp in UTC. For example:

-- 20240115103000-add-user-roles.sql
CREATE TABLE user_roles (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    role VARCHAR(50) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(user_id, role)
);

Each migration script must be idempotent: running it multiple times produces the same state. Use CREATE TABLE IF NOT EXISTS, CREATE INDEX IF NOT EXISTS, and ALTER TABLE ... ADD COLUMN IF NOT EXISTS.

To track which migrations have been applied, maintain a schema_migrations table:

CREATE TABLE schema_migrations (
    version VARCHAR(255) PRIMARY KEY,
    applied_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    checksum TEXT
);

After applying a migration, insert its version into this table. Use a migrate command that reads all unapplied scripts, applies them in order, and records success.

Apply Migrations with Idempotent Scripts

Apply migrations using a single command that runs all pending scripts in sequence. Use a script or tool that:

Example migrate script using psql and bash:

#!/bin/bash

DB_HOST=127.0.0.1
DB_PORT=5432
DB_NAME=app
DB_USER=app_user
MIGRATIONS_DIR=migrations

# Connect to database and get current schema version
current_versions=$(psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -t -c "
    SELECT version FROM schema_migrations ORDER BY version ASC
")

# Get all migration files, sorted
migration_files=($(find "$MIGRATIONS_DIR" -name "*.sql" -print0 | sort -z | xargs -0 -I {} basename {}))

# Apply each migration
for file in "${migration_files[@]}"; do
    version=$(echo "$file" | cut -d'-' -f1)
    if echo "$current_versions" | grep -q "^$version$"; then
        echo "Migration $version already applied. Skipping."
        continue
    fi

    echo "Applying migration $version..."
    psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -f "$MIGRATIONS_DIR/$file" --echo-queries

    if [ $? -ne 0 ]; then
        echo "Migration $version failed."
        exit 1
    fi

    # Insert version into schema_migrations
    psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -c "
        INSERT INTO schema_migrations (version, checksum)
        VALUES ('$version', (SELECT md5(string_agg(content, '')) FROM (SELECT content FROM pg_read_file('$MIGRATIONS_DIR/$file')) AS s))
    "

    echo "Migration $version applied successfully."
done

echo "All migrations applied."

This script applies migrations in order, logs each step, and exits with failure if any script fails. Use --echo-queries in psql to log SQL commands as they run.

Handle Rollbacks and Reversibility

Every migration must support a reverse operation. Create a rollback script for each migration, named rollback-YYYYMMDDHHMMSS-description.sql. For example:

-- rollback-20240115103000-add-user-roles.sql
DROP TABLE IF EXISTS user_roles CASCADE;

A migration tool must support migrate up and migrate down commands:

# Apply all migrations
./migrate.sh up

# Roll back the last migration
./migrate.sh down

Use a migrate down script that:

To handle partial rollbacks (e.g., roll back only the last three migrations), the tool must track the migration stack and allow down 3.

Use Transactions for Atomic Application

Wrap each migration in a transaction to ensure atomicity: either all changes apply successfully, or the entire migration fails and rolls back. A migration script should begin with BEGIN;, end with COMMIT;, and include ROLLBACK; on error.

For a migration script:

-- 20240116120000-add-user-profiles.sql
BEGIN;

CREATE TABLE user_profiles (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL UNIQUE REFERENCES users(id),
    bio TEXT,
    avatar_url TEXT,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_user_profiles_user_id ON user_profiles(user_id);

INSERT INTO schema_migrations (version, applied_at)
VALUES ('20240116120000-add-user-profiles', NOW());

COMMIT;

If any statement fails, the entire transaction rolls back. This guarantees consistency even if a migration is interrupted.

Deploy Migrations with Application Code

Schema migrations must be deployed alongside application code. Use a deployment pipeline that:

  1. Builds the application (e.g., go build -o app)
  2. Packages the migrations in the app bundle (migrations/ directory in the binary or archive)
  3. Deploys the app to the target environment (e.g., via kubectl apply -f deployment.yaml)
  4. Runs migrate up as part of the deployment

For example, a Dockerfile that includes migrations:

FROM golang:1.22-alpine AS builder

WORKDIR /app
COPY . .

RUN go build -o app

FROM alpine:latest
RUN apk --no-cache add postgresql-client

WORKDIR /app
COPY --from=builder /app/app .
COPY --from=builder /app/migrations ./migrations

CMD ["/app/app"]

In production, run migrate up after deploying the new app:

# In a deployment script
kubectl rollout restart deployment/app
# Wait for app to be ready
kubectl wait --for=condition=ready pod -l app=app --timeout=300s

# Run migrations
kubectl exec deployment/app -- /app/migrate.sh up

Fail the deployment if migrations fail.

Monitor Migration Health

After applying migrations, verify their health. Use a migrate verify command that:

Example health check script:

#!/bin/bash
# verify-migrations.sh

DB_HOST=127.0.0.1
DB_PORT=5432
DB_NAME=app
DB_USER=app_user

# Check that all expected migrations exist
expected_migrations=$(find migrations -name "*.sql" -exec basename {} \; | sort)

applied_migrations=$(psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -t -c "
    SELECT version FROM schema_migrations ORDER BY version ASC
")

if ! diff <(echo "$expected_migrations") <(echo "$applied_migrations"); then
    echo "Error: Migrations do not match expected set."
    exit 1
fi

# Check that key tables exist and have expected structure
psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -c "
    SELECT
        table_name,
        column_name,
        data_type,
        is_nullable
    FROM information_schema.columns
    WHERE table_name = 'user_profiles'
    ORDER BY ordinal_position;
"

# Check that indexes exist
psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -c "
    SELECT
        tablename,
        indexname,
        indexdef
    FROM pg_indexes
    WHERE tablename = 'user_profiles';
"

echo "All migrations verified."

Integrate this into the deployment pipeline. Fail the build or deployment if verify-migrations.sh returns non-zero.

Handle Downtime and Zero-Downtime Migrations

For zero-downtime deployments, apply migrations before the application starts and in parallel with old versions. Use a two-phase migration:

  1. Phase 1: Schema-only migration
  1. Phase 2: Data migration
  1. Phase 3: Switch to new schema

For a schema-only migration, use ALTER TABLE ... ADD COLUMN IF NOT EXISTS and CREATE INDEX IF NOT EXISTS so both old and new code can coexist.

Use CREATE INDEX CONCURRENTLY for large tables:

CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

This allows reads during index creation, minimizing downtime.

Handle Common Failure Modes

BEGIN;

CREATE TABLE new_table (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

-- Simulate a failure
INSERT INTO new_table (name) VALUES ('test'), ('fail'), ('done');
-- Suppose the second insert fails

EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE NOTICE 'Migration failed: %', SQLERRM;
        RAISE;
END;

Integrate with CI/CD

In CI, run migrations against a test database before merging to main. Use docker-compose up to spin up a test database:

# docker-compose.test.yml
version: '3.8'
services:
  db:
    image: postgres:15
    environment:
      POSTGRES_DB: test_app
      POSTGRES_USER: test_user
      POSTGRES_PASSWORD: test_pass
    volumes:
      - ./migrations:/migrations
    ports:
      - "5433:5432"
  app:
    build: .
    depends_on:
      - db
    command: |
      sh -c "
        psql -h db -U test_user -d test_app -c 'SELECT 1'
        && ./migrate.sh up
        && go test -v ./...
      "

Run migrate up in CI, and fail the build if any migration fails.

Final Checklist

With these practices, schema migrations become a reliable, repeatable, and safe part of the database lifecycle.

This page was rewritten on 17 October 2026. It replaced a templated version that was not specific to its own title. How these pages are written and checked. If anything here is wrong, tell us at [email protected] and we will correct it.