radlang — Database

This document covers radlang’s built-in database support: the schema keyword, the Database type, and all connection and query methods.

For language fundamentals see the Overview.

Overview

Database access in radlang is built around two primitives:

• schema — a declaration that maps a radlang struct to a database table and governs column naming. It may be declared at the top level or inside a namespace (see Schemas inside a namespace).
• Database — an opaque connection handle returned by db.connect and used to call all query and write methods.

The compiler generates SQL at compile time from the schema declaration. No string-building or reflection happens at runtime.

SQLite is supported out of the box. MySQL is enabled automatically when mysql-client headers are detected on the build machine (see MySQL).

Async by default

Every method that touches the database — read, query, insert, update, delete, and the select query builder — is asynchronous by default. Each returns a Future<T> and runs the round-trip on a worker thread so the cooperative scheduler is never blocked. Consume the future with await (inside an async fn) or .resolve(cb) (a non-blocking microtask callback):

async fn activeUsers(Database conn): DbUsers[] {
    return await conn.read(UsersTbl)
}

Each async method has a synchronous …Sync twin — readSync, querySync, insertSync, updateSync, deleteSync, selectSync — that blocks and returns the value directly. Use the twins in straight-line setup/scripts where you don’t want to introduce an async fn:

Database conn = db.connect("sqlite://./app.db")
DbUsers[] users = conn.readSync(UsersTbl)   // blocks, returns the list

The DDL helpers generate and migrate are synchronous only (no async twin).

Schema declarations

schema UsersTbl<"users"> [snakeLower] {
    [pk] int id
    ["email_addr"] string email
    string firstName
    bool active = true
    string bio?
}

That single declaration shows the full syntax:

