Database

A JSON file worked for the first few weeks, but the box office now sells tickets for dozens of venues, and the staff need the line-up in one place that every instance of the box office shares. Time for PostgreSQL.

You never write database queries by hand in Margaret. Tables are Rust structs with #[model], and Margaret generates a typed query API for each of them while your project builds. A query that would scan a whole table, read without a bound, or load related rows one by one doesn't compile.

Declaring the database

The box office has one database. Declare it once, and name the environment variables that configure it:

use margaret::framework::macros::postgres_database;

#[postgres_database(
    url_from = "DATABASE_URL",
    max_connections_from = "DATABASE_MAX_CONNECTIONS"
)]
pub struct BoxOfficeDatabase;

Margaret connects a pool when the application starts, and any singleton can ask for it as Arc. A declared database that nothing uses is a build error:

Error: Container { source: UnconsumedDeclaration { path: "margaret::framework::database::database::Database" } }

Defining models

Mark the struct with #[model] and every field with #[column]. Option makes a column nullable. ColumnDefault::UuidV7 lets the database pick the primary key, so the box office never generates one itself:

use margaret::framework::macros::model;
use margaret::framework::model::column_default::ColumnDefault;
use uuid::Uuid;

#[model(table = "venues")]
#[derive(Clone)]
pub struct Venue {
    #[column(primary_key, default = ColumnDefault::UuidV7)]
    pub id: Uuid,
    #[column(unique)]
    pub name: String,
    #[column]
    pub city: String,
}

Relationships, indexes, and constraints

A concert takes place at a venue. A field of type Key points at a venue's primary key, and #[foreign_key] decides what happens when that venue goes away. Restrict keeps the staff from deleting a venue that still has concerts:

#[model(table = "concerts")]
#[index(name = "concerts_by_start", fields = [starts_at, id])]
#[derive(Clone)]
pub struct Concert {
    #[column(primary_key, default = ColumnDefault::UuidV7)]
    pub id: Uuid,
    #[column(unique)]
    pub slug: String,
    #[column]
    pub artist: String,
    #[column]
    pub support: Json<Vec<String>>,
    #[column]
    pub starts_at: DateTime<Utc>,
    #[column(precision = 8, scale = 2)]
    pub price: Decimal,
    #[column(minimum = 1)]
    pub capacity: i32,
    #[column]
    #[foreign_key(on_delete = OnDelete::Restrict)]
    #[index]
    pub venue: Key<Venue>,
}

A few things to notice:

• #[column(unique)] and #[column(minimum = 1)] become constraints the database enforces.

• Json stores any serializable value, here the list of support acts.

• Decimal needs a precision and a scale, because prices must never round.

• The concerts_by_start index lists concerts in the order they start. The id at its end makes the order unique, which lets Margaret page through it.

• Every key must lead an index, so finding a venue's concerts never scans the table. Leave out the #[index] on venue, and the build stops:

Error: Model { source: UnindexedForeignKey { field: "venue", model: "crate::concerts::concert::Concert" } }

Add chrono, uuid, and rust_decimal to your dependencies for these column types.

Loading related rows

A Key holds only the venue's primary key. To read concerts together with their venues, describe the shape you want with #[eager_load]:

use margaret::framework::macros::eager_load;

use crate::concerts::concert::Concert;
use crate::concerts::venue::Venue;

#[eager_load(model = Concert)]
pub struct ConcertWithVenue {
    #[base]
    pub concert: Concert,
    #[relation(venue)]
    pub venue: Venue,
}

Margaret loads the whole shape at once. There's no way to reach a venue through a concert row by row: concert.venue.name doesn't compile, because a key has no name:

error[E0609]: no field `name` on type `margaret::framework::margaret_active_record::key::Key<Venue>`

Querying

The line-up file goes away. A ConcertRepository takes its place: it holds the database and the clock, and it's the one place that asks about concerts:

#[singleton]
pub struct ConcertRepository {
    clock: Arc<Clock>,
    database: Arc<Database>,
}

impl ConcertRepository {
    #[constructor]
    pub fn create(clock: Arc<Clock>, database: Arc<Database>) -> anyhow::Result<Self> {
        Ok(Self { clock, database })
    }

    pub async fn upcoming(&self) -> Result<Vec<ConcertWithVenue>, ActiveRecordError> {
        Ok(Concert::query()
            .starts_at
            .above(self.clock.now())
            .ascending()
            .limit::<50>()
            .load::<ConcertWithVenue, _>(self.database.as_ref())
            .await?
            .records)
    }

Read the query from top to bottom: start at the concerts, walk the concerts_by_start index from now on, in ascending order, at most 50 at a time, and load each concert with its venue. The fields you can walk are exactly the leading fields of your primary key, unique columns, and indexes. Filter by artist, which has no index yet, and it doesn't compile:

error[E0609]: no field `artist` on type `concerts_concert_concert::query::step::Step`
   |
   = note: available fields are: `id`, `slug`, `starts_at`, `venue`

Every read has a bound, and the bound is part of the type. Leave out limit, and there's nothing to fetch:

error[E0599]: no method named `fetch` found for struct `margaret::framework::margaret_active_record::scan::Scan<Modeled, Ordering, Toward>` in the current scope

The web page shows the next 50 concerts. The staff want the whole season in the terminal, so the repository pages through all of them. Each page tells you whether it Continues, with a cursor to resume from, or is Exhausted:

    pub async fn season(&self) -> Result<Vec<ConcertWithVenue>, ActiveRecordError> {
        let mut page = Concert::query()
            .starts_at
            .above(self.clock.now())
            .ascending()
            .limit::<50>();
        let mut season = Vec::new();

        loop {
            let Page { next, records } = page
                .clone()
                .load::<ConcertWithVenue, _>(self.database.as_ref())
                .await?;

            season.extend(records);

            match next {
                NextPage::Continues(cursor) => page = page.resume(cursor),
                NextPage::Exhausted => return Ok(season),
            }
        }
    }

The lineup command asks for the repository instead of the old Lineup:

    #[process]
    pub async fn run(&self) -> anyhow::Result<CommandOutcome> {
        for ConcertWithVenue { concert, venue } in self.concerts.season().await? {
            println!(
                "{}  {} at {}, {}",
                concert.starts_at.format("%b %e"),
                concert.artist,
                venue.name,
                venue.city
            );
        }

        Ok(CommandOutcome::Succeeded)
    }

Creating the database

Your models describe the whole database, so Margaret generates a schema command. It needs PostgreSQL 18, and prints everything the box office needs: a table for every model, with its primary key, unique constraints, checks, foreign keys, and indexes, created in dependency order. Feed it to a fresh database:

$ box_office schema | psql "$DATABASE_URL"

There are no migrations. In development, recreate the database whenever your models change. Then point the box office at it:

$ DATABASE_URL=postgres://localhost/box_office DATABASE_MAX_CONNECTIONS=4 box_office lineup

The tables are empty for now. Console commands fills them.

What the build checks

Unsupported column types, keys to structs that aren't models, keys without an index, cycles between tables, duplicate or redundant indexes, and names PostgreSQL wouldn't accept are all build errors. The Queries cheat sheet lists every query the generated API offers.