# Reylo

> Import .sql files into TypeScript with full type inference. A TypeScript 7.1 content mapper types each query from your live Postgres, MySQL or SQLite database.

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

If you have a file like this:

`get_author.sql`:

```sql
select u.id, u.name, count(p.id)::int as post_count
from users u
left join posts p on p.user_id = u.id
where u.id = :id::int
group by u.id
```

You can import it directly from a TypeScript file like this:

`main.ts`:

```ts
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 });
```

**View the schema**

**Postgres**:

```sql
create extension if not exists pg_jsonschema;

create type user_role as enum ('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',  -- role: "admin" | "member"
  tags text[] not null default '{}',         -- tags: (string | null)[]
  created_at timestamptz                     -- created_at: string
    not null default now()
);

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
    check (kind in ('click', 'key')),        -- kind: "click" | "key"
  payload jsonb not null                     -- payload: what the schema admits
    check (jsonb_matches_schema('{
      "type": "object",
      "properties": {
        "x": { "type": "integer" },
        "y": { "type": "integer" },
        "code": { "type": "string" }
      }
    }', payload))
);
```

**MySQL**:

```sql
create table users (
  id int auto_increment primary key,          -- at most one row per id
  name text not null,                         -- name: string
  email varchar(255) unique,                  -- email: string | null
  role enum('admin', 'member')                -- role: string
    not null default 'member',
  tags json not null default (json_array()),  -- tags: unknown
  created_at datetime                         -- created_at: string, UTC
    not null default (utc_timestamp())
);

create table posts (
  id int auto_increment primary key,
  user_id int not null,
  title text not null,
  body text,
  published_at datetime,
  foreign key (user_id) references users (id)
);

create table events (
  id int auto_increment primary key,
  kind text not null,
  payload json not null                       -- payload: what the schema admits
    check (json_schema_valid('{
      "type": "object",
      "properties": {
        "x": { "type": "integer" },
        "y": { "type": "integer" },
        "code": { "type": "string" }
      }
    }', payload))
);
```

**SQLite**:

```sql
create table users (
  id integer primary key,               -- at most one row per id
  name text not null,                   -- name: string
  email text unique,                    -- email: string | null
  role text not null default 'member'   -- role: string
    check (role in ('admin', 'member')),
  tags text not null default '[]',      -- tags: string, JSON text
  created_at text                       -- created_at: string, UTC
    not null default (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))
) strict;

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

create table events (
  id integer primary key,
  kind text not null,
  payload text not null
) strict;
```

