sqlite
The sqlite package uses SQLite, the embedded SQL database, through the system's SQLite library (libsqlite3, which it binds with an extern block; everything else is Tern). You open a database file, run statements with exec, and read rows either as maps of strings with query or straight into your own structs with query_into. This page covers those functions, how parameters are bound, what happens on errors, and how connections work across tasks and threads.
import sqlite
struct Todo:
id: int
title: str
done: bool
fn main():
d = sqlite.open(":memory:")
d.exec("CREATE TABLE todos (id INTEGER PRIMARY KEY, title TEXT, done INTEGER)", [])
id = d.exec("INSERT INTO todos (title, done) VALUES (?, ?)", ["write docs", "0"])
todos: [Todo] = []
d.query_into("SELECT id, title, done FROM todos", [], &todos)
print(id, todos)1 [Todo{id: 1, title: "write docs", done: false}]SQLite is linked only into programs that import sqlite. It is available on macOS, Linux, and iOS, but not on Android (the NDK doesn't ship it) or WebAssembly.
The Db type
A sqlite.Db names an open database: its path, and the pool of connections the program keeps to it. It is a handle, so copying it is free and every copy refers to the same database. That is also why a read-only parameter d: sqlite.Db can still run INSERTs: the database lives outside the program's values, and "read-only" is about Tern values only.
All the functions can be called with method syntax: d.exec(...) is sqlite.exec(d, ...).
open
fn open(path: str) DbOpens the SQLite database file at path, creating it if it doesn't exist. If it can't be opened (for example, the directory doesn't exist), the program stops with a runtime error.
- File databases are switched to SQLite's WAL mode, which lets readers and a writer work at the same time. SQLite then keeps two helper files next to the database,
name-walandname-shm; leave them alone. ":memory:"opens a temporary in-memory database. Each connection to it is its own, separate database (see Connections below), so use a file whenever tasks,parallelcode or a web server need to see the same data.- A statement waits up to 5 seconds for a lock held by another connection before failing with "database is locked". In a task, only that task waits.
- Open each database once, at start-up, and pass the
Dbaround.
exec
fn exec(d: Db, sql: str, params: [str]) intRuns one SQL statement (CREATE, INSERT, UPDATE, DELETE, BEGIN, ...) and returns the row id of the most recent successful INSERT on this connection. After an INSERT into a table with an INTEGER PRIMARY KEY, that is the new row's id. For other statements the value is left over from an earlier insert (or 0), so ignore it.
sql may hold several statements separated by ; (a schema, say), and they run in order. With ? parameters, write one statement per call: SQLite would bind the parameters to the first statement only, so that is a runtime error. (query and query_into take exactly one statement.)
import sqlite
fn main():
d = sqlite.open(":memory:")
d.exec("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT); CREATE INDEX users_name ON users (name);", [])
d.exec("INSERT INTO users (name) VALUES (?)", ["ada"])
print(d.query("SELECT name FROM sqlite_master WHERE type = 'index'", []), d.query("SELECT name FROM users", []))[{"name": "users_name"}] [{"name": "ada"}]To find out how many rows an UPDATE or DELETE changed, run it with exec_count, which returns that number instead of the row id:
fn exec_count(d: Db, sql: str, params: [str]) intimport sqlite
fn main():
d = sqlite.open(":memory:")
d.exec("CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT UNIQUE, visits INTEGER DEFAULT 0)", [])
a = d.exec("INSERT INTO users (email) VALUES (?)", ["ada@example.com"])
b = d.exec("INSERT INTO users (email) VALUES (?)", ["bob@example.com"])
changed = d.exec_count("UPDATE users SET visits = visits + 1 WHERE id = ?", [str(b)])
print(a, b, changed)
d.exec("BEGIN", [])
for i in 0..3:
d.exec("INSERT INTO users (email) VALUES (?)", [f"user{i}@example.com"])
d.exec("COMMIT", [])
print(d.query("SELECT count(*) AS n FROM users", []))1 2 1
[{"n": "5"}]Wrapping many inserts in BEGIN / COMMIT makes them much faster, since SQLite then writes to disk once instead of once per statement.
query
fn query(d: Db, sql: str, params: [str]) [{str: str}]Runs a SELECT (or any statement that returns rows) and returns every row as a map from column name to value, as text. Numbers come back as their text ("42", "9.5"), and NULL comes back as "". Each map keeps the columns in the order of the SELECT. Use AS to name computed columns; if two columns have the same name, the last one wins.
import sqlite
fn main():
d = sqlite.open(":memory:")
d.exec("CREATE TABLE items (name TEXT, price REAL, note TEXT)", [])
d.exec("INSERT INTO items VALUES (?, ?, NULL)", ["tea", "3.5"])
d.exec("INSERT INTO items VALUES (?, ?, ?)", ["cake", "4", "fresh"])
rows = d.query("SELECT name, price, note FROM items ORDER BY name", [])
print(rows)
for r in rows:
print(r["name"], r["price"].to_float(0.0) * 2.0, "[" + r["note"] + "]")
stats = d.query("SELECT count(*) AS n, sum(price) AS total FROM items", [])[0]
print(stats["n"].to_int(0), stats["total"])
print(len(d.query("SELECT * FROM items WHERE name = ?", ["coffee"])))[{"name": "cake", "price": "4.0", "note": "fresh"}, {"name": "tea", "price": "3.5", "note": ""}]
cake 8.0 [fresh]
tea 7.0 []
2 7.5
0query is the quick way to look at data. For real code, query_into is faster (no maps, no string conversion) and gives you typed values.
query_into
fn query_into(d: Db, sql: str, params: [str], out: mut T) intRuns a query and reads the rows straight into structs, with the standard library's sql decoder (code the compiler writes for your struct type). out is a struct, or an array of structs; pass it with &. Fields may be int, float, bool, str, an enum without payloads, an array or any other type json.decode reads (from JSON text in the column), or an Option of one. An enum field reads a text column by variant name, loosely (in_progress fills InProgress). A value that doesn't fit its field (text naming no variant, "seven" for an int) is a runtime error. Columns are matched to fields by name (use AS to rename), columns with no matching field are ignored, and a field with no matching column is left alone. NULL reads as 0, 0.0, false, or "", and as None in an Option field. Returns the number of rows the query produced.
- Into an array (
&todos): the array is replaced by one element per row. Fields with no matching column are zero (0,"", ...). - Into a single struct (
&todo): the first row fills the matching fields and the other fields keep their values. With no rows, the struct is unchanged, sod.query_into(sql, params, &one) > 0means "found".
import sqlite
struct Book:
id: int
title: str
price: float
in_stock: bool
fn main():
d = sqlite.open(":memory:")
d.exec("CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT, price REAL, stock INTEGER)", [])
d.exec("INSERT INTO books (title, price, stock) VALUES (?, ?, ?)", ["Dune", "9.99", "3"])
d.exec("INSERT INTO books (title, price, stock) VALUES (?, ?, ?)", ["Emma", "4.5", "0"])
books: [Book] = []
n = d.query_into("SELECT id, title, price, stock > 0 AS in_stock FROM books", [], &books)
print(n, books[0])
b = Book(0, "(none)", 0.0, false)
if d.query_into("SELECT id, title FROM books WHERE title = ?", ["Emma"], &b) > 0:
print("found", b)
missing = Book(0, "(none)", 0.0, false)
print(d.query_into("SELECT * FROM books WHERE id = ?", ["99"], &missing), missing.title)2 Book{id: 1, title: "Dune", price: 9.99, in_stock: true}
found Book{id: 2, title: "Emma", price: 0.0, in_stock: false}
0 (none)A field that holds a list or a nested value reads it from JSON text, so store it as JSON (see json):
import json
import sqlite
struct Post:
id: int
tags: [str]
fn main():
d = sqlite.open(":memory:")
d.exec("CREATE TABLE posts (id INTEGER PRIMARY KEY, tags TEXT)", [])
d.exec("INSERT INTO posts (tags) VALUES (?)", [json.encode(["tern", "sql"])])
posts: [Post] = []
d.query_into("SELECT id, tags FROM posts", [], &posts)
print(posts)[Post{id: 1, tags: ["tern", "sql"]}]Errors
A statement that SQLite rejects (a syntax error, a missing table, a violated UNIQUE or NOT NULL constraint, a locked database) is a runtime error: the program prints SQLite's message, after sqlite:, to standard error and exits with status 2.
runtime error in sqlite._exec (sqlite/sqlite.tn:N): sqlite: UNIQUE constraint failed: users.emailInside a web request, the same error ends only that request, which gets a 500 internal server error response while the server keeps running (see http). So in a server, a constraint failure can't take the site down, but it can't be caught and answered with a nicer message either. When you expect a conflict, check first (SELECT ... WHERE email = ?), or use SQL that can't fail, such as INSERT OR IGNORE, INSERT ... ON CONFLICT DO UPDATE, or CREATE TABLE IF NOT EXISTS.
run_script is the exception: it returns SQLite's error instead of stopping the program.
fn run_script(d: Db, sql: str) Result[int, str]It runs every statement in sql in order (a schema file, a migration), takes no ? parameters, and returns the number of rows inserted, updated, or deleted. The first error stops the rest. The statements before it stay done unless the script began a transaction, which you then end with ROLLBACK. For versioned schema changes, see migrate.
import sqlite
fn main():
d = sqlite.open(":memory:")
print(d.run_script("CREATE TABLE t (x INTEGER UNIQUE); INSERT INTO t VALUES (1), (2);"))
print(d.run_script("BEGIN; INSERT INTO t VALUES (3); INSERT INTO t VALUES (1);"))
d.run_script("ROLLBACK")
print(d.query("SELECT count(*) AS n FROM t", []))Ok(2)
Err("sqlite: script failed: UNIQUE constraint failed: t.x")
[{"n": "2"}]describe
fn describe(d: Db, sql: str) Result[Statement, str]What SQLite knows about a statement without running it: its parameters (params: their names as written, "@id", ":name", "?1", or "" for a plain ?) and its result columns (columns: name, decl_type, table, column, not_null). decl_type is the type the table declares, "" for an expression; not_null is true for a column declared NOT NULL (and an INTEGER PRIMARY KEY). It is what sqlgen reads to generate typed functions.
import sqlite
struct User:
id: int
email: Option[str]
fn main():
d = sqlite.open(":memory:")
d.run_script("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT); INSERT INTO users (name) VALUES ('ada');").unwrap()
s = d.describe("SELECT id, name, email, count(*) AS n FROM users WHERE name = @name").unwrap()
print(s.params)
for c in s.columns:
print(c.name, c.decl_type, c.table, c.not_null)
users: [User] = []
d.query_into("SELECT id, email FROM users", [], &users)
print(users)["@name"]
id INTEGER users true
name TEXT users true
email TEXT users false
n false
[User{id: 1, email: None}]Threads and connections
A Db can be used from any task and any thread at once. Each statement runs on a connection of the database's pool, taken for that statement and given back afterwards; the pool keeps connections per thread, each with its own cache of prepared statements (up to 64), so tasks and server threads run queries at the same time without meeting at a lock. SQLite itself lets many readers work at once, and one writer at a time.
- A transaction (
BEGIN...COMMIT) belongs to the task that began it (or, outside tasks, to the thread): its connection stays with it until the transaction ends, even while the task waits (a sleep, a channel, a socket) or moves to another thread. Other tasks use other connections and don't see its uncommitted rows. End every transaction in the task that began it: if that task ends (or fails) with the transaction still open, nothing rolls it back, so its connection and the write lock stay held until the program ends and other writers get "database is locked" after waiting five seconds. (Inside aroosthandler, make an error afterBEGINroll back first:ROLLBACKbefore returning or raising.) - Connections are closed when the program ends; there is no
close, so open aDbonce and share it rather than opening one per request. ":memory:"databases are per connection, so tasks may each see their own empty database. Use a file for anything shared.
Inside an http.serve handler, a thread's reads of a database share one read transaction that ends when the response is sent, so all the reads in a request see the same data (and cost one lock instead of one per query). A write, run_script, flush or a transaction of your own ends that snapshot first, so they behave exactly as without it. (Handlers of a task server such as roost run their reads one by one.)
Many writers at once
SQLite commits one write transaction at a time, and each commit costs far more than the statement itself. So a single INSERT, UPDATE, or DELETE run from a task (every web request is one), outside a transaction of its own, goes to the database's writer: one task that runs every such write waiting at that moment in one transaction and commits once for all of them. Each statement still returns only after its batch has committed, so it behaves exactly like its own transaction: its rows are saved when it returns, a constraint error fails only that statement, and if the commit itself fails, every statement in the batch fails with that error. exec returns the statement's own row id and exec_count its own count of changed rows. If the writer can't open its connection (the database file's directory is gone, say), the statements waiting fail with that error, and the next write starts a new writer, which tries again.
For a server doing a write per request this is several times the throughput of a commit per statement, with much shorter tails (see bench/agent_app/results.md). Statements in a transaction of their own, statements run with run_script or several at once in exec, PRAGMA and schema statements, and code outside tasks run on the caller's connection as usual. (In SQL, changes() and last_insert_rowid() report the connection a statement ran on, which for a batched write isn't the caller's: use what exec and exec_count return.)
Writes that may wait: exec_later and flush
Some writes don't need to be saved before the response: a click count, a view counter, a "last seen" time. d.exec_later(sql, params) queues such a write and returns at once; a background task commits everything queued, in order and in one transaction, every 50 milliseconds (a server too busy to give the task a turn has the callers of exec_later do it, one of them each 50 ms), and once more when the program exits (through os.at_exit). The request doesn't wait for SQLite at all, which makes it the fastest way to write on a hot path. The price: if the process crashes (or is killed with SIGKILL), the writes of the last few milliseconds are lost. A queued write that fails (a constraint, say) is reported on stderr, since there's no caller left to return the error to, and the others still commit. If the commit can't get the write lock (another process holds it for a few seconds), the batch is rolled back and stays queued, in order and ahead of newer writes, and the background task tries again every tick until it works (one line on stderr says it is waiting). flush keeps trying for up to a minute. Only a write that itself errors is dropped, and the last commit at exit, if it still can't get the lock after several tries, reports how many writes were lost.
d.flush() commits what's queued right away, so a read that must see those writes can:
import fs
import sqlite
fn main():
fs.remove("/tmp/clicks_doc.db")
d = sqlite.open("/tmp/clicks_doc.db")
d.exec("CREATE TABLE links (slug TEXT PRIMARY KEY, clicks INTEGER)", [])
d.exec("INSERT INTO links VALUES ('home', 0)", [])
for i in 0..1000: # the hot path: returns at once
d.exec_later("UPDATE links SET clicks = clicks + 1 WHERE slug = ?", ["home"])
d.flush() # before reading the exact count
print(d.query("SELECT clicks FROM links", [])[0]["clicks"])
fs.remove("/tmp/clicks_doc.db")1000Use exec for anything that must be saved before you answer (a signup, an order, a payment), and exec_later for the rest.
Connection settings run with exec (d.exec("PRAGMA foreign_keys = ON", [])) apply to every connection opened afterwards, on any thread.
A complete example
A JSON API for bookmarks, backed by a database file (build it with tern build bookmarks.tn -o bookmarks):
import http
import sqlite
import json
struct Bookmark:
id: int
url: str
title: str
visits: int
struct NewBookmark:
url: str
title: str
fn list_all(d: sqlite.Db) http.Response:
marks: [Bookmark] = []
d.query_into("SELECT id, url, title, visits FROM bookmarks ORDER BY visits DESC, id", [], &marks)
return http.json(json.encode(marks))
fn create(d: sqlite.Db, body: str) http.Response:
nb = NewBookmark("", "")
if not json.decode(body, &nb) or not nb.url.starts_with("http"):
return http.status(400, `send {"url": "https://...", "title": "..."}`)
d.exec("INSERT INTO bookmarks (url, title) VALUES (?, ?) ON CONFLICT(url) DO NOTHING", [nb.url, nb.title])
b = Bookmark(0, "", "", 0)
d.query_into("SELECT id, url, title, visits FROM bookmarks WHERE url = ?", [nb.url], &b)
res = http.json(json.encode(b))
res.status = 201
res.set_header("Location", f"/bookmarks/{b.id}")
return res
fn visit(d: sqlite.Db, id: str) http.Response:
b = Bookmark(0, "", "", 0)
if d.query_into("SELECT id, url FROM bookmarks WHERE id = ?", [id], &b) == 0:
return http.not_found()
d.exec("UPDATE bookmarks SET visits = visits + 1 WHERE id = ?", [id])
return http.redirect(b.url)
fn handle(req: http.Request, d: sqlite.Db) http.Response:
params: {str: str} = {}
if req.path == "/bookmarks":
if req.method == "GET":
return list_all(d)
if req.method == "POST":
return create(d, req.body)
if req.method == "GET" and http.match(req.path, "/bookmarks/{id}", ¶ms):
return visit(d, params["id"])
return http.not_found()
fn main():
d = sqlite.open("bookmarks.db")
d.exec("CREATE TABLE IF NOT EXISTS bookmarks (id INTEGER PRIMARY KEY, url TEXT UNIQUE NOT NULL, title TEXT, visits INTEGER DEFAULT 0)", [])
http.serve(8080, handle, d)Try it with curl:
curl -X POST -d '{"url": "https://example.com", "title": "Example"}' localhost:8080/bookmarks
curl -i localhost:8080/bookmarks/1 # 302 to https://example.com, and counts a visit
curl localhost:8080/bookmarks # [{"id":1,"url":"https://example.com","title":"Example","visits":1}]