How-To: The Database Layer

How-To: Use the Database Layer

Vox utilizes a unified storage paradigm known as Codex, which compiles into type-safe SQLite database schemas and Rust structs. You never need to write raw migrations; they are deterministically derived from your file structures.

Defining a Table

Any type struct declared with the table keyword becomes a persistent database entity.

table Note {
    title: str
    content: str
}

Indexing for Performance

To speed up lookups on large datasets, use the index keyword. Vox determines the optimal storage engine (B-Tree or Hash) and generates the SQL automatically.

table User {
    email: str
    team_id: Id[Team]
}

// Index: speeds up email lookups
index User.unique_email on (email)

// Composite index: speeds up filtered team lookups
index User.by_team on (team_id, email)

[!TIP] Always index foreign keys (like Id[T]) if you plan to filter or join on them frequently.

Basic CRUD Accessors

The built-in db module uses code-generation to inject statically typed accessors for all your table types.

Create:

// vox:skip -- excerpt; Task table and imports come from the rest of the file
let new_id: Id[Task] = db.Task.insert({
    title: "Clean desk",
    done: false,
    priority: 1,
    owner: "alice"
})

Read:

// vox:skip -- excerpt; new_id comes from the Create step above
match db.Task.find(new_id) {
    Some(t) -> println(t.title)
    None    -> println("Not found")
}

Update:

// vox:skip -- excerpt; new_id comes from the Create step above
db.Task.update(new_id, { done: true })

Delete:

// vox:skip -- excerpt; new_id comes from the Create step above
db.Task.delete(new_id)

Advanced Filtering

Instead of raw string interpolation, use Vox’s exact literal querying to avoid injection attacks.

// Fetch simple exact match parameters

let alice_tasks = db.Task.filter({ owner: "alice" })

// Advanced predicate-object queries

let urgent_tasks = db.Task.where({ priority: { gt: 10 }, done: { eq: false } }).all()

Query Chaining

You can apply limits, multi-field ordering, and select specific field projections by chaining.

let feed = db.Task.where({ done: false }).order_by("priority", "desc").limit(10).all()

Guarding Reads/Writes with `query` and `mutation`

For security, you should rarely expose db.* calls directly to UI islands or agents. Instead, wrap your database interactions in query (read-only) and mutation (write-enabled) functions.

The compiler verifies that a query function does not contain .insert, .update, or .delete operations.

Transactional Integrity with `mutation`

Every function declared with mutation is automatically wrapped in a database transaction. If the function returns an Error or panics, the transaction is rolled back.

// vox:skip -- illustrative future syntax; db.Table.update() is not implemented yet
table Account {
    balance: int
}

mutation transfer_funds(from: Id[Account], to: Id[Account], amount: int) to Result[Unit] {
    let mut sender = db.Account.find(from)?
    let mut receiver = db.Account.find(to)?

    sender.balance -= amount
    receiver.balance += amount

    db.Account.update(from, sender)
    db.Account.update(to, receiver)

    return Ok(())
}

Under the hood, this uses Codex::transaction to ensure ACID compliance across the local SQLite or distributed Turso mesh.

The Escape Hatch: Raw SQL

Occasionally, complex analytic aggregations exceed the currently supported ORM builder patterns. You can drop down to raw SQL using db.query.

[!WARNING] Use this only as a last resort. Raw SQL queries bypass Vox’s type checking checks on schema changes.

let count = db.query("SELECT COUNT(*) FROM Task WHERE owner = ?", ["alice"])

A Note on Codex

When running vox-run, the backing data source is the Local Codex Store (an embedded SQLite engine on disk). For enterprise orchestration and Populi GPU meshes, the database seamlessly promotes to Turso cloud sync clusters dynamically, without requiring any changes to your .vox schema definitions!

Related Topics: