SQL and transactions

Repositories write SQL by hand over effect/sql, on Postgres, SQLite or D1. This page answers which library and casing to use, who owns a transaction (and what replaces it on D1), and how SQL failures become typed errors.

Use SqlClient + SqlSchema with hand-written SQL, one client per database

Impact: HIGH every row is decoded and every statement joins the ambient transaction

• Repositories write tagged-template SQL and run it through SqlSchema.findAll / findOne / findOneOption / findNonEmpty / void, with Result: Row for reads and Request: Row for writes. No ORM: the table is defined in the migration DDL and in the Row, nowhere else.
• The driver is whichever @effect/sql-* package matches the database: @effect/sql-pg, @effect/sql-sqlite-node / -bun, or @effect/sql-d1 on the Cloudflare Worker profile.
• One SqlClient layer per database, provided once at the composition root. The transaction tag is minted per client instance, so a repository on a second instance silently runs outside every transaction (see layer memoization).
• Accepted cost: no query builder, and a misspelled column compiles. Every repository method gets a test against a real database of its own dialect.

❌ Incorrect — a second client “for reads”:

const ReadsLayer = Layer.fresh(DbLayer)          // a new instance: its statements escape transactions
export const RuleQueriesLayer = RuleQueries.layer.pipe(Layer.provide(ReadsLayer))

✅ Correct — one client, provided at the root:

const MainLayer = Layer.mergeAll(LabelingLayer, BillingLayer).pipe(
  Layer.provide(DbLayer),                        // the one SqlClient for this database
)

Source: notes/07-schema-and-data/sql-and-transactions.md · Decision 1, amended

Keep column names in the Row; rename in the mapper

Impact: MEDIUM one spelling per column wherever SQL is involved

• No client transform (transformQueryNames, transformResultNames, transformJson stay unset) and no AS "camelCase" aliases. The DDL, the SQL text and the Row key are the same string. The mapper that exists anyway does the rename.
• SELECT * / RETURNING * is fine: the Row decode is the column allowlist. An AS alias is only for a computed column, named in snake_case (count(*) AS rule_count).
• labeling_rules is a tenant table: the Row has org_id, every statement pins it, and toRow takes orgId from the caller, never from the payload.
• This is the canonical row-mapper sketch, shown here with the SQLite / D1 codecs (see schema at boundaries for the Postgres ones).

❌ Incorrect — a global rename leaves two spellings in one repository:

const Row = Schema.Struct({ repositoryId: RepositoryId, createdAt: … })
sql`SELECT * FROM labeling_rules WHERE repository_id = ${id}`  // snake here, camel in the Row
// and on Postgres transformJson rewrites jsonb keys: { userId } is stored as { user_id }

✅ Correct — snake_case Row, exhaustive mapper renames:

const Row = Schema.Struct({
  id: RuleId, org_id: OrgId, repository_id: RepositoryId,
  enabled: Schema.BooleanFromBit, evidence: Schema.fromJsonString(Evidence),
  client_name: Schema.String, client_ip: Schema.String,
  created_at: Schema.DateTimeUtcFromMillis, deleted_at: Schema.NullOr(Schema.DateTimeUtcFromMillis),
})
const toRow = (orgId: OrgId, { id, repositoryId, enabled, evidence, client, createdAt, ...rest }: LabelingRule) => {
  rest satisfies Exhausted
  return { id, org_id: orgId, repository_id: repositoryId, enabled, evidence,
    client_name: client.name, client_ip: client.ip, created_at: createdAt, deleted_at: null }
}

Source: notes/07-schema-and-data/sql-and-transactions.md · Decision 2, amended

Bind values in their column’s encoded form

Impact: MEDIUM a plain object cannot be bound; a DB clock read hides time

• On Postgres every write binds each jsonb field with sql.json(…). The Row codec stays the plain schema, which decodes the parsed object the driver returns.
• Hand-written SQL binds a time value as its column stores it: DateTime.toDateUtc(t) for timestamptz on Postgres, DateTime.toEpochMillis(t) for INTEGER on SQLite and D1, and a number from Clock.currentTimeMillis in every infra/ statement. See time and clocks.
• A cutoff is bound from now. The statement never reads the database’s clock.

❌ Incorrect — a plain object bound to jsonb, and the database’s clock:

sql`INSERT INTO labeling_rules ${sql.insert(row)}`              // "Cannot infer a PostgreSQL type"
sql`DELETE FROM outbox_events WHERE created_at < now() - interval '7 days'`

✅ Correct — sql.json for jsonb, a cutoff bound from the app’s clock:

sql`INSERT INTO labeling_rules ${sql.insert({ ...row, evidence: sql.json(row.evidence) })} RETURNING *`
const now = yield* Clock.currentTimeMillis
sql`DELETE FROM outbox_events WHERE created_at < ${now - retentionMillis}`

Source: notes/07-schema-and-data/sql-and-transactions.md · Decision 2, amended

Let the use-case own the transaction, through a Transactions port

Impact: HIGH atomicity across slices without merging repositories

