Database
A new project uses SQLite: the data is one file, db.sqlite, next to the app, and there is nothing to install. spider new myapp --pg makes a project on PostgreSQL instead. PostgreSQL is about that.
The code on this page is from Posts, the example app in examples/posts (source).
Migrations
A migration is a SQL file that changes the database: it creates a table, adds a column. They live in src/core/db/migrations/, and spider g feature writes the first one of a feature. This is the one for the posts table:
-- migrate:up
CREATE TABLE IF NOT EXISTS posts (
id INTEGER PRIMARY KEY AUTOINCREMENT,
title TEXT NOT NULL,
body TEXT NOT NULL,
author TEXT NOT NULL,
inserted_at TEXT NOT NULL DEFAULT (datetime('now')),
updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE TRIGGER IF NOT EXISTS posts_set_updated_at
AFTER UPDATE ON posts
FOR EACH ROW
BEGIN
UPDATE posts SET updated_at = datetime('now') WHERE id = NEW.id;
END;
-- migrate:down
DROP TRIGGER IF EXISTS posts_set_updated_at;
DROP TABLE IF EXISTS posts;The part after -- migrate:up is what runs. The number that starts the file name puts the migrations in order.
The app applies the migrations that have not run yet each time it starts, and remembers which ones ran, so each runs once. spider migrate does the same without starting the app.
To add one by hand, write the file and add it to the list in src/core/db/migrations.zig:
const MIGRATIONS = [_]Migration{
// {{entry}}
.{
.version = "1791551321_create_posts",
.sql_file = @embedFile("./migrations/1791551321_create_posts.sql"),
},
.{
.version = "1791551322_seed_posts",
.sql_file = @embedFile("./migrations/1791551322_seed_posts.sql"),
},
.{
.version = "1791568783_create_users",
.sql_file = @embedFile("./migrations/1791568783_create_users.sql"),
},
.{
.version = "1791583874_posts_belong_to_users",
.sql_file = @embedFile("./migrations/1791583874_posts_belong_to_users.sql"),
},
};A project on PostgreSQL has no list: every file of the folder is built into the binary, and they run in the order of their names. Migrations in production is about that.
A migration can also put rows in: the second one of Posts adds four posts, so a new copy of the app has something to show.
Queries
A feature keeps its SQL in repository.zig. A row comes back as a struct: each column goes to the field with the same name.
/// A row of the posts table. `age` is how many seconds ago it was written.
pub const Post = struct {
id: i64,
title: []const u8,
body: []const u8,
author: []const u8,
/// The account that wrote it; 0 when it belongs to no account.
user_id: i64,
age: i64,
};db.query gives back every row, as a slice:
const columns =
"id, title, body, author, COALESCE(user_id, 0) AS user_id, " ++
"CAST(strftime('%s', 'now') - strftime('%s', inserted_at) AS INTEGER) AS age";
/// Every post, newest first.
pub fn list(arena: std.mem.Allocator) ![]model.Post {
const sql = "SELECT " ++ columns ++ " FROM posts ORDER BY inserted_at DESC, id DESC";
return db.query(model.Post, arena, sql, .{});
}
/// Every post, newest first.
pub fn list(arena: std.mem.Allocator) ![]model.Post {
const sql = "SELECT " ++ columns ++ " FROM posts ORDER BY inserted_at DESC, id DESC";
return db.query(model.Post, arena, sql, .{});
}Values never go into the SQL as text. They go in the last argument, and the SQL says where with ?1, ?2 (in PostgreSQL, $1, $2):
/// Posts whose title or body contains `term`, newest first.
pub fn search(arena: std.mem.Allocator, term: []const u8) ![]model.Post {
const sql = "SELECT " ++ columns ++ " FROM posts " ++
"WHERE instr(lower(title), lower(?1)) > 0 OR instr(lower(body), lower(?1)) > 0 " ++
"ORDER BY inserted_at DESC, id DESC";
return db.query(model.Post, arena, sql, .{term});
}db.queryOne gives back one row, or null when there is none:
pub fn find(arena: std.mem.Allocator, id: i64) !?model.Post {
const sql = "SELECT " ++ columns ++ " FROM posts WHERE id = ?1";
return db.queryOne(model.Post, arena, sql, .{id});
}To write, the same two functions. With RETURNING, an insert answers the row it made; a statement that answers nothing takes void as its type:
/// Saves a new post and gives back its id. `user_id` is the account that
/// wrote it, or null for a post that belongs to no account.
pub fn create(arena: std.mem.Allocator, input: model.Input, user_id: ?i64) !i64 {
const Row = struct { id: i64 };
const sql = "INSERT INTO posts (title, body, author, user_id) VALUES (?1, ?2, ?3, ?4) RETURNING id";
const row = try db.queryOne(Row, arena, sql, .{ input.title, input.body, input.author, user_id });
return (row orelse return error.InsertFailed).id;
}
pub fn update(arena: std.mem.Allocator, id: i64, input: model.Input) !void {
const sql = "UPDATE posts SET title = ?1, body = ?2, author = ?3 WHERE id = ?4";
_ = try db.query(void, arena, sql, .{ input.title, input.body, input.author, id });
}
pub fn delete(arena: std.mem.Allocator, id: i64) !void {
_ = try db.query(void, arena, "DELETE FROM posts WHERE id = ?1", .{id});
}The first argument after the type is where the result is kept. In a handler that is c.arena: memory of the request, freed when it ends.
Where the database is
SQLITE_PATH in .env names the file; without it the app uses db.sqlite. The file is not kept in git.
A PostgreSQL project reads PG_HOST, PG_PORT, PG_USER, PG_PASSWORD and PG_DB instead, and comes with a docker-compose.yml that starts a server for development:
docker compose up -d