Databases

A database block holds typed SQL querys, called like db.findUser(id). Calls are fallible, so try / else apply. Two backends are built in — Postgres (the default) and embedded SQLite (driver sqlite, see below) — and everything except the placeholder syntax is identical between them.

The Postgres driver speaks the v3 wire protocol directly over TCP — no libpq, pure Zig.

database db {
    url env("DATABASE_URL")            # postgres://user@host:5432/dbname
    query findUser(id: int) "SELECT id, name, email FROM users WHERE id = $1" -> User
    query listUsers()       "SELECT id, name, email FROM users"               -> [User]
}

route GET "/users/:id" (id: int) -> User { respond try db.findUser(id) }
route GET "/users"     -> [User]        { respond try db.listUsers() }

How queries map

• $1, $2 … bind from the query's typed parameters via the extended protocol (prepared statements — injection-safe).
• The SELECT columns map positionally onto the return record's fields (int / float / bool / string, with T? for nullable).
• Choose the return cardinality by type: T (≥1 row, else 404), T? (0 or 1), or [T] (many).

Streaming large results

A query declared -> Stream<T> returns rows one at a time from a server-side cursor instead of buffering the whole result set. Consume it with a for loop — each row is decoded, handled, and its scratch memory reclaimed before the next arrives, so a result of any size runs in constant memory:

database db {
    url env("DATABASE_URL")
    query recent(lim: int) "SELECT id, name, email FROM users ORDER BY id DESC LIMIT $1" -> Stream<User>
}

route GET "/export" {
    type "text/csv"
    for u in db.recent(1000000) {        # never materialised as a list
        emit "{u.id},{u.name}\n"         # stream each row straight to the client
    }
}

The loop holds one pooled connection for its duration and returns it to the pool when the loop ends — including an early break, after draining the rest of the result set so the connection stays reusable. Streaming queries are Postgres-only.

Bulk insert

To insert many rows in a single round-trip, declare a query that takes one [Record] parameter and targets a table with insert into "table" -> int. The record's fields become the columns (in order); the return value is the number of rows inserted:

type NewUser = { name: string, email: string }

database db {
    url env("DATABASE_URL")
    query addUsers(rows: [NewUser]) insert into "users" -> int
}

route POST "/users/import" (body rows: [NewUser]) -> string {
    let n = try db.addUsers(rows)        # one multi-row INSERT, not N statements
    respond "inserted {n}"
}

One INSERT … VALUES (…),(…),… is generated with a bind parameter per column per row. Since Postgres caps a bind at 65535 parameters, large lists are split into batches automatically. A bulk insert works inside a transaction block too (running on the pinned connection). Bulk insert is Postgres-only.

A [Record] request body is parsed into its own heap arena (not the small fixed request arena), so a large import fits in one request — bounded by MAX_BODY_SIZE (8 MB default; over it → 413), which is roughly 100 K–200 K small rows. The body may be either a JSON array ([{…},{…}]) or NDJSON (one JSON object per line) — the framing is detected automatically, so curl --data-binary @rows.ndjson works as-is.

Streaming the import body

For imports of any size, declare the body as Stream<Record> and consume it with a for loop. Records are decoded one at a time straight off the socket — the body is never buffered whole, so there's no MAX_BODY_SIZE ceiling and memory stays flat no matter how large the upload:

route POST "/users/import" (body rows: Stream<NewUser>) -> string {
    var n = 0
    for u in rows {                      # pulled off the wire one record at a time
        let _ = try db.addUsers([u])     # (batch in practice — see below)
        n = n + 1
    }
    respond "imported {n}\n"
}

Same two framings are accepted (JSON array or NDJSON), detected from the first byte. Stream<T> vs [T] is the only change — the loop body is identical — so you choose materialized-vs-streaming purely by the type. For throughput, accumulate a batch in a var list and flush it with a single bulk insert every N records rather than inserting one row per iteration.

SQLite

