Writing Rows

The problem

Reading was the query builder; writing is the other half. An INSERT must name every column and number every placeholder; an UPDATE must split key columns from data columns. Writing those statements by hand, per table, is exactly the repetitive, drift-prone work the schema already has the information to do.

The pattern

Derive insert_sql at compile time: column list and placeholder list walked from the same fields, so they cannot disagree in length or order.
Derive update_sql with a stated convention: the first field is the key. Conventions a library documents are API; pick them deliberately.
Pair each statement with bind, which turns a row into an array of tagged-union Values, one per placeholder, in the same field order.

const std = @import("std");
const expect = std.testing.expect;

// The driver-facing value type. SQL text and values travel separately;
// this union is the shape of "separately".
const Value = union(enum) {
    int: i64,
    text: []const u8,
    boolean: bool,
};

fn Table(comptime T: type, comptime name: []const u8) type {
    const info = @typeInfo(T).@"struct";
    return struct {
        // "INSERT INTO users (id, name, active) VALUES ($1, $2, $3)"
        pub const insert_sql = blk: {
            var cols: []const u8 = "";
            var params: []const u8 = "";
            for (info.field_names, 0..) |field, i| {
                if (i > 0) {
                    cols = cols ++ ", ";
                    params = params ++ ", ";
                }
                cols = cols ++ field;
                params = params ++ std.fmt.comptimePrint("${d}", .{i + 1});
            }
            break :blk "INSERT INTO " ++ name ++ " (" ++ cols ++ ") VALUES (" ++ params ++ ")";
        };

        // "UPDATE users SET name = $2, active = $3 WHERE id = $1":
        // the first field is the key, by convention stated in the docs.
        pub const update_sql = blk: {
            var sets: []const u8 = "";
            for (info.field_names[1..], 2..) |field, n| {
                if (n > 2) sets = sets ++ ", ";
                sets = sets ++ field ++ std.fmt.comptimePrint(" = ${d}", .{n});
            }
            break :blk "UPDATE " ++ name ++ " SET " ++ sets ++
                " WHERE " ++ info.field_names[0] ++ " = $1";
        };

        // The runtime half: one Value per column, in placeholder order.
        // The inline for resolves every @field while compiling, so this
        // is a fixed sequence of stores at runtime.
        pub fn bind(row: T) [info.field_names.len]Value {
            var values: [info.field_names.len]Value = undefined;
            inline for (info.field_names, 0..) |field, i| {
                values[i] = switch (@TypeOf(@field(row, field))) {
                    i64 => .{ .int = @field(row, field) },
                    []const u8 => .{ .text = @field(row, field) },
                    bool => .{ .boolean = @field(row, field) },
                    else => @compileError("no Value mapping for field " ++ field),
                };
            }
            return values;
        }
    };
}

const User = struct {
    id: i64,
    name: []const u8,
    active: bool,
};
const Users = Table(User, "users");

test "insert statement is derived, not written" {
    try expect(std.mem.eql(
        u8,
        Users.insert_sql,
        "INSERT INTO users (id, name, active) VALUES ($1, $2, $3)",
    ));
}

test "update keys on the first field" {
    try expect(std.mem.eql(
        u8,
        Users.update_sql,
        "UPDATE users SET name = $2, active = $3 WHERE id = $1",
    ));
}

test "bind produces one typed value per placeholder" {
    const values = Users.bind(.{ .id = 7, .name = "ada", .active = true });
    try expect(values.len == 3);
    try expect(values[0].int == 7);
    try expect(std.mem.eql(u8, values[1].text, "ada"));
    try expect(values[2].boolean == true);
}

The statement and its values cannot drift

The deep property here: insert_sql’s placeholders and bind’s output are generated from one walk over one list of fields. Add a column to the struct and both change together at the next compile. The classic ORM bug (a statement with four placeholders fed five values) is not caught here; it is unrepresentable.

comptimePrint does the number formatting during compilation, so even $3 is baked into the constant. At runtime, bind is a fixed sequence of stores; the inline for leaves no loop behind.

The Value union is the wire contract

Drivers do not take Zig structs; they take a statement and a list of typed values. The Value union is that list’s element type, and the compile error in bind’s else arm is the schema’s type system meeting the driver’s: a field type with no wire mapping fails the build, naming the field.

In a full ORM

The draft adds RETURNING id to inserts, skips auto-managed columns (created_at, updated_at get their values from the library, not the caller), and marks the key column explicitly instead of by position. All three are more comptime walking of the same field list.