SQLite

std/sqlite exposes VM-native SQLite helpers for Harn scripts that need a local, portable relational store without running a database server.

import "std/sqlite"

pipeline default() {
  let db = sqlite_open(".harn/events.sqlite", {
    create: true,
    busy_timeout_ms: 5000,
    journal_mode: "wal",
    foreign_keys: true,
  })

  let rows = sqlite_query(
    db,
    "select topic, id, payload from events where topic = ? order by id desc limit ?",
    ["agent_events", 50],
  )

  log(json_stringify(rows))
  sqlite_close(db)
}

Functions

Function	Returns	Notes
`sqlite_open(path, options?)`	`SqliteDb`	Open `:memory:` or a file-backed database.
`sqlite_query(handle, sql, params?)`	`list<dict>`	Run a parameterized query and return decoded rows.
`sqlite_query_one(handle, sql, params?)`	`dict` or `nil`	Return the first row, or `nil` when no rows match.
`sqlite_execute(handle, sql, params?)`	`SqliteExecuteResult`	Run one statement and return `{rows_affected}`.
`sqlite_transaction(db, fn(tx) -> any, options?)`	closure result	Begin a transaction, pass a scoped `SqliteTx`, commit on normal return, rollback when the closure throws.
`sqlite_savepoint(tx, name)`	`bool`	Create a savepoint inside an open transaction.
`sqlite_release_savepoint(tx, name)`	`bool`	Release a savepoint.
`sqlite_rollback_to_savepoint(tx, name)`	`bool`	Roll back to a savepoint while keeping the outer transaction open.
`sqlite_migrate(db, {dir, table?, dry_run?})`	`SqliteMigrateResult`	Apply pending `.sql` files from a directory.
`sqlite_close(db)`	`bool`	Close and unregister a database handle.
`sqlite_mock_db(fixtures)`	`SqliteMockDb`	Create an in-process fixture-backed database for tests.
`sqlite_mock_calls(mock)`	`list<dict>`	Inspect recorded mock calls.

Open Options

sqlite_open(":memory:") creates a private in-memory database. File paths use the same filesystem path policy as other stdlib file operations.

Options:

Option	Default	Notes
`create`	`false`	Create the database file and parent directories. Without it, missing files fail.
`read_only`	`false`	Open with SQLite read-only flags. Write helpers and migrations reject read-only handles.
`busy_timeout_ms`	`5000`	Wait for transient local writer contention before returning `database is locked`.
`journal_mode`	unset	Optional `delete`, `truncate`, `persist`, `memory`, `wal`, or `off`; ignored for read-only opens.
`foreign_keys`	`true`	Enables SQLite foreign-key enforcement on the connection.

Extension loading is not exposed by std/sqlite.

Parameters and Decoding

Always pass dynamic values through the params list:

let row = sqlite_query_one(
  db,
  "select id, payload from events where topic = ? and id > ?",
  ["agent_events", 100],
)

Primitive Harn values bind as null, booleans (0 or 1), integers, reals, text, blobs, or integer durations. Compound values bind as JSON text.

Rows decode into dictionaries keyed by SQLite column name. SQLite's dynamic type affinity is preserved: nulls become nil, integer and real columns become Harn numbers, text becomes string, and blobs become bytes. JSON text remains text unless the query decodes it explicitly with SQLite JSON functions.

Transactions and Savepoints

sqlite_transaction opens BEGIN IMMEDIATE by default. Pass {mode: "deferred"} or {mode: "exclusive"} when that SQLite transaction mode is required.

sqlite_transaction(db, { tx ->
  sqlite_execute(tx, "insert into notes(id, body) values (?, ?)", [1, "outer"])
  sqlite_savepoint(tx, "before_optional")
  sqlite_execute(tx, "insert into notes(id, body) values (?, ?)", [2, "optional"])
  sqlite_rollback_to_savepoint(tx, "before_optional")
  sqlite_release_savepoint(tx, "before_optional")
})

The outer database handle is blocked while a transaction is active; use the transaction handle passed to the closure. Savepoint names must match /^[A-Za-z_][A-Za-z0-9_.]*$/.

Migrations

sqlite_migrate applies every .sql file under a directory that has not yet been recorded in the ledger. Files are sorted lexicographically. .down.sql siblings are ignored.

let result = sqlite_migrate(db, {
  dir: "./migrations/sqlite",
  table: "harn_sqlite_migrations",
})

The result dict carries applied, skipped, available, dry_run, and table. Pass {dry_run: true} to list pending migrations without applying them.

Mocking

Mocks match exact SQL text after trimming plus exact params when params is provided:

let db = sqlite_mock_db([
  {sql: "select id from events where topic = ?", params: ["agent_events"], rows: [{id: 1}]},
])

let rows = sqlite_query(db, "select id from events where topic = ?", ["agent_events"])
let calls = sqlite_mock_calls(db)

SQLite vs Postgres

std/sqlite and std/postgres intentionally share only familiar call shapes: open/connect, query, query_one, execute, transactions, savepoints, migrations, and deterministic mocks.

They do not share a generic std/sql abstraction. SQLite has local file modes, in-memory databases, WAL pragmas, dynamic type affinity, and ? parameters. Postgres has network pools, $1 parameters, schemas, JSONB, server-side settings, and richer type metadata. Keep dialect-specific SQL in the module that actually runs it.