comptime sql bindings for zig
zzstoatzz.io
ziglang
sql
1//! Query - comptime sql metadata extraction
2//!
3//! extracts parameter names, column names, and provides
4//! struct mapping utilities at compile time.
5
6const std = @import("std");
7const parser = @import("parse.zig");
8
9/// query metadata extracted at comptime
10pub fn Query(comptime sql: []const u8) type {
11 comptime {
12 const parsed = parser.parse(sql);
13 return struct {
14 pub const raw = sql;
15 pub const positional: []const u8 = parsed.positional[0..parsed.positional_len];
16 pub const param_count = parsed.param_count;
17 pub const params: []const []const u8 = parsed.params[0..parsed.params_len];
18 pub const columns: []const []const u8 = parsed.columns[0..parsed.columns_len];
19
20 /// validate that args struct has all required params
21 pub fn validateArgs(comptime Args: type) void {
22 const fields = @typeInfo(Args).@"struct".fields;
23 inline for (params) |p| {
24 if (!hasField(fields, p)) {
25 @compileError("missing param :" ++ p ++ " in args struct");
26 }
27 }
28 }
29
30 /// validate that struct fields match query columns
31 pub fn validateStruct(comptime T: type) void {
32 const fields = @typeInfo(T).@"struct".fields;
33 inline for (fields) |f| {
34 if (!hasColumn(f.name)) {
35 @compileError("struct field '" ++ f.name ++ "' not found in query columns");
36 }
37 }
38 }
39
40 /// get column index by name at comptime
41 pub inline fn columnIndex(comptime name: []const u8) comptime_int {
42 inline for (columns, 0..) |col, i| {
43 if (comptime std.mem.eql(u8, col, name)) {
44 return i;
45 }
46 }
47 @compileError("column '" ++ name ++ "' not found in query");
48 }
49
50 /// map row data to a struct using column names
51 /// row must have .text(idx) and .int(idx) methods
52 pub fn fromRow(comptime T: type, row: anytype) T {
53 comptime validateStruct(T);
54 var result: T = undefined;
55 const fields = @typeInfo(T).@"struct".fields;
56 inline for (fields) |f| {
57 const idx = comptime columnIndex(f.name);
58 @field(result, f.name) = switch (f.type) {
59 []const u8 => row.text(idx),
60 i64 => row.int(idx),
61 bool => row.int(idx) != 0,
62 else => @compileError("unsupported field type: " ++ @typeName(f.type)),
63 };
64 }
65 return result;
66 }
67
68 /// bind args struct to positional tuple in param order
69 pub fn bind(args: anytype) BindTuple(@TypeOf(args)) {
70 comptime validateArgs(@TypeOf(args));
71 var result: BindTuple(@TypeOf(args)) = undefined;
72 inline for (params, 0..) |p, i| {
73 result[i] = @field(args, p);
74 }
75 return result;
76 }
77
78 fn BindTuple(comptime Args: type) type {
79 const fields = @typeInfo(Args).@"struct".fields;
80 var types: [param_count]type = undefined;
81 inline for (params, 0..) |p, i| {
82 for (fields) |f| {
83 if (std.mem.eql(u8, f.name, p)) {
84 types[i] = f.type;
85 break;
86 }
87 }
88 }
89 return std.meta.Tuple(&types);
90 }
91
92 fn hasField(fields: anytype, name: []const u8) bool {
93 inline for (fields) |f| {
94 if (std.mem.eql(u8, f.name, name)) return true;
95 }
96 return false;
97 }
98
99 fn hasColumn(comptime name: []const u8) bool {
100 const cols = @This().columns;
101 inline for (cols) |col| {
102 if (std.mem.eql(u8, col, name)) return true;
103 }
104 return false;
105 }
106 };
107 }
108}
109
110test "columns" {
111 const Q = Query("SELECT id, name, age FROM users");
112 try std.testing.expectEqual(3, Q.columns.len);
113 try std.testing.expectEqualStrings("id", Q.columns[0]);
114 try std.testing.expectEqualStrings("name", Q.columns[1]);
115 try std.testing.expectEqualStrings("age", Q.columns[2]);
116}
117
118test "named params" {
119 const Q = Query("SELECT * FROM users WHERE id = :id AND age > :min_age");
120 try std.testing.expectEqual(2, Q.params.len);
121 try std.testing.expectEqualStrings("id", Q.params[0]);
122 try std.testing.expectEqualStrings("min_age", Q.params[1]);
123}
124
125test "positional conversion" {
126 const Q = Query("SELECT * FROM users WHERE id = :id AND age > :min_age");
127 try std.testing.expectEqualStrings("SELECT * FROM users WHERE id = ? AND age > ?", Q.positional);
128}
129
130test "columns with alias" {
131 const Q = Query("SELECT id, first_name AS name FROM users");
132 try std.testing.expectEqual(2, Q.columns.len);
133 try std.testing.expectEqualStrings("id", Q.columns[0]);
134 try std.testing.expectEqualStrings("name", Q.columns[1]);
135}
136
137test "columns with function" {
138 const Q = Query("SELECT COUNT(*) AS count, MAX(age) AS max_age FROM users");
139 try std.testing.expectEqual(2, Q.columns.len);
140 try std.testing.expectEqualStrings("count", Q.columns[0]);
141 try std.testing.expectEqualStrings("max_age", Q.columns[1]);
142}
143
144test "columnIndex" {
145 const Q = Query("SELECT id, name, age FROM users");
146 // first verify columns work directly
147 try std.testing.expectEqual(3, Q.columns.len);
148 try std.testing.expectEqualStrings("id", Q.columns[0]);
149
150 // now try columnIndex
151 try std.testing.expectEqual(0, Q.columnIndex("id"));
152 try std.testing.expectEqual(1, Q.columnIndex("name"));
153 try std.testing.expectEqual(2, Q.columnIndex("age"));
154}
155
156test "validateStruct" {
157 const Q = Query("SELECT id, name, age FROM users");
158 // verify columns first
159 try std.testing.expectEqual(3, Q.columns.len);
160
161 const User = struct { id: i64, name: []const u8, age: i64 };
162 comptime Q.validateStruct(User);
163
164 const Partial = struct { id: i64, name: []const u8 };
165 comptime Q.validateStruct(Partial);
166}
167
168test "bind" {
169 const Q = Query("INSERT INTO users (name, age) VALUES (:name, :age)");
170 try std.testing.expectEqualStrings("INSERT INTO users (name, age) VALUES (?, ?)", Q.positional);
171
172 const args = Q.bind(.{ .name = "alice", .age = @as(i64, 25) });
173 try std.testing.expectEqualStrings("alice", args[0]);
174 try std.testing.expectEqual(25, args[1]);
175
176 // order doesn't matter in input struct
177 const args2 = Q.bind(.{ .age = @as(i64, 30), .name = "bob" });
178 try std.testing.expectEqualStrings("bob", args2[0]);
179 try std.testing.expectEqual(30, args2[1]);
180}
181
182test "fromRow" {
183 const Q = Query("SELECT id, name, age FROM users");
184
185 // mock row matching leaflet-search's Row interface
186 const MockRow = struct {
187 texts: [3][]const u8,
188 ints: [3]i64,
189
190 pub fn text(self: @This(), idx: usize) []const u8 {
191 return self.texts[idx];
192 }
193 pub fn int(self: @This(), idx: usize) i64 {
194 return self.ints[idx];
195 }
196 };
197
198 const row = MockRow{
199 .texts = .{ "42", "alice", "25" },
200 .ints = .{ 42, 0, 25 },
201 };
202
203 const User = struct { id: i64, name: []const u8, age: i64 };
204 const user = Q.fromRow(User, row);
205
206 try std.testing.expectEqual(42, user.id);
207 try std.testing.expectEqualStrings("alice", user.name);
208 try std.testing.expectEqual(25, user.age);
209}