SQLite
The SQLite adapter uses the system libsqlite3. It needs no server, so it is also the database for tests.
import Db from "pkg/db/db"
import Sqlite from "pkg/db/sqlite"
def main() -> Void
file = Db.open("sqlite:app.db", [Sqlite.driver()])
memory = Db.open("sqlite::memory:", [Sqlite.driver()])
puts(file.ok?)
puts(memory.ok?)
Db.close(memory.unwrap!)
Db.close(file.unwrap!)
endtrue
truesqlite:PATH opens the file at PATH and creates it when it does not exist. sqlite::memory: is a private database that lives as long as its connection: each connection to :memory: has its own.
Settings #
A connection is set up for a web app. The parameters after a ? change the settings:
| Parameter | Values | Default |
|---|---|---|
journal_mode | wal, delete, truncate, persist, memory, off | wal |
foreign_keys | on, off | on |
busy_timeout | milliseconds, 0 or more | 5000 |
timeout_ms | milliseconds, the limit of each statement | none |
synchronous | off, normal, full, extra | normal in WAL, SQLite’s full in other modes |
mmap_size | bytes, 0 or more | 134217728 (128 MiB) |
cache_size | pages, or KiB when negative | 2000 pages |
journal_size_limit | bytes, negative for no limit | 67108864 (64 MiB) |
fts5 | required | not checked |
statement_cache | prepared statements kept per connection, 0 to 10000 | 100 |
batch_rows | rows that one call of the native driver gives, 1 or more | 1000 |
batch_bytes | bytes of values that one call gives, about, 1 or more | 4194304 (4 MiB) |
import Db from "pkg/db/db"
import Sqlite from "pkg/db/sqlite"
def main() -> Void
case Db.open("sqlite::memory:?fts5=required", [Sqlite.driver()])
error(message) -> puts(message)
ok(conn) ->
created = Db.execute(conn, "create virtual table notes using fts5(body, tokenize = porter)", [])
puts(created.ok?)
Db.close(conn)
end
endtruejournal_mode=wallets readers run while one connection writes, which a web server needs. The mode is stored in the database file, so the file stays in WAL mode for any later connection. While a connection is open, the file has-waland-shmfiles beside it, and recent writes may live only in-wal: copying the.dbfile alone then can lose data. Close the connection first, or back up with SQLite’s backup API (VACUUM INTO 'copy.db'also works).synchronous=normalis the default in WAL, as in Rails 8: a commit does not wait for the WAL file to reach the disk, only a checkpoint does. After a power loss or an operating system crash, the last commits can be lost; the database is never corrupted. A crash of the program alone loses nothing, since the data is in the operating system’s cache. Use?synchronous=fullto sync the WAL file at each commit, at the cost of a disk flush per transaction (extrais the same in WAL; it only adds a directory sync in the rollback modes). In the other journal modes the default stays SQLite’s ownfull.offhands everything to the operating system and can corrupt the database on a power loss.mmap_size,cache_sizeandjournal_size_limitare the values of Rails 8, and SQLite’s own are0(no memory-mapped reads),-2000(2000 KiB; a positive number is a count of pages, 4096 bytes each by default, so the default here is about 8 MiB) and-1(the-walfile keeps its size after a checkpoint).mmap_sizeis capped by the limit that SQLite was built with, and a smaller build value is not an error. They are set on every connection, also on an in-memory database and on a read-only file, which accept them.- A database that cannot use WAL (a network file system) is an error, not a silent fallback. An in-memory database always stays in
memorymode and is accepted. A database that cannot be written (a read-only file, ormode=roin afile:name) keeps the mode it has when the url did not name one;journal_mode=walon it issqlite error 8: attempt to write a readonly database. A plain path that cannot take WAL is an error, so that a web app stops at startup and does not run in another mode; afile:name is more forgiving, since its own parameters can rule WAL out. foreign_keys=onmakes SQLite check foreign keys. SQLite itself leaves them off.busy_timeoutis how long eachprepareor step of a statement waits for a lock held by another connection before it fails withsqlite error 5: database is locked(sqlite error 6: database table is lockedfor a table of a shared cache). The wait is per call, so a statement that waits again at a later step can wait up tobusy_timeoutonce more. The task sleeps a few milliseconds and tries again, so the other tasks, the one that holds the lock among them, keep running;0fails at once. SQLite’s own busy timeout stays 0 (PRAGMA busy_timeoutshows 0): its wait would hold one thread of the pool that runs the SQLite calls, which has one thread per scheduler, and a few schedulers would all wait while the lock holder could not finish. RunningPRAGMA busy_timeout = Nyourself brings that blocking wait back. Afile:name always waits 5000 ms.- A transaction that starts with a plain
BEGIN, reads, and then writes does not wait for a lock: if another connection holds the write lock, the write fails at once withsqlite error 5: database is locked, as in SQLite itself. Injournal_mode=deletewaiting would stall both connections: the other one’sCOMMITwaits for this transaction’s read lock untilbusy_timeoutends, and the write then fails all the same. In WAL mode there is no such stall (a commit does not wait for readers); the write is refused at once there too, since waiting would fail if the other connection commits: the snapshot of the reader is then stale. Roll back and start again, or useDb.transaction, which begins withBEGIN IMMEDIATEand takes the write lock first (asdefault_transaction_mode: immediatedoes in Rails). The refusal needs SQLite 3.34 or later; an older library waits as before. fts5=requiredmakesopenfail withsqlite error: this SQLite was built without FTS5 (fts5=required)when the system library has no FTS5, instead of failing later at the firstcreate virtual table.- Names and values are lowercase and case-sensitive (
journal_mode=WALis an error, although SQLite itself takes it), and a value is not percent-decoded. A repeated name keeps its last value. A parameter that is not in the table, an empty one (?&x=1), one without=or a value that is not allowed issqlite error: .... sqlite:app.db?journal_mode=delete&foreign_keys=off&synchronous=full&mmap_size=0&cache_size=-2000&journal_size_limit=-1is SQLite’s own behavior before these defaults.
Drivers #
Two drivers run the statements, with the same results and errors:
- The native driver is a small C module of the package (
src/db/native/db_sqlite.c) that opens the system SQLite itself. Each connection keeps up tostatement_cacheprepared statements, keyed by the sql text: a statement that ran before is not parsed again, and the one used least long ago is finalized when the cache is full. A cached read (select,with,values,PRAGMA data_versionorPRAGMA user_version) runs whole in one short call on the scheduler while it stays small (about 100 µs of SQLite work, 100 rows, and 256 KiB of rows or of parameters) and never waits there for another task; acount(*)of a whole table and any other statement run on the compute pool, one call perbatch_rowsrows orbatch_bytesbytes. The pragmas thatopenruns are not cached. Closing the connection finalizes its statements.Db.statement_cache(conn)gives the counters of the cache (size,capacity,hits,misses,evictions,shorts,fallbacks), and none on the FFI driver. - The FFI driver calls
libsqlite3throughexternfunctions: it prepares each statement at its call and steps it one row per call. Each query costs about ten times as much.
In WAL mode the native driver runs short writes on the scheduler too. The BEGIN of Db.transaction, and a cached insert, update, delete or replace inside a transaction, run in one short call when the plan of the statement bounds its work (it finds its rows by key: no scan of a table or of a range of keys, no trigger, no sort, no virtual table such as FTS5), its last run took at most the 10000 SQLite instructions of a short read, and the write lock is free. A statement that would wait for the lock goes to the compute pool, which waits for it, so a short call never waits. So does a write that makes or reads a value or row over 256 KiB (the statement alone fails there and runs again on the compute pool), and every write of a transaction after 64 short ones or one on the compute pool. Any other write, a write outside a transaction and the COMMIT run on the compute pool, since a COMMIT can checkpoint the WAL, which copies it into the database file. In a pool a checkpointer takes the checkpoints off the commits, and then the COMMIT of a transaction of short writes and the cheap writes outside a transaction run short too. The FFI driver runs every write on the compute pool.
open takes the native driver when its library loads, else the FFI one. Agentica builds the library with the system C compiler (cc, or CC; no SQLite headers) at the first run or agentica compile of a program that imports the SQLite driver (pkg/db/sqlite), from a git dependency or a path one, and keeps it in its package cache for the next runs: native-build/ of $XDG_CACHE_HOME/agentica/pkg (by default ~/.cache/agentica/pkg), or of %LOCALAPPDATA%/agentica/cache/pkg on Windows. Without a compiler (or when the build fails) Agentica prints warning: native module db_sqlite not built: REASON; the package runs without it and the program runs the FFI driver; with no built library to cache, each run tries the build again and warns again, and the bytecode of agentica compile runs the FFI driver until it is compiled again with a compiler. A program that imports only another driver builds nothing for SQLite. The bytecode of agentica compile names the built library by its path and runs without a compiler; where that file is missing it runs the FFI driver. AGENTICA_DB_SQLITE_DRIVER=native makes a missing library an error of open, and AGENTICA_DB_SQLITE_DRIVER=ffi always runs the FFI driver.
On Debian or Ubuntu, apt-get install gcc libc6-dev gives the compiler, and libsqlite3-0 the SQLite of the run. In a Docker image either keep the compiler in the image that runs the program, or run agentica compile in a build stage and copy the bytecode and the package cache, at the same path, into the final image.
Timeouts #
timeout_ms (a url parameter, or the timeout_ms argument of Db.query and Db.execute: -1 takes the connection’s, 0 has none) ends a statement that runs longer. A watchdog task calls sqlite3_interrupt when the time is over, and the statement fails with sqlite error 9: interrupted; Db.timeout?(e.message) tells it from other failures. The connection works after it. The wait for a lock (busy_timeout) ends at the same deadline and fails the same way.
SQLite rolls back the whole transaction when it interrupts a write inside one, the earlier writes of the transaction included: Db.in_transaction?(conn) is then false. Db.transaction sends its ROLLBACK only when the transaction is still open, so its error is the timeout alone. A read that times out leaves the transaction open, and so does a statement whose time ran out before its first step: it does not run, so nothing is rolled back.
SQLite URIs #
A name that starts with file: is a SQLite URI and goes to SQLite as it is: its ? parameters belong to SQLite, and the table above does not apply to it. sqlite:file:app.db?mode=ro opens read-only, and sqlite:file::memory:?cache=shared is an in-memory database that several connections share. The defaults still apply (foreign_keys on, WAL tried, and the synchronous, mmap_size, cache_size and journal_size_limit of the table: if SQLite will not switch to WAL, for example with immutable=1, nolock=1 or mode=ro, the database keeps its mode and synchronous stays SQLite’s), and to change one, run its PRAGMA after open, such as PRAGMA journal_mode = DELETE, PRAGMA foreign_keys = OFF or PRAGMA synchronous = FULL.
A ? in a path #
A ? always starts the parameters, so a file whose name holds a ? cannot be opened by name. That includes the Windows extended-length prefix \\?\C:\.... Write such a path as a file: URI with the ? as %3f (sqlite:file:C:/dir/a%3fb.db).
What to know #
- Parameters bind with the type of the
Db.Value. ADb.Value.Boolis stored as 0 or 1 and reads back asDb.Value.Int, and NaN is stored as NULL. See values. - A text is bound with its length, so a text with a NUL is stored and read back whole.
- A transaction starts with
BEGIN IMMEDIATE, which needs write access to the database. executegives the rows changed by aninsert,updateordelete.- An engine failure is
sqlite error <code>: <message>, as insqlite error 1: near "from": syntax error. A bad input issqlite error: <message>. - A connection used after
closeis a runtime error (RUNTIME-NATIVE-CLOSED), not anerrorvalue.