• A transaction wraps a use-case, not a repository method. Repositories never take a tx argument and never know whether they are in one. The connection is ambient, so another slice’s service call joins the same transaction.
• The use-case depends on the one-method Transactions port (@app/infra/Transactions), not on SqlClient. It maps a failed BEGIN or COMMIT to PersistenceError, and a test fakes it with withTransaction: (e) => e.
• Signals that cannot roll back go after the transaction returns: PubSub, outbound HTTP, queue enqueue, cache invalidation. A domain-pair event is the exception: Outbox.append runs inside (see idempotency and outbox).
• Nesting means a savepoint. No forking inside a transaction except into work that finishes before it. Accepted cost: a forgotten withTransaction compiles and autocommits.

❌ Incorrect — SqlClient in the use-case, and publishing before commit:

const sql = yield* SqlClient.SqlClient
yield* sql.withTransaction(Effect.gen(function* () {
  const stored = yield* rules.insert(org.orgId, input)
  yield* PubSub.publish(events, RuleCreated.make({ rule: stored }))   // sent even if rollback follows
  yield* audit.append(entryFor(stored))
}))

✅ Correct — the port owns the boundary; publish after it returns:

const tx = yield* Transactions.Transactions
const org = yield* CurrentOrg
const rule = yield* tx.withTransaction(Effect.gen(function* () {
  const stored = yield* rules.insert(org.orgId, input)
  yield* gitHubRepositories.bumpRulesRevision(repositoryId, expectedRevision)  // github's service
  yield* audit.append(entryFor(stored))
  return stored
}))
yield* PubSub.publish(events, RuleCreated.make({ rule }))

Source: notes/07-schema-and-data/sql-and-transactions.md · Decision 3, amended

On D1, make an atomic unit one repository method running one batch

Impact: HIGH a D1 transaction dies at runtime

• D1 has no interactive transactions. The Worker profile’s graph does not provide the Transactions layer, so a use-case that needs it fails to compile there instead of dying.
• A multi-statement atomic unit is one repository method running one batch. Every value is computed first (ids from the app, invariants checked when the domain value is made). Guards on stored state go into the SQL (WHERE revision = ${expected}). No read-modify-write in a batch.
• There is no atomic write across repositories or slices on D1. One slice owns all the rows, or the follower reacts through an outbox row written in the same batch.

❌ Incorrect — a transaction on the D1 profile:

yield* sql.withTransaction(Effect.gen(function* () {   // D1's transactionAcquirer dies
  yield* sql`INSERT INTO finance_transactions ${sql.insert(toTransactionRow(transaction))}`
  yield* sql`INSERT INTO finance_postings ${sql.insert(transaction.postings.map(toPostingRow))}`
}))

✅ Correct — one repository method, one batch:

post: (transaction: Transaction) =>
  d1.batch([
    sql`INSERT INTO finance_transactions ${sql.insert(toTransactionRow(transaction))}`,
    sql`INSERT INTO finance_postings ${sql.insert(transaction.postings.map(toPostingRow))}`,
  ]).pipe(Effect.asVoid, Effect.catchTag("SqlError", Persistence.fail("LedgerRepo.post")))

Source: notes/07-schema-and-data/sql-and-transactions.md · Decision 3, amended

Write conflicts into the SQL; everything else is PersistenceError

Impact: HIGH the same conflict handling on Postgres, SQLite and D1

• A repository’s error channel is the domain errors it can predict plus one shared PersistenceError (@app/infra/PersistenceError, with an operation string and the original cause). Nothing else.
• A domain conflict is expressed in the statement: ON CONFLICT (<unique columns>) DO NOTHING RETURNING *, and zero rows means the domain error. Matching a named constraint with catchReason is Postgres-only; D1 classifies nothing.
• “No row” is Option (findOneOption), not an error. A Row decode failure is an infrastructure fault, so it becomes PersistenceError too.
• Map with catchTags, not mapError, so the domain error is not wrapped. A repository never retries; see scheduling and retry.

❌ Incorrect — a raw SqlError and a pre-check read:

insert: (orgId, rule) => Effect.gen(function* () {
  const existing = yield* findByLabel(orgId, rule.label)   // a race still becomes a 500
  if (Option.isSome(existing)) return yield* new RuleLabelTaken({ label: rule.label })
  return yield* insertRow(toRow(orgId, rule))              // SqlError | SchemaError leak out
})

✅ Correct — the conflict is in the SQL; the row count decides:

const insertRow = SqlSchema.findOneOption({ Request: Row, Result: Row,
  execute: (row) => sql`INSERT INTO labeling_rules ${sql.insert(row)}
    ON CONFLICT (org_id, repository_id, label) DO NOTHING RETURNING *` })

insert: (orgId, rule) => insertRow(toRow(orgId, rule)).pipe(
  Effect.flatMap(Option.match({
    onNone: () => Effect.fail(new RuleLabelTaken({ label: rule.label })),
    onSome: (row) => Effect.succeed(fromRow(row)) })),
  Effect.catchTags({ SqlError: Persistence.fail("LabelingRulesRepo.insert"),
    SchemaError: Persistence.fail("LabelingRulesRepo.insert") }))

Source: notes/07-schema-and-data/sql-and-transactions.md · Decision 4, amended

Deferred

• Drizzle — trigger: Drizzle’s Effect drivers are released as stable and can decode through a schema.