// good idea to pass EXResCode to get extended result codes (more detailed error codes)
const flags = zqlite.OpenFlags.Create | zqlite.OpenFlags.EXResCode;
var conn = try zqlite.open("/tmp/test.sqlite", flags);
defer conn.close();
try conn.exec("create table test (name text)", .{});
try conn.exec("insert into test (name) values (?1), (?2)", .{"Leto", "Ghanima"});
{
if (try conn.row("select * from test order by name limit 1", .{})) |row| {
defer row.deinit();
std.debug.print("name: {s}\n", .{row.text(0)});
}
}
{
var rows = try conn.rows("select * from test order by name", .{});
defer rows.deinit();
while (rows.next()) |row| {
std.debug.print("name: {s}\n", .{row.text(0)});
}
if (rows.err) |err| return err;
}
Unless zqlite.OpenFlags.ReadOnly
is set in the open flags, zqlite.OpenFlags.ReadWrite
is assumed (in other words, the database opens in read-write by default, and the ReadOnly
flag must be used to open it in readony mode.)
This library is tested with SQLite3 3.46.1 .
- Add zqlite as a dependency in your
build.zig.zon
:
zig fetch --save git+https://github.com/karlseguin/zqlite.zig#master
- The library doesn't attempt to link/include SQLite. You're free to do this how you want.
If you have sqlite3 installed on your system you might get away with just adding this to your build.zig
const zqlite = b.dependency("zqlite", .{
.target = target,
.optimize = optimize,
});
exe.linkSystemLibrary("sqlite3");
exe.root_module.addImport("zqlite", zqlite.module("zqlite"));
Alternatively, If you download the SQLite amalgamation from the SQLite download page and place the sqlite.c
and sqlite.h
file in your project's lib/
folder, you can then:
- Add this in
build.zig
:
const zqlite = b.dependency("zqlite", .{
.target = target,
.optimize = optimize,
});
exe.addCSourceFile(.{
.file = b.path("lib/sqlite3.c"),
.flags = &[_][]const u8{
"-DSQLITE_DQS=0",
"-DSQLITE_DEFAULT_WAL_SYNCHRONOUS=1",
"-DSQLITE_USE_ALLOCA=1",
"-DSQLITE_THREADSAFE=1",
"-DSQLITE_TEMP_STORE=3",
"-DSQLITE_ENABLE_API_ARMOR=1",
"-DSQLITE_ENABLE_UNLOCK_NOTIFY",
"-DSQLITE_ENABLE_UPDATE_DELETE_LIMIT=1",
"-DSQLITE_DEFAULT_FILE_PERMISSIONS=0600",
"-DSQLITE_OMIT_DECLTYPE=1",
"-DSQLITE_OMIT_DEPRECATED=1",
"-DSQLITE_OMIT_LOAD_EXTENSION=1",
"-DSQLITE_OMIT_PROGRESS_CALLBACK=1",
"-DSQLITE_OMIT_SHARED_CACHE",
"-DSQLITE_OMIT_TRACE=1",
"-DSQLITE_OMIT_UTF16=1",
"-DHAVE_USLEEP=0",
},
});
exe.root_module.addImport("zqlite", zqlite.module("zqlite"));
You can tweak the SQLite build flags for your own needs/platform.
The Conn
type returned by open
has the following functions:
row(sql, args) !?zqlite.Row
- returns an optional rowrows(sql, args) !zqlite.Rows
- returns an iterator that yields rowsexec(sql, args) !void
- executes the statement,execNoArgs(sql) !void
- micro-optimization if there are no args,sql
must be a null-terminated stringchanges() usize
- the number of rows inserted/updated/deleted by the previous statementlastInsertedRowId() i64
- the row id of the last inserted rowlastError() [*:0]const u8
- an error string describing the last errortransaction() !void
andexclusiveTransaction() !void
- begins a transactioncommit() !void
androllback() void
- commits and rollback the current transactionprepare(sql, args) !zqlite.Stmt
- returns a thin wrapper around a*c.sqlite3_stmt
.row
androws
wrap this type.close() void
andtryClsoe() !void
- closes the database.close()
silently ignores any error, if you care about the error, usetryClose()
busyTimeout(ms)
- Sets the busyHandler for the connection. See https://www.sqlite.org/c3ref/busy_timeout.html
Both row
and rows
wrap an zqlite.Stmt
which itself is a thin wrapper around an *c.sqlite3_stmt
.
While zqlite.Row
exposes a deinit
and deinitErr
method, it should only be called when the row was fetched directly from conn.row(...)
:
if (try conn.row("select 1", .{})) |row| {
defer row.deinit(); // must be called
std.debug.print("{d}\n", .{row.int(0)});
}
When the row
comes from iterating rows
, deinit
or deinitErr
should not be called on the individual row:
var rows = try conn.rows("select 1 union all select 2", .{})
defer rows.deinit(); // must be called
while (rows.next()) |row| {
// row.deinit() should not be called!
...
}
Note that zqlite.Rows
has an err: ?anyerror
field which can be checked at any point. Calls to next()
when err != null
will return null. Thus, err
need only be checked at the end of the loop:
var rows = try conn.rows("select 1 union all select 2", .{})
defer rows.deinit(); // must be called
while (rows.next()) |row| {
...
}
if (rows.err) |err| {
// something went wrong
}
A row
exposes the following functions to fetch data:
boolean(index) bool
nullableBoolean(index) ?bool
int(index) i64
nullableInt(index) ?i64
float(index) i64
nullableFloat(index) f64
text(index) []const u8
nullableText(index) ?[]const u8
blob(index) []const u8
nullableBlob(index) ?[]const u8
The nullableXYZ
functions can safely be called on a not null
column. The non-nullable versions avoid a call to sqlite3_column_type
(which is needed in the nullable versions to determine if the value is null or not).
The transaction()
, exclusiveTransaction()
, commit()
and rollback()
functions are simply wrappers to conn.execNoArgs("begin")
, conn.execNoArgs("begin exclusive")
, conn.execNoArgs("commit")
and conn.execNoArgs("rollback")
conn.transaction()
errdefer conn.rollback();
try conn.exec(...);
try conn.exec(...);
try conn.commit();
When binding a []const u8
, this library has no way to tell whether the value should be treated as an text or blob. It defaults to text. To have the value bound as a blob use zqlite.blob(value)
:
conn.insert("insert into records (image) values (?1)", .{zqlite.blob(image)})
However, this should only be necessary in specific cases where SQLite blob-specific operations are used on the data. Text and blob are practically the same, except they have a different type.
zqlite.Pool
is a simple thread-safe connection pool. After being created, the acquire
and release
functions are used to get a connection from the pool and to release it.
var pool = try zqlite.Pool.init(allocator, .{
// The number of connection in the pool. The pool will not grow or
// shrink beyond this count
.size = 5, // default 5
// The path of the DB connection
.path = "/tmp/zqlite.sqlite", // no default, required
// The zqlite.OpenFlags to use when opening each connection in the pool
// Defaults are as shown here:
.flags = zqlite.OpenFlags.Create | zqlite.OpenFlags.EXResCode
// Callback function to execute for each connection in the pool when opened
.on_connection = null,
// Callback function to execute only for the first connection in the pool
.on_first_connection = null,
});
const c1 = pool.acquire();
defer pool.release(c1);
c1.execNoArgs(...);
Both the on_connection
and on_first_connection
have the same signature. For the first connection to be opened by the pool, if both callbacks are provided then both callbacks will be executed, with on_first_connection
executing first.
var pool = zqlite.Pool.init(allocator, .{
.size = 5,
.on_first_connection = &initializeDB,
.on_connection = &initializeConnection,
// other required & optional fields
});
...
// Our size is 5, but this will only be executed once, for the first
// connection in our pool
fn initializeDB(conn: Conn) !void {
try conn.execNoArgs("create table if not exists testing(id int)");
}
// Our size is 5, so this will be executed 5 times, once for each
// connection. `initializeDB` is guaranteed to be called before this
// function is called.
fn initializeConnection(conn: Conn) !void {
return conn.busyTimeout(1000);
}