Testing Effect SQL Migrations You Can't Mock
- Event
- The Innovation Lab
- Overview
PgMigrator.run wraps migration failures as MigrationError defects on the Cause — so a test asserting via Effect.exit + Cause.hasDies catches the bug class that mocks fundamentally cannot. Walks through the reference repo's eight tests, including a transformational split_part backfill that a V1 row survives end to end.
- Technologies
- Effect-TSPostgreSQLPgMigratorTestcontainersProperty TestingMigrations
Testing Migrations You Can’t Mock
A migration that breaks in production rarely fails because the SQL is malformed. It fails because the data already in the table makes the new schema impossible — an ADD COLUMN NOT NULL against a populated table, a transformation that drops a value the next migration depends on, a type widening that silently truncates. No mocked database can catch any of this, because the bug lives in the relationship between real bytes and a real engine.
This article is about an Effect pattern that makes that bug class self-identifying. PgMigrator.run wraps migration failures as a MigrationError defect (Effect.die, not a typed failure), so a test asserting on the Cause via Cause.hasDies distinguishes “migration broken” from any other SQL error. Every test in the reference repo runs the production deploy code path against a real Postgres container, with real pg_dump wired in.
github.com/dlb-technologies-llc/effect-database
Eight migration tests pass against a Postgres container that testcontainers spins up automatically. Roughly five seconds wall time. The article references the same files (broken-migration.test.ts, 0004_split_email.ts, FreshDbLayer.ts) throughout; clone it if you want to follow along.
UserV1 through UserV4 as Model.Class snapshots, each derived from the previous via Model.fields(prev) or Struct.omit. No parallel type declarations, no alias to “the latest.”
Four hand-written migrations, including a transformational one that splits email into email_local + email_domain via split_part backfill, drops the original column, and sets NOT NULL — all in one atomic Effect.
Broken migrations surface as a MigrationError defect on the Cause — not a typed failure to Effect.flip. Tests assert with Effect.exit + Cause.hasDies. The wrapping is what makes the failure self-identifying.
Reference repo runs Effect 4.0.0-beta.67 with effect/unstable/sql and @effect/sql-pg.
Two Type Definitions, One Source of Drift
A typical TypeScript backend ends up with two parallel descriptions of what a user is: the application type used in code, and the schema definition the ORM uses to manage migrations. They are supposed to be the same shape. They drift the moment someone hand- edits a migration, runs a column type change manually, or adds a database trigger that the application type doesn’t know about. There is no enforcement — just a convention that the team will remember to update both sides.
The reference repo collapses both sides into one. Effect Schema is the type. Migrations are hand-written Effects that operate on the database. The contract between them is not a generator; it is a test that round-trips a sample of the schema through real Postgres and proves the two agree.
The ORM file. Drizzle reads it to plan migrations and to type query results. It is the closest the codebase gets to the column types, defaults, and nullability of the underlying table.
Two files. Two languages of types. A column rename, a NOT NULL flip, a default change — any of these needs a coordinated edit on both sides, with nothing in the type system enforcing the agreement.
What the application believes a row looks like. Drives validation, encoding, API responses, test fixtures.
What the database actually stores. Driven by migration history, column types, constraints, triggers, defaults.
The gap between the two. Discovered through pain: a runtime decode failure, a NOT NULL surprise, a transformation that lost a column the app expected.
Hand-written migrations are the point
Auto-generated migrations are restricted to operations the generator can express. A column rename becomes a drop + add; the data is lost. A type change that needs a backfill cannot be expressed at all. Anything transformational — splitting an email field into local and domain parts, normalizing a JSON column into rows, backfilling a derived value — ends up as a separate script with no relationship to the schema change.
Hand-written Effect migrations put structure changes and data transformations in the same atomic body. The test then proves both happened correctly — that the rows already in the table survived the change, not just that the new column exists.
Versioned Schemas, No Parallel Declarations
The reference repo keeps four Model.Class snapshots of the users table inline in src/schemas/User.ts. Each version is derived from the previous by plain object operations on the field map — additive via Model.fields(prev), destructive via Struct.omit. Field schemas (UserId, FirstName, Email, ...) are declared once and referenced from every Vn.
There is no User or UserRepo alias to “the latest.” Every consumer references the snapshot it actually wants by version number. Bumping to V5 means adding UserV5 / UserV5Repo and updating the specific call sites — an explicit code change, not a silent alias rebind.
Additive transitions via Model.fields
UserV2 spreads the V1 field map and appends phone. The shared field schemas come along automatically; nothing about firstName or email gets restated.
Destructive transitions via Struct.omit
UserV3 omits phone from V2. UserV4 omits email from V3 and adds emailLocal + emailDomain — matching the transformational migration that splits the column.
Each field schema annotates toArbitrary with a faker-backed generator. Property tests sample the variant via Schema.toArbitrary(UserV4.insert) and pass the result straight to the repository — no payload structs to keep in sync.
One Schema, Four Jobs
The same UserVn declaration powers every place the shape shows up — not by codegen, by reading the schema at runtime. Each downstream use is a different angle on the same source of truth, which is what makes drift detectable instead of invisible.
The same Email / FirstName field schemas drive client-side validation. The error message annotated on the schema (“Please enter an email address that looks like name@domain.com”) is the same one shown to the user — not a parallel string in a form library config.
HTTP request bodies, response payloads, DB row reads, queue messages — every boundary uses Schema.decode / Schema.encode. A schema change that breaks a boundary is a type error, not a runtime surprise three weeks later.
Every field annotates toArbitrary with a faker generator. Tests pull realistic-looking sample rows from Schema.toArbitrary(UserV4) — no factory functions, no hand-rolled fixtures, no UUID literals drifting from the column type.
Round-trip and validation invariants on the schema itself — decode of encode equals identity, pattern checks hold across faker output, branded types survive serialization — run at high iteration counts to surface real drift before it ships to production as a 4xx in a handler nobody touched.
Phone — all four jobs in one field schemaLifted verbatim from src/schemas/User.ts in the reference repo. One declaration does the work of four parallel artifacts:
Encode / decode: Schema.decodeTo with stripToDigits turns “(202) 555-1234” or “+1 202 555 1234” into 2025551234 on the way in, passes it through on the way out.
FE validation: the message on Schema.isPattern is the same string the form shows the user. No duplicate in a form library config.
Faker arbitrary: toArbitrary produces 10-digit strings that pass the pattern check — sample-able directly from Schema.toArbitrary(UserV2).
Property-testable: decode of encode equals identity, the regex holds against 1000+ faker samples, the brand survives the JSON round-trip. The schema is the test surface.
Iteration counts split by intent. Schema property tests — value-space coverage on a single shape — want 1000+ runs to find the rare faker output that violates an invariant. The migration tests in the next section want five — their coverage is on test shape (which migrations stage where), not on iteration count.
Schema.DateTimeUtcFromDate’s default arbitrary walks globalThis.Date’s full range and produces values like 4714 BC that TIMESTAMPTZ rejects with timestamp out of range. The repo’s src/schemas/timestamps.ts provides a drop-in with a faker-backed past-date arbitrary that stays inside PG’s range:
The Migrations
Each migration in src/migrations/ is a file that export defaults an Effect built on the SqlClient from context. The Migrator discovers files by name (<id>_<name>.ts), parses the id, sorts, runs in order, and records what it applied. The file is the source of truth at the DB level — no Drizzle, no codegen.
Migrations 0001–0003 are structural (create, add column, drop column). The interesting one is 0004_split_email.ts: a transformational migration that reshapes data, doesn’t just move metadata.
The transformational case. Add two new nullable columns, backfill from the existing email via split_part, drop the original column, then mark the new columns NOT NULL. Every row’s email survives — just reshaped.
An auto-generator can produce the four ALTER statements in 0004. It cannot produce the UPDATE that reshapes existing rows in between. That step lives in code or it lives nowhere — and the round-trip test against a V1 row going to V4 is what proves it happened correctly.
PgMigrator: The Production Deploy Path
The reference repo runs every test through PgMigrator.run from @effect/sql-pg — not the bare Migrator.make() form. The Pg variant is the production deploy code path, with full pg_dump wiring: each successful run dumps the post-migration schema to .tmp/schema/_schema.sql via pg_dump --schema-only --no-owner --no-privileges. The dump only runs when at least one new migration was applied (the Migrator’s completed.length > 0 gate), so idempotent re-runs are cheap.
Common point of confusion: the container is the Postgres server; pg_dump is the client. The host running tests must have pg_dump on PATH (Ubuntu: sudo apt install postgresql-client). It connects out to the container, not the other way around.
PgMigrator exposes two loaders. The choice depends on whether the test needs to stage migrations or apply them all at once. Most articles show only fromRecord; the interesting bit is the comparison.
Used by contract.test.ts, idempotency.test.ts, user-update.test.ts, user-soft-delete.test.ts — tests where the whole chain is expected to apply first, then assertions run against the final shape. No loader map, no string keys.
fromFileSystemFirst: the directory path must be absolute. The Migrator’s dynamic import() resolves relative to the Migrator module inside node_modules — not the cwd. A relative ./src/migrations resolves to the wrong place. The reference repo exports an absolute constant:
Second: each migration file must use export default. The loader dynamically imports the module and expects the Effect to land as the default export. Neither requirement is documented.
Real Postgres, Layer-Composed
The reference repo uses @testcontainers/postgresql — the same library the Effect maintainers’ own @effect/sql-pg test suite uses. Container lifecycle is owned by an Effect Layer: start on scope acquire, stop on scope release. No yaml file, no port allocation, automatic cleanup if the test process crashes.
One service, one responsibility: start a Postgres container, stop it on scope release. Image pinned to postgres:16-alpine for deterministic runs.
No bespoke test harness — just layer composition
FreshDbLayer is the same composition mechanism used everywhere else: services declared, dependencies provided, lifecycle owned by the scope. Every test that opens with layer(FreshDbLayer) gets a freshly-reset schema, real PgClient against a throwaway Postgres, and PgMigrator.run wired to the actual deploy code path. No mocks, no in-memory substitutes, no test-only abstractions.
Round-Trip and Idempotency
The contract test is the lightest assertion in the suite: apply every migration, then prove the current schema and the current DB agree by round-tripping a row. The property test generates a realistic UserV4.insert payload via Schema.toArbitrary, inserts through UserV4Repo, reads back by id, and asserts deep equality.
If a migration creates wrong column names, wrong types, or missing constraints — the decode fails, the test fails. There is no version management here: the current schema is the test fixture, the current DB is what the chain of migrations produced. Either they agree or they don’t.
CI pipelines typically invoke the migrator unconditionally on every deploy. If the migrator ever re-applied an already-applied migration, every deploy after the first would corrupt data. This test runs the chain twice and asserts the second call applies zero.
A V1 Row Survives a Transformational Migration
A round-trip test only proves the current schema and current DB agree. It says nothing about rows already in the table. The interesting question is whether a user inserted under an old schema still reads correctly through every migration since — including the one that physically reshapes their data.
year-old-user.test.ts stages that explicitly. Stage 1 applies 0001 and inserts a V1 row. Stage 2 applies 0002 + 0003 + 0004 on top — including the transformational split_part backfill that reshapes the row’s email into email_local + email_domain. Reading back via UserV4Repo.findById must reconstruct the original email’s parts exactly.
The Migrator’s effect_sql_migrations tracking table remembers 0001 from stage 1, so stage 2’s fromRecord only lists the migrations it actually introduces. There is no “test the migration in isolation” here — the whole point is the staged narrative.
expect(decoded.emailLocal).toBe(originalLocal) and expect(decoded.emailDomain).toBe(originalDomain) — the assertion proves the UPDATE ... split_part backfill ran against the V1 row before DROP COLUMN email removed the source. A migration that forgot the backfill, or whose backfill dropped the data, fails here.
What mocks fundamentally cannot demonstrate
A mocked database can answer “does my code call the right queries.” It cannot answer “will the value the database actually stored survive the next four migrations.” The whole reason to run the production deploy code path against a real engine is to assert on the relationship between bytes on disk and the new schema — the exact place migration bugs live.
MigrationError as a Defect
The article’s central claim: PgMigrator.run reports failures as defects, not typed errors. A migration that’s backwards-incompatible (the canonical example: an ADD COLUMN NOT NULL against a populated table) raises a MigrationError via Effect.die. Without that wrapping, the underlying SQL failure would arrive as a raw SqlError with no signal that it’s a migration problem. With it, the failure is self-identifying.
Defects are programmer-error signals — they’re not supposed to be caught in business logic. They are exactly the right channel for “this migration is broken; do not ship.” That’s why the tests assert via Effect.exit + Cause.hasDies, not Effect.flip.
The error type of PgMigrator.run doesn’t include MigrationError — the Migrator calls Effect.die internally. Catching it with Effect.flip would change the success type but leave the defect uncaught. Effect.exit + Cause.hasDies is the idiomatic v4 form.
The same defect surfaces in production: a deploy that runs PgMigrator.run and hits a backwards-incompatible migration dies loudly with MigrationError — not a generic SQL error. The article’s “tests catch this before deploy” claim is specifically this mechanism, run earlier.
ALTER TABLE with ok would pass. The test fails specifically because Postgres rejects the operation against the row inserted in stage 1 — and PgMigrator.run wraps that rejection as a defect with provenance. That’s the whole bug class.What the Pattern Actually Buys You
A migration that’s broken against the data already in the table is the bug class mocks cannot catch. The reference pattern catches it by running the production deploy code path (PgMigrator.run) against a real Postgres engine, asserting on the Cause via Cause.hasDies, and staging migrations explicitly so a V1 row can be inserted before V2–V4 transform the schema underneath it.
The result is a small suite of tests (eight in the reference repo at dlb-technologies-llc/effect-database, ~5–6 seconds wall time) that gate every deploy by proving the migration chain works against bytes — not just against an idealized type.
Four Things to Carry Away
PgMigrator.run wraps migration failures via Effect.die so they surface on the Cause as MigrationError. Tests assert with Effect.exit + Cause.hasDies — the wrapping makes broken migrations self-identifying at the test boundary and at the deploy boundary.
UserV1..UserV4 chained via Model.fields(prev) and Struct.omit. Field schemas declared once, referenced from every Vn. No parallel type declarations, no alias to 'the latest' — bumping to V5 is an explicit code change.
A V1 row's email survives 0002 + 0003 + 0004 — including the split_part backfill that reshapes it. Round-trip equality on emailLocal + emailDomain against the original is the assertion mocks fundamentally cannot demonstrate.
Testcontainers manages the Postgres lifecycle; NodeServices.layer provides the platform services PgMigrator needs for pg_dump; resetPublicSchema runs inside every test body for per-iteration isolation. No mocks, no docker-compose, no bespoke harness.
github.com/dlb-technologies-llc/effect-database
Eight passing tests, four migrations including the transformational one, two loader patterns, real pg_dump wiring, and every gotcha called out in the repo’s CLAUDE.md. The article references its files (broken-migration.test.ts, 0004_split_email.ts, FreshDbLayer.ts) directly — the repo is the source of truth.
Ready to transform your engineering?
Whether you need technical leadership, enterprise development, or team optimization—let's discuss how we can help.