modo-db

Database integration for the modo framework. Provides SeaORM-backed connection pooling, automatic schema synchronisation, versioned migrations, and a compile-time entity/migration registration system built on inventory.

Features

• sqlite (default) — enables SQLite via sqlx-sqlite. WAL mode, busy-timeout, and foreign keys are applied automatically.
• postgres — enables PostgreSQL via sqlx-postgres.

Usage

Configuration

DatabaseConfig is deserialized from your app's YAML config. The backend is auto-detected from the URL scheme.

use modo::AppConfig;
use modo_db::DatabaseConfig;
use serde::Deserialize;

#[derive(Default, Deserialize)]
struct Config {
    #[serde(flatten)]
    core: AppConfig,
    database: DatabaseConfig,
}

Example config.yaml:

database:
    url: "sqlite://data/main.db?mode=rwc"
    max_connections: 5
    min_connections: 1

Defaults: sqlite://data/main.db?mode=rwc, max_connections: 5, min_connections: 1.

Connecting and migrating

#[modo::main]
async fn main(
    app: modo::app::AppBuilder,
    config: Config,
) -> Result<(), Box<dyn std::error::Error>> {
    let db = modo_db::connect(&config.database).await?;
    modo_db::sync_and_migrate(&db).await?;
    app.config(config.core).managed_service(db).run().await
}

sync_and_migrate runs in two phases:

1. Schema sync — creates or adds columns for all registered entities (addition-only).
2. Migration runner — executes pending versioned migrations tracked in _modo_migrations.

Group-scoped sync

Use sync_and_migrate_group to sync only entities and migrations belonging to a named group. This is useful when entities in a group live in a separate database (e.g. a dedicated jobs database):

let jobs_db = modo_db::connect(&config.jobs_database).await?;
modo_db::sync_and_migrate_group(&jobs_db, "jobs").await?;  // syncs only "jobs" group
modo_db::sync_and_migrate(&db).await?;                     // syncs all entities to main DB

Defining entities

Apply #[modo_db::entity(table = "...")] to a plain struct. The macro preserves your struct as a first-class domain type and generates a SeaORM entity module alongside it.

The struct receives Clone, Debug, Serialize, Default, and From<Model> automatically. You never need to work with the SeaORM Model directly.

Optionally assign an entity to a named group with group = "<name>" (defaults to "default"). Entities in a group can be synced to a separate database via sync_and_migrate_group.

#[modo_db::entity(table = "todos")]
#[entity(timestamps)]              // adds created_at, updated_at
pub struct Todo {
    #[entity(primary_key, auto = "ulid")]
    pub id: String,
    pub title: String,
    #[entity(default_value = false)]
    pub completed: bool,
}

The macro creates:

• Your struct Todo with Clone, Debug, Serialize, Default, From<todo::Model>
• A submodule todo containing Model, ActiveModel, Entity, Column, and Relation
• An impl Record for Todo with CRUD operations and query builders

Because Default is generated, you can use struct-update syntax to set only the fields you care about:

let todo = Todo {
    title: "Buy milk".into(),
    ..Default::default()   // id auto-generated, completed = false, timestamps = now()
};

Field attributes

Attribute	Effect
primary_key	Marks the primary key column
auto_increment = false	Disables auto-increment (required for composite PKs)
auto = "ulid" / auto = "nanoid"	Auto-generates the PK before insert (primary key only)
unique	Adds a unique constraint
indexed	Adds a single-column index
column_type = "Text"	Overrides the SeaORM column type
default_value = <lit>	Sets a column default
default_expr = "<sql>"	Sets a SQL expression default
belongs_to = "OtherEntity"	Defines a FK relation; combine with on_delete/on_update
has_many / has_one	Declares an inverse relation (no DB column)
via = "JunctionEntity"	Many-to-many through a junction table
renamed_from = "old_name"	Records rename as a column comment
nullable	Accepted (no-op; Option<T> already implies nullable)

