Liking cljdoc? Tell your friends :D

Database Migration Best Practices

Wagoe uses Migratus for SQL-based, versioned migrations. This guide covers the naming convention, safe patterns, and common recipes.

Quick Reference

# Run pending migrations
clojure -M:migrate up

# Rollback last migration
clojure -M:migrate down

# Check status (REPL)
(require '[wagoe.platform.shell.database.migrations :as mig])
(mig/print-status)

Naming Convention

Migrations use timestamp-based IDs:

YYYYMMDDHHMMSS-description.up.sql
YYYYMMDDHHMMSS-description.down.sql

Examples from the codebase:

20260311100000-search-tables.up.sql
20260312000000-search-filters.up.sql

Rules:

  • Always create both .up.sql and .down.sql — every migration must be reversible

  • Use descriptive kebab-case names: add-user-email-index, create-orders-table

  • Increment the last 6 digits when creating multiple migrations on the same day (e.g. 000000, 001000, 002000)

File Location

LocationPurpose

migrations/

Application-level migrations (main schema). Keep them all here: a resources/migrations/ directory takes precedence and the migrator refuses the split.

libs/<library>/resources/wagoe/<library>/migrations/

Library-contributed migrations (auto-discovered)

Libraries contribute migrations by publishing a wagoe/migration-paths/<library>.edn manifest on the classpath. The file name must be unique to the library: an uberjar keeps only one copy of a shared name.

;; libs/geo/resources/wagoe/migration-paths/geo.edn
{:paths ["wagoe/geo/migrations/"]}

The migration system (wagoe.platform.shell.database.migrations) discovers the manifests and merges their directories at startup.

migrate up and :migrate-on-start? apply a framework module’s migrations only when the profile’s :active switches the module on, and log each one they leave out. Switching a module on later applies its migrations on the next run; switching it off re-runs and rolls back nothing. A library that is not a framework module is always migrated.

Both read :active only. A module enabled in code through :extra-modules is not migrated, and boot logs a warning naming it; put its key in :active. rollback and reset go down through every module’s migrations, so a rollback past a module that is off still undoes its last migration. A last applied migration that no library on the classpath ships stops rollback with its id.

Migration ids are shared with the framework

Your migrations and every library’s share one schema_migrations table, keyed on the id alone. Two migrations with one id cannot both run, so every migration command and :migrate-on-start? refuse to start and name both files. Give your migration a new id: rename its .up and .down files to a fresh timestamp.

New framework migration ids end in an odd second (…120017), so a hand-picked round timestamp such as …120000 will not collide with one. bb scaffold and bb migrate create always pick an even second.

If your database already applied one of the pair

Until #749 nothing refused the pair: one migration ran and the id was recorded for both. To see which one ran, look up the id the refusal names:

SELECT id, description FROM schema_migrations WHERE id = 20261017120000;

description is the name of the migration that ran.

  • The library’s ran. Rename yours to a new id and run migrate up; it runs then.

  • Yours ran. Rename yours, then record the new id so it does not run again: INSERT INTO schema_migrations (id, applied, description) VALUES (<new id>, CURRENT_TIMESTAMP, '<name>'). The library’s migration is still skipped: delete the old id’s row and run migrate up to apply it.

The user module’s 20261017120000-user-session-token-hash is the known case. It needs nothing: the user module hashes plaintext session tokens again at every boot.

A per-module migration table, or ids namespaced by module, would remove the shared space altogether; that is a follow-up.

Resetting a database

bb db:reset drops what the application owns and migrates again. It runs only in the dev, test and acc profiles, and there is no override:

  • the profile must be named, by --env or WAG_ENV; the built-in default does not count;

  • each of those that is set must name dev, test or acc, so WAG_ENV=prod refuses whatever --env says;

  • the profile’s database must not be the one conf/prod/config.edn resolves to.

What the application owns: the objects of each applied migration, removed by its down migration; the tables libraries create at boot, which each declares in wagoe/boot-tables/<lib>.edn; schema_migrations; and the schema of every tenant in tenants. Other tables, schemas and extensions stay. When anything the application does not own depends on something the reset would drop — on PostgreSQL any dependency pg_depend records, such as a view, key, column type, default, function, trigger or policy — it drops nothing and names it. On PostgreSQL the drops run in one transaction; a failure says which steps finished and how to recover.

Before it asks, the reset shows the host, database, schema, tables and tenant schemas. It confirms by the database’s name, typed on a terminal, so piping yes does not answer it. A database on another machine needs --allow-remote.

Production changes go through migrations: bb migrate up, with a down migration or a conversion migration for what must change or go. Never a reset.

Statement Separator

Migratus uses --;; to separate SQL statements within a single migration file. Always use this between statements:

CREATE TABLE orders (...);
--;;
CREATE INDEX idx_orders_user_id ON orders (user_id);
--;;
CREATE INDEX idx_orders_status ON orders (status);

Safe Migration Patterns

Always use IF EXISTS / IF NOT EXISTS

-- up.sql
CREATE TABLE IF NOT EXISTS products (...);
--;;
CREATE INDEX IF NOT EXISTS idx_products_name ON products (name);

-- down.sql
DROP INDEX IF EXISTS idx_products_name;
--;;
DROP TABLE IF EXISTS products;

This makes migrations idempotent — safe to re-run if a partial failure occurred.

Add columns as nullable first

Never add a NOT NULL column without a default in one step — it will fail on existing rows.

-- Step 1: add nullable column
ALTER TABLE users ADD COLUMN phone VARCHAR(50);

-- Step 2 (separate migration, after backfill): add constraint
ALTER TABLE users ALTER COLUMN phone SET NOT NULL;

Add indexes without locking (PostgreSQL)

On PostgreSQL, CREATE INDEX locks the table. Use CONCURRENTLY for large tables:

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_email ON orders (email);
CONCURRENTLY cannot run inside a transaction, and Migratus runs each migration file in one. Start the file with -- :disable-transaction and give the index a migration of its own.

Foreign keys — order matters

Create the referenced table before the referencing table. If they span separate migrations, use two migration files with ordered timestamps.

Rename columns safely

Renaming in one step breaks running application instances. Use a three-step approach:

  1. Add new column, copy data

  2. Deploy app that reads from new column

  3. Drop old column

-- Migration 1: add new column
ALTER TABLE users ADD COLUMN full_name VARCHAR(255);
--;;
UPDATE users SET full_name = name;

-- Migration 2 (after app deploy): drop old column
ALTER TABLE users DROP COLUMN name;

Zero-Downtime Migrations

For production deployments with no downtime:

OperationSafe approach

Add column

Add as nullable (no default needed). App ignores it until next deploy.

Remove column

Deploy app that stops reading it first. Then drop in next migration.

Add index

Use CONCURRENTLY on PostgreSQL. Other DBs: schedule during low traffic.

Add table

Always safe — nothing references it yet.

Drop table

Deploy app that stops using it first. Then drop.

Change column type

Add new column, backfill, deploy, drop old column (3 steps).

The key principle: schema changes and code changes deploy separately. The database schema should always be compatible with both the old and new application version.

Multi-Tenant Migrations

When using wagoe-tenant (schema-per-tenant), migrations run in two scopes:

  1. Shared schema (public) — tenant registry, shared tables. Use migrations/ as normal.

  2. Per-tenant schema — tenant-specific tables, in migrations-tenant/. The tenant library runs these into a new tenant’s schema when it is provisioned, and into every existing tenant schema at startup.

Per-tenant migrations run with search_path set to the tenant’s schema, so table names need no schema prefix:

-- Runs in the tenant's schema context
CREATE TABLE IF NOT EXISTS orders (
  id         VARCHAR(255) PRIMARY KEY,
  tenant_id  VARCHAR(255) NOT NULL,
  ...
);

Always include tenant_id in indexes for tenant-scoped queries:

CREATE INDEX IF NOT EXISTS idx_orders_tenant_status
  ON orders (tenant_id, status);

Large Data Backfills

For migrations that update many rows, process in batches to avoid long locks:

-- PostgreSQL: batch update with CTE
WITH batch AS (
  SELECT id FROM users
  WHERE status IS NULL
  LIMIT 10000
)
UPDATE users SET status = 'active'
WHERE id IN (SELECT id FROM batch);

For very large tables, write a Clojure migration script instead of SQL and run it as a one-off task outside the migration framework.

Testing Migrations

  • CI runs all migrations against H2 - the test suite creates a fresh schema every run

  • Test locally against PostgreSQL before deploying to production (H2 doesn’t support all PostgreSQL features like CONCURRENTLY)

  • Rollback test: run migrate up, then migrate down, then migrate up again. If it fails, your down migration is broken.

# Full roundtrip test
clojure -M:migrate up
clojure -M:migrate down
clojure -M:migrate up

Common Recipes

Create a table with indexes

-- up.sql
CREATE TABLE IF NOT EXISTS products (
  id          VARCHAR(255)             NOT NULL PRIMARY KEY,
  tenant_id   VARCHAR(255)             NOT NULL,
  name        VARCHAR(255)             NOT NULL,
  price       DECIMAL(10,2)            NOT NULL,
  status      VARCHAR(50)              NOT NULL DEFAULT 'draft',
  created_at  TIMESTAMP WITH TIME ZONE NOT NULL,
  updated_at  TIMESTAMP WITH TIME ZONE
);
--;;
CREATE INDEX IF NOT EXISTS idx_products_tenant_id ON products (tenant_id);
--;;
CREATE INDEX IF NOT EXISTS idx_products_status ON products (status);

-- down.sql
DROP TABLE IF EXISTS products;

Add a column with default

-- up.sql
ALTER TABLE orders ADD COLUMN priority INTEGER DEFAULT 0;

-- down.sql
ALTER TABLE orders DROP COLUMN IF EXISTS priority;

Add a unique constraint

-- up.sql
ALTER TABLE users ADD CONSTRAINT uq_users_email UNIQUE (email);

-- down.sql
ALTER TABLE users DROP CONSTRAINT IF EXISTS uq_users_email;

Add a foreign key

-- up.sql
ALTER TABLE orders
  ADD CONSTRAINT fk_orders_user_id
  FOREIGN KEY (user_id) REFERENCES users (id);

-- down.sql
ALTER TABLE orders DROP CONSTRAINT IF EXISTS fk_orders_user_id;

Checklist Before Merging a Migration

  • Both .up.sql and .down.sql exist

  • Uses IF EXISTS / IF NOT EXISTS where applicable

  • Statements separated by --;;

  • No NOT NULL added to existing column without a default or backfill

  • Indexes use CONCURRENTLY for large tables (PostgreSQL)

  • Column names use snake_case (database boundary convention)

  • Tested roundtrip: up → down → up

  • Timestamp ID doesn’t collide with existing migrations

❮Managing accessMulti-Tenancy❯

Can you improve this documentation? These fine people already did:
thijscreemers & Thijs CreemersEdit on GitHub

cljdoc builds & hosts documentation for Clojure/Script libraries

Keyboard shortcuts
Ctrl+kJump to recent docs
←Move to previous article
→Move to next article
Ctrl+/Jump to the search field
Raise an issueBrowse cljdoc sourceChat on Slack
× close