Migrations Basics

7 examples for schema migration fundamentals - 5 basic and 2 intermediate. Versioned SQL in git is the single source of truth for what production looks like.

Prerequisites

mkdir -p db/migrations
git init  # migrations live beside application code

One ordered history table (flyway_schema_history, liquibase.databasechangelog, or custom).
Never apply hand-edited DDL in prod without the same SQL in a merged migration file.

Basic Examples

1. First Migration File

-- db/migrations/V001__init_schema.sql
CREATE SCHEMA app AUTHORIZATION app_owner;
 
CREATE TABLE app.users (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text NOT NULL UNIQUE,
  created_at timestamptz NOT NULL DEFAULT now()
);

Filename carries order (V001, timestamp prefix, or tool convention).
Idempotent patterns (IF NOT EXISTS) are tool-specific - Flyway versioned migrations are not re-run.
Review migrations like application code in PRs.

2. Schema History Table

CREATE TABLE IF NOT EXISTS app.schema_migrations (
  version text PRIMARY KEY,
  applied_at timestamptz NOT NULL DEFAULT now(),
  checksum text
);

Runner records applied versions after success.
Checksum detects tampering with already-applied files.
Never delete rows from history to "fix" deploy - forward-fix with new migration.

Related: Flyway & Liquibase - enterprise runners

3. Separate Migration Role

CREATE ROLE app_owner NOLOGIN;
CREATE ROLE app_migrator LOGIN PASSWORD 'vault';
GRANT app_owner TO app_migrator;
 
-- Migration session
SET ROLE app_owner;
CREATE TABLE app.orders (id bigint PRIMARY KEY);
RESET ROLE;

Runtime app_api should not own tables or hold DDL rights.
Migration CI uses same role as production deploy for identical grants.
SET ROLE lines belong in migration wrapper or connection init for migrator only.

4. Transactional DDL (When Safe)

BEGIN;
CREATE TABLE app.feature_flags (
  key text PRIMARY KEY,
  enabled boolean NOT NULL DEFAULT false
);
INSERT INTO app.feature_flags (key) VALUES ('new_checkout');
COMMIT;

Most DDL in PostgreSQL is transactional - failed migration rolls back.
Exceptions: CREATE INDEX CONCURRENTLY, DROP INDEX CONCURRENTLY, VACUUM - cannot run inside transaction block.
Split unsafe statements into separate migration files with operational notes.

5. Grant in Same Migration

CREATE TABLE app.products (id bigint PRIMARY KEY, sku text NOT NULL);
 
GRANT SELECT ON app.products TO app_readers;
GRANT SELECT, INSERT, UPDATE, DELETE ON app.products TO app_writers;

Or rely on ALTER DEFAULT PRIVILEGES from bootstrap migration.
Missing grants cause deploy success but runtime permission errors.
Test migration against fresh database in CI, not only against dev laptops.

Intermediate Examples

6. Expand/Contract Preview

-- Expand: add nullable column (safe deploy)
ALTER TABLE app.orders ADD COLUMN discount_cents integer;
 
-- App v2 writes column; backfill job fills values
 
-- Contract (later migration): enforce NOT NULL after backfill
ALTER TABLE app.orders ALTER COLUMN discount_cents SET NOT NULL;

Ship additive changes before destructive ones.
Two-phase deploy: schema expand, app deploy, data backfill, schema contract.
Never drop column until no code reads it.

Related: Expand/Contract Pattern - full workflow

7. CI Apply on Ephemeral DB

# pipeline excerpt
services:
  postgres:
    image: postgres:18.4
steps:
  - run: flyway migrate -url=jdbc:postgresql://postgres:5432/test
  - run: psql $TEST_URL -f ci/assert_schema.sql

Every PR applies full migration chain to empty database.
Catches ordering bugs and missing grants before merge.
Match PostgreSQL major in CI to production target major.

Official PostgreSQL Documentation - Central hub for all manuals.
PostgreSQL Tutorial (Official) - Official hands-on introduction to PostgreSQL and SQL.
PostgreSQL Wiki - Community FAQ, howtos, and technical notes.
Learn PostgreSQL (2nd Edition) - Luca Ferrari & Enrico Pirozzi.
PostgreSQL 16 Administration Cookbook - Ciolli, Riggs et al.

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.