Struct attributes

Attribute	Effect
#[entity(timestamps)]	Appends created_at and updated_at (DateTime<Utc>)
#[entity(soft_delete)]	Appends deleted_at (Option<DateTime<Utc>>) and generates soft-delete methods on the struct
#[entity(index(columns = ["a", "b"]))]	Generates a composite index via CREATE INDEX IF NOT EXISTS
#[entity(index(columns = ["slug"], unique))]	Generates a composite unique index

CRUD operations

The Record trait is implemented for every entity struct. All CRUD methods are inherent methods on your struct, so you call them directly without importing extra traits.

Insert

use modo_db::{Db, Record};

#[modo::handler(POST, "/todos")]
async fn create_todo(Db(db): Db, input: JsonReq<CreateTodo>) -> JsonResult<TodoResponse> {
    let todo = Todo {
        title: input.title.clone(),
        ..Default::default()
    }
    .insert(&*db)
    .await?;
    Ok(Json(TodoResponse::from(todo)))
}

Find by ID

find_by_id returns the record or a 404 Not Found error automatically:

let todo = Todo::find_by_id(&id, &*db).await?;

Find all

let todos = Todo::find_all(&*db).await?;

Update

update mutates the struct in-place and refreshes all fields from the database:

let mut todo = Todo::find_by_id(&id, &*db).await?;
todo.completed = true;
todo.update(&*db).await?;

Delete

Todo::delete_by_id(&id, &*db).await?;
// or, if you already have the record:
todo.delete(&*db).await?;

Filtered queries

Use Todo::query() to build chainable queries. Results are automatically converted to the domain type.

// All incomplete todos, newest first
let todos: Vec<Todo> = Todo::query()
    .filter(todo::Column::Completed.eq(false))
    .order_by_desc(todo::Column::CreatedAt)
    .all(&*db)
    .await?;

// At most one result
let maybe: Option<Todo> = Todo::query()
    .filter(todo::Column::Title.contains("milk"))
    .one(&*db)
    .await?;

// Count matching rows
let n: u64 = Todo::query()
    .filter(todo::Column::Completed.eq(true))
    .count(&*db)
    .await?;

limit and offset are also available for manual slicing:

let page: Vec<Todo> = Todo::query()
    .order_by_asc(todo::Column::CreatedAt)
    .limit(20)
    .offset(40)
    .all(&*db)
    .await?;

Pagination

Offset-based

use modo::extractor::QueryReq;
use modo_db::{Db, PageParams, PageResult};

#[modo::handler(GET, "/todos")]
async fn list_todos(
    Db(db): Db,
    params: QueryReq<PageParams>,
) -> JsonResult<PageResult<TodoResponse>> {
    let result = Todo::query()
        .order_by_desc(todo::Column::CreatedAt)
        .paginate(&*db, &params)
        .await?;
    Ok(Json(result.map(TodoResponse::from)))
}

Cursor-based

use modo_db::{CursorParams, CursorResult, Db};

#[modo::handler(GET, "/todos/cursor")]
async fn list_cursor(
    Db(db): Db,
    params: QueryReq<CursorParams>,
) -> JsonResult<CursorResult<TodoResponse>> {
    let result = Todo::query()
        .paginate_cursor(
            todo::Column::Id,
            |m| m.id.clone(),
            &*db,
            &params,
        )
        .await?;
    Ok(Json(result.map(TodoResponse::from)))
}

per_page defaults to 20 and is clamped to [1, 100]. Paginate forward with ?after=<cursor> and backward with ?before=<cursor>.

Bulk operations

Bulk update

use sea_orm::sea_query::Expr;

let affected = Todo::update_many()
    .filter(todo::Column::Completed.eq(false))
    .col_expr(todo::Column::Completed, Expr::value(true))
    .exec(&*db)
    .await?;

Bulk delete

