--- name: navree-database description: "Works with the database layer of a Navree app: the `database` configuration and its databases (TimescaleDB by default, PostgreSQL, MySQL/MariaDB, SQLite), the per-request unit of work (`Uow`), transactions in jobs and commands, SeaORM entities with UUID v7 keys and `Repo` writes with change events, seeders and `db:seed`, `navree::db::paginate`, soft deletes by hand, and TimescaleDB hypertables. Models and queries are the `navree-orm` skill, migrations the `navree-migrations` skill. Use when configuring the database, writing data outside a request, working with SeaORM entities or `Repo`, writing a seeder, or debugging \"database is locked\", 409 conflicts or writes that are not saved." --- # The database Apps work with their data through the ORM (`#[derive(Model)]` models: load the `navree-orm` skill) and write migrations with the schema builder (the `navree-migrations` skill). Both run on navree-db, which this skill covers. TimescaleDB (PostgreSQL with hypertables for time series) is the default database: `navree new` writes its URL and turns on the `postgres` feature of `navree`, and `navree dock up` runs it. Plain PostgreSQL uses the same feature; MySQL and MariaDB the `mysql` feature (`mysql://` or `mariadb://` URLs; MySQL 8's default accounts sign in over TLS); SQLite is compiled in (tests, `navree new --database sqlite`, and the fallback when `database.url` is unset). navree-db provides: a connection pool from the `database` section (SeaORM 2 on SQLx), a **unit of work** per request (one lazy transaction, committed when the response status is below 400), the migrator, seeders and pagination, and for SeaORM entities `Repo` writes that record **change events** (the audit log; models record the same events). ## Steps: a new table For a new table, write a model: `navree make:model posts Tag --migration` (the `navree-orm` skill). The steps below are for a SeaORM entity, which Navree's own modules and older app code use. 1. `navree make:entity posts tag` writes `src/modules/posts/entities/tag.rs` (id, `created_at`, `updated_at`) and prints the next command; `navree make:migration posts create_tags_table` writes the migration and adds it to the module's `MigrationSet`. (A new module from `navree make:module posts --crud` has both already.) 2. Add the columns to the entity **and** to the migration. 3. Write a service that takes `&Uow` and uses `Repo` for writes. 4. Run it: migrations apply at boot in development (`database.migrate_on_boot = true`), or run `navree migrate`. 5. Test with `TestApp::builder().temp_database()` (a fresh SQLite file per test, migrated at boot). ## Entity ```rust use sea_orm::entity::prelude::*; use serde::Serialize; #[derive(Clone, Debug, PartialEq, Eq, DeriveEntityModel, Serialize)] // Serialize: Repo needs it #[sea_orm(table_name = "posts")] pub struct Model { #[sea_orm(primary_key, auto_increment = false)] pub id: Uuid, // UUID v7, generated by the app: Uuid::now_v7() pub owner_id: Option, pub name: String, pub published: bool, pub created_at: DateTimeUtc, // set them yourself on insert and update pub updated_at: DateTimeUtc, } #[derive(Copy, Clone, Debug, EnumIter, DeriveRelation)] pub enum Relation {} impl ActiveModelBehavior for ActiveModel {} ``` Relations are plain SeaORM (`belongs_to`/`has_many` plus `Related` impls); navree-auth's users are `navree::auth::user::Entity`. Keep secrets out of a model that handlers serialize, or `#[serde(skip)]` them. ## Service: reads and writes in the unit of work ```rust use chrono::Utc; use navree::access::{Accessible, Read}; use navree::db::{Page, PageQuery, Repo, Uow, paginate}; use navree::injectable; use sea_orm::{ActiveValue::Set, DbErr, EntityTrait, IntoActiveModel, QueryOrder}; use uuid::Uuid; #[injectable] pub struct PostService; // a shared singleton: request state comes in as parameters impl PostService { pub async fn list(&self, uow: &Uow, scope: Accessible, page: PageQuery) -> Result, DbErr> { let select = scope.apply( Entity::find() .order_by_desc(Column::CreatedAt) .order_by_desc(Column::Id), // always end with a unique column ); paginate(select, &*uow.tx().await?, page).await } pub async fn create(&self, uow: &Uow, input: CreatePost) -> Result { let now = Utc::now(); Repo::::insert(uow, ActiveModel { id: Set(Uuid::now_v7()), owner_id: Set(uow.actor()), // the signed-in user, if any name: Set(input.name), published: Set(false), created_at: Set(now), updated_at: Set(now), }) .await } pub async fn update(&self, uow: &Uow, post: Model, input: UpdatePost) -> Result { let mut active = post.into_active_model(); // only `Set` fields are written if let Some(name) = input.name { active.name = Set(name); } active.updated_at = Set(Utc::now()); Repo::::update(uow, active).await // records only the changed fields } pub async fn delete(&self, uow: &Uow, post: Model) -> Result<(), DbErr> { Repo::::delete(uow, post).await } } ``` The controller passes the request's unit of work: `async fn store(&self, uow: Uow, ...)` then `self.posts.create(&uow, input).await?`. | `Repo::::` | Returns | |---|---| | `insert(&uow, active)` | the inserted model; records `created` | | `update(&uow, active)` | the updated model; records `updated` with changed fields (re-reads the row first) | | `delete(&uow, model)` | `()`; records `deleted` | | `find(&uow, id)` / `find_or_fail(&uow, id)` | `Option` / the model or `RecordNotFound` (404 over HTTP) | | `find_for_update(&uow, id)` | `Option`, row locked on PostgreSQL and MySQL | Use plain SeaORM through `&*uow.tx().await?` for reads, joins, counts, `update_many`/`delete_many` and upserts (they record no change events; call `uow.record_change(ChangeEvent { .. })` when one should be audited). There is no `save()`: never call SeaORM's `ActiveModelTrait::save` on an entity with an app-generated id. ## Which connection | Code | Connection | |---|---| | handler, or service taking `&Uow` | `&*uow.tx().await?` (keep the `Arc` short-lived) | | service without `&Uow` | `&db.reader().await` (`Arc` injected): the current unit of work's transaction if any, else the pool | | command, seeder, test assertions | `db.conn()`, or a unit of work from `db.transaction(..)` / `db.uow(..)` | Never write through `db.conn()` during a request: it escapes the rollback and, on SQLite, waits on the request's own write lock ("database is locked" after 5 s). ## Outside a request ```rust // A command or job: a new unit of work that commits on Ok, rolls back on Err. let db = ctx.get::().await?; // in a job: ctx.app.get::() db.transaction(|uow| async move { let tx = uow.tx().await?; // ... SeaORM through &*tx, or Repo::::insert(&uow, ..) Ok(()) }) .await?; // Work done on behalf of a user (the audit log records them as the actor): let uow = db.uow(Some(user_id), None).await; let result = uow.scope(async { Repo::::find_or_fail(&uow, id).await }).await; match result { Ok(_) => uow.commit().await?, Err(error) => { uow.rollback().await?; return Err(error.into()); } } ``` `uow.after_commit(move || Box::pin(async move { .. }))` runs work only after a successful commit (broadcasts, calls to other systems). Queued jobs, queued mail and notifications already wait for the commit. ## Migrations Load the `navree-migrations` skill to write or run migrations. In short: a migration implements `navree::schema::Migration` (`async fn up` and `down` taking `&Schema`), written with the schema builder; each module registers one `MigrationSet` (`m.migrations(migrations::migrations())`), ordered with `.after([..])`. `references/migrations.md` has a summary and `references/timescaledb.md` time-series tables (hypertables). Never edit a migration that has run anywhere: add a new one. ```sh navree migrate # pending migrations, as one batch (--step, --pretend, --seed) navree migrate:rollback # the last batch; --step N, --batch N (APP_DATABASE__MIGRATE_ON_BOOT=false first) navree migrate:fresh --seed # drops EVERY table; development only ``` ## Seeders, factories and pagination See `references/seeding-and-pagination.md` for a `Seeder` (`db:seed`), a SeaORM entity `Factory` (`create`, `create_many`) and cursor pagination. In short: `navree make:seeder posts demo_posts` writes and registers a seeder (`m.seeder::()`); a root seeder (`is_root() -> true`) runs the others with `ctx.call::()`; seeders fill tables with model factories (`Post::factory().count(10).create(db)`, the `navree-orm` skill), or, for entities, `navree::db::Factory` (named `ModelFactory` in the prelude), which builds active models; `paginate(select, conn, PageQuery)` returns `Page` (`{data, meta}`), `cursor_paginate_desc(select, Column::Id, conn, CursorQuery)` returns `CursorPage` (`{data, next_cursor}`). Models paginate with `query.paginate(&uow, page)`. ## Configuration ```toml [database] url = "postgres://navree:navree@127.0.0.1:5432/my_app" # TimescaleDB (`postgres`); mysql://... (`mysql`); sqlite://storage/app.db?mode=rwc max_connections = 10 migrate_on_boot = false # a new app sets true for development slow_query_ms = 200 test_before_acquire = false # true: ping every checkout (one round trip each) idle_timeout_secs = 600 # 0: keep idle connections max_lifetime_secs = 1800 # 0: no limit transaction_settings = true # false: no navree.actor / @navree_actor per transaction [database.mysql] # MySQL and MariaDB only strict = true # strict SQL mode plus ONLY_FULL_GROUP_BY (default) # isolation_level = "read_committed" # the server's (REPEATABLE READ) when unset # modes = ["STRICT_ALL_TABLES"] # the exact SQL mode instead of `strict` ``` On MySQL the unit of work sets `@navree_actor` and `@navree_request_id` (cleared before commit or rollback) where PostgreSQL uses `set_config` (neither when `transaction_settings = false`), the UUIDs models and entities write are 16 bytes, for `BINARY(16)` columns (the schema builder's `uuid` columns are `CHAR(36)` there: see `navree-migrations`), and workspace isolation is query scoping only (no row-level security): every workspace query must go through `WorkspaceRepo` or a `#[model(workspace)]` model. Override in production with `APP_DATABASE__URL` (an environment variable, not `.env`, since `config/app.toml` sets it). `Arc` is injectable anywhere (`DatabaseModule` is global). ## Time series Navree's own time-series tables are hypertables on TimescaleDB, converted by their modules' migrations: `audit_logs`, `webhook_deliveries`, `webhook_attempts`, `fleet_jobs`, `fleet_metrics`, `schedule_runs`, `lighthouse_samples`, `lighthouse_occurrences`, `lighthouse_aggregates`. Their primary keys include the time column there, so nothing may reference them by `id` alone. Prune old rows with `navree::db::prune_time_series(conn, table, time_column, cutoff)`: whole chunks go on a hypertable, one `DELETE` elsewhere. See `references/timescaledb.md`. ## Common mistakes - Forgetting `m.migrations(..)` in the module's `wire`, or the set's `.after([..])` for tables it references. - Not ordering a paginated query, or ordering by a non-unique column only. - Holding `uow.tx()`'s `Arc` in a struct or spawned task: the commit fails with "its transaction is still in use". - In-memory SQLite (`sqlite::memory:`): each pool connection sees a different database. Tests use temp files. - On SeaORM entities, soft deletes are manual: a `deleted_at` column, filter every list, return 404 for deleted rows, and check it in `#[can(.., from = "id")]` handlers. Models do all of it with `#[model(soft_deletes)]`. - Expecting navree-validation's `exists` or entity record checks to skip soft-deleted rows (the ORM's `exists` rule does). - Running app commands (`migrate`, `key:generate`) before `navree dock up`: the app can't connect to TimescaleDB. - A foreign key to a hypertable's `id`: its key is `(id, time)`, so the conversion stops. Reference a regular table (the endpoint, the device) and delete the dependent rows when pruning. ## Docs - https://navree.com/docs/database/getting-started/ - https://navree.com/docs/database/entities-and-repositories/ - https://navree.com/docs/database/unit-of-work/ - https://navree.com/docs/database/migrations/ - https://navree.com/docs/database/running-migrations/ - https://navree.com/docs/database/timescaledb/ - https://navree.com/docs/database/seeding-and-factories/ - https://navree.com/docs/database/pagination/ - https://navree.com/docs/orm/getting-started/