> **Content mappers.** Reylo is powered by TypeScript [content mappers](https://github.com/microsoft/typescript-go/pull/4712), introduced in TypeScript 7.1 (currently [in beta](https://devblogs.microsoft.com/typescript/announcing-typescript-7-1-beta/)), which let you dynamically define static types for external file types and integrate them directly into TypeScript compilation.

Reylo is the perfect option for the agent era:

- 💪 **The full power of SQL** — any statement, function, operator or syntax your database supports, in its own dialect.
- 🌵 **DRY** — the types come from the SQL itself: no hand-written row types, no schema file to keep in sync.
- 🎯 **Checked against your database** — a misspelled column, a wrong parameter or a dropped table is a type error, in `tsc` and in your editor.
- 🤖 **Agent-friendly** — each fact lives in one place, so an agent cannot leave a stale type behind, and the type check finds every caller it missed.
- 🧩 **Postgres, MySQL and SQLite** — bring your own driver: `pg`, postgres.js, `mysql2`, `better-sqlite3`, `Bun.sql`, Cloudflare D1 and [more](https://reylo.dev/#run).
- ⚡ **No codegen** — the type checker asks Reylo directly: no generated files, no watcher.

## Setup

To install Reylo, you'll need:

- **TypeScript 7.1 (currently in beta).** The first release with content mappers.
- **Node 24 or later.**
- **A running dev database.** Reylo reads your current schema from it ([Database](https://reylo.dev/#database) points Reylo at it):
  - **Postgres** 14 to 18, including Neon and PGlite
  - **MySQL** 8.0.22 or later, including PlanetScale
  - **SQLite**, including Cloudflare D1 and Durable Objects, Turso and libSQL

```text
Set up Reylo in this project. Reylo lets TypeScript import .sql files, with types inferred from the live database. Its docs are https://reylo.dev, with a reference page for each database (https://reylo.dev/postgres, https://reylo.dev/mysql, https://reylo.dev/sqlite), all served as Markdown at https://reylo.dev/llms.txt. Each step below links its section.

Each setup has a complete, working example project to copy, whose README's "How Reylo is wired in" lists every line Reylo needs. Install from npm, since the examples link local builds: https://github.com/colinhacks/reylo/tree/main/examples

1. Check the requirements and install Reylo, the engine of the project's database (@reylosql/postgres, @reylosql/mysql or @reylosql/sqlite) and TypeScript 7.1 (currently in beta): https://reylo.dev/#setup
2. Add Reylo to tsconfig.json as a content mapper for the extensions .sql, .reylo.yaml and .reylo.yml, and add a typecheck script that runs tsc --noEmit --runExternalCode (after next typegen on Next.js): https://reylo.dev/#typescript
3. Make .sql imports load wherever the project runs code, with Node's --import or the plugin for its runtime, bundler or framework: https://reylo.dev/#plugins
   The example for that setup shows it end to end (node, nub, bun, vite, nextjs, esbuild, webpack, rspack, jest or cloudflare-workers): https://github.com/colinhacks/reylo/tree/main/examples/<name>
   If the project runs on anything else, Reylo has no plugin for it yet: say so, and do not write one.
4. Point Reylo at the development database with DATABASE_URL, or with direct_url in .reylo.yaml: https://reylo.dev/#database
5. Set up the editor the project uses, or VS Code's settings when nothing in the project names an editor: 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/#run
   For more queries, from nested JSON to bulk inserts, see https://reylo.dev/#inference
7. Run the typecheck script. The query's types come from the database; an error in a .sql file shows at its line and column.
8. Install Reylo's agent skill, which gives later sessions its rules for writing queries: npx skills add colinhacks/reylo --skill using-reylo
```

Then set Reylo up in six steps:

### Install

Install Reylo, the engine for your database and TypeScript 7.1:

```sh
npm install reylo
npm install -D @reylosql/postgres  # or mysql, sqlite
npm install -D typescript@beta
```

### TypeScript

Add Reylo to `tsconfig.json`, for `.sql` files and for its project file, `.reylo.yaml` or `.reylo.yml`:

`tsconfig.json`:

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

Then type-check with `--runExternalCode`, which lets `tsc` run content mappers:

`package.json`:

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

### Runtime / framework

Install the plugin for your runtime or framework of choice:

**Node**

Load Reylo's preload with `--import`:

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

**Bun**

Add the preload to `bunfig.toml`, and under `[test]` for `bun test`:

`bunfig.toml`:

```toml
preload = ["reylo/register"]

[test]
preload = ["reylo/register"]
```

For Bun's bundler, use the esbuild plugin:

`build.ts`:

```ts
import reylo from "reylo/esbuild";

await Bun.build({
  entrypoints: ["main.ts"],
  target: "bun",
  plugins: [reylo()],
  outdir: "dist",
});
```

**Nub**

Add the preload to `nub.jsonc`:

`nub.jsonc`:

```jsonc
{
  "preload": ["reylo/register"]
}
```

**Vite**

Add Reylo's plugin to your Vite config:

`vite.config.ts`:

```ts
import { defineConfig } from "vite";
import reylo from "reylo/vite";

export default defineConfig({
  plugins: [reylo()],
});
```

Import `.sql` files from server code only, such as a `.server/` directory: Vite bundles a query wherever it is imported, the browser included. For Vitest, this plugin is all you need.

**Next.js**

Wrap your Next.js config with `withReylo`:

`next.config.ts`:

```ts
import type { NextConfig } from "next";
import { withReylo } from "reylo/next";

const nextConfig: NextConfig = {
  // ...
};

export default withReylo(nextConfig);
```

Your server code can now import `.sql` files, with Turbopack or webpack, and `next build` fails on a type error in a query or its caller.

To type-check without building, run `next typegen` first, which writes the route types:

`package.json`:

```json
{
  "scripts": {
    "typecheck": "next typegen && tsc --noEmit --runExternalCode"
  }
}
```

**How the build checks the types**

Next.js type-checks with your project's `tsc` but without `--runExternalCode`, so `withReylo` turns that check off and runs `tsc --noEmit --runExternalCode` itself, after the compile and before the prerender. Next.js still prints "Skipping validation of types" for its own check.

**esbuild**

Add Reylo's plugin to your esbuild build:

`build.mjs`:

```js
import * as esbuild from "esbuild";
import reylo from "reylo/esbuild";

await esbuild.build({
  entryPoints: ["main.ts"],
  bundle: true,
  platform: "node",
  format: "esm",
  plugins: [reylo()],
  outfile: "dist/main.mjs",
});
```

**webpack**

Load `.sql` files with Reylo's loader in your webpack config:

`webpack.config.mjs`:

```js
export default {
  // ...
  module: {
    rules: [{ test: /\.sql$/, use: "reylo/webpack" }],
  },
};
```

Compile TypeScript with `swc-loader` or `babel-loader`, since TypeScript 7 has no JavaScript API for `ts-loader`.

**Rspack**

Load `.sql` files with Reylo's webpack loader in your Rspack config:

`rspack.config.mjs`:

```js
export default {
  // ...
  module: {
    rules: [{ test: /\.sql$/, use: "reylo/webpack" }],
  },
};
```

**Cloudflare**

Add Reylo's Vite plugin beside Cloudflare's:

`vite.config.ts`:

```ts
import { cloudflare } from "@cloudflare/vite-plugin";
import { defineConfig } from "vite";
import reylo from "reylo/vite";

export default defineConfig({
  plugins: [reylo(), cloudflare()],
});
```

Pass the D1 binding as the client, as in `getUser.run(env.DB, { id: 1 })`; you don't need `nodejs_compat`. For the type check, set [`direct_url`](https://reylo.dev/#direct_url) in `.reylo.yaml` to the local D1 file that `wrangler d1 migrations apply --local` creates. Don't set it in `.env`, which Wrangler loads into the Worker as secrets.

**Jest**

Add Reylo's transformer to your Jest config:

`jest.config.js`:

```js
export default {
  transform: { "\\.sql$": "reylo/jest" },
  extensionsToTreatAsEsm: [".sql"],
};
```

```sh
# Jest runs ES modules only behind this Node flag
NODE_OPTIONS=--experimental-vm-modules npx jest
```

For a complete, working project, copy the example for your setup. Its README, under "How Reylo is wired in", names every line Reylo needs:

- [Node](https://github.com/colinhacks/reylo/tree/main/examples/node)
- [Nub](https://github.com/colinhacks/reylo/tree/main/examples/nub)
- [Bun](https://github.com/colinhacks/reylo/tree/main/examples/bun)
- [Vite](https://github.com/colinhacks/reylo/tree/main/examples/vite)
- [Next.js](https://github.com/colinhacks/reylo/tree/main/examples/nextjs)
- [esbuild](https://github.com/colinhacks/reylo/tree/main/examples/esbuild)
- [webpack](https://github.com/colinhacks/reylo/tree/main/examples/webpack)
- [Rspack](https://github.com/colinhacks/reylo/tree/main/examples/rspack)
- [Jest](https://github.com/colinhacks/reylo/tree/main/examples/jest)
- [Cloudflare Workers](https://github.com/colinhacks/reylo/tree/main/examples/cloudflare-workers)

### Database

**Reylo requires a connection to a live dev database.** It retrieves the types of each compiled query, its parameters and its columns, from the database itself. Reylo finds your dev database through the `DATABASE_URL` environment variable, and the URL's scheme tells it which database you use:

**Postgres**, `.env`:

```sh
# or postgresql://
DATABASE_URL=postgres://localhost:5432/app
```

**MySQL**, `.env`:

```sh
# or mariadb://
DATABASE_URL=mysql://root@localhost:3306/app
```

**SQLite**, `.env`:

```sh
# or sqlite:; the path starts at package.json
DATABASE_URL=file:./app.db
```

To override this, create a `.reylo.yaml` file in your project root and set the `direct_url` field:

`.reylo.yaml`:

```yaml
direct_url: postgres://localhost:5432/app
```

You can also reference other environment variables via string interpolation:

`.reylo.yaml`:

```yaml
direct_url: ${APP_DATABASE_URL}
```

Or specify a set of `.env` files to load:

`.reylo.yaml`:

```yaml
# the default is [.env.local, .env]
env: [.env.development, .env]
```

### Editor

Set up your editor to use the TypeScript 7.1 language server, with content mappers on. Then a hover over a query shows its types, its SQL and the comment at the top of its `.sql` file, an error in a `.sql` file is underlined where it is, and go-to-definition on a query opens its `.sql` file.

**VS Code**

1. Install the **TypeScript 7** extension (`TypeScriptTeam.native-preview`).
2. Point it at the project's TypeScript: the extension's own copy is TypeScript 7.0, which has no content mappers.

   ```json title=".vscode/settings.json"
   {
     "js/ts.experimental.useTsgo": true,
     "js/ts.tsdk.path": "./node_modules/typescript"
   }
   ```

3. Trust the workspace, and click **Allow** when the extension asks to use the workspace's TypeScript. Content mappers run in a trusted workspace only. Missed it? It waits under the bell in the status bar, or **Developer: Reload Window** asks again.

Keep `DATABASE_URL` in `.env`: the editor doesn't see your shell's environment.

**Cursor**

1. Install the **TypeScript 7** extension (`TypeScriptTeam.native-preview`).
2. Point it at the project's TypeScript: the extension's own copy is TypeScript 7.0, which has no content mappers.

   ```json title=".vscode/settings.json"
   {
     "js/ts.experimental.useTsgo": true,
     "js/ts.tsdk.path": "./node_modules/typescript"
   }
   ```

3. Trust the workspace, and click **Allow** when the extension asks to use the workspace's TypeScript. Content mappers run in a trusted workspace only. Missed it? It waits under the bell in the status bar, or **Developer: Reload Window** asks again.

Keep `DATABASE_URL` in `.env`: the editor doesn't see your shell's environment.

**Zed**

1. Install the **TypeScript Language Server** extension by Zed Industries (search for "tsgo").
2. Point it at the project's TypeScript, with content mappers on:

   ```jsonc title=".zed/settings.json"
   {
     "languages": {
       "TypeScript": {
         "language_servers": [
           "typescript-ls",
           "!vtsls",
           "!typescript-language-server",
           "..."
         ]
       },
       "TSX": {
         "language_servers": [
           "typescript-ls",
           "!vtsls",
           "!typescript-language-server",
           "..."
         ]
       }
     },
     "lsp": {
       "typescript-ls": {
         // the extension's own download is TypeScript 7.0, which has no content mappers
         "binary": {
           "path": "node_modules/typescript/bin/tsc",
           "arguments": ["--lsp", "--stdio"]
         },
         "initialization_options": { "runExternalCode": true }
       }
     }
   }
   ```

3. Trust the folder. In restricted mode, Zed starts no language server.

Zed doesn't send `.sql` files to the language server, so an error inside one shows only in the type check.

### Skill

Install Reylo's agent skill, which gives your coding agent Reylo's rules for writing and running queries in every session:

```sh
npx skills add colinhacks/reylo --skill using-reylo
```

## Usage

Write a query in a `.sql` file, import it, and run it with your database driver. The query is your database's own SQL: any function, operator or syntax it supports works. Reylo uses your live dev database to compile the query and retrieve its types.

`main.ts`:

```ts
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 });
```

`get_user.sql`:

```sql
select id, name, email from users where id = :id::int
```

Pass your database client as the first argument of `.run()`: a pool, a client, or a connection or transaction taken from one, from any of the [supported drivers](https://reylo.dev/#run).

### `.sql`

To get the compiled query, as Reylo sends it to your database:

```ts
import getUser from "./get_user.sql";

getUser.sql; // "select id, name, email from users where id = $1::int"
```

To get the file's contents as written, before Reylo compiles its [parameters](https://reylo.dev/#naming), use `.raw`. Its path, relative to your project, is `.source`, a name for your [logs and traces](https://reylo.dev/#tracing); Reylo's own errors about the query start with it.

### `.run()`

To run an imported Reylo query, pass a database connection/pool into the `.run()` method along with any parameters:

```ts
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
```

Reylo has no driver of its own: it runs each query on your app's database client, from any of these drivers.

#### Postgres

**pg**:

```ts
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 });
```

`postgres.js`:

```ts
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 });
```

`Bun.sql`:

```ts
import { SQL } from "bun";
import getUser from "./get_user.sql";

const db = new SQL(process.env.DATABASE_URL);

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

**PGlite**:

```ts
import { PGlite } from "@electric-sql/pglite";
import getUser from "./get_user.sql";

const db = new PGlite("./pgdata");

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

**Neon**:

```ts
import { neon } from "@neondatabase/serverless";
import getUser from "./get_user.sql";

const db = neon(process.env.DATABASE_URL);

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

**pg-promise**:

```ts
import pgPromise from "pg-promise";
import getUser from "./get_user.sql";

const pgp = pgPromise();
const db = pgp(process.env.DATABASE_URL);

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

**Slonik**:

```ts
import { createPool } from "slonik";
import getUser from "./get_user.sql";

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

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

**@vercel/postgres**:

```ts
// deprecated on npm; type-checked against Reylo, not run in its tests
import { sql } from "@vercel/postgres";
import getUser from "./get_user.sql";

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

**Drizzle**:

```ts
import { drizzle } from "drizzle-orm/node-postgres";
import getUser from "./get_user.sql";

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

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

Transactions are also supported.

**pg**:

```ts
const client = await db.connect();
try {
  await client.query("begin");
  await getUser.run(client, { id: 1 });
  await client.query("commit");
} catch (error) {
  await client.query("rollback");
  throw error;
} finally {
  client.release();
}
```

`postgres.js`:

```ts
await db.begin((tx) => getUser.run(tx, { id: 1 }));
```

`Bun.sql`:

```ts
await db.begin((tx) => getUser.run(tx, { id: 1 }));
```

**PGlite**:

```ts
await db.transaction((tx) => getUser.run(tx, { id: 1 }));
```

**Neon**:

```ts
import { Pool } from "@neondatabase/serverless";

// a transaction takes a Pool, over a WebSocket
const pool = new Pool({ connectionString: process.env.DATABASE_URL });
const client = await pool.connect();
try {
  await client.query("begin");
  await getUser.run(client, { id: 1 });
  await client.query("commit");
} catch (error) {
  await client.query("rollback");
  throw error;
} finally {
  client.release();
}
```

**pg-promise**:

```ts
await db.tx((t) => getUser.run(t, { id: 1 }));
```

**Slonik**:

```ts
await db.transaction((tx) => getUser.run(tx, { id: 1 }));
```

**@vercel/postgres**:

```ts
const client = await sql.connect();
try {
  await client.query("begin");
  await getUser.run(client, { id: 1 });
  await client.query("commit");
} catch (error) {
  await client.query("rollback");
  throw error;
} finally {
  client.release();
}
```

**Drizzle**:

```ts
await db.transaction((tx) => getUser.run(tx, { id: 1 }));
```

#### MySQL

**mysql2**:

```ts
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 });
```

**mariadb**:

```ts
// mariadb takes only a mariadb:// URL, which Reylo reads as MySQL
import mariadb from "mariadb";
import getUser from "./get_user.sql";

const db = mariadb.createPool(process.env.DATABASE_URL);

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

**PlanetScale**:

```ts
import { connect } from "@planetscale/database";
import getUser from "./get_user.sql";

const db = connect({ url: process.env.DATABASE_URL });

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

`Bun.sql`:

```ts
import { SQL } from "bun";
import getUser from "./get_user.sql";

const db = new SQL(process.env.DATABASE_URL);

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

**Drizzle**:

```ts
import { drizzle } from "drizzle-orm/mysql2";
import getUser from "./get_user.sql";

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

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

Transactions are also supported.

**mysql2**:

```ts
const conn = await db.getConnection();
try {
  await conn.beginTransaction();
  await getUser.run(conn, { id: 1 });
  await conn.commit();
} catch (error) {
  await conn.rollback();
  throw error;
} finally {
  conn.release();
}
```

**mariadb**:

```ts
const conn = await db.getConnection();
try {
  await conn.beginTransaction();
  await getUser.run(conn, { id: 1 });
  await conn.commit();
} catch (error) {
  await conn.rollback();
  throw error;
} finally {
  conn.release();
}
```

**PlanetScale**:

```ts
await db.transaction((tx) => getUser.run(tx, { id: 1 }));
```

`Bun.sql`:

```ts
await db.begin((tx) => getUser.run(tx, { id: 1 }));
```

**Drizzle**:

```ts
await db.transaction((tx) => getUser.run(tx, { id: 1 }));
```

#### SQLite

**better-sqlite3**:

```ts
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 });
```

**node:sqlite**:

```ts
import { DatabaseSync } from "node:sqlite";
import getUser from "./get_user.sql";

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

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

**bun:sqlite**:

```ts
import { Database } from "bun:sqlite";
import getUser from "./get_user.sql";

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

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

`Bun.sql`:

```ts
import { SQL } from "bun";
import getUser from "./get_user.sql";

const db = new SQL({ adapter: "sqlite", filename: "app.db" });

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

**libSQL**:

```ts
import { createClient } from "@libsql/client";
import getUser from "./get_user.sql";

const db = createClient({ url: "file:app.db" });

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

**Turso**:

```ts
import { connect } from "@tursodatabase/database";
import getUser from "./get_user.sql";

const db = await connect("app.db");

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

**Cloudflare D1**:

```ts
import type { D1Database } from "@cloudflare/workers-types";
import getUser from "./get_user.sql";

export default {
  async fetch(request: Request, env: { DB: D1Database }) {
    const user = await getUser.run(env.DB, { id: 1 });
    return Response.json(user);
  },
};
```

**Durable Objects**:

```ts
import { DurableObject } from "cloudflare:workers";
import getUser from "./get_user.sql";

export class Board extends DurableObject {
  user(id: number) {
    return getUser.runSync(this.ctx.storage.sql, { id });
  }
}
```

**sqlite3**:

```ts
import sqlite3 from "sqlite3";
import getUser from "./get_user.sql";

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

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

**sqlite**:

```ts
import { open } from "sqlite";
import sqlite3 from "sqlite3";
import getUser from "./get_user.sql";

const db = await open({ filename: "app.db", driver: sqlite3.Database });

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

**Drizzle**:

```ts
import { drizzle } from "drizzle-orm/better-sqlite3";
import getUser from "./get_user.sql";

const db = drizzle("app.db");

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

Transactions are also supported.

**better-sqlite3**:

```ts
// the function must not await, so it runs .runSync()
db.transaction(() => getUser.runSync(db, { id: 1 }))();
```

**node:sqlite**:

```ts
db.exec("begin");
try {
  getUser.runSync(db, { id: 1 });
  db.exec("commit");
} catch (error) {
  db.exec("rollback");
  throw error;
}
```

**bun:sqlite**:

```ts
// the function must not await, so it runs .runSync()
db.transaction(() => getUser.runSync(db, { id: 1 }))();
```

`Bun.sql`:

```ts
await db.begin((tx) => getUser.run(tx, { id: 1 }));
```

**libSQL**:

```ts
const tx = await db.transaction("write");
try {
  await getUser.run(tx, { id: 1 });
  await tx.commit();
} finally {
  tx.close();
}
```

**Turso**:

```ts
await db.transactionAsync((tx) => getUser.run(tx, { id: 1 }))();
```

**Durable Objects**:

```ts
export class Board extends DurableObject {
  user(id: number) {
    return this.ctx.storage.transactionSync(() =>
      getUser.runSync(this.ctx.storage.sql, { id }),
    );
  }
}
```

**sqlite3**:

```ts
import { promisify } from "node:util";

const exec = promisify(db.exec.bind(db));
await exec("begin");
try {
  await getUser.run(db, { id: 1 });
  await exec("commit");
} catch (error) {
  await exec("rollback");
  throw error;
}
```

**sqlite**:

```ts
await db.exec("begin");
try {
  await getUser.run(db, { id: 1 });
  await db.exec("commit");
} catch (error) {
  await db.exec("rollback");
  throw error;
}
```

**Drizzle**:

```ts
db.transaction((tx) => getUser.runSync(tx, { id: 1 }));
```

D1 has no transaction a query can join: its `batch()` takes D1's own prepared statements.

### `.runSync()`

For databases that support synchronous operations, you can use `.runSync()`.

> **Supported drivers.** - `better-sqlite3`
> - `node:sqlite`
> - `bun:sqlite`
> - the `Database` of `@tursodatabase/database/compat`
> - a Durable Object's `ctx.storage.sql`

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

const db = new Database("app.db");
const user = getUser.runSync(db, { id: 1 });
//    ^? const user: {
//           id: number;
//           name: string;
//           email: string | null;
//       } | null
```

### `.try()`

To get your database's errors as values, use `.try()`. It returns `{ value }`, or `{ error }` when the database refuses the query:

**main.ts (Postgres)**:

```ts
import createUser from "./create_user.sql";

const result = await createUser.try(db, {
  name: "Ada",
  email: "ada@example.com",
});
if (result.error?.condition === "unique_violation") {
  result.error.constraint;
  //           ^? constraint: "users_email_key" | "users_pkey" | undefined
}
```

**main.ts (MySQL)**:

```ts
import createUser from "./create_user.sql";

const result = await createUser.try(db, {
  name: "Ada",
  email: "ada@example.com",
});
if (result.error?.code === "ER_DUP_ENTRY") {
  result.error.key;
  //           ^? key: "users.PRIMARY" | "users.email" | undefined
}
```

**main.ts (SQLite)**:

```ts
import createUser from "./create_user.sql";

const result = await createUser.try(db, {
  name: "Ada",
  email: "ada@example.com",
});
if (result.error?.code === "SQLITE_CONSTRAINT_UNIQUE") {
  result.error.columns;
  //           ^? columns: readonly ["email"] | undefined
}
```

`create_user.sql`:

```sql
insert into users (name, email) values (:name, :email)
```

The error is your database's own, typed with every code it defines, so TypeScript catches a misspelled one:

| Database | Its code | What else it names |
| --- | --- | --- |
| Postgres | `code`, the SQLSTATE, and `condition`, its name | `constraint`, `table`, `column`, `schema`, `detail`, `hint`, `position` |
| MySQL | `code`, the error's name, `errno` and `sqlState` | `key`, `constraint`, `table`, `column` |
| SQLite | `code`, the result code's name, and `errcode` | `table`, `columns`, `constraint` |

When the query runs none of your own code, such as a trigger or a function you wrote, Reylo also narrows each error to the names it can carry: a unique violation names one of the unique keys of the tables the query writes, as in the examples above. Your database's page says what it narrows.

Each driver's error is read into the same fields, and the driver's own error is the `cause`. Any other error still throws, as from `.run()`: a refused connection, say. The file exports the error's type as `QueryError`. On a synchronous SQLite driver, `.trySync()` returns the result itself.

On Postgres, an error aborts the transaction it is in, even an error `.try()` returned: every later statement in it fails, and the transaction rolls back. MySQL and SQLite keep it open after most errors. The [Postgres page](https://reylo.dev/postgres#database-errors) shows how to carry on after an error.

### `.with()`

To bind the query to a client, call `.with()`. It returns a function that takes the parameters:

```ts
import getUser from "./get_user.sql";

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

### Tracing

Reylo publishes every query it runs on Node's [tracing channel](https://nodejs.org/api/diagnostics_channel.html#class-tracingchannel) `reylo:query`, so your tracer or logger can wrap each one in a span:

```ts
import { tracingChannel } from "node:diagnostics_channel";
import type { QueryTrace } from "reylo";

const queries = tracingChannel<QueryTrace & { span: Span }>("reylo:query");

queries.subscribe({
  start(ctx) {
    ctx.span = tracer.startSpan("db.query", {
      attributes: {
        "db.query.summary": ctx.query.source,
        "db.query.text": ctx.query.sql,
      },
    });
  },
  error(ctx) {
    ctx.span.recordException(ctx.error);
  },
  end(ctx) {
    if (ctx.sync) ctx.span.end();
  },
  asyncEnd(ctx) {
    ctx.span.end();
  },
});
```

Each event carries the `query`, its `params`, the `client`, and then its `result` or `error`. Calls to `.runSync()` and `.trySync()` set `sync` and end on `end`; every other call ends on `asyncEnd`.

The channel is there on Node, on Bun, and on Cloudflare Workers with the `nodejs_compat` flag. Elsewhere, queries run untraced.

## Inference

Reylo lets you use the full power of SQL provided by your database. Postgres, MySQL and SQLite are all supported.

Reylo is a [content mapper](https://devblogs.microsoft.com/typescript/announcing-typescript-7-1-beta/#content-mappers), the kind of plugin [TypeScript 7.1](https://devblogs.microsoft.com/typescript/announcing-typescript-7-1-beta/) introduced for files that aren't TypeScript. 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.

To type a query, Reylo has your live database prepare it, without running it, and reads the catalog. From the answer, it learns:

- **Parameter types** — from where each parameter is used, or its cast
- **Column types** — from the table, or the expression that computes the column
- **Nullability** — from `NOT NULL` and outer joins
- **Cardinality** — from keys, aggregates, `limit 1` and `returning`
- **Column comments** — on Postgres and MySQL, shown when you hover a field that reads the column

**The TypeScript type of each SQL type, in each dialect**

Reylo decodes its own queries' values itself, so every driver gives the same types. A 64-bit integer is a `bigint`, a decimal its exact text, and an instant its ISO 8601 text in UTC: no value is a `Date`.

**Postgres**

| SQL type | TypeScript |
| --- | --- |
| `smallint`, `integer`, `oid` | `number` |
| `bigint` | `bigint` |
| `real`, `double precision` | `number` |
| `numeric`, `money` | `string` |
| `text`, `varchar`, `char` | `string` |
| `boolean` | `boolean` |
| `uuid` | `string` |
| `date` | `string`, as `2026-10-20` |
| `timestamp` | `string`, as `2026-10-20T10:00:00.123456` |
| `timestamptz` | `string`, the instant in UTC, as `2026-10-20T08:00:00.123456Z` |
| `time`, `timetz` | `string` |
| `interval` | `string`, an ISO 8601 duration, as `P1DT2H3M4S` |
| `json`, `jsonb` | [typed by its schema or the query](https://reylo.dev/#json) |
| `bytea` | `Uint8Array` |
| An array | an array of its element type, a `NULL` element `null` |
| An enum | the union of its labels |
| A domain | its base type |
| A row value | `string` |
| pgvector's `vector`, `halfvec` | `number[]` |
| `hstore` | `Record<string, string \| null>` |
| Every other type (`inet`, `cidr`, ranges, `point`, `tsvector`, `xml`, …) | a `string`, as the database writes it |

**MySQL**

| SQL type | TypeScript |
| --- | --- |
| `TINYINT`, `SMALLINT`, `MEDIUMINT`, `INT`, `YEAR` | `number` |
| `BOOLEAN` (a `TINYINT(1)`), `BIT(1)` | `boolean` |
| A comparison, `IS NULL`, `IN`, `BETWEEN`, `LIKE`, `EXISTS`, `NOT`, `AND`, `OR` | `boolean` |
| `BIGINT`, `count(*)` | `bigint` |
| `DECIMAL` | `string` |
| `FLOAT`, `DOUBLE` | `number` |
| `CHAR`, `VARCHAR`, `TEXT` | `string` |
| `ENUM` | `string` |
| `SET` | `string`, as `a,b` |
| `DATE` | `string`, as `2026-10-20` |
| `DATETIME`, `TIMESTAMP` | `string`, as `2026-10-20T10:00:00.123` |
| `TIME` | `string` |
| `JSON` | [typed by its schema or the query](https://reylo.dev/#json) |
| `BINARY`, `VARBINARY`, `BLOB`, `BIT` wider than 1 | `Uint8Array` |
| `VECTOR` | `number[]` |

**SQLite**

| Declared type | TypeScript |
| --- | --- |
| `INTEGER`, `INT`, `count(*)` | `number` |
| `BIGINT`, `INT8`, `UNSIGNED BIG INT` | `bigint` |
| `REAL` | `number` |
| `TEXT` | `string` |
| `BLOB` | `Uint8Array` |
| `BOOLEAN`, `BOOL` | `boolean` |
| A comparison, `IS NULL`, `IN`, `BETWEEN`, `LIKE`, `EXISTS`, `NOT`, `AND`, `OR` | `boolean` |
| `NUMERIC`, `DATE`, `DATETIME` | `number \| string` |
| No type, or `ANY` in a `STRICT` table | `unknown` |
| `json_object(…)`, `json_group_array(…)` | [the object or array it builds](https://reylo.dev/#json) |

For every rule and edge case of each database, from 64-bit integers to time zones, nulls, row counts and parameters, see its own page: [Postgres](https://reylo.dev/postgres#data-types), [MySQL](https://reylo.dev/mysql#data-types) and [SQLite](https://reylo.dev/sqlite#data-types).

At run time, the [preload](https://reylo.dev/#setup-node) or a bundler [plugin](https://reylo.dev/#plugins) turns the same file into a module that runs the query through your driver.

Reylo caches each answer in [`.reylo/`](https://reylo.dev/#faq-reylo-directory), keyed by a fingerprint of the schema, so after a migration the next type check sees the new schema: a dropped column fails in the `.sql` file that names it, and in the code that reads it.

**Where each type comes from, in each dialect**

**Postgres**

Reylo sends the query to the live database [prepared](https://www.postgresql.org/docs/current/sql-prepare.html) and reads back the database's own type information on its parameters and return type. This information is cross-referenced against the schema catalog, enabling high-fidelity type inference for any query.

| What | Where it comes from |
| --- | --- |
| Parameter types | the server's description of the prepared statement |
| Column types | the server's description; an enum as its labels, a domain as its base type, a JSON column as what its JSON Schema `CHECK` admits |
| Nullability | the catalog's `NOT NULL`, outer joins, the expressions |
| Cardinality | primary and unique keys, aggregates, `limit 1`, `returning` |
| Nested JSON | built with `jsonb_build_object`, `jsonb_agg` or `to_jsonb` of a row, typed from their arguments |

**MySQL**

Reylo sends the query to the live database [prepared](https://dev.mysql.com/doc/refman/8.4/en/sql-prepared-statements.html) and reads back the database's own type information on its parameters and return type.

Reylo adds which parameters may be null, the row count, and the shape of JSON the query builds.

| What | Where it comes from |
| --- | --- |
| Parameter types | the prepared statement's metadata; whether one may be null, from the statement, as on Postgres |
| Column types | the prepared statement's metadata; a JSON column read from its table as what its JSON Schema `CHECK` admits |
| Nullability | the server's `NOT NULL` flag, then what the `where` and `group by` rule out |
| Cardinality | primary and unique keys, aggregates; a write is `void` |
| Nested JSON | built with `json_object` and `json_arrayagg`, typed from their arguments |

**SQLite**

Reylo reads the schema from the database file and [prepares](https://sqlite.org/c3ref/prepare.html) the query in its own copy of SQLite, which reports where each column and parameter comes from.

| What | Where it comes from |
| --- | --- |
| Parameter types | where SQLite places the parameter: a comparison with a column, an element of a row value, an `insert` or `update` column, a `cast`, `limit` and `offset` |
| Column types | the declared type's affinity |
| Nullability | the table's `NOT NULL`, outer joins, Reylo's table of SQLite's built-in functions |
| Cardinality | unique indexes and `integer primary key` |
| Nested JSON | built with `json_object` and `json_group_array`, typed from their arguments |

### Column types

Each column comes back with its type:

```sql
select id, name
from users
```

```ts
// Returns
{ id: number; name: string }[]
```

### Named parameters

For convenience, there is some Reylo-specific syntax that can be used inside your `.sql` files. This syntax is transpiled into the idiomatic form for your database's dialect.

Name a parameter with a colon, and Reylo infers its type from where it's used:

```sql
update users
set name = :name
where id = :id
```

```ts
// Params
{ name: string; id: number }
// Returns
void
```

### Casts

Where the query doesn't pin down a parameter's type, cast it. Reylo compiles the cast to each database's own:

```sql
select id, name
from users
where name like concat('%', :q::text, '%')
```

```ts
// Params
{ q: string }
// Returns
{ id: number; name: string }[]
```

**When a parameter's type cannot be inferred**

It's an error that says to [cast it](https://reylo.dev/#casts), unless [`strict_params: false`](https://reylo.dev/#strict_params) types it `unknown`.

**Postgres**

Postgres types a parameter from its context: a bare `select :x` is a `string`, and `select :x is null` is an error until a cast types it.

**MySQL**

MySQL types every parameter: one that nothing else types, such as a bare `select :x`, is a `string`.

**SQLite**

A bare `select :x`, or an argument of a table-valued function other than `json_each` and `json_tree`, has no type until a cast gives it one:

```sql
select length(:text::text) as n
```

A call to a function your application registers fails the type check, cast or not: Reylo prepares the statement in its own SQLite, which lacks it ("no such function"). Apply that function in TypeScript instead.

### Left joins

A left join makes the joined table's columns nullable:

```sql
select u.name, p.title
from users u
left join posts p on p.user_id = u.id
```

```ts
// Returns
{ name: string; title: string | null }[]
```

A null check on one joined column makes the whole joined row non-null:

```sql
select u.name, p.title
from users u
left join posts p on p.user_id = u.id
where p.published_at is not null
```

```ts
// Returns
{ name: string; title: string }[]
```

### Computed columns

Reylo infers which computed columns can never be null:

```sql
select u.name,
  coalesce(u.email, '') as email,
  exists (select 1 from posts p where p.user_id = u.id) as has_posts
from users u
```

```ts
// Returns
{ name: string; email: string; has_posts: boolean }[]
```

### Nullable parameters

To accept null, cast a parameter to `nullable(…)`. Leaving it out passes `null`:

```sql
select id, title
from posts
where user_id = coalesce(:user_id::nullable(int), user_id)
```

```ts
// Params
{ user_id?: number | null }
// Returns
{ id: number; title: string }[]
```

A parameter written as it is to a column that may hold null, as in `insert into posts (published_at) values (:published_at)`, takes null too, and may be left out as well. Any other parameter takes no null: if one gets `null`, or is left out, `.run()` throws before the query runs, since the result's types rely on its value.

### Dynamic sorting

To pick the sort order at run time, order by a `case` on a parameter:

```sql
-- a parameter appears once, so one read twice comes from a with
with input as (select :sort::text as sort)
select u.id, u.name
from users u, input
order by
  case when input.sort = 'name' then u.name end,
  case when input.sort = 'newest' then u.created_at end desc,
  u.id
```

```ts
// Params
{ sort: string }
// Returns
{ id: number; name: string }[]
```

### Dynamic filtering

To filter a list by a form's values, pass them as one [JSON parameter](https://reylo.dev/#typed-json). Its schema says what each filter takes and what it defaults to, and Reylo checks it before the query runs:

Postgres:

```sql
with input as (
  select * from jsonb_to_record(:filters::jsonb('{
    "type": "object",
    "properties": {
      "q": { "type": "string", "maxLength": 100, "default": "" },
      "author_id": { "type": "integer" },
      "status": { "enum": ["published", "draft", "all"], "default": "all" },
      "sort": { "enum": ["newest", "oldest", "title"], "default": "newest" }
    },
    "additionalProperties": false
  }')) as f(q text, author_id int, status text, sort text)
)
select p.id, p.title, u.name as author
from posts p
join users u on u.id = p.user_id
cross join input
where p.title like concat('%', input.q, '%')
  and (input.author_id is null or p.user_id = input.author_id)
  and case input.status
    when 'published' then p.published_at is not null
    when 'draft' then p.published_at is null
    else true
  end
order by
  case when input.sort = 'title' then p.title end,
  case when input.sort = 'oldest' then p.id end,
  p.id desc
```

```ts
// Params
{
  filters: {
    q?: string;
    author_id?: number;
    status?: "published" | "draft" | "all";
    sort?: "newest" | "oldest" | "title";
  };
}
// Returns
{ id: number; title: string; author: string }[]
```

MySQL:

```sql
with input as (
  select f.q, f.author_id, f.status, f.sort
  from json_table(:filters::json('{
    "type": "object",
    "properties": {
      "q": { "type": "string", "maxLength": 100, "default": "" },
      "author_id": { "type": "integer" },
      "status": { "enum": ["published", "draft", "all"], "default": "all" },
      "sort": { "enum": ["newest", "oldest", "title"], "default": "newest" }
    },
    "additionalProperties": false
  }'), '$' columns (
    q varchar(100) path '$.q',
    author_id int path '$.author_id',
    status varchar(9) path '$.status',
    sort varchar(6) path '$.sort'
  )) as f
)
select p.id, p.title, u.name as author
from posts p
join users u on u.id = p.user_id
cross join input
where p.title like concat('%', input.q, '%')
  and (input.author_id is null or p.user_id = input.author_id)
  and case input.status
    when 'published' then p.published_at is not null
    when 'draft' then p.published_at is null
    else true
  end
order by
  case when input.sort = 'title' then p.title end,
  case when input.sort = 'oldest' then p.id end,
  p.id desc
```

```ts
// Params
{
  filters: {
    q?: string;
    author_id?: number;
    status?: "published" | "draft" | "all";
    sort?: "newest" | "oldest" | "title";
  };
}
// Returns
{ id: number; title: string; author: string }[]
```

SQLite:

```sql
with input as (
  select f ->> '$.q' as q, f ->> '$.author_id' as author_id, f ->> '$.status' as status, f ->> '$.sort' as sort
  from (select :filters::json('{
    "type": "object",
    "properties": {
      "q": { "type": "string", "maxLength": 100, "default": "" },
      "author_id": { "type": "integer" },
      "status": { "enum": ["published", "draft", "all"], "default": "all" },
      "sort": { "enum": ["newest", "oldest", "title"], "default": "newest" }
    },
    "additionalProperties": false
  }') as f)
)
select p.id, p.title, u.name as author
from posts p
join users u on u.id = p.user_id
cross join input
where p.title like concat('%', input.q, '%')
  and (input.author_id is null or p.user_id = input.author_id)
  and case input.status
    when 'published' then p.published_at is not null
    when 'draft' then p.published_at is null
    else true
  end
order by
  case when input.sort = 'title' then p.title end,
  case when input.sort = 'oldest' then p.id end,
  p.id desc
```

```ts
// Params
{
  filters: {
    q?: string;
    author_id?: number;
    status?: "published" | "draft" | "all";
    sort?: "newest" | "oldest" | "title";
  };
}
// Returns
{ id: number; title: string; author: string }[]
```

A filter left out takes its default, and one with none, like the author, is null, which turns it off:

```ts
import searchPosts from "./search_posts.sql";

// POST /posts/search with { "q": "sql", "status": "draft" }
const posts = await searchPosts.run(db, { filters: await request.json() });
```

### Unique keys

Reylo infers each query's cardinality, how many rows it returns. A query that matches a unique key returns one row or null:

```sql
-- Schema (Postgres)
create table users (
  id serial primary key,
  name text not null,
  email text unique
);
```

```sql
-- Schema (MySQL)
create table users (
  id int auto_increment primary key,
  name text not null,
  email varchar(255) unique
);
```

```sql
-- Schema (SQLite)
create table users (
  id integer primary key,
  name text not null,
  email text unique
) strict;
```

```sql
select id, name
from users
where email = :email::text
```

```ts
// Params
{ email: string }
// Returns
{ id: number; name: string }[]
```

**Which matches count as a key, in each dialect**

**Postgres**

Also a key match:

- a join on the joined table's key
- a unique index on an expression or with a `where`
- a `distinct` or `group by` over values the `where` pins
- a subquery, a `with` query or a view that keeps a key

**MySQL**

Also a key match:

- a join on the joined table's numeric key
- a `distinct` or `group by` over values the `where` pins
- a derived table, a `with` query or a view that keeps a key

A unique index on an expression gives `many`.

**SQLite**

Also a key match:

- a join on the joined table's key
- a `distinct` or `group by` over values the `where` pins
- a subquery, a `with` query or a view that keeps a key

A unique index on an expression or with a `where` gives `many`.

At run time, `.run()` throws rather than return a row count its type rules out.

### Aggregates

An aggregate without a group by returns exactly one row:

Postgres:

```sql
select count(*)::int as posts
from posts
where user_id = :user_id::int
```

```ts
// Params
{ user_id: number }
// Returns
{ posts: number }[]
```

MySQL:

```sql
select count(*) as posts
from posts
where user_id = :user_id::int
```

```ts
// Params
{ user_id: number }
// Returns
{ posts: bigint }[]
```

SQLite:

```sql
select count(*) as posts
from posts
where user_id = :user_id::int
```

```ts
// Params
{ user_id: number }
// Returns
{ posts: number }[]
```

### Subqueries

Run unrelated subqueries side by side, in one query and one round trip. With no `from`, the query returns exactly one row, and `.run()` resolves to it:

Postgres:

```sql
-- the five latest posts
with recent as (select id, title from posts order by id desc limit 5)
select
  (select count(*)::int from users) as user_count,
  (select count(*)::int from posts) as post_count,
  (
    select coalesce(jsonb_agg(jsonb_build_object(
      'id', r.id,
      'title', r.title
    )), '[]')
    from recent r
  ) as recent_posts
```

```ts
// Returns
{
  user_count: number;
  post_count: number;
  recent_posts: { id: number; title: string }[];
}[]
```

MySQL:

```sql
-- the five latest posts
with recent as (select id, title from posts order by id desc limit 5)
select
  (select count(*) from users) as user_count,
  (select count(*) from posts) as post_count,
  (
    select coalesce(json_arrayagg(json_object(
      'id', r.id,
      'title', r.title
    )), json_array())
    from recent r
  ) as recent_posts
```

```ts
// Returns
{
  user_count: bigint;
  post_count: bigint;
  recent_posts: { id: number; title: string }[];
}[]
```

SQLite:

```sql
-- the five latest posts
with recent as (select id, title from posts order by id desc limit 5)
select
  (select count(*) from users) as user_count,
  (select count(*) from posts) as post_count,
  (
    select json_group_array(json_object(
      'id', r.id,
      'title', r.title
    ))
    from recent r
  ) as recent_posts
```

```ts
// Returns
{
  user_count: number;
  post_count: number;
  recent_posts: { id: number; title: string }[];
}[]
```

### Returning inserts

An insert that returns its row resolves to exactly that row:

Postgres and SQLite:

```sql
insert into users (name)
values (:name::text)
returning id
```

```ts
// Params
{ name: string }
// Returns
{ id: number }[]
```

### Enums

A text column whose check constraint lists its values is a union of them, as a Postgres enum is:

```sql
-- Schema
create table events (
  id serial primary key,
  kind text not null check (kind in ('click', 'key'))
);
```

Postgres:

```sql
select id, kind
from events
```

```ts
// Returns
{ id: number; kind: "click" | "key" }[]
```

### JSON aggregation

Type information bubbles up through JSON aggregation:

Postgres:

```sql
select u.name,
  jsonb_agg(jsonb_build_object('id', p.id, 'title', p.title)) as posts
from users u
join posts p on p.user_id = u.id
group by u.id
```

```ts
// Returns
{ name: string; posts: { id: number; title: string }[] }[]
```

MySQL:

```sql
select u.name,
  json_arrayagg(json_object('id', p.id, 'title', p.title)) as posts
from users u
join posts p on p.user_id = u.id
group by u.id
```

```ts
// Returns
{ name: string; posts: { id: number; title: string }[] }[]
```

SQLite:

```sql
select u.name,
  json_group_array(json_object('id', p.id, 'title', p.title)) as posts
from users u
join posts p on p.user_id = u.id
group by u.id
```

```ts
// Returns
{ name: string; posts: { id: number; title: string }[] }[]
```

### Nested fetching

Fetch each row's related rows with an aggregated subquery: it collects them into JSON, and Reylo types every field inside it:

Postgres:

```sql
select u.id, u.name,
  -- every post, as an array
  (
    select coalesce(jsonb_agg(jsonb_build_object(
      'id', p.id,
      'title', p.title
    ) order by p.id), '[]')
    from posts p
    where p.user_id = u.id
  ) as posts,
  -- the latest post, or null
  (
    select jsonb_build_object('id', p.id, 'title', p.title)
    from posts p
    where p.user_id = u.id
    order by p.published_at desc nulls last
    limit 1
  ) as latest_post
from users u
```

```ts
// Returns
{
  id: number;
  name: string;
  posts: { id: number; title: string }[];
  latest_post: { id: number; title: string } | null;
}[]
```

MySQL:

```sql
select u.id, u.name,
  -- every post, as an array
  (
    select coalesce(json_arrayagg(json_object(
      'id', p.id,
      'title', p.title
    )), json_array())
    from posts p
    where p.user_id = u.id
  ) as posts,
  -- the latest post, or null
  (
    select json_object('id', p.id, 'title', p.title)
    from posts p
    where p.user_id = u.id
    order by p.published_at desc
    limit 1
  ) as latest_post
from users u
```

```ts
// Returns
{
  id: number;
  name: string;
  posts: { id: number; title: string }[];
  latest_post: { id: number; title: string } | null;
}[]
```

SQLite:

```sql
select u.id, u.name,
  -- every post, as an array
  (
    select json_group_array(json_object(
      'id', p.id,
      'title', p.title
    ))
    from posts p
    where p.user_id = u.id
  ) as posts,
  -- the latest post, or null
  (
    select json_object('id', p.id, 'title', p.title)
    from posts p
    where p.user_id = u.id
    order by p.published_at desc nulls last
    limit 1
  ) as latest_post
from users u
```

```ts
// Returns
{
  id: number;
  name: string;
  posts: { id: number; title: string }[];
  latest_post: { id: number; title: string } | null;
}[]
```

### JSON Schema

Declare a JSON Schema, and Reylo types the JSON it describes. A JSON column declares one in a check constraint:

```sql
-- Schema (Postgres)
create extension if not exists pg_jsonschema;

create table events (
  id serial primary key,
  payload jsonb not null check (jsonb_matches_schema('{
    "type": "object",
    "properties": {
      "x": { "type": "integer" },
      "y": { "type": "integer" }
    },
    "required": ["x", "y"],
    "additionalProperties": false
  }', payload))
);
```

```sql
-- Schema (MySQL)
create table events (
  id int auto_increment primary key,
  payload json not null check (json_schema_valid('{
    "type": "object",
    "properties": {
      "x": { "type": "integer" },
      "y": { "type": "integer" }
    },
    "required": ["x", "y"],
    "additionalProperties": false
  }', payload))
);
```

Postgres and MySQL:

```sql
select id, payload
from events
```

```ts
// Returns
{ id: number; payload: { x: number; y: number } }[]
```

### JSON parameters

A JSON parameter declares its schema in its cast to `json` or `jsonb`, and Reylo infers the parameter's type from it:

```sql
insert into events (kind, payload)
values ('click', :payload::json('{
  "type": "object",
  "properties": {
    "x": { "type": "integer" },
    "y": { "type": "integer" },
    "button": { "type": "string", "enum": ["left", "right"] }
  },
  "required": ["x", "y"]
}'))
```

```ts
// Params
{ payload: { x: number; y: number; button?: "left" | "right" } }
// Returns
void
```

Reylo also checks the value against the schema when you call `.run()`:

```ts
import recordClick from "./record_click.sql";

// type-checks, but .run() rejects it: x must be an integer
await recordClick.run(db, { payload: { x: 1.5, y: 20 } });
```

A schema can nest objects and arrays, and check every value in them:

```sql
insert into events (kind, payload)
values ('order', :order::json('{
  "type": "object",
  "properties": {
    "customer": { "type": "string", "format": "email" },
    "items": {
      "type": "array",
      "minItems": 1,
      "items": {
        "type": "object",
        "properties": {
          "sku": { "type": "string", "pattern": "^[A-Z]{3}-[0-9]{4}$" },
          "qty": { "type": "integer", "minimum": 1 }
        },
        "required": ["sku", "qty"]
      }
    },
    "shipping": { "enum": ["standard", "express"] }
  },
  "required": ["customer", "items"]
}'))
```

```ts
// Params
{
  order: {
    customer: string;
    items: { sku: string; qty: number }[];
    shipping?: "standard" | "express";
  };
}
// Returns
void
```

A JSON parameter written to a schema-checked column takes that schema as its type, with no cast:

```sql
-- Schema
create extension if not exists pg_jsonschema;

create table events (
  id serial primary key,
  payload jsonb not null check (jsonb_matches_schema('{
    "type": "object",
    "properties": {
      "x": { "type": "integer" },
      "y": { "type": "integer" }
    },
    "required": ["x", "y"],
    "additionalProperties": false
  }', payload))
);
```

Postgres:

```sql
insert into events (payload)
values (:payload)
```

```ts
// Params
{ payload: { x: number; y: number } }
// Returns
void
```

### Filtering by a list

Pass the list as one parameter:

Postgres:

```sql
select id, name from users where id = any(:ids::int[])
```

```ts
// Params
{ ids: number[] }
// Returns
{ id: number; name: string }[]
```

MySQL:

```sql
-- MySQL has no array type, so the list is a JSON parameter
with input as (
  select :ids::json('{
    "type": "array",
    "items": { "type": "integer" }
  }') as ids
)
select id, name from users
where id in (
  select j.id from input, json_table(input.ids, '$[*]' columns (
    id int path '$'
  )) as j
)
```

```ts
// Params
{ ids: number[] }
// Returns
{ id: number; name: string }[]
```

SQLite:

```sql
-- SQLite has no array type, so the list is a JSON parameter
with input as (
  select :ids::json('{
    "type": "array",
    "items": { "type": "integer" }
  }') as ids
)
select id, name from users
where id in (select j.value from input, json_each(input.ids) as j)
```

```ts
// Params
{ ids: number[] }
// Returns
{ id: number; name: string }[]
```

### Bulk inserts

Pass the rows as one [JSON parameter](https://reylo.dev/#typed-json): the schema in its cast types them, and Reylo checks every row before the statement runs:

Postgres:

```sql
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
```

```ts
// Params
{ users: { name: string; email?: string | null }[] }
// Returns
{ id: number }[]
```

MySQL:

```sql
insert into users (name, email)
with input as (
  select :users::json('{
    "type": "array",
    "maxItems": 1000,
    "items": {
      "type": "object",
      "properties": {
        "name": { "type": "string" },
        "email": { "type": ["string", "null"], "format": "email" }
      },
      "required": ["name"]
    }
  }') as users
)
select u.name, u.email
from input, json_table(input.users, '$[*]' columns (
  name text path '$.name',
  email varchar(255) path '$.email'
)) as u
```

```ts
// Params
{ users: { name: string; email?: string | null }[] }
// Returns
void
```

SQLite:

```sql
with input as (
  select :users::json('{
    "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.value ->> 'name', u.value ->> 'email'
from input, json_each(input.users) as u
returning id
```

```ts
// Params
{ users: { name: string; email?: string | null }[] }
// Returns
{ id: number }[]
```

### Nested inserts

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

**Postgres**

Postgres writes both tables in one statement: a `with` inserts the users, and the statement inserts the posts:

Postgres:

```sql
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)
```

```ts
// Params
{
  users: {
    name: string;
    email: string;
    posts: { title: string }[];
  }[];
}
// Returns
void
```

**MySQL**

On MySQL, a `with` only reads, so the users and the posts are two statements, run in one transaction:

`main.ts`:

```ts
import mysql from "mysql2/promise";
import insertUsers, { type Params } from "./insert_users.sql";
import insertPosts from "./insert_posts.sql";

const db = mysql.createPool(process.env.DATABASE_URL);
const users = [
  {
    name: "Ada",
    email: "ada@example.com",
    posts: [{ title: "Notes" }],
  },
];

const conn = await db.getConnection();
try {
  await conn.beginTransaction();
  await insertUsers.run(conn, { users });
  await insertPosts.run(conn, { users });
  await conn.commit();
} catch (error) {
  await conn.rollback();
  throw error;
} finally {
  conn.release();
}
```

`insert_users.sql`:

```sql
insert into users (name, email)
with input as (
  select :users::json('{
    "type": "array",
    "items": {
      "type": "object",
      "properties": {
        "name": { "type": "string" },
        "email": { "type": "string", "format": "email" }
      },
      "required": ["name", "email"]
    }
  }') as users
)
select u.name, u.email
from input, json_table(input.users, '$[*]' columns (
  name text path '$.name',
  email varchar(255) path '$.email'
)) as u
```

`insert_posts.sql`:

```sql
insert into posts (user_id, title)
with input as (
  select :users::json('{
    "type": "array",
    "items": {
      "type": "object",
      "properties": {
        "email": { "type": "string" },
        "posts": {
          "type": "array",
          "items": {
            "type": "object",
            "properties": { "title": { "type": "string" } },
            "required": ["title"]
          }
        }
      },
      "required": ["email", "posts"]
    }
  }') as users
)
select users.id, u.title
from input, json_table(input.users, '$[*]' columns (
  email varchar(255) path '$.email',
  nested path '$.posts[*]' columns (title text path '$.title')
)) as u
join users on users.email = u.email
where u.title is not null
```

**SQLite**

On SQLite, a `with` only reads, so the users and the posts are two statements, run in one transaction:

`main.ts`:

```ts
import Database from "better-sqlite3";
import insertUsers, { type Params } from "./insert_users.sql";
import insertPosts from "./insert_posts.sql";

const db = new Database("app.db");
const users = [
  {
    name: "Ada",
    email: "ada@example.com",
    posts: [{ title: "Notes" }],
  },
];

db.transaction(() => {
  insertUsers.runSync(db, { users });
  insertPosts.runSync(db, { users });
})();
```

`insert_users.sql`:

```sql
with input as (
  select :users::json('{
    "type": "array",
    "items": {
      "type": "object",
      "properties": {
        "name": { "type": "string" },
        "email": { "type": "string", "format": "email" }
      },
      "required": ["name", "email"]
    }
  }') as users
)
insert into users (name, email)
select u.value ->> 'name', u.value ->> 'email'
from input, json_each(input.users) as u
```

`insert_posts.sql`:

```sql
with input as (
  select :users::json('{
    "type": "array",
    "items": {
      "type": "object",
      "properties": {
        "email": { "type": "string" },
        "posts": {
          "type": "array",
          "items": {
            "type": "object",
            "properties": { "title": { "type": "string" } },
            "required": ["title"]
          }
        }
      },
      "required": ["email", "posts"]
    }
  }') as users
)
insert into posts (user_id, title)
select users.id, p.value ->> 'title'
from input
join json_each(input.users) as u
join users on users.email = u.value ->> 'email'
join json_each(u.value -> 'posts') as p
```

### Discriminated unions with `oneOf`

A `oneOf` in a schema is a [union](https://reylo.dev/#unions). Give each member a key with one value, and it is a discriminated union, which TypeScript narrows on that key:

```sql
-- Schema (Postgres)
create extension if not exists pg_jsonschema;

create table events (
  id serial primary key,
  payload jsonb not null check (jsonb_matches_schema('{
    "type": "object",
    "oneOf": [{
      "properties": {
        "kind": { "const": "click" },
        "x": { "type": "integer" },
        "y": { "type": "integer" }
      },
      "required": ["kind", "x", "y"],
      "additionalProperties": false
    }, {
      "properties": {
        "kind": { "const": "key" },
        "code": { "type": "string" }
      },
      "required": ["kind", "code"],
      "additionalProperties": false
    }]
  }', payload))
);
```

```sql
-- Schema (MySQL)
-- MySQL checks JSON Schema draft 4, which has no const
create table events (
  id int auto_increment primary key,
  payload json not null check (json_schema_valid('{
    "type": "object",
    "oneOf": [{
      "properties": {
        "kind": { "enum": ["click"] },
        "x": { "type": "integer" },
        "y": { "type": "integer" }
      },
      "required": ["kind", "x", "y"],
      "additionalProperties": false
    }, {
      "properties": {
        "kind": { "enum": ["key"] },
        "code": { "type": "string" }
      },
      "required": ["kind", "code"],
      "additionalProperties": false
    }]
  }', payload))
);
```

Postgres and MySQL:

```sql
select id, payload
from events
```

```ts
// Returns
{
  id: number;
  payload: {
    kind: "click";
    x: number;
    y: number;
  } | { kind: "key"; code: string };
}[]
```

A parameter's schema works the same way:

Postgres:

```sql
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)
```

```ts
// Params
{
  events: ({
    kind: "click";
    x: number;
    y: number;
  } | { kind: "key"; code: string })[];
}
// Returns
void
```

MySQL:

```sql
insert into events (kind, payload)
with input as (
  select :events::json('{
    "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
)
select e.kind, e.payload
from input, json_table(input.events, '$[*]' columns (
  kind text path '$.kind',
  payload json path '$'
)) as e
```

```ts
// Params
{
  events: ({
    kind: "click";
    x: number;
    y: number;
  } | { kind: "key"; code: string })[];
}
// Returns
void
```

SQLite:

```sql
with input as (
  select :events::json('{
    "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, json_each(input.events) as e
```

```ts
// Params
{
  events: ({
    kind: "click";
    x: number;
    y: number;
  } | { kind: "key"; code: string })[];
}
// Returns
void
```

## Configuration

Configure Reylo in a `.reylo.yaml` (or `.reylo.yml`) file beside `package.json`. Every key is optional:

`.reylo.yaml`:

```yaml
# the dev database Reylo reads your schema from (the default)
direct_url: ${DATABASE_URL}
# the .env files Reylo reads (the default)
env: [.env.local, .env]
# an error for a parameter whose type cannot be inferred (the default)
strict_params: true
# a comment naming each query's file, for your database's monitoring (the default)
sqlcommenter: true
```

### `direct_url`

The dev database Reylo reads your schema from. It defaults to `DATABASE_URL`, and its scheme names the dialect:

**Postgres**, `.env`:

```sh
# or postgresql://
DATABASE_URL=postgres://localhost:5432/app
```

**MySQL**, `.env`:

```sh
# or mariadb://
DATABASE_URL=mysql://root@localhost:3306/app
```

**SQLite**, `.env`:

```sh
# or sqlite:; the path starts at package.json
DATABASE_URL=file:./app.db
```

Point it at a direct connection, not a pooler: describing a query through a pooler in transaction mode, such as PgBouncer's, can fail. You can interpolate other variables from your environment to construct a connection string.

`.reylo.yaml`:

```yaml
# the default is ${DATABASE_URL}
direct_url: postgres://${DB_USER}:${DB_PASSWORD}@${DB_HOST}:5432/app
```

To keep a password out of every file, `$(…)` runs a command, such as a password manager's or a cloud's token command, and puts what it prints in its place. Reylo runs it once per editor session or build, and again when the database refuses the login, as when a token expires.

`.reylo.yaml`:

```yaml
# or $(aws rds generate-db-auth-token …) for a short-lived token
direct_url: postgres://app:$(op read op://dev/db/password)@db:5432/app
```

### `env`

The `.env` files Reylo reads, relative to `.reylo.yaml`. The first file that sets a variable wins, and the environment wins over every file.

`.reylo.yaml`:

```yaml
# the default is [.env.local, .env]
env: [.env.development, .env]
```

### `strict_params`

A [parameter whose type cannot be inferred](https://reylo.dev/#a-parameter-nothing-types) is an error that says to [cast it](https://reylo.dev/#casts). Set `strict_params: false` to type it `unknown` instead.

`.reylo.yaml`:

```yaml
# the default is true
strict_params: false
```

### `sqlcommenter`

Each compiled query ends with a comment that names its file, in [sqlcommenter](https://google.github.io/sqlcommenter/)'s format, so database monitoring that reads it, such as Datadog's and PlanetScale's, groups your queries by file. Set `sqlcommenter: false` to leave it off.

`.reylo.yaml`:

```yaml
# the default is true
sqlcommenter: false
```

**What Postgres receives for queries/get_user.sql, by default**:

```sql
select id, name, email from users where id = $1::int /*file='queries%2Fget_user.sql'*/
```

## Errors

An error in a query shows at its line and column, in your editor as you type and in `tsc`. Your database checks each query, so a misspelled column is the database's own error:

`get_user.sql`:

```sql
select id, nmae, email
from users
where id = :id::int
```

```text
Postgres: get_user.sql:1:12 - error reylo6: column "nmae" does not exist (42703)
MySQL: get_user.sql:1:1 - error reylo6: Unknown column 'nmae' in 'field list' (ER_BAD_FIELD_ERROR)
SQLite: get_user.sql:1:12 - error reylo6: no such column: nmae
```

**Postgres**

Reylo checks its own syntax too, such as a [typed JSON](https://reylo.dev/#typed-json) parameter's schema:

**MySQL**

MySQL gives no position for this error, so it covers the whole statement. Reylo checks its own syntax too, such as a [typed JSON](https://reylo.dev/#typed-json) parameter's schema:

**SQLite**

Reylo checks its own syntax too, such as a [typed JSON](https://reylo.dev/#typed-json) parameter's schema:

`record_click.sql`:

```sql
insert into events (kind, payload)
values ('click', :payload::json('{ "type": object }'))
```

```text
record_click.sql:2:33 - error reylo20: The schema in the cast of :payload is not JSON: expected value at line 1 column 11.
```

Two columns of one name are an error, since each row is an object and keeps only the last:

`post_ids.sql`:

```sql
select u.id, p.id, p.title
from users u
join posts p on p.user_id = u.id
```

```text
Postgres: post_ids.sql:1:16 - error reylo14: Columns 1 and 2 are both named "id", and a row keeps only the last of them. Give one of them another name with `as`.
MySQL, SQLite: post_ids.sql:1:1 - error reylo14: Columns 1 and 2 are both named "id", and a row keeps only the last of them. Give one of them another name with `as`.
```

Sorting or grouping by a parameter is an error, since a parameter is a value and every row would sort the same. To let the caller choose the order, see [Dynamic sorting](https://reylo.dev/#dynamic-sorting):

`list_users.sql`:

```sql
select id, name
from users
order by :sort::text
```

```text
list_users.sql:3:10 - error reylo17: order by :sort sorts every row by the same value, since a parameter is a value and not a column's name; choose the column with case :sort when 'name' then name end
```

A [parameter whose type cannot be inferred](https://reylo.dev/#a-parameter-nothing-types) is Reylo's error too, which says to cast it. When a query changes, its types change with it, and code that reads a column the query no longer selects is an error:

`main.ts`:

```ts
import listPosts from "./list_posts.sql";

const posts = await listPosts.run(db);
for (const post of posts) console.log(post.body);
```

`list_posts.sql`:

```sql
select id, title
from posts
order by id
```

A wrong argument is an error where you call `.run()`:

```ts
import getUser from "./get_user.sql";

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

The type check reports the same errors:

**Terminal**:

```ansi
$ 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
```

While a `.sql` file has an error, `tsc` holds back the TypeScript errors, so fix the SQL first.

## FAQ

**Does the type check run my queries?**

No. Reylo has your database prepare each query, which checks it and describes its parameters and columns without running it, and reads the rest from the catalog. On SQLite, Reylo prepares the query in its own copy of SQLite, from the schema in your database file.

**Does Reylo run migrations?**

Not at the moment: bring your own. Reylo types your queries against whatever schema your dev database has, so apply your migrations with the tool you already use, such as Prisma Migrate, Drizzle Kit, Atlas, dbmate or plain SQL files. After a migration, the next type check sees the new schema: a dropped column is an error in the `.sql` file that names it, and in the code that reads it.

**How do I type-check in CI?**

Start the database, apply the migrations, and run `tsc --noEmit --runExternalCode` with `DATABASE_URL` set. Your tests need the database too.

**What is the .reylo directory, and should I commit it?**

It is Reylo's cache of each query's analysis. It has its own `.gitignore`, so git skips it: don't commit it.

**Can I build without a database?**

No, not yet: Reylo reads your types from a live database, so a type check or a build needs one.

**Why are dates strings, and 64-bit integers bigints?**

Reylo decodes its own queries' values itself, so every driver gives the same types. A 64-bit integer is a `bigint`, since a `number` loses precision past 2^53; a decimal is its exact text; and an instant is its ISO 8601 text in UTC. No value is a `Date`, which a driver reads in a time zone nobody chose. Every SQL type's TypeScript type is in [Inference](https://reylo.dev/#data-types).