Effect
@typesql/effect runs your queries as Effects on an Effect SqlClient. A statement is the Effect of its rows, so you yield* it, and it fails with the client's SqlError.
| Database | Driver package | Dialect |
|---|---|---|
| PostgreSQL | @effect/sql-pg | postgresDialect from @typesql/core/postgres |
| SQLite | @effect/sql-sqlite-node | sqliteDialect from @typesql/core/sqlite |
| MySQL, MariaDB | @effect/sql-mysql2 | mysqlDialect or mariadbDialect from @typesql/core/mysql |
pnpm add @typesql/core @typesql/effect effect @effect/sql-pgIt needs effect 4.1.3 or later. The schema is a TypeSQL one: declare it, or generate it from the database.
Create sql
createSql takes the dialect. Without a client, statements run on the SqlClient of the fiber that runs them, so they require it like any other service:
import { PgClient } from "@effect/sql-pg";
import { postgresDialect } from "@typesql/core/postgres";
import { createSql } from "@typesql/effect";
import { Effect, Redacted } from "effect";
const sql = createSql<DB>(postgresDialect);
const program = Effect.gen(function* () {
const users = yield* sql("SELECT id, email FROM users");
return users.length;
});
await Effect.runPromise(
program.pipe(
Effect.provide(
PgClient.layer({ url: Redacted.make(process.env["DATABASE_URL"]!) }),
),
),
);With a client, they run on that one and require nothing:
import { SqlClient } from "effect/sql/SqlClient";
const users = Effect.gen(function* () {
const client = yield* SqlClient;
const sql = createSql<DB>(postgresDialect, client);
// The statement itself is an Effect<…, SqlError, never>.
return yield* sql("SELECT id, email FROM users");
});Run queries
A statement is an Effect, the same one as exec(): yield it, pipe it, or pass it to any function that takes an Effect.
| Effect of | Fails with | |
|---|---|---|
yield* q, q.exec() | Row[] | SqlError |
q.execTakeFirstOrNone() | Row | undefined | SqlError |
q.execTakeFirst() | Row | SqlError | NoResultError |
q.execCount() | number: the rows changed or returned | SqlError |
q.stream() | a Stream of Row | SqlError |
import { Effect, Stream } from "effect";
const program = Effect.gen(function* () {
const user = yield* sql(
`SELECT id, email FROM users WHERE id = ${sql.param(7)}`,
).execTakeFirst();
const emails = yield* sql("SELECT email FROM users").pipe(
Effect.map((users) => users.map((user) => user.email)),
);
const changed = yield* sql(
"UPDATE users SET display_name = NULL",
).execCount();
yield* sql("SELECT id FROM users").stream().pipe(
Stream.runForEach((user) => Effect.log(user.id)),
);
});NoResultError, from@typesql/effect, is a tagged error with the statement'stext: handle it withEffect.catchTag("NoResultError", …).stream()reads rows as the driver streams them.@effect/sql-sqlite-nodehas no streaming, so there it dies; useexec().${query}splices a statement as a subquery, and everything else onsql(sql.param,sql.fragment,sql.values,sql.prepare, …) works as on every adapter.- Statements run through the client's
unsafe, so they are traced like the client's own.
Transactions
sql.withTransaction(effect) is the client's withTransaction. The statements effect runs are the transaction's, a failure rolls it back, and inside another transaction it is a savepoint. There is no tx: the same sql runs in the transaction.
const id = yield* sql.withTransaction(
Effect.gen(function* () {
const { id } = yield* sql(
`INSERT INTO users (email) VALUES (${sql.param("a@b.c")}) RETURNING id`,
).execTakeFirst();
yield* sql(
`INSERT INTO posts (author_id, title) VALUES (${sql.param(id)}, ${sql.param("Hello")})`,
);
return id;
}),
);It takes one argument, so it pipes: effect.pipe(sql.withTransaction). The client's own client.withTransaction(effect) does the same.
Row names and plugins
Rows keep the database's column names: the client's transformResultNames is bypassed, since the types describe the names the database returns. Attach CasePlugin for camelCase keys:
import { CasePlugin } from "@typesql/core/plugins";
const camel = sql.withPlugin(new CasePlugin());
const users = yield* camel("SELECT display_name FROM users"); // { displayName }[]Types
Rows are typed as each driver returns them. Pass another type map as the second type argument when your client is configured differently.
- PostgreSQL:
EffectPgTypeMap.int8(socount(*)) is abigint,numerica string,timestampandtimestamptzaDate,dateits text,timeabigintof microseconds andintervalanEffectPgInterval. Types the driver has no codec for areunknown. See Type mapping. - SQLite: SQLite's
DefaultTypeMap, as for node:sqlite. - MySQL and MariaDB:
EffectMysqlTypeMap. It is mysql2's map, withBIGINTasnumber | string: the driver setssupportBigNumbers, so a value past 2^53 is a string.
Parameters
Values are bound by the driver, with two exceptions on PostgreSQL. An array is sent as PostgreSQL's array text and sql.json(value) as JSON text, so the server reads them as the parameter's type. The driver alone would send an array of strings as text[], which a uuid[] or int8[] parameter rejects.
On SQLite and MySQL, values are converted as on @typesql/sqlite and @typesql/mysql.