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 ` ) .…