Mool

Mool, pronounced mool, means root or source.

Mool is a typed SQL data mapper for Rust and SQLx. It turns Rust models and schema SQL into deterministic, reviewable migrations while keeping application queries explicit, typed, and dialect-correct.

Mool brings three application-facing guarantees to SQLx projects:

The result is a practical middle ground: direct SQL control, SQLx compatibility, and a migration workflow that stays close to the application's Rust types.

Plain SQL remains excellent for one-off statements and carefully tuned queries. Mool is for the rest: application queries that repeatedly need reliable column names, bind ordering, row mapping, filters, relations, migrations, and testable SQL plans.

It is ORM-like where that saves work, but it is not an active-record ORM. Models do not own a connection or save themselves. You write the operation, Mool provides the typed metadata, builder, and SQLx execution path.

Select at most one database backend for query execution:

mool = { version = "0.2", features = ["postgres"] }
# or: "sqlite", "mysql", "mariadb"

Mool also compiles without a backend for memory-only framework modes. Migration registration and schema metadata remain available, along with compatibility DbConf, DbError, DbPool, and backend::{Pool, Database, ...} types. The backendless DbPool is inert: an empty default configuration is accepted, while a configured database URL returns a structured capability error. Query builders, sessions, SQLx access, and selected-dialect schema helpers require one backend.

Common optional features:

mool = { version = "0.2", features = ["postgres", "migrations"] }
mool = { version = "0.2", features = ["sqlite", "mock"] }
mool = { version = "0.2", features = ["postgres", "time"] }
mool = { version = "0.2", features = ["postgres", "test-support"] }

For SQLx-managed live test databases, declare SQLx directly in the consuming application's dev dependencies:

[dev-dependencies]
mool = { version = "0.2", features = ["postgres"] }
sqlx = { version = "0.8", default-features = false, features = ["runtime-tokio", "postgres", "macros", "migrate"] }

Use #[sqlx::test] directly. SQLx's attribute expansion resolves the consuming crate, so Mool does not forward SQLx test macros.

For a plain Mool-owned isolated database, enable test-support instead. It creates a per-test target from DbConf without adding an attribute macro or a direct SQLx dependency:

let mut test_db = db::testing::setup(db::DbConf::from_env()?)
    .with_migrations(&registry) // PostgreSQL and SQLite only
    .create()
    .await?;

db::from(&Post::table()).all::<Post>().exec(test_db.pool_mut()).await?;
test_db.teardown().await?;

An empty registered migration history skips the migration runner entirely.

TestDatabase attempts best-effort cleanup on drop, but teardown().await is the deterministic cleanup path and reports permission or server failures. Server test credentials must be able to create and drop databases.

Automatic migrations are supported for PostgreSQL and SQLite. MySQL and MariaDB are query backends only; Mool does not execute migrations for either backend. Lateral joins are not currently supported. Do not use --all-features: backend features are exclusive.

mool::prelude::* is the normal application import. It includes the common query and model API plus only the extensions supported by the chosen backend.

Set the connection URL, then create a Mool pool inside your async application:

export DATABASE_URL='postgres://app:password@localhost:5432/blog'

Define a table-backed model and run a typed query. DbPool owns the selected SQLx pool; every Mool executable accepts it as a DbSession.

use mool as db;
use mool::prelude::*;

#[derive(Debug, Clone, db::Model)]
#[table(name = "posts")]
struct Post {
    #[column(primary_key)]
    id: i64,
    author_id: i64,
    title: String,
    published: bool,
    published_at: chrono::DateTime<chrono::Utc>,
}

async fn published_posts() -> Result<Vec<Post>, db::DbError> {
    let conf = db::DbConf::from_env()?;
    let mut pool = db::DbPool::from_conf(&conf).await?;
    let posts = Post::table();

    db::from(&posts)
        .filter(posts.published.eq(db::val(true)))
        .sort(posts.id.desc())
        .all::<Post>()
        .exec(&mut pool)
        .await
}

Use DbPool::from_pool(...) when the application already owns a SQLx pool. DbPool::as_sqlx() and DbTransaction::as_sqlx() retain direct access to SQLx for backend facilities outside Mool's typed query API.

