Simple sqlite3 wrapper for zig

example.zig

const std = @import("std");

const sqlite3 = @import("./sqlite3.zig");

pub fn main() anyerror!void {
    var db = try sqlite3.Database.open(":memory:");
    defer db.close();
    var boom = db.mapTable("boom", &[_]sqlite3.TemplateDefinition{ .{
        .name = "id",
        .decl = .integer_primary_key,
    }, .{
        .name = "text",
        .decl = .{
            .standard = .{
                .mapped = []u8,
            },
        },
    } });
    try boom.create();
    try boom.insert(.{
        .id = 0,
        .text = "2333",
    });
    var it = try boom.iter();
    defer it.deinit();
    while (try it.next()) |res| {
        std.log.info("{}", .{res});
    }
}

sqlite3.zig

const std = @import("std");

const sqlite3 = *@Type(.Opaque);
const sqlite3_stmt = *@Type(.Opaque);

extern fn sqlite3_errmsg(db: sqlite3) [*:0]const u8;
extern fn sqlite3_open(path: [*:0]const u8, db: *sqlite3) c_int;
extern fn sqlite3_close(db: sqlite3) c_int;
extern fn sqlite3_exec(
    db: sqlite3,
    sql: [*:0]const u8,
    callback: ?fn (
        user: ?*c_void,
        row: c_int,
        data: [*:null]const ?[*:0]const u8,
        columns: [*:null]const ?[*:0]const u8,
    ) callconv(.C) c_int,
    user: ?*c_void,
    errmsg: ?*?[*:0]const u8,
) c_int;
extern fn sqlite3_prepare(
    db: sqlite3,
    sql: [*]const u8,
    nbytes: c_int,
    stmt: *sqlite3_stmt,
    tail: ?*[*:0]const u8,
) c_int;
extern fn sqlite3_finalize(stmt: sqlite3_stmt) c_int;
extern fn sqlite3_reset(stmt: sqlite3_stmt) c_int;
extern fn sqlite3_step(stmt: sqlite3_stmt) c_int;
extern fn sqlite3_clear_bindings(stmt: sqlite3_stmt) c_int;

extern fn sqlite3_bind_blob(stmt: sqlite3_stmt, pos: c_int, data: [*]const u8, len: u32, de: ?fn (ptr: *c_void) callconv(.C) void) c_int;
extern fn sqlite3_bind_zeroblob(stmt: sqlite3_stmt, pos: c_int, len: u32) c_int;
extern fn sqlite3_bind_zeroblob64(stmt: sqlite3_stmt, pos: c_int, len: u64) c_int;
extern fn sqlite3_bind_double(stmt: sqlite3_stmt, pos: c_int, data: f64) c_int;
extern fn sqlite3_bind_int(stmt: sqlite3_stmt, pos: c_int, data: i32) c_int;
extern fn sqlite3_bind_int64(stmt: sqlite3_stmt, pos: c_int, data: i64) c_int;
extern fn sqlite3_bind_null(stmt: sqlite3_stmt, pos: c_int) c_int;
extern fn sqlite3_bind_text(stmt: sqlite3_stmt, pos: c_int, data: [*]const u8, len: u32, de: ?fn (ptr: *c_void) callconv(.C) void) c_int;

extern fn sqlite3_column_blob(stmt: sqlite3_stmt, pos: c_int) [*]const u8;
extern fn sqlite3_column_double(stmt: sqlite3_stmt, pos: c_int) f64;
extern fn sqlite3_column_int(stmt: sqlite3_stmt, pos: c_int) i32;
extern fn sqlite3_column_int64(stmt: sqlite3_stmt, pos: c_int) i64;
extern fn sqlite3_column_text(stmt: sqlite3_stmt, pos: c_int) [*]const u8;
extern fn sqlite3_column_bytes(stmt: sqlite3_stmt, pos: c_int) c_int;
extern fn sqlite3_column_type(stmt: sqlite3_stmt, pos: c_int) c_int;

