sqlite

On this page:

• spider.sqlite: SQLite: init, query, queryOne, queryExecute, begin. Opt-in with -Dsqlite=true.

spider.sqlite

SQLite for an app (spider.sqlite, opt-in with -Dsqlite=true): one database per process, opened by init, then query, queryOne, queryExecute and begin from anywhere. Rows come back as structs whose fields are matched to columns by name.

spider.sqlite.DbConfig

pub const DbConfig = struct

The options of init. .{} opens the file named by SQLITE_PATH with a pool of five connections.

Fields

• path: ?[]const u8 = null: The database file, or ":memory:". null (the default): the SQLITE_PATH variable, or "db.sqlite" when it is not set.
• size: usize = 5: Connections in the pool. Default 5. With 1 there is no pool: every call uses the same connection, which is what an in-memory database needs (each connection to ":memory:" is a database of its own).

spider.sqlite.init

pub fn init(allocator: std.mem.Allocator, io: std.Io, overrides: DbConfig) !void

Opens the database, creating the file when it does not exist. Call it once at startup, before any query, and deinit() at exit. allocator must outlive the database. Fails with the SQLite error when the file can't be opened.

try spider.sqlite.init(allocator, io, .{});
defer spider.sqlite.deinit();

spider.sqlite.deinit

pub fn deinit() void

Closes every connection. No query may run after it.

spider.sqlite.MappingError

pub const MappingError = error{ ColumnMissing, UnexpectedNull, IntegerOverflow }

What a row can fail to give a struct: no column with the field's name, a NULL where the field cannot hold one, or an integer that does not fit the field's type.

spider.sqlite.query

pub fn query(comptime T: type, arena: std.mem.Allocator, sql: []const u8, params: anytype) !QueryResult(T)

Runs one statement with ? parameters and gives back what T asks for:

• a struct: every row as a []T allocated in arena. A field takes the column of the same name. Fields may be []const u8 (copied into arena), bool (true for the integer 1), integers (i8 to i64, u8 to u64), f32, f64, enums (stored as their name; error.InvalidEnumValue for another text) and optionals of those. A stored integer that does not fit the field's type is error.IntegerOverflow.
• i64: the first column of the first row, 0 when there is no row.
• void: nothing; for INSERT, UPDATE and DELETE.

Fails with the SQLite error of the statement (a constraint, a missing table, a syntax error), and with a MappingError when a row does not fit T: error.ColumnMissing for a field with no column of its name, error.UnexpectedNull for a NULL column. A field that is optional takes null in both cases, and a field with a default takes its default; any other field is the error. The same rule as spider.pg.

const posts = try spider.sqlite.query(Post, c.arena, "SELECT id, title FROM posts WHERE author = ?", .{author});
try spider.sqlite.query(void, c.arena, "DELETE FROM posts WHERE id = ?1", .{id});

spider.sqlite.queryOne

pub fn queryOne(comptime T: type, arena: std.mem.Allocator, sql: []const u8, params: anytype) !?T

The first row of a statement as a struct T, or null when it returns no row. Fields are mapped as in query; strings are allocated in arena.

const post = try spider.sqlite.queryOne(Post, c.arena, "SELECT id, title FROM posts WHERE id = ?", .{id}) orelse return error.NotFound;

spider.sqlite.queryExecute

pub fn queryExecute(comptime T: type, _: std.mem.Allocator, sql: []const u8) !QueryResult(T)

Runs a script: one or more statements separated by ;, without parameters (DDL, migrations). Triggers and ; inside strings or comments are fine. Pass void as T: no rows are returned (a struct T gives an empty slice). The allocator argument is not used.

try spider.sqlite.queryExecute(void, arena, "CREATE TABLE IF NOT EXISTS posts (id INTEGER PRIMARY KEY, title TEXT)");

spider.sqlite.exec

pub fn exec(sql: []const u8) !void

Runs a script without parameters: queryExecute(void, ...) without the allocator argument.

spider.sqlite.Transaction

pub const Transaction = struct

A transaction on one connection, made by begin(). End it with commit() or rollback(); a call after the first does nothing.

var tx = try spider.sqlite.begin();
defer tx.rollback();
try tx.query(void, c.arena, "INSERT INTO posts (title) VALUES (?1)", .{title});
try tx.commit();

Fields

• conn: zqlite.Conn: The connection the transaction runs on.
• finished: bool = false: The transaction was committed or rolled back: its connection went back to the pool and must not be given back again.

sqlite.Transaction.query

pub fn query(self: Transaction, comptime T: type, arena: std.mem.Allocator, sql: []const u8, params: anytype) !QueryResult(T)

The same as the module's query, inside this transaction.

sqlite.Transaction.commit

pub fn commit(self: *Transaction) !void

Commits and gives the connection back. When the commit fails the connection is still held: call rollback().

sqlite.Transaction.rollback

pub fn rollback(self: *Transaction) void

Undoes everything since begin() and gives the connection back. After a commit() or another rollback() it does nothing, so defer tx.rollback() right after begin() is safe.

spider.sqlite.begin

pub fn begin() !Transaction

Starts a transaction on a connection that stays reserved until commit() or rollback(). Statements run through the module's query meanwhile are not part of it when the pool has more than one connection.

var tx = try spider.sqlite.begin();
try tx.query(void, arena, "INSERT INTO posts (title) VALUES (?)", .{title});
try tx.commit();