Effect Migration Testing — Reference Implementation

The canonical setup for testing Effect-SQL migrations against real Postgres, in CI, with no mocks. Companion to the Effect SQL migration tests article.

Quick start

bun install
bun run test

You'll see eight tests pass against a real Postgres container that testcontainers spins up automatically. ~5-6 seconds wall time, no docker-compose, no setup steps, no manual port management. The host must have pg_dump on PATH (Ubuntu: sudo apt install postgresql-client); each test triggers a real pg_dump --schema-only to .tmp/schema/_schema.sql after every applied migration.

What's in here

This repo demonstrates the article's claims about migration testing with Effect Schema as the source of truth — no Drizzle-style parallel type definitions, no schema-vs-DB drift, real Postgres in CI.

Versioned schema chain

src/schemas/User.ts defines four Model.Class snapshots, each derived from the previous via plain object operations on the field map — additive via Model.fields(prev), destructive via Struct.omit. Field schemas (UserId, FirstName, Email, Phone, …) are referenced once and shared across every Vn.

UserV1 — initial table
UserV2 — V1 + phone (matches migration 0002)
UserV3 — V2 minus phone (matches migration 0003, destructive)
UserV4 — V3 minus email, plus emailLocal + emailDomain (matches migration 0004, transformational)

No User / UserRepo alias to "the latest." Every consumer references a specific UserVnRepo. Bumping to V5 is an explicit code change, not a silent alias rebind.

Four migrations, including a real transformation

File	Effect
src/migrations/0001_create_users.ts	CREATE TABLE users with id (UUID generated), names, email, timestamps, soft-delete
src/migrations/0002_add_phone.ts	ALTER TABLE users ADD COLUMN phone TEXT (nullable)
src/migrations/0003_remove_phone.ts	ALTER TABLE users DROP COLUMN phone (destructive)
src/migrations/0004_split_email.ts	Add email_local + email_domain, backfill via split_part(email, '@', N), drop email, set new columns NOT NULL (transformational — schema change AND data reshape in one atomic Effect)

Eight tests, two loaders

All eight run via PgMigrator.run from @effect/sql-pg — the production deploy code path with real pg_dump wiring. The choice of loader depends on whether the test needs subset control:

• fromFileSystem(migrationsDir) — used where the test always applies every migration. No loader map, no string keys, just discovers <id>_<name>.ts files in the directory.
• fromRecord({...}) — used where the test stages migrations explicitly (apply 0001, insert a row, then apply 0002+0003+0004). The string key carries the id and name for the Migrator's parser.

Test	Loader	What it asserts
contract.test.ts	fromFileSystem	UserV4 round-trips through repo.insert → repo.findById
year-old-user.test.ts	fromRecord (staged)	A V1 row survives migrations 0002 + 0003 + 0004; emailLocal + emailDomain reconstruct the original V1 email exactly
phone-removed.test.ts	fromRecord (staged)	A V2 row with a phone number survives the destructive V3 column drop
idempotency.test.ts	fromFileSystem	Running the migrator twice applies 4 migrations the first time, 0 the second
partial-migration.test.ts	fromRecord (4 stages)	fromRecord runs only the migrations in the supplied map at each stage; column state matches expectations after each
user-update.test.ts	fromFileSystem	repo.update bumps updatedAt; createdAt stays put (proves Overrideable variants fire correctly)
user-soft-delete.test.ts	fromFileSystem	repo.delete sets deleted_at (row stays in table); repo.findById then fails (soft-delete filter works)
broken-migration.test.ts	fromRecord (inline broken)	A deliberately broken migration (ADD COLUMN NOT NULL on populated table) surfaces as a MigrationError defect from PgMigrator.run — the article's "test catches it before deploy" moment as a permanent regression check

Schema as source of truth, mechanically

Each field has a faker-backed toArbitrary annotation so Schema.toArbitrary(UserV4.insert) produces realistic, PG-safe, validation-passing rows directly. Tests sample the variant and pass it straight to the repository — no payload structs to keep in sync.

const runMigrations = PgMigrator.run

it.effect.prop(
  "...",
  { insert: Schema.toArbitrary(UserV4.insert) },
  ({ insert }) =>
    Effect.gen(function* () {
      yield* resetPublicSchema
      yield* runMigrations({
        schemaDirectory: ".tmp/schema",
        loader: PgMigrator.fromFileSystem(migrationsDir),
      })
      const repo = yield* UserV4Repo
      const inserted = yield* repo.insert(insert)
      // ...
    }),
  { fastCheck: { numRuns: 5 } },
)

Why testcontainers, not docker-compose

The Effect maintainers' own @effect/sql-pg test suite uses @testcontainers/postgresql (see effect-smol/packages/sql/pg/test/utils.ts). Container lifecycle is managed by an Effect Layer — start on scope acquire, stop on scope release. No yaml file to maintain, no port allocation conflicts, automatic cleanup if the test process crashes.

Why this is a CI gate, not an inner-loop test

These tests need a real engine — mocks defeat the entire purpose. That makes them slower than unit tests, which is fine: they run on PR via CI, not on every save. ~5-6 seconds for the whole suite is well within "gate" budget. Property tests use numRuns: 5 (not 100) because the structural coverage — which migrations get applied and when — is on the test SHAPE, not iteration count; five iterations is enough for the schema arbitraries to hit boundary cases.

What this repo is NOT

• Not a production app — no HTTP, no services, no auth surface.
• Not a Drizzle alternative — the SQL is hand-written because the demo is about testing migrations, not generating them.
• Not a monorepo template — single package, intentionally flat.

Project structure

src/
  schemas/
    User.ts                       UserV1 .. UserV4 (Model.Class) + UserV1Repo .. UserV4Repo
    timestamps.ts                 PG-safe DateTime field helpers
  migrations/
    0001_create_users.ts          export default Effect.gen(...)
    0002_add_phone.ts             export default Effect.gen(...)
    0003_remove_phone.ts          export default Effect.gen(...)
    0004_split_email.ts           export default Effect.gen(...)
  test/
    setup/
      PgContainer.ts              testcontainers-backed Postgres service
      errors.ts                   ContainerError
      FreshDbLayer.ts             PgClient + NodeServices layer composition
      migrationsDir.ts            absolute path constant for PgMigrator.fromFileSystem
      resetPublicSchema.ts        per-test DB reset effect
    migrations/
      *.test.ts                   the eight tests above

See CLAUDE.md for the documented findings the article will reference, and ISSUES.md for the upstream proposal (a first-class SqlModel.Table abstraction).