Prerequisites

mkdir -p db/migrations
git init  # migrations live beside application code

One ordered history table (flyway_schema_history, liquibase.databasechangelog, or custom).
Never apply hand-edited DDL in prod without the same SQL in a merged migration file.

Basic Examples

1. First Migration File

-- db/migrations/V001__init_schema.sql
CREATE SCHEMA app AUTHORIZATION app_owner;
 
CREATE TABLE app.users (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text NOT NULL UNIQUE,
  created_at timestamptz NOT NULL DEFAULT now()
);

Filename carries order (V001, timestamp prefix, or tool convention).
Idempotent patterns (IF NOT EXISTS) are tool-specific - Flyway versioned migrations are not re-run.
Review migrations like application code in PRs.

2. Schema History Table

CREATE TABLE IF NOT EXISTS app.schema_migrations (
  version text PRIMARY KEY,
  applied_at timestamptz NOT NULL DEFAULT now(),
  checksum text
);

Runner records applied versions after success.
Checksum detects tampering with already-applied files.
Never delete rows from history to "fix" deploy - forward-fix with new migration.

Related: Flyway & Liquibase - enterprise runners

3. Separate Migration Role

CREATE ROLE app_owner NOLOGIN;
CREATE ROLE app_migrator LOGIN PASSWORD 'vault';
GRANT app_owner TO app_migrator;
 
-- Migration session
SET ROLE app_owner;
CREATE TABLE app.orders (id bigint PRIMARY KEY);
RESET ROLE;

Runtime app_api should not own tables or hold DDL rights.
Migration CI uses same role as production deploy for identical grants.
SET ROLE lines belong in migration wrapper or connection init for migrator only.

4. Transactional DDL (When Safe)

BEGIN;
CREATE TABLE app.feature_flags (
  key text PRIMARY KEY,
  enabled boolean NOT NULL DEFAULT false
);
INSERT INTO app.feature_flags (key) VALUES ('new_checkout');
COMMIT;

Most DDL in PostgreSQL is transactional - failed migration rolls back.
Exceptions: CREATE INDEX CONCURRENTLY, DROP INDEX CONCURRENTLY, VACUUM - cannot run inside transaction block.
Split unsafe statements into separate migration files with operational notes.

5. Grant in Same Migration

CREATE TABLE app.products (id bigint PRIMARY KEY, sku text NOT NULL);
 
GRANT SELECT ON app.products TO app_readers;
GRANT SELECT, INSERT, UPDATE, DELETE ON app.products TO app_writers;

Or rely on ALTER DEFAULT PRIVILEGES from bootstrap migration.
Missing grants cause deploy success but runtime permission errors.
Test migration against fresh database in CI, not only against dev laptops.

Intermediate Examples

6. Expand/Contract Preview

-- Expand: add nullable column (safe deploy)
ALTER TABLE app.orders ADD COLUMN discount_cents integer;
 
-- App v2 writes column; backfill job fills values
 
-- Contract (later migration): enforce NOT NULL after backfill
ALTER TABLE app.orders ALTER COLUMN discount_cents SET NOT NULL;

Ship additive changes before destructive ones.
Two-phase deploy: schema expand, app deploy, data backfill, schema contract.
Never drop column until no code reads it.

Related: Expand/Contract Pattern - full workflow

7. CI Apply on Ephemeral DB

# pipeline excerpt
services:
  postgres:
    image: postgres:18.4
steps:
  - run: flyway migrate -url=jdbc:postgresql://postgres:5432/test
  - run: psql $TEST_URL -f ci/assert_schema.sql

Every PR applies full migration chain to empty database.
Catches ordering bugs and missing grants before merge.
Match PostgreSQL major in CI to production target major.

Official PostgreSQL Documentation - Central hub for all manuals.
PostgreSQL Tutorial (Official) - Official hands-on introduction to PostgreSQL and SQL.
PostgreSQL Wiki - Community FAQ, howtos, and technical notes.
Learn PostgreSQL (2nd Edition) - Luca Ferrari & Enrico Pirozzi.
PostgreSQL 16 Administration Cookbook - Ciolli, Riggs et al.

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.

Migrations Basics

Search Documentation

Migrations Basics