effect-sql
/effect-sql
Type-safe SQL with Effect — SqlClient tagged-template queries, SqlSchema, SqlModel CRUD repositories, SqlResolver batching, and Migrator. Use when working with databases, writing queries, defining models, or setting up migrations.
- Author
- mpsuesser ↗
- Repository
- opencode-effect-enforcer ↗
- Licence
- MIT
- Last repository change
- 26.10.2026
Assessment and security
- Files in this package
- 1
- Scripts
- 0
- HTML comments
- 1
- URLs in SKILL.md
- 0
No signals detected. Repository changed on 26.10.2026, without references to retired models or APIs.
MIT, open: use and modify while retaining author attribution.
- SKILL.md zawiera komentarze HTML (1). Nie widać ich w podglądzie Markdown, a model je czyta.
What it does and when to use it
Type-safe SQL with Effect — SqlClient tagged-template queries, SqlSchema, SqlModel CRUD repositories, SqlResolver batching, and Migrator. Use when working with databases, writing queries, defining models, or setting up migrations.
Original author description
Type-safe SQL with Effect — SqlClient tagged-template queries, SqlSchema, SqlModel CRUD repositories, SqlResolver batching, and Migrator. Use when working with databases, writing queries, defining models, or setting up migrations.
How it works
- Effect Source Reference
- Core Imports
- SqlClient — Tagged Template Queries
- SqlSchema — Schema-Validated Queries
- Model — Schema Variant Classes
- SqlModel — CRUD Repository
- SqlResolver — Request Batching
- Migrator — Schema Migrations
- Driver Packages and Layer Setup
- Streaming Large Result Sets
SKILL.md preview
---
name: effect-sql
description: Type-safe SQL with Effect — SqlClient tagged-template queries, SqlSchema, SqlModel CRUD repositories, SqlResolver batching, and Migrator. Use when working with databases, writing queries, defining models, or setting up migrations.
---
You are an Effect TypeScript expert specializing in type-safe SQL database access using the Effect SQL modules.
## Effect Source Reference
The Effect v4 source is available at `~/.local/share/opencode/repos/github.com/Effect-TS/effect@main/`.
Inspect the `effect@4.0.6` tag for this skill; main may be newer. SQL APIs remain
`@stability unstable` and may break in minor releases. Keep `effect` and all
`@effect/sql-*` and platform dependencies on the same release.
Reference this for:
- `packages/effect/src/sql/` — Core SQL modules (SqlClient, SqlSchema, SqlModel, SqlResolver, Migrator, Statement)
- `packages/effect/src/schema/Model.ts` — Model class with variant schemas
- `packages/sql/pg/src/PgClient.ts` — PostgreSQL driver example
## Core Imports
All SQL modules live under the `effect/sql` path:
```ts
import { SqlClient } from 'effect/sql/SqlClient';
import * as SqlSchema from 'effect/sql/SqlSchema';
import * as SqlModel from 'effect/sql/SqlModel';
import * as SqlResolver from 'effect/sql/SqlResolver';
import * as Migrator from 'effect/sql/Migrator';
```
Alternatively, the barrel exports namespace modules:
```ts
import { SqlClient, SqlSchema, SqlModel, SqlResolver, Migrator } from 'effect/sql';
// With the barrel, the service is SqlClient.SqlClient.
```
For Model schemas (used with SqlModel):
```ts
import { Model } from 'effect/schema';
```
## SqlClient — Tagged Template Queries
`SqlClient` is a service accessed via `yield* SqlClient`. It doubles as a tagged template literal function for building parameterized queries.
### Basic Queries
```ts
import { Effect } from 'effect';
import { SqlClient } from 'effect/sql/SqlClient';
const program = Effect.gen(function* () {
const sql = yield* SqlClient;
// SELECT — returns ReadonlyArray<Row>
const users = yield* sql`SELECT * FROM users`;
// Parameterized query — values are safely interpolated
const user = yield* sql`SELECT * FROM users WHERE id = ${userId}`;
// INSERT with sql.insert helper
yield* sql`INSERT INTO users ${sql.insert({ name: 'Alice', email: 'alice@example.com' })}`;
// INSERT multiple rows
yield* sql`INSERT INTO users ${sql.insert([
{ name: 'Alice', email: 'alice@example.com' },
{ name: 'Bob', email: 'bob@example.com' }
])}`;
// INSERT with RETURNING
const [inserted] =
yield* sql`INSERT INTO users ${sql.insert({ name: 'Alice' }).returning('*')}`;
// UPDATE with sql.update helper (second arg = columns to omit from SET)
yield* sql`UPDATE users SET ${sql.update(userData, ['id'])} WHERE id = ${userData.id}`;
// DELETE
yield* sql`DELETE FROM users WHERE id = ${userId}`;
});
```
### Statement Properties
Each tagged template expression produces a `Statement<A>` which is also an `Effect<ReadonlyArray<A>, SqlError>`. Statements expose additional accessors:
```ts
const stmt = sql`SELECT * FROM users`;
// Execute as Effect (default) — returns ReadonlyArray<Row>
yield* stmt;
// Require the first row; fails with Cause.NoSuchElementError when empty
const first = yield* Effect.head(stmt);
// Stream results row by row (for large result sets)
const stream = stmt.stream; // Stream<Row, SqlError>
// Raw result without row transforms
yield* stmt.withoutTransform;
// Get raw result object
yield* stmt.raw;
// Get rows as arrays of values (no column names)
yield* stmt.values;
// Execute without prepared statement
yield* stmt.unprepared;
// Compile to [sqlString, params] without executing
const [sqlString, params] = stmt.compile();
```
### Identifiers, Literals, and Helpers
```ts
const sql = yield* SqlClient;
// Identifier (table/column name) — properly escaped
sql('users'); // => Identifier
sql`SELECT * FROM ${sql('users')}`;
// Literal SQL (unescaped — use with caution)
sql.literal('NOW()');
// Unsafe raw query
yield* sql.unsafe<User>('SELECT * FROM users WHERE id = $1', [userId]);
// IN clause
sql`SELECT * FROM users WHERE ${sql.in('id', [1, 2, 3])}`;
// AND / OR chains
sql`SELECT * FROM users WHERE ${sql.and([sql`name = ${'Alice'}`, sql`active = ${true}`])}`;
// CSV helper (for ORDER BY, GROUP BY)
sql`SELECT * FROM users ORDER BY ${sql.csv(['name', 'created_at'])}`;
```
### Transactions
```ts
const sql = yield* SqlClient;
// Wrap any effect in a transaction — automatically handles BEGIN/COMMIT/ROLLBACK
yield*
sql.withTransaction(
Effect.gen(function* () {
yield* sql`INSERT INTO orders ${sql.insert(order)}`;
yield* sql`UPDATE inventory SET quantity = quantity - 1 WHERE id = ${itemId}`;
})
);
// Nested calls to withTransaction create SAVEPOINTs automatically
```
Transaction context is attached to the active `SqlClient` service instance. Queries join a transaction only when they run with that same client; avoid mixing clients or manually reserved connections for one atomic unit of work.
A failed top-level `BEGIN` or nested `SAVEPOINT` is propagated as a typed `SqlError`; the wrapped effect does not run, and no rollback is attempted for the transaction or savepoint that never started. If a nested `SAVEPOINT` failure escapes the outer transaction body, the already-started outer transaction rolls back. Catching this typed failure does not repair the database transaction state: continue only if the driver/database still permits it. A failed COMMIT remains a **typed `SqlError`** when cleanup succeeds (or is unnecessary). Rollback/release cleanup failures remain defects.
Nested savepoints are released after success or successful rollback by PostgreSQL,
PGlite, MySQL, libSQL, and Node/Bun/React Native/WASM SQLite drivers. A release
failure is also a defect. Custom clients opt in with
`SqlClient.make({ releaseSavepoint: (name) => ... })`; omitting it leaves the
previous behavior, suitable for dialects such as MSSQL without release support.
`commit` accepts either SQL text or `(connection) => Effect<void, SqlError>` so
drivers can inspect completion, while `onCommitFailure` performs cleanup on the
same connection before release. If COMMIT and cleanup both fail, both causes are
preserved as defects, never a successful transaction result. SQLite drivers roll back transactions left
open by failed COMMIT (for example deferred foreign-key violations). A failed
cleanup prevents reuse until a subsequent acquisition successfully retries that
rollback; the database is not silently reopened.
PostgreSQL can return a `ROLLBACK` command result for `COMMIT` after a caught
statement error aborted the transaction. `PgClient` detects this and fails the
transaction with a typed `SqlError` (`UnknownError`, operation `commit`). Catching
a statement failure in application code does not restore PostgreSQL transaction
state. Use a nested transaction/savepoint around a recoverable operation when the
outer transaction must continue.
`sql.afterCommit(effect)` queues infallible callbacks until the **outermost**
successful commit. Successful savepoints transfer callbacks to their parent;
rollback or failed COMMIT drops them. Outside a buffered transaction they run
immediately. Deferred callbacks capture current services without the transaction,
run uninterruptibly after commit, and log/ignore defects rather than undoing a
committed write. Keep them short, not long-running jobs; registration from a fiber
that outlives its transaction is a defect. Clients that bypass
`makeWithTransaction` (such as SQLite Durable Objects) execute callbacks immediately.
<!-- typecheck -->
```ts
import { Effect } from 'effect';
import { SqlClient } from 'effect/sql';
const update = Effect.gen(function* () {
const sql = yield* SqlClient.SqlClient;
return yield* sql.withTransaction(Effect.gen(function* () {
yield* sql`UPDATE accounts SET active = true WHERE id = ${'a1'}`;
yield* sql.afterCommit(Effect.logInfo('account activation committed'));
})).pipe(
Effect.provideService(SqlClient.BeginTransaction, 'BEGIN ISOLATION LEVEL SERIALIZABLE')
);
});
```
`BeginTransaction` replaces only top-level BEGIN SQL, not savepoints; drivers
without SQL-based transaction starts can ignore it. `Reactivity.invalidate` /
`mutation` read from transaction context likewise defer until outer commit and
drop invalidations on rollback. They do not join an enclosing `withBatch` after
commit; a previously captured Reactivity service bypasses that deferral.
Custom drivers must provide `SqlClient.driver` and pass `driver: 'my-driver'` to
`SqlClient.make`. This is **driver identity**, not dialect: PGlite and PostgreSQL
share a dialect but cluster storage must distinguish their connection semantics.
The unstable `makeWithTransaction` now owns its connection scope:
`acquireConnection(restore)` returns a scoped connection Effect, not a
`[scope, connection]` tuple. Restore interruptibility around queued acquisition;
register releases in the supplied scope. `begin(conn, statement)` receives the
optional `BeginTransaction` override.
### Dialect Branching
```ts
const sql = yield* SqlClient
// Branch on database dialect
const result = sql.onDialectOrElse({
pg: () => sql`SELECT * FROM users LIMIT 10`,
mysql: () => sql`SELECT * FROM users LIMIT 10`,
sqlite: () => sql`SELECT * FROM users LIMIT 10`,
orElse: () => sql`SELECT TOP 10 * FROM users`
})
// All dialects required (no orElse)
sql.onDialect({
pg: () => ...,
mysql: () => ...,
sqlite: () => ...,
mssql: () => ...,
clickhouse: () => ...
})
```
## SqlSchema — Schema-Validated Queries
`SqlSchema` wraps SQL queries with Effect Schema encoding/decoding for type-safe request and result handling.
```ts
import { Schema } from 'effect';
import { SqlClient } from 'effect/sql/SqlClient';
import * as SqlSchema from 'effect/sql/SqlSchema';
const sql = yield* SqlClient;
// findAll — returns Array<Res["Type"]>
const listUsers = SqlSchema.findAll({
Request: Schema.Void,
Result: User,
execute: () => sql`SELECT * FROM users`
});
const users = yield* listUsers(void 0);
// findOne — returns Res["Type"], fails with NoSuchElementError if empty
const getUserById = SqlSchema.findOne({
Request: Schema.Number,
Result: User,
execute: (id) => sql`SELECT * FROM users WHERE id = ${id}`
});
const user = yield* getUserById(42);
// findOneOption — returns Option<Res["Type"]>
const findUser = SqlSchema.findOneOption({
Request: Schema.String,
Result: User,
execute: (email) => sql`SELECT * FROM users WHERE email = ${email}`
});
const maybeUser = yield* findUser('alice@example.com');
// findNonEmpty — returns NonEmptyArray<Res["Type"]>, fails with NoSuchElementError if empty
const getActiveUsers = SqlSchema.findNonEmpty({
Request: Schema.Void,
Result: User,
execute: () => sql`SELECT * FROM users WHERE active = true`
});
// void — executes query, discards result, validates request
const deleteUser = SqlSchema.void({
Request: Schema.Number,
execute: (id) => sql`DELETE FROM users WHERE id = ${id}`
});
yield* deleteUser(42);
```
## Model — Schema Variant Classes
The `Model` module provides a schema class system with built-in variants for database operations (`select`, `insert`, `update`) and JSON APIs (`json`, `jsonCreate`, `jsonUpdate`).
```ts
import { Schema } from 'effect';
import { Model } from 'effect/schema';
const UserId = Schema.Number.pipe(Schema.brand('UserId'));
class User extends Model.Class<User>('User')({
// DB-generated primary key usable by repositories: omitted from insert,
// but present in select/update/json so update/delete can address rows.
id: UserId.pipe(Model.FieldExcept(["insert"])),
// DB-generated read-only field: present in select/json only.
searchText: Model.GeneratedByDb(Schema.String),
// Regular field: present in all variants
name: Schema.String,
email: Schema.String,
// Sensitive: present in DB variants, excluded from JSON variants
passwordHash: Model.Sensitive(Schema.String),
// Timestamps with auto-generation
createdAt: Model.DateTimeInsertFromDate, // auto-set on insert
updatedAt: Model.DateTimeUpdateFromDate, // auto-set on insert and update
// Optional field (nullable in DB, optional key in JSON)
bio: Model.FieldOption(Schema.String)
}) {}
// Variant schemas are auto-generated:
User; // select schema — all fields
User.insert; // insert schema — without FieldExcept(["insert"]) and GeneratedByDb fields
User.update; // update schema — includes FieldExcept(["insert"]) IDs, excludes GeneratedByDb fields
User.json; // JSON API schema — without Sensitive fields
User.jsonCreate;
User.jsonUpdate;
```
### Model Field Helpers
| Helper | select | insert | update | json | Description |
| ----------------------------------- | -------- | ------ | ------ | -------- | ----------------------------------------------------- |
| `Model.GeneratedByDb(S)` | S | — | — | S | DB-generated read-only field |
| `S.pipe(Model.FieldExcept(["insert"]))` | S | — | S | S | DB-generated repository ID that updates must include |
| `Model.GeneratedByApp(S)` | S | S | S | S | App-generated, required everywhere |
| `Model.Sensitive(S)` | S | S | S | — | Excluded from JSON variants |
| `Model.FieldOption(S)` | Option | Option | Option | Option | Nullable/optional across all variants |
| `Model.DateTimeInsertFromDate` | DateTime | auto | — | DateTime | Timestamp set on insert |
| `Model.DateTimeUpdateFromDate` | DateTime | auto | auto | DateTime | Timestamp set on insert+update |
| `Model.Field({...})` | custom | custom | custom | custom | Per-variant field configuration |
Use `GeneratedByDb` only for fields that are truly read-only after selection, such as computed columns. For a database-generated primary key used by `SqlModel.makeRepository` or update calls, keep the key in the update variant with `FieldExcept(["insert"])` or an explicit `Model.Field({ select, update, json })` shape; upstream prose may lag, but the constructor and `SqlModel` tests require this distinction.
## SqlModel — CRUD Repository
`SqlModel.makeRepository` generates a complete CRUD interface from a Model class.
```ts
import * as SqlModel from 'effect/sql/SqlModel';
const UserRepo =
yield*
SqlModel.makeRepository(User, {
tableName: 'users',
spanPrefix: 'UserRepo',
idColumn: 'id'
});
// insert — returns the inserted row (decoded via Model schema)
const user =
yield* UserRepo.insert({ name: 'Alice', email: 'alice@example.com' });
// insertVoid — insert without returning the row
yield* UserRepo.insertVoid({ name: 'Bob', email: 'bob@example.com' });
// update — returns the updated row
const updated = yield* UserRepo.update({ id: userId, name: 'Alice Updated' });
// updateVoid — update without returning the row
yield* UserRepo.updateVoid({ id: userId, name: 'Alice Updated' });
// findById — returns the row, fails with NoSuchElementError if not found
const found = yield* UserRepo.findById(userId);
// delete
yield* UserRepo.delete(userId);
```
### Batched Resolvers (CRUD)
`SqlModel.makeResolvers` creates `RequestResolver` values for the same insert, insert-void, find-by-id, and delete operations — ideal for solving N+1 problems while keeping single-request call sites.
```ts
import { RequestResolver } from 'effect';
import * as SqlModel from 'effect/sql/SqlModel';
import * as SqlResolver from 'effect/sql/SqlResolver';
const UserResolvers =
yield*
SqlModel.makeResolvers(User, {
tableName: 'users',
spanPrefix: 'UserResolver',
idColumn: 'id'
});
const findById = SqlResolver.request(UserResolvers.findById);
const user = yield* findById(userId);
const inserted = yield* SqlResolver.request(
User.insert.make({ name: 'Alice', email: 'alice@example.com' }),
UserResolvers.insert
);
yield* SqlResolver.request(userId, UserResolvers.delete);
// Tune individual returned resolvers when you need a wider collection window or cap.
const cappedFindById = UserResolvers.findById.pipe(
RequestResolver.setDelay('50 millis'),
RequestResolver.batchN(100)
);
```
### Soft Deletes
`makeRepository` and `makeResolvers` accept `softDeleteColumn`. When supplied, reads and updates add an `is null` filter for that column, and delete updates the column to `CURRENT_TIMESTAMP` instead of removing the row.
```ts
class SoftDeleteUser extends Model.Class<SoftDeleteUser>('SoftDeleteUser')({
id: UserId.pipe(Model.FieldExcept(["insert"])),
name: Schema.String,
deletedAt: Schema.NullOr(Schema.String).pipe(
Model.FieldOnly(["select", "update"])
)
}) {}
const repo = yield* SqlModel.makeRepository(SoftDeleteUser, {
tableName: 'users',
spanPrefix: 'UserRepo',
idColumn: 'id',
softDeleteColumn: 'deletedAt'
});
// findById/update ignore rows where deletedAt is not null.
yield* repo.delete(userId); // UPDATE users SET deletedAt = CURRENT_TIMESTAMP ...
```
Resolver versions created by `SqlModel.makeResolvers` honor the same soft-delete filter and delete behavior.
## SqlResolver — Request Batching
`SqlResolver` creates `RequestResolver` instances for batching SQL queries. Use these when you need fine-grained control or custom query shapes beyond the resolvers returned by `SqlModel.makeResolvers`.
### Ordered Resolver
Results map 1:1 to requests by position. Result count must match request count.
```ts
import * as SqlResolver from 'effect/sql/SqlResolver';
const insertResolver = SqlResolver.ordered({
Request: User.insert,
Result: User,
execute: (requests) =>
sql`INSERT INTO users ${sql.insert(requests).returning('*')}`
});
// Use with SqlResolver.request
const insertUser = SqlResolver.request(insertResolver);
const user = yield* insertUser({ name: 'Alice', email: 'alice@example.com' });
```
### FindById Resolver
Batches lookups by ID, matching results back by a key function.
```ts
const findByIdResolver = SqlResolver.findById({
Id: UserId,
Result: User,
ResultId: (user) => user.id,
execute: (ids) => sql`SELECT * FROM users WHERE ${sql.in('id', ids)}`
});
```
### Grouped Resolver
Returns multiple results per request, grouped by a key.
```ts
const userPostsResolver = SqlResolver.grouped({
Request: UserId,
RequestGroupKey: (userId) => userId,
Result: Post,
ResultGroupKey: (post) => post.userId,
execute: (userIds) =>
sql`SELECT * FROM posts WHERE ${sql.in('user_id', userIds)}`
});
// Returns NonEmptyArray<Post> per userId
const posts = yield* SqlResolver.request(userPostsResolver)(userId);
```
### Void Resolver
For side-effect-only batched operations (deletes, updates without return).
```ts
const deleteResolver = SqlResolver.void({
Request: UserId,
execute: (ids) => sql`DELETE FROM users WHERE ${sql.in('id', ids)}`
});
```
### Configuring Resolvers
Resolvers already batch same-turn/concurrently queued requests by default (`Effect.yieldNow`). Use `RequestResolver.setDelay` only to widen the collection window, and `RequestResolver.batchN` to cap batch size.
```ts
import { RequestResolver } from 'effect';
const resolver = SqlResolver.ordered({ ... }).pipe(
RequestResolver.setDelay('50 millis'), // wider collection window
RequestResolver.batchN(100), // max batch size
RequestResolver.withSpan('UserRepo.insert')
);
```
## Migrator — Schema Migrations
The `Migrator` module runs sequential, transactional migrations tracked in a `effect_sql_migrations` table.
ClickHouse uses its dialect-specific ledger DDL and does not wrap migrations in
unsupported transactions.
### Migration File Convention
Files must be named `<id>_<name>.js`, `<id>_<name>.ts`, `<id>_<name>.mjs`, or `<id>_<name>.mts`, where `id` is a numeric identifier (e.g. `0001_create_users.ts`). Unsupported extensions are ignored by the file and glob loaders.
Each migration file exports a default Effect:
```ts
// migrations/0001_create_users.ts
import { Effect } from 'effect';
import { SqlClient } from 'effect/sql/SqlClient';
export default Effect.gen(function* () {
const sql = yield* SqlClient;
yield* sql`
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
email TEXT NOT NULL UNIQUE,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
)
`;
});
```
### Running Migrations
```ts
import * as Migrator from 'effect/sql/Migrator';
// Create a migrator (optionally with schema dump support)
const migrate = Migrator.make({
// Optional: dump schema after migrations
dumpSchema: (path, table) => Effect.void
});
// Load migrations from filesystem
const completed =
yield*
migrate({
loader: Migrator.fromFileSystem('./migrations'),
schemaDirectory: './migrations', // optional: where to dump _schema.sql
table: 'effect_sql_migrations' // optional: custom table name (default)
});
```
### Migration Loaders
```ts
// From filesystem (requires both FileSystem and Path services)
Migrator.fromFileSystem('./migrations');
// From Vite/bundler glob import; only .js/.ts/.mjs/.mts keys are loaded
Migrator.fromGlob(import.meta.glob('./migrations/*.{js,ts,mjs,mts}'));
// From a record of effects (inline)
Migrator.fromRecord({
'0001_create_users': Effect.gen(function* () {
const sql = yield* SqlClient;
yield* sql`CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL)`;
}),
'0002_add_email': Effect.gen(function* () {
const sql = yield* SqlClient;
yield* sql`ALTER TABLE users ADD COLUMN email TEXT`;
})
});
// From Babel-style glob (keys like _0001_createUsersTs or _0001_createUsersMts)
Migrator.fromBabelGlob(migrations);
```
`fromFileSystem` resolves dynamic imports through the platform `Path` service so absolute Windows paths become valid file URLs. Its loader requirement is `FileSystem | Path`; aggregate platform layers already provide both, but a standalone `FileSystem` layer must now be paired with the matching platform-aware `Path` layer (not a POSIX-only layer on Windows).
### Migration Errors
Migration execution failures now remain typed `MigrationError` values rather
than being converted to defects. Handle/report them at the deployment boundary;
do not treat failed migrations as a successful application startup.
```ts
import * as Migrator from 'effect/sql/Migrator';
// MigrationError has a `kind` discriminator:
// - "BadState" — migrations table in unexpected state
// - "ImportError" — failed to import migration file
// - "Failed" — migration execution failed
// - "Duplicates" — duplicate migration IDs found
// - "Locked" — migrations already running (concurrent protection)
```
## Driver Packages and Layer Setup
Effect SQL uses driver-specific packages that provide `SqlClient` layers.
### Common Drivers
| Package | Database |
| ------------------------- | ------------------------------------ |
| `@effect/sql-pg` | PostgreSQL (native Effect protocol client) |
| `@effect/sql-pglite` | Embedded PostgreSQL/PGlite |
| `@effect/sql-mysql2` | MySQL (via `mysql2`) |
| `@effect/sql-sqlite-node` | SQLite (`node:sqlite`; Node 22.16+) |
| `@effect/sql-libsql` | libSQL / Turso |
| `@effect/sql-mssql` | Microsoft SQL Server |
| `@effect/sql-clickhouse` | ClickHouse |
### PostgreSQL Setup
```ts
import { Effect, Layer } from 'effect';
import { PgClient } from '@effect/sql-pg';
import { SqlClient } from 'effect/sql/SqlClient';
// Static config
const DatabaseLayer = PgClient.layer({
host: 'localhost',
port: 5432,
database: 'myapp',
username: 'postgres',
password: Redacted.make('secret'),
// Optional settings:
maxConnections: 10,
idleTimeout: '30 seconds',
transformResultNames: (s) => camelCase(s), // snake_case → camelCase
transformQueryNames: (s) => snakeCase(s) // camelCase → snake_case
});
// From Config (reads from environment/config provider)
const DatabaseLayerConfig = PgClient.layerConfig({
url: Config.Redacted('DATABASE_URL')
});
// The layer provides both PgClient and SqlClient services
const program = Effect.gen(function* () {
const sql = yield* SqlClient; // generic interface
// or
const pg = yield* PgClient.PgClient; // PostgreSQL-specific service
});
const main = program.pipe(Effect.provide(DatabaseLayer));
```
### PgClient-Specific Features
### Native PostgreSQL client and codecs
Pool `idleTimeout` now defaults to **60 seconds** (was 10); set it explicitly to
retain the old connection-retirement behavior. `acquireTimeout` bounds waiting
for a checkout and fails with `SqlError` (reason `ConnectionError`) when exhausted,
separately from connection establishment's `connectTimeout`. `keepAlive` is an
optional idle duration that enables TCP keepalive probes.
`onAcquire: Effect<void, never, R>` runs **per checkout**, not per physical
socket, in the consuming fiber. It runs once per statement/stream outside a
transaction and per transaction/reserve/listen/notify acquisition. Its services
are captured at client construction, with consuming-fiber services taking
precedence; constructor/layer requirements now reflect `R`. Hook finalizers end
with the acquisition. Keep it cheap while holding the connection; with
`makeClient({ acquireForStream: true })` it runs before a stream opens its own
connection.
`PgClient` uses `PgConnection` and `PgPool` for native binary queries, prepared
statements, pipelining, streaming, cancellation, and notifications. Use `make`
for a pool or `makeClient` for one connection. `types` accepts a `PgTypes.Registry`.
Bind JSON explicitly with `sql.json`, and send one statement per query string.
Named preparation is enabled by default; set `prepare: false` for incompatible
poolers, or use statement-level `unprepared` / `valuesUnprepared`.
Prepared names are namespaced per physical connection to avoid collisions when
backend sessions are shared. This does not replace checking your pooler's support
for named prepared statements. Pools retire fatal sessions before the next borrow
and retire sessions with an unconfirmed `CancelRequest`, including cancellation
while idle, so a delayed cancel cannot hit a later checkout. The current checkout
and unpooled sessions can still encounter their own late cancellation. Failed
background pool acquisitions are retried on borrow; callers already waiting on
their own failed acquisition receive that failure.
Decode rows according to the actual driver boundary: `int8` is `bigint`, `date`
is a string, timestamps are `Date` (millisecond precision), and `bytea` is
`Uint8Array`. Infinite/out-of-range timestamps decode to invalid Dates; validate
before constructing domain instants. Timestamp encoders accept Date or epoch
milliseconds; invalid Date encoding fails. A Date binds as `timestamptz`, so use
UTC session time or `PgTypes.timestamp(value)` for UTC fields in a timestamp column.
`executeRaw` returns `PgConnection.Result`. Binary `regclass` and `regclass[]`
decode as unsigned numeric OIDs; cast to `text` in SQL when relation names are
required.
Binary `interval` preserves independent months, days, and bigint microseconds,
including interval arrays and typed interval parameters; do not flatten calendar
months/days into a fixed duration. Released `PgConnection` pins reject queries,
streams, and listeners, ignore interrupt, and close a session released with work
still in flight so it cannot affect the next borrower.
Unregistered OIDs decode as UTF-8 text, suitable for scalar enum labels but not
arbitrary binary types. Register custom scalar/array codecs through
`PgTypes.makeRegistry().register(elementOid, codec, { arrayOid })` and pass the
registry as `types`. Decode failures close the connection and fail pending work.
`inet` uses `IpInterface`; `cidr` uses `IpNetwork` and rejects host bits.
Use structured `startupParameters` and opaque `startupOptions` for per-connection
session defaults. Passwords may be infallible, service-free Effects reevaluated
per physical connection. `sslmode=prefer` and `allow` try TLS first and fall back
only when the server declines SSLRequest; handshake/certificate failures remain
fatal. Explicit SSL options take precedence. `maxMessageSize` defaults to 16 MiB;
when multiplexing is enabled, `multiplexConcurrency` defaults to 32.
- `PgProtocol`: PostgreSQL 3.0 frontend encoding and incremental backend-frame
parsing. Stateful parser failures are terminal and synchronous; lift a parser
boundary with `Effect.try` and preserve the protocol error.
- `PgTypes`: binary scalar and one-dimensional array OID codecs returning typed
`Result` failures. Internal field-reader throwing fast paths are not the public
application API.
- `PgAuth`: MD5 and SCRAM-SHA-256 authentication codecs with typed `Result`
failures. Treat authentication material as secrets.
- Encoded frames/decoded byte fields are buffer views. Copy bytes that must
outlive the owning message. Read `packages/sql/pg/src/{PgProtocol,PgTypes,PgAuth}.ts`
for exact signatures before implementing an adapter.
Driver releases also update production dependencies for D1, mysql2, and PGlite;
keep the Effect-family package versions aligned when upgrading those adapters.
`PgMigrator` dumps use resolved URL/socket/startup/TLS settings and keep passwords
out of `pg_dump` argv. Optional TLS modes (`prefer` / `allow`) dump using
`verify-full`, so a non-TLS server can reject the dump even if normal connections
can fall back.
SQL statement spans now use `Statement.spanName` / `spanTarget`: database
namespace, server address/port, or database system, falling back to `sql.execute`.
The Effect execution method is `effect.sql.method`, not `db.operation.name`.
Transaction spans are internal with `effect.sql.transaction.*` events. With
`Statement.SpanPropagationEnabled`, statement spans record
`db.client.connection.wait_time_ms`; PostgreSQL adds `db.connect` covering the
part of a newly created session's connection that the statement waited for.
### PgClient JSON and Notifications
```ts
const pg = yield* PgClient.PgClient;
// JSON parameter helper
sql`INSERT INTO data ${sql.insert({ metadata: pg.json({ key: 'value' }) })}`;
// LISTEN/NOTIFY
const notifications = yield* pg.listen('my_channel'); // scoped Dequeue<string, SqlError>
yield* pg.notify('my_channel', 'hello');
const messages = Stream.fromQueue(notifications);
```
Listener acquisition completes after registration, giving an explicit readiness
boundary. PostgreSQL listener queues carry `SqlError` when the connection fails;
retry a scoped effect that reacquires the listener, not the failed queue. Intentional
scope closure interrupts consumers. PGlite also returns a scoped notification dequeue.
`SqlModel.makeResolvers().insert` requires both input encoding and row decoding
services; `insertVoid` needs only input encoding services. D1 statement `.raw`
returns the complete native result; read `.results` for rows. Durable Object SQL
transactions support nested child rollback but no explicit async operations inside
the transaction. Use `Statement.SpanPropagationEnabled` to opt into driver span
parenting under the statement span (whose name follows `Statement.spanName`).
Single-connection drivers retain exclusion for the whole statement/stream, not
just acquisition. SQLite node/bun/in-memory WASM preserve the original typed SQL
failure when SQLite already auto-rolled back; OPFS/React Native are unchanged.
SQLite WAL setup contention retries within `busyTimeout` and setup failures are
typed `SqlError` (Node read-only connections skip WAL initialization).
MSSQL `withoutTransforms()` and procedure calls join the active transaction.
ClickHouse exposes its native `.client`; early stream termination kills the
server query instead of leaving it running.
Reactive queries stop rerunning after failure rather than retaining a broken
subscription. Applications can optionally augment `Reactivity.Keys` for typed
key names/record IDs; libraries must not augment this global registry.
`Model.Class.extend` retains inherited variant fields when extending SQL models.
**SQL resolver isolation:** only the transaction connection separates batches.
Request encoding uses each caller's context, but `execute` and result decoding
use the **first entry's** context. Different tenant/auth services outside a
transaction can therefore share a batch; use separate resolver instances or
explicit tenant/auth values in the request and SQL. Caller context alone is not
an authorization boundary.
### PGlite Setup
Use `@effect/sql-pglite` for embedded PostgreSQL-compatible databases backed by `@electric-sql/pglite`. Its layer provides both the PGlite-specific service and the generic `SqlClient` service.
```ts
import { Config, Effect } from 'effect';
import { PgliteClient, PgliteMigrator } from '@effect/sql-pglite';
import * as Migrator from 'effect/sql/Migrator';
import { SqlClient } from 'effect/sql/SqlClient';
const PgliteLayer = PgliteClient.layer({
dataDir: 'idb://myapp'
});
const PgliteLayerConfig = PgliteClient.layerConfig({
dataDir: Config.String('PGLITE_DATA_DIR')
});
const program = Effect.gen(function* () {
const sql = yield* SqlClient; // generic interface
const pglite = yield* PgliteClient.PgliteClient;
yield* sql`INSERT INTO data ${sql.insert({ metadata: pglite.json({ key: 'value' }) })}`;
const notifications = yield* pglite.listen('my_channel');
yield* pglite.notify('my_channel', 'hello');
yield* pglite.refreshArrayTypes;
const snapshot = yield* pglite.dumpDataDir('gzip');
});
const runPgliteMigrations = PgliteMigrator.run({
loader: Migrator.fromFileSystem('./migrations')
});
```
`PgliteClient.layerFrom` wraps an existing acquired client. `PgliteMigrator` reuses the shared migrator loaders, but it does not currently write schema dumps for `schemaDirectory`; use PGlite data-dir persistence or `PgliteClient.dumpDataDir` for embedded snapshots.
### Connection Reservation
```ts
const sql = yield* SqlClient;
// Reserve a dedicated connection (useful for advisory locks, temp tables, etc.)
const conn = yield* sql.reserve; // Effect<Connection, SqlError, Scope>
```
## Streaming Large Result Sets
Use `.stream` on any statement for memory-efficient processing of large result sets:
```ts
import { Stream } from 'effect';
const sql = yield* SqlClient;
// Stream rows one at a time
const allUsers = sql`SELECT * FROM users`.stream;
// Process with Stream combinators
yield*
allUsers.pipe(
Stream.filter((user) => user.active),
Stream.map((user) => user.email),
Stream.runCollect
);
// Streaming uses the driver's bounded result batching.
```
## Error Handling
All SQL operations can fail with `SqlError`:
```ts
import { SqlError } from 'effect/sql/SqlError';
yield*
sql`SELECT * FROM users`.pipe(
Effect.catchTag('SqlError', (err) => {
console.error('SQL failed:', err.message);
console.error('Cause:', err.cause); // underlying driver error
return Effect.succeed([]);
})
);
```
Unique constraint failures classify as `err.reason._tag === 'UniqueViolation'` when the driver exposes enough detail. The `constraint` field names the violated constraint; classifiers fall back to `'unknown'` when the name is missing.
```ts
const constraintName = (err: SqlError) =>
err.reason._tag === 'UniqueViolation'
? err.reason.constraint || 'unknown'
: undefined;
```
Keep non-unique integrity failures on their own paths; they remain `ConstraintError` rather than `UniqueViolation`.
`SqlResolver` also exposes `ResultLengthMismatch` for ordered resolvers when result count doesn't match request count.
## Complete Example
```ts
import { Effect, Layer, Schema } from 'effect';
import { Model } from 'effect/schema';
import { SqlClient } from 'effect/sql/SqlClient';
import * as SqlModel from 'effect/sql/SqlModel';
import * as Migrator from 'effect/sql/Migrator';
import { PgClient } from '@effect/sql-pg';
// 1. Define Model
const UserId = Schema.Number.pipe(Schema.brand('UserId'));
class User extends Model.Class<User>('User')({
id: UserId.pipe(Model.FieldExcept(["insert"])),
name: Schema.String,
email: Schema.String,
createdAt: Model.DateTimeInsertFromDate,
updatedAt: Model.DateTimeUpdateFromDate
}) {}
// 2. Build Repository
const makeUserRepo = Effect.gen(function* () {
const repo = yield* SqlModel.makeRepository(User, {
tableName: 'users',
spanPrefix: 'UserRepo',
idColumn: 'id'
});
return repo;
});
// 3. Run Migrations
const runMigrations = Migrator.make({})({
loader: Migrator.fromFileSystem('./migrations')
});
// 4. Wire it up
const DatabaseLayer = PgClient.layer({
host: 'localhost',
database: 'myapp',
username: 'postgres'
});
const program = Effect.gen(function* () {
yield* runMigrations;
const repo = yield* makeUserRepo;
const user = yield* repo.insert({
name: 'Alice',
email: 'alice@example.com'
});
const found = yield* repo.findById(user.id);
yield* Effect.log(`Created user: ${found.name}`);
});
Effect.runPromise(program.pipe(Effect.provide(DatabaseLayer)));
```
## Anti-Patterns
- **String concatenation in queries** — Always use tagged template interpolation or `sql.unsafe()`. Never build SQL strings manually.
- **Forgetting `sql.insert()` / `sql.update()`** — Use the helpers for INSERT/UPDATE instead of manually listing columns and values.
- **Not using transactions** — Wrap multi-statement mutations in `sql.withTransaction()` for atomicity.
- **Ignoring `SqlSchema`** — Raw queries return untyped rows. Use `SqlSchema.findOne/findAll/void` for validated I/O.
- **Assuming custom delay is required for batching** — `SqlResolver` resolvers batch concurrently queued requests by default via `Effect.yieldNow`. Add `RequestResolver.setDelay` only to widen the collection window when the latency tradeoff is acceptable.
Versions
| Repository | Score | Licence |
|---|---|---|
| This page · mpsuesser/opencode-effect-enforcer | 91 | MIT |
| mpsuesser/opencode-effect-enforcer | 91 | MIT |
Database snapshot: 13.11.2026 · package version 21bbf5e0f9ab (SHA-256 digest of the entire package)
Downloads in the last 30 days: 0 · ranking
Report a problem with this skill: harmful content, incorrect descriptions or an author’s removal request.
Similar skills
- 100sqlmodel
Comprehensive guide for working with SQLModel, PostgreSQL, and SQLAlchemy in FastAPI projects. Use when working with database operations in FastAPI including: (1) Defining SQLModel models and relationships, (2) Database connection and session management, (3) CRUD operations, (4)… - 95effect-batching
Implement automatic request batching and deduplication using Effect's Request, RequestResolver, and SqlResolver APIs. Use this skill when solving N+1 query problems, building batched data-fetching layers, or integrating request caching with resolvers. - 95effect-batching
Implement automatic request batching and deduplication using Effect's Request, RequestResolver, and SqlResolver APIs. Use this skill when solving N+1 query problems, building batched data-fetching layers, or integrating request caching with resolvers. - 100go-database
Use when writing or reviewing Go SQL queries, transactions, repositories, pools, migrations, or row mapping with database/sql, pgx, or an ORM; also for slow queries and queries in loops. - 100connect-from-dotnet
Connects a .NET application to Azure SQL Database with Microsoft.Data.SqlClient: which package references are required, the encryption defaults and what changed, connection pooling and the keys that split a pool, and managed identity or other Microsoft Entra ID modes. Use when a… - 100effect-ts
Use for nontrivial Effect 4 work including services/layers, typed errors, Schema/JsonSchema, Config, runtime/concurrency, @effect/vitest, effect/ai and @effect/ai-*, effect/sql and @effect/sql-*, Effect Atom (@effect/atom-*), or @prb/effect-next.