I wanted to write plain SQL in Effect code and still get typed rows, without a query builder and without writing row types by hand. So I made @typesql/effect.
import { Effect } from "effect"; import { createSql } from "@typesql/effect"; import { postgresDialect } from "@typesql/core/postgres"; import type { DB } from "./db/schema.js"; const sql = createSql<DB>(postgresDialect); export const program = Effect.gen(function* () { const author = yield* sql( "SELECT id, name FROM authors ORDER BY followers DESC LIMIT 1", ).execTakeFirst(); const books = yield* sql(` WITH stats AS ( SELECT book_id, round(avg(rating), 1) AS rating, count(*) AS reviews FROM reviews GROUP BY book_id ) SELECT b.title, b.published_year, s.rating, coalesce(s.reviews, 0) AS reviews, rank() OVER (ORDER BY s.rating DESC NULLS LAST) AS rank FROM books b LEFT JOIN stats s ON s.book_id = b.id WHERE b.author_id = ${sql.param(author.id)} ORDER BY rank, b.published_year `).exec(); return { author, books }; }); author is { id: number; name: string }, and books is:
{ published_year: number | null; rank: bigint; rating: string | null; reviews: bigint; title: string; }[] The compiler reads the query. rating is a string because round(avg(...)) returns numeric, and it's nullable because of the LEFT JOIN. reviews isn't nullable because of the coalesce. count(*) and rank() return int8, so they're bigint.
A typo is a type error:
sql("SELECT titel FROM books"); // column "titel" does not exist. Perhaps you meant the column "title"? Effects
A statement is an Effect, and yield* runs .exec(). There's also execTakeFirst() (fails with NoResultError), execTakeFirstOrNone(), execCount() and stream(). Errors and the SqlClient requirement show up in the type:
const findAuthor = (id: number) => sql(`SELECT id, name FROM authors WHERE id = ${sql.param(id)}`).execTakeFirst(); // Effect<{ id: number; name: string }, SqlError | NoResultError, SqlClient> const orNull = findAuthor(42).pipe( Effect.catchTag("NoResultError", () => Effect.succeed(null)), ); // Effect<{ id: number; name: string } | null, SqlError, SqlClient> Transactions use the client's withTransaction, and the same sql works inside it:
const addReview = (bookId: number, rating: number) => sql.withTransaction( Effect.gen(function* () { yield* sql( `INSERT INTO reviews (book_id, rating) VALUES (${sql.param(bookId)}, ${sql.param(rating)})`, ); return yield* sql( `SELECT count(*) AS reviews FROM reviews WHERE book_id = ${sql.param(bookId)}`, ).execTakeFirst(); }), ); Setup
pnpm add @typesql/core @typesql/effect effect @effect/sql-pg It needs effect 4.1.3+ and TypeScript 7.1+ (the native compiler). It also works with @effect/sql-sqlite-node and @effect/sql-mysql2.
The schema is a TypeScript type. You can write it by hand or generate it:
pnpm exec typesql introspect --url postgres://localhost/app --out src/db/schema.ts Big statements can run into the compiler's limits. sql.as<Row>() lets you type those yourself.
Docs: https://typesql.karsli.net/adapters/effect
Source: https://github.com/ismailkarsli/TypeSQL