Use Model for a table-backed row. Use Record for projections, patches, joined output, and write payloads.

#[derive(Debug, Clone, db::Record)]
#[table(name = "posts")]
struct PostPatch {
    title: String,
    published: bool,
}

let posts = Post::table();
let id = db::var::<i64>().named("id");

db::from(&posts)
    .filter(posts.id.eq(&id))
    .bind(&id, 42_i64)
    .update(&PostPatch {
        title: "A clearer title".to_string(),
        published: true,
    })
    .exec(&mut pool)
    .await?;

The same builder handles insert, batch_insert, update, delete, upsert, returning, count, exists, scalar, and paginated reads. Plans can be inspected without a database:

let plan = db::from(&Post::table()).all::<Post>().plan()?;
println!("{}", plan.sql);

Model schema inference uses Rust traits, so imported names and type aliases work without macro-specific path spelling:

Native arrays require PostgreSQL-compatible SQLx array metadata. Use sqlx::types::Json<Vec<T>> for portable list storage. An explicit #[column(type = "...")] always wins, including when an application prefers json over jsonb, textual UUIDs, a domain, or custom temporal precision.

Batch writes accept ordinary Record or Model slices. Mool derives the largest statement size allowed by the selected backend; batch_size() can lower that limit, while single_statement() rejects input that cannot fit.

db::from(&posts)
    .batch_insert(&new_posts)
    .batch_size(1_000)
    .exec(&mut pool)
    .await?;

db::from(&posts)
    .batch_upsert(&new_posts, (&posts.author_id, &posts.title))
    .update_only(&posts.published)
    .exec(&mut pool)
    .await?;

db::from(&posts)
    .batch_update(&changed_posts, (&posts.title, &posts.published))
    .exec(&mut pool)
    .await?;

PostgreSQL and SQLite provide exact ignore_conflicts() and ignore_conflicts_on(...) extensions. MySQL and MariaDB provide the broader ignore_errors(), which renders INSERT IGNORE. Returning composes with batch writes on backends that support it; ignored rows are not returned and return order is unspecified. Affected-row counts follow backend semantics and may not equal the input length, especially for MySQL-family upserts.

Use .plans() to inspect every generated statement and its input row range. Each planned batch is one session call. Multiple batches are not implicitly transactional, so wrap execution in an explicit transaction when all rows must commit or roll back together.

PostgreSQL can transpose derived records into typed arrays and bind one parameter per writable column:

use mool::backend::PostgresUnnestExt;

let inserted = db::from(&posts)
    .returning::<Post>()
    .batch_insert(&new_posts)
    .using_unnest()
    .exec(&mut pool)
    .await?;

using_unnest() is explicit, works with inserts and upserts, and supports normal models, purpose-built records, nullable values, UUIDs, temporal values, JSON, and SqlEnum fields when their PostgreSQL array representation exists.

Filterable turns request-shaped structs into typed predicates. Empty optional values are omitted, so one filter type can serve many search forms.

#[derive(Debug, Clone, db::Filterable)]
#[filter(model = Post)]
struct PostFilter {
    #[filter(op = "eq")]
    published: Option<bool>,
    #[filter(op = "in", column = "id")]
    ids: Vec<i64>,
}

let rows = db::from(&Post::table())
    .filter_with(&filter)
    .all::<Post>()
    .exec(&mut pool)
    .await?;

Applications own their request vocabulary while Mool keeps the generated SQL typed. Sortable and Pageable follow the same builder-transform shape as Filterable:

enum PostOrdering {
    Newest,
    Title,
    Random,
}

impl db::Sortable for PostOrdering {
    type Model = Post;

    fn apply_sort(
        &self,
        sort: db::SortBuilder<Post>,
    ) -> db::SortBuilder<Post> {
        let order = match self {
            Self::Newest => sort.published_at.desc(),
            Self::Title => sort.title.asc(),
            Self::Random => db::random_order(),
        };
        sort.sort(order)
    }
}

struct BrowserPagination {
    page: Option<usize>,
    size: Option<usize>,
}

