# Usage

> 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

Reylo imports each `.sql` file as a typed module.

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

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

## Setup

To install Reylo, you'll need:

- **TypeScript 7.1 (currently in nightly builds).** The first release with content mappers. The `tsconfig.json` entry below needs a nightly from 2026-11-12 or later.
- **Node 24 or later.**
- **macOS, Linux or Windows, on x64 or Arm64.** Reylo's engine is a native program, installed for your machine.
- **A running dev database.** Reylo reads your current schema from it ([Database](https://reylo.dev/typescript#database) points Reylo at it):
  - **Postgres** 14 to 19, including Neon and PGlite
  - **MySQL** 8.0.38 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/docs, 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/reylosql/reylo/tree/main/examples

1. Check the requirements and install Reylo, which brings its engine for the machine, and TypeScript 7.1 (currently typescript@next, a nightly from 2026-11-12 or later): https://reylo.dev/typescript#setup
2. Add { "package": "reylo" } to the contentMappers of tsconfig.json, and add a typecheck script that runs tsc --noEmit --runExternalCode (after next typegen on Next.js): https://reylo.dev/typescript#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/typescript#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/reylosql/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/typescript#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/typescript#editors
6. Write a first query and run it with the project's own driver: https://reylo.dev/typescript#usage and https://reylo.dev/typescript#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 reylosql/reylo --skill reylo
9. If the project queries its database through Prisma Client, move each call to a .sql file as https://reylo.dev/prisma describes, starting with its schema changes.
```

Then set Reylo up in six steps:

### Install

Install Reylo, which brings its engine for your machine, and TypeScript 7.1:

```sh
npm install reylo
npm install -D typescript@next
```

### 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" }]
}
```

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

> **In production.** The preload builds each query's module as it loads, so your app needs the engine at run time: keep `reylo` a dependency, not a dev dependency, and install it without `--omit=optional`. If you compile with `tsc`, copy your `.sql` files into its output directory, keeping their paths, and run the output with the preload.

**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"]
}
```

Nub refuses to add a name close to a popular package's, which takes in `reylo`. Allow it with this flag, from Nub 0.10 on, and add `-W` at a workspace's root:

```sh
nub add --allow-similar-name reylo
```

A `nub.jsonc` applies to every package below it, and the preload fails where Reylo isn't installed. Give a workspace package that installs on its own, without Reylo, a `nub.jsonc` of its own holding `{}`.

**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);
```

It works with Turbopack and 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/configuration#direct_url) in `.reylo.yaml` to a local SQLite file your migrations create ([Cloudflare D1](https://reylo.dev/d1#local-database)). 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/reylosql/reylo/tree/main/examples/node)
- [Nub](https://github.com/reylosql/reylo/tree/main/examples/nub)
- [Bun](https://github.com/reylosql/reylo/tree/main/examples/bun)
- [Vite](https://github.com/reylosql/reylo/tree/main/examples/vite)
- [Next.js](https://github.com/reylosql/reylo/tree/main/examples/nextjs)
- [esbuild](https://github.com/reylosql/reylo/tree/main/examples/esbuild)
- [webpack](https://github.com/reylosql/reylo/tree/main/examples/webpack)
- [Rspack](https://github.com/reylosql/reylo/tree/main/examples/rspack)
- [Jest](https://github.com/reylosql/reylo/tree/main/examples/jest)
- [Cloudflare Workers](https://github.com/reylosql/reylo/tree/main/examples/cloudflare-workers)

### Database

**Reylo requires a connection to a live dev database.** 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
# or mysql://… or sqlite:./path/to/your.db, etc
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.

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

`main.ts`:

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

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

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

**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.

### 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 reylosql/reylo --skill 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.

`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](https://reylo.dev/typescript#run) taken from one, from any of the [supported drivers](https://reylo.dev/typescript#run).

### `.sql`

To get the compiled query, with your database's own placeholders:

```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/inference#naming), use `.raw`. Its path, relative to your project, is `.source`, a name for your [logs and traces](https://reylo.dev/typescript#observability); Reylo's own errors about the query start with it.

### `.run()`

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

Await each query in turn, since a connection runs one statement at a time; on Postgres an error aborts the transaction, so every later statement in it fails until it rolls back ([Database errors](https://reylo.dev/postgres#database-errors)).

#### Postgres

**pg**:

```ts
import pg from "pg";
import createUser from "./create_user.sql";
import getUser from "./get_user.sql";

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

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

// two inserts in one transaction: both land, or neither
const client = await db.connect();
try {
  await client.query("begin");
  await createUser.run(client, { name: "Ada" });
  await createUser.run(client, { name: "Grace" });
  await client.query("commit");
} catch (error) {
  await client.query("rollback");
  throw error;
} finally {
  client.release();
}
```

`postgres.js`:

```ts
import postgres from "postgres";
import createUser from "./create_user.sql";
import getUser from "./get_user.sql";

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

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

// two inserts in one transaction: both land, or neither
await db.begin(async (tx) => {
  await createUser.run(tx, { name: "Ada" });
  await createUser.run(tx, { name: "Grace" });
});
```

`Bun.sql`:

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

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

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

// two inserts in one transaction: both land, or neither
await db.begin(async (tx) => {
  await createUser.run(tx, { name: "Ada" });
  await createUser.run(tx, { name: "Grace" });
});
```

**PGlite**:

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

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

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

// two inserts in one transaction: both land, or neither
await db.transaction(async (tx) => {
  await createUser.run(tx, { name: "Ada" });
  await createUser.run(tx, { name: "Grace" });
});
```

**Neon**:

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

// a query over HTTP
const db = neon(process.env.DATABASE_URL);
const user = await getUser.run(db, { id: 1 });

// a transaction takes a Pool, over a WebSocket: both inserts land, or neither
const pool = new Pool({ connectionString: process.env.DATABASE_URL });
const client = await pool.connect();
try {
  await client.query("begin");
  await createUser.run(client, { name: "Ada" });
  await createUser.run(client, { name: "Grace" });
  await client.query("commit");
} catch (error) {
  await client.query("rollback");
  throw error;
} finally {
  client.release();
}
```

**pg-promise**:

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

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

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

// two inserts in one transaction: both land, or neither
await db.tx(async (tx) => {
  await createUser.run(tx, { name: "Ada" });
  await createUser.run(tx, { name: "Grace" });
});
```

**Slonik**:

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

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

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

// two inserts in one transaction: both land, or neither
await db.transaction(async (tx) => {
  await createUser.run(tx, { name: "Ada" });
  await createUser.run(tx, { name: "Grace" });
});
```

**@vercel/postgres**:

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

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

// two inserts in one transaction: both land, or neither
const client = await sql.connect();
try {
  await client.query("begin");
  await createUser.run(client, { name: "Ada" });
  await createUser.run(client, { name: "Grace" });
  await client.query("commit");
} catch (error) {
  await client.query("rollback");
  throw error;
} finally {
  client.release();
}
```

**Drizzle**:

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

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

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

// two inserts in one transaction: both land, or neither
await db.transaction(async (tx) => {
  await createUser.run(tx, { name: "Ada" });
  await createUser.run(tx, { name: "Grace" });
});
```

**Knex**:

```ts
import knex from "knex";
import createUser from "./create_user.sql";
import getUser from "./get_user.sql";

const db = knex({ client: "pg", connection: process.env.DATABASE_URL });

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

// two inserts in one transaction: both land, or neither
await db.transaction(async (trx) => {
  await createUser.run(trx, { name: "Ada" });
  await createUser.run(trx, { name: "Grace" });
});
```

#### MySQL

**mysql2**:

```ts
import mysql from "mysql2/promise";
import createUser from "./create_user.sql";
import getUser from "./get_user.sql";

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

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

// two inserts in one transaction: both land, or neither
const conn = await db.getConnection();
try {
  await conn.beginTransaction();
  await createUser.run(conn, { name: "Ada" });
  await createUser.run(conn, { name: "Grace" });
  await conn.commit();
} catch (error) {
  await conn.rollback();
  throw error;
} finally {
  conn.release();
}
```

**mariadb**:

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

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

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

// two inserts in one transaction: both land, or neither
const conn = await db.getConnection();
try {
  await conn.beginTransaction();
  await createUser.run(conn, { name: "Ada" });
  await createUser.run(conn, { name: "Grace" });
  await conn.commit();
} catch (error) {
  await conn.rollback();
  throw error;
} finally {
  conn.release();
}
```

**PlanetScale**:

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

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

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

// two inserts in one transaction: both land, or neither
await db.transaction(async (tx) => {
  await createUser.run(tx, { name: "Ada" });
  await createUser.run(tx, { name: "Grace" });
});
```

`Bun.sql`:

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

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

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

// two inserts in one transaction: both land, or neither
await db.begin(async (tx) => {
  await createUser.run(tx, { name: "Ada" });
  await createUser.run(tx, { name: "Grace" });
});
```

**Drizzle**:

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

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

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

// two inserts in one transaction: both land, or neither
await db.transaction(async (tx) => {
  await createUser.run(tx, { name: "Ada" });
  await createUser.run(tx, { name: "Grace" });
});
```

**Knex**:

```ts
import knex from "knex";
import createUser from "./create_user.sql";
import getUser from "./get_user.sql";

const db = knex({ client: "mysql2", connection: process.env.DATABASE_URL });

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

// two inserts in one transaction: both land, or neither
await db.transaction(async (trx) => {
  await createUser.run(trx, { name: "Ada" });
  await createUser.run(trx, { name: "Grace" });
});
```

#### SQLite

**better-sqlite3**:

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

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

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

// two inserts in one transaction: both land, or neither
// the function must not await, so it runs .runSync()
db.transaction(() => {
  createUser.runSync(db, { name: "Ada" });
  createUser.runSync(db, { name: "Grace" });
})();
```

**node:sqlite**:

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

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

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

// two inserts in one transaction: both land, or neither
db.exec("begin");
try {
  createUser.runSync(db, { name: "Ada" });
  createUser.runSync(db, { name: "Grace" });
  db.exec("commit");
} catch (error) {
  db.exec("rollback");
  throw error;
}
```

**bun:sqlite**:

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

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

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

// two inserts in one transaction: both land, or neither
// the function must not await, so it runs .runSync()
db.transaction(() => {
  createUser.runSync(db, { name: "Ada" });
  createUser.runSync(db, { name: "Grace" });
})();
```

`Bun.sql`:

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

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

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

// two inserts in one transaction: both land, or neither
await db.begin(async (tx) => {
  await createUser.run(tx, { name: "Ada" });
  await createUser.run(tx, { name: "Grace" });
});
```

**libSQL**:

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

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

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

// two inserts in one transaction: both land, or neither
const tx = await db.transaction("write");
try {
  await createUser.run(tx, { name: "Ada" });
  await createUser.run(tx, { name: "Grace" });
  await tx.commit();
} finally {
  tx.close();
}
```

**Turso engine**:

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

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

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

// two inserts in one transaction: both land, or neither
await db.transactionAsync(async (tx) => {
  await createUser.run(tx, { name: "Ada" });
  await createUser.run(tx, { name: "Grace" });
})();
```

**SQLite Wasm**:

```ts
import sqlite3InitModule from "@sqlite.org/sqlite-wasm";
import createUser from "./create_user.sql";
import getUser from "./get_user.sql";

const sqlite3 = await sqlite3InitModule();
const db = new sqlite3.oo1.OpfsDb("/app.db");

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

// two inserts in one transaction: both land, or neither
// the function must not await, so it runs .runSync()
db.transaction(() => {
  createUser.runSync(db, { name: "Ada" });
  createUser.runSync(db, { name: "Grace" });
});
```

**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);
  },
};

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

**Durable Objects**:

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

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

  // two inserts in one transaction: both land, or neither
  addPair() {
    const sql = this.ctx.storage.sql;
    this.ctx.storage.transactionSync(() => {
      createUser.runSync(sql, { name: "Ada" });
      createUser.runSync(sql, { name: "Grace" });
    });
  }
}
```

**sqlite3**:

```ts
import { promisify } from "node:util";
import sqlite3 from "sqlite3";
import createUser from "./create_user.sql";
import getUser from "./get_user.sql";

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

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

// two inserts in one transaction: both land, or neither
const exec = promisify(db.exec.bind(db));
await exec("begin");
try {
  await createUser.run(db, { name: "Ada" });
  await createUser.run(db, { name: "Grace" });
  await exec("commit");
} catch (error) {
  await exec("rollback");
  throw error;
}
```

**sqlite**:

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

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

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

// two inserts in one transaction: both land, or neither
await db.exec("begin");
try {
  await createUser.run(db, { name: "Ada" });
  await createUser.run(db, { name: "Grace" });
  await db.exec("commit");
} catch (error) {
  await db.exec("rollback");
  throw error;
}
```

**Drizzle**:

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

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

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

// two inserts in one transaction: both land, or neither
db.transaction((tx) => {
  createUser.runSync(tx, { name: "Ada" });
  createUser.runSync(tx, { name: "Grace" });
});
```

**Knex**:

```ts
import knex from "knex";
import createUser from "./create_user.sql";
import getUser from "./get_user.sql";

const db = knex({ client: "better-sqlite3", connection: { filename: "app.db" } });

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

// two inserts in one transaction: both land, or neither
await db.transaction(async (trx) => {
  await createUser.run(trx, { name: "Ada" });
  await createUser.run(trx, { name: "Grace" });
});
```

### `.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`
> - an `oo1` database of `@sqlite.org/sqlite-wasm`
> - the `Database` of `sql.js`
> - 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.

### `.readOnly`

To tell whether a query writes nothing, read `.readOnly`. It is `true` only where Reylo finds nothing that could write, so it's safe to run the query again after a lost connection:

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

getUser.readOnly;
//      ^? Postgres: Query<{ id: number; }, { id: number; name: string; email: string | null; }, "maybeOne", "postgres", QueryError>.readOnly: boolean
//         MySQL: Query<{ id: number; }, { id: number; name: string; email: string | null; }, "maybeOne", "mysql", QueryError>.readOnly: boolean
//         SQLite: Query<{ id: number; }, { id: number; name: string; email: string | null; }, "maybeOne", "sqlite", QueryError>.readOnly: boolean
```

A write is `false`, and so is a query that locks rows or may call a volatile function, directly or through a view, a policy or an operator. A function of your own counts by the volatility it declares.

### `.with()`

To bind the query to a client, call `.with()`:

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

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

## Observability

Trace every query Reylo runs.

### Tracing

Subscribe to Node's [tracing channel](https://nodejs.org/api/diagnostics_channel.html#class-tracingchannel) `reylo:query`, and wrap each query 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.
