Readiness: Implemented
Build an app
SQL with sqlc
uf has no ORM. The server half of a uf application is a backend for its own
frontend, and when it reads a database it reads it in SQL. sqlc
parses that SQL against your schema and infers every parameter and column. uf is
sqlc's Flow target: it turns what sqlc inferred into Flow row types and one
function per query, and @uniflowed/sql runs them on the driver you already use.
Status: ready for the tested adapters. Generation and the adapters marked
tested below run against real SQLite, PostgreSQL and MySQL in uf's CI, and
@uniflowed/sql is available on npm. better-sqlite3 uses its tested prebuilt addon; D1 runs in workerd in CI.
D1 supports its own atomic batch() API and has no interactive transactions.
Point sqlc at uf, generate Flow from your
queries, run them on SQLite, PostgreSQL or MySQL, use transactions, and fail CI
when the generated files are stale.
You need a project (Your first project).
uf sqlc install downloads sqlc 1.31.1, the release uf's own tests use, into
.uf/sqlc and checks the archive. A sqlc already on your PATH, or named by
$SQLC, is used instead of that copy.
Point sqlc at uf
Your project keeps an ordinary sqlc config. The only uf part is the plugin:
sqlc starts uf as a process plugin
and hands it what it inferred.
# sqlc.yaml
version: "2"
plugins:
- name: flow
process:
cmd: uf
sql:
- engine: postgresql
schema: db/schema.sql
queries: db/query.sql
codegen:
- out: src/db
plugin: flow
The engine can be postgresql, mysql or sqlite. The schema and queries are
sqlc's as they are, and sqlc's own documentation covers them. Every sqlc flag
and every sqlc upgrade reaches your project unchanged, and if you leave uf you
keep your SQL.
Generate
uf sqlc install
uf sqlc generate
uf sqlc install downloads sqlc 1.31.1 into .uf/sqlc, checks the archive
against the SHA-256 uf was built with for this platform, and prints the
binary's absolute path. A later install of the same version prints the path
and does not download. SQLC=$(uf sqlc install) is how a script takes that
path. --version 1.30.0 --sha256 <hex> installs a different release; the
digest is required, because uf only knows the pinned one. --force replaces
a copy that is already there.
uf sqlc generate runs sqlc generate with the running uf first on PATH,
so the plugin sqlc starts is the same uf you asked. It looks for sqlc as
$SQLC (even when that path is missing), then as sqlc on PATH, then as
.uf/sqlc/sqlc when the version file there is 1.31.1. It does not download
sqlc itself. A version you installed with --version is selected by exporting
the path install printed. Pass -f other.yaml for a config that is not
sqlc.yaml, sqlc.yml or sqlc.json in the project root.
In CI, leave SQLC unset and run uf sqlc install before uf sqlc diff.
tools/ci/sqlc.sh does that. .uf/ is gitignored, so the binary is not
committed.
For this query file
-- name: GetAuthor :one
SELECT * FROM authors WHERE id = $1 LIMIT 1;
-- name: ListAuthors :many
SELECT * FROM authors ORDER BY name;
-- name: CreateAuthor :one
INSERT INTO authors (name, bio) VALUES ($1, $2) RETURNING *;
against CREATE TABLE authors (id BIGSERIAL PRIMARY KEY, name text NOT NULL, bio text),
src/db/models.js gets the row type
export type Author = {|
readonly id : bigint,
readonly name: string,
readonly bio : string | null,
|};
and src/db/query.sql.js gets one function per query. Each one takes the
database first and an arguments object second, which is left out when the
query has no parameters:
export function getAuthor(db: Queryable, args: GetAuthorArgs): Promise<Author | null> {
// …
}
export function listAuthors(db: Queryable): Promise<Array<Author>> {
// …
}
The files are signed as @generated. uf fmt leaves them alone, and the
generator already prints them the way uf fmt would, so they pass uf check,
uf lint and uf fmt --check as generated. Edit the SQL and run
uf sqlc generate again rather than editing them.
Run the queries
Wrap your driver in an adapter and pass it as the first argument:
import pg from "pg";
import { fromPgPool } from "@uniflowed/sql/pg";
import { createAuthor, getAuthor, listAuthors } from "./db/query.sql.js";
const db = fromPgPool(new pg.Pool({ connectionString: process.env.DATABASE_URL }));
const author = await createAuthor(db, { name: "Brian Kernighan", bio: null });
const same = author === null ? null : await getAuthor(db, { id: author.id });
const everyone = await listAuthors(db);
| Engine | Adapter | Import | Status |
|---|---|---|---|
| SQLite | node:sqlite (Node 22.16 or 24 and newer) | fromNodeSqlite from @uniflowed/sql/node-sqlite | tested |
| SQLite | bun:sqlite | fromBunSqlite from @uniflowed/sql/bun-sqlite | tested |
| SQLite | better-sqlite3 | fromBetterSqlite3 from @uniflowed/sql/better-sqlite3 | Tested with its prebuilt addon |
| SQLite | Cloudflare D1 | fromD1 from @uniflowed/sql/d1 | Tested in workerd; no interactive transactions |
| PostgreSQL | PGlite | fromPGlite from @uniflowed/sql/pglite | tested |
| PostgreSQL | pg | fromPgClient, fromPgPool from @uniflowed/sql/pg | tested |
| PostgreSQL | postgres (postgres.js) | fromPostgres from @uniflowed/sql/postgres | tested |
| MySQL | mysql2 | fromMysql2Pool from @uniflowed/sql/mysql2 | tested, against MySQL 8.4 |
Tested means uf's CI runs sqlc's own examples and uf's type cases through that adapter against a real database, and checks every value that comes back.
The adapter does not decide the types. Drivers disagree about them: pg hands
back int8 as a string, postgres.js parses date into a Date at local
midnight, and a SQLite driver may round an integer past 2^53. So every adapter
returns what the database sent, as text or as SQLite's own storage classes, and
the generated code decodes each column by its SQL type. An int8 is a bigint
under every PostgreSQL driver, and a type that is wrong is wrong in one place.
What the types are
| SQL | Flow |
|---|---|
smallint, integer, MySQL int | number |
bigint | bigint |
SQLite INTEGER | number, which throws rather than rounds past 2^53 |
numeric, decimal, money | string, the exact digits |
real, double precision | number |
boolean, MySQL tinyint(1) | boolean |
text types, uuid, inet, interval, time | string |
bytea, blob | Uint8Array |
date | string, YYYY-MM-DD: a calendar date has no time zone |
timestamp without a time zone, MySQL datetime | string, as the server printed it |
timestamptz | Date |
json, jsonb | JsonValue |
| an enum | a union of its labels, plus a $ReadOnlyArray of them |
| a PostgreSQL array | $ReadOnlyArray<T>, one level per dimension |
A column that can be NULL is T | null, never ?T, because no driver returns
undefined. When sqlc cannot tell a column's type, as with max(created), the
column is mixed. Add a cast (max(created)::timestamptz) and it gets a real
one. The design record
has the full table and the reason for each row.
Query annotations
:oneresolves to the row ornull,:manyto anArrayof rows, and:execto nothing.:execrowsresolves to the number of rows changed,:execresultto{ rowsAffected, lastInsertId }, and:execlastidto the last id as abigint.:copyfromtakes an array of argument objects. PostgreSQL adapters send oneCOPY … FROM STDINin text format. MySQL, SQLite and the synchronous path insert with multi-rowINSERTs, split under the driver's parameter limit, in one transaction when the adapter can open one. ACOPYthe server rejects is that error; it is not retried asINSERT.:batchexec,:batchmanyand:batchonetake an array of argument objects and resolve to one result per item, in order, in one transaction when the adapter can open one.sqlc.arg(name)and@namename an argument, andsqlc.narg(name)makes itT | null.sqlc.slice(name)takes a$ReadOnlyArray<T>and expands toIN (…)on MySQL and SQLite.sqlc.embed(table)nests that table's row type under its name.
Transactions
A database or pool adapter has a transaction method. The function you pass it
gets a transaction to run queries on. The transaction commits when the function
resolves and rolls back when it throws, and it rethrows what the function threw:
const transaction = db.transaction;
if (transaction !== undefined) {
await transaction(async (tx) => {
const author = await createAuthor(tx, { name: "Anonymous", bio: null });
if (author === null) {
throw new Error("no row came back");
}
});
}
transaction is optional in the type because D1 cannot hold one open. Calling
tx.transaction inside the function opens a savepoint, which rolls back only
its own work. A transaction refuses queries once it has ended, so a leaked tx
fails loudly instead of running outside the transaction.
Synchronous SQLite
node:sqlite, better-sqlite3 and bun:sqlite run a statement on the calling
thread. A transaction there has to stay on that thread from BEGIN through
COMMIT, so the generated functions have to return their value directly
instead of a Promise. Set sync: true on the plugin:
codegen:
- out: src/db
plugin: flow
options:
sync: true
getAuthor then takes a SyncQueryable and returns Author | null. The three
SQLite adapters above return a handle that has both query and querySync,
and both transaction and transactionSync:
import { DatabaseSync } from "node:sqlite";
import { fromNodeSqlite } from "@uniflowed/sql/node-sqlite";
import { getAuthor } from "./db/query.sql.js";
const db = fromNodeSqlite(new DatabaseSync("app.db"));
const author = getAuthor(db, { id: 1 });
const transaction = db.transactionSync;
if (transaction !== undefined) {
transaction((tx) => {
getAuthor(tx, { id: 1 });
});
}
transactionSync commits when the function returns and rolls back when it
throws. Pass tx into the generated functions. Calling db.querySync on the
outer handle while that transaction is open throws: the body would be waiting
for itself. :copyfrom and :batch* are generated for this path too, and run
on the same thread. D1, PostgreSQL and MySQL stay asynchronous; they have no
querySync, and sync: true code does not typecheck against them.
Options
Plugin options go under the codegen entry:
codegen:
- out: src/db
plugin: flow
options:
int8: bigint # bigint | number | string
sqliteInteger: number # number | bigint
numeric: string # string | number
timestamptz: Date # Date | string
naming: camelCase # camelCase | preserve
sync: false # true returns values directly, for synchronous SQLite
rename:
spotify_url: spotifyURL
overrides:
- column: authors.settings
type: { import: "./src/settings.js", name: "Settings", decode: "parseSettings" }
An override that changes a type names a decode function, (value: T) => U
from the default type, because the generator does not write any. When the
column is also a parameter, name an encode function too, unless your type
already is the default one. An unknown option is an error, not something the
generator ignores.
Keep the generated files current in CI
uf sqlc diff
This runs sqlc diff. It exits non-zero when generating again would change a
file, and says to run uf sqlc generate.
Using the WASM plugin
Each GitHub Release includes sqlc-gen-flow.wasm, its .sha256 checksum and
its .sigstore signing bundle. sqlc 1.31.1 can use that artifact instead of
starting uf as a process plugin. Download the checksum from the same release
and use its 64-character digest below:
plugins:
- name: flow
wasm:
url: https://github.com/ubugeeei-prod/uf/releases/download/uf%400.16.0/sqlc-gen-flow.wasm
sha256: <the digest in sqlc-gen-flow.wasm.sha256>
Keep the existing codegen entry naming the flow plugin. cmd: uf remains
supported. The release smoke test verifies the signature, downloads the
published bytes and runs a generated SQLite query with the pinned sqlc.
To rehearse it locally, add the Rust wasm32-wasip1 target, run
uf run test:sqlc, then uf run test:sqlc:wasm.
What is not here
These are deliberately missing: migrations (sqlc reads a schema, and applying it is your migration tool's job), a connection pool, retries, and a query builder. The pool and retries belong to the driver you already chose.
Where to go next
GraphQL and Relay is the other way a uf server reaches data it does not own. Server actions are where most generated queries end up being called.
Edit this pagedocs/app/guide/sqlc/$page.mdx