impl db::Pageable for BrowserPagination {
    fn apply_page(
        &self,
        page: db::PaginationBuilder,
    ) -> db::PaginationBuilder {
        page
            .page_num(self.page.unwrap_or(1))
            .page_size(self.size.unwrap_or(25).min(100))
    }
}

let query = db::from(&Post::table()).filter_with(&filter);
let query = match ordering.as_ref() {
    Some(ordering) => query.sort_with(ordering),
    None => query,
};
let page = query.page_with(&pagination, &mut pool).await?;

Use .sort(column.asc()) for direct ordering and .page(Pagination { page_num, page_size }, session) when request parsing is not needed. random_order() uses each backend's trusted random function and can be costly for large result sets.

Models can declare references, and records can flatten joined rows. Mool also supports back-reference and many-to-many predicates, relation aggregates, and two-query prefetch when that is more efficient than a join.

Derived sources remain typed. Build a query, turn it into a subquery_as(...) or cte_as(...), and use its output handles in the parent query. The expression API covers comparisons, boolean logic, IN, null checks, CASE, casts, common functions, aggregates, windows, and backend-specific capabilities.

Composition itself is infallible. Invalid names, source ownership, unsupported combinations, and missing bindings surface from .plan(), .plans(), or .exec(), so queries can be assembled and passed between application layers without intermediate error plumbing.

Unsupported dialect features are absent at compile time rather than accepted and rejected later. For example, PostgreSQL-only helpers such as ILIKE, arrays, DISTINCT ON, and RETURNING are exposed only in PostgreSQL builds.

Datetime helpers are typed expressions, so they compose with filters, projections, grouping, ordering, CTEs, and updates. Portable operations use UTC and accept Chrono, optional time types, and standard or Tokio durations:

let recent = db::from(&posts)
    .filter(posts.published_at.lte(
        db::funcs::datetime::now::<chrono::DateTime<chrono::Utc>>(),
    ))
    .filter(
        db::funcs::datetime::extract_year(posts.published_at.clone())
            .eq(db::val(2026)),
    )
    .sort(db::funcs::datetime::trunc_day(posts.published_at.clone()).desc());

let expires_at = db::funcs::datetime::add(
    posts.published_at.clone(),
    tokio::time::Duration::from_secs(300),
);

Fixed-duration arithmetic requires exactly representable millisecond values; lossy or overflowing durations fail during planning. now() means database statement time on every backend. Calendar months, time-zone conversion, native intervals, and formatting live in the selected backend module, such as funcs::postgres::datetime or funcs::sqlite::datetime. Mool-created PostgreSQL and MySQL-family pools use UTC sessions; externally managed pools must be configured equivalently.

Transactions use the same explicit lifecycle as SQLx:

let mut transaction = pool.begin().await?;

db::from(&Post::table())
    .insert(&PostPatch {
        title: "Created in a transaction".to_string(),
        published: false,
    })
    .exec(&mut transaction)
    .await?;

transaction.commit().await?;

DbTransaction::as_sqlx() gives access to the underlying SQLx transaction when an operation falls outside Mool's builder. Raw SQL is always available:

let count = db::query("SELECT COUNT(*) FROM posts WHERE author_id = :author_id")
    .bind("author_id", 42_i64)
    .scalar::<i64>(&mut pool)
    .await?;

Named raw binds are checked for missing, unused, and duplicate names before a statement reaches SQLx.

SqlEnum maps fieldless Rust enums to database values and works directly with SQLx binds, model fields, filters, and expressions.

#[derive(Debug, Clone, Copy, PartialEq, Eq, db::SqlEnum)]
#[sql_enum(rename_all = "snake_case")]
enum PostStatus {
    Draft,
    InReview,
    Published,
}

#[derive(Debug, Clone, db::Model)]
#[table(name = "posts")]
struct PostWithStatus {
    #[column(primary_key)]
    id: i64,
    #[column(sql_enum)]
    status: PostStatus,
}

Text labels are portable and the default. Explicit integer codes are available for compact, stable storage. PostgreSQL native enums are schema-aware; MySQL native ENUM columns are represented in schema metadata.

Models provide schema metadata, including keys, references, checks, indexes, and enum-backed columns. Build the desired schema directly from Rust models:

let schema = db::schema()
    .model::<PostWithStatus>()
    .build()?;

