about things
1# database
2
3patterns for sqlite in zig - connection management, thread safety, transactions.
4
5## zqlite vs raw c bindings
6
7prefer [zqlite](https://github.com/karlseguin/zqlite.zig) over raw c bindings. the api is cleaner and handles common pitfalls:
8
9```zig
10const zqlite = @import("zqlite");
11
12// open with flags
13conn = zqlite.open(path, zqlite.OpenFlags.Create | zqlite.OpenFlags.ReadWrite) catch |err| {
14 return err;
15};
16
17// exec with tuple binding (type-safe)
18conn.exec("INSERT INTO users (name, age) VALUES (?, ?)", .{ name, age }) catch |err| {
19 return err;
20};
21
22// exec without args
23conn.execNoArgs("CREATE TABLE IF NOT EXISTS users (...)") catch |err| {
24 return err;
25};
26```
27
28with raw c bindings you manually manage statement lifecycle, bind parameters by index, and handle error codes. zqlite wraps all that.
29
30see: [build.zig.zon dependency](https://tangled.sh/@zzstoatzz.io/music-atmosphere-feed/tree/main/build.zig.zon)
31
32## connection patterns
33
34### global connection + mutex (simple apps)
35
36for single-writer workloads, a global connection protected by mutex works:
37
38```zig
39pub var conn: zqlite.Conn = undefined;
40pub var mutex: std.Thread.Mutex = .{};
41
42pub fn init(path: [*:0]const u8) !void {
43 conn = zqlite.open(path, zqlite.OpenFlags.Create | zqlite.OpenFlags.ReadWrite) catch |err| {
44 return err;
45 };
46 // enable WAL for better read concurrency
47 _ = conn.exec("PRAGMA journal_mode=WAL", .{}) catch {};
48 _ = conn.exec("PRAGMA busy_timeout=5000", .{}) catch {};
49}
50
51pub fn insertUser(name: []const u8) !void {
52 mutex.lock();
53 defer mutex.unlock();
54
55 conn.exec("INSERT INTO users (name) VALUES (?)", .{name}) catch |err| {
56 return err;
57 };
58}
59```
60
61every function that touches the db acquires the mutex first. the `defer mutex.unlock()` ensures unlock even on error.
62
63see: [pollz/db.zig](https://tangled.sh/@zzstoatzz.io/pollz/tree/main/backend/src/db.zig), [music-atmosphere-feed/db.zig](https://tangled.sh/@zzstoatzz.io/music-atmosphere-feed/tree/main/src/store/db.zig)
64
65### connection pool (concurrent apps)
66
67for multi-threaded servers with concurrent writes, use zqlite's pool:
68
69```zig
70const pool = try zqlite.Pool.init(.{
71 .path = path,
72 .size = 4, // number of connections
73 .on_first_connection = initSchema, // runs once
74 .on_connection = configurePragmas, // runs per connection
75});
76
77// in request handler
78const c = pool.acquire();
79defer c.release();
80
81try c.exec("INSERT ...", .{...});
82```
83
84the pool won't grow beyond the configured size. each connection gets its own cursor/statement state.
85
86## transactions
87
88wrap multi-statement operations in transactions for atomicity:
89
90```zig
91try conn.transaction(); // BEGIN
92errdefer conn.rollback();
93
94try conn.exec("UPDATE flow_run SET state = ?", .{state});
95try conn.exec("INSERT INTO flow_run_state ...", .{...});
96
97try conn.commit(); // COMMIT
98```
99
100the `errdefer conn.rollback()` ensures rollback if any statement fails. without this, partial writes corrupt state.
101
102for write-heavy operations, use `exclusiveTransaction()` which acquires a write lock immediately (avoids busy-wait):
103
104```zig
105try conn.exclusiveTransaction(); // BEGIN IMMEDIATE
106// ... writes ...
107try conn.commit();
108```
109
110## pragmas for concurrency
111
112always set these for concurrent access:
113
114```zig
115_ = conn.exec("PRAGMA journal_mode=WAL", .{}) catch {}; // write-ahead logging
116_ = conn.exec("PRAGMA busy_timeout=5000", .{}) catch {}; // wait 5s on lock
117_ = conn.exec("PRAGMA foreign_keys=ON", .{}) catch {}; // enforce FK constraints
118```
119
120WAL allows concurrent readers during writes. busy_timeout prevents immediate SQLITE_BUSY errors.
121
122## module organization
123
124for larger apps, separate db concerns into a module:
125
126```
127src/db/
128 mod.zig -- re-exports, global state
129 Client.zig -- connection/pool management
130 schema.zig -- table definitions, migrations
131 result.zig -- result parsing helpers
132```
133
134the `mod.zig` pattern:
135
136```zig
137pub const Client = @import("Client.zig");
138pub const Row = result.Row;
139
140var client: ?Client = null;
141
142pub fn init() !void {
143 client = try Client.init();
144 try schema.init(&client.?);
145}
146
147pub fn getClient() ?*Client {
148 if (client) |*c| return c;
149 return null;
150}
151```
152
153see: [leaflet-search/db/mod.zig](https://tangled.sh/@zzstoatzz.io/leaflet-search/tree/main/backend/src/db/mod.zig)
154
155## comptime sql with zql
156
157[zql](https://tangled.sh/@zzstoatzz.io/zql) parses sql at compile time and provides type-safe bindings. it extracts column names, named parameters, and validates struct mapping:
158
159```zig
160const zql = @import("zql");
161
162// parse sql at comptime
163const Q = zql.Query("SELECT id, name FROM users WHERE age > :min_age");
164
165// comptime validation - fails if struct missing required param
166Q.validateArgs(struct { min_age: i64 });
167
168// access parsed metadata
169_ = Q.positional; // "SELECT id, name FROM users WHERE age > ?"
170_ = Q.params; // ["min_age"]
171_ = Q.columns; // ["id", "name"]
172
173// bind named params to positional tuple (order independent)
174const args = Q.bind(.{ .min_age = 25 }); // returns tuple for db.exec
175
176// map result row to struct (validates fields match columns)
177const User = struct { id: i64, name: []const u8 };
178Q.validateStruct(User);
179const user = Q.fromRow(User, row); // row must have .text(idx), .int(idx)
180```
181
182the key pattern: `Query(comptime sql)` returns a type with methods for validation and binding. all validation happens at compile time - typos in param names or missing fields are compile errors.
183
184combine with zqlite for fully type-safe database layer:
185
186```zig
187const Q = zql.Query("INSERT INTO users (name, age) VALUES (:name, :age)");
188try conn.exec(Q.positional, Q.bind(.{ .name = "alice", .age = 25 }));
189```
190
191see: [zql/Query.zig](https://tangled.sh/@zzstoatzz.io/zql/tree/main/src/Query.zig)
192
193## postgres with pg.zig
194
195[pg.zig](https://github.com/karlseguin/pg.zig) is the postgresql client. key difference from sqlite: **strict type checking on column types**.
196
197### strict integer types
198
199pg.zig enforces exact type matching for integer columns:
200
201```zig
202// WRONG - panics at runtime
203row.getInt(col) // returns i32, but you assigned to i64 from BIGINT column
204
205// RIGHT - match postgres types exactly
206const count: i32 = row.getInt(col); // for INTEGER / INT4 columns
207const big_id: i64 = row.getBigInt(col); // for BIGINT / INT8 columns
208```
209
210common mistake: sqlite's `row.int()` returns i64 regardless of column type. pg.zig is strict.
211
212### abstracting sqlite/postgres
213
214when supporting both databases, create a wrapper that handles the differences:
215
216```zig
217pub const Row = union(enum) {
218 sqlite: zqlite.Row,
219 postgres: pg.Row,
220
221 // for INTEGER columns
222 pub fn int(self: Row, col: usize) i64 {
223 return switch (self) {
224 .sqlite => |r| r.int(col),
225 .postgres => |r| r.getInt(col), // returns i32
226 };
227 }
228
229 // for BIGINT columns - MUST use separate method
230 pub fn bigint(self: Row, col: usize) i64 {
231 return switch (self) {
232 .sqlite => |r| r.int(col),
233 .postgres => |r| r.getBigInt(col), // returns i64
234 };
235 }
236
237 pub fn text(self: Row, col: usize) []const u8 {
238 return switch (self) {
239 .sqlite => |r| r.text(col),
240 .postgres => |r| r.getText(col) orelse "",
241 };
242 }
243};
244```
245
246see: [prefect-server/db/backend.zig](https://tangled.sh/@zzstoatzz.io/prefect-server/tree/main/src/db/backend.zig)
247
248### placeholder syntax
249
250postgres uses `$1, $2` instead of `?`. convert at runtime:
251
252```zig
253fn rewritePlaceholders(alloc: Allocator, sql: []const u8) ![]const u8 {
254 var result: std.ArrayList(u8) = .empty;
255 errdefer result.deinit(alloc);
256 var param_num: usize = 1;
257
258 for (sql) |c| {
259 if (c == '?') {
260 try result.print(alloc, "${d}", .{param_num});
261 param_num += 1;
262 } else {
263 try result.append(alloc, c);
264 }
265 }
266 return result.toOwnedSlice(alloc);
267}
268```
269
270### connection pooling
271
272pg.zig has built-in pooling:
273
274```zig
275const pool = try pg.Pool.init(.{
276 .connect = .{
277 .host = "localhost",
278 .port = 5432,
279 .database = "mydb",
280 .username = "user",
281 .password = "pass",
282 },
283 .size = 10,
284});
285
286// acquire connection (returns to pool on deinit)
287const conn = try pool.acquire();
288defer conn.deinit();
289
290var result = try conn.query("SELECT ...", .{});
291defer result.deinit();
292```