let deleted = Todo::delete_many()
    .filter(todo::Column::Completed.eq(true))
    .exec(&*db)
    .await?;

Both return the number of rows affected as u64.

Transactions

Pass the transaction handle the same way as &db:

let txn = db.begin().await.map_err(|e| modo::Error::internal(e.to_string()))?;

let todo = Todo {
    title: "Buy milk".into(),
    ..Default::default()
}.insert(&txn).await?;

txn.commit().await.map_err(|e| modo::Error::internal(e.to_string()))?;

Lifecycle hooks

Define inherent methods on your struct to hook into save and delete operations. No attributes or trait imports are required — Rust's inherent-method priority means your methods automatically take precedence over the no-op defaults provided by DefaultHooks.

#[modo_db::entity(table = "users")]
#[entity(timestamps)]
pub struct User {
    #[entity(primary_key, auto = "ulid")]
    pub id: String,
    pub email: String,
    pub password_hash: String,
}

impl User {
    pub fn before_save(&mut self) -> Result<(), modo::Error> {
        self.email = self.email.to_lowercase();
        Ok(())
    }

    pub fn after_save(&self) -> Result<(), modo::Error> {
        tracing::info!(id = %self.id, "user saved");
        Ok(())
    }

    pub fn before_delete(&self) -> Result<(), modo::Error> {
        if self.email.ends_with("@example.com") {
            return Err(modo::Error::bad_request("cannot delete example accounts"));
        }
        Ok(())
    }
}

The three hook signatures are:

Hook	Signature	When called
before_save	fn(&mut self) -> Result<(), modo::Error>	Before insert and update
after_save	fn(&self) -> Result<(), modo::Error>	After successful insert / update
before_delete	fn(&self) -> Result<(), modo::Error>	Before delete

Relations

Declare relations with field attributes and the macro generates async accessor methods on your struct.

#[modo_db::entity(table = "posts")]
#[entity(timestamps)]
pub struct Post {
    #[entity(primary_key, auto = "ulid")]
    pub id: String,
    #[entity(belongs_to = "User", on_delete = "Cascade")]
    pub user_id: String,
    pub title: String,
}

#[modo_db::entity(table = "users")]
#[entity(timestamps)]
pub struct User {
    #[entity(primary_key, auto = "ulid")]
    pub id: String,
    #[entity(unique)]
    pub email: String,
    // Relation field — excluded from DB columns
    #[entity(has_many)]
    pub posts: (),
}

Generated accessors:

// belongs_to: field `user_id` -> method `user()`
let author: Option<User> = post.user(&*db).await?;

// has_many: field `posts` -> method `posts()`
let posts: Vec<Post> = user.posts(&*db).await?;

has_one works the same way but returns Option<T> instead of Vec<T>.

Soft delete

Add #[entity(soft_delete)] to inject a deleted_at column and enable soft-delete semantics. Standard query() and find_all() automatically exclude soft-deleted records.

#[modo_db::entity(table = "items")]
#[entity(timestamps, soft_delete)]
pub struct Item {
    #[entity(primary_key, auto = "ulid")]
    pub id: String,
    pub name: String,
}

Generated methods:

// Soft-delete a single record (sets deleted_at = now, does not remove the row)
item.delete(&*db).await?;

// Soft-delete by ID
Item::delete_by_id(&id, &*db).await?;

// Restore a soft-deleted record (clears deleted_at)
item.restore(&*db).await?;

// Hard-delete a single record
item.force_delete(&*db).await?;

// Hard-delete by ID
Item::force_delete_by_id(&id, &*db).await?;

// Query including soft-deleted records
let all: Vec<Item> = Item::with_deleted().all(&*db).await?;

// Query only soft-deleted records
let trash: Vec<Item> = Item::only_deleted().all(&*db).await?;

// Bulk soft-delete (UPDATE SET deleted_at = now() WHERE ...)
let n = Item::delete_many()
    .filter(item::Column::Name.starts_with("temp_"))
    .exec(&*db)
    .await?;