pub const Database = struct {
    raw: sqlite3,

    fn reportError(self: *@This()) anyerror {
        std.log.err("{}", .{sqlite3_errmsg(self.raw)});
        return error.DbError;
    }

    pub fn open(path: [*:0]const u8) !@This() {
        var ret: @This() = .{ .raw = undefined };
        const res = sqlite3_open(path, &ret.raw);
        if (res != 0) return error.DbError;
        return ret;
    }

    pub fn close(self: *@This()) void {
        const res = sqlite3_close(self.raw);
        if (res != 0) @panic("sqlite3_close error");
    }

    pub fn exec(self: *@This(), sql: []const u8) !void {
        var stmt = try self.prepare(sql);
        defer stmt.deinit();
        _ = try stmt.step();
    }

    pub fn prepare(self: *@This(), sql: []const u8) !PreparedStatement {
        var ret: PreparedStatement = .{ .raw = undefined };
        const res = sqlite3_prepare(self.raw, sql.ptr, @intCast(c_int, sql.len), &ret.raw, null);
        if (res != 0) return self.reportError();
        return ret;
    }

    pub fn mapTable(self: *@This(), name: []const u8, comptime template: []const TemplateDefinition) MappedTable(template) {
        return MappedTable(template).init(self, name);
    }
};

pub const TransportStrategy = union(enum) {
    const FuncType = fn (data: *c_void) callconv(.C) void;

    static: void,
    transient: void,
    dynamic: FuncType,

    fn get(self: @This()) ?FuncType {
        return switch (self) {
            .static => null,
            .transient => @intToPtr(FuncType, @bitCast(usize, @as(isize, -1))),
            .dynamic => |ptr| ptr,
        };
    }
};

pub const ColumnType = enum {
    int,
    int64,
    double,
    blob,
    text,

    pub fn mapType(self: ColumnType) type {
        return switch (self) {
            .int => i32,
            .int64 => i64,
            .double => f64,
            .blob, .text => []const u8,
        };
    }
};

pub const PreparedStatement = struct {
    raw: sqlite3_stmt,

    pub fn deinit(self: *@This()) void {
        const res = sqlite3_finalize(self.raw);
        if (res != 0) @panic("sqlite3_close error");
    }

    fn bindNotNull(self: *@This(), idx: c_int, data: anytype) !void {
        const res = switch (@TypeOf(data)) {
            comptime_int => if (data <= std.math.maxInt(i32))
                sqlite3_bind_int(self.raw, idx, data)
            else
                sqlite3_bind_int64(self.raw, idx, data),
            i32 => sqlite3_bind_int(self.raw, idx, data),
            i64 => sqlite3_bind_int64(self.raw, idx, data),
            f32, f64, comptime_float => sqlite3_bind_double(self.raw, idx, data),
            else => @compileError("not support type"),
        };
        if (res != 0) return error.DbError;
    }

    pub fn bind(self: *@This(), idx: c_int, data: anytype) !void {
        if (@typeInfo(@TypeOf(data)) == .Optional) {
            if (data) |notnull| {
                return self.bindNotNull(idx, notnull);
            } else {
                const res = sqlite3_bind_null(self.raw, idx);
                if (res != 0) return error.DbError;
            }
        } else {
            return self.bindNotNull(idx, data);
        }
    }

    pub fn bindText(self: *@This(), idx: c_int, data: ?[]const u8, strategy: TransportStrategy) !void {
        const res = if (data) |notnull|
            sqlite3_bind_text(self.raw, idx, notnull.ptr, @intCast(u32, notnull.len), strategy.get())
        else
            sqlite3_bind_null(self.raw, idx);
        if (res != 0) return error.DbError;
    }

    pub fn bindBlob(self: *@This(), idx: c_int, data: ?[]const u8, strategy: TransportStrategy) !void {
        const res = if (data) |notnull|
            sqlite3_bind_blob(self.raw, idx, notnull.ptr, @intCast(u32, notnull.len), strategy.get())
        else
            sqlite3_bind_null(self.raw, idx);
        if (res != 0) return error.DbError;
    }

    pub fn step(self: *@This()) !bool {
        return switch (sqlite3_step(self.raw)) {
            0 => @panic("Unexpected code"),
            100 => return true,
            101 => return false,
            else => return error.DbError,
        };
    }

    pub fn reset(self: *@This()) void {
        sqlite3_reset(self.raw);
    }

    pub fn clear(self: *@This()) void {
        sqlite3_clear_bindings(self.raw);
    }

    pub fn get(self: *@This(), idx: c_int, comptime coltype: ColumnType) coltype.mapType() {
        return switch (coltype) {
            .int => sqlite3_column_int(self.raw, idx),
            .int64 => sqlite3_column_int64(self.raw, idx),
            .double => sqlite3_column_double(self.raw, idx),
            .blob => blk: {
                const ret = sqlite3_column_blob(self.raw, idx);
                const len = sqlite3_column_bytes(self.raw, idx);
                break :blk ret[0..@intCast(usize, len)];
            },
            .text => blk: {
                const ret = sqlite3_column_text(self.raw, idx);
                const len = sqlite3_column_bytes(self.raw, idx);
                break :blk ret[0..@intCast(usize, len)];
            },
        };
    }
};

