Database

Feature-gated behind database. One Database handle spans SQLite, MySQL, and Postgres via sqlx's Any driver — the backend is chosen by the URL scheme. It lives in the GUI-free elyra-db crate so the CLI can use it too.

elyra = { version = "0.1", features = ["database"] }

Connecting

The easiest path is App::database, which connects lazily and binds a Database into the container:

App::new()
    .database("sqlite://app.db?mode=rwc")   // scheme picks the driver
    .commands(commands![list_todos]);

Resolve it in commands:

use elyra::{Database, Ctx};

#[command]
async fn count(ctx: Ctx) -> Result<i64, String> {
    let db = ctx.get::<Database>();
    let row = elyra::db::sqlx::query("SELECT COUNT(*) AS n FROM todos")
        .fetch_one(db.pool()).await.map_err(|e| e.to_string())?;
    Ok(elyra::db::sqlx::Row::try_get(&row, "n").map_err(|e| e.to_string())?)
}

API

• Database::connect(url).await — connect eagerly (async; for the CLI / setup).
• Database::connect_lazy(url) — build the pool without connecting; connections open on first use (sync; used by App::database).
• db.pool() — the underlying sqlx::AnyPool, for running queries.
• db.driver() — the detected Driver (Sqlite / MySql / Postgres).
• db.migrator(dir) — a Migrator sharing this pool.

Writing queries

sqlx is re-exported as elyra::db::sqlx, so app crates don't need a direct dependency:

use elyra::db::sqlx::{self, Row};

Placeholders differ per backend (? for sqlite/mysql, $1 for postgres); the Any driver does not translate them. For hand-written queries, mind the target driver — or use models, whose query builder renders placeholders per driver for you.

Building a SQLite URL from a path

use elyra_db::sqlite_url;

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

format!("sqlite://{}", path.display()) is the obvious thing to write and is wrong on Windows: the drive colon and backslashes don't match the URL grammar, and SQLite answers unable to open database file. sqlite_url normalizes the separators and uses the three-slash absolute form; it appends ?mode=rwc so the file is created when missing (sqlite_url_opts(path, false) opts out).

Pool options

Defaults are deliberately desktop-shaped (a small pool and a bounded wait, not sqlx's unlimited acquire):

use elyra::db::{Database, DatabaseOptions};
use std::time::Duration;

let db = Database::connect_with(
    url,
    DatabaseOptions::default()
        .max_connections(8)
        .acquire_timeout(Duration::from_secs(5)),
).await?;

SQLite additionally gets journal_mode = WAL, busy_timeout = 5000 and foreign_keys = ON on connect, so the UI thread and background jobs can share the file.

Transactions

db.transaction(|tx| Box::pin(async move {
    sqlx::query("UPDATE accounts SET balance = balance - 100 WHERE id = 1")
        .execute(&mut **tx).await?;
    sqlx::query("UPDATE accounts SET balance = balance + 100 WHERE id = 2")
        .execute(&mut **tx).await?;
    Ok(())
})).await?;

Commits on Ok, rolls back on Err and returns your error. db.begin() gives you the raw sqlx::Transaction when you need manual control.

Changes

Every Database clone shares a change hub: each model-layer write reports its table once it's durable — the generated insert / update / delete / save, Query's bulk update / delete / soft_delete / restore (only when a row changed), factories, and the pivot operations (attach, detach, sync, which report the pivot table).

let mut changes = db.changes().subscribe();
tokio::spawn(async move {
    while let Ok(table) = changes.recv().await {
        // "customers", "role_user", … — or `Err(Lagged)`: assume everything changed
    }
});

The model layer can't see raw SQL, so report it by hand — a table, or any other key you want to treat the same way:

sqlx::query("UPDATE orders SET status = 'shipped'").execute(db.pool()).await?;
db.touch("orders");
db.touch("settings:theme");

Inside transaction, reports are held and sent on commit; a rollback sends nothing. This is the data side of live queries (RFC 0002): elyra::db::live::track records the tables a piece of code reads, so it can be re-run when one of them changes.

Testing status

SQLite is fully test-covered (including the query builder, pagination, joins, soft deletes and transactions). MySQL and Postgres run model CRUD against real servers in CI.

Related

• Migrations · Models
• Configuration — [database]