A database on the desktop

Freddy's notes need to live somewhere. On the desktop that is usually a SQLite file next to the app, and Elyra treats it the way Laravel treats any database: migrations, a schema builder, and an Active Record model.

One Database

The database layer is behind the database feature, so Freddy turns it on in Cargo.toml, on the same tagged dependency as before:

elyra = { git = "https://github.com/kwhorne/elyra-framework", tag = "v0.16.0", features = ["database"] }

One Database handle spans SQLite, MySQL and Postgres through sqlx, and the URL scheme picks the driver. App::database connects lazily and binds the handle into the container, so commands get it with ctx.get::<Database>():

use elyra_db::sqlite_url;

let path = elyra::system::paths().data.map(|d| format!("{d}/freddy-notes/notes.db")).unwrap();
App::new().database(sqlite_url(&path))

That puts the file in the user's data folder. sqlite_url is there for a reason the database page spells out: format!("sqlite://{}", ...) is the obvious thing to write and is wrong on Windows. SQLite also gets WAL mode, a busy timeout and foreign keys on connect, so the window and background jobs can share the file, and the pool is small with a bounded wait, which suits a desktop.

Migrations

Migrations are Elyra's php artisan migrate: SQL files applied in order, tracked in a _elyra_migrations table and grouped into batches, so a rollback undoes the most recent migrate as a unit.

rata make:migration create_notes   # scaffold up + down files
rata migrate                       # apply all pending, as one new batch
rata migrate:status                # list applied and pending
rata migrate:rollback              # undo the most recent batch

Hand-written SQL differs per driver, so there is a schema builder that renders it for SQLite, MySQL or Postgres. With --rust, make:migration scaffolds a Rust migration that uses it. Freddy's notes table:

Schema::create("notes", |t| {
    t.id();
    t.foreign_id("notebook_id", "notebooks").on_delete_cascade();
    t.string("title");
    t.text("body");
    t.boolean("pinned").default_value("0");
    t.timestamps();
    t.soft_deletes();
    t.index("title");
})

Rust and SQL migrations share one history table and mix freely. When src/ has a Rust migration, rata migrate runs the app in its migrate mode, which knows them; ELYRA_MIGRATE=up cargo run does the same without rata. A seeder fills demo data with ELYRA_SEED=1 cargo run. One portability rule from the migrations page: boolean columns are INTEGER 0/1, which is what the schema builder writes and what models expect.

Models

#[derive(Model)] is Elyra's Eloquent: a thin Active Record layer over the pool, not a second ORM. Freddy's note:

#[derive(Model, serde::Serialize, serde::Deserialize, specta::Type)]
#[model(table = "notes", timestamps, soft_deletes, belongs_to(Notebook, fk = "notebook_id"))]
struct Note {
    #[model(id)] id: i64,
    notebook_id: i64,
    title: String,
    body: String,
    pinned: bool,              // INTEGER 0/1
    created_at: i64,
    updated_at: i64,
    deleted_at: Option<i64>,
}

Because a model is a plain struct with specta::Type, a command that returns Vec<Note> becomes Promise<Note[]> in bindings.ts. And unknown options in #[model(...)] are compile errors: a misspelt option used to compile into something quietly wrong.

let db = ctx.get::<Database>();
let mut note = Note { id: 0, notebook_id: 1, title: "Train ideas".into(), body: "".into(),
                      pinned: false, created_at: 0, updated_at: 0, deleted_at: None };
note.insert(&db).await?;          // sets note.id and the timestamps
note.pinned = true;
note.save(&db).await?;            // insert when id == 0, otherwise update

let pinned = Note::query().where_eq("pinned", true).order_by_desc("updated_at").get(&db).await?;

What a notes app needs from it

• A search box. when_some applies a clause only when there is something to filter on, and or_where_like searches several columns at once: Note::query().when_some(term, |q, t| q.or_where_like(&["title", "body"], format!("%{t}%"))).
• Pages. paginate(&db, page, 25) returns a Page with data, total and last_page, and a command can return it as is: the page gets Promise<Page<Note>>.
• A bin. With soft_deletes every query skips trashed rows; soft_delete and restore go through the builder, and with_trashed reaches them. Note that the instance method note.delete(&db) is a hard delete.
• Notebooks without N+1. A relation accessor is one query each, so for a list there is a load_* method that loads them all in one query and returns a map.
• Test data. A Factory is a recipe for a valid row: Note::factory().count(50).create(&db).

Every write reports its table

One last detail sets up chapter 7. Every model-layer write reports its table once it is durable, and inside a transaction the reports wait for the commit. Raw SQL cannot be seen, so after one Freddy calls db.touch("notes"). That stream of changed tables is what lets a list on screen update itself.

What you learned

• The database feature gives one Database for SQLite, MySQL and Postgres; sqlite_url builds a file URL that also works on Windows.
• rata migrate and its friends run SQL or Rust migrations, in batches, with a schema builder for portable DDL.
• #[derive(Model)] gives Active Record, a query builder, relations with eager loading, soft deletes, pagination and factories.
• What is not implemented yet is listed on the models page, and every write reports its table.