fn stripOptional(comptime typ: type) type {
    const info = @typeInfo(typ);
    if (info == .Optional) {
        return struct {
            const optional = true;
            const child = info.Optional.child;
        };
    } else {
        return struct {
            const optional = false;
            const child = typ;
        };
    }
}

pub const TemplateDefinition = struct {
    pub const BlobType = @Type(.Opaque);

    name: []const u8,
    decl: union(enum) {
        integer_primary_key: void,
        standard: struct {
            mapped: type,
            optional: bool = true,
            unique: bool = false,
            primary_key: bool = false,
            default: ?[]const u8 = null,

            fn StorageNotNull(comptime self: @This()) type {
                return switch (self.mapped) {
                    BlobType => []const u8,
                    []u8 => []const u8,
                    i32, i64, f32, f64, bool => |r| r,
                    else => @compileError("Unknown type: " ++ @typeName(@TypeOf(ref.mapped))),
                };
            }

            fn StorageType(comptime self: @This()) type {
                return if (self.optional) ?self.StorageNotNull() else self.StorageNotNull();
            }

            fn getColumnType(comptime self: @This()) ColumnType {
                return comptime switch (self.mapped) {
                    BlobType => .blob,
                    []u8 => .text,
                    i32 => .int,
                    i64 => .int64,
                    f32, f64 => .double,
                    bool => .int,
                    else => @compileError("Unknown type: " ++ @typeName(@TypeOf(ref.mapped))),
                };
            }

            fn fetch(comptime self: @This(), target: anytype, stmt: *PreparedStatement, idx: c_int) void {
                target.* = switch (self.mapped) {
                    BlobType => stmt.get(idx, .blob),
                    []u8 => stmt.get(idx, .text),
                    i32 => stmt.get(idx, .int),
                    i64 => stmt.get(idx, .int64),
                    f32 => @floatCast(f32, stmt.get(idx, .double)),
                    f64 => stmt.get(idx, .double),
                    bool => stmt.get(idx, .int) != 0,
                    else => @compileError("Unknown type: " ++ @typeName(@TypeOf(ref.mapped))),
                };
            }

            fn setup(comptime self: @This(), source: anytype, stmt: *PreparedStatement, idx: c_int) !void {
                return switch (self.mapped) {
                    BlobType => stmt.bindBlob(idx, source, .transient),
                    []u8 => stmt.bindText(idx, source, .transient),
                    i32, i64, f32, f64 => stmt.bind(idx, source),
                    bool => stmt.bind(idx, if (source) 1 else 0),
                    else => @compileError("Unknown type: " ++ @typeName(@TypeOf(ref.mapped))),
                };
            }
        },

        fn StorageNotNull(comptime self: @This()) type {
            return switch (self) {
                .integer_primary_key => i32,
                .standard => |ref| return ref.StorageNotNull(),
            };
        }

        fn StorageType(comptime self: @This()) type {
            return switch (self) {
                .integer_primary_key => i32,
                .standard => |ref| return ref.StorageType(),
            };
        }

        fn getColumnType(comptime self: @This()) ColumnType {
            return comptime switch (self) {
                .integer_primary_key => .int,
                .standard => |ref| return ref.getColumnType(),
            };
        }

        fn fetch(comptime self: @This(), target: anytype, stmt: *PreparedStatement, idx: c_int) void {
            target.* = switch (self) {
                .integer_primary_key => stmt.get(idx, .int),
                .standard => |ref| return ref.fetch(target, stmt, idx),
            };
        }

        fn setup(comptime self: @This(), source: anytype, stmt: *PreparedStatement, idx: c_int) !void {
            return switch (self) {
                .integer_primary_key => stmt.bind(idx, source),
                .standard => |ref| ref.setup(source, stmt, idx),
            };
        }
    },

    pub fn render(comptime self: @This(), writer: anytype) !void {
        try writer.print("{} ", .{self.name});
        switch (self.decl) {
            .integer_primary_key => {
                return writer.writeAll("INTEGER PRIMARY KEY");
            },
            .standard => |ref| {
                const str = switch (ref.mapped) {
                    BlobType => "BLOB",
                    []u8 => "TEXT",
                    i32, i64 => "INTEGER",
                    f32, f64 => "REAL",
                    bool => "BOOLEAN",
                    else => @compileError("Unknown type: " ++ @typeName(@TypeOf(ref.mapped))),
                };
                try writer.writeAll(str);
                if (!ref.optional) {
                    try writer.writeAll(" NOT NULL");
                }
                if (ref.unique) {
                    try writer.writeAll(" UNIQUE");
                }
                if (ref.primary_key) {
                    try writer.writeAll(" PRIMARY KEY");
                }
                if (ref.default) |default| {
                    try writer.print("DEFULT VALUE {}", .{default});
                }
            },
        }
    }
};

