DEV Community

Cover image for Typed raw SQL for Effect
İsmail Karslı
İsmail Karslı

Posted on AI-assisted

Typed raw SQL for Effect

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 };
});
Enter fullscreen mode Exit fullscreen mode

author is { id: number; name: string }, and books is:

{
  published_year: number | null;
  rank: bigint;
  rating: string | null;
  reviews: bigint;
  title: string;
}[]
Enter fullscreen mode Exit fullscreen mode

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"?
Enter fullscreen mode Exit fullscreen mode

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>
Enter fullscreen mode Exit fullscreen mode

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();
    }),
  );
Enter fullscreen mode Exit fullscreen mode

Setup

pnpm add @typesql/core @typesql/effect effect @effect/sql-pg
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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)