Skip to main content

Documentation Index

Fetch the complete documentation index at: https://mintlify.com/iterate/sqlfu/llms.txt

Use this file to discover all available pages before exploring further.

sqlfu generate reads checked-in .sql files and emits TypeScript wrappers into sql/.generated/. The generated function name comes from the file path: sql/get-post.sql becomes getPost. You write the data-access layer in plain SQL, then call it from TypeScript with inferred params and result rows — no query builder, no DSL, no rewriting SQL into another syntax.
sql/queries.sql
/** @name getPost */
select id, slug, title
from posts
where id = :id;
app/post.ts
import {getPost} from './sql/.generated/queries.sql.ts';

const post = await getPost(client, {id: 123});

​
File path to function name

The generated function name is derived from the file path. sql/get-post.sql becomes getPost. When one file contains multiple queries, use @name block comments to assign a name to each query.

​
Multiple queries in one file

Put @name in a block comment before each query when one .sql file contains more than one query.
sql/queries.sql
/** @name listPosts */
select id, slug, title
from posts
order by id;

/** @name findPostBySlug */
select id, slug, title
from posts
where slug = :slug;
This emits one generated module, sql/.generated/queries.sql.ts, with both listPosts and findPostBySlug exports. If a file uses @name, every executable statement in that file must have its own @name.

​
Parameter forms

sqlfu infers the TypeScript parameter type from the SQL shape. The placeholder syntax you use in .sql files directly determines how runtime arguments are structured and how the SQL is expanded before execution.

​
Plain scalar params (:id)

The basic form. One placeholder maps to one scalar argument.
sql/queries.sql
/** @name getPost */
select id, slug, title
from posts
where id = :id;

await getPost(client, {id: 123});

​
Scalar lists (IN (:ids))

Use IN (:ids) or NOT IN (:ids) when one param should expand into a comma-separated placeholder list. TypeSQL infers the array type from the IN operator.
sql/queries.sql
/** @name listPostsByIds */
select id, slug, title
from posts
where id in (:ids)
order by id;

await listPostsByIds(client, {ids: [1, 2, 3]});
At runtime sqlfu executes where id in (?, ?, ?) with [1, 2, 3]. Empty arrays throw before the query reaches SQLite.

​
Dot paths (:post.slug)

Use dot paths when a query naturally accepts one object. One path segment is supported; nested paths such as :post.author.id are intentionally rejected until the type shape is designed.
sql/queries.sql
/** @name insertPost */
insert into posts (slug, title)
values (:post.slug, :post.title)
returning id, slug, title;

await insertPost(client, {
  post: {
    slug: 'hello-world',
    title: 'Hello world',
  },
});
The generated params type is {post: {slug: string; title: string}}.

​
INSERT object shorthand (values :posts)

Use an object param directly after values when an INSERT column list already names the object fields. The generated param accepts either one object or a non-empty array.
sql/queries.sql
/** @name insertPosts */
insert into posts (slug, title)
values :posts;

// Single row
await insertPosts(client, {
  posts: {slug: 'first', title: 'First'},
});

// Multiple rows
await insertPosts(client, {
  posts: [
    {slug: 'second', title: 'Second'},
    {slug: 'third', title: 'Third'},
  ],
});
At runtime sqlfu executes values (?, ?) for one object or values (?, ?), (?, ?) for an array, flattening values in INSERT column-list order. Empty arrays throw.
The INSERT object shorthand does not support RETURNING yet. Use explicit dot-path values such as values (:post.slug, :post.title) for returning single-row inserts.

​
Row-value IN lists ((slug, title) in (:keys))

Row-value IN lists infer object-array params from the left-hand column tuple.
sql/queries.sql
/** @name listPostsByKeys */
select id, slug, title
from posts
where (slug, title) in (:keys)
order by id;

await listPostsByKeys(client, {
  keys: [
    {slug: 'first', title: 'First'},
    {slug: 'third', title: 'Third'},
  ],
});

​
generate.authority

sqlfu generate needs to know your schema to produce typed query wrappers. The generate.authority option controls where it reads the schema from:
ValueSourceDB required
'desired_schema' (default)Reads definitions.sql directlyNo
'migrations'Replays migrations/*.sql into a scratch DBNo
'migration_history'Reads sqlfu_migrations from config.db, replays matching migrationsYes
'live_schema'Extracts schema directly from config.dbYes
sqlfu.config.ts
export default defineConfig({
  definitions: './definitions.sql',
  queries: './sql',
  migrations: './migrations',
  generate: {authority: 'migrations'},
});
Use 'desired_schema' (the default) for the fastest, most deterministic output. Use 'migration_history' when generated types should match what is actually deployed. Drift between definitions.sql and migrations is surfaced by sqlfu check, not silently hidden by the generator.

​
Experimental JSON logical types

Set generate.experimentalJsonTypes: true to opt into experimental JSON logical-type handling. Columns declared with the SQLite type name json are generated as unknown by default. For a narrower TypeScript type, add a sqlfu_types metadata view to your schema. Each row maps a logical declared type name to an encoding, a definition format, and a type definition:
definitions.sql
create view sqlfu_types (name, encoding, format, definition) as
values
  (
    'slack_payload',
    'json',
    'typescript',
    '{
      action: "message" | "reaction";
      content: string
    }'
  );

create table slack_webhooks(
  id integer primary key,
  payload slack_payload not null
);

await recordSlackWebhook(client, {
  payload: {
    action: 'message',
    content: 'hello',
  },
});
The generated wrapper accepts the TypeScript definition, serializes inputs as JSON text, and parses selected result columns before returning them.
The definition value is not a validator schema and sqlfu does not resolve imports, aliases, or references from it. The first experimental slice supports only encoding: 'json' and format: 'typescript'.

​
Effect SQL runtime

If your app uses Effect, set generate.runtime: 'effect-v3' to emit functions that return Effect values and require Effect SQL’s SqlClient.SqlClient from the Effect environment: 
const post = yield* getPost({id: 123});
See the Effect SQL runtime documentation for the experimental native Effect SQL generation modes, including the Effect v4 beta 'effect-v4-unstable' target.

​
Limits

  • Runtime-expanded params (inferred scalar IN lists, row-value IN lists, and INSERT values :param objects) can appear only once in a query. Reusing the same expanded array in two places would require duplicating driver arguments, so sqlfu rejects that shape.
  • generate.experimentalJsonTypes must be opted into explicitly. JSON logical-type handling — including columns declared as json and matching rows in sqlfu_types — is not active by default.
  • sqlfu_types.definition values only describe the generated TypeScript surface. They do not add runtime validation for JSON payload shape.
  • Parameter shape is inferred from SQL shape, not comment metadata. @name names queries; IN (:ids), (slug, title) in (:keys), and values :posts describe runtime placeholder expansion where the SQL shape changes.
  • One dot-path segment is supported (:post.slug). Nested paths such as :post.author.id are rejected.

Runtime validation

Validate params on input and rows on output with arktype, valibot, zod, or zod-mini.

Observability

Route query names to OpenTelemetry spans, Sentry errors, and Datadog metrics.

Adapters

Bring your own SQLite driver: better-sqlite3, node:sqlite, Cloudflare D1, and more.

Lint plugin

Keep generated wrappers fresh and enforce SQL-first conventions in ESLint.

Build docs developers (and LLMs) love

Get started for freeTalk to us