sqlgen
The sqlgen package turns SQL queries into typed Tern functions, like Go's sqlc. You write each query in plain SQL under a -- name: line. sqlgen asks PostgreSQL or SQLite for the types of its parameters and columns, and writes a module with a struct per result and a function per query. A misspelled column, a missing parameter, or a nullable column read as if it couldn't be NULL then fails when you generate or compile, not when the query runs in production.
It is a package: the tern sqlgen command runs it (or tern add sqlgen to use it from code). It pairs with migrate: point it at your migrations and it checks the queries against a scratch database built from them.
Queries
-- name: get_user :one
-- One user by id.
SELECT id, name, email FROM users WHERE id = @id;
-- name: list_users :many
SELECT id, name FROM users WHERE active = @active ORDER BY id LIMIT @max;
-- name: rename_user :exec
UPDATE users SET name = @name WHERE id = @id;| After the name | Generated function returns (PostgreSQL) | (SQLite) |
|---|---|---|
:one | Result[Option[Row], str]: the first row, or None | Option[Row] |
:many | Result[[Row], str] | [Row] |
:exec | Result[int, str]: the rows changed | int |
- Comment lines right under the
-- name:line become the function's comment. - Parameters are
@name; using a name twice passes one value. Numbered parameters work too ($1in PostgreSQL,?1or?in SQLite) and are calledarg1,arg2, ... - A query that returns one column gives that column's values (
Option[int],[str]) instead of a one-field struct. - A query that returns every column of one table, in the table's order (
SELECT * FROM users), uses that table's struct,User, and every such query shares it. Others get a struct of their own, named after the query:GetUserRow. - The function's first parameter is the database:
pool: pg.Poolorconn: sqlite.Db. SQLite functions return plain values becausesqliteerrors are runtime errors.
Types and NULL
| PostgreSQL | SQLite (declared type) | Tern |
|---|---|---|
int2, int4, int8, oid | contains INT | int |
float4, float8, numeric | REAL, FLOAT, DOUBLE, NUMERIC, DECIMAL | float |
bool | contains BOOL | bool |
timestamptz, timestamp | float: seconds since 1970, UTC, like time.now() (parameters too) | |
an enum type (CREATE TYPE mood AS ENUM ...) | a Tern enum sqlgen writes for it (parameters too) | |
int8[], text[], ... (arrays of the types above) | [int], [str], ... (parameters too) | |
everything else: text, date, uuid, json, enums | everything else (SQLite timestamps too) | str (as the database writes it) |
A column is an Option unless it is a table column declared NOT NULL. In a query with a LEFT, RIGHT, or FULL join, every column is an Option, because the join can fill it with NULL. When you know better, say so in the column's name, in double quotes:
| Name | Means |
|---|---|
count(*) AS "total!" | never NULL: a plain int |
u.email AS "email?" | may be NULL: an Option |
count(*) AS "n!: int" | the type too (SQLite, where an expression has no declared type and is read as str) |
The notes are removed from the SQL that runs, so the column is called total, email, n.
Enums
A PostgreSQL enum type becomes a Tern enum in the generated module, with a function giving each variant's label:
CREATE TYPE order_status AS ENUM ('pending', 'in_progress', 'shipped');
# the PostgreSQL enum order_status
enum OrderStatus:
Pending
InProgress
Shipped
# the label PostgreSQL knows a OrderStatus by
fn order_status_label(v: OrderStatus) strColumns of the type read into it (Option[OrderStatus] when they can be NULL), and parameters of the type take it: orders_with(pool, orders_db.OrderStatus.Shipped). A match on it must cover every variant, so adding a label and regenerating shows every place that needs to handle it. Labels become variant names by capitalizing their words (in_progress, in progress and in-progress are all InProgress); a label with other characters (a.b) or starting with a digit is an error that names it.
Your own enums work in rows too, without sqlgen: pg.fetch and sqlite.query_into fill an enum field from a text column, matching the text to a variant name the same way (so a TEXT column holding shipped fills OrderStatus.Shipped).
PostgreSQL knows its parameters' types. SQLite doesn't, so sqlgen reads them from where each one is used: compared with a column (id = @id), inserted into one (INSERT INTO posts (user_id) VALUES (@user_id)), or after LIMIT / OFFSET. Anything else is a str; write @min:float to say otherwise.
A parameter that may be NULL ends in ?: UPDATE users SET email = @email? WHERE id = @id makes email an Option[str], and None sends NULL (through pg.NULL / sqlite.NULL).
What it writes
import sqlite
import fs
import sqlgen
fn main() Result[int, str]:
dir = fs.temp_dir("sqlgen_doc")
d = sqlite.open(dir + "/app.db")
d.run_script("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT, active BOOLEAN NOT NULL DEFAULT 1)")?
fs.write(dir + "/queries.sql", "-- name: get_user :one\nSELECT id, name, email FROM users WHERE id = @id;\n\n-- name: count_active :one\nSELECT count(*) AS \"n!: int\" FROM users WHERE active = @active;\n\n-- name: rename_user :exec\nUPDATE users SET name = @name WHERE id = @id;\n")
code = sqlgen.generate(sqlgen.sqlite(d), [dir + "/queries.sql"])?
print(code)
fs.remove_all(dir)
return Ok(0)# Code generated by sqlgen from queries.sql. DO NOT EDIT.
import sqlite
struct GetUserRow:
id: int
name: str
email: Option[str]
fn get_user(conn: sqlite.Db, id: int) Option[GetUserRow]:
_rows: [GetUserRow] = []
conn.query_into("SELECT id, name, email FROM users WHERE id = ?1", [str(id)], &_rows)
if len(_rows) == 0:
return None
return Some(_rows[0])
struct _CountActiveRow:
n: int
fn count_active(conn: sqlite.Db, active: bool) Option[int]:
_rows: [_CountActiveRow] = []
conn.query_into("SELECT count(*) AS \"n\" FROM users WHERE active = ?1", ["1" if active else "0"], &_rows)
if len(_rows) == 0:
return None
return Some(_rows[0].n)
fn rename_user(conn: sqlite.Db, name: str, id: int) int:
return conn.exec_count("UPDATE users SET name = ?1 WHERE id = ?2", [name, str(id)])
For PostgreSQL, the same queries become functions on a pg.Pool that return Results:
fn get_user(pool: pg.Pool, id: int) Result[Option[GetUserRow], str]:
return pool.fetch_one[GetUserRow]("SELECT id, name, email FROM users WHERE id = $1", [str(id)])Running it
tern sqlgen is the command. The database is --db URL, or $DATABASE_URL:
$ tern sqlgen --db sqlite:app.db queries.sql -o users_db.tn
wrote users_db.tn
$ export DATABASE_URL=postgres://app:secret@localhost/app
$ tern sqlgen queries.sql -o users_db.tn --migrations migrations
wrote users_db.tn
$ tern sqlgen queries.sql -o users_db.tn --migrations migrations --check
users_db.tn is up to dateThe same command is a function too, for a build script of your own: sqlgen.command(url, ["queries.sql", "-o", "users_db.tn"]), or sqlgen.cli(url) to take the arguments from the program's.
- Without
--migrations, the queries are checked against the database as it is. - With
--migrations DIR,sqlgencreates a scratch database, applies the migrations with migrate, generates, and drops it. For PostgreSQL this needs permission to create databases; for SQLite it's a temporary file. Nothing needs a schema set up beforehand, so it works in CI. --checkwrites nothing, and fails if the file isn't whatsqlgenwould write: run it in CI so the generated code never falls behind the SQL.- Several
.sqlfiles make one module.
Then import the module like any other and call its functions:
import pg
import users_db # the generated module
fn main() Result[int, str]:
pool = pg.pool("postgres://app:secret@localhost/app", 16)
if users_db.get_user(pool, 7)? is Some(u):
print(u.name, u.email.unwrap_or("(no email)"))
return Ok(0)Functions
fn cli(url: str) Result[int, str] the command: QUERIES.sql ... -o OUT.tn [--migrations DIR] [--check]
fn postgres(p: pg.Pool) Database
fn sqlite(d: sqlite.Db) Database
fn generate(dbase: Database, files: [str]) Result[str, str] the module's text
fn generate_at(url: str, files: [str], migrations: str) Result[str, str] what cli runs
fn parse_queries(file: str, text: str) Result[[Query], str]
fn describe_query(dbase: Database, q: Query, cols: {str: str}) Result[Described, str]Errors name the file, line, and query:
queries.sql:12: get_user: ERROR 42703: column "emial" does not exist
queries.sql:20: totals: column `sum(x)` can't be a field name; name it with AS, like `count(*) AS n`