zig-sqlite

When fetching rows into structs that contain slices (like []const u8), zig-sqlite performs multiple allocations: one for each slice field and one for the resulting slice of structs. To prevent memory leaks and simplify cleanup, use a std.heap.ArenaAllocator to manage the lifetime of the fetched data.

const allocator = std.heap.page_allocator;
var arena = std.heap.ArenaAllocator.init(allocator);
defer arena.deinit();

// Pass the arena allocator to the query method
const users = try stmt.all(User, arena.allocator(), .{}, .{ .id = 20 });

To add zig-sqlite to your project, use the zig fetch command to download the dependency and then configure your build.zig to import the module.

zig fetch --save git+https://github.com/vrischmann/zig-sqlite

In your build.zig:

const sqlite = b.dependency("sqlite", .{
    .target = target,
    .optimize = optimize,
});
exe.root_module.addImport("sqlite", sqlite.module("sqlite"));

To improve performance when running the same query multiple times with different parameters, call stmt.reset() before each subsequent execution.

const query = "UPDATE employees SET salary = ? WHERE id = ?";
var stmt = try db.prepare(query);
defer stmt.deinit();

var id: usize = 0;
while (id < 20) : (id += 1) {
    stmt.reset();
    try stmt.exec(.{}, .{
        .salary = 2000,
        .id = id,
    });
}

To use the library, import sqlite and initialize a sqlite.Db instance using sqlite.Db.init. You must provide an InitOptions struct. The mode field is mandatory. Other fields like open_flags and threading_mode have sane defaults.

const sqlite = @import("sqlite");

var db = try sqlite.Db.init(.{
    .mode = sqlite.Db.Mode{ .File = "/home/vincent/mydata.db" },
    .open_flags = .{
        .write = true,
        .create = true,
    },
    .threading_mode = .MultiThread,
});

SQLite works via prepared statements. Use db.prepare(query) to create a sqlite.Statement. The query string is evaluated at comptime.

For queries that do not return data (e.g., INSERT, UPDATE), use stmt.exec(.{}, params) to execute the statement with bound parameters.

try db.exec("CREATE TABLE IF NOT EXISTS employees(id integer primary key, name text, age integer, salary integer)", .{}, .{});

const query = "SELECT id, name, age, salary FROM employees WHERE age > ? AND age < ?";
var stmt = try db.prepare(query);
defer stmt.deinit();

// For an INSERT:
const insert_query = "INSERT INTO employees(name, age, salary) VALUES(?, ?, ?)";
var ins_stmt = try db.prepare(insert_query);
defer ins_stmt.deinit();
try ins_stmt.exec(.{}, .{
    .name = "José",
    .age = 40,
    .salary = 20000,
});

You can extend SQLite by defining custom scalar functions using db.createScalarFunction. The function receives input arguments which are converted to Zig values based on the SQLite type (e.g., TEXT to []const u8, INTEGER to Zig integers).

try db.createScalarFunction(
    "blake3",
    struct {
        fn run(input: []const u8) [std.crypto.hash.Blake3.digest_length]u8 {
            var hash: [std.crypto.hash.Blake3.digest_length]u8 = undefined;
            std.crypto.hash.Blake3.hash(input, &hash, .{});
            return hash;
        }
    }.run,
    .{},
);

// Usage in SQL
const hash = try db.one([std.crypto.hash.Blake3.digest_length]u8, "SELECT blake3('hello')", .{}, .{});

If db.prepare fails and you need detailed error information, use prepareWithDiags. You must provide a pointer to a sqlite.Diagnostics instance.

var diags = sqlite.Diagnostics{};
var stmt = db.prepareWithDiags(query, .{ .diags = &diags }) catch |err| {
    std.log.err("unable to prepare statement, got error {}. diagnostics: {s}", .{ err, diags });
    return err;
};
defer stmt.deinit();

For large resultsets, use sqlite.Iterator to process rows one by one. You obtain an iterator by calling stmt.iterator(Type, params).

• Iterator.next(params): Returns an optional ?Type. Does not allocate memory.
• Iterator.nextAlloc(allocator, params): Returns an optional ?Type. Allocates memory using the provided allocator.

// Using Iterator.next (no allocation)
var iter = try stmt.iterator(usize, .{ .age = 20 });
while (try iter.next(.{})) |age| {
    std.debug.print("age: {}
", .{age});
}

// Using Iterator.nextAlloc (with allocation)
var iter = try stmt.iterator([]const u8, .{ .age = 20 });
const allocator = std.heap.page_allocator;
while (true) {
    var arena = std.heap.ArenaAllocator.init(allocator);
    defer arena.deinit();

    const name = (try iter.nextAlloc(arena.allocator(), .{})) orelse break;
    std.debug.print("name: {s}\n", .{name});
}