# Database
## Purpose
`jerrycan::db` is SQL storage for generated backends: one `Db` handle over
SQLite and Postgres (URL-driven), module-owned dual-dialect migrations, and
SeaORM entities resolved through DI. SeaORM owns SQL rendering — placeholders,
quoting, `RETURNING`, booleans — for whichever engine is connected, so the same
entity code runs on both. Enable with the design dependency `"db"`
(or `jerrycan add db`).
## Signature
```rust
# use jerrycan::prelude::*;
# fn main() { tokio::runtime::Builder::new_current_thread().enable_all().build().unwrap().block_on(async {
use jerrycan::db::{Db, Migration};
// `from_env()` reads JERRYCAN_DATABASE_URL, defaulting to `sqlite::memory:`
// when it's unset — so dev/test "just works" with no database to provision.
let db = Db::connect("sqlite::memory:").await.unwrap(); // or postgres://…
db.migrate(&[Migration { // dual-dialect: the connected backend picks its column
name: "0001_create_notes",
sqlite: "CREATE TABLE notes (id INTEGER PRIMARY KEY AUTOINCREMENT, text TEXT NOT NULL)",
postgres: "CREATE TABLE notes (id BIGSERIAL PRIMARY KEY, text TEXT NOT NULL)",
}]).await.unwrap();
let app = App::new().extend(db); // Db is an Extension: registers itself app-wide
# let _ = app.into_test();
# }); }
```
## Minimal example
A SeaORM entity, a migration that creates its table on either backend, and a
handler that lists and inserts rows through `db.conn()`:
```rust
# use jerrycan::prelude::*;
# use jerrycan::db::sea_orm::{ActiveModelTrait, EntityTrait, Set};
# use jerrycan::db::{db_error, Db, Migration};
// One entity = one table. `DeriveEntityModel` generates Entity/ActiveModel/Column.
mod note {
use jerrycan::db::sea_orm; // the derive macros emit `sea_orm::…` paths
use jerrycan::db::sea_orm::entity::prelude::*;
#[derive(Clone, Debug, PartialEq, DeriveEntityModel)]
#[sea_orm(table_name = "notes")]
pub struct Model {
#[sea_orm(primary_key)]
pub id: i32,
pub text: String,
}
#[derive(Copy, Clone, Debug, EnumIter, DeriveRelation)]
pub enum Relation {}
impl ActiveModelBehavior for ActiveModel {}
}
async fn list(db: Dep<Db>) -> Result<Json<Vec<String>>> {
let rows = note::Entity::find().all(db.conn()).await.map_err(db_error)?;
Ok(Json(rows.into_iter().map(|n| n.text).collect()))
}
async fn create(db: Dep<Db>, Json(text): Json<String>) -> Result<Created<i32>> {
// ActiveModel + `Set` carries values as binds; `id` is left default (DB-assigned).
let note = note::ActiveModel { text: Set(text), ..Default::default() };
let saved = note.insert(db.conn()).await.map_err(db_error)?;
Ok(Created(saved.id))
}
# fn main() { tokio::runtime::Builder::new_current_thread().enable_all().build().unwrap().block_on(async {
let db = Db::connect("sqlite::memory:").await.unwrap();
db.migrate(&[Migration {
name: "0001_create_notes",
sqlite: "CREATE TABLE notes (id INTEGER PRIMARY KEY AUTOINCREMENT, text TEXT NOT NULL)",
postgres: "CREATE TABLE notes (id BIGSERIAL PRIMARY KEY, text TEXT NOT NULL)",
}]).await.unwrap();
let t = App::new()
.extend(db)
.route("/notes", get(list).post(create))
.into_test();
assert_eq!(t.get("/notes").await.json::<Vec<String>>(), Vec::<String>::new());
t.post_json("/notes", &"hello").await;
assert_eq!(t.get("/notes").await.json::<Vec<String>>(), vec!["hello".to_string()]);
# }); }
```
## Variations
Wrap multiple writes in a transaction — the closure returning `Err` rolls back
EVERY statement it issued, so a handler never leaves a partial write:
```rust
# use jerrycan::prelude::*;
# use jerrycan::db::sea_orm::{self, ConnectionTrait, TransactionError, TransactionTrait};
# use jerrycan::db::{db_error, Db};
# fn main() { tokio::runtime::Builder::new_current_thread().enable_all().build().unwrap().block_on(async {
# let db = Db::connect("sqlite::memory:").await.unwrap();
# db.conn().execute_unprepared("CREATE TABLE notes (id INTEGER PRIMARY KEY, text TEXT)").await.unwrap();
db.conn()
.transaction::<_, (), sea_orm::DbErr>(|txn| {
Box::pin(async move {
txn.execute_unprepared("INSERT INTO notes VALUES (1, 'a')").await?;
txn.execute_unprepared("INSERT INTO notes VALUES (2, 'b')").await?;
Ok(()) // returning Err here rolls BOTH inserts back
})
})
.await
// `transaction` wraps your error as `TransactionError`; both arms hold a DbErr.
.map_err(|e| match e {
TransactionError::Connection(e) | TransactionError::Transaction(e) => db_error(e),
})?;
# Result::<()>::Ok(())
# }).unwrap(); }
```
- Generated repos take `Dep<Db>` through a factory: `.provide_dep(repo::note_repo)`
(the tool-owned `lib.rs` wires this; your handlers just declare `repo: Dep<NoteRepo>`).
- `jerrycan db migrate --url postgres://…` applies module migrations from the CLI;
generated apps also migrate automatically at startup.
- Startup work (an idempotent dev seed, cache warm-up) goes in the AGENT-owned
`crates/app/src/boot.rs` — `on_boot(db: &Db)` runs after migrations and before
the app serves. It is created once and preserved across `jerrycan generate`
(the tool-owned `main.rs` calls it). Keep it idempotent: it runs on every boot.
(`jerrycan db seed` is separate — it applies a Supabase migration's streamed seed.)
- Escape hatch for hand-written SQL — `db.sql()` translates `?`→`$n` for the
connected backend, then `Statement::from_sql_and_values` binds the values:
```rust
# use jerrycan::prelude::*;
# use jerrycan::db::sea_orm::{ConnectionTrait, Statement};
# use jerrycan::db::{db_error, Db};
# fn main() { tokio::runtime::Builder::new_current_thread().enable_all().build().unwrap().block_on(async {
# let db = Db::connect("sqlite::memory:").await.unwrap();
# db.conn().execute_unprepared("CREATE TABLE notes (id INTEGER PRIMARY KEY, text TEXT)").await.unwrap();
let stmt = Statement::from_sql_and_values(
db.conn().get_database_backend(),
db.sql("INSERT INTO notes (id, text) VALUES (?, ?)"), // `?`→`$1, $2` on Postgres
[1.into(), "a".into()],
);
db.conn().execute(stmt).await.map_err(db_error)?;
# Result::<()>::Ok(())
# }).unwrap(); }
```
## Foreign keys in the schema contract (`enforced`)
`jerrycan schema` (and the committed `schema.json`) emits an `"enforced"` bool on
every foreign key. It tells you **who upholds the relation** — don't read
`on_delete` without it:
- **`enforced: true`** — a same-module `belongs_to` becomes a real database
`FOREIGN KEY` constraint (introspected from the migration). The `on_delete`
policy (`cascade`/`set_null`/`restrict`) is enforced by the DB itself.
- **`enforced: false`** — a cross-module `belongs_to` is an *indexed but
application-enforced* relation: the fk column exists (and is indexed) but there
is **no** DB constraint, because per-module migrations only create their own
tables. Here `on_delete` is honored by your handlers, **NOT** the database — so
`{ "on_delete": "cascade", "enforced": false }` does *not* mean the DB will
cascade-delete; a child row outlives its parent unless a handler removes it.
So `enforced` is the line between a DB-guaranteed constraint and a contract the
code must keep. The `belongs_to` derivation rules behind this live in
`jerrycan docs modules` (Relations); tenant-scoped relations in
`jerrycan docs tenancy`.
## Cross-module data access
A route crate exports only `module()` (the JL0001 lint enforces it), so you
CANNOT import a sibling module's `model`/`repo`. There are two supported
channels — pick by *what* you need to share:
- **A shared TYPE** (a DTO/enum both modules serialize) → put it in the app's
`shared` crate (`crates/shared/src/lib.rs`). Every route crate already depends
on `shared`; keep it deliberately tiny (a lint guards its growth).
- **Another module's TABLE** (an admin sweep, a cross-module read) → declare a
**narrow second SeaORM entity** on that table in YOUR module's agent-owned
`model.rs`. A SeaORM entity is just a typed description of a table, and the
running app has every module's tables migrated — so a second entity pointing at
the same `table_name` resolves at runtime and queries through `db.conn()` like
any of your own. Declare only the columns you actually touch.
```rust
# use jerrycan::prelude::*;
# use jerrycan::db::sea_orm::{ActiveModelTrait, ColumnTrait, EntityTrait, QueryFilter, Set};
# use jerrycan::db::{db_error, Db};
// A NARROW second entity for another module's `subscribers` table, declared in
// YOUR module's agent-owned `model.rs` — only the columns the sweep touches.
mod subscriber {
use jerrycan::db::sea_orm;
use jerrycan::db::sea_orm::entity::prelude::*;
#[derive(Clone, Debug, PartialEq, DeriveEntityModel)]
#[sea_orm(table_name = "subscribers")] // must match the OWNING module's migration
pub struct Model {
#[sea_orm(primary_key)]
pub id: i32,
pub status: String,
}
#[derive(Copy, Clone, Debug, EnumIter, DeriveRelation)]
pub enum Relation {}
impl ActiveModelBehavior for ActiveModel {}
}
// An admin sweep — flip every `pending` subscriber to `expired`. A cross-module
// write with no access to the subscribers crate, only to its table.
async fn expire_pending(db: Dep<Db>) -> Result<Json<u64>> {
let stale = subscriber::Entity::find()
.filter(subscriber::Column::Status.eq("pending"))
.all(db.conn())
.await
.map_err(db_error)?;
let mut expired = 0u64;
for row in stale {
// Update by pk: build the ActiveModel with `id` Set, change `status`.
let m = subscriber::ActiveModel {
id: Set(row.id),
status: Set("expired".to_string()),
};
m.update(db.conn()).await.map_err(db_error)?;
expired += 1;
}
Ok(Json(expired))
}
# fn main() { let _ = expire_pending; }
```
Do NOT instead hand-edit the owning module's `lib.rs` to re-export its entity, or
add any `pub` item beyond `module()` to a route crate's `lib.rs` — JL0001 flags
it, and the next `jerrycan generate` clobbers the edit (`lib.rs` is tool-owned).
`model.rs` is agent-owned; the second entity belongs there.
### Where it lives in an ENTITY-LESS module (an admin sweep with no table)
An admin/webhook module that declares **no entity of its own** has **no
`model.rs`** — the generator only writes `model.rs`/`repo.rs` for modules that
declare an entity, and such a module's `lib.rs` only carries `mod deps;` and
`mod handlers;`. So there is no natural `model.rs` home for the narrow second
entity. Put it **inline in that module's agent-owned `handlers.rs`** — the
`mod subscriber { … }` block above the sweep handler, exactly as shown above,
just in `handlers.rs` instead of `model.rs`. `handlers.rs` is agent-owned and
always present, so the inline entity survives regeneration.
Do **not** work around the missing `model.rs` by creating one and hand-adding
`mod model;` to `lib.rs`: `lib.rs` is tool-owned, so the next `jerrycan add` or
`jerrycan generate route <module>` rewrites it and your `mod` line is dropped
(the command now WARNS by name when this happens — but the line is still gone).
Keep cross-module entities in `handlers.rs` and the regeneration never touches them.
**The tradeoff — you now keep two entity definitions of one table in sync.** The
OWNING module's migration is the single source of truth for that table's schema;
your second entity is a hand-maintained view of it. If the owner adds or renames
a column your entity reads, nothing checks the two still agree — YOU update your
copy. Keeping the second entity narrow (only the columns the sweep needs) shrinks
that surface.
## Errors you'll hit
- A unique-key violation surfaces as `409 JC0409` (a re-POSTed id is the
client's fault); every other database failure is `500 JC0510`. Neither leaks
internals in the body — the real SeaORM/sqlx error goes to stderr for the
operator. Always `.map_err(db_error)?` so both codes happen for free.
- A failing migration stops the run and is NOT recorded — fix it and rerun.
## Anti-patterns
- Don't build SQL strings from request input — request values enter queries only
as binds (entity `Set`/`build_any` values, or `from_sql_and_values`). The
jerrycan lint walks repos for SQL outside `repo.rs`, and string-built SQL is
the one injection door this framework refuses to open.
- Don't share one Postgres database across parallel tests — generated acceptance
tests use `sqlite::memory:` per test for hermetic isolation.
- Booleans are NATIVE under SeaORM — model them as `bool` and use migrations
with `BOOLEAN`. Never store them as `0`/`1` integers or compare with `= 0`/`= 1`;
that old as-`i64` workaround is gone.
- JSON columns are `sea_orm` `Json` (`serde_json::Value`) — store the value
directly. Never `serde_json::to_string` it first; double-encoding turns a JSON
object into a quoted string the next reader can't parse.
- `datetime` and `uuid` design fields are `String` at the Rust layer (no native
time/uuid type yet) — the column is TEXT and the model field is `String`. Parse
and format them yourself in handlers. For a server-set create timestamp, prefer
the design sentinel `"default": "now"` on the `datetime` field (see 00-designing.md):
it drops the field from both request DTOs and the generated create stub sets it via
`now_rfc3339()` — the prelude helper returning the current UTC time as RFC3339
(`YYYY-MM-DDTHH:MM:SSZ`). Call `now_rfc3339()` directly in a handler for any other
timestamp (e.g. an `updated_at` you set on write).