Add driver sqlite to target an embedded SQLite database instead (Postgres is the default). Everything above — typed querys, cardinality by return type, try/else, and transaction blocks — works identically; only the backend differs. The url is a file path, opened in WAL mode on first use (with a busy timeout and foreign keys on). Use positional ? parameters (SQLite's placeholder) instead of $1.

database db {
    driver sqlite
    url env("DB_PATH")                 # e.g. app.db
    query add(title: string) "INSERT INTO todos(title) VALUES (?) RETURNING id, title" -> Todo
    query all()              "SELECT id, title FROM todos ORDER BY id"                  -> [Todo]
}

The SQLite engine (the official amalgamation) is bundled into the compiler and compiled into your binary — no external database server, and the same single-file deployment. A program that uses driver sqlite links libc; one that doesn't is unaffected.

Auth & pooling

• SCRAM-SHA-256 (password from the URL, postgres://user:pass@…) and trust, done natively with std.crypto (PBKDF2 + HMAC + SHA-256).
• Connection pooling: a per-database pool (8 connections) guarded by an Io.Semaphore + Io.Mutex; queries acquire/release, and a broken connection is dropped and reconnected.

Transactions

A transaction db { … } block pins one pooled connection for its whole body, wraps it in BEGIN / COMMIT, and rolls back automatically if any query fails or a fail fires — so multi-statement writes are atomic. Respond after the block (responding inside would skip the commit); export values by assigning to a var declared before it.

route POST "/transfer/:from/:to/:amt" (from: int, to: int, amt: int) -> Account {
    var result = Account { id: 0, balance: 0 }
    transaction bank {
        let src = try bank.balanceOf(from)
        if src.balance < amt { fail 400 "insufficient funds" }   # rolls back
        let _ = try bank.debit(from, amt)
        result = try bank.credit(to, amt)
    }
    respond result                     # only reached if the transaction committed
}

Migrations

Declare schema migrations alongside the database. Each migration is applied once, in declaration order, inside a transaction, and recorded in an auto-created _weld_migrations table (so re-runs are no-ops). up is required; down is optional. The SQL is either inline or read from a file at compile time with file "…" — so it's embedded in the binary and travels with your deploy (nothing extra to ship):

database db {
    url env("DATABASE_URL")

    migration "0001_create_users" {
        up   file "db/0001_create_users.up.sql"    # embedded at compile time
        down "DROP TABLE users"
    }
    migration "0002_add_name" {
        up "ALTER TABLE users ADD COLUMN name text"
    }

    query listUsers() "SELECT id, name FROM users" -> [User]
}

Name migrations with a leading number (they apply top-to-bottom); names use letters, digits, _, -, and .. A migration's up may contain multiple ;-separated statements.

A migrations directory

To keep SQL out of the .weld file entirely, point at a directory with migrations "dir" and drop in files named <name>.up.sql (and an optional <name>.down.sql):

database db {
    url env("DATABASE_URL")
    migrations "sql"                 # ./sql/0001.create_users.up.sql, 0001.create_users.down.sql, …
    query listUsers() "…" -> [User]
}

Weld discovers them at compile time, sorts by name (so a numeric prefix orders them), and embeds each file's SQL — again, nothing extra to ship. The migration name is the filename without the .up.sql / .down.sql suffix (e.g. 0001.create_users). Inline migration blocks and a directory can coexist; the inline ones apply first.

Running them

Pending migrations are applied at startup, before serving. On Postgres the apply is guarded by an advisory lock, so rolling out several instances at once is safe. Command-line flags:

Command	Effect
./server	Apply pending migrations, then serve (the default).
./server --migrate-only	Apply pending migrations and exit — run this as a deploy step.
./server --no-migrate	Serve without touching the schema.
./server --rollback	Roll back the last applied migration (runs its down), then exit.
./server --rollback N	Roll back the last N applied migrations, then exit.
./server --migration-status	Print each migration's [applied]/[pending] state (and a count), then exit.

Rollback runs each migration's down in reverse order, in a transaction, and removes its tracking row; a migration with no down cannot be rolled back (Weld reports it and stops). Migrations run on both the Postgres and SQLite drivers. They're raw SQL (not derived from your types), so you write the DDL for your database. A failed migration prints the database error and exits non-zero without recording the migration, so it retries on the next run.

Verified against live Postgres, including a mid-transaction failure (a committed-so-far debit is undone when a later query errors).