Recipe: SQLite From Zig

The problem

You want a real database from Zig, and SQLite is the obvious first one: it is a C library with no server, so the whole thing is a header and a linked library. Zig calls C directly, so there is no binding layer to install. The work is learning SQLite’s own open/prepare/bind/step/finalize shape and translating its integer return codes into Zig errors.

The plan

Import the C API. On current Zig this is a translate-c step in the build, imported as @import("c"), not the old @cImport (see below).
sqlite3_open(":memory:", &db). An in-memory database keeps the example deterministic and leaves no file behind.
sqlite3_exec for statements with no results, like CREATE TABLE.
sqlite3_prepare_v2 once, then bind, step, and reset per row for inserts.
Step a SELECT and read columns until it returns SQLITE_DONE.

const std = @import("std");

// @cImport was removed from the language. build.zig translates sqlite3.h with a
// translate-c step and hands it to us under the name "c".
const c = @import("c");

// SQLite reports success as SQLITE_OK; anything else is an error code. Turn
// that into a Zig error so `try` works and nothing is ignored.
fn check(db: ?*c.sqlite3, rc: c_int, ok: c_int) !void {
    if (rc == ok) return;
    std.debug.print("sqlite error: {s}\n", .{c.sqlite3_errmsg(db)});
    return error.Sqlite;
}

pub fn main(init: std.process.Init) !void {
    var buf: [1024]u8 = undefined;
    var file_writer = std.Io.File.stdout().writerStreaming(init.io, &buf);
    const out = &file_writer.interface;

    // ":memory:" is a real database that never touches disk, so the run is
    // deterministic and leaves nothing behind.
    var db: ?*c.sqlite3 = null;
    try check(db, c.sqlite3_open(":memory:", &db), c.SQLITE_OK);
    defer _ = c.sqlite3_close(db);
    try out.writeAll("opened :memory:\n");

    // sqlite3_exec runs SQL with no results. err_msg is separate from the
    // return code and must be freed.
    var err_msg: [*c]u8 = null;
    try check(db, c.sqlite3_exec(db,
        "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT);",
        null, null, &err_msg), c.SQLITE_OK);
    try out.writeAll("created table users\n");

    // A prepared statement is compiled once, then bound and stepped per row.
    // The ? placeholders keep values out of the SQL text, which is how you
    // avoid injection.
    const users = [_]struct { name: []const u8, email: []const u8 }{
        .{ .name = "Ziggy Stardust", .email = "ziggy@ziglang.in" },
        .{ .name = "Grace Hopper", .email = "grace@ziglang.in" },
        .{ .name = "Alan Turing", .email = "alan@ziglang.in" },
    };

    var insert: ?*c.sqlite3_stmt = null;
    try check(db, c.sqlite3_prepare_v2(db,
        "INSERT INTO users (name, email) VALUES (?, ?);", -1, &insert, null), c.SQLITE_OK);
    defer _ = c.sqlite3_finalize(insert);

    for (users) |u| {
        // Passing null as the destructor is SQLITE_STATIC: the string outlives
        // the step, so SQLite need not copy it.
        _ = c.sqlite3_bind_text(insert, 1, u.name.ptr, @intCast(u.name.len), null);
        _ = c.sqlite3_bind_text(insert, 2, u.email.ptr, @intCast(u.email.len), null);
        try check(db, c.sqlite3_step(insert), c.SQLITE_DONE);
        _ = c.sqlite3_reset(insert); // reuse the compiled statement
    }
    try out.print("inserted {d} rows\n", .{users.len});

    // Stepping a SELECT yields SQLITE_ROW per row until SQLITE_DONE.
    var query: ?*c.sqlite3_stmt = null;
    try check(db, c.sqlite3_prepare_v2(db,
        "SELECT id, name, email FROM users ORDER BY id;", -1, &query, null), c.SQLITE_OK);
    defer _ = c.sqlite3_finalize(query);

    while (c.sqlite3_step(query) == c.SQLITE_ROW) {
        const id = c.sqlite3_column_int(query, 0);
        const name = std.mem.span(c.sqlite3_column_text(query, 1));
        const email = std.mem.span(c.sqlite3_column_text(query, 2));
        try out.print("{d} | {s} | {s}\n", .{ id, name, email });
    }

    try out.flush();
}

`@cImport` is gone; use translate-c

If you are arriving from an older tutorial, the first line will not compile. Zig used to translate C headers inline with @cImport/@cInclude. That builtin was removed. Headers now come through a build-system step, b.addTranslateC, which produces a normal Zig module you import by name. This guide’s build.zig does it from a //! link: sqlite3 marker on the snippet: it synthesizes a header that includes sqlite3.h, translates it, links libsqlite3, and hands the module to the snippet as c. The payoff is that C imports are cached and configured like any other dependency rather than re-run on every compile.

Return codes become errors

Every SQLite call returns an int: SQLITE_OK for the setup calls, SQLITE_ROW and SQLITE_DONE for stepping. Left as integers they are easy to ignore, which is how C programs skip error handling. The check helper compares the code against the expected one and returns a Zig error otherwise, so try enforces it and sqlite3_errmsg explains any failure. This is the main thing a Zig wrapper adds over the raw API.

Prepared statements, not string building

The insert uses ? placeholders and sqlite3_bind_text, never string concatenation. That is the rule that keeps user data out of the SQL grammar, so a name containing a quote is data, not syntax. Binding null as the destructor argument is SQLITE_STATIC: it promises the string outlives the step, so SQLite does not copy it, which is true here because the values are string literals. sqlite3_reset rewinds the compiled statement so the next row reuses it instead of recompiling.

Reading columns

Stepping a SELECT returns SQLITE_ROW once per row. Columns are read by index and type: sqlite3_column_int for the id, sqlite3_column_text for the strings. The text accessor returns a C pointer, so std.mem.span turns it into a Zig slice for printing. The loop ends when sqlite3_step returns SQLITE_DONE.

Variations

On disk: pass a filename instead of ":memory:" to persist. Everything else is identical.
Transactions: wrap a batch of inserts in BEGIN/COMMIT via sqlite3_exec to make them atomic and much faster.
Other C libraries: the same //! link: mechanism works for any system library whose header matches its name; that is how struct memory layout and the ABI chapter connect to the C side.