1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
|
# zig-sqlite
This package is a thin wrapper around [sqlite](https://sqlite.org/index.html)'s C API.
## Requirements
* Linux
* the system and development package for sqlite
* `libsqlite3-dev` for Debian and derivatives
* `sqlite3-devel` for Fedora
## Installation
Since there's no package manager for Zig yet, the recommended way is to use a git submodule:
$ git submodule add https://git.sr.ht/~vrischmann/zig-sqlite src/sqlite
Then add the following to your `build.zig` target(s):
exe.linkLibC();
exe.linkSystemLibrary("sqlite3");
exe.addPackage(.{ .name = "sqlite", .path = "src/sqlite/sqlite.zig" });
Now you should be able to import sqlite like this:
const sqlite = @import("sqlite");
## Usage
### Initialization
You must create and initialize an instance of `sqlite.Db`:
var db: sqlite.Db = undefined;
try db.init(allocator, .{ .mode = sqlite.Db.Mode{ .File = "/home/vincent/mydata.db" } });
The `init` method takes an allocator and an optional tuple which will used to configure sqlite.
Right now the only member used in that tuple is `mode` which defines if the sqlite database is in memory or uses a file.
### Preparing a statement
sqlite works exclusively by using prepared statements. The wrapper type is `sqlite.Statement`. Here is how you get one:
const query =
\\SELECT id, name, age, salary FROM employees WHERE age > ? AND age < ?
;
var stmt = try db.prepare(query, .{
.age1 = 20,
.age2 = 40,
});
defer stmt.deinit();
The `Db.prepare` method takes a `comptime` query string and a tuple of bind parameters.
The number of bind parameters is comptime checked against the number of bind markers in the query string, so if you have 2 bind markers
you must provide 2 bind parameters.
Note that the fields name is irrelevant, only their order is relevant.
See the section "Bind parameters and resultset rows" for more information on the types mapping rules.
### Executing a statement
For queries which do not return data (`INSERT`, `UPDATE`) you can use the `exec` method:
const query =
\\UPDATE foo SET salary = ? WHERE id = ?
;
var stmt = try db.prepare(query, .{
.salary = 20000,
.id = 40,
});
defer stmt.deinit();
try stmt.exec();
### Reading data
For queries which do return data you can use the `all` method:
const query =
\\SELECT id, name, age, salary FROM employees WHERE age > ? AND age < ?
;
var stmt = try db.prepare(query, .{
.age1 = 20,
.age2 = 40,
});
defer stmt.deinit();
const rows = try stmt.all(
struct {
id: usize,
name: []const u8,
age: u16,
salary: u32,
},
.{ .allocator = allocator },
);
for (rows) |row| {
std.log.debug("id: {} ; name: {}; age: {}; salary: {}", .{ row.id, row.name, row.age, row.salary });
}
The `all` method takes a type and an optional tuple.
The type represents a "row", it can be:
* a struct where each field maps to the corresponding column in the resultset (so field 0 must map to field 1 and so on).
* a single type, in that case the resultset must only return one column.
Not all types are allowed, see the section "Bind parameters and resultset rows" for more information on the types mapping rules.
### Bind parameters and resultset rows
Since sqlite doesn't have many [types](https://www.sqlite.org/datatype3.html) only a small number of Zig types are allowed in binding parameters and in resultset mapping types.
Here are the rules for bind parameters:
* any Zig `Int` or `ComptimeInt` is tread as a `INTEGER`.
* any Zig `Float` or `ComptimeFloat` is treated as a `REAL`.
* `[]const u8`, `[]u8` or any array of `u8` is treated as a `TEXT` or `BLOB`.
|
