Building Typesafe Databases with Effect
- Event
- The Innovation Lab
- Overview
Effect Schema as your single source of truth gives you richer types than any ORM, and hand-written migrations as composable Effects give you full control. Contract tests prove your schema and database agree, while migration tests make evolution safe.
- Technologies
- Effect-TSPostgreSQLSQLMigrationsTestingSchema
Overview
Your database is an external system. It has its own type system, its own schema, and communicates over a network boundary. For every other external system — APIs, webhooks, payment providers — you validate at the boundary and write tests. But for the database, the one external system your entire app depends on, most teams just hope.
Effect gives you the tools to do better. Rich schemas that carry validation, transformations, and test data generation. Hand-written migrations that can alter structure and transform data in one atomic operation. And something almost nobody does — contract tests that prove your schema and database actually agree, and migration tests that prove a user from a year ago still works after seven migrations.
Effect Schema carries validation, transformations, arbitrary generation, and frontend error messages. Far richer than DB column types or ORM models.
Hand-written migrations as composable Effects. Structure changes and data transformations in the same atomic operation — testable end to end.
Contract tests prove schema and DB agree. Migration tests prove transitions are safe. Your database is an external system — test it like one.
Code examples use Effect v4 beta with unstable import paths.
Effect Schema: Richer Than Any ORM Model
A database table gives you column types. varchar, integer, timestamp — that is the extent of what the database knows about your data. An ORM model adds type mappings on top, translating between language types and SQL types. But an Effect Schema carries far more information than either.
Validation Rules and Constraints
Email must be valid, age must be positive, name must be non-empty. Constraints live in the schema, not scattered across middleware or database triggers.
Encode/Decode Transformations
Date objects become ISO strings for storage, branded types enforce identity semantics, and the schema handles both directions automatically.
Arbitrary Generation
Property-based test data generated FROM the schema via Schema.toArbitrary(). No factory functions, no hardcoded fixtures. The schema defines what valid data looks like.
Frontend-Legible Error Messages
TreeFormatter produces structured, user-facing validation errors. The schema describes not just what the data is, but how to explain violations to users.
One Definition, Everything Derives
The schema defines what the data is. Everything downstream — API request and response shapes, service contracts, property-based test data, and what the database stores — derives from this single definition. No parallel type declarations that drift apart. No ORM model that disagrees with your application types. One source of truth, enforced by the type system.
Two Sources of Truth
ORMs promise to bridge the gap between your application and your database. In practice, they create two separate descriptions of your data that must stay in sync — and the gap between them is where bugs live.
On one side, your application defines types, validation logic, and business rules. On the other, the ORM maintains its own schema: migration history, column types, and relationship mappings. These two descriptions evolve independently, and you discover disagreements through careful sandbox testing — or when errors reach production.
TypeScript types, validation logic, business rules. This is what your application believes the data looks like.
- •Interface and class definitions
- •Validation constraints
- •Transformation logic
Database schema, migration history, column types. This is what the database actually stores.
- •Generated migration files
- •Column type mappings
- •Relationship definitions
The space between the two where drift lives. You discover disagreements through pain, not tooling.
- •Drift discovered in production
- •Sandbox testing as verification
- •Manual review of generated SQL
Migrations Without a Safety Net
ORM-generated migrations handle structural changes — ALTER TABLE, ADD COLUMN — but they cannot safely handle data transformations. Splitting a name column into first and last name, backfilling computed values, or migrating enum representations require custom logic that the ORM cannot generate.
When these migrations go wrong, the consequences are catastrophic: data loss, corrupted records, failed rollbacks. The standard mitigation is staging environments with replicated production data — enormous operational overhead to compensate for a tool that cannot express what you need. The alternative is not "no ORM." It is one source of truth with enforcement: a schema that defines the data, hand-written migrations that transform it, and tests that prove both are correct.
Effect SQL: Tagged Template Literals
Effect SQL uses tagged template literals for queries — no ORM abstraction layer, no query builder DSL. You write SQL directly, with parameters safely interpolated and the full power of your database available.
The SqlClient is accessed through Effect's dependency injection. Template literals handle parameterization, and sql.insert maps object fields to columns automatically:
For common patterns, SqlSchema.findAll and SqlSchema.findOne provide a cleaner alternative that wires up Effect Schema decoding automatically — you define the query once and get fully typed results without manual decoding. But underneath, it is still the same tagged template approach. No magic query generation.
Migrations as Effect Programs
Each migration is a standalone Effect program — not a string of SQL in a YAML file, not an auto-generated diff. You have the full Effect runtime available: conditional logic, multi-step operations, data backfills, and error handling, all within a single composable program.
Migration files export a default Effect that receives the SQL client from the context. The Migrator handles ordering, tracking, and transactional execution:
But look at that migration — it is raw SQL. Column names, types, constraints, all hand-written strings. Meanwhile, your TypeScript code has an Effect Schema defining the exact same entity with full type information. What connects the migration to the schema?
The Database Is an External System
PostgreSQL looks like an internal service — it sits in your docker compose, you control the schema, it feels like part of your application. But step back and look at the characteristics:
Its own type system — TEXT, TIMESTAMPTZ, JSONB, INTEGER. Independent of TypeScript and not interchangeable.
Its own schema — table definitions, constraints, and indexes that can drift from your code without warning.
A network boundary — you communicate over TCP. It is a separate process with its own lifecycle.
Independent changes — someone runs a manual migration, a DBA alters a column. Your code finds out at runtime.
| External System | Has Own Types | Network Boundary | You Test It? |
|---|---|---|---|
| Payment API | JSON schema | HTTPS | |
| Webhook Provider | Event schema | HTTPS | |
| PostgreSQL | SQL types | TCP |
The Disconnect
Nothing programmatically connects these. If you fat-finger a column name or use the wrong type, nothing catches it until production.
The question is not “should you test migrations?” — it is “why would you skip contract tests for the one external system your entire application depends on?”
Setting Up the Test Database
Setting up test infrastructure is straightforward with Effect layers. You need two things: a running Postgres instance and a composed layer that connects to it and runs migrations. The database lifecycle is a vitest global setup; the layer composition is pure Effect.
A vitest globalSetup file spins up a Postgres container before any tests run and tears it down after. No docker-compose, no external scripts — just Node child processes.
The pg_isready loop waits for Postgres to accept connections before tests start. The container runs on a non-default port to avoid collisions with any local Postgres instance.
Composing Layers, Not Building a Test Harness
You are composing layers — the same mechanism Effect uses for dependency injection everywhere. The test layer provides a SqlClient backed by a real Postgres instance with real migrations applied. Every test that uses this layer gets a fully migrated database. No mocks, no in-memory substitutes, no test-specific abstractions.
Contract Tests: Schema Meets Database
Contract tests are the enforcement mechanism. They make "one source of truth" real by proving the schema and the database actually agree. The key insight: the current schema is always the test fixture. No version management, no historical schemas, no UserV1 / UserV2. You change the schema, write a migration, and the contract test uses the current schema — it either passes or it fails.
The core contract test: generate a random valid entity from the schema, encode it, insert it into the database, read it back, decode it, and compare. If the migration created wrong column names, wrong types, or missing columns — this test fails.
Arbitrary generation means you are testing the schema's full range, not one hand-picked object. Every valid combination of branded IDs, constrained strings, and date transformations gets exercised.
Migration Tests: Safe Evolution
A Drizzle migration can ALTER TABLE but it cannot touch the data. If your migration needs to rename a column and backfill values, you write the DDL in the migration and the data transformation... somewhere else. A script. A manual query. Hope.
Effect migrations are just Effects. Structure changes and data transformations live in the same atomic operation — and the whole thing is testable.
A user signed up a year ago. Their data was inserted under migration 1. You have shipped 7 migrations since then — each one altering structure, transforming data. When they log in today, does their data still work?
Why This Matters
The INSERT succeeding after migration 1 proves the migration created a valid table. The data surviving migrations 2 through 7 proves every transition preserved it. The migration files are the sync truth between your schema and the database — this test proves they work.
Conclusion
Your database is an external system with its own type system, its own schema, and its own opinions about your data. For every other external boundary, you validate and test. Effect gives you the tools to do the same for your database — rich schemas as the single source of truth, migrations as composable Effects, and contract tests that prove they agree.
The path from here is clear: start with rich schemas that carry more than column types. Own your migrations so you control exactly how data transforms. Recognize that the database is an adapter, not a trusted internal service. Write contract tests that prove schema and storage agree. And write migration tests that verify transitions before they touch real data.
Key Principles
Effect Schema carries validation, transformations, arbitraries, and frontend error messages in a single definition. Far richer than column types or ORM models — and everything downstream derives from it.
Your database is an external system with its own type system, its own schema, and its own opinions about your data. The same boundary testing you would do for any third-party API applies here.
The current schema is always the test fixture. No version management, no historical schemas to maintain. Change the schema, write a migration, run the test. The round-trip proves they agree.
Test data transformations BEFORE they touch real data. Seed, migrate, verify. This is the exact scenario where ORMs cannot help and production breaks.
This approach requires owning your SQL — you need to understand what the database is actually doing
You write and maintain migrations by hand, including rollback logic
You need Effect familiarity across the team, not just one person
You need to write (and run) the tests — they do not exist by default
If your team is not there yet, that is fine — but know what the ORM is costing you. Every any cast, every migration you cannot test, every production schema drift you discover at 2 AM — those are the costs. When you are ready for a better path, the tools exist.
Ready to transform your engineering?
Whether you need technical leadership, enterprise development, or team optimization—let's discuss how we can help.