Fields are separated by newlines; a trailing comma is also accepted. (Inline // comments are not allowed inside a schema body — put them on their own line above the schema.)

A schema declaration:

1. Registers the named struct type in the type system (same rules as type).
2. Stores the column mapping for use by conn.read, conn.insert, conn.update, and conn.delete.
3. Generates a DB alias type — the name with Tbl replaced by a Db prefix — which is the type used when reading rows (see Reading rows).

For UsersTbl the DB alias is DbUsers. If the schema name has no Tbl suffix the alias equals the schema name.

Naming rules

Schema names must end with Tbl:

schema OrdersTbl { ... }   // OK — alias is DbOrders
schema Invoice   { ... }   // compile error: must end with Tbl

Schemas inside a namespace

A schema may be declared inside a namespace to group it with the code that uses it. It registers by its bare name (schema names are global identifiers), so conn.generate/insert/read and the Db-alias row type resolve exactly as they do for a top-level schema:

ns database {
    schema UserTbl<"user"> [snakeLower] {
        [pk] string id = "{guid()}"
        string fullName
    }

    fn init() {
        Database c = db.connect("sqlite://./app.db")
        c.generate(UserTbl)          // resolves the in-namespace schema
        c.close()
    }

    fn guid(): string { /* ... */ return "…" }
}

A runtime-expression default ("{guid()}") is evaluated in the scope of the caller of conn.insert; keeping the schema and the helper (guid) in the same namespace means the bare call resolves. See Default values.

Table name

By default the table name matches the schema name exactly. Override it with an angle-bracket string literal immediately after the name:

schema UsersTbl<"app_users"> { ... }   // SELECT ... FROM app_users

Column presets

A preset transforms every camelCase field name into a column name automatically. Place it after the optional table name:

schema UsersTbl [snakeLower] {
    int userId
    string firstName
}

Under [snakeLower], userId maps to user_id and firstName to first_name. Under [snakeUpper] they would be USER_ID and FIRST_NAME.

Five presets are available (shown for a field named userId):

The preset applies to the table name when it is derived from the schema name. Explicit overrides — a <"table"> table name or a ["col"] column name — are always used verbatim, never re-cased by the preset. This keeps a single consistent name across generate/migrate, insert/update/delete, select, where(eq(...)), and orderBy for any given field.

Per-field column overrides

Prefix a field with ["col_name"] to use a specific column name regardless of any preset. The override is used exactly as written:

schema UsersTbl [snakeLower] {
    [pk] int id
    ["display_name"] string name
    bool active
}

Here name’s column is display_name (not name or a preset variant), while active still maps to active via the preset.

Primary key

Mark one field [pk] to identify it as the primary key. conn.update and conn.delete require a [pk] field:

schema UsersTbl {
    [pk] int id,
    string name
}

The [pk] and ["col"] decorators can appear in either order before a field — [pk] ["user_id"] int id and ["user_id"] [pk] int id are equivalent.

Supported field types

Nullable fields

Append ? to a field to make its column nullable — the generated column drops its NOT NULL constraint and the field may be omitted from an insert (it binds SQL NULL):

schema ProductsTbl {
    [pk] int id
    string name
    string description?
    float discount?
}

description and discount are nullable — both may be omitted from an insert, and their columns have no NOT NULL constraint.

Nullability is a storage-level property: a NULL read back from the database currently materializes as the type’s zero value ("" for strings, 0 for numbers, false for bools), not a distinct null.

Default values

Give a field a default with = value. A field that is nullable or has a default is omittable from an insert; every other field is required. There are two kinds of default, distinguished by whether the value is a compile-time literal:

Literal defaults ("", 0, true, -1, …) are used both as the column’s SQL DEFAULT in CREATE TABLE and as the value bound when the field is omitted from an insert:

schema AccountTbl [snakeLower] {
    [pk] int id
    string name = "anon"
    bool active = true
    int score = 0
}

conn.insertSync(AccountTbl, { id: 1 })   // name/active/score take their defaults

Runtime-expression defaults — any non-literal expression (a function call, an interpolated string, a Date chain) — get no SQL DEFAULT clause. Instead they are evaluated application-side, fresh at every conn.insert that omits the field. This is how you auto-generate a primary key or a timestamp:

schema AuthTbl<"auth"> [snakeLower] {
    [pk] string id  = "{guid()}"                          // fresh id per insert
    string createdAt = "{Date.new().format("YYYY-MM-ddTHH:mm:ss")}"
    string expiry
}

conn.insertSync(AuthTbl, { expiry: "2026-08-01" })   // id + createdAt generated
conn.insertSync(AuthTbl, { expiry: "2026-09-01" })   // a *different* id

Scope caveat. A runtime default is emitted in the scope of the caller of conn.insert, not the schema’s. An unqualified name like guid() must be resolvable there — fine when the schema and the insert live in the same namespace; from outside, qualify it (database.guid()). Built-ins such as Date.new() and math.random() resolve everywhere.

Defaults are optional in the row constructor too. A field with a default may be dropped from the positional DbXxx(...) / XxxTbl(...) constructor; the default expression fills it, evaluated fresh at that call site. Supply either the required (default-less) fields only, or every field:

schema RoleTbl<"role"> {
    [pk] string id = guid()      // auto-generated
    string name
    int level
}

DbRole a = DbRole("Admin", 5)        // id ← guid(); 2 required args
DbRole b = DbRole("mgr-1", "Mgr", 3) // explicit id; all 3 fields

This is how you get an auto-populated primary key without hand-writing it at every construction site. The same scope rule applies — an unqualified default name must resolve where the constructor is written.

Connecting

Database conn = db.connect(url)

db.connect returns a Database handle. If the connection fails it throws an Error — wrap it in try/catch when the path may not exist:

Database conn
try {
    conn = db.connect("sqlite://./data/app.db")
} catch (Error e) {
    sys.error(e)
    sys.exit(1)
}

Connection strings

SQLite

db.connect("sqlite://./relative/path/to/file.db")
db.connect("sqlite:///absolute/path/to/file.db")
db.connect("./bare/path.db")   // no prefix — treated as SQLite

MySQL (requires build-time MySQL headers — see MySQL)

db.connect("mysql://user:password@host:3306/dbname")
db.connect("mysql://user@localhost/mydb")   // port defaults to 3306
db.connect("mysql://localhost/mydb")        // anonymous user

libsql / Turso

db.connect("libsql://<db>-<org>.turso.io?authToken=<token>")

A libsql:// URL connects to a remote Turso (libsql) database over HTTPS using the Hrana pipeline protocol — no extra native library is required (it rides the same HTTP client the http namespace uses). Pass the database auth token as the authToken query parameter (auth_token is also accepted). The host is reached at https://<host>/v2/pipeline.

The connection is stateless: each statement is a one-shot HTTPS request, so there is no persistent socket and conn.close() is a no-op. db.connect performs a SELECT 1 round-trip up front, so a bad host or token throws an Error immediately (e.g. db(libsql): HTTP 400 … {"error":"JWT error: InvalidToken"}).

All schema operations work over libsql — generate, migrate, read, insert, update, delete, the query builder, and raw conn.query. Because libsql is SQLite-compatible, an [pk] int id? column autoincrements server-side when omitted from an insert, exactly as with local SQLite.

Keep the token out of source — read it from config and compose the URL. A per-profile env: entry (a key: with indented debug: / release: / default: lines) resolves automatically to the active build profile, so the same code uses local SQLite under radlang run and Turso under radlang build:

# config.yaml
env:
  db_path:
    debug:   "sqlite://./dev.db"
    release: "libsql://<db>-<org>.turso.io"
  db_token:
    release: "<token>"          # no debug variant -> "" under `radlang run`

// main.rad — cfg.env picks the debug/release variant by profile
string url   = cfg.env("db_path")
string token = cfg.env("db_token")
if token != "" { url = url + "?authToken=" + token }
Database conn = db.connect(url)

Note. db.connect takes a single URL, so the token rides the URL as ?authToken=. Keep db_path and db_token as separate config entries and combine them at connect time as above.

Closing

conn.close()

Call conn.close() when the connection is no longer needed. The underlying file handle or socket is released immediately.

Reading rows

conn.read is async by default — it returns Future<DbUsers[]>. Consume it with await in an async fn, or use the synchronous twin readSync:

// async — inside an async fn
async fn loadUsers(Database conn): DbUsers[] {
    return await conn.read(UsersTbl)
}

// sync twin — straight-line code
DbUsers[] users = conn.readSync(UsersTbl)

conn.read executes SELECT col1, col2, ... FROM table at runtime and returns a list of the DB alias type. The SELECT column list and table name are determined at compile time from the schema.

The argument must be the schema type name (the ...Tbl name), not the DB alias:

DbUsers[] users = conn.readSync(UsersTbl)   // correct
DbUsers[] users = conn.readSync(DbUsers)    // also accepted (alias resolves back)

Iterate the result with for — the loop variable needs an explicit type and parentheses (for (T x in list)):

for (DbUsers user in users) {
    sys.output(user.id.toString() + ": " + user.name)
}

Or index directly:

DbUsers first = users[0]
int count = users.length()

Reading nullable columns

A NULL in a nullable column currently materializes as the field type’s zero value on read ("" for strings, 0 for numbers, false for bools) rather than a distinct null. Distinguishing “unset” from a real zero value is not yet supported on the read path — treat a nullable column’s absence as its zero value, or store a sentinel default.

Creating & migrating tables

Both methods derive their DDL from the schema (table override, preset, per-column overrides, [pk], nullability, and defaults) and return an int.

generate

conn.generate(Schema) runs CREATE TABLE IF NOT EXISTS for the schema:

conn.generate(AccountTbl)

Produces, for the AccountTbl example above:

CREATE TABLE IF NOT EXISTS accounts (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL DEFAULT 'anon',
  active INTEGER NOT NULL DEFAULT 1,
  score INTEGER NOT NULL DEFAULT 0
)

The primary key uses the field’s own type affinity (INTEGER PRIMARY KEY for an int pk, TEXT PRIMARY KEY for a string pk).

migrate

conn.migrate(Schema) brings an existing table up to the current schema additively (SQLite only). It creates the table if absent, then issues ALTER TABLE ... ADD COLUMN for every schema column not already present, and returns the number of columns added (0 when already up to date):

int added = conn.migrate(AccountTbl)

It never drops or alters existing columns — SQLite cannot do so in place. A newly added NOT NULL column must carry a default (from = value) so existing rows can be backfilled; a nullable column needs none.

Writing rows

All write methods are async by default, returning Future<int> (the number of rows affected). Use await / .resolve, or the synchronous …Sync twins (insertSync / updateSync / deleteSync) when you want the count directly. The examples below use the sync twins for brevity.

Insert

conn.insertSync(UsersTbl, { id: 1, name: "Alice", active: 1 })   // sync twin
int n = await conn.insert(UsersTbl, { id: 2, name: "Bob" })      // async form

Builds INSERT INTO table (col1, col2, ...) VALUES (?, ?, ...) at compile time. The struct literal must include every field that is neither nullable nor defaulted; omitted nullable fields bind SQL NULL and omitted defaulted fields bind their default. Fields are matched by name; order does not matter in the literal.

Parameters are bound with the database’s prepared statement API — no SQL injection risk from user-supplied values.

Update

conn.updateSync(UsersTbl, { id: 1, name: "Alicia", active: 1 })

Builds UPDATE table SET col=? ... WHERE pk=?. The struct literal must include the [pk] field. All other fields present in the literal become SET clauses; omitted fields are left unchanged (partial update):

// Full replace — both name and active change
conn.updateSync(UsersTbl, { id: 1, name: "Alicia", active: 0 })

// Partial update — only name changes, active is untouched
conn.updateSync(UsersTbl, { id: 1, name: "Alicia" })

A schema without a [pk] field causes a compile-time error when conn.update is called.

Delete

conn.deleteSync(UsersTbl, 1)       // delete by literal pk
conn.deleteSync(UsersTbl, user.id) // delete by expression

Builds DELETE FROM table WHERE pk=?. The second argument is any expression that produces the primary key value. A schema without [pk] causes a compile-time error.

Query builder

The query builder provides a fluent, chainable API for SELECT, UPDATE, and DELETE operations. It reads like SQL and is fully type-safe at compile time.

Like the other read methods, the builder is async by default: a chain rooted at conn.select() materializes to Future<DbAlias[]> (consume with await / .resolve). The synchronous twin is conn.selectSync(), which materializes to DbAlias[] directly. The two are otherwise identical — same .from/.where/ .orderBy/.limit chain, same projection support. The UPDATE/DELETE write terminals (.update / .delete, below) are synchronous and belong on a selectSync chain.

Building a query

conn.select().from(UsersTbl)       // async  → Future<DbUsers[]>
conn.selectSync().from(UsersTbl)   // sync   → DbUsers[]

conn.select() / conn.selectSync() open the chain. Chain methods to refine the query. The chain may stay on one line or span several lines with leading-dot continuation — both parse the same:

conn.select().from(UsersTbl).where("active = 1").orderBy("name ASC").limit(10)

conn.select()
    .from(UsersTbl)
    .where("active = 1")
    .orderBy("name ASC")
    .limit(10)

radlang fmt keeps short chains on one line and breaks long ones onto leading-dot lines automatically.

.from(SchemaTbl) is required. The rest are optional and can appear in any order.

Condition helpers for where

.where takes any SQL string, but the db helpers build conditions that resolve column names through the schema (so presets and overrides apply) and bind values safely. Import the ones you use:

import { eq, like, and, or, not } from "db"

The first argument to eq/like is a DbAlias.field reference — the compiler turns it into the correct column name. Compose them freely:

conn.select().from(UsersTbl).where(and(eq(DbUsers.active, true), like(DbUsers.name, "Al%")))
conn.select().from(UsersTbl).where(or(eq(DbUsers.id, 1), eq(DbUsers.id, 2)))
conn.select().from(UsersTbl).where(not(eq(DbUsers.active, true)))

Ordering with asc / desc

.orderBy accepts either a raw string (orderBy("name ASC")) or the asc / desc helpers, which take a destructured column set — just like select:

import { asc, desc } from "db"

conn.select().from(UsersTbl).orderBy(asc({ name }))
conn.select().from(UsersTbl).orderBy(desc({ createdAt }))
conn.select().from(UsersTbl).orderBy(asc({ lastName, firstName }))   // last_name ASC, first_name ASC

Column names inside the helper are resolved through the chain’s .from() schema, so presets and per-field overrides apply automatically (createdAt under [snakeLower] orders by created_at). Multiple fields in one helper all take the same direction; the raw-string form remains available for mixed directions.

Column projection with select(?T)

Pass a ?Type partial to select() to restrict which columns are fetched. Only the fields named in the partial appear in the SELECT list; unselected fields are zero-initialized in the returned structs.

// SELECT name, active FROM users
?UsersTbl cols = { name: "", active: 0 }
DbUsers[] rows = conn.selectSync(cols).from(UsersTbl)

Projection works the same on the async select — inferred var is the natural form there (var rows = await conn.select(cols).from(UsersTbl)).

Field names in the partial are validated against the schema at compile time (same rule as any ?T declaration — each name must exist in the type; at least one is required). The actual values in the partial literal are ignored; only the field names matter for SQL building.

conn.select() with no argument is equivalent to SELECT * and is the default behavior.

Executing a SELECT

There is no explicit .exec() call — the chain fires when it is consumed. How it fires depends on the root method:

// selectSync — fires and materializes synchronously into DbUsers[]
DbUsers[] users = conn.selectSync().from(UsersTbl).where("active = 1")

// select — async; the chain is a Future<DbUsers[]>, consumed with await
async fn actives(Database conn): DbUsers[] {
    return await conn.select().from(UsersTbl).where("active = 1").orderBy("id ASC")
}

// …or a non-blocking .resolve callback
conn.select().from(UsersTbl).resolve(fn(DbUsers[] rows) {
    sys.output("got " + rows.length().toString() + " rows")
})

An async chain may also be stored in an inferred var and awaited later (var fut = conn.select().from(UsersTbl); DbUsers[] r = await fut). A selectSync chain may pause in an intermediate QueryBuilder and fire at the later typed assignment:

QueryBuilder q = conn.selectSync().from(UsersTbl).where("active = 0")
DbUsers[] inactive = q.orderBy("id ASC")

The compiler walks the chain at compile time to find the .from(SchemaTbl) call and uses the schema to build the SELECT column list. The actual SQL is composed and executed at runtime.

Partial type declarations

A partial type (?TypeName) declares a struct that contains a subset of another type’s fields. All provided fields must exist on the base type; at least one field is required:

?UsersTbl payload = { name: "Alice" }           // valid — name is in UsersTbl
?UsersTbl bad     = { phone: "555-1234" }        // compile error: 'phone' not a field
?UsersTbl empty   = {}                           // compile error: at least one field required

Partial declarations work against any named type or schema struct:

type Config {
    string host,
    int port,
    bool tls
}

?Config patch = { host: "newhost.example.com" }

Updating rows with the query builder

The write terminals are synchronous — build them on a selectSync chain (the async select root is read-only; pairing it with .update/.delete is a compile error). Use .update(partial) as the terminal method on a builder that has a .where() clause. The partial struct determines which columns are SET:

?UsersTbl payload = { name: "Alicia" }
conn.selectSync().from(UsersTbl).where("id = 1").update(payload)
// Executes: UPDATE users SET name = ? WHERE id = 1

Inline struct literals work too:

conn.selectSync().from(UsersTbl).where("active = 0").update({ name: "Inactive" })

.update() returns the affected row count as int. Fields in the partial are mapped through the schema’s column naming rules (presets, per-field overrides) the same way conn.update does.

Deleting rows with the query builder

.delete() is the terminal method for a DELETE, likewise on a selectSync chain. The builder must have a .where() clause set:

conn.selectSync().from(UsersTbl).where("id = 99").delete()
// Executes: DELETE FROM users WHERE id = 99

.delete() returns the affected row count as int.

Query builder quick reference

// SELECT (sync twin; use `await conn.select()...` for the async form)
DbUsers[] rows = conn.selectSync().from(UsersTbl).where("active = 1").orderBy("name ASC").limit(5)

// UPDATE via partial
?UsersTbl patch = { name: "Bob" }
int updated = conn.selectSync().from(UsersTbl).where("id = 2").update(patch)

// DELETE
int deleted = conn.selectSync().from(UsersTbl).where("id = 99").delete()

Raw SQL queries

string[][] rows = conn.querySync("SELECT name, email FROM users WHERE active = 1")

conn.query executes an arbitrary SQL string and returns the rows as string[][] — a list of rows, where each row is a list of column values as strings. NULL cells are returned as empty string "". Like the other read methods it is async by default (Future<string[][]>, consumed with await / .resolve); the example above uses the synchronous twin querySync.

This is the escape hatch for:

• Queries that cannot be expressed through the schema intrinsics (JOINs, GROUP BY, subqueries, aggregates)
• DDL statements (CREATE TABLE, ALTER TABLE, etc.)
• Transactions (BEGIN, COMMIT, ROLLBACK)

Accessing the result:

string[][] rows = conn.querySync("SELECT COUNT(*) FROM users")
int count = json.parse<int>(rows[0][0])

string[][] users = conn.querySync("SELECT id, name FROM users ORDER BY name")
for (string[] row in users) {
    string id   = row[0]
    string name = row[1]
    sys.output(id + ": " + name)
}

Column count and row count:

int rowCount = rows.length()
int colCount = rows[0].length()    // assumes at least one row

conn.query does not support parameterized placeholders. For user-supplied values use conn.insert, conn.update, or conn.delete which bind parameters safely. If you must use conn.query with dynamic values, escape them yourself.

MySQL

MySQL support is compiled in automatically when the compiler detects MySQL client headers at build time. Detection order:

1. pkg-config --cflags --libs mysqlclient
2. /opt/homebrew/opt/mysql-client/include (macOS Homebrew)
3. /usr/local/opt/mysql-client/include
4. /usr/include/mysql

When headers are found, runtime/db.c is compiled with -DHAVE_MYSQL and linked with -lmysqlclient.

If MySQL is not detected, a mysql:// connection string throws an Error at runtime with the message MySQL support not compiled in.

MySQL read queries use mysql_store_result / mysql_fetch_row. Write queries use mysql_stmt_prepare + MYSQL_BIND parameterized binding — the same safety guarantee as the SQLite path.

Full example

A complete, runnable program exercising the schema decorators, generate, defaults/nullable, the query builder with where/orderBy(asc), partial update, and delete. (Comments live above the schema — not inside its body.)

import "db"
import { eq, asc, desc } from "db"

// email uses an explicit column name (verbatim); firstName maps to first_name
// via the preset; active has a default; bio is nullable.
schema UserTbl<"users"> [snakeLower] {
    [pk] int id
    ["email_addr"] string email
    string firstName
    bool active = true
    string bio?
}

fn main() {
    Database conn = db.connect("sqlite://./myapp.db")

    // Create the table from the schema (idempotent).
    conn.generate(UserTbl)

    // active and bio may be omitted — they take their default / bind NULL.
    // `main` is synchronous, so use the `…Sync` twins (or wrap in an async fn
    // and `await` the async forms).
    conn.insertSync(UserTbl, { id: 1, email: "alice@x.com", firstName: "Alice" })
    conn.insertSync(UserTbl, { id: 2, email: "bob@x.com", firstName: "Bob", active: false })

    // Read every row into the DB-alias type.
    DbUser[] all = conn.readSync(UserTbl)
    sys.output("total: " + all.length().toString())

    // Query builder: filter + order. Column names resolve through the schema.
    // A chain may span multiple lines with leading-dot continuation.
    DbUser[] activeUsers = conn.selectSync()
        .from(UserTbl)
        .where(eq(DbUser.active, true))
        .orderBy(asc({ firstName }))
    for (DbUser u in activeUsers) {
        sys.output(u.firstName + " <" + u.email + ">")
    }

    // Partial update — only firstName changes.
    conn.updateSync(UserTbl, { id: 1, firstName: "Alicia" })

    // Delete by primary key.
    conn.deleteSync(UserTbl, 2)

    sys.output("remaining: " + conn.readSync(UserTbl).length().toString())
    conn.close()
}

Output:

total: 2
Alice <alice@x.com>
remaining: 1

The generated table is:

CREATE TABLE users (id INTEGER PRIMARY KEY, email_addr TEXT NOT NULL,
                    first_name TEXT NOT NULL, active INTEGER NOT NULL DEFAULT 1,
                    bio TEXT)

Error handling

db.connect throws Error on connection failure. All other methods (read, insert, update, delete, query and their …Sync twins) print to stderr on SQL error and return a zero/empty result rather than throwing. Wrap db.connect in try/catch for robust applications:

try {
    Database conn = db.connect("sqlite://./data.db")
    // use conn ...
    conn.close()
} catch (Error e) {
    sys.error(e)
}

Quick reference

Connection and direct methods

Query builder methods

Partial type

Schema decorator quick reference