rok-db
An ergonomic, type-safe async ORM for PostgreSQL, built on sqlx.
- One derive —
#[derive(Model)]gives you CRUD, aFromRowimpl and typed column constants. - Typed, model-scoped columns —
User::EMAIL.eq(..)can't be used to filterPosts, and typos are compile errors. - One executor story — every method accepts
&Db,&mut Tx,&PgPoolor&mut PgConnection. Sendfutures everywhere — works intokio::spawn, axum handlers, etc.- Relations without N+1 —
belongs_to,has_many,has_onewith batched eager loading. - Batteries included — streaming, aggregates and projections, automatic timestamps, memoized (cached) queries and structured query logging.
- Escape hatches —
Expr::raw,rok_db::raw(..),to_sql()on every builder, and full access to sqlx.
Install
[]
= { = "0.4", = ["chrono", "uuid", "json"] }
= { = "1", = ["macros", "rt-multi-thread"] }
| feature | enables |
|---|---|
chrono |
DateTime<Utc>, NaiveDateTime, NaiveDate, NaiveTime |
uuid |
Uuid columns |
json |
serde_json::Value and Json<T> columns |
migrate |
Db::migrate("./migrations") |
testing |
#[rok_db::test]: a temporary database per test |
serde |
Serialize for Page, CursorPage, Cursor, ValidationErrors |
axum |
rok_db::Error as an HTTP response (implies serde) |
metrics |
query, cache and pool metrics via the metrics crate |
full |
all of the above except testing |
Quick start
use *;
// table: "users"
async
Defining models
// default: snake_case plural of the struct name
Every column gets a constant named after its field: Post::SLUG, Post::AUTHOR, Post::CREATED_AT.
| Attribute | On | Meaning |
|---|---|---|
table = "name" |
struct | table name (default: snake_case plural) |
timestamps |
struct | manage created_at / updated_at automatically |
soft_delete |
struct | deleted_at marks rows deleted; queries skip them |
hooks |
struct | you implement rok_db::Hooks (lifecycle hooks) |
validate_with = path |
struct | record-level validation fn(&Self) -> Result<(), ValidationErrors> |
default_scope = path |
struct | fn() -> Expr<Self> applied to every query (remove with .unscoped()) |
has_many(posts = Post::USER_ID) |
struct | one-to-many relation → User::POSTS, user.posts() |
has_one(profile = Profile::USER_ID) |
struct | one-to-one relation → User::PROFILE, user.profile() |
primary_key |
field | primary key (default: id) |
generated |
field | filled in by the database; never written |
column = "name" |
field | column name differs from the field |
skip |
field | not a column |
created_at / updated_at / deleted_at |
field | managed column with a custom name |
version |
field | optimistic-locking counter |
tenant |
field | tenant column for row-level multi-tenancy |
validate(length(..), range(..), email, non_empty, custom = f) |
field | validation rules |
belongs_to = User |
field | user_id → Post::USER, post.user() (or belongs_to(author = User)) |
API overview
On the model (User::…) |
|
|---|---|
query(), filter(expr), order_by(col) |
start a SELECT |
find(db, id) / find_or_fail(db, id) / find_many(db, ids) |
by primary key |
all(db), count(db) |
whole table |
create().set(col, v)….exec(db) |
insert column by column |
insert_all(db, &records) |
multi-row insert, returns stored rows |
update_all().filter(..).set(..).exec(db) |
bulk update |
On a record (user.…) |
|
|---|---|
insert(db) |
insert (skipping generated columns), returns stored row |
save(db) |
update by primary key, returns stored row |
save_only(db, Some(vec![User::EMAIL])) |
update only some columns |
track() |
Tracked<M>: save only what changed |
Model::copy_in(db, &records) |
bulk load with binary COPY |
upsert(db) |
INSERT … ON CONFLICT (pk) DO UPDATE |
upsert_on(db, [User::EMAIL]) |
upsert on a unique column |
force_delete(db), restore(db), is_trashed() |
soft deletes |
delete(db) |
delete by primary key |
reload(db) |
re-read from the database |
On a query (Select) |
|
|---|---|
filter, filter_opt, filter_if |
WHERE (combined with AND) |
order_by, limit, offset, for_update, for_share |
|
all, first, one, count, exists |
run it |
cursor_paginate(db, after, limit) |
keyset pagination with an opaque cursor |
with_trashed(), only_trashed(), force_delete(db), restore(db) |
soft deletes |
paginate(db, page, per_page) |
Page<T> with total, total_pages(), has_next() — one round trip |
update().set(..)/increment(..)/set_raw(..) |
turn into a bulk UPDATE |
delete(db) |
bulk DELETE |
stream(db) |
rows one at a time |
sum, avg, min, max |
aggregates over the matching rows |
group_by, having, select(..) |
projections and grouped aggregates |
memoize(ttl) |
cache the result (see below) |
to_sql() |
inspect the generated SQL and parameters |
Column operators: eq ne gt gte lt lte like not_like ilike contains starts_with ends_with is_in not_in in_subquery not_in_subquery eq_outer between is_null is_not_null asc desc.
Combine expressions with .and(..), .or(..), !expr, Expr::all_of(..), Expr::any_of(..), Expr::exists(..), Expr::not_exists(..), or Expr::raw("lower(email) = ?", [v]).
Composite primary keys
find.await?; // tuple in key-field order
find_many.await?;
membership.save.await?; // WHERE org_id = $ AND user_id = $
Everything that identifies a row — save, delete, reload, upsert, optimistic locking, keyset
pagination tiebreakers, change feeds and the audit log — uses the whole key. Relations still need a
single-column key on the parent side.
Relations
// Lazy: a query you can refine
let recent = user.posts.order_by.limit.all.await?;
let author = post.user.one.await?;
// Eager: one extra query for any number of records (no N+1)
let users = all.await?;
let posts = POSTS.load.await?;
for user in &users
let authors = USER.load.await?; // `authors.get(&post)`
Streaming
use *; // brings `try_next`, `try_collect`, … into scope
let mut users = query.stream;
while let Some = users.try_next.await?
Aggregates and projections
let total: = query.sum.await?;
let avg_age: = query.avg.await?;
let oldest: = query.max.await?;
// GROUP BY / HAVING into tuples…
let per_author: = query
.group_by
.having
.select
.fetch_all
.await?;
// …or into your own structs
let stats: = query
.group_by
.select
.fetch_all
.await?;
Aggregates: count, count_distinct, sum, avg, min, max, plus .cast("BIGINT") and
Projection::raw(..).
Timestamps
The database clock is used (now()), so values are consistent across servers. Bulk updates
set updated_at too, unless you set it yourself.
Memoized queries
Cache read results in memory, with automatic invalidation:
let db = builder.query_cache.connect.await?;
let admins = filter
.memoize
.all // also: first, one, count, exists, paginate
.await?;
db.cache.unwrap.stats; // hits, misses, entries
- Entries expire after their TTL and are keyed by the exact SQL and parameters.
- Any write rok-db makes to a table (insert, save, upsert, delete, bulk update/delete — in transactions too, and again on commit) invalidates that table's cached results.
- Writes rok-db can't see (other services, raw SQL, cascades and triggers on other tables) need
raw(..).invalidates("users"),cache.invalidate("users"), or a short TTL. - Without a configured cache (or with a plain sqlx pool),
memoizesimply runs the query.
Running several app servers? Add .shared_cache_invalidation() to the builder: every instance
broadcasts its invalidations over PostgreSQL NOTIFY and drops stale entries from the others,
with no extra infrastructure. (If a listener reconnects, its local cache is cleared, since
messages may have been missed.)
Query logging
Every statement is logged through tracing:
| target | level | content |
|---|---|---|
rok_db::query |
DEBUG |
SQL, elapsed time, row count; failures with the error |
rok_db::query |
TRACE |
bound parameter values (may contain sensitive data) |
rok_db::slow_query |
WARN |
queries slower than the threshold (default 1s) |
rok_db::cache |
DEBUG |
memoized cache hits |
let db = builder.slow_query_threshold.connect.await?;
// e.g. RUST_LOG=rok_db=debug with tracing-subscriber's EnvFilter
Keyset pagination
Offset pagination (paginate) gets slower with every page and can skip or repeat rows when
data changes between requests. Keyset pagination doesn't:
let page = order_by
.cursor_paginate // first page
.await?;
let token: = page.next.map; // opaque, URL-safe
// next request
let cursor: Cursor = token.unwrap.parse?;
let page = order_by
.cursor_paginate
.await?;
The primary key is added as a tiebreaker automatically; ordering columns must be NOT NULL.
Soft deletes
doc.delete.await?; // UPDATE … SET deleted_at = now()
all.await?; // skips deleted rows (also count, find, relations, subqueries)
query.with_trashed.all.await?;
query.only_trashed.restore.await?;
doc.force_delete.await?; // real DELETE
Optimistic locking
let mut account = find_or_fail.await?;
account.balance += 10;
match account.save.await
save and delete check the version in the same statement (no extra round trip), and bulk
updates and upserts increment it too.
Upserts
user.upsert.await?; // ON CONFLICT (id) DO UPDATE …
user.upsert_on.await?; // ON CONFLICT (email) DO UPDATE …
insert_many
.on_conflict
.do_update // or .do_update_all() / .do_nothing()
.exec
.await?; // returns inserted + updated rows
let created: = create
.set
.on_constraint
.do_nothing
.exec_optional // None if it already existed
.await?;
Subqueries
// IN (subquery)
let authors = filter.all.await?;
// Correlated EXISTS / NOT EXISTS
let without_posts = filter.all.await?;
Joins
// Join through a relation; filter, order and select across both models.
let rows: = query
.join // belongs_to: INNER JOIN users ON users.id = posts.author_id
.filter
.order_by
.select
.fetch_all
.await?;
// Root models through has_many joins come back once each, in your order.
let authors: = query
.join
.filter
.order_by // each user ranked by their best post
.paginate
.await?
.items;
// LEFT JOIN, ad-hoc conditions, grouping, chains.
let per_user: = query
.left_join
.group_by
.select // 0 for users without posts
.fetch_all
.await?;
query.left_join;
query.join.join.filter;
// Whole models as tuples (RFC 0004); `Option` for LEFT JOINs.
let rows: = query
.join
.left_join
.
.await?;
// Keyset pagination, caching and bulk writes work through joins too.
let page = query.join.order_by.cursor_paginate.await?;
query.join.filter.delete.await?;
Using a column of a model that isn't part of the query is a compile error. Joined models'
tenant, soft-delete and default scopes apply in the ON clause. Memoized joined queries are
invalidated by writes to any of their tables. See RFC 0001 for
the design. Self-joins (the same table twice) are not supported yet.
Custom column types
// stored as TEXT: "admin", "member", "ro"
// a native `CREATE TYPE mood AS ENUM (…)`
; // stored like its inner type
filter.all.await?;
Any other type that implements sqlx's Type + Encode can be registered with rok_db::impl_value!(MyType).
Validation and hooks
match user.insert.await
Validation runs before every insert, insert_all/insert_many, upsert and save, collecting
every failure. Hooks: before_/after_ × insert, save, delete. Bulk query operations
(update(), delete() on a query) skip both, by design.
Scopes
all.await?; // only published
query.scope.all.await?; // named scope
query.unscoped.count.await?; // everything
Change tracking
let mut user = find_or_fail.await?.track;
user.name = "Ann".into; // Tracked<User> derefs to User
user.changes; // [("name", "ann", "Ann")]
user.save.await?; // UPDATE users SET name = $1 … — only changed columns
user.save.await?; // nothing changed: no query
Writing only changed columns avoids clobbering concurrent edits to other columns.
Bulk loading with COPY
let rows: u64 = copy_in.await?; // or (&mut tx, …)
Uses PostgreSQL's binary COPY, typically 5–20× faster than INSERT for large batches. Records
are validated first; generated columns are skipped; field types must match column types
exactly (i64 ↔ BIGINT). Nothing is returned (no after_insert hooks).
Web integration (axum)
With the axum feature, rok-db errors are HTTP responses, so handlers can just use ?:
async
async
| error | status |
|---|---|
| not found | 404 |
validation (with fields), hook rejection, foreign-key violation |
422 |
| optimistic-lock conflict, unique violation | 409 |
| invalid cursor | 400 |
| anything else | 500 (details logged, never returned) |
Metrics
With the metrics feature, install any metrics recorder (Prometheus,
StatsD, OpenTelemetry…) and rok-db reports rok_db_queries_total{kind,outcome},
rok_db_query_duration_seconds{kind}, rok_db_slow_queries_total, rok_db_rows_total,
rok_db_cache_requests_total{result} and, via db.record_pool_metrics(),
rok_db_pool_connections{state}. db.stats() gives a pool snapshot without the feature.
PostgreSQL arrays, JSONB and full-text search
filter; // $1 = ANY(tags)
filter; // tags && $1
filter; // id = ANY($1): one parameter for any number of ids
filter; // prefs ? $1
filter; // prefs @> $1 (feature `json`)
filter; // prefs ->> 'lang' = $1
filter;
filter // websearch syntax
.order_by // order by an expression
.paginate
.await?;
Vec<T> fields map to PostgreSQL arrays for String, bool, integers, floats and (with the
features) Uuid and dates.
Read replicas
let db = builder
.read_replica
.read_replica
.connect
.await?;
all.await?; // round-robin over replicas
query.on_primary.all.await?; // read your own writes
all.await?; // a handle that never uses replicas
Only the query builder's plain reads use replicas. Writes, for_update/for_share, transactions and
raw SQL (unless .on_replica()) go to the primary, and a read whose replica is unreachable is retried
on the primary.
Change notifications (LISTEN/NOTIFY)
let mut listener = db.listen.await?;
db.notify.await?;
let n = listener.recv.await?; // n.channel, n.payload
install_change_notifications.await?; // once: an AFTER trigger on `users`
let mut changes = changes.await?;
while let Ok = changes.recv.await
Notifications arrive on commit, at most once, and only to connected listeners: great for cache busting, websockets and waking workers, not a durable event log.
Multi-tenancy
with_tenant.await?;
all.await?; // outside a scope: matches nothing (fails closed)
query.all_tenants.all.await?; // explicit opt-out for admin jobs
Every query, count, bulk update/delete and record operation is restricted to the current tenant;
upserts can't take over another tenant's row and saves can't move a row to another tenant. The
scope is task-local (tokio::spawned tasks need their own with_tenant), and raw SQL is not
filtered.
Audit log
use audit;
install.await?; // table + trigger function, once
.await?; // audit `users`, minus secrets
with_actor.await?;
for entry in .await?
Trigger-based (feature json): it records every insert, update and delete of the table, including bulk
and raw SQL, with old/new rows as JSONB and the changed columns. No-op updates are skipped.
AuditEntry is a regular model you can query.
Code from .sql files (rok-db-gen)
Keep the schema and hand-written queries in .sql files and generate a crate from them.
Each db/<name>.sql becomes db-gen/src/<name>.rs, with models and enums, typed query
functions and generated migrations:
-- db/user.sql
(id BIGSERIAL PRIMARY KEY, email TEXT NOT NULL UNIQUE, name TEXT);
-- name: find_by_email :one
SELECT * FROM users WHERE email = $1;
up.await?; // apply migrations
let ann = find_by_email.await?; // Option<User>
let named = filter.all.await?;
Query types are checked by PostgreSQL, and the results are cached in db-gen/queries.json.
Migrations come with undo SQL, a guard against destructive steps and --rename. The
migration runner (rok_db::migration) can also be used without the generator. See
docs/v4.md and examples/sqlgen.
Retrying transactions
use ;
// Retry serialization failures and deadlocks with backoff.
let opts = new.isolation.retries;
db.transaction_with.await?;
Testing
With the testing feature, #[rok_db::test] gives each test a fresh database on the server
named by DATABASE_URL, dropped afterwards (even on panic). Tests are skipped when
DATABASE_URL isn't set.
async
Needs tokio (with macros, rt) as a dev-dependency and a database user allowed to create
databases.
Transactions
let user = db.transaction.await?; // commits on Ok, rolls back on Err
let mut tx = db.begin.await?; // or manage it yourself
filter.for_update.one.await?;
tx.commit.await?;
Raw SQL
let users: = raw
.bind
.bind
.fetch_all
.await?;
let total: i64 = raw.scalar.await?;
Errors
rok_db::Error has helpers for the common cases: is_not_found(), is_conflict(), validation_errors(), is_serialization_failure(), is_unique_violation(), is_foreign_key_violation() and constraint().
Crates
| crate | |
|---|---|
rok-db |
the crate to depend on: re-exports everything plus the derive |
rok-db-core |
runtime: Db, Model, query builders, Value |
rok-db-macros |
#[derive(Model)] |
rok-db-codegen |
the rok-db-gen binary: a crate from .sql files |
Contributing
Contributions are welcome! Start with CONTRIBUTING.md for setup, the change protocol and commit conventions. Larger changes go through the RFC process; see GOVERNANCE.md for how decisions are made. Please report security issues privately as described in SECURITY.md. Everyone is expected to follow the Code of Conduct. Notable changes are listed in the changelog.
Development
DATABASE_URL=postgres://postgres:postgres@localhost/rok_db_test \
Database tests use per-connection TEMP tables, so they never touch existing data.
Coding agents: start with AGENTS.md and llms.txt.
License
Licensed under either of
- Apache License, Version 2.0 (LICENSE-APACHE or https://www.apache.org/licenses/LICENSE-2.0)
- MIT license (LICENSE-MIT or https://opensource.org/licenses/MIT)
at your option.
Unless you explicitly state otherwise, any contribution intentionally submitted for inclusion in the work by you, as defined in the Apache-2.0 license, shall be dual licensed as above, without any additional terms or conditions.