Reylo

Import .sql files into your TypeScript code with full type inference

Reylo lets you import .sql files with full type safety. It's powered by TypeScript content mappers, introduced in TypeScript 7.1 (currently in beta), an official mechanism for integrating external file types directly into TypeScript compilation.

import postgres from "postgres";
import getAuthor from "./get_author.sql";

const db = postgres(process.env.DATABASE_URL!);

// hover over author to see its type
const author = await getAuthor.run(db, { id: 1 });

create type user_role as enum ('admin', 'member');  -- role: "admin" | "member"

create table users (
  id serial primary key,                            -- at most one row per id
  name text not null,                               -- name: string
  email text unique,                                -- email: string | null
  role user_role not null default 'member',
  tags text[] not null default '{}',                -- tags: string[]
  created_at timestamptz not null default now()     -- created_at: Date
);

create table posts (
  id serial primary key,
  user_id integer not null references users (id),
  title text not null,
  body text,
  published_at timestamptz
);

create table events (
  id serial primary key,
  kind text not null,
  payload jsonb not null
);

Setup

Set up Reylo in this project. Reylo lets TypeScript import .sql files, with types
inferred from the live database. Its docs are one page, https://reylo.dev: follow the
section linked at each step.

1. Check the requirements and install Reylo with TypeScript 7.1 beta or later:
   https://reylo.dev/#install
2. Add Reylo to tsconfig.json as a content mapper, and add a typecheck script that runs
   tsc --noEmit --runExternalCode: https://reylo.dev/#typescript
