import PackageManagers from "../../../../components/PackageManagers.astro"; export const deps = `@alchemy.run/sql @effect/sql-pg`; SQL.Postgres provides tagged-template queries over a Postgres connection. Queries are Effects with typed SqlError failures, interruption, and tracing.
Install @alchemy.run/sql and the Effect Postgres driver:
Connect
import * as SQL from "@alchemy.run/sql/Postgres";
import * as Effect from "effect/Effect";
import type * as Redacted from "effect/Redacted";
export const makeQueries = <E, R>(
connectionString:
| Redacted.Redacted<string>
| Effect.Effect<Redacted.Redacted<string>, E, R>,
) =>
Effect.gen(function* () {
const sql = yield* SQL.Postgres({ url: connectionString });
return {
listUsers: () => sql<{ id: number; name: string }>`SELECT id, name FROM users`,
};
}); url accepts a redacted URL or an Effect resolving one, such as Config.Redacted("DATABASE_URL") or a runtime binding's connectionString. Other @effect/sql-pg pool options pass through. Call queries within a request or an explicit Effect.scoped block: the pool opens lazily and closes with that scope.
Queries
Interpolated values are parameters, never string concatenation:
const user = yield* sql`SELECT * FROM users WHERE id = ${id}`;
yield* sql`INSERT INTO users ${sql.insert({ name, email })}`;
const rows = yield* sql`
SELECT * FROM users WHERE id IN ${sql.in(ids)}
`; Rows are plain objects; supply a row type with sql<Row>. The
effect/sql/Statement API also provides
fragments, sql.csv, sql.and, and identifier escaping.
Errors
Failures surface as SqlError in the typed error channel:
const users = yield* sql`SELECT * FROM users`.pipe(
Effect.catchTag("SqlError", (e) =>
Effect.succeed([]).pipe(Effect.tap(() => Effect.logWarning(e))),
),
); Transactions
Wrap a group of queries in sql.withTransaction — the whole effect
commits or rolls back together:
yield* sql.withTransaction(
Effect.gen(function* () {
yield* sql`UPDATE accounts SET balance = balance - ${amount} WHERE id = ${from}`;
yield* sql`UPDATE accounts SET balance = balance + ${amount} WHERE id = ${to}`;
}),
); Provide as a service
Depend on the generic SqlClient tag, then provide the database layer:
import * as SqlClient from "effect/sql/SqlClient";
const makeUsers = Effect.gen(function* () {
const sql = yield* SqlClient.SqlClient;
return {
find: (id: number) => sql<User>`SELECT * FROM users WHERE id = ${id}`,
};
});
const users = yield* makeUsers.pipe(
Effect.provide(SQL.PostgresLayer({ url: connectionString })),
); The layer provides SqlClient.SqlClient and @effect/sql-pg's PgClient from one per-execution pool. Other drivers can satisfy the generic service, but changing engines still requires compatible SQL and transaction semantics.
Provider setup
Choose a database and deployment guide in SQL databases; the client above only needs a connection URL. Hyperdrive and Fly Managed Postgres cover runtime-specific bindings.
Where next
Read Migrations to apply committed SQL files, or Drizzle on Postgres for typed schemas over the same driver. Connection lifecycle covers per-request cleanup.