Tern

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 nameGenerated function returns (PostgreSQL)(SQLite)
:oneResult[Option[Row], str]: the first row, or NoneOption[Row]
:manyResult[[Row], str][Row]
:execResult[int, str]: the rows changedint
  • 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 ($1 in PostgreSQL, ?1 or ? in SQLite) and are called arg1, 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.Pool or conn: sqlite.Db. SQLite functions return plain values because sqlite errors are runtime errors.

Types and NULL

PostgreSQLSQLite (declared type)Tern
int2, int4, int8, oidcontains INTint
float4, float8, numericREAL, FLOAT, DOUBLE, NUMERIC, DECIMALfloat
boolcontains BOOLbool
timestamptz, timestampfloat: 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, enumseverything 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:

NameMeans
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) str

Columns 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)
Output
# 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 date

The 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, sqlgen creates 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.
  • --check writes nothing, and fails if the file isn't what sqlgen would write: run it in CI so the generated code never falls behind the SQL.
  • Several .sql files 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`
Source: docs/packages/sqlgen.md. Every example on this page is compiled and run by tern x test docs.