std/db/sqlite is a database engine for SQLite’s file format and SQL
dialect, written entirely in Dodo. It links no native library: the
same .db file can be opened by Dodo programs, by the sqlite3 command-line
tool, and by any other program built on SQLite, even at the same time. Like
the rest of the standard library, it works inside storage you provide and
never allocates.
Two small packages sit next to it:
| Import | Contents |
|---|---|
std/db |
Shared vocabulary for every database driver: db.Error (portable db.ErrorKind, db.Constraint, and the driver’s native code), the borrowed db.Value, db.IsolationLevel, and the connection pool db.Pool (see PostgreSQL). |
std/db/sql |
Interfaces that every SQL driver matches: sql.Conn, sql.Row, and sql.RowSink, the ready-made sinks sql.Count and sql.ScalarInt, and the value conversions behind @derive(Row). |
std/db/sqlite |
The SQLite driver: sqlite.Connection, prepared statements, row cursors. |
Quickstart
package notes
import "std/db"
import "std/db/sqlite"
static mut WORKSPACE: [sqlite.WORKSPACE_BYTES]u8 = [0u8; sqlite.WORKSPACE_BYTES]
fn main() -> i32 {
// ":memory:" keeps the database in the workspace; pass a file path to
// keep it on disk.
conn := sqlite.Connection.open_memory(unsafe { &mut WORKSPACE[..] })!
conn.exec("create table notes(id integer primary key, title text, stars int)")!
params := [db.Value.Text("buy milk"), db.Value.Int(3)]
conn.execute("insert into notes(title, stars) values (?, ?)", ¶ms)!
conn.exec("insert into notes(title, stars) values ('call home', 5)")!
none_: [0]db.Value = []
rows := conn.select("select title, stars from notes order by stars desc", &none_)!
let some(first) = rows.next()! else {
return 1
}
assert_eq(first.text(0), "call home")
assert_eq(first.int(1), 5i64)
return 0
}
Connection.open(path, workspace) opens or creates a database file instead;
conn.close() closes it (dropping the connection does the same, ignoring
errors). Every value read from a row borrows the workspace and stays valid
until the cursor moves on.
The workspace
A connection keeps everything in the byte slice you pass to open: the page
cache, the parsed statements, the values of the current row, and the space for
sorting and grouping. sqlite.WORKSPACE_BYTES (2 MiB) suits most programs.
The smallest workspace depends on the page size:
sqlite.min_workspace_bytes(page_size) returns it, and
sqlite.MIN_WORKSPACE_BYTES (424 KiB) covers SQLite’s default 4096-byte
pages. Databases with 65536-byte pages fit in the default 2 MiB workspace.
Larger workspaces cache more pages and can sort, group, and materialize larger
results. Put a large workspace in a static mut array rather than on the
stack.
The regions are sized when the connection opens, from the database’s page
size. PRAGMA page_size on a database that does not exist yet lays the
workspace out again for the new size before the next statement; prepared
statements are recompiled automatically.
When a region is too small for a request, for example a single value
larger than the value area or an ORDER BY over more rows than the sorter
holds, the statement fails with db.ErrorKind.Capacity, rolls back, and
conn.message() names the region. Query results are never spilled to
temporary files. A single-table query ordered by its rowid (or INTEGER
PRIMARY KEY), ascending or descending, reads the table in that order and
needs no sorter space.
Running statements
| Call | Use |
|---|---|
conn.exec(sql) |
Run one or more statements, discarding any rows. |
conn.execute(sql, ¶ms) |
Run one statement; returns the number of changed rows. |
conn.select(sql, ¶ms) |
Run one query and get a Rows cursor. |
conn.query(sql, ¶ms, &mut sink) |
Run one query and pass every row to an sql.RowSink. |
conn.prepare(sql) |
Parse once; then conn.run(&stmt, ¶ms) or conn.rows(&stmt, ¶ms) repeatedly. |
conn.finalize(stmt) |
Release a prepared statement’s slot. |
conn.statement_length(sql) |
Length of the first statement of a script, for splitting scripts yourself. |
Parameters use SQLite’s syntax: ? (numbered left to right), ?NNN,
:name, @name, and $name. params[i] binds parameter i + 1, unbound
parameters are NULL, and conn.parameter_index(&stmt, ":name") finds a
named parameter’s number. Text and blob parameters are copied, so the slice
only has to live for the call.
Arrays and slices you pass must be bound to a local first: write
params := [db.Value.Int(1)] and pass ¶ms, and use an empty
none_: [0]db.Value = [] for statements without parameters.
Prepared statements live in sqlite.STATEMENT_SLOTS slots; preparing more
reuses the least recently used slot and invalidates that handle (run and
rows then fail with db.ErrorKind.Misuse). A statement is recompiled
automatically when the schema changes.
Reading rows
rows.next() returns ok(some(row)) for each row and ok(none) at the end.
Only one statement runs at a time: the Rows cursor borrows the connection,
and the statement completes when the cursor reaches the end or is dropped.
Row method |
Result |
|---|---|
len(), name(i) |
Column count and the column’s name (alias, column name, or expression text). |
get(i) |
db.Value: Null, Int, Real, Text, or Bytes (text that is not valid UTF-8 comes back as Bytes). |
value_type(i), is_null(i) |
The column’s storage class. |
int(i), real(i) |
The value converted with SQLite’s rules (NULL is 0). |
text(i), bytes(i) |
Borrowed text or blob contents (empty for other types). |
text_into(i, &mut buf) |
Any value formatted as SQLite would print it, including reals. |
conn.changes(), conn.total_changes(), and conn.last_insert_rowid()
report the effects of the last statements, as their SQLite counterparts do.
Mapping rows to structs
@derive(Row) generates the code that turns a result row into a struct and
a struct into statement parameters. It works with every SQL driver, because
the generated methods only use std/db/sql:
package notes
import "std/db"
import "std/db/sqlite"
static mut WORKSPACE: [sqlite.WORKSPACE_BYTES]u8 = [0u8; sqlite.WORKSPACE_BYTES]
@derive(Row)
struct Note {
id: i64
title: &str
@db_name("stars")
rating: u8
// NULL becomes none.
due: Option<&str>
}
fn main() -> i32 {
conn := sqlite.Connection.open_memory(unsafe { &mut WORKSPACE[..] })!
conn.exec("create table notes(id integer primary key, title text, stars int, due text)")!
note := Note { id: 1, title: "buy milk", rating: 3, due: none }
params := note.params()
conn.execute("insert into notes(id, title, stars, due) values (?, ?, ?, ?)", ¶ms)!
none_: [0]db.Value = []
rows := conn.select("select * from notes", &none_)!
for {
let some(row) = rows.next()! else {
break
}
found := Note.decode_row(&row)!
assert_eq(found.title, "buy milk")
assert_eq(found.rating, 3u8)
}
return 0
}
The derive adds two ordinary, type- and borrow-checked methods:
decode_row<R: sql.Row>(row: &R) -> Self!db.Errorfinds each field’s column by name (exact, case-sensitive;@db_name("column")overrides the field name) and converts its value. Column order does not matter, and extra columns are ignored. When the statement lists columns in field order, each field costs a single name comparison.Rcan be a driver’s row type ordyn sql.Row, so asql.RowSinkcan decode its rows too.params(&self) -> [N]db.Valuereturns the fields in declaration order, ready to pass toexecute,run, orselect.
&str and &[u8] fields borrow the row: decoding copies nothing, and the
compiler rejects code that keeps a decoded struct after the cursor moves on
or after the workspace goes away. Copy what you need to keep into storage of
your own.
| Field type | Column value | Parameter |
|---|---|---|
i64, isize, i8 to i32, u8 to u32 |
Integer within the field’s range | Int |
bool |
Integer; nonzero is true |
Int 0 or 1 |
f64, f32 |
Real or integer (for f32, finite and in range) |
Real |
&str |
Text | Text |
&[u8] |
Blob, or the bytes of a text value | Bytes |
Option<T> |
NULL gives none, anything else decodes as T |
Null for none |
A struct with decode_value/encode_value |
Whatever that struct accepts | encode_value() |
u64 and usize are rejected at compile time, because SQL integers are
signed 64-bit values. A missing column is a db.ErrorKind.Schema error. A
NULL in a non-optional field, a value of the wrong type, or a number
outside the field’s range is a db.ErrorKind.Type error, never a trap.
For column types of your own, such as money or timestamps, write a struct with these two methods and use it as a field type:
pub struct Cents {
pub amount: i64
pub fn decode_value(value: db.Value) -> Self!db.Error {
ok(Cents { amount: sql.int(value)? })
}
pub fn encode_value(&self) -> db.Value {
db.Value.Int(self.amount)
}
}
Mark a field @db_flatten to read a nested @derive(Row) struct from the
same row. This is useful for joins: select users.*, teams.name as team ...
can fill struct Member { @db_flatten user: User, team: &str }. A struct with
flattened fields only gets decode_row, because the nested struct’s
parameter count is not known where the outer struct is declared.
The conversions are plain functions in std/db/sql (sql.column,
sql.int, sql.int_in, sql.real, sql.real32, sql.boolean, sql.text,
sql.bytes, sql.is_null), so a hand-written decode_row follows the same
rules as a derived one:
fn decode_row<R: sql.Row>(row: &R) -> Self!db.Error {
count := sql.int(row.get(sql.column(row, "count", 0)?)?)?
ok(Totals { count })
}
Transactions
Outside a transaction every statement commits on its own. Wrap several
statements in BEGIN/COMMIT (or conn.begin(level), conn.commit(),
conn.rollback()) to make them atomic; that is also much faster for many
inserts, because each commit synchronizes the file.
conn.exec("begin")?
insert := conn.prepare("insert into notes(title) values (?)")?
for &title in titles {
p := [db.Value.Text(title)]
conn.run(&insert, &p)?
}
conn.exec("commit")?
As in SQLite, a statement that fails inside a transaction undoes only its own
changes, and the transaction stays open. BEGIN IMMEDIATE (and
conn.begin(db.IsolationLevel.Serializable)) takes the write lock at once,
so a later write cannot fail because another process holds it.
SAVEPOINT name, RELEASE [SAVEPOINT] name, and ROLLBACK TO [SAVEPOINT] name nest transactions as in SQLite: ROLLBACK TO undoes everything since
the savepoint and leaves it open, RELEASE keeps the changes, and a
SAVEPOINT outside BEGIN starts a transaction that the release of the
outermost savepoint commits. The page images that savepoints and failing
statements need for undo stay in the workspace; when a large transaction on
a file database outgrows that area, they continue in a <database>-stmtjrnl
file that is deleted when the transaction ends. (Without a savepoint, an
in-memory database that outgrows the area rolls back the whole transaction
when a statement fails; conn.in_transaction() then reports false.)
When another process holds a conflicting lock, the operation fails with
db.ErrorKind.Busy (“database is locked”). Options.busy_timeout_ms,
conn.set_busy_timeout(ms), or PRAGMA busy_timeout = ms make the
connection wait and retry for up to that long first.
Attached and temporary databases
ATTACH 'file' AS name opens another database file on the same connection
(up to ten, as in SQLite; ':memory:' or '' attaches an in-memory
database), and DETACH name closes it. Tables, indexes, views, and triggers
are then named name.object; an unqualified name means the TEMP database
first, then main, then the attached databases in attach order. PRAGMA database_list lists them, and pragmas such as integrity_check,
table_info, page_count, or journal_mode accept a name. prefix
(unqualified journal_mode = ... applies to every database, as in SQLite).
conn.exec("attach 'archive.db' as archive")?
conn.exec("insert into archive.orders select * from orders where year < 2020")?
conn.exec("detach archive")?
CREATE TEMP TABLE (and TEMP VIEW, TEMP TRIGGER, and indexes on temp
tables) put objects into the temp database, which is private to the
connection and disappears when it closes. Its pages stay in the workspace’s
page cache, as with temp_store = MEMORY, so the workspace bounds its size.
A temp trigger may fire on a table of another database, and a temp object
may shadow one of the same name in main.
A transaction covers every database it writes. When it writes more than one
file in rollback-journal mode, the commit goes through a super-journal
(<main>-mjXXXXXXXX) exactly as in SQLite, so after a crash either every
file has the transaction or none does, and Dodo and SQLite each recover the
other’s interrupted multi-file commits. (In WAL mode, as in SQLite, the
commit is atomic per file only.) Statements lock only the databases they
use. Attached databases must use the main database’s text encoding; a new
attached file takes it.
WAL mode
PRAGMA journal_mode = WAL switches a database file to write-ahead logging,
exactly as SQLite does (the setting is stored in the file, so it stays on for
every later connection, Dodo or SQLite). Commits then append the changed
pages to <file>-wal instead of rewriting the database, and readers keep
reading a consistent snapshot while a writer commits, so readers and the
single writer no longer block each other.
conn := sqlite.Connection.open("app.db", unsafe { &mut WORKSPACE[..] })?
conn.exec("pragma journal_mode = wal")?
A checkpoint copies committed pages from the log back into the database
file. One runs automatically after a commit leaves 1000 or more frames in
the log (PRAGMA wal_autocheckpoint = N changes the threshold; 0 turns it
off), and when the last connection to the file closes; that connection then
deletes the -wal and -shm files. PRAGMA wal_checkpoint runs one now and
returns SQLite’s three columns: busy (1 if it could not finish because of
other connections), log (frames in the log) and checkpointed (frames
copied so far). The argument selects SQLite’s modes: PASSIVE (default,
never waits), FULL (waits for the writer and for readers of older
snapshots, up to the busy timeout), RESTART (as FULL, then waits until
the next writer can start the log over) and TRUNCATE (as RESTART, and
truncates the log file). PRAGMA journal_mode = DELETE returns to rollback
journaling; it needs every other connection to have closed the file and
fails with Busy otherwise. Neither conversion, nor a checkpoint, can run
inside a transaction.
In-memory databases ignore the setting and report memory, as in SQLite.
Everything follows SQLite’s documented formats, so Dodo and SQLite processes
can work on one WAL database at the same time: the log (frames, salts, and
checksums in either byte order), the shared -shm index with its hash
tables and read marks, and SQLite’s WAL locks on it. Without memory-mapped
files, the engine reads and writes the index with ordinary file I/O and
relies on its double header copy, checksums, and retry rules. A connection
that finds the index stale or damaged (for example after a crash) rebuilds it
from the log, keeping only committed transactions.
Errors
Failures return db.Error. Its kind is portable (Syntax, Constraint,
Busy, ReadOnly, Capacity, Corrupt, Io, …), constraint says which
constraint rejected a change (Unique, PrimaryKey, NotNull, Check),
code is SQLite’s extended result code, and position is the byte offset of
a syntax error. conn.message() returns SQLite’s wording:
match conn.exec("insert into users(email) values ('a@example.com')") {
ok() => {},
err(e) => {
if e.constraint == db.Constraint.Unique {
// "UNIQUE constraint failed: users.email"
console.println(conn.message())?
}
},
}
Code that works with any SQL driver
std/db/sql describes connections and rows with interfaces, so code written
against them works with this driver and with future ones. Generic code is
specialized per driver at compile time; &mut dyn sql.Conn selects the driver
at run time.
fn count<C: sql.Conn>(conn: &mut C, table_sql: &str) -> i64!db.Error {
result := sql.ScalarInt.new()
none_: [0]db.Value = []
conn.query(table_sql, &none_, &mut result)?
ok(result.value)
}
A sink is any value with pub fn row(&mut self, row: &dyn sql.Row) -> void!db.Error; the row and its borrowed values are valid during that call.
Opening options
sqlite.Options configures Connection.open_with(path, workspace, &options):
| Field | Default | Meaning |
|---|---|---|
read_only |
false |
Open without write access; writes fail with db.ErrorKind.ReadOnly. |
create |
true |
Create the file if it does not exist. |
busy_timeout_ms |
0 |
How long to wait for other processes’ locks before reporting Busy. |
page_size |
4096 |
Page size of a new database file (512 to 65536). |
Compatibility
The engine implements SQLite’s documented file format and semantics. The
test suite runs the same SQL scripts through SQLite and through this driver,
compares every result row, checks the files Dodo writes with SQLite’s
PRAGMA integrity_check, and reads SQLite-written files back through Dodo.
It also covers crash recovery in both directions (each recovers the other’s
interrupted transaction from its hot journal or its write-ahead log), the
lock protocol between the two, and Dodo and SQLite processes reading,
writing, and checkpointing one WAL database concurrently.
Files. All page sizes; overflow pages; the freelist; rollback-journal
transactions with hot-journal recovery; WAL mode with its -wal log and
-shm index; SQLite’s lock bytes through
fs.File.try_lock_range; UTF-8, UTF-16le, and UTF-16be
databases; schema formats 1 to 4.
SQL. CREATE/DROP TABLE (including WITHOUT ROWID tables),
INDEX (including UNIQUE, indexes on expressions, partial indexes with
WHERE, and the automatic indexes behind PRIMARY KEY and UNIQUE
constraints), VIEW, and TRIGGER (BEFORE, AFTER, and INSTEAD OF on
views; INSERT, DELETE, UPDATE [OF columns]; WHEN; RAISE; nested
triggers, with recursive firing off as in SQLite’s default);
ALTER TABLE RENAME TO, RENAME COLUMN, ADD COLUMN, and DROP COLUMN
(references in views, triggers, and foreign keys follow renames, as with
SQLite’s default legacy_alter_table = OFF); INSERT
(including INSERT ... SELECT, DEFAULT VALUES, OR IGNORE/REPLACE/…,
and UPSERT), UPDATE, DELETE, each with RETURNING; SELECT with joins
(inner, LEFT, CROSS, USING), subqueries (scalar, IN, EXISTS, in
FROM), compound selects (UNION [ALL], INTERSECT, EXCEPT), WITH and
WITH RECURSIVE, DISTINCT, aggregates, GROUP BY/HAVING,
ORDER BY ... NULLS FIRST/LAST, and LIMIT/OFFSET; BEGIN
(DEFERRED/IMMEDIATE/EXCLUSIVE), COMMIT, ROLLBACK, SAVEPOINT,
RELEASE, ROLLBACK TO; constraints
NOT NULL, UNIQUE, PRIMARY KEY, CHECK, DEFAULT, COLLATE,
AUTOINCREMENT; INTEGER PRIMARY KEY rowid aliases; collations BINARY,
NOCASE, and RTRIM; type affinity and comparison rules as in SQLite.
Functions. The core scalar functions (abs, coalesce, ifnull, iif,
instr, length, like, glob, lower, upper, ltrim, rtrim, trim,
max, min, nullif, printf/format, quote, random, randomblob,
replace, round, sign, substr, typeof, hex, unhex, unicode,
char, zeroblob, concat, concat_ws, octet_length, changes,
last_insert_rowid, total_changes, sqlite_version, …); the aggregates
count, sum, total, avg, min, max, group_concat/string_agg
(with DISTINCT and FILTER (WHERE ...)); and the date and time functions date, time,
datetime, julianday, unixepoch, strftime with their modifiers. The
localtime and utc modifiers use the operating system’s time zone rules
(localtime_r, or _localtime64_s on Windows), as SQLite does, including
its handling of times in a daylight-saving gap or overlap and its mapping of
years outside 1970 to 2037 onto a year with the same leap-year position.
Reals print and parse exactly as in SQLite 3.53 and later: CAST to
text (printf('%!.17g'): 17 significant digits, fewer where they end in a
run of 9s or 0s and the shorter form reads back), printf’s %f, %e, %g (with the ! and
# flags), quote, round, and text-to-real conversion all reproduce
SQLite’s own decimal rounding digit for digit, not just correctly rounded
values. Older SQLite versions round the last digit differently in places.
Text encodings. PRAGMA encoding = 'UTF-16le' (or 'UTF-16be', or
'UTF-16' for the native byte order) before a new database’s first table
creates a UTF-16 database; PRAGMA encoding reports the encoding of the
database, and an existing database keeps its own. Text in records and in
the schema is stored in the database’s encoding, and as in SQLite the
BINARY collation then orders text by its UTF-16 bytes (which differs from
UTF-8 order for non-ASCII text), while NOCASE and RTRIM compare UTF-8.
hex(text), CAST(text AS BLOB), octet_length, and blob-to-text
conversions see UTF-16 bytes; conversions handle invalid sequences as SQLite
does. The API binds and returns text as UTF-8 for every encoding. One
difference remains: SQLite keeps the bytes of a blob cast to text in a UTF-16
database unchanged, while Dodo converts them, so an invalid UTF-16 sequence
stored that way (an unpaired surrogate or an odd byte count) is normalized
rather than kept byte for byte.
Window functions. fn(...) [FILTER (WHERE ...)] OVER (...) and
OVER name with WINDOW name AS (...) clauses (a definition may build on an
earlier one); PARTITION BY, ORDER BY (with NULLS FIRST/LAST and
collations), and ROWS, RANGE (numeric offsets on one ORDER BY term), or
GROUPS frames with UNBOUNDED PRECEDING, n PRECEDING, CURRENT ROW,
n FOLLOWING, UNBOUNDED FOLLOWING and EXCLUDE NO OTHERS/CURRENT ROW/GROUP/TIES. The functions are row_number, rank, dense_rank,
percent_rank, cume_dist, ntile, lag, lead, first_value,
last_value, nth_value, and every aggregate above (without DISTINCT).
Windows run over the rows left after WHERE, GROUP BY and HAVING
(aggregates may appear in window arguments), may be used in ORDER BY, and
combine with DISTINCT, LIMIT, compound selects, subqueries, views, and
CTEs. Default frames, error messages, row order, and the results of sliding
frames (including SQLite’s incremental sum and group_concat behaviour)
follow SQLite. A window query materializes its rows in the sorter, so the
workspace bounds its size.
Pragmas. user_version, application_id, schema_version,
page_size, page_count, freelist_count, encoding, journal_mode
(DELETE and WAL; other rollback modes behave as DELETE),
wal_checkpoint, wal_autocheckpoint, table_info, table_list, index_list, index_info, integrity_check,
quick_check, busy_timeout, database_list, legacy_alter_table; others
are accepted and
ignored, as SQLite does for unknown pragmas.
Not supported. Virtual tables.
Concurrency. Locks follow SQLite’s protocol, so Dodo and SQLite processes
can use one file safely. As with SQLite’s POSIX locking, two connections to
the same file inside one process interfere: closing either releases the
other’s locks. Open one connection per file per process. (In WAL mode the
engine at least keeps a second connection of the same process from resetting
the -shm index or deleting the log under the first.) The WAL index is
written in the host’s byte order, as SQLite does, so it is shared only
between processes on the same machine.
Complete API reference
For every public type and signature, see std/db, std/db/sql, and std/db/sqlite.