Models and migrations

A Model is a Schema that describes one row of a SQL table while generating the variants you actually need at runtime — a select variant for reading, an insert variant that omits database-generated columns, and an update variant for partial writes (plus JSON variants for an HTTP API). SqlModel.makeRepository turns a model into a ready-made repository with insert, update, findById, and delete, so the common CRUD operations are type-safe and decoded without hand-writing each query.

import { Schema } from "effect"
import { Model } from "effect/unstable/schema"

// Branded id keeps Person ids from being confused with other numeric ids.
export const PersonId = Schema.Number.pipe(Schema.brand("PersonId"))

export class Person extends Model.Class<Person>("Person")({
  // FieldExcept(["insert"]): the column is present in the select and update
  // variants but omitted from insert, because the database assigns the id.
  id: PersonId.pipe(Model.FieldExcept(["insert"])),
  name: Schema.String,
  email: Schema.String,
  // Timestamp fields filled with the current time on insert / update.
  createdAt: Model.DateTimeInsertFromDate,
  updatedAt: Model.DateTimeUpdateFromDate
}) {}

// `Person`         — the select schema (full row)
// `Person.insert`  — input for INSERT (no id, no timestamps to supply)
// `Person.update`  — input for UPDATE
// `Person.json`    — variant for serializing over an API

The field helpers encode intent: Model.FieldExcept(["insert"]) keeps a column in the select and update variants while omitting it from insert (ideal for a database-assigned, but still updatable, id), Model.DateTimeInsertFromDate / Model.DateTimeUpdateFromDate populate timestamps on write, and Model.Sensitive hides values from JSON output. Model.GeneratedByDb exists for read-only generated columns — it contributes only select and json variants, so it is not suitable for an id you also pass to update. Each variant is a real Schema, so models compose with SqlSchema, validation, and the rest of the ecosystem.

Generating a repository

SqlModel.makeRepository reads the SqlClient from context and returns the CRUD operations bound to a table. Wrap it in a service so the rest of your app depends on the repository, not on raw SQL.

import { Context, Effect, Layer } from "effect"
import { SqlClient, SqlModel } from "effect/unstable/sql"
import { Person, PersonId } from "./Person.js"

// makeRepository returns an Effect whose success value is the record of CRUD
// operations and whose only requirement is SqlClient. `Effect.Success` reads
// that shape back out so the service interface stays in sync.
const makePeopleRepository = SqlModel.makeRepository(Person, {
  tableName: "people",
  spanPrefix: "People", // names the tracing spans for each operation
  idColumn: "id"
})

export class People extends Context.Service<People, Effect.Success<typeof makePeopleRepository>>()(
  "app/People"
) {
  // Layer.effect runs makePeopleRepository, so this layer still needs a
  // SqlClient layer to be provided alongside it.
  static readonly layer: Layer.Layer<People, never, SqlClient.SqlClient> =
    Layer.effect(People, makePeopleRepository)
}

const program = Effect.gen(function*() {
  const people = yield* People

  // insert encodes Person.insert, runs INSERT … RETURNING *, and decodes the
  // row back into a full `Person`.
  const created = yield* people.insert(
    Person.insert.make({ name: "Ada", email: "ada@example.com" })
  )

  // findById fails with NoSuchElementError when the row is absent.
  const found = yield* people.findById(PersonId.make(created.id))

  yield* people.update(
    Person.update.make({ id: found.id, name: "Ada Lovelace", email: found.email })
  )
})

The repository is just an Effect

SqlModel.makeRepository returns an Effect whose success value is the record of operations and whose only requirement is SqlClient. Wrapping it in a Context.Service with Layer.effect (as above) is the idiomatic way to expose it; see Services & Layers for the full pattern.

makeRepository also accepts a softDeleteColumn. When set, reads and updates only see rows where that column is null, and delete sets it to CURRENT_TIMESTAMP instead of removing the row. For batched lookups inside a request resolver, SqlModel.makeResolvers produces the same operations as RequestResolvers that coalesce concurrent requests.

Migrations

A migrator runs numbered migration effects exactly once. It ensures a migrations table exists, reads the highest applied id, and runs every pending migration in order inside a transaction, recording each id so it never runs twice. Each migration is an Effect that uses the ambient SqlClient.

1. Write migration files named <id>_<name>.ts, each default-exporting an Effect that creates or alters tables.

   migrations/0001_create_people.ts

   import { Effect } from "effect"
   import { SqlClient } from "effect/unstable/sql"
   
   // The default export is the migration Effect. It runs with the SqlClient
   // already in context, inside the migrator's transaction.
   export default Effect.gen(function*() {
     const sql = yield* SqlClient.SqlClient
     yield* sql`
       CREATE TABLE people (
         id SERIAL PRIMARY KEY,
         name TEXT NOT NULL,
         email TEXT NOT NULL UNIQUE,
         created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
         updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
       )
     `
   })

2. Provide a migrator layer. The driver’s Migrator re-exports the loaders and adds a layer that runs pending migrations during layer construction.

   import { Config, Layer } from "effect"
   import { PgClient, PgMigrator } from "@effect/sql-pg"
   import { NodeServices } from "@effect/platform-node"
   
   const PgLive = PgClient.layerConfig({
     url: Config.redacted("DATABASE_URL")
   })
   
   // `fromFileSystem` discovers <id>_<name>.ts files at runtime. Other loaders:
   // `fromGlob` (bundler import map) and `fromRecord` (in-memory, great for tests).
   const MigratorLive = PgMigrator.layer({
     loader: PgMigrator.fromFileSystem("migrations"),
     // Optional: dump the schema with pg_dump after successful migrations.
     schemaDirectory: "migrations"
   }).pipe(
     Layer.provide(PgLive),
     // fromFileSystem + pg_dump need FileSystem, Path, and ChildProcessSpawner,
     // which NodeServices.layer provides together.
     Layer.provide(NodeServices.layer)
   )

Because MigratorLive runs migrations as a side effect of building its layer, provide it once at application startup (typically merged with your other layers) and pending migrations apply before the rest of the program starts. Migration ids must be unique; only ids greater than the latest recorded id are considered pending, so editing an already-applied migration will not re-run it — add a new one instead.

Loaders for tests

For unit tests, Migrator.fromRecord({ "0001_init": initEffect }) supplies migrations directly in memory, with no filesystem access — pair it with an in-memory SQLite layer (@effect/sql-sqlite-node) for fast, isolated runs. See Testing.

The migrator returns the list of applied [id, name] pairs and fails with a MigrationError (kinds include Duplicates, ImportError, Failed, and Locked) or a SqlError. On PostgreSQL the migrations table is locked during a run, so concurrent deployers coordinate safely; other dialects rely on the table’s primary-key constraint to detect a concurrent run.

For the query-building primitives these models and migrations are built on, see Queries; for the section overview, see SQL.