// Bulk hard-delete (bypasses soft-delete)
let n = Item::force_delete_many()
    .filter(item::Column::Name.starts_with("temp_"))
    .exec(&*db)
    .await?;

Partial updates

When you need to update only specific fields without fetching the full record first, use into_active_model and the raw SeaORM API:

use sea_orm::{ActiveModelTrait, Set};
use modo_db::Record;

let mut am = todo.into_active_model();
am.completed = Set(true);
am.update(&*db).await.map_err(|e| modo::Error::internal(e.to_string()))?;

Escape hatch

EntityQuery wraps a SeaORM Select<E>. Unwrap it at any point with into_select() for advanced queries:

use sea_orm::QuerySelect;

let select = Todo::query()
    .filter(todo::Column::Completed.eq(false))
    .into_select();

// Use raw SeaORM from here
let models = select
    .columns([todo::Column::Id, todo::Column::Title])
    .all(&*db)
    .await?;

You can also use the SeaORM Entity directly at any time:

use modo_db::sea_orm::EntityTrait;
let models = todo::Entity::find().all(&*db).await?;
let todos: Vec<Todo> = models.into_iter().map(Todo::from).collect();

Versioned migrations

Use #[modo_db::migration] for changes that schema sync cannot express (e.g. data seeding, backfills, renaming columns).

The db parameter is a &sea_orm::DatabaseConnection, so you can use the full SeaORM typed API:

#[modo_db::migration(version = 1, description = "Seed default roles")]
async fn seed_default_roles(db: &sea_orm::DatabaseConnection) -> Result<(), modo::Error> {
    use sea_orm::{ActiveModelTrait, Set};

    for name in ["admin", "user"] {
        role::ActiveModel {
            name: Set(name.to_owned()),
            ..Default::default()
        }
        .insert(db)
        .await
        .map_err(|e| modo::Error::internal(format!("Migration failed: {e}")))?;
    }
    Ok(())
}

Raw SQL is also available for DDL operations that SeaORM cannot express:

#[modo_db::migration(version = 2, description = "Add full-text index")]
async fn add_fts_index(db: &sea_orm::DatabaseConnection) -> Result<(), modo::Error> {
    use sea_orm::ConnectionTrait;

    db.execute_unprepared("CREATE INDEX IF NOT EXISTS idx_todos_title ON todos(title)")
        .await
        .map_err(|e| modo::Error::internal(format!("Migration failed: {e}")))?;
    Ok(())
}

Migrations are executed in ascending version order. Each version is recorded in _modo_migrations and runs exactly once. Duplicate version numbers are detected at startup and cause an error.

Migrations can also be assigned to a group with group = "<name>" so they only run when sync_and_migrate_group is called with the matching group.

ID generation

let ulid_id = modo_db::generate_ulid();   // 26-char Crockford Base32
let nano_id = modo_db::generate_nanoid(); // 21-char NanoID

Key Types

Type	Purpose
DatabaseConfig	Connection URL + pool size, deserialised from YAML
DbPool	Newtype over sea_orm::DatabaseConnection; implements GracefulShutdown
Db	Axum extractor that pulls DbPool from app state
Record	Trait implemented by every entity struct; provides CRUD and query builder methods
DefaultHooks	Blanket trait providing no-op before_save, after_save, before_delete
EntityQuery<T, E>	Chainable query builder with automatic domain-type conversion
EntityUpdateMany<E>	Chainable bulk UPDATE builder
EntityDeleteMany<E>	Chainable bulk DELETE builder
EntityRegistration	Compile-time entity registry entry (produced by #[entity] macro)
MigrationRegistration	Compile-time migration registry entry (produced by #[migration] macro)
PageParams / PageResult<T>	Offset pagination request + response
CursorParams<V> / CursorResult<T>	Cursor pagination request + response