Your schema is the foundation of everything. Bad schema decisions are expensive to fix later. Good ones make your application easier to build and scale.

Why Migrations?

A migration is a versioned SQL file that changes your schema. Instead of running ALTER TABLE manually in production and hoping everyone does the same, migrations track every schema change in version control.

Every team member applies the same migrations in the same order. Your CI pipeline applies them automatically. Rollbacks are possible.

Migration File Structure

The simplest approach: numbered SQL files in a migrations/ folder.

migrations/
  001_create_users.sql
  002_create_orders.sql
  003_add_user_avatar.sql
  004_create_order_items.sql

Each file contains an UP migration (apply change) and optionally a DOWN migration (revert):

-- migrations/001_create_users.sql

-- UP
CREATE TABLE users (
  id         BIGSERIAL PRIMARY KEY,
  email      TEXT NOT NULL,
  name       TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

  CONSTRAINT uq_users_email UNIQUE (email),
  CONSTRAINT chk_users_email CHECK (email LIKE '%@%')
);

CREATE INDEX idx_users_email ON users (email);

-- DOWN
-- DROP TABLE users;

Table Naming Conventions

Use these conventions consistently:

-- Table names: lowercase, plural, snake_case
CREATE TABLE users (...);
CREATE TABLE order_items (...);
CREATE TABLE product_categories (...);

-- Column names: lowercase, snake_case
-- Primary key: always "id"
-- Foreign keys: "referenced_table_id"
-- Timestamps: created_at, updated_at (always TIMESTAMPTZ, not TIMESTAMP)