fn GenResultSet(comptime template: []const TemplateDefinition) type {
    var fields: [template.len]std.builtin.TypeInfo.StructField = undefined;
    const decls = [0]std.builtin.TypeInfo.Declaration{};
    inline for (template) |field, i| {
        fields[i] = .{
            .name = field.name,
            .field_type = field.decl.StorageType(),
            .default_value = null,
            .is_comptime = false,
        };
    }
    return @Type(.{
        .Struct = .{
            .layout = .Auto,
            .fields = &fields,
            .decls = &decls,
            .is_tuple = false,
        },
    });
}

pub fn MappedTable(comptime template: []const TemplateDefinition) type {
    return struct {
        const TableSelf = @This();
        const ResultSet = GenResultSet(template);
        pub const Template = template;
        db: *Database,
        name: []const u8,

        fn init(db: *Database, name: []const u8) TableSelf {
            return .{ .db = db, .name = name };
        }

        pub fn drop(self: *@This()) !void {
            var buffer: [1024]u8 = undefined;
            var stream = std.io.fixedBufferStream(buffer);
            try stream.writer().print("DROP TABLE IF EXISTS {}", .{self.name});
            try self.db.exec(stream.getWritten());
        }

        pub fn create(self: *@This()) !void {
            var buffer: [1024]u8 = undefined;
            var stream = std.io.fixedBufferStream(buffer[0..]);
            var writer = stream.writer();
            try writer.print("CREATE TABLE IF NOT EXISTS {} (", .{self.name});
            inline for (template) |decl, i| {
                try decl.render(writer);
                if (i != template.len - 1) try writer.writeAll(", ");
            }
            try writer.writeAll(")");
            std.log.info("{}", .{stream.getWritten()});
            try self.db.exec(stream.getWritten());
        }

        const Iterator = struct {
            stmt: PreparedStatement,

            pub fn next(self: *@This()) !?ResultSet {
                if (try self.stmt.step()) {
                    var ret: ResultSet = undefined;
                    inline for (template) |decl, i| {
                        decl.decl.fetch(&@field(ret, decl.name), &self.stmt, @intCast(c_int, i));
                    }
                    return ret;
                } else {
                    return null;
                }
            }

            pub fn deinit(self: *@This()) void {
                self.stmt.deinit();
            }
        };

        pub fn iter(self: *@This()) !Iterator {
            var buffer: [1024]u8 = undefined;
            var stream = std.io.fixedBufferStream(buffer[0..]);
            var writer = stream.writer();
            try writer.print("SELECT ", .{});
            inline for (template) |decl, i| {
                try writer.print("{}", .{decl.name});
                if (i != template.len - 1) try writer.writeAll(", ");
            }
            try writer.print(" FROM {}", .{self.name});
            std.log.info("{}", .{stream.getWritten()});
            return Iterator{ .stmt = try self.db.prepare(stream.getWritten()) };
        }

        pub fn insert(self: *@This(), rs: ResultSet) !void {
            var buffer: [1024]u8 = undefined;
            var stream = std.io.fixedBufferStream(buffer[0..]);
            var writer = stream.writer();
            try writer.print("INSERT INTO {} (", .{self.name});
            inline for (template) |decl, i| {
                try writer.print("{}", .{decl.name});
                if (i != template.len - 1) try writer.writeAll(", ");
            }
            try writer.print(") VALUES (", .{});
            inline for (template) |decl, i| {
                try writer.print("?", .{});
                if (i != template.len - 1) try writer.writeAll(", ");
            }
            try writer.print(")", .{});
            var stmt = try self.db.prepare(stream.getWritten());
            defer stmt.deinit();
            inline for (template) |decl, i| {
                try decl.decl.setup(@field(rs, decl.name), &stmt, i + 1);
            }
            _ = try stmt.step();
        }
    };
}