README.md

sqlt

Generate fully typed TypeScript from plain SQL files. Write your queries in .sql, and sqlt reads your PostgreSQL schema to produce type-safe query functions with zero runtime overhead.

How it works

1. You write plain SQL with named parameters (:email, :id)
2. sqlt parses the SQL, connects to your database, and reads information_schema.columns
3. It generates a .ts file per query with typed params, typed results, and a ready-to-use function

Input — queries/getUser.sql

SELECT id, email FROM users WHERE email = :email

Output — generated/getUser.ts

import { Pool, QueryResult } from "pg";

export interface GetUserParams {
  email: string;
}

export interface GetUserResult {
  id: string;
  email: string;
}

export async function getUser(pool: Pool, params: GetUserParams): Promise<GetUserResult[]> {
  const sql = `SELECT id, email FROM users WHERE email = $1`;

  const result: QueryResult<GetUserResult> = await pool.query(sql, [params.email]);
  return result.rows;
}

Features

• SELECT and INSERT queries
• JOIN support — columns are resolved against the correct table using aliases
• Named parameters — :name syntax, mapped to $N placeholders
• Nullable types — columns marked is_nullable become T | null in TypeScript
• PostgreSQL type mapping — integer -> number, text -> string, boolean -> boolean, etc.

JOINs

sqlt resolves qualified column references (u.id, p.bio) to the correct table's schema:

SELECT u.id, u.email, p.bio, p.age
FROM users u
JOIN profiles p ON u.id = p.user_id
WHERE u.email = :email

This generates a result type with fields from both tables, each typed according to its own table's schema.

Installation

Requires Rust (1.85+) and a running PostgreSQL instance.

cargo install --path .

Or build from source:

cargo build --release

Usage

sqlt --input ./queries --output ./generated --database-url postgres://localhost/mydb

Flag	Description
-i, --input	Directory containing .sql files
-o, --output	Output directory for generated .ts files
-d, --database-url	PostgreSQL connection string

Running the example

The examples/basic directory contains a working example with users, profiles, SELECT, INSERT, and JOIN queries.

1. Set up the database

createdb sqlt_example
psql sqlt_example < examples/basic/setup.sql

2. Generate TypeScript

export DATABASE_URL=postgres://localhost/sqlt_example

cargo run -- -i examples/basic/queries -o examples/basic/generated -d $DATABASE_URL

3. Run the example app

cd examples/basic
npm install
npm run dev

Supported SQL

SELECT

-- Simple
SELECT id, email FROM users WHERE email = :email

-- With JOIN
SELECT u.id, u.email, p.bio
FROM users u
JOIN profiles p ON u.id = p.user_id
WHERE u.email = :email

INSERT

INSERT INTO users (id, email) VALUES (:id, :email)

Type mapping

PostgreSQL	TypeScript
integer, bigint, smallint, serial, numeric, real, double precision	number
text, character varying, character, uuid	string
boolean	boolean
timestamp, timestamptz, date, time	string
json, jsonb	unknown