std.sql

Embedded SQLite, compiled into the binary. Nothing to install.

import std.sql

db = sql.open("app.db")             # or ":memory:"

sql.exec(db, "create table if not exists users (id integer primary key, name text)")
sql.exec(db, "insert into users (name) values (?)", ["Ada"])

rows = sql.query(db, "select * from users where name = ?", ["Ada"])
one  = sql.query_one(db, "select count(*) as n from users")

sql.transaction(db, fn(tx) {
    sql.exec(tx, "insert into users (name) values (?)", ["Bob"])
    sql.exec(tx, "insert into users (name) values (?)", ["Cy"])
})

sql.close(db)

:memory: is pure; a file database needs fs:write covering its path (a scoped grant confines which databases a package can open).

Always use parameters

sql.query(db, "select * from users where name = ?", [name])   # correct
sql.query(db, "select * from users where name = '{name}'")    # injection

The second form is a SQL injection, and interpolation makes it easy to write by accident precisely because interpolation is so natural in this language. A parameter is never parsed as SQL, whatever it contains.

Results

query returns a list of maps keyed by column name. query_one returns the first row, or null - so it is one of the nullable lookups, and absence is expected:

row = sql.query_one(db, "select * from users where id = ?", [id])
if row == null { return not_found() }

Types, and money

SQLite has no decimal type, so keep money in a text column (or as whole cents in an integer one) and rebuild it with decimal(row.amount). A column declared numeric, decimal or real does not keep a decimal: SQLite itself converts '12.50' to the binary float 12.5 as it stores it.

Arithmetic done inside SQL is SQLite's, not Ecko's. update accounts set balance = balance + ? that passes the largest int does not raise; SQLite turns the column into a float. Do checked arithmetic in Ecko and write the result, or keep amounts where they cannot get near the limit.

Transactions

sql.transaction(db, fn) commits when the function returns and rolls back if it raises. That is the whole reason to use it rather than issuing sql.begin(db), sql.commit(db) and sql.rollback(db) yourself: an error between begin and commit would otherwise leave the transaction open. Reach for the manual calls only when the boundary does not line up with a single function.

It rolls back if the commit itself fails, too. A commit can fail for reasons the function never sees - a deferred foreign-key violation, a busy lock - and the transaction is still open when it does, so without the rollback the connection stays inside it and every later begin is refused. The commit's own error is what you catch either way.

sql { ... } blocks

A block form for literal SQL, where every {expr} hole becomes a bind parameter rather than text spliced into the query - the safe form of the interpolation above:

q = sql { SELECT * FROM users WHERE name = {name} }
# { query: "SELECT * FROM users WHERE name = ?", params: [name] }
rows = sql.query(db, q)

The block is a { query, params } map, which sql.query, sql.query_one and sql.exec take in place of the string and the list. One statement per call, as with the string form.

Concurrency

SQLite is a single file with one writer at a time. When two connections want to write at once, the second waits for the first rather than failing: every connection has a busy timeout, 5 seconds unless you set another.

db = sql.open("app.db", busy_timeout_ms: 10000)   # wait up to 10 s for a lock

ECKO_SQL_BUSY_TIMEOUT_MS sets the default for every sql.open that does not pass one. 0 means do not wait, the old behaviour. A lock held for longer than the timeout still fails, with the same { kind: "sql" } error and the message database is locked.

sql.transaction takes the write lock when it starts (BEGIN IMMEDIATE), so a transaction that reads and then writes cannot be refused halfway through by another writer. Two workers doing read-then-write transactions on the same database serialize cleanly instead of one of them failing. The manual sql.begin(db) is still a plain deferred BEGIN.

The safest shape for parallel work is one connection per worker: sql.open the same file in each task. A handle shared between tasks also works for sql.transaction: a second thread's transaction waits for the first, up to the same timeout. So does a plain sql.exec or sql.query from another thread: it runs after the transaction commits or rolls back, never inside it, so a rollback cannot take another task's write with it. If a transaction waits on a task that uses the same handle, the task's statement waits out the timeout and fails; give that task its own connection. A transaction started inside another on the same thread is refused at once, since it would be waiting on itself.

Within one program, sql.transactions writing to the same file take turns in the order they asked, so no worker is starved while others keep writing. Between separate processes SQLite decides the order, and it is not first-come-first-served: under sustained contention from several processes, raise busy_timeout_ms.

A transaction started inside another on the same thread is refused at once, even on a different connection to the same file, since SQLite allows one writer and the inner one could never begin.

Keep write transactions short: every other writer waits for them. For read-heavy work SQLite is excellent; for write-heavy work spread across several machines it is the wrong shape - use postgres or mysql.

Errors

Failures raise { kind: "sql", message } - constraint violations included, which is often the useful signal:

try { sql.exec(db, "insert into users (email) values (?)", [e]) }
catch (err) { match get(err, "kind") { "sql" => already_taken() _ => error(err) } }

Why it is embedded

One binary, no service, no connection string, no apt install. A program that needs a database has one, which is the same reasoning as a file being a program.