3. Point Reylo at the development database with DATABASE_URL: https://reylo.dev/#url
4. Make .sql imports load wherever the project runs code, with Node's --import
   (https://reylo.dev/#node) or the plugin for its runtime, bundler or framework
   (https://reylo.dev/#plugins).
5. Set up the editor the project uses: https://reylo.dev/#editors
6. Write a first query and run it with the project's own driver:
   https://reylo.dev/#usage and https://reylo.dev/#supported-drivers
7. Run the typecheck script. The query's types come from the database, and an error in
   a .sql file shows at its line and column.

Install Reylo and point it at your database, then add it to each tool that loads your code.

Install

Reylo needs:

• TypeScript 7.1 beta or later. The first release with content mappers.
• Node 24 or later, Bun or nub.
• A running dev database. Reylo's type inference engine needs to know your current database schema. It does this by introspecting your live database: Postgres, MySQL 8.0.22 or later, or a SQLite file.

npm install reylo
npm install -D typescript@beta

Then set DATABASE_URL to your database, in the environment or in .env: see url.

TypeScript

Add Reylo to tsconfig.json:

{
  "compilerOptions": { /* ... */ },
  "contentMappers": [ 
    { "package": "reylo", "extensions": [".sql"] } 
  ] 
}

Type-check with --runExternalCode, which lets tsc run content mappers. Without it, tsc reports TS18068 and finds no .sql module (TS2307).

{
  "scripts": {
    "typecheck": "tsc --noEmit --runExternalCode"
  }
}

A .sql file holds one select, insert, update, delete or values; merge on Postgres; replace on MySQL and SQLite; or table on Postgres and MySQL. The type check reports every other .sql file it reads, imported or not, such as a migration or a schema, so keep those out of tsconfig.json:

{
  "compilerOptions": { /* ... */ },
  "contentMappers": [ /* ... */ ],
  "exclude": ["db/**"] 
}

reylo generate skips such files, and only the type check reports them: the plugins and the preload build whatever you import.

npm install -D @types/node

{
  "compilerOptions": {
    "module": "nodenext",
    "strict": true,
    "types": ["node"]
  },
  "contentMappers": [
    { "package": "reylo", "extensions": [".sql"] }
  ]
}

{
  "type": "module"
}

Editors

The editor needs the TypeScript 7.1 language server, with content mappers on.

{ "recommendations": ["TypeScriptTeam.native-preview"] }

Node

Node loads Reylo with --import, which turns each .sql import into a module as it loads, and --env-file loads your .env for the app:

node --env-file=.env --import reylo/register main.ts

Plugins

Everywhere else, a plugin turns each .sql import into a module: Bun and nub load Reylo's preload from their config files, and a bundler builds each module at build time, so a bundled app needs no --import and no preload.

Usage

Write a statement in a .sql file, with a :name for each parameter, and import it. A select by primary key resolves to the row or null:

select id, name, email from users where id = :id

import postgres from "postgres";
import getUser from "./get_user.sql";

const db = postgres(process.env.DATABASE_URL!);

const user = await getUser.run(db, { id: 1 });
      
const user: {
    id: number;
    name: string;
    email: string | null;
} | null

An insert with returning resolves to the row it wrote:

insert into users (name, email) values (:name, :email)
returning id

import createUser from "./create_user.sql";

const created = await createUser.run(db, { name: "Ada", email: "ada@example.com" });
       
const created: {
    id: number;
}

An update by primary key with returning resolves to the row or null:

update users set name = :name where id = :id
returning id, name

import renameUser from "./rename_user.sql";

const renamed = await renameUser.run(db, { id: 1, name: "Ada Lovelace" });
       
const renamed: {
    id: number;
    name: string;
} | null

The module also exports the statement's types:

import getUser, { type Params, type Row } from "./get_user.sql";
                                   
type Row = {
    id: number;
    name: string;
    email: string | null;
}

run

run(client, params) runs the statement and resolves to its result, shaped by how many rows Reylo proves the statement can return:

At run time, a one query throws unless it gets exactly one row, and a maybeOne query throws when it gets more than one. The client is one from any of the supported drivers, or a transaction, savepoint or connection taken from it, and run always returns a promise, which rejects with the driver's own error. The parameters may also be an array, in the order of params:

import getUser from "./get_user.sql";

const user = await getUser.run(db, [1]);

runSync

On better-sqlite3, node:sqlite and bun:sqlite, runSync runs the statement synchronously and returns the result itself, so it works inside better-sqlite3's and bun:sqlite's db.transaction(fn), whose function cannot await:

import Database from "better-sqlite3";
import createUser from "./create_user.sql";
import renameUser from "./rename_user.sql";

const db = new Database("app.db");

db.transaction(() => {
  const { id } = createUser.runSync(db, { name: "Ada", email: null });
  renameUser.runSync(db, { id, name: "Ada Lovelace" });
})();

node:sqlite has no db.transaction: run the statements between db.exec("begin") and db.exec("commit").

with

with(client) binds the statement to a client once, such as a transaction, and returns a function that takes the parameters:

import getUser from "./get_user.sql";

const get = getUser.with(db);
const user = await get({ id: 1 });

sql

The statement as Reylo sends it to the database, typed as its string literal:

import getUser from "./get_user.sql";

getUser.sql;
       
sql: "select id, name, email from users where id = $1\n"
The statement as Reylo sends it to the database, as the module's sql export: :id is $1
on Postgres and ? on MySQL and SQLite, and each cast is the dialect's own.

raw

The file's contents, as written:

import getUser from "./get_user.sql";

getUser.raw;
       
raw: "select id, name, email from users where id = :id\n"
The .sql file's contents, exactly as written, as the module's raw export.

params

The parameters' names, in the order an array of values follows. A query also carries its row count, cardinality, and its dialect:

import getUser from "./get_user.sql";

getUser.params;       // ["id"]
getUser.cardinality;  // "maybeOne"

inputSchema

The JSON Schema (2020-12) of the parameters, as a plain object, for anything that takes JSON Schema: an OpenAPI document, a form, an agent's tool definition.

import getUser from "./get_user.sql";

getUser.inputSchema;
// { type: "object", properties: { id: { type: "integer", … } }, required: ["id"], … }

outputSchema

The JSON Schema of the result, as JSON.stringify writes it:

import getUser from "./get_user.sql";

getUser.outputSchema;
// { anyOf: [{ type: "object", properties: { id, name, email }, … }, { type: "null" }] }

A query is also a Standard JSON Schema, so a library that reads one gets both schemas from getUser["~standard"].jsonSchema.

Parameters

Each :name in a statement is a parameter, and run takes them as an object. Reylo types each one from where the statement uses it: the column it is compared with or written to, or the function it is passed to.

Casts

Most parameters need no cast. Where nothing types one, cast it, with ::type on every dialect or with the dialect's own cast(… as …). Here only Postgres needs one:

select id, name from users
where name like concat(:prefix::text, '%')

import usersNamed from "./users_named.sql";

const users = await usersNamed.run(db, { prefix: "Ad" });
                                          
prefix: string

On MySQL and SQLite, Reylo writes ::type as the dialect's own cast: :n::int is cast(? as signed) on MySQL and cast(? as integer) on SQLite. Their casts turn a value they cannot read into 0 or null, so Reylo checks the value against the type before the statement runs.

null

A parameter takes null where the statement lets it be null: a nullable column it is written to, as email in create_user.sql above, or a coalesce, as in Filter on an optional value.

Lists

JSON parameters

A JSON parameter carries a JSON Schema in its cast. The schema types it in TypeScript, and Reylo checks every value against it before the statement runs, so a list, a batch of rows or a nested object is one parameter:

with input as (
  select :names::jsonb('{ "type": "array", "items": { "type": "string" } }') as names
)
insert into users (name)
select jsonb_array_elements_text(input.names) from input

import addUsers from "./add_users.sql";

await addUsers.run(db, { names: ["Ada", "Grace"] });
                         
names: string[]

• The schema is JSON Schema 2020-12: string, number, integer, boolean, null, object and array, a key optional unless required lists it, and { "type": ["string", "null"] } for a value that may be null.
• Its checks, such as format, pattern, minimum, maxItems and enum, run before the statement does, and a value that fails makes run reject with every issue listed. enum and const also narrow the type, and oneOf, anyOf and allOf make a union.
• The schema stays in Reylo: the database receives the value as JSON text, $1::jsonb on Postgres, cast(? as json) on MySQL and ? on SQLite.

The examples use JSON parameters to insert many rows, insert related rows and record events of several kinds.

Positional parameters

A statement may use its database's own positional form instead of :name: $1 on Postgres, ? on MySQL, ?1 on SQLite. The key is then the number, as in { 1: 42 }.

Configuration

Reylo's settings go in .reylo.yaml, beside package.json, or in the file the REYLO_CONFIG variable names; tsconfig.json only names the content mapper.

Every key is optional: a project without the file behaves as if it held url: ${DATABASE_URL}. A key Reylo does not know is an error at its line and column, reported on every .sql import; Reylo ignores a top-level $schema and the api section, which is Reylo Framework's.

url

The database Reylo analyzes each statement against. The default is ${DATABASE_URL}, and the scheme names the dialect:

# or postgresql://; the type check uses your pg or postgres.js
DATABASE_URL=postgres://localhost:5432/app

A ${VAR} comes from the environment, then from the env files. One that nothing sets is an error, which every .sql import reports. Write the URL itself, or another variable:

url: ${APP_DATABASE_URL}

direct_url

A URL that reaches the database past a pooler. Describing a statement through a pooler in transaction mode, such as PgBouncer's, can fail, so when direct_url is set, Reylo's analysis (the type check, the editor and reylo generate) connects through it instead of url, and keys .reylo/ by it. It has no default, and your app's client is not affected.

url: ${DATABASE_URL}
direct_url: ${DIRECT_URL}

env

The .env files Reylo reads, relative to .reylo.yaml. The default is [.env.local, .env]: the first file that sets a variable wins, and the environment wins over every file.

env: [.env.development, .env]

Reylo reads them for its own analysis only. Your app loads .env itself: Node takes --env-file (Node), and Bun and nub read .env on their own.

strict

With strict: true, an untyped parameter is an error instead of unknown. The default is false.

strict: true

cli

The .sql files reylo generate analyzes, the command that fills .reylo/ ahead of a build without a database (In CI). The globs are relative to .reylo.yaml: include replaces the default, every .sql file, and exclude adds to what is always left out: node_modules, build output, directories whose names start with a dot, and what git ignores. The type check reads the files tsconfig.json includes instead.

cli:
  include: ["src/**/*.sql"]
  exclude: ["src/legacy/**"]

How it works

Reylo is a content mapper, the kind of plugin TypeScript 7.1 introduced for files that are not TypeScript. Your tsconfig.json names Reylo for .sql files, so whenever the type checker meets a .sql import, in tsc or in your editor, it hands Reylo the file and takes back the module's types. It is TypeScript's own mechanism, so the types are the same in CI and as you type.

To type a statement, Reylo asks your live database. It has the database prepare the statement, without running it (Postgres and MySQL on the server, SQLite in Reylo's own copy of SQLite, from the database file's schema), and reads the catalog. From the answer, Reylo learns:

• the parameter types
• the column types
• nullability, from NOT NULL and outer joins
• the row count, from keys, aggregates, limit 1 and returning

At run time, Node's --import, Bun's or nub's preload, or a bundler plugin turns the same file into a module that runs the statement through your driver.

Reylo keeps each answer in .reylo/, keyed by a fingerprint of the schema, so it asks the database again only for a schema it has not seen. After a migration, from any migration tool, the next type check sees the new schema: a statement that names a dropped or renamed column fails in its .sql file, and a caller that reads a column the statement no longer returns fails in TypeScript.

$ tsc --noEmit --runExternalCode
get_user.sql:1:12 - error reylo6: column "nmae" does not exist (42703)

1 select id, nmae, email
             ~~~~

Found 1 error in get_user.sql:1

import getUser from "./get_user.sql";

const user = await getUser.run(db, { id: "1" });
Type 'string' is not assignable to type 'number'.

$ reylo generate
postgres at localhost/reylo_docs (DATABASE_URL in .env)
  src/admin/find_by_email.sql  ✗
  src/admin/set_email.sql      ✓
  src/queries/count_users.sql  ✓
  src/queries/create_user.sql  ✓
  src/queries/get_user.sql     ✓
  src/queries/list_users.sql   ✓

src/admin/find_by_email.sql:1:12: column "nmae" does not exist (42703)
  1 | select id, nmae
    |            ^^^^

Skipped 1 file that is not one query (a migration?): db/schema.sql

✗ 1 of 6 queries failed (0.1 s)

Supported drivers

Reylo is bring-your-own-driver: pass run a client from any of these, or a transaction, savepoint or connection taken from one (see Run statements in a transaction). The type check reads the schema through pg or postgres.js on Postgres and through mysql2 on MySQL, so an app on another driver adds one of those as a dev dependency. Every driver gives Reylo's queries the same values, whatever it is set up to return for yours (Data types).

Postgres

import pg from "pg";
import getUser from "./get_user.sql";

const db = new pg.Pool({ connectionString: process.env.DATABASE_URL });

const user = await getUser.run(db, { id: 1 });

Each takes its transactions and connections too: pg's Pool, Client and PoolClient; postgres.js's and Bun.sql's begin, savepoint and reserve; PGlite's transaction; Neon's Pool and Client; and pg-promise's task and tx.

MySQL

import mysql from "mysql2/promise";
import getUser from "./get_user.sql";

const db = mysql.createPool(process.env.DATABASE_URL!);

const user = await getUser.run(db, { id: 1 });

Each takes its transactions and connections too: mysql2's and mariadb's promise or callback API, PlanetScale's transaction, and Bun.sql's begin, savepoint and reserve. Use PlanetScale's driver 1.x: 2.0 binds no parameters.

SQLite

import Database from "better-sqlite3";
import getUser from "./get_user.sql";

const db = new Database("app.db");

const user = await getUser.run(db, { id: 1 });

Each takes its transactions too: libSQL's transaction(), Bun.sql's begin and savepoint, and D1's withSession() sessions. better-sqlite3, node:sqlite and bun:sqlite take runSync as well (Run statements in a transaction).

Data types

Reylo gives every value one type, the same on every driver, whatever the driver is set up to return: it decodes each value of its own queries itself, on your client, and leaves your other queries as your driver decodes them. A 64-bit integer and a decimal are their exact text, an instant is a Date, a date or a time without a zone is the text the database wrote, JSON is parsed and binary data is a Uint8Array.

Examples

Each example shows the statement in Postgres, MySQL and SQLite, against the schema at the top of the page; pick a dialect once and every example follows it. The examples call the client db.

List rows

A statement that can match more than one row resolves to an array.

select id, name, role, tags, created_at from users
where role = :role
order by created_at desc
limit :limit

import listUsers from "./list_users.sql";

const users = await listUsers.run(db, { role: "member", limit: 20 });
      
const users: {
    id: number;
    name: string;
    role: "admin" | "member";
    tags: string[];
    created_at: Date;
}[]

Count rows

An aggregate without group by is exactly one row.

-- count(*) is a bigint, a string; count(*)::int is a number
select count(*) as total from users where role = :role

import countUsers from "./count_users.sql";

const { total } = await countUsers.run(db, { role: "admin" });
        
const total: string

Filter on an optional value

coalesce lets a parameter be null: here null matches every role. A parameter takes null only where the statement allows it, such as a coalesce or a nullable column it is written to.

select id, name from users
where role = coalesce(:role, role)

import usersByRole from "./users_by_role.sql";

const users = await usersByRole.run(db, { role: "admin" });
                                          
role: "admin" | "member" | null

Sort by a chosen column

A parameter is a value, never a name: order by :column sorts by a constant, and select * from :table is a syntax error. Choose the column with case:

select id, name, created_at from users
order by
  case :sort when 'name' then name end,
  created_at desc

import sortedUsers from "./sorted_users.sql";

const users = await sortedUsers.run(db, { sort: "name" });
      
const users: {
    id: number;
    name: string;
    created_at: Date;
}[]

Search several columns

Each parameter appears once in a statement, so that one place types it, and a second use is an error. To use a value twice, select it once in a with:

with input as (
  select concat('%', :q::text, '%') as pattern
)
select id, name from users, input
where name like input.pattern or email like input.pattern

import searchUsers from "./search_users.sql";

const users = await searchUsers.run(db, { q: "ada" });
      
const users: {
    id: number;
    name: string;
}[]

Join tables

A column of the table a left join adds can be null. coalesce gives it a default.

select u.name, p.title, coalesce(p.title, '') as label
from users u
left join posts p on p.user_id = u.id

import userPosts from "./user_posts.sql";

const rows = await userPosts.run(db);
      
const rows: {
    name: string;
    title: string | null;
    label: string;
}[]

Page through rows

Pass the sort key of the last row you have, here created_at and id, to get the rows after it.

select id, name, created_at from users
where (created_at, id) < (:created_at, :id)
order by created_at desc, id desc
limit 20

import nextPage from "./next_page.sql";

// the first page: a cursor after every row
const page = await nextPage.run(db, {
  created_at: "3000-01-01 00:00:00+00:00",
  id: 0,
});
const next = await nextPage.run(db, page.at(-1)!);
      
const next: {
    id: number;
    name: string;
    created_at: Date;
}[]

Filter by a list

Pass the list as one parameter.

select id, name from users where id = any(:ids)

import usersByIds from "./users_by_ids.sql";

const users = await usersByIds.run(db, { ids: [1, 2, 3] });
                                        
ids: number[]

Keep a list's order

Number the list's elements, and sort by the number.

-- with ordinality numbers the elements
select u.id, u.name
from unnest(:ids::int[]) with ordinality as i(id, position)
join users u on u.id = i.id
order by i.position

import usersInOrder from "./users_in_order.sql";

const users = await usersInOrder.run(db, { ids: [3, 1, 2] });
      
const users: {
    id: number;
    name: string;
}[]

Nest related rows

Build the related rows as JSON in the statement. Reylo types the JSON from the arguments that build it.

select u.id, u.name,
  coalesce((
    select jsonb_agg(jsonb_build_object(
      'id', p.id,
      'title', p.title,
      'at', p.published_at
    ))
    from posts p where p.user_id = u.id
  ), '[]') as posts
from users u
where u.id = :id

import userWithPosts from "./user_with_posts.sql";

const user = await userWithPosts.run(db, { id: 1 });
      
const user: {
    id: number;
    name: string;
    posts: ({
        id: number;
        title: string;
        at: string | null;
    })[];
} | null

Insert many rows

Pass the rows as one JSON parameter: the schema in its cast types them, and Reylo checks every row before the statement runs.

with input as (
  select :users::jsonb('{
    "type": "array",
    "maxItems": 1000,
    "items": {
      "type": "object",
      "properties": {
        "name": { "type": "string" },
        "email": { "type": ["string", "null"], "format": "email" }
      },
      "required": ["name"]
    }
  }') as users
)
insert into users (name, email)
select u.name, u.email
from input, jsonb_to_recordset(input.users) as u(name text, email text)
returning id

import createUsers, { type Params } from "./create_users.sql";
                            
type Params = {
    users: {
        name: string;
        email?: string | null;
    }[];
}

const created = await createUsers.run(db, {
  users: [{ name: "Ada", email: "ada@example.com" }, { name: "Grace" }],
});

Insert related rows

Users with their posts, in one parameter. The posts find their user by email, a unique key.

with input as (
  select :users::jsonb('{
    "type": "array",
    "items": {
      "type": "object",
      "properties": {
        "name": { "type": "string" },
        "email": { "type": "string", "format": "email" },
        "posts": {
          "type": "array",
          "items": {
            "type": "object",
            "properties": { "title": { "type": "string" } },
            "required": ["title"]
          }
        }
      },
      "required": ["name", "email", "posts"]
    }
  }') as users
),
new_users as (
  insert into users (name, email)
  select u.name, u.email
  from input, jsonb_to_recordset(input.users) as u(name text, email text)
  returning id, email
)
insert into posts (user_id, title)
select n.id, p.title
from input
cross join jsonb_to_recordset(input.users) as u(email text, posts jsonb)
join new_users n on n.email = u.email
cross join jsonb_to_recordset(u.posts) as p(title text)

import createUsersWithPosts, { type Params } from "./create_users_with_posts.sql";
                                     
type Params = {
    users: {
        name: string;
        email: string;
        posts: {
            title: string;
        }[];
    }[];
}

await createUsersWithPosts.run(db, {
  users: [
    { name: "Ada", email: "ada@example.com", posts: [{ title: "Notes" }] },
  ],
});

Run statements in a transaction

A transaction, a savepoint or a connection taken from a client is a client too:

import postgres from "postgres";
import getUser from "./get_user.sql";
import renameUser from "./rename_user.sql";

const db = postgres(process.env.DATABASE_URL!);

await db.begin(async (tx) => {
  const user = await getUser.run(tx, { id: 1 });
  await tx.savepoint((sp) =>
    renameUser.run(sp, { id: user!.id, name: "Grace" }),
  );
});

Insert or update

When the email already exists, the insert updates that row instead. With returning, it resolves to the id of the row it wrote.

insert into users (name, email) values (:name, :email)
on conflict (email) do update set name = excluded.name
returning id

import upsertUser from "./upsert_user.sql";

const ada = { name: "Ada Lovelace", email: "ada@example.com" };
const result = await upsertUser.run(db, ada);
       
const result: {
    id: number;
}

Delete rows

A write without returning resolves to void.

delete from users where id = :id

import deleteUser from "./delete_user.sql";

const result = await deleteUser.run(db, { id: 1 });
       
const result: void

Record events of several kinds

oneOf types a JSON parameter as a union. Here a const key tells the members apart:

with input as (
  select :events::jsonb('{
    "type": "array",
    "items": {
      "oneOf": [
        {
          "type": "object",
          "properties": {
            "kind": { "type": "string", "const": "click" },
            "x": { "type": "integer" },
            "y": { "type": "integer" }
          },
          "required": ["kind", "x", "y"]
        },
        {
          "type": "object",
          "properties": {
            "kind": { "type": "string", "const": "key" },
            "code": { "type": "string" }
          },
          "required": ["kind", "code"]
        }
      ]
    }
  }') as events
)
insert into events (kind, payload)
select e.value ->> 'kind', e.value
from input, jsonb_array_elements(input.events) as e(value)

import recordEvents, { type Params } from "./record_events.sql";
                             
type Params = {
    events: ({
        kind: "click";
        x: number;
        y: number;
    } | {
        kind: "key";
        code: string;
    })[];
}

await recordEvents.run(db, {
  events: [
    { kind: "click", x: 10, y: 20 },
    { kind: "key", code: "Enter" },
  ],
});