sqlfu

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.

i know sqlfu

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 next to those queries

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.
migrations/ The ordered history of schema changes.
sql/ A flat directory of checked-in query files.
generated query wrappers TypeScript code generated next to those .sql files.
sqlfu_migrations The table that records applied migrations in a real database.
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 is meant to work with checked-in SQL rather than replace it with a query builder.

Runtime adapters can be imported from sqlfu/client, for example createExpoSqliteClient, createLibsqlClient, or createD1Client.

Migrator

The migrator is SQL-only. Migrations are applied in filename order, recorded in sqlfu_migrations, and treated as explicit history rather than generated runtime code.

The production path is replayed migrations, not direct declarative apply.

Diff Engine

sqlfu includes a native SQLite schema diff engine. It compares replayed migration state against definitions.sql and produces SQL statements that describe the difference.

That diff engine powers commands like draft, goto, and sync.

For the engine model itself, see docs/schema-diff-model.md.

Type Generator

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

The goal is to keep SQL as the authored source while still getting useful TypeScript output in application code.

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.

If you want to see or change that behavior, start here:

UI

sqlfu also has a UI package for working with the project interactively. It sits on top of the same SQL-first model rather than inventing a separate one.

Quick Start

Install

pnpm add sqlfu

sqlfu currently supports macOS and Linux.

Minimal Setup

The default layout is:

.
├── definitions.sql
├── migrations/
│   └── 20260326120000_add_posts_table.sql
├── sql/
│   ├── some-query.sql
│   └── some-query.ts
└── sqlfu.config.ts

Configuration

Create sqlfu.config.ts in your project root:

import {defineConfig} from 'sqlfu';

export default defineConfig({
  db: './db/app.sqlite',
  migrationsDir: './migrations',
  definitionsPath: './definitions.sql',
  sqlDir: './sql',
});

Required config fields:

db: path to the main dev database used by tooling commands like sync, migrate, and generate
migrationsDir: directory containing migration files
definitionsPath: schema source of truth
sqlDir: directory containing checked-in .sql queries

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

Generate Types

Generate query wrappers from your schema and .sql files:

sqlfu generate

sqlfu generate:

exports the schema from your configured main database into a temporary SQLite database for TypeSQL
generates TypeScript wrappers next to those .sql files
refines generated result types for some SQLite cases that TypeSQL currently misses

Draft and Apply Migrations

Draft a migration from the diff between replayed migrations and definitions.sql:

sqlfu draft --name add_posts_table

Apply migrations:

sqlfu migrate

Run all migration checks:

sqlfu check

When you draft a migration, sqlfu will:

replay migrations into a temporary SQLite database
diff that replayed state against definitions.sql
create a new migration file in migrations/

You should still review and edit the generated migration, especially for renames, data backfills, and destructive changes.

There is no committed snapshot.sql file. If you want the guarantee a snapshot would normally provide, run sqlfu check, which verifies that replayed migrations still reproduce definitions.sql.

For the migration model in more detail, see docs/migration-model.md.

Launch the UI

For end users, start the local backend from your sqlfu project root:

npx sqlfu

That starts the UI backend on localhost:56081. The intended browser origin is local.sqlfu.dev, which should resolve to that local server.

If you ever need to avoid a local port conflict, sqlfu serve --port <port> overrides the default. Most users should never need this.

If the default port is already occupied, sqlfu kill stops the existing listener on 56081.

While developing the UI itself inside this repo, the client bundle still lives in packages/ui:

pnpm --filter sqlfu-ui dev

Command Reference

Generate query wrappers:

sqlfu generate

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
SQLite view typing is still imperfect in TypeSQL
some expressions still need the sqlfu post-pass to get better generated result types
the formatter is opinionated and still evolving