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
Top comments (0)