Ingot (database)
Database and models
On this page
The database is set by DATABASE_URL in .env. Each backend is a Cargo feature of smeltery:
| Backend | Feature | DATABASE_URL |
|---|---|---|
| SQLite | sqlite |
sqlite://database/database.sqlite (relative to the app root; the file is created when missing, its directory must exist), sqlite:///srv/app/database.sqlite, sqlite::memory: |
| PostgreSQL | postgres |
postgres://user:password@localhost/my_app |
| MySQL / MariaDB | mysql |
mysql://user:password@localhost/my_app (mariadb://… works the same) |
The app connects while it boots; when it cannot, boot fails with the reason. A URL whose backend feature is off is
an error naming the feature. Every connection and every wait for a pooled connection is bounded by
DB_CONNECT_TIMEOUT. Connection errors show the URL with *** in place of the user name and password. A /, ?,
# or @ in the password is written percent-encoded (%2F, %3F, %23, %40).
SQLite database files run in WAL journal mode with synchronous=NORMAL, and a connection waits up to 5 seconds for
another one's lock before it reports "database is locked". Readers do not block the writer, so serve and a
migrate run can share the file. WAL keeps two more files next to the database (database.sqlite-wal and
database.sqlite-shm): copy all three in a backup, or use SQLite's .backup command. WAL needs a local file system
(not a network share). In-memory databases and ?mode=ro URLs keep their own journal mode. Every SQLite connection
runs with recursive_triggers on, so INSERT OR REPLACE fires the delete triggers of the row it replaces. A trigger
of the app's own that writes to the table it fires on fires itself again: give it a guard, AFTER UPDATE OF <column>
on a column it does not write, or WHEN new.<column> IS NOT old.<column>. On Unix a new database
file is created readable by its owner only (0600), and SQLite gives the -wal / -shm files the same mode.
smeltery::db::Db is the handle: a pool, cheap to clone. Handlers take it as an argument (db: Db), other code
calls app.db()?. db.execute(sql) runs one SQL statement exactly as written, so it never holds values from a
request; db.execute_with(sql, values) (affected rows) and db.query_with(sql, values) (the rows) bind values as
parameters: ? on SQLite and MySQL, $1, $2 … on PostgreSQL. db.backend() names the engine, and db.conn() is
SeaORM's DatabaseConnection for anything else.
db.execute_with("UPDATE users SET active = ? WHERE email = ?", [true.into(), email.into()])
.await?;
let rows = db.query_with("SELECT name FROM users WHERE id > ?", [10.into()]).await?;
for row in rows {
let name: String = row.try_get("", "name")?;
println!("{name}");
}
Models are SeaORM 2.0 entities. app/models/post.rs:
use smeltery::db::prelude::*;
#[sea_orm::model]
#[derive(Clone, Debug, PartialEq, DeriveEntityModel, Serialize, Deserialize)]
#[sea_orm(table_name = "posts")]
pub struct Model {
#[sea_orm(primary_key)]
pub id: i64,
pub title: String,
pub body: String,
pub created_at: Option<DateTimeUtc>,
pub updated_at: Option<DateTimeUtc>,
}
impl ActiveModelBehavior for ActiveModel {}
app/models/mod.rs names it: pub use post::Model as Post;. smeltery::db::prelude::* brings the sea_orm crate
itself (for the SeaORM macros), SeaORM's entity prelude and query traits, Set / NotSet / Unchanged, the column
types (DateTimeUtc, Date, Json, Uuid …), serde's Serialize / Deserialize, and Smeltery's Db, Record
and Found. The serde derives expand to code that names the serde crate, so an app that uses them depends on it
(cargo add serde --features derive).
Smeltery's Record trait gives every model the everyday calls:
| Call | Returns |
|---|---|
Post::all(&db) |
every row: Result<Vec<Post>> |
Post::find(&db, id) |
Result<Option<Post>> |
Post::find_or_404(&db, id) |
the row, or a 404 Error |
Post::count(&db) |
Result<u64> |
Post::create(&db, post::ActiveModel { title: Set(..), ..Default::default() }) |
the inserted row |
post.update(&db, \|m\| { m.title = Set(..); }) |
the updated row (the closure edits the ActiveModel) |
post.delete(&db) |
Result<()> |
Post::paginate(&db, page) |
one Page<Post> of every row, by primary key (see Pagination) |
Post::query() |
a SeaORM Select to filter, order and run |
When the model has created_at / updated_at columns, create sets both (unless the values set them) and update
sets updated_at, to the current UTC time.
For everything else, SeaORM's query API is available through smeltery::db::prelude, and its queries run on
db.conn():
use smeltery::db::prelude::*;
async fn latest(db: Db) -> smeltery::Result<Vec<Post>> {
let post = Post::create(&db, post::ActiveModel { title: Set("Hello".into()), ..Default::default() }).await?;
post.update(&db, |m| { m.title = Set("Hello again".into()); }).await?;
Ok(Post::query()
.filter(post::Column::Title.contains("Hello"))
.order_by_desc(post::Column::Id)
.limit(10)
.all(db.conn())
.await?)
}
SeaORM's ModelTrait is not in the prelude because its delete would take the place of Record::delete; import it
from sea_orm for find_related. Database errors convert into smeltery::Error with ? (a 500, details in the log).
Route model binding: Found<Post> loads the row whose primary key is the route's last path parameter, and
answers 404 when there is none (or the parameter is not a valid key):
use smeltery::prelude::*;
async fn show(Found(post): Found<Post>) -> String {
post.title
}
pub fn routes(r: &mut Router) {
r.get("/posts/{post}", show).name("posts.show");
}
Pagination#
PageQuery is a handler argument that reads ?page= (default 1) and ?per_page= (default 15) from the URL. It
never rejects a request: a missing or unreadable value means the default, page is kept within 1 to 10,000 and
per_page within 1 to 100 (page.max(250) raises the upper bound, page.default_per_page(30) changes the default).
Post::paginate(&db, page) returns one page of every row by primary key; smeltery::db::paginate(&db, select, page)
returns one page of any SeaORM select (give it an order so pages do not overlap). Each runs two statements: the page
and the count.
use smeltery::db::paginate;
use smeltery::db::prelude::{ColumnTrait, QueryFilter, QueryOrder};
use smeltery::prelude::*;
async fn index(db: Db, page: PageQuery) -> Result<Json<Page<Post>>> {
let select = Post::query()
.filter(post::Column::Published.eq(true))
.order_by_desc(post::Column::Id);
Ok(Json(paginate(&db, select, page).await?))
}
A Page<T> holds items, page, per_page, total and last_page (at least 1), and serializes with those names
({"items": [...], "page": 2, "per_page": 15, "total": 47, "last_page": 4}), so a Mold template or an Alloy page
reads it directly. page.map(f) changes every item; has_next() and has_previous() tell whether there are pages
around it. A page past the last one has no items. Page::new(items, page, per_page, total) builds one.
Model events#
A model listener is told about every successful create, update and delete made through Record. It sees what
happened (ModelChange::Created, Updated or Deleted), the table and the row: the saved row after a create or an
update, the row as the caller of delete held it. Register it with .model_listener(…) in bootstrap/app.rs:
use std::sync::atomic::{AtomicU64, Ordering};
use smeltery::db::{Db, ModelChange, ModelEvent, ModelListener};
use smeltery::{AppBuilder, BoxFuture};
#[derive(Default)]
struct CountDeletes(AtomicU64);
impl ModelListener for CountDeletes {
fn changed<'a>(&'a self, _db: &'a Db, event: ModelEvent<'a>) -> BoxFuture<'a, ()> {
Box::pin(async move {
if event.change == ModelChange::Deleted && event.table == "posts" {
self.0.fetch_add(1, Ordering::Relaxed);
}
})
}
}
pub fn build(app: AppBuilder) -> AppBuilder {
app.model_listener(CountDeletes::default())
}
event.model::<Post>() gives the row as a Post when the event concerns posts (None otherwise), and
event.is::<Post>() checks the model alone. A unit test calls a listener directly with
ModelEvent::new(ModelChange::Created, "posts", &post).
- Listeners run after the write has succeeded, outside any transaction, in registration order, and
create/update/deletewait for them before they return: a listener does quick work (or hands slow work to a queue orapp.spawn_owned). A failed write tells no listener, and adeleteof a row that is already gone deletes nothing and tells no listener. - The listeners of the app's database run on a task the app owns: when the caller is cancelled while they run (a request timeout, a closed connection), they still finish, and shutdown waits for them like for the app's other owned work.
- A listener returns nothing and logs its own errors; the write it is told about stays done. A listener that panics is logged and the next one still runs.
- An
updatethat changes nothing writes nothing and tells no listener. With anupdated_atcolumn everyupdatewrites. - Only writes through
Recordare seen: raw SQL (db.execute…) and SeaORM's own calls (Entity::insert,update_many,delete_many) are not. smeltery::db::without_listeners(async { … })runs its future with listeners switched off for writes made in that task (tasks it spawns are not covered), for bulk work such as a seeder.- Writes a listener makes through
Recordare reported to the listeners too: wrap them inwithout_listenerswhen they must not be (a listener that writes to the table it listens to would loop). - An app without listeners does no listener work on a write.