Menu

Typed raw SQL for Effect
📰

Typed raw SQL for Effect

DEV Community: postgres·İsmail Karslı·about 2 hours ago
#yCNKQWXu
#devtopostgresql#devto#dev#effect#rating
Reading 0:00
15s threshold

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

Continue reading — create a free account

Join HashtagPLUS to read full articles, follow hashtags, vote, and join the conversation.