Models, queries and relationships

A model represents application data. A repository handles database access so HTTP handlers do not contain SQL.

1. Define a model

This Note value represents one row of a notes table:

struct Note:
    var id: Int
    var title: String

    def __init__(out self, id: Int, title: String):
        self.id = id
        self.title = title

2. Create the table

CREATE TABLE notes (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL
);

3. Insert a note safely

For PostgreSQL:

from mojara.dbclient import PooledDatabase

def add_note(db: PooledDatabase, id: String, title: String) raises:
    var lease = db.acquire(timeout_ms=3000)
    var result = lease.query2(
        "INSERT INTO notes (id, title) VALUES ($1::int, $2::text)",
        id, title,
    )
    lease.release()

Explanation: the function accepts the database pool and values. query2 binds parameters without assembling SQL strings from user input. The lease goes back to the pool after the operation.

4. Read rows into model values

def list_notes(db: PooledDatabase) raises:
    var lease = db.acquire(timeout_ms=3000)
    var result = lease.query("SELECT id, title FROM notes ORDER BY id")
    for i in range(result.rows()):
        var note = Note(Int(result.cell(i, 0)), result.cell(i, 1))
        print(note.id, note.title)
    lease.release()

This example maps each SQL row to a Mojo value. For a web endpoint, return these values as serialized JSON rather than printing them.

5. One-to-many relationships

Suppose each author can own many notes:

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
ALTER TABLE notes ADD COLUMN author_id INTEGER REFERENCES authors(id);
SELECT authors.name, notes.title
FROM notes JOIN authors ON notes.author_id = authors.id;

A relation is represented by the foreign key author_id. The SQL JOIN combines the tables. Choose eager loading when you need many related rows rather than running an additional query for every result.

See Migrations for schema evolution and Pools for transactions.