Overview

sqlfu is a SQLite-first toolkit for teams that want their data layer to stay close to SQL.

It is built around a simple idea: SQL should be the source language for schema, migrations, queries, formatting, and diffing. TypeScript comes second. You should still get good generated types and wrappers, but without having to push the whole project through an ORM-shaped API.

New to sqlfu? Start with the Getting Started walkthrough.

• What Is sqlfu?
• Philosophy
  • SQL First
  • TypeScript Second
• Core Concepts
• Capabilities
  • Client
  • Migrator
  • Type Generator
  • Formatter
  • Observability
  • Outbox (experimental)
  • UI
  • Lint Plugin
  • Agent Skill
• Quick Start
• Configuration
• Command Reference
• Limitations and Non-Goals
• Prior Art and Acknowledgements

What Is sqlfu?

sqlfu is a set of SQL-first tools that are meant to work together:

• a client for executing checked-in SQL
• a migrator built around SQL files, not JavaScript migration code
• a SQLite schema diff engine
• a type generator for .sql queries
• a SQL formatter
• a UI for inspecting and working with the project

The intended shape is simple:

• your desired schema lives in definitions.sql
• your migration history lives in migrations/
• your queries live in a flat sql/ directory
• generated TypeScript wrappers live in sql/.generated/

Philosophy

SQL First

Humans have been writing SQL for decades. Agents are excellent at generating and editing it. SQL is deep “in the weights”. sqlfu tries to keep that advantage instead of hiding it behind another abstraction layer.

That is why the project leans so heavily on SQL artifacts:

• schema in definitions.sql
• migrations as SQL files
• checked-in .sql queries
• a SQL formatter
• a SQL diff engine

The goal is not to make SQL disappear. The goal is to make SQL a better source language for the rest of the toolchain.

TypeScript Second

TypeScript is the second language in sqlfu, not the first one.

You should still get strong TypeScript output from SQL: generated wrappers, typed params, typed result rows, and a client surface that feels natural in an application. That is why sqlfu includes query type generation and why it borrows from vendored TypeSQL analysis instead of asking you to rewrite queries in a TypeScript DSL.

Core Concepts

• definitions.sql The desired schema now. Tables, views, triggers, and (if you want them) copy-paste id generators (ULID, KSUID, nanoid, cuid2-shaped) live here alongside your schema. See docs/id-helpers.md.
• migrations/ The ordered history of schema changes.
• sql/ A flat directory of checked-in query files.
• generated query wrappers TypeScript code generated into sql/.generated/ as <name>.sql.ts.
• sqlfu_migrations The table that records applied migrations in a real database. Configurable via migrations.preset: set preset: 'd1' to use Cloudflare D1’s d1_migrations table instead, for projects taking over from alchemy/wrangler.
• live schema The schema the database actually has right now.

Those pieces give sqlfu enough information to answer the important questions:

• what should the schema be?
• how did it get here?
• what queries exist in the repo?
• do the repo and the database agree?

Capabilities

Client

sqlfu includes a lightweight client layer for executing SQL directly. It works with checked-in SQL rather than replacing it with a query builder.

sqlfu doesn’t ship its own database driver. Instead, sqlfu exports a thin adapter for each SQLite-compatible driver, so you can bring whichever one fits your runtime and get the same typed client surface on top. One thing sqlfu goes out of its way to preserve: sync stays sync. A client built on a synchronous driver (better-sqlite3, node:sqlite, Durable Objects) is itself synchronous — no spurious async creeping up your call stack.

That sync/async distinction carries through generated query wrappers and the migrator. Sync-backed generated functions return rows directly, and applyMigrations() runs synchronously on a SyncClient. Async-backed clients get the same API shape, but with promises where the underlying driver actually needs them.

When you need to run SQL outside a generated wrapper, the same client surface gives you client.all(...), client.run(...), client.iterate(...), and client.prepare(sql). Prepared statements are the low-level path for reusable ad-hoc SQL and named parameters without reaching through to client.driver. See Prepared statements.

See Adapters for the full driver table, copy-paste snippets, and guidance on which to pick.

Migrator

The migrator is SQL-only. Migrations are applied in filename order, recorded in sqlfu_migrations, and treated as explicit history. The production path is replayed migrations, not direct declarative apply.

The diff engine powers draft, goto, and sync by comparing replayed migration state against definitions.sql and producing the SQL statements that describe the difference. See Migration Model for the full model.

For Cloudflare D1 projects already using alchemy or wrangler, set migrations.preset: 'd1' and sqlfu reads and writes the same d1_migrations table alchemy does. See Migration Presets for the schema detection and checksum tradeoff.

Type Generator

sqlfu generate reads checked-in .sql files and generates TypeScript wrappers into a .generated/ subdirectory. The implementation uses vendored TypeSQL analysis, with a small sqlfu post-pass to improve some SQLite result types.

Note: generate reads the live database schema, so migrations must be applied first.

Use /** @name listPosts */ comments when one .sql file contains multiple queries. Parameter placeholders can also describe the runtime SQL shape directly:

/** @name insertPosts */
insert into posts (slug, title)
values :posts;

/** @name listPostsByIds */
select id, slug, title
from posts
where id in (:ids);

Scalar params stay :id; scalar lists are inferred from IN (:ids) / NOT IN (:ids); row-value lists from (slug, title) in (:keys); INSERT objects from values :posts; object fields use dot paths like :post.slug; and empty runtime-expanded arrays throw before SQLite sees the query. See Type generation.

Opt in to runtime validation by setting generate.validator to 'arktype', 'valibot', 'zod', or 'zod-mini'. Wrappers then validate params on the way in and rows on the way out, and derive types via the validator’s native inference. See Runtime validation.

Formatter

sqlfu includes a SQL formatter via formatSql(). It started from a vendored copy of sql-formatter, then diverged because upstream formatting is more newline-heavy than we want. The current sqlfu defaults are intentionally opinionated: SQLite-first, lowercase by default, and biased toward keeping simple clause bodies inline when they still read well.

Observability

Generated queries carry their identity to runtime as a name field on the emitted SqlQuery (the camelCase function name, matching the symbol you import, e.g. insertMigration). That name reaches OpenTelemetry spans, Sentry errors, PostHog events, and Datadog metrics through a single instrument() call:

import {instrument} from 'sqlfu';

const client = instrument(baseClient,
  instrument.otel({tracer}),
  instrument.onError(({context, error}) => Sentry.captureException(error, {
    tags: {'db.query.summary': context.query.name || 'sql'},
  })),
);

No peer dependencies on OpenTelemetry or Sentry. TracerLike is structural; hook consumers bring their own SDK. Copy-pasteable recipes live in Observability.

Typed errors

Every adapter throws SqlfuError with a normalized .kind discriminator ('unique_violation', 'missing_table', 'syntax', 'transient', and so on), so application code branches on the outcome instead of string-matching the driver’s message.

import {SqlfuError} from 'sqlfu';

try {
  await client.run(createUser);
} catch (error) {
  if (error instanceof SqlfuError && error.kind === 'unique_violation') {
    return response.status(409).json({error: 'email already taken'});
  }
  throw error;
}

The driver error is preserved byte-identical on .cause; .query and .system come along so error reporters can tag events without a parallel QueryExecutionContext. Kind names are SQLSTATE-aligned, so the day a postgres adapter lands the mapping is a direct lookup rather than a second vocabulary to remember. Full kind list, handler recipes, Sentry-tagging example: Errors.

Outbox (experimental)

⚠️ The shape of this module is still in flux. The basic principle of events + consumers will stay, but expect breaking changes between releases.

A small transactional-outbox / job-queue sits at sqlfu/outbox. Emit events in the same transaction as your domain writes; register consumers with retry, delay, when filter, and visibility timeout; drive a worker loop by calling tick() on a timer. Fan-out, crash recovery, and causation chains all work the way you’d expect, built on the fact that SQLite serialises writers so the queue doesn’t need row-locks. See Outbox.

UI

sqlfu also has a UI package for working with the project interactively. To use it with your DB, run: npx sqlfu. This will start a server on your machine, and print a link to the hosted UI at sqlfu.dev/ui. The hosted UI talk to the backend running on your dev machine.

The same UI can be embedded in a fetch server with @sqlfu/ui when you want your own auth, route prefix, or Worker/Durable Object database binding. See UI.

