todos_orm_sqlite

A complete REST API using SQLite database with ORM integration.

Source Code

Path: examples/todos_orm_sqlite/

const std = @import("std");
const tk = @import("tokamak");
const fr = @import("fridge");
const Status = std.http.Status;

const Todo = struct {
    pub const sql_table_name = "todos";
    id: ?u32 = null,
    title: []const u8,
    is_done: bool = false,
};

pub const PatchTodoReq = struct {
    title: ?[]const u8 = null,
    is_done: ?bool = null,
};

const App = struct {
    db_pool: fr.Pool(fr.SQLite3),
    db_opts: fr.SQLite3.Options = .{ .filename = ":memory:" },
    db_pool_opts: fr.PoolOptions = .{ .max_count = 4 },
    server: tk.Server,
    server_opts: tk.ServerOptions = .{ .listen = .{ .port = 8080 } },
    routes: []const tk.Route = &.{
        // add debug logging
        tk.logger(.{}, &.{
            // provide the db session, group endpoints under /todo
            .provide(fr.Pool(fr.SQLite3).getSession, &.{.group("/todo", &.{
                .get("/", readAll),
                .get("/:id", readOne),
                .post("/", create),
                .put("/:id", update),
                .patch("/:id", patch),
                .delete("/:id", delete),
            })}),
        }),
    },

    pub fn configure(bundle: *tk.Bundle) void {
        // Register some callbacks to be auto-called during the app init.
        bundle.addInitHook(initDb);
        bundle.addInitHook(printServerPort);
    }

    fn initDb(allocator: std.mem.Allocator, db_pool: *fr.Pool(fr.SQLite3)) !void {
        var db = try db_pool.getSession(allocator);
        defer db.deinit();

        try db.exec(
            \\ CREATE TABLE IF NOT EXISTS todos (
            \\   id INTEGER PRIMARY KEY AUTOINCREMENT,
            \\   title TEXT NOT NULL,
            \\   is_done BOOLEAN NOT NULL
            \\ );
        , .{});
    }

    fn printServerPort(server: *tk.Server) void {
        std.debug.print("Starting tokamak on: http://localhost:{d}/todo\n", .{server.http.config.port.?});
    }
};

pub fn main() !void {
    try tk.app.run(tk.Server.start, &.{App});
}

fn readOne(db: *fr.Session, id: u32) !Todo {
    return try db.query(Todo).find(id) orelse error.NotFound;
}

fn readAll(db: *fr.Session) ![]const Todo {
    return try db.query(Todo).findAll();
}

fn create(res: *tk.Response, db: *fr.Session, body: Todo) !Todo {
    res.status = @intFromEnum(Status.created);
    return try db.query(Todo).insert(body).returning("*").fetchOne(Todo) orelse error.InternalServerError;
}

fn update(db: *fr.Session, id: u32, body: Todo) !void {
    return try db.update(Todo, id, body);
}

fn patch(db: *fr.Session, id: u32, body: PatchTodoReq) !void {
    return try patchSetFields(db, Todo, PatchTodoReq, id, body);
}

fn delete(db: *fr.Session, id: u32) !void {
    try db.query(Todo).where("id", id).delete().exec();
}

// helper for updating all fields which are set in the body and not null / undefined
fn patchSetFields(db: *fr.Session, comptime RowType: type, comptime BodyType: type, id: u32, body: BodyType) !void {
    var row = try db.query(RowType).find(id) orelse return error.NotFound;

    inline for (
        std.meta.fields(BodyType),
    ) |field| {
        const body_field = @field(body, field.name);
        const is_set = if (switch (@typeInfo(field.type)) {
            .pointer => |ptr| ptr.size == .slice,
            else => false,
        }) {
            const Child = std.meta.Child(field.type);
            return !std.mem.eql(Child, body_field, null);
        } else body_field != null;

        // if the field is not null, update the row's field value
        if (is_set) {
            @field(row, field.name) = body_field.?;
        }
    }

    try db.update(RowType, id, row);
}

Features Demonstrated

  • Database integration with fridge ORM
  • Connection pooling
  • Database migrations
  • CRUD operations with SQL
  • PATCH endpoint with partial updates
  • Lifecycle hooks (configure, initDb)
  • Custom middleware composition

Model

const Todo = struct {
    pub const sql_table_name = "todos";
    id: ?u32 = null,
    title: []const u8,
    is_done: bool = false,
};

Database Setup

The app uses lifecycle hooks to initialize the database:

const App = struct {
    db_pool: fr.Pool(fr.SQLite3),
    db_opts: fr.SQLite3.Options = .{ .filename = ":memory:" },
    db_pool_opts: fr.PoolOptions = .{ .max_count = 4 },
    server: tk.Server,
    server_opts: tk.ServerOptions = .{ .listen = .{ .port = 8080 } },
    routes: []const tk.Route = &.{
        // add debug logging
        tk.logger(.{}, &.{
            // provide the db session, group endpoints under /todo
            .provide(fr.Pool(fr.SQLite3).getSession, &.{.group("/todo", &.{
                .get("/", readAll),
                .get("/:id", readOne),
                .post("/", create),
                .put("/:id", update),
                .patch("/:id", patch),
                .delete("/:id", delete),
            })}),
        }),
    },

    pub fn configure(bundle: *tk.Bundle) void {
        // Register some callbacks to be auto-called during the app init.
        bundle.addInitHook(initDb);
        bundle.addInitHook(printServerPort);
    }

    fn initDb(allocator: std.mem.Allocator, db_pool: *fr.Pool(fr.SQLite3)) !void {
        var db = try db_pool.getSession(allocator);
        defer db.deinit();

        try db.exec(
            \\ CREATE TABLE IF NOT EXISTS todos (
            \\   id INTEGER PRIMARY KEY AUTOINCREMENT,
            \\   title TEXT NOT NULL,
            \\   is_done BOOLEAN NOT NULL
            \\ );
        , .{});
    }

    fn printServerPort(server: *tk.Server) void {
        std.debug.print("Starting tokamak on: http://localhost:{d}/todo\n", .{server.http.config.port.?});
    }
};

API Endpoints

Create Todo

curl -X POST -H "content-type: application/json" \
  -d '{ "title": "my todo" }' \
  http://localhost:8080/todo

Status: 201 Created

{ "id": 1, "title": "my todo", "is_done": false }

List All Todos

curl http://localhost:8080/todo

Status: 200 OK

[{ "id": 1, "title": "my todo", "is_done": false }]

Get Single Todo

curl http://localhost:8080/todo/1

Status: 200 OK

{ "id": 1, "title": "my todo", "is_done": false }

Update Todo (Full Replacement)

curl -X PUT -H "content-type: application/json" \
  -d '{ "id": 1, "is_done": true, "title": "my todo" }' \
  http://localhost:8080/todo/1

Status: 204 No Content

Patch Todo (Partial Update)

curl -X PATCH -H "content-type: application/json" \
  -d '{ "title": "new title only" }' \
  http://localhost:8080/todo/1

Status: 204 No Content

Only the fields provided are updated. This example includes a helper function patchSetFields that handles partial updates generically.

Delete Todo

curl -X DELETE http://localhost:8080/todo/1

Status: 204 No Content

Handler Examples

Create with Custom Status

fn create(res: *tk.Response, db: *fr.Session, body: Todo) !Todo {
    res.status = @intFromEnum(Status.created);
    return try db.query(Todo).insert(body).returning("*").fetchOne(Todo) orelse error.InternalServerError;
}

Read with ORM

fn readOne(db: *fr.Session, id: u32) !Todo {
    return try db.query(Todo).find(id) orelse error.NotFound;
}

fn readAll(db: *fr.Session) ![]const Todo {
    return try db.query(Todo).findAll();
}

Delete with Query Builder

fn delete(db: *fr.Session, id: u32) !void {
    try db.query(Todo).where("id", id).delete().exec();
}

Configuration

Database and connection pool options are configured in the App struct:

    db_pool: fr.Pool(fr.SQLite3),
    db_opts: fr.SQLite3.Options = .{ .filename = ":memory:" },
    db_pool_opts: fr.PoolOptions = .{ .max_count = 4 },
  • Change filename to a path like "db.sqlite" for persistence
  • Adjust max_count for connection pool size

Running

cd examples/todos_orm_sqlite
zig build run

The server starts at http://localhost:8080/todo

Dependency Injection Pattern

Database sessions are provided to handlers via middleware:

    routes: []const tk.Route = &.{
        // add debug logging
        tk.logger(.{}, &.{
            // provide the db session, group endpoints under /todo
            .provide(fr.Pool(fr.SQLite3).getSession, &.{.group("/todo", &.{
                .get("/", readAll),
                .get("/:id", readOne),
                .post("/", create),
                .put("/:id", update),
                .patch("/:id", patch),
                .delete("/:id", delete),
            })}),
        }),
    },

Handlers simply request db: *fr.Session and get an active session.

Next Steps