Tern

sql

The sql module is what database drivers share, like Go's database/sql: it reads rows straight into your structs. A driver (the pg and sqlite packages, or one you write) hands its rows over in one format, and sql.decode_rows fills a struct or an array of structs from them, with code the compiler writes for that struct type: columns are matched to fields by name once, then each value is read by its field's type. There is no JSON or map in between.

Most programs never call it: they call their driver (db.fetch[User](...) in pg, d.query_into(...) in sqlite), which does. This page is for writing a driver, or for reading rows that come from somewhere else.

import sql

enum Status:
    todo
    done

struct Task:
    id: int
    title: str
    status: Status
    due: Option[str]

fn main():
    b = sql.rows(["id", "title", "status", "due"])
    b.row(4)
    b.put_int(1)
    b.put_text("write the docs")
    b.put_text("done")
    b.put_null()
    b.row(4)
    b.put_text("2")                    # text where the field is a number: read as one
    b.put_text("ship")
    b.put_text("todo")
    b.put_text("2026-10-12")
    tasks: [Task] = []
    n = sql.decode_rows(b.bytes(), &tasks)
    print(n)
    for t in tasks:
        print(t)
Output
2
Task{id: 1, title: "write the docs", status: Status.done, due: None}
Task{id: 2, title: "ship", status: Status.todo, due: Some("2026-10-12")}

Reading rows

fn decode_rows(raw: str, out: mut T) int          T: a struct, or an array of structs
fn decode_error() str

sql.decode_rows(raw, &out) returns the number of rows in raw, or -1 when a value doesn't fit its field (then sql.decode_error() says which) or raw isn't in the format below (then decode_error() is ""). On -1, out is left as it was.

  • Into an array [T], the rows replace what it held.
  • Into a single T, the first row fills the fields it has a column for; the other fields keep their values. Every row is counted, so > 0 means "found".
  • Columns are matched to fields by name. A column with no field is skipped, and a field with no column keeps its zero value (in an array) or its value (in a single struct).
  • NULL is None in an Option field, and the zero value (0, "", false) elsewhere.

What each field type reads:

FieldReads
intan integer, or its text ("42", "-7")
floata float or an integer, their text, or a timestamp's text (2026-10-11 12:00:00+00, 2026-10-11T12:00:00Z, a date): seconds since 1970, like time.now()
boola boolean, an integer (not 0), or text starting with t or 1
strtext, or a number or boolean written as text
an enum without payloadsthe variant's label ("in_progress")
[int] [float] [bool] [str]a PostgreSQL array ({1,2}, {a,"b c"}) or a JSON array
any other type json.decode readsJSON text (a jsonb column into a struct, say)
Option[...] of one of theseNone for NULL, else Some of the value

A value that doesn't fit is an error, not a silent zero:

import sql

struct Item:
    id: int
    name: str

fn main():
    b = sql.rows(["id", "name"])
    b.row(2)
    b.put_text("seven")
    b.put_text("x")
    items: [Item] = []
    print(sql.decode_rows(b.bytes(), &items), sql.decode_error())

    one = Item(0, "unchanged")
    b2 = sql.rows(["id"])
    b2.row(1)
    b2.put_int(9)
    print(sql.decode_rows(b2.bytes(), &one), one)
Output
-1 the value "seven" doesn't fit id: int
1 Item{id: 9, name: "unchanged"}

Building rows

fn rows(columns: [str]) Rows
fn row(r: mut Rows, n: int)               starts a row of n values, in column order
fn put_int(r: mut Rows, v: int)
fn put_float(r: mut Rows, v: float)
fn put_bool(r: mut Rows, v: bool)
fn put_text(r: mut Rows, s: str)
fn put_null(r: mut Rows)
fn bytes(r: mut Rows) str                 the rows so far (r can go on)

A driver that reads a database one value at a time (SQLite's sqlite3_column_*) builds its rows with these. Any column may hold any kind of value, row by row.

The format

A driver that receives rows in bulk can hand them over without copying, if they're in this format already (PostgreSQL's are: the pg package passes the server's DataRow messages through untouched).

  • Header: the column count, then for each column its format and name: u32 format, u32 name length, the name's bytes (little-endian).
  • Rows: each one is 'D', a u32 big-endian length (of what follows, counting the length itself), a u16 big-endian field count, then per field an i32 big-endian length (-1 for NULL) and that many bytes.
  • Formats, per column:
ConstantValueA field is
sql.TEXT0the value as text
sql.INT1a signed big-endian integer of 1, 2, 4 or 8 bytes
sql.FLOAT2a big-endian IEEE float of 4 or 8 bytes
sql.BOOL3one byte, 0 for false
sql.UINT4an unsigned big-endian integer of 1, 2, 4 or 8 bytes
sql.TAGGED5one byte giving the value's own format (0 to 4), then the value

sql.rows writes every column as TAGGED.

A driver that runs the same statement over and over keeps the header: h = sql.rows(columns).head() once, then sql.rows_from(h) starts each result without writing the names again.

Filling a struct from values in hand

A driver that reads values one at a time (sqlite does, with its column functions) can skip the rows format for the common case, the first row into one struct: sql.row_field(&out, name) is the number of the field a column is for (-1: none), worked out once per statement; sql.row_id(&out) is a number for the struct's type (0 when out is an array, which only decode_rows fills); then sql.set_int(&out, field, v), set_float, set_text and set_null put a column's value in, converted as decode_rows would (NULL zeroes the field, or makes an Option None). Each returns false when the value doesn't fit the field, and sql.decode_error() says which.

Source: docs/std/sql.md. Every example on this page is compiled and run by tern x test docs.