CREATE TABLE orders (
  id          BIGSERIAL PRIMARY KEY,
  user_id     BIGINT NOT NULL REFERENCES users (id) ON DELETE CASCADE,
  status      TEXT NOT NULL DEFAULT 'pending',
  total       NUMERIC(10, 2) NOT NULL,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Always use TIMESTAMPTZ (timestamp with timezone), not TIMESTAMP. It stores UTC and converts correctly to any timezone.

Constraints

Constraints enforce data integrity at the database level — not just in application code.

CREATE TABLE products (
  id          BIGSERIAL PRIMARY KEY,
  sku         TEXT NOT NULL,
  name        TEXT NOT NULL,
  price       NUMERIC(10, 2) NOT NULL,
  stock       INTEGER NOT NULL DEFAULT 0,
  category_id BIGINT REFERENCES categories (id) ON DELETE SET NULL,

  -- Unique constraint
  CONSTRAINT uq_products_sku UNIQUE (sku),

  -- Check constraints
  CONSTRAINT chk_products_price CHECK (price > 0),
  CONSTRAINT chk_products_stock CHECK (stock >= 0)
);

Naming constraints makes error messages readable: uq_products_sku tells you exactly what failed.

Foreign Keys and Cascade Behavior

-- ON DELETE CASCADE: deleting a user deletes all their orders
ALTER TABLE orders
  ADD CONSTRAINT fk_orders_user
  FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE;

-- ON DELETE SET NULL: deleting a category nulls the product's category_id
ALTER TABLE products
  ADD CONSTRAINT fk_products_category
  FOREIGN KEY (category_id) REFERENCES categories (id) ON DELETE SET NULL;

-- ON DELETE RESTRICT (default): prevents deleting a user who has orders
-- (This is an example — in practice you would use one constraint, not two)
ALTER TABLE order_items
  ADD CONSTRAINT fk_order_items_user
  FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE RESTRICT;

Enum Types

PostgreSQL enums are stored as OIDs (object identifiers) referencing entries in pg_enum. They appear as strings in queries but maintain a defined sort order:

CREATE TYPE order_status AS ENUM ('pending', 'processing', 'shipped', 'delivered', 'cancelled');

CREATE TABLE orders (
  id     BIGSERIAL PRIMARY KEY,
  status order_status NOT NULL DEFAULT 'pending'
);

-- Query by name
SELECT * FROM orders WHERE status = 'pending';

-- Add a new value (non-destructive)
ALTER TYPE order_status ADD VALUE 'refunded' AFTER 'delivered';

Note: you cannot remove values from an enum without recreating the type. Consider using a TEXT column with a CHECK constraint if you need more flexibility.

Safe Zero-Downtime ALTER TABLE

Avoid locking your table in production. Some operations lock the whole table:

-- DANGEROUS: locks the table while adding NOT NULL column
ALTER TABLE users ADD COLUMN phone TEXT NOT NULL DEFAULT '';

-- SAFE: add nullable first, backfill, then add NOT NULL
ALTER TABLE users ADD COLUMN phone TEXT;
UPDATE users SET phone = '' WHERE phone IS NULL;
ALTER TABLE users ALTER COLUMN phone SET NOT NULL;

Adding an index without locking:

-- SAFE: builds the index without locking writes
CREATE INDEX CONCURRENTLY idx_users_phone ON users (phone);

Tracking Applied Migrations

Use a schema_migrations table to track which migrations have run:

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

A simple shell script to apply pending migrations:

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

DB_URL="${DATABASE_URL:?DATABASE_URL is required}"

# Create migrations table if it doesn't exist
psql "$DB_URL" -c "
  CREATE TABLE IF NOT EXISTS schema_migrations (
    version TEXT PRIMARY KEY,
    applied_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
  );
"

for file in migrations/*.sql; do
  version=$(basename "$file" .sql)

  # Skip if already applied
  applied=$(psql "$DB_URL" -tAc "SELECT 1 FROM schema_migrations WHERE version = '$version'")
  if [ "$applied" = "1" ]; then
    echo "  Skipping $version (already applied)"
    continue
  fi

  echo "  Applying $version..."
  psql "$DB_URL" -f "$file"
  psql "$DB_URL" -c "INSERT INTO schema_migrations (version) VALUES ('$version')"
  echo "  Done."
done

For production projects, use a dedicated tool: Flyway, Liquibase, or golang-migrate — they handle edge cases, locking, and parallel deployment scenarios.

If you are using Prisma, migrations are managed automatically via prisma migrate dev and prisma migrate deploy. See Database Tutorial #19: ORMs vs Raw SQL.

Complete Schema Example

A realistic e-commerce schema:

-- Users
CREATE TABLE users (
  id         BIGSERIAL PRIMARY KEY,
  email      TEXT NOT NULL,
  name       TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  CONSTRAINT uq_users_email UNIQUE (email)
);

-- Products
CREATE TABLE products (
  id    BIGSERIAL PRIMARY KEY,
  sku   TEXT NOT NULL,
  name  TEXT NOT NULL,
  price NUMERIC(10, 2) NOT NULL,
  stock INTEGER NOT NULL DEFAULT 0,
  CONSTRAINT uq_products_sku UNIQUE (sku),
  CONSTRAINT chk_products_price CHECK (price > 0),
  CONSTRAINT chk_products_stock CHECK (stock >= 0)
);

-- Orders
CREATE TABLE orders (
  id         BIGSERIAL PRIMARY KEY,
  user_id    BIGINT NOT NULL REFERENCES users (id) ON DELETE RESTRICT,
  status     TEXT NOT NULL DEFAULT 'pending',
  total      NUMERIC(10, 2) NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  CONSTRAINT chk_orders_total CHECK (total >= 0)
);

-- Order items
CREATE TABLE order_items (
  id         BIGSERIAL PRIMARY KEY,
  order_id   BIGINT NOT NULL REFERENCES orders (id) ON DELETE CASCADE,
  product_id BIGINT NOT NULL REFERENCES products (id) ON DELETE RESTRICT,
  quantity   INTEGER NOT NULL,
  unit_price NUMERIC(10, 2) NOT NULL,
  CONSTRAINT chk_order_items_qty CHECK (quantity > 0)
);

-- Indexes
CREATE INDEX idx_orders_user_id ON orders (user_id);
CREATE INDEX idx_orders_status ON orders (status) WHERE status != 'delivered';
CREATE INDEX idx_order_items_order_id ON order_items (order_id);

What’s Next?

Your schema is now well-designed and versioned. Next: PostgreSQL replication and high availability — how to scale reads and survive server failures.

Next: Database Tutorial #8: PostgreSQL Replication and High Availability