effect-sql

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 ~/.cache/effect-v4/. Browse and read files there directly to look up APIs, types, and implementations.

Reference this for:

• packages/effect/src/unstable/sql/ — Core SQL modules (SqlClient, SqlSchema, SqlModel, SqlResolver, Migrator, Statement)
• packages/effect/src/unstable/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/unstable/sql path:

import { SqlClient } from 'effect/unstable/sql/SqlClient';
import * as SqlSchema from 'effect/unstable/sql/SqlSchema';
import * as SqlModel from 'effect/unstable/sql/SqlModel';
import * as SqlResolver from 'effect/unstable/sql/SqlResolver';
import * as Migrator from 'effect/unstable/sql/Migrator';

Alternatively, the barrel exports namespace modules:

import { SqlClient, SqlSchema, SqlModel, SqlResolver, Migrator } from 'effect/unstable/sql';
// With the barrel, the service is SqlClient.SqlClient.

For Model schemas (used with SqlModel):

import { Model } from 'effect/unstable/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

import { Effect } from 'effect';
import { SqlClient } from 'effect/unstable/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: '[email protected]' })}`;

	// INSERT multiple rows
	yield* sql`INSERT INTO users ${sql.insert([
		{ name: 'Alice', email: '[email protected]' },
		{ name: 'Bob', email: '[email protected]' }
	])}`;

	// 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}`;
});