SQL MIGRATIONS

A Complete Course
with PostgreSQL & Go

9 Chapters Full Examples Best Practices

Core Concepts to Production Real-world Go + Postgres Code Patterns Used in Production
 CH 01 What Are SQL Migrations?

Chapter 1: What Are SQL Migrations?

A SQL migration is a versioned, incremental change to your database schema. Instead of manually running
ALTER TABLE statements or editing tables ad-hoc, migrations capture every schema change as code —
committed to version control, reviewable, repeatable, and reversible.

1.1 The Problem Migrations Solve

Early in a project it is tempting to just open a database GUI and make changes. This breaks down quickly:
• No history — you cannot tell what the schema looked like last week.
• No reproducibility — spinning up a new dev environment requires manual steps.
• No coordination — two engineers changing the schema simultaneously cause conflicts.
• No rollback — if a deployment breaks prod, reverting the schema is ad-hoc and risky.

Migrations solve all of this by treating the database schema exactly like application code.

1.2 Up and Down Migrations

Every migration has two halves:
• Up — applies the change (adds a table, column, index, etc.).
• Down — reverts the change (drops the table, column, etc.).

The down migration is what allows you to roll back a bad deployment in seconds.

-- 0001_create_users.[Link]
CREATE TABLE users (
id BIGSERIAL PRIMARY KEY,
email TEXT NOT NULL UNIQUE,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 0001_create_users.[Link]
DROP TABLE users;

1.3 The Migration Table

A migration tool tracks which migrations have been applied by storing version numbers in a dedicated table —
typically called schema_migrations or migrations. Before running any migration the tool checks this table to
decide what still needs to be applied.

-- What a schema_migrations table looks like

CREATE TABLE schema_migrations (
version BIGINT PRIMARY KEY,
applied_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

💡 TIP Think of the migration table as a ledger. Every entry is a fact: this change was applied at
this time. Never edit it manually.

1.4 Migration Numbering Strategies

There are two common numbering approaches:

Strategy Example Notes

Sequential int 0001, 0002, 0003 Simple; can conflict on teams

Timestamp 20240315120000 No conflicts; harder to read

order
 CH 02 Tooling: golang-migrate

Chapter 2: Tooling — golang-migrate

golang-migrate is the de-facto standard migration library for Go. It supports PostgreSQL, MySQL, SQLite, and
many others, and can be used both as a CLI tool and as a Go library embedded directly in your application.

2.1 Installation
# Install the CLI
go install -tags 'postgres' [Link]/golang-migrate/migrate/v4/cmd/migrate@latest

# Or as a Go module dependency
go get -u [Link]/golang-migrate/migrate/v4
go get -u [Link]/golang-migrate/migrate/v4/database/postgres
go get -u [Link]/golang-migrate/migrate/v4/source/file

2.2 Project Layout

Keep migration files in a dedicated directory. A typical layout:
myapp/
├── cmd/
│ └── [Link]
├── internal/
│ └── db/
│ └── [Link] ← migration runner
└── migrations/
├── 0001_create_users.[Link]
├── 0001_create_users.[Link]
├── 0002_add_posts.[Link]
└── 0002_add_posts.[Link]

2.3 CLI Usage

The CLI is useful during development and in CI/CD pipelines.
# Apply all pending migrations
migrate -path ./migrations -database "postgres://user:pass@localhost:5432/mydb?
sslmode=disable" up

# Roll back the last applied migration

migrate -path ./migrations -database $DATABASE_URL down 1

# Check current version

migrate -path ./migrations -database $DATABASE_URL version

# Jump to a specific version

migrate -path ./migrations -database $DATABASE_URL goto 5
 # Force set version (use with caution — skips the migration itself)
migrate -path ./migrations -database $DATABASE_URL force 3

2.4 Embedding in Your Go Application

Running migrations at application startup ensures your schema is always in sync with your code. Here is a
complete, production-ready [Link]:
package db

import (
"errors"
"fmt"
"log"

"[Link]/golang-migrate/migrate/v4"
_ "[Link]/golang-migrate/migrate/v4/database/postgres"
_ "[Link]/golang-migrate/migrate/v4/source/file"
)

// RunMigrations applies all pending UP migrations.

// Call this once during application startup, before serving traffic.
func RunMigrations(databaseURL, migrationsPath string) error {
m, err := [Link](
"[Link]
databaseURL,
)
if err != nil {
return [Link]("creating migrator: %w", err)
}
defer [Link]()

if err := [Link](); err != nil &&  {

return [Link]("running migrations: %w", err)
}

v, _, _ := [Link]()
[Link]("database schema at version %d", v)
return nil
}

2.5 Calling RunMigrations from [Link]

func main() {
cfg := [Link]() // reads DATABASE_URL, MIGRATIONS_PATH from env

if err := [Link]([Link], [Link]); err != nil {

[Link]("migrations failed: %v", err)
}

// ... start HTTP server etc.

}

⚠️ Never embed RunMigrations inside a request handler. Run it exactly once at startup.
WARNING
Running it on every request adds latency and can cause race conditions.
 CH 03 Writing Good Migration Files

Chapter 3: Writing Good Migration Files

3.1 One Concern Per Migration
Each migration file should do exactly one logical thing. Do not bundle "add posts table" and "add index on
[Link]" into the same file. Keeping them separate makes rollbacks surgical and history readable.

3.2 Common DDL Patterns

Creating a Table
-- 0002_create_posts.[Link]
CREATE TABLE posts (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
title TEXT NOT NULL,
body TEXT NOT NULL DEFAULT '',
published BOOLEAN NOT NULL DEFAULT FALSE,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Always add indexes for FK columns you will filter/join on

CREATE INDEX idx_posts_user_id ON posts(user_id);

-- 0002_create_posts.[Link]
DROP TABLE IF EXISTS posts;

Adding a Column
-- 0003_add_users_name.[Link]
-- Add with a default so existing rows are immediately valid
ALTER TABLE users ADD COLUMN name TEXT NOT NULL DEFAULT '';

-- 0003_add_users_name.[Link]
ALTER TABLE users DROP COLUMN IF EXISTS name;

Renaming a Column (safe pattern)

Renaming directly causes downtime if application code still references the old name. The safe pattern is to add a
new column, backfill, and drop the old one across separate deployments.
-- 0004_rename_users_email_step1.[Link]
-- Step 1: add the new column and copy data
 ALTER TABLE users ADD COLUMN email_address TEXT;
UPDATE users SET email_address = email;
ALTER TABLE users ALTER COLUMN email_address SET NOT NULL;
CREATE UNIQUE INDEX idx_users_email_address ON users(email_address);

-- Step 2 (next deploy): drop the old column

-- In a SEPARATE migration file deployed after app code is updated

Adding an Index
-- 0005_idx_posts_published.[Link]

-- CONCURRENTLY avoids locking the table on large datasets

-- Note: cannot be run inside a transaction block
CREATE INDEX CONCURRENTLY IF NOT EXISTS
idx_posts_published ON posts(published)
WHERE published = TRUE;

💡 TIP Prefix CREATE INDEX CONCURRENTLY with -- +migrate no-transaction if your tool wraps
each migration in a transaction. golang-migrate does this; add a special comment or use
a separate non-transactional migration file.

3.3 Data Migrations vs Schema Migrations

Schema migrations change structure (DDL). Data migrations transform data (DML). Keep them separate:
• Schema migration: ADD COLUMN status TEXT DEFAULT 'active'
• Data migration: UPDATE users SET status = 'legacy' WHERE created_at < '2020-01-01'

Data migrations on large tables can be slow and should be run with care. Consider batching:
-- 0006_backfill_status.[Link]
-- Batch update to avoid long lock times
DO $$
DECLARE
batch_size INT := 10000;
updated INT;
BEGIN
LOOP
UPDATE users
SET status = 'legacy'
WHERE id IN (
SELECT id FROM users
WHERE created_at < '2020-01-01'
AND status IS NULL
LIMIT batch_size
);
GET DIAGNOSTICS updated = ROW_COUNT;
EXIT WHEN updated = 0;
PERFORM pg_sleep(0.1); -- yield between batches
END LOOP;
END $$;
 CH 04 Zero-Downtime Migrations

Chapter 4: Zero-Downtime Migrations

In production you cannot simply pause traffic while you run a migration. Zero-downtime migrations require that
schema changes are backward-compatible with the version of code that is currently running, and forward-
compatible with the version you are deploying.

4.1 The Expand-Contract Pattern

This is the gold standard for zero-downtime schema changes. It proceeds in three phases across separate
deployments:

Phase Action State

1 — Expand Add new column / table Old code ignores new column. New code can start
writing to it.

2— Backfill / dual-write New code writes both old and new. Backfill fills old
Migrate rows.

3— Drop old column / table All code now uses only the new schema. Old column
Contract can be removed.

4.2 What Makes a Migration Dangerous?

These operations acquire locks that block reads and writes and should be avoided in production without careful
planning:
• ALTER TABLE ... ADD COLUMN NOT NULL without a default (scans entire table)
• DROP COLUMN (acquires ACCESS EXCLUSIVE lock)
• ALTER TABLE ... ALTER COLUMN TYPE (rewrites entire table)
• Adding a non-partial index without CONCURRENTLY
• Adding a FOREIGN KEY constraint without NOT VALID first

4.3 Safe Approach to Adding a NOT NULL Column

In PostgreSQL 11+ you can add a NOT NULL column with a constant default without a table rewrite. For variable
defaults or older versions, use this safe pattern:
 -- Step 1 (deploy A): add column as nullable
ALTER TABLE orders ADD COLUMN status TEXT;

-- Step 2 (deploy A): backfill existing rows

UPDATE orders SET status = 'pending' WHERE status IS NULL;

-- Step 3 (deploy B): enforce NOT NULL after code always writes it
-- Uses NOT VALID to avoid full table scan
ALTER TABLE orders ALTER COLUMN status SET NOT NULL;

4.4 Safe Foreign Key Addition

-- Step 1: add the FK constraint as NOT VALID (fast, no full scan)
ALTER TABLE posts
ADD CONSTRAINT fk_posts_user_id
FOREIGN KEY (user_id) REFERENCES users(id)
NOT VALID;

-- Step 2: validate existing rows (takes ShareUpdateExclusiveLock, not full lock)

ALTER TABLE posts VALIDATE CONSTRAINT fk_posts_user_id;
 CH 05 Transaction Safety

Chapter 5: Transaction Safety

Most migration tools (including golang-migrate) wrap each migration file in a single transaction by default. This
is very powerful: if any statement in the migration fails, the entire migration is rolled back and the database is
left unchanged.

5.1 Transactional vs Non-Transactional Migrations

Most DDL in PostgreSQL is transactional — you can CREATE TABLE, ALTER TABLE, and DROP TABLE inside a
transaction. However, a small number of operations cannot run inside a transaction:
• CREATE INDEX CONCURRENTLY
• CREATE DATABASE / DROP DATABASE
• VACUUM

golang-migrate handles this with a special comment at the top of the file:
-- +migrate no-transaction

CREATE INDEX CONCURRENTLY IF NOT EXISTS

idx_orders_status ON orders(status);

5.2 Dirty State

If a migration fails midway through and golang-migrate cannot roll back (e.g., after a non-transactional
operation), the migration version is marked as dirty in the schema_migrations table. The tool will refuse to
proceed until you resolve the issue.

To recover from a dirty state:

1. Manually fix or reverse whatever partial changes were made.
2. Run: migrate force <version-1> to set the version back to the last good state.
3. Re-run migrate up.

⚠️ Never use force to skip past a failed migration without actually fixing the underlying
WARNING
issue. Force only updates the version number — it does not revert or reapply any SQL.
5.3 Idempotent Migrations
Where possible, write migrations to be idempotent — safe to run more than once. Use IF NOT EXISTS / IF EXISTS
guards:
CREATE TABLE IF NOT EXISTS feature_flags (...);

ALTER TABLE users ADD COLUMN IF NOT EXISTS bio TEXT;

DROP INDEX IF EXISTS idx_old_name;

CREATE UNIQUE INDEX IF NOT EXISTS idx_users_email ON users(email);

 CH 06 Multi-Service & Multi-Tenant Schemas

Chapter 6: Multi-Service & Multi-Tenant Schemas

6.1 Multiple Services, One Database
When multiple services share a database, each service should own its own set of tables and run its own
migrations independently. Cross-service foreign keys should be avoided; reference by ID instead.
• Use separate PostgreSQL schemas (namespaces) per service: CREATE SCHEMA billing;
• Or prefix table names: billing_invoices, billing_payments.
• Each service maintains its own migrations directory and migration table.

-- Service A migration table

CREATE TABLE IF NOT EXISTS service_a_schema_migrations (...);

-- Service B migration table

CREATE TABLE IF NOT EXISTS service_b_schema_migrations (...);

6.2 Passing a Custom Migration Table Name in Go

// Use a service-specific table to avoid cross-service conflicts
func NewMigrator(databaseURL, path, tableName string) (*[Link], error) {
db, err := [Link]("postgres", databaseURL)
if err != nil {
return nil, err
}

driver, err := [Link](db, &[Link]{

MigrationsTable: tableName, // e.g. "billing_schema_migrations"
})
if err != nil {
return nil, err
}

return [Link](
"[Link]
"postgres",
driver,
)
}

6.3 Multi-Tenant Schemas (Schema-per-Tenant)

Some SaaS apps give each tenant their own PostgreSQL schema for strong data isolation. Migrations must be
applied to every tenant schema.
// ApplyToAllTenants runs migrations against every tenant schema.
func ApplyToAllTenants(baseURL string, tenants []string, migrationsPath string)
error {
for _, tenant := range tenants {
// Point search_path at the tenant schema
tenantURL := baseURL + "&search_path=" + tenant

m, err := [Link]("[Link] tenantURL)

if err != nil {
return [Link]("tenant %s: %w", tenant, err)
}

if err := [Link](); err != nil &&  {

[Link]()
return [Link]("tenant %s migration failed: %w", tenant, err)
}
[Link]()
}
return nil
}

💡 TIP In a schema-per-tenant model, create a provisioning migration that runs when a new
tenant is onboarded, bringing their schema up to the latest version immediately.
 CH 07 Testing Migrations

Chapter 7: Testing Migrations

Untested migrations are a liability. A migration that works on a fresh database can fail on production because of
existing data, constraints, or locks. Testing should cover both the up migration and the down migration.

7.1 Integration Tests with a Real PostgreSQL

Use testcontainers-go to spin up a real, isolated PostgreSQL instance for each test run. This guarantees tests
match production behavior exactly.
package db_test

import (
"context"
"testing"

"[Link]/testcontainers/testcontainers-go"
"[Link]/testcontainers/testcontainers-go/modules/postgres"
tcwait "[Link]/testcontainers/testcontainers-go/wait"
)

func TestMigrations(t *testing.T) {

ctx := [Link]()

ctr, err := [Link](ctx,

[Link]("postgres:16"),
[Link]("testdb"),
[Link]("test"),
[Link]("test"),
[Link](
[Link]("database system is ready to accept connections"),
),
)
if err != nil {
[Link]("start postgres: %v", err)
}
[Link](func() { [Link](ctx) })

connStr, _ := [Link](ctx, "sslmode=disable")

// Apply all migrations

if err := RunMigrations(connStr, "../../migrations"); err != nil {
[Link]("up migrations: %v", err)
}

// Roll back all migrations

if err := RollbackAll(connStr, "../../migrations"); err != nil {
[Link]("down migrations: %v", err)
}

// Apply again to verify idempotent up-down-up cycle

if err := RunMigrations(connStr, "../../migrations"); err != nil {
 [Link]("second up: %v", err)
}
}

7.2 Test Helpers: RollbackAll

func RollbackAll(databaseURL, migrationsPath string) error {
m, err := [Link]("[Link] databaseURL)
if err != nil {
return err
}
defer [Link]()

if err := [Link](); err != nil &&  {

return err
}
return nil
}

7.3 What to Assert

After running migrations, verify the schema programmatically:
// Assert a table exists
func tableExists(t *testing.T, db *[Link], table string) {
[Link]()
var exists bool
err := [Link](`
SELECT EXISTS (
SELECT 1 FROM information_schema.tables
WHERE table_name = $1
)`, table).Scan(&exists)
if err != nil || !exists {
[Link]("table %q should exist", table)
}
}

// Assert a column exists

func columnExists(t *testing.T, db *[Link], table, column string) {
[Link]()
var exists bool
[Link](`
SELECT EXISTS (
SELECT 1 FROM information_schema.columns
WHERE table_name = $1 AND column_name = $2
)`, table, column).Scan(&exists)
if !exists {
[Link]("column %s.%s should exist", table, column)
}
}
 CH 08 CI/CD Integration

Chapter 8: CI/CD Integration

Migrations should be automatically validated in CI before any code reaches production. At deployment time,
migrations should run before the new application version starts serving traffic.

8.1 CI: Validate Migrations on Every PR

Add a step to your pipeline that applies all migrations to a fresh database and runs the full up-down-up cycle:
# .github/workflows/[Link] (GitHub Actions excerpt)
jobs:
test:
services:
postgres:
image: postgres:16
env:
POSTGRES_DB: testdb
POSTGRES_USER: test
POSTGRES_PASSWORD: test
ports:
- 5432:5432
options: >-
--health-cmd pg_isready
--health-interval 5s
--health-retries 5

steps:
- uses: actions/checkout@v4

- name: Run migrations

env:
DATABASE_URL: postgres://test:test@localhost:5432/testdb?sslmode=disable
run: |
go run ./cmd/migrate up

- name: Run tests

run: go test ./...

8.2 Deployment: Migrations Before Code

The deployment sequence must be:
4. Run migrations (new schema is live, old app still running — must be backward compatible).
5. Deploy new application version (reads from new schema).
6. (Optional) Run contract migrations to remove deprecated columns after old code is gone.

In Kubernetes this is typically done with an init container or a Job:

 # kubernetes/[Link]
apiVersion: batch/v1
kind: Job
metadata:
name: db-migrate
spec:
template:
spec:
restartPolicy: Never
containers:
- name: migrate
image: myapp:latest
command: ["/app/migrate", "up"]
env:
- name: DATABASE_URL
valueFrom:
secretKeyRef:
name: db-secret
key: url

8.3 Locking During Deployment

If you have multiple application instances starting simultaneously, they may all try to run migrations at once.
golang-migrate uses PostgreSQL advisory locks to prevent this — only one instance runs migrations at a time;
others wait. This is safe out of the box.
 CH 09 Production Best Practices

Chapter 9: Production Best Practices

9.1 The Golden Rules
• Migrations are append-only. Never edit a migration that has already been applied anywhere (dev,
staging, prod). If you need to fix a mistake, write a new migration.
• Every migration must have a valid down. Test it. If a down is truly irreversible, document why and make
the down a no-op with a comment.
• Never modify production schema manually. All changes go through migrations, always.
• Keep migrations small. Large migrations that modify many tables are harder to debug and roll back.
• Run migrations before deploying code, never after.
• Always test on a database with production-like data volume before deploying.

9.2 Backups Before Migrations

For any migration that deletes data or rewrites large tables, take a backup first:
# Full database backup before a destructive migration
pg_dump $DATABASE_URL | gzip > backup-$(date +%Y%m%d-%H%M%S).[Link]

# Or use PITR (Point-In-Time Recovery) if your hosted provider supports it

# AWS RDS: create a manual snapshot before deploying

9.3 Monitoring Migration Time

Add timing around your RunMigrations call so you can alert if it runs unexpectedly long:
func RunMigrationsWithMetrics(databaseURL, path string) error {
start := [Link]()
err := RunMigrations(databaseURL, path)
duration := [Link](start)

if err != nil {
[Link]("migrations FAILED after %s: %v", duration, err)
[Link]()
return err
}

[Link]("migrations completed in %s", duration)

[Link]([Link]())
return nil
}
9.4 Schema Drift Detection
Over time, manual changes to production databases create drift. Use pg_dump and diff to detect drift in CI:
# Dump schema from a migration-applied test database
pg_dump --schema-only $TEST_DATABASE_URL > [Link]

# Dump schema from production

pg_dump --schema-only $PROD_DATABASE_URL > [Link]

# Diff
diff [Link] [Link]

9.5 Alternatives to golang-migrate

While golang-migrate is the most popular choice for Go, other tools worth knowing:

Tool Strengths Notes

Atlas ([Link]) Declarative schema; auto-generates Great for teams wanting schema-as-
migrations code

goose Go and SQL migrations; easy Good alternative; similar API to

embedding golang-migrate

Flyway / Liquibase JVM-based; very mature; wide DB Good if your team is polyglot
support

sqitch Dependency-based ordering; no Flexible for complex dependency

version numbers graphs

You now know everything you need

Write migrations with confidence. Test them. Ship them safely.