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 };
});
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: typesql.karsli.net
Source: ismailkarsli/TypeSQL
Raw SQL parser in TypeScript types
Hello. I created this library to use raw SQL queries within TypeScript.
I don't particularly like ORMs, so lately I've been using the Kysely query builder library, which is very similar to SQL. However, even though it's very similar, it still has some catches and special cases.
Therefore, I started looking for ways to use raw SQL directly. Frankly, there weren't many alternatives for this specific case, and those that were available were generally hacky solutions requiring codegens or running a process.
Considering that TypeScript has sped up recently with tsc 7 and tsrs, it seemed logical to do this directly in TypeScript. However, since writing a project of this scale manually as a hobby would take too long, I used AI. I should add that I think calling it a "vibe slop" is unfair because for the past two weeks I've been constantly trying to make the right decisions and keep the code organized.
Honestly, although it developed faster than I expected, it wasn't as easy as "create sql parser, no mistakes," but I believe I reached a good point in two weeks. I added it to a project with 1 million lines of code that I've been working on for 6 years, and it's working flawlessly.
At the end of the day, I think I achieved a good result. It can infer return types even in complex cases, catch errors, highlight syntax, and inline suggest.
What do you think? Do you think it's worth using?
ismailkarsli/typesql
6 points · 1 comment
Show HN: A SQL parser and type checker written in TypeScript types
3 points · 1 comment
What if TypeScript could read your SQL?
TypeSQL parses queries inside the type checker. You get exact row and parameter types from plain SQL strings, with no codegen and no database connection.
Works with pg, postgres.js, SQLite, and existing Kysely/Drizzle/Prisma.
ismailkarsli/TypeSQL
1 like
TypeSQL: type-safe raw SQL in TypeScript
I made this library that checks raw SQL strings with the TypeScript compiler. It infers the types of rows and parameters, including pretty complex wueries. Typos and invalid queries become compile errors.
It has some helpers but most of the time you will only need sql() function.
5 points · 3 comments
TypeSQL: type-safe plain SQL in TypeScript
I like writing SQL. But in TypeScript, raw SQL usually means giving up types: you get any[] back, or you write the row type by hand and hope it stays in sync with the query.
So I built TypeSQL. It checks your SQL strings with the TypeScript compiler and gives you exact types for rows and parameters.
const rows = await sql(
`SELECT u.email, p.title
FROM users u LEFT JOIN posts p ON p.author_id = u.id
WHERE u.id = ${sql.param(userId)}`,
).execute();
// rows: { email: string; title: string | null }[]
title is string | null because of the LEFT JOIN. userId must be a number, because it's compared to u.id. Nobody wrote those types: the compiler worked them out from the SQL.
How it works
TypeSQL is a SQL parser written in TypeScript's type system. When you call sql("…") with a string literal, the compiler parses the SQL and looks up each table and column in your schema type. This happens in your editor and in tsc.
At runtime, sql() does very little. It builds { text, values }, and your driver sends the text as you wrote it.
Getting started
Install the core package and the adapter for your driver:
npm install @typesql/core @typesql/pg pg
npm install -D typescript@7.1.0-beta
Describe your tables with your database's type names:
import type { Col } from "@typesql/core/postgres";
export interface DB {
users: {
id: Col<"int4", { hasDefault: true }>;
email: Col<"text">;
display_name: Col<"text", { nullable: true }>;
created_at: Col<"timestamptz", { hasDefault: true }>;
};
posts: {
id: Col<"int8", { hasDefault: true }>;
author_id: Col<"int4">;
title: Col<"text">;
};
}
Then create sql from your client:
import { Pool } from "pg";
import { createSql } from "@typesql/pg";
const sql = createSql<DB>(new Pool());
You don't have to write the schema by hand. You can generate it from a live database with typesql introspect, derive it from your migrations, or reuse an existing Kysely, Drizzle or Prisma schema.
Mistakes become compile errors
Errors show up on the SQL string, worded like the database's own errors:
sql("SELECT emial FROM users");
// column "emial" does not exist. Perhaps you meant the column "email"?
sql("SELECT * FROM post");
// relation "post" does not exist. Perhaps you meant the relation "posts"?
sql("SELECT id FROM users WHERE id = $1", "7");
// Argument of type 'string' is not assignable to parameter of type 'number'.
You find these in your editor, not in production.
Nullability that follows the SQL
This is the part I'm most happy with. TypeSQL works out nullability from the query itself:
- Columns from the outer side of a LEFT JOIN become nullable.
- COALESCE(x, 'default') makes them non-null again.
- WHERE x IS NOT NULL narrows x to non-null.
- Aggregates like max() are nullable, and count() is not.
Tools that ask a running database for types can't do this, because Postgres doesn't report nullability for result columns.
Parameters
Parameters take their type from where you use them. There are three ways to write them:
// Positional
sql("SELECT id FROM users WHERE id = $1", 7);
// Named
sql("SELECT id FROM users WHERE email = :email", { email: "a@b.c" });
// Inline
sql(`SELECT id FROM users WHERE id = ${sql.param(userId)}`);
Values are always sent as parameters, never pasted into the SQL text, so SQL injection isn't possible through them.
Writes are checked too
INSERT, UPDATE, DELETE and MERGE are checked against your schema:
sql("INSERT INTO users (display_name) VALUES ($1)", "Ada");
// null value in column "email" of relation "users" violates not-null constraint
Target columns must exist, values must fit their types, and every NOT NULL column without a default must be set. RETURNING gives the statement a row type.
Dynamic SQL
Sometimes a query has optional parts. Fragments let you build it from pieces, and the finished statement is still checked as a whole:
const recent = sql.fragment("u.id > $1", 10);
const q = sql(
`SELECT u.email FROM users u WHERE ${recent} AND u.email <> $1`,
"a@b.c",
);
Optional pieces are checked both with and without the fragment. There are also helpers for lists, identifiers, SET clauses and bulk VALUES. Most of the time, though, sql() is all you need.
Tested against PostgreSQL itself
A SQL parser in the type system sounds fragile, so I test it against PostgreSQL 18's own regression suite: about 50,000 statements. TypeSQL agrees with Postgres on all 49,999 statements it can check. CI fails if a single one disagrees.
TypeSQL also supports SQLite, MySQL and MariaDB, each with its own parser and type rules.
Editor support
Hover any query to see its row type. This works in any TypeScript editor. The VS Code extension and the Neovim language server also add table and column completions and SQL highlighting inside sql() strings.
Limits
- TypeScript 7.1 is required. TypeSQL runs in the new native compiler. Older versions are too slow and aren't supported.
- Only SQL known at compile time is checked. For SQL built at runtime, you give the row type yourself with sql.as<Row>().
- Very large statements can hit the compiler's limits, for example around 42 UNION ALL branches. The docs list the exact numbers.
- No tagged templates (` sql… `). TypeScript doesn't expose their text to the type system.
Try it
- Try it online, with nothing to install: TypeScript Playground (it runs an older TypeScript, so treat it as a demo)
- Docs: typesql.karsli.net
- GitHub: ismailkarsli/TypeSQL
I'd love feedback, especially queries where TypeSQL gets a type wrong or gives up. Issues are welcome on GitHub.
TypeSQL: type-safe raw SQL in TypeScript
It checks your SQL strings with the TypeScript compiler and infers row and parameter types, even for complex queries. Typos and invalid queries become compile errors.
Most of the time, sql() is all you need.
ismailkarsli/TypeSQL
1 like
TypeSQL: type-safe raw SQL in TypeScript
I made this library that checks raw SQL strings with the TypeScript compiler. It infers the types of rows and parameters, including pretty complex wueries. Typos and invalid queries become compile errors.
4 comments
TypeSQL: type-safe raw SQL in TypeScript
I made this library that checks raw SQL strings with the TypeScript compiler. It infers the types of rows and parameters, including pretty complex wueries. Typos and invalid queries become compile errors.
It has some helpers but most of the time you will only need sql() function.
1 comment
Show HN: TypeSQL – Postgres SQL parsed in TypeScript's type system, no codegen
2 points · 2 comments
Mentions
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 }; }); 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: typesql.karsli.net Source: ismailkarsli/TypeSQL
Raw SQL parser in TypeScript types Hello. I created this library to use raw SQL queries within TypeScript. I don't particularly like ORMs, so lately I've been using the Kysely query builder library, which is very similar to SQL. However, even though it's very similar, it still has some catches and special cases. Therefore, I started looking for ways to use raw SQL directly. Frankly, there weren't many alternatives for this specific case, and those that were available were generally hacky solutions requiring codegens or running a process. Considering that TypeScript has sped up recently with tsc 7 and tsrs, it seemed logical to do this directly in TypeScript. However, since writing a project of this scale manually as a hobby would take too long, I used AI. I should add that I think calling it a "vibe slop" is unfair because for the past two weeks I've been constantly trying to make the right decisions and keep the code organized. Honestly, although it developed faster than I expected, it wasn't as easy as "create sql parser, no mistakes," but I believe I reached a good point in two weeks. I added it to a project with 1 million lines of code that I've been working on for 6 years, and it's working flawlessly. At the end of the day, I think I achieved a good result. It can infer return types even in complex cases, catch errors, highlight syntax, and inline suggest. What do you think? Do you think it's worth using? ismailkarsli/typesql
Show HN: A SQL parser and type checker written in TypeScript types
What if TypeScript could read your SQL? TypeSQL parses queries inside the type checker. You get exact row and parameter types from plain SQL strings, with no codegen and no database connection. Works with pg, postgres.js, SQLite, and existing Kysely/Drizzle/Prisma. ismailkarsli/TypeSQL
TypeSQL: type-safe raw SQL in TypeScript I made this library that checks raw SQL strings with the TypeScript compiler. It infers the types of rows and parameters, including pretty complex wueries. Typos and invalid queries become compile errors. It has some helpers but most of the time you will only need sql() function.
TypeSQL: type-safe plain SQL in TypeScript I like writing SQL. But in TypeScript, raw SQL usually means giving up types: you get any[] back, or you write the row type by hand and hope it stays in sync with the query. So I built TypeSQL. It checks your SQL strings with the TypeScript compiler and gives you exact types for rows and parameters. const rows = await sql( `SELECT u.email, p.title FROM users u LEFT JOIN posts p ON p.author_id = u.id WHERE u.id = ${sql.param(userId)}`, ).execute(); // rows: { email: string; title: string | null }[] title is string | null because of the LEFT JOIN. userId must be a number, because it's compared to u.id. Nobody wrote those types: the compiler worked them out from the SQL. How it works TypeSQL is a SQL parser written in TypeScript's type system. When you call sql("…") with a string literal, the compiler parses the SQL and looks up each table and column in your schema type. This happens in your editor and in tsc. At runtime, sql() does very little. It builds { text, values }, and your driver sends the text as you wrote it. Getting started Install the core package and the adapter for your driver: npm install @typesql/core @typesql/pg pg npm install -D typescript@7.1.0-beta Describe your tables with your database's type names: import type { Col } from "@typesql/core/postgres"; export interface DB { users: { id: Col<"int4", { hasDefault: true }>; email: Col<"text">; display_name: Col<"text", { nullable: true }>; created_at: Col<"timestamptz", { hasDefault: true }>; }; posts: { id: Col<"int8", { hasDefault: true }>; author_id: Col<"int4">; title: Col<"text">; }; } Then create sql from your client: import { Pool } from "pg"; import { createSql } from "@typesql/pg"; const sql = createSql<DB>(new Pool()); You don't have to write the schema by hand. You can generate it from a live database with typesql introspect, derive it from your migrations, or reuse an existing Kysely, Drizzle or Prisma schema. Mistakes become compile errors Errors show up on the SQL string, worded like the database's own errors: sql("SELECT emial FROM users"); // column "emial" does not exist. Perhaps you meant the column "email"? sql("SELECT * FROM post"); // relation "post" does not exist. Perhaps you meant the relation "posts"? sql("SELECT id FROM users WHERE id = $1", "7"); // Argument of type 'string' is not assignable to parameter of type 'number'. You find these in your editor, not in production. Nullability that follows the SQL This is the part I'm most happy with. TypeSQL works out nullability from the query itself: - Columns from the outer side of a LEFT JOIN become nullable. - COALESCE(x, 'default') makes them non-null again. - WHERE x IS NOT NULL narrows x to non-null. - Aggregates like max() are nullable, and count() is not. Tools that ask a running database for types can't do this, because Postgres doesn't report nullability for result columns. Parameters Parameters take their type from where you use them. There are three ways to write them: // Positional sql("SELECT id FROM users WHERE id = $1", 7); // Named sql("SELECT id FROM users WHERE email = :email", { email: "a@b.c" }); // Inline sql(`SELECT id FROM users WHERE id = ${sql.param(userId)}`); Values are always sent as parameters, never pasted into the SQL text, so SQL injection isn't possible through them. Writes are checked too INSERT, UPDATE, DELETE and MERGE are checked against your schema: sql("INSERT INTO users (display_name) VALUES ($1)", "Ada"); // null value in column "email" of relation "users" violates not-null constraint Target columns must exist, values must fit their types, and every NOT NULL column without a default must be set. RETURNING gives the statement a row type. Dynamic SQL Sometimes a query has optional parts. Fragments let you build it from pieces, and the finished statement is still checked as a whole: const recent = sql.fragment("u.id > $1", 10); const q = sql( `SELECT u.email FROM users u WHERE ${recent} AND u.email <> $1`, "a@b.c", ); Optional pieces are checked both with and without the fragment. There are also helpers for lists, identifiers, SET clauses and bulk VALUES. Most of the time, though, sql() is all you need. Tested against PostgreSQL itself A SQL parser in the type system sounds fragile, so I test it against PostgreSQL 18's own regression suite: about 50,000 statements. TypeSQL agrees with Postgres on all 49,999 statements it can check. CI fails if a single one disagrees. TypeSQL also supports SQLite, MySQL and MariaDB, each with its own parser and type rules. Editor support Hover any query to see its row type. This works in any TypeScript editor. The VS Code extension and the Neovim language server also add table and column completions and SQL highlighting inside sql() strings. Limits - TypeScript 7.1 is required. TypeSQL runs in the new native compiler. Older versions are too slow and aren't supported. - Only SQL known at compile time is checked. For SQL built at runtime, you give the row type yourself with sql.as<Row>(). - Very large statements can hit the compiler's limits, for example around 42 UNION ALL branches. The docs list the exact numbers. - No tagged templates (` sql… `). TypeScript doesn't expose their text to the type system. Try it - Try it online, with nothing to install: TypeScript Playground (it runs an older TypeScript, so treat it as a demo) - Docs: typesql.karsli.net - GitHub: ismailkarsli/TypeSQL I'd love feedback, especially queries where TypeSQL gets a type wrong or gives up. Issues are welcome on GitHub.
TypeSQL: type-safe raw SQL in TypeScript It checks your SQL strings with the TypeScript compiler and infers row and parameter types, even for complex queries. Typos and invalid queries become compile errors. Most of the time, sql() is all you need. ismailkarsli/TypeSQL
TypeSQL: type-safe raw SQL in TypeScript I made this library that checks raw SQL strings with the TypeScript compiler. It infers the types of rows and parameters, including pretty complex wueries. Typos and invalid queries become compile errors.
TypeSQL: type-safe raw SQL in TypeScript I made this library that checks raw SQL strings with the TypeScript compiler. It infers the types of rows and parameters, including pretty complex wueries. Typos and invalid queries become compile errors. It has some helpers but most of the time you will only need sql() function.
Show HN: TypeSQL – Postgres SQL parsed in TypeScript's type system, no codegen