Small deterministic configuration or reference sets can be migration-managed from purpose-built records. Add ManagedRecord to opt into Serde-backed migration values without imposing serialization requirements on ordinary query records. The complete declared set is owned: changing a row generates an update and removing one generates a reviewed delete migration.

#[derive(db::Model)]
#[table(name = "app_settings")]
struct AppSetting {
    #[column(primary_key)]
    key: String,
    value: serde_json::Value,
}

#[derive(db::Record, db::ManagedRecord)]
struct AppSettingSeed {
    key: String,
    value: serde_json::Value,
}

let schema = db::schema()
    .model::<AppSetting>()
    .managed_rows::<AppSetting>([AppSettingSeed {
        key: "mail.reply_to".into(),
        value: serde_json::json!("support@example.com"),
    }])
    .build()?;

The same builder can extend modeled tables with verified unmanaged clauses, and register opaque CREATE declarations while keeping validation at the terminal build() call.

let schema = db::schema()
    .model::<PostWithStatus>()
    .extend_table("posts", |table| table.unmanaged_prefix("UNLOGGED"))
    .opaque("CREATE INDEX posts_live_idx ON posts (id) WHERE published")
    .build()?;

Dialect-specific parent-table syntax uses the same extension point. For example, PostgreSQL PARTITION BY RANGE (purge_at) belongs in an unmanaged table suffix:

let schema = db::schema()
    .model::<CrawlerUrl>()
    .extend_table("crawler_url", |table| {
        table.unmanaged_suffix("PARTITION BY RANGE (purge_at)")
    })
    .build()?;

Rolling child partitions are operational objects and are not modeled or generated by Mool migrations. Converting an existing plain table requires an explicit reviewed raw migration.

That Rust-derived schema can be combined with authored schema SQL. The migration workflow compares desired schema state with the committed migration history, then generates a deterministic migration for review. Hand-authored SQL remains available for changes that need it.

With the migrations feature, Mool embeds committed migration YAML files and registers migration history with the application:

static MIGRATIONS: db::migrations::EmbeddedMigrations =
    db::migrations::embed_migrations!("migrations");

Register the resulting desired schema and embedded history with MigrationRegistry at the application boundary. The generated migration is a normal reviewed file, not an opaque runtime schema change.

Frameworks can own command handling without importing Gaman. Build one runner from the registry, serialize commands through an async mutex, and construct the public Mool command protocol directly:

use std::sync::Arc;
use tokio::sync::Mutex;
use mool as db;
use db::migrations::engine::{
    ApplyCommand, MigrationCommand, NativeRunnerFactory,
};

let runner = NativeRunnerFactory::from_store(config, Arc::new(registry)).build();
let runner = Arc::new(Mutex::new(runner));

let result = runner
    .lock()
    .await
    .run_command(&MigrationCommand::Apply(ApplyCommand::Plan))
    .await?;

For migration generation, construct MigrationCommand::Make(...) with registry.schema_for(None)?. A non-interactive host should return structured clarifications from MigrationCommandError::NeedsInput and resubmit the command with decisions rather than prompting internally.

Framework command handlers can use db::migrations::engine::EntityFilter::parse("[kind:]glob") to pass repeated CLI migration filters to Gaman's MakeCommand::Generate protocol. Mool does not add a schema or query filtering API for migration selection.

Mool supports database-free testing through planned SQL and MockDbSession. The mock records ordered statements and can return planned query responses, so unit tests can assert application behavior without booting a database.

let posts = Post::table();
let plan = db::from(&posts)
    .filter(posts.published.eq(db::val(true)))
    .all::<Post>()
    .plan()?;

assert!(plan.sql.contains("WHERE"));

The project maintains offline SQL golden tests across supported dialects, macro compile-contract tests, SQLx compatibility checks, and database-free examples. The support and compatibility matrix is in SUPPORT.md. Run the local confidence suite with:

scripts/confidence-check.sh

For live backend validation, use scripts/integration-tests.sh <backend> all. The live suite also verifies db::testing::setup(...) across every query backend and applies registry migrations on PostgreSQL and SQLite.

Mool keeps SQL explicit while removing the repetitive parts of building and maintaining typed database code.