Lint Plugin

sqlfu ships a lint plugin as a sub-export (sqlfu/lint-plugin). It runs under ESLint (flat config) on both TS/JS source (inline SQL templates) and standalone .sql files (via an ESLint processor):

• sqlfu/query-naming — flags inline SQL that duplicates a checked-in .sql file. Your filename is your query’s identity; an inline duplicate loses the name, generated types, and observability metadata.
• sqlfu/format-sql — flags SQL that does not match sqlfu’s formatter output. eslint --fix '**/*.sql' reformats files in place.

See Lint Plugin for setup and configuration.

Agent Skill

sqlfu ships an agent skill at skills/using-sqlfu. It gives an agent the few sqlfu-specific facts it needs before editing a project: find sqlfu.config.ts, treat SQL as the authored source, draft migrations instead of inventing them, and regenerate TypeScript outputs from query files.

Install it into a project:

npx skills@latest add mmkal/sqlfu/skills/using-sqlfu

The skill is self-contained: it does not depend on the sqlfu package itself, and the SKILL.md format is agent-agnostic.

Quick Start

pnpm add sqlfu

For a full end-to-end walkthrough — schema, migrations, query files, typed wrappers, and a working generated-function call — see Getting Started.

Configuration

Create sqlfu.config.ts in your project root:

export default {
  db: './db/app.sqlite',
  migrations: './migrations',
  definitions: './definitions.sql',
  queries: './sql',
};

Required fields:

• definitions — schema source of truth (definitions.sql)
• queries — directory containing checked-in .sql queries

Optional fields:

• db — the database sqlfu talks to for migrate, check, sync, goto, baseline, and the UI. Either a filesystem path (opens a local sqlite file) or a factory returning a DisposableAsyncClient. See Pluggable db. Can be omitted when you only use sqlfu generate and the default generate.authority.
• migrations — directory containing migration files. Omit if you don’t use migrations (library-author projects).
• generate.authority — where sqlfu generate reads the schema from. See generate.authority. Default 'desired_schema'.

sqlfu manages its own temporary files under .sqlfu/, including scratch databases used for schema diffing. These are generally safe to delete at any time.

If a repo has more than one sqlfu project, pass the config file explicitly:

sqlfu --config ./durable-objects/counter/sqlfu.config.ts generate
sqlfu --config ./durable-objects/session/sqlfu.config.ts draft

Relative paths inside that config are resolved from the config file’s directory, so each Durable Object can keep its own definitions.sql, migrations/, and sql/ directories alongside the config.

Pluggable db

When your app talks to an adapter-mediated database (Cloudflare D1, Turso, libsql, a miniflare binding), point sqlfu at the same client your app uses by giving db a factory instead of a path. Every sqlfu command that touches the DB — migrate, check, sync, goto, baseline, generate, the UI — will then operate on the real database, not a scratch file.

import {defineConfig, createD1Client} from 'sqlfu';
import {Miniflare} from 'miniflare';

export default defineConfig({
  db: async () => {
    const mf = new Miniflare({
      script: '', modules: true,
      d1Persist: true,
      d1Databases: {DB: '<dev-db-id>'},
    });
    await mf.ready;
    const d1 = await mf.getD1Database('DB');
    return {
      client: createD1Client(d1),
      async [Symbol.asyncDispose]() { await mf.dispose(); },
    };
  },
  migrations: './migrations',
  definitions: './definitions.sql',
  queries: './sql',
});

The factory is invoked on every openDb call; sqlfu calls [Symbol.asyncDispose] when the command scope exits. Memoize inside the factory if the setup is expensive (e.g. spinning up miniflare once per process).

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:

• 'desired_schema' (default) — read definitions.sql directly. No DB required. Fastest, most deterministic. Drift between definitions.sql and migrations is surfaced by sqlfu check, not silently hidden here.
• 'migrations' — replay migrations/*.sql into a scratch DB and extract the resulting schema. No DB required. Types follow what the migrator would actually produce.
• 'migration_history' — read sqlfu_migrations from config.db, then replay the matching migration files. Requires db. Throws if a recorded migration is missing from migrations/. Use when types should match what’s actually deployed.
• 'live_schema' — extract schema directly from config.db. Requires db to be populated up-front. This was the default before the factory form of db landed; now opt-in.

export default defineConfig({
  // db optional for desired_schema / migrations authority
  definitions: './definitions.sql',
  queries: './sql',
  migrations: './migrations',
  generate: {authority: 'migrations'},
});

Command Reference

Generate query wrappers:

sqlfu generate

By default generate reads from definitions.sql (no DB needed). Switch generate.authority if you want types to reflect the live schema or the applied migration history — see generate.authority.

Draft a migration:

sqlfu draft

Apply migrations:

sqlfu migrate

Stop the local backend process on the default port:

sqlfu kill

Move the database and migration history to an exact target:

sqlfu goto <target>

Rewrite migration history to an exact target without changing live schema:

sqlfu baseline <target>

Update live schema directly from definitions.sql:

sqlfu sync

Check the important repo/database mismatches:

sqlfu check

Limitations and Non-Goals

sqlfu deliberately leaves out a few common migration features:

• no repeatable migrations
• no down migrations
• no JavaScript migrations

Those are not accidents. The project is trying to keep schema history explicit, SQL-authored, and easy to inspect.

Current limits also matter:

• sqlfu is SQLite-first in important parts of the toolchain
• result-type inference is imperfect on some SQLite expressions and views; the sqlfu post-pass that fills gaps in the vendored TypeSQL output is still evolving
• the formatter is opinionated and still evolving

Prior Art and Acknowledgements

sqlfu is not built in a vacuum. Several existing projects directly shape what it looks like today, either as vendored code or as ideas we lean on.

• TypeSQL by Wanderson Camargo (MIT). TypeSQL is vendored under src/vendor/typesql and powers SQL-to-TypeScript analysis for sqlfu generate. sqlfu adds a small post-pass for SQLite result typing but otherwise relies on TypeSQL’s query analyzer, its ANTLR4-based parser (typesql-parser, vendored under src/vendor/typesql-parser), and its code generator.
• sql-formatter (MIT). The formatter is essentially vendored whole under src/vendor/sql-formatter and then wrapped by src/formatter.ts with sqlfu-specific defaults (SQLite-first, lowercase by default, biased toward keeping simple clause bodies inline).
• prettier-plugin-sql-cst by Rene Saarsoo (MIT). The target output shape for formatSql() draws on this project’s style, and a large set of its upstream tests are imported into sqlfu’s formatter fixtures under test/formatter/generated-prettier-plugin-sql-cst-*.fixture.sql.
• antlr4 JavaScript runtime (BSD-3-Clause). Vendored under src/vendor/antlr4 so TypeSQL’s parser can run without loading from node_modules.
• code-block-writer by David Sherret (MIT). Vendored under src/vendor/code-block-writer and used by TypeSQL’s code generator.
• Drizzle. The local.drizzle.studio product model — hosted UI shell talking to a local backend via a permissioned localhost API — is the direct inspiration for sqlfu.dev/ui and the shape of the sqlfu UI package. More generally, Drizzle raised the bar for what modern SQL-oriented tooling should feel like, and sqlfu is trying to meet that bar for a different slice of the workflow.
• @pgkit/schemainspect and @pgkit/migra. The sqlfu schemadiff engine under src/schemadiff is structurally inspired by these libraries: materialize both schemas into scratch databases, inspect them into a typed model, diff the inspected models, and emit an ordered statement plan. The SQLite-specific implementation does not copy their code, but the shape is taken from them. See src/schemadiff/CLAUDE.md for more detail.
• djrobstep/schemainspect and djrobstep/migra by Robert Lechte. These are the Python originals that the @pgkit/* packages ported to TypeScript, and therefore the upstream lineage of the sqlfu diff engine.
• pgkit (same author). pgkit is sqlfu’s Postgres-focused prior art. A lot of the mental model for sqlfu — “SQL as the authored source, generated types next to queries, schema-diff-driven migrations, a web UI that sits on the real client” — comes from trying that approach in pgkit first. sqlfu is the SQLite-first version of that idea, with the goal of eventually growing back to Postgres.

Vendored directories each carry a short CLAUDE.md that pins the upstream commit or version and lists the local modifications, so future updates from upstream can be applied intelligently.