zzstoatzz.io
comptime sql bindings for zig
ziglang
sql
1# zql design notes
2
3## current state
4
5zql provides comptime utilities for SQL:
6
71. **parse** - extract column names and param names from SQL strings
82. **bind** - convert named struct args to positional tuple
93. **fromRow** - map row data to typed struct with validation
10
11```zig
12const Q = zql.Query("SELECT id, name FROM users WHERE id = :id");
13
14// bind: struct -> tuple in param order
15c.query(Q.positional, Q.bind(.{ .id = user_id }));
16
17// fromRow: row -> typed struct
18const user = Q.fromRow(User, row);
19```
20
21## what comptime enables
22
23based on research (see comptime.md), zig's comptime can:
24
25- parse and validate strings at compile time
26- generate types from data
27- create perfect hash functions for O(1) lookups
28- build complex data structures via type functions
29
30## potential directions
31
32### 1. table definitions (orm-lite)
33
34define schema once, generate queries:
35
36```zig
37const User = zql.Table("users", struct {
38 id: i64,
39 name: []const u8,
40 email: []const u8,
41});
42
43// generates: INSERT INTO users (id, name, email) VALUES (?, ?, ?)
44User.insert(.{ .id = 1, .name = "alice", .email = "a@b.com" });
45
46// generates: SELECT id, name, email FROM users WHERE id = ?
47User.select().where(.{ .id = 1 });
48
49// generates: UPDATE users SET name = ? WHERE id = ?
50User.update(.{ .name = "bob" }).where(.{ .id = 1 });
51```
52
53pros:
54- single source of truth for schema
55- no manual SQL writing for CRUD
56- compile-time validation
57
58cons:
59- scope creep into ORM territory
60- complex queries still need raw SQL
61- migration story unclear
62
63### 2. query composition (options struct pattern)
64
65zig idiom: use options structs, not fluent builders.
66
67```zig
68// NOT idiomatic zig:
69zql.Select("id").from("users").where("x = :x")
70
71// idiomatic zig - options struct:
72const Q = zql.Query(.{
73 .select = "d.uri, d.did, d.title, d.created_at",
74 .from = "documents d",
75 .joins = &.{
76 "LEFT JOIN publications p ON d.publication_uri = p.uri",
77 },
78 .where = "d.uri = :uri",
79 .order_by = "d.created_at DESC",
80 .limit = 40,
81});
82```
83
84for variants, use comptime conditionals:
85
86```zig
87fn DocQuery(comptime opts: struct {
88 fts: bool = false,
89 tag: bool = false,
90}) type {
91 return zql.Query(.{
92 .select = if (opts.fts) fts_columns else basic_columns,
93 .from = if (opts.fts) "documents_fts f" else "documents d",
94 .joins = buildJoins(opts),
95 .where = buildWhere(opts),
96 .order_by = if (opts.fts) "rank" else "d.created_at DESC",
97 });
98}
99
100// usage:
101const DocsByFts = DocQuery(.{ .fts = true });
102const DocsByTag = DocQuery(.{ .tag = true });
103const DocsByFtsAndTag = DocQuery(.{ .fts = true, .tag = true });
104```
105
106pros:
107- follows zig idioms (options struct pattern)
108- explicit, readable
109- comptime conditional logic is clear
110- type-returning function pattern from std
111
112cons:
113- more verbose than current raw SQL strings
114- query structure must fit the options model
115
116### 3. schema validation
117
118define schema, validate queries reference real columns:
119
120```zig
121const schema = zql.Schema{
122 .tables = .{
123 .users = .{ .id = .int, .name = .text, .email = .text },
124 .posts = .{ .id = .int, .user_id = .int, .title = .text },
125 },
126};
127
128// compile error if 'users' table doesn't have 'name' column
129const Q = schema.Query("SELECT name FROM users");
130```
131
132pros:
133- catches typos at compile time
134- documents schema in code
135
136cons:
137- schema must be maintained in zig (duplication from DB)
138- no runtime schema introspection in zig
139
140### 4. better parsing
141
142current parser is basic. could add:
143
144- sql syntax validation (not just name extraction)
145- join detection
146- subquery handling
147- aggregate function recognition
148
149### 5. perfect hash for columns
150
151use comptime perfect hashing for O(1) column lookups:
152
153```zig
154const Q = zql.Query("SELECT id, name, age FROM users");
155// Q.columnIndex("name") could use perfect hash instead of linear search
156```
157
158probably premature optimization for typical column counts.
159
160## zig idioms (from research)
161
162the zig community prefers:
163
1641. **options structs** over fluent builders
1652. **type-returning functions** for generics
1663. **explicit code** over clever patterns
1674. **comptime validation** via @compileError
168
169from the zig zen:
170- "favor reading code over writing code"
171- "only one obvious way to do things"
172- "communicate intent precisely"
173
174## recommendation
175
176**keep the current simple approach.** three explicit Query constants is more readable than a factory function with options.
177
178```zig
179// current - explicit, clear
180const DocsByTag = zql.Query("SELECT ... WHERE tag = :tag ...");
181const DocsByFts = zql.Query("SELECT ... WHERE MATCH :query ...");
182const DocsByFtsAndTag = zql.Query("SELECT ... WHERE MATCH :query AND tag = :tag ...");
183```
184
185this follows zig's preference for explicit over clever. the "duplication" is actually meaningful - these queries have different semantics.
186
187if we do add composition, use the **type-returning function pattern**:
188
189```zig
190fn DocQuery(comptime opts: struct { fts: bool = false, tag: bool = false }) type {
191 // comptime conditionals to build SQL
192 return zql.Query(sql);
193}
194```
195
196but only if the duplication becomes a real maintenance burden.
197
198## open questions
199
2001. how to handle dynamic WHERE clauses? (optional filters)
2012. how to compose queries that return different column sets?
2023. should zql own the execution layer or just generate SQL?
2034. how to handle database-specific SQL dialects?