Database & Models

The server returns hardcoded JSON. Let's give it a real database. By the end of this page you'll have a SQLite schema, a model struct that maps between database rows and JSON, and query functions for notes — each stamped with a creation time.

Step 1 — Schema

Create src/db/schema.ks:

module notes.db

import talon.sqlite.database.(Database)
import talon.sqlite.error.(SqliteError)

public func initSchema(path: String) -> () throws SqliteError {
    let db = try Database(path);

    try db.execute("""
        CREATE TABLE IF NOT EXISTS notes (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            title TEXT NOT NULL,
            body TEXT NOT NULL,
            created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now'))
        )
        """);
    .Ok(())
}

initSchema opens a database connection and creates the notes table. A few things to note:

The return type () throws SqliteError means the function can fail. The try keyword before each call propagates errors — if any statement fails, the function returns early with that error.
Triple-quoted strings ("""...""") let you write multi-line SQL without escaping.
created_at stores a Unix timestamp — seconds since 1970-01-01. SQLite's strftime('%s', 'now') fills it automatically on insert. We'll convert it to a proper date using the datetime library when displaying notes.
The function ends with .Ok(()) to explicitly return success. In a throws function, you can also just let the last expression be (), but being explicit makes the intent clear.

Step 2 — Call it from main

Update src/main.ks to initialize the schema on startup. Add the import and the call before opening the shared connection:

import notes.db.(initSchema)

@main
func main() {
    match initSchema("notes.db") {
        .Ok(_) => {},
        .Err(e) => {
            println("Failed to initialize database: " + e.description());
            return
        }
    };

    let db = match SharedDatabase("notes.db") {
        // ... rest unchanged
    };
}

initSchema opens its own temporary connection to create the table, then closes it. The SharedDatabase opened afterward is the long-lived connection shared across all handlers. Run flock run — you should see notes.db appear in your project directory.

Step 3 — Note model

Models bridge two worlds: database rows and JSON. A single struct can conform to both FromRow (for reading from SQLite) and Serialize (for writing JSON). Create src/models/note.ks:

module notes.models

import talon.sqlite.row.(Row, FromRow)
import talon.sqlite.error.(SqliteError)
import quill.value.(Value)
import quill.serialize.(Serialize)
import quill.error.(SerializeError)
import datetime.(Instant)

public struct Note: FromRow, Serialize, Cloneable {
    public var id: Int64
    public var title: String
    public var body: String
    public var createdAt: Int64

    public static func fromRow(row: Row) -> Note throws SqliteError {
        Note(
            id: try row.read[Int64](at: 0),
            title: try row.read[String](at: 1),
            body: try row.read[String](at: 2),
            createdAt: try row.read[Int64](at: 3)
        )
    }

    public func toValue() -> Result[Value, SerializeError] {
        var obj = Dictionary[String, Value]();
        obj.insert("id", Value.Int(self.id));
        obj.insert("title", Value.Str(self.title));
        obj.insert("body", Value.Str(self.body));
        let timestamp = Instant(secondsSinceEpoch: self.createdAt);
        obj.insert("created_at", Value.Str("\(timestamp)"));
        .Ok(Value.Obj(obj))
    }

    public func clone() -> Note {
        Note(id: self.id, title: self.title.clone(), body: self.body.clone(), createdAt: self.createdAt)
    }
}

FromRow requires a static func fromRow that reads columns by position. Serialize requires a toValue that builds a Value.Obj — Quill's representation of a JSON object. Cloneable lets Perch copy the struct when needed.

The createdAt field is stored as an Int64 — the raw epoch seconds from SQLite. In toValue, we convert it to an Instant and interpolate it into a string. Because Instant conforms to Formattable, string interpolation produces an RFC 3339 timestamp like "2026-05-27T15:30:05Z".

Step 4 — Note queries

Create src/db/notes.ks:

module notes.db

import talon.sqlite.executor.(SqliteExecutor)
import talon.sqlite.error.(SqliteError)
import notes.models.(Note)

public func listNotes(db: some SqliteExecutor) -> Array[Note] throws SqliteError {
    db.query[Note]("SELECT id, title, body, created_at FROM notes ORDER BY created_at DESC")
}

public func findNoteById(db: some SqliteExecutor, id noteId: Int64) -> Note? throws SqliteError {
    let rows = try db.query[Note]("""
        SELECT id, title, body, created_at FROM notes WHERE id = \(noteId)
        """);
    if rows.count > 0 { .Ok(.Some(rows(0))) } else { .Ok(.None) }
}

public func createNote(db: some SqliteExecutor, title: String, body: String) -> Note throws SqliteError {
    try db.execute("""
        INSERT INTO notes (title, body) VALUES (\(title), \(body))
        """);
    let rows = try db.query[Note]("""
        SELECT id, title, body, created_at FROM notes WHERE rowid = last_insert_rowid()
        """);
    .Ok(rows(0))
}

public func updateNote(db: some SqliteExecutor, id noteId: Int64, title: String, body: String) -> Note? throws SqliteError {
    try db.execute("""
        UPDATE notes SET title = \(title), body = \(body)
        WHERE id = \(noteId)
        """);
    let rows = try db.query[Note]("""
        SELECT id, title, body, created_at FROM notes WHERE id = \(noteId)
        """);
    if rows.count > 0 { .Ok(.Some(rows(0))) } else { .Ok(.None) }
}

public func deleteNote(db: some SqliteExecutor, id noteId: Int64) -> () throws SqliteError {
    try db.execute("DELETE FROM notes WHERE id = \(noteId)");
    .Ok(())
}

The parameter db: some SqliteExecutor uses an opaque type — the function accepts any type that conforms to the SqliteExecutor protocol. Both Database and SharedDatabase conform, so these query functions work with either.

The string interpolation in SQL queries is safe — Talon-SQLite treats interpolated values as parameterized bindings, not raw string concatenation. \(title) becomes a ? placeholder with title bound as a parameter.

db.query[Note] is generic — it calls Note.fromRow on each result row and returns an Array[Note].

findNoteById uses labeled parameters: id noteId: Int64. The first name is the label callers see (id:), and the second is the local binding inside the function. This makes call sites read naturally: findNoteById(db, id: 5).

Notes are ordered by created_at DESC so the newest notes appear first. The createNote function doesn't pass a created_at value — SQLite fills it automatically with the current time via the column default.

What you saw

Step	Feature
1	`throws` functions, `try` propagation, multi-line strings, Unix timestamps
2	Calling a `throws` function with `match`
3	Protocol conformance (`FromRow`, `Serialize`, `Cloneable`), `static func`, `Instant` for timestamp formatting
4	Opaque types (`some SqliteExecutor`), generic queries (`db.query[T]`), safe SQL interpolation, labeled parameters

The pattern is worth internalizing: define a struct, conform it to FromRow and Serialize, write query functions that return it. Every data type in the app follows this shape.