Repositories & Bulk Operations

Repositories in autumn-web provide a clean, type-safe, and highly optimized ORM-like data access layer. By annotating a trait with #[autumn_web::repository(Model, table = "table_name")], Autumn automatically generates high-performance implementations targeting PostgreSQL using diesel-async.

In version 0.5.0, Autumn introduces high-performance Bulk CRUD operations to minimize database round trips and execute massive writes transaction-safely and hook-compliantly.

Generated Bulk CRUD Methods

When you declare a repository, the generated Pg[Name]Repository automatically implements the following high-performance bulk operations:

fn save_many(
    &self, 
    new: &[NewModel]
) -> impl Future<Output = AutumnResult<Vec<Model>>> + Send;

fn save_many_skip_invalid(
    &self, 
    new: &[NewModel]
) -> impl Future<Output = AutumnResult<(Vec<Model>, Vec<(usize, AutumnError)>)>> + Send;

fn update_many(
    &self, 
    ids: &[i64], 
    changes: &UpdateModel
) -> impl Future<Output = AutumnResult<Vec<Model>>> + Send;

fn delete_many(
    &self, 
    ids: &[i64]
) -> impl Future<Output = AutumnResult<()>> + Send;

fn upsert_many(
    &self, 
    records: &[Model]
) -> impl Future<Output = AutumnResult<Vec<Model>>> + Send;

1. High-Performance Batch Insertion: save_many

save_many takes a slice of new records and inserts them in a single batch statement.

Non-Hooked (Zero-Cost Path)

If your model has no hooks configured, save_many translates to a single SQL query:

INSERT INTO table_name (col1, col2, ...) 
VALUES ($1, $2, ...), ($3, $4, ...), ... 
RETURNING *;

For large inputs, queries are automatically chunked under the Postgres parameter ceiling (65,535 parameters), preventing compilation or runtime DB overflow errors.

Hook-Aware Execution

If hooks are enabled on your repository, save_many guarantees full transaction integrity:

1. Runs before_create hooks sequentially on each record.
2. Batches the validated records and inserts them in a single database round trip inside a transaction.
3. Runs after_create hooks sequentially on successfully inserted records.
4. Stages after_create_commit hooks to fire only after the surrounding transaction successfully commits.

2. Validation & Partial Success: save_many_skip_invalid

When bulk importing dirty external data (e.g., from CSVs or public API hooks), some rows might violate business rules or database constraints. save_many_skip_invalid enables maximum throughput without losing valid rows.

• It runs before_create hooks on each row and filters out custom validation failures.
• It attempts a high-speed batch insert of all successful records in a transaction.
• Constraint Fallback: If the batch insert fails due to a database constraint (e.g., UniqueViolation), it automatically falls back to row-by-row insertion for that chunk, isolating individual DB constraint failures.
• Returns a tuple of (successful_models, list_of_errors_with_indices).

3. Bulk Updates: update_many

update_many modifies a batch of records identified by their IDs in a single SQL operation.

Non-Hooked

Updates all matching rows directly:

repo.update_many(&[1, 2, 3], &UpdatePost { title: Some("Bulk Updated Title".to_string()) }).await?;

Hook-Aware

If before_update hooks are configured:

1. Performs a SELECT ... FOR UPDATE on all specified IDs to load their current state.
2. For each row, constructs an UpdateDraft containing the original model and applies the changes.
3. Runs before_update hooks on each draft.
4. Updates all matching records in the database.
5. Runs after_update hooks.

4. Bulk Deletions: delete_many

delete_many deletes or soft-deletes a batch of records in a single statement.

Non-Hooked

Runs a single direct delete or soft-delete update statement.

Hook-Aware

1. Performs a SELECT ... FOR UPDATE on all specified IDs.
2. Runs before_delete hooks sequentially.
3. Executes the batch delete / soft-delete.
4. Runs after_delete hooks sequentially.

5. Bulk Upserts: upsert_many

upsert_many executes high-performance "insert-or-update" operations using a single SQL query matching on the primary key:

INSERT INTO table_name (id, col1, col2, ...) 
VALUES ($1, $2, ...), ($3, $4, ...) 
ON CONFLICT (id) DO UPDATE SET col1 = EXCLUDED.col1, ... 
RETURNING *;

[!IMPORTANT] Compile-Time Hook Safety: If hooks are enabled on your repository, calling upsert_many is explicitly rejected at compile-time. Because Postgres determines whether a row will insert vs update at runtime, it is impossible to correctly invoke before_create or before_update hooks before sending the query. To prevent silent hook bypass, this is caught during compilation.

6. Race-safe get-or-insert: find_or_create_by_<field> (unreleased)

The classic "find it, and if it isn't there create it" pattern is a TOCTOU race: between your find_by_slug returning empty and your insert landing, another request can insert the same key — and one of you gets a Postgres 23505 unique-violation (which also aborts the surrounding transaction).

Declare the lookup in the repository trait (just the lookup fields — the new value is added for you):

#[autumn_web::repository(Subreddit)]
pub trait SubredditRepository {
    /// Backed by `CREATE UNIQUE INDEX ... ON subreddits (slug)`.
    fn find_or_create_by_slug(slug: String);
}

This generates an inherent method on PgSubredditRepository:

pub async fn find_or_create_by_slug(
    &self,
    slug: String,
    new: &NewSubreddit,
) -> AutumnResult<(Subreddit, bool)>;

Call it and branch on the returned created flag if you care:

let (community, created) = repo
    .find_or_create_by_slug(slug.clone(), &new_sub)
    .await?;
if created {
    tracing::info!(%slug, "created new community");
}

Composite keys use _and_, matching a composite unique index:

// UNIQUE (user_id, list_id)
fn find_or_create_by_user_id_and_list_id(user_id: i64, list_id: i64);

The (Model, bool) return

The method returns (model, created). created == true only when this call actually inserted the row. When a matching row already exists — or when a concurrent caller won the insert race — you get the existing row with created == false.

How it stays race-safe

1. Preliminary lookup on the read path (replica-eligible, honoring tenant scoping and soft-delete). A hit returns (row, false) immediately and fires no hooks.
2. Otherwise insert on the primary with INSERT ... ON CONFLICT DO NOTHING. ON CONFLICT DO NOTHING is the crux: instead of raising 23505 (and poisoning the transaction), Postgres silently skips a conflicting insert.
   • If a row comes back, this call created it → (row, true), and create/commit hooks fire.
   • If nothing comes back, a concurrent caller won → the method re-reads the row on the primary (read-your-writes) and returns (row, false) with no hooks.

Under 10+ concurrent callers for the same key, exactly one row exists afterward, exactly one caller observes created == true, and no unique-violation ever surfaces to any caller.

Hooks and replica routing

• Lifecycle hooks (before_create / after_create and the durable after_create_commit commit-hook queue) fire only on the created path — never when the preliminary lookup finds an existing row.
• One caveat: before_create runs before the ON CONFLICT DO NOTHING insert, so when a concurrent caller wins the insert race the loser's before_create has already executed — and any DB writes it made inside the transaction still commit even though that caller's row ends up not created (it returns created == false). Only after_create and the after_create_commit commit hooks are guaranteed to run exclusively on the created path. Keep before_create side effects idempotent, or move create-only work into after_create.
• Unlike upsert_many, find_or_create_by_* is generated on repositories that configure hooks = .... Because the found-vs-created decision is made before any hook runs, there is no hook-bypass hazard.
• The lookup may run on a replica; the insert and the read-your-writes re-lookup always run on the primary, consistent with on_primary() write routing.

You must have a unique constraint (AC6)

Race-safety depends entirely on a unique constraint (or unique index) covering the lookup column(s). ON CONFLICT DO NOTHING only skips inserts that violate a constraint — with no matching constraint, two concurrent callers will each insert a row and you get duplicates. The method cannot detect a missing constraint at compile time, so this is on you:

• Single-field find_or_create_by_slug → UNIQUE (slug).
• Composite find_or_create_by_a_and_b → UNIQUE (a, b).
• On a tenant_scoped repository the unique index should include tenant_id (e.g. UNIQUE (tenant_id, slug)) so the constraint and the tenant-filtered re-lookup agree.
• On a soft_delete repository the unique constraint must be a partial index scoped WHERE deleted_at IS NULL (e.g. CREATE UNIQUE INDEX ... ON subreddits (slug) WHERE deleted_at IS NULL). With a plain (non-partial) unique index, a soft-deleted row keeps occupying the unique slot: the insert conflicts with it, while the deleted_at IS NULL lookup can't see it — so the re-lookup finds nothing and the method returns the internal error below. A partial index frees the slot the moment a row is soft-deleted, keeping the constraint and the filtered lookup in agreement.

If an insert conflicts but the follow-up re-lookup finds nothing — the tell-tale sign that the conflict fired on a different constraint than the one you're looking up by — the method returns a clear internal error rather than silently looping or lying. Only _and_ composites are supported; _or_ is rejected at compile time because it would span multiple constraints and defeat the single-constraint guarantee.

7. Grouped aggregate queries: count_/sum_/avg_/min_/max_..._grouped_by_<col> (unreleased)

Dashboard roll-ups — a post's vote tally, an experiment's audit-trail size, a per-day event time series — are GROUP BY aggregates. Hand-writing them as raw diesel::sql_query("SELECT … SUM(...) … GROUP BY …") strings bypasses the repository's replica routing, tenant scoping, and soft-delete filters, and has to be re-typed for every widening cast. Declare them on the #[repository] trait instead (issue #1364):

#[autumn_web::repository(Vote, table = "votes")]
pub trait VoteRepository {
    /// COUNT(*)  GROUP BY post_id → Vec<(post_id, count)>.
    fn count_grouped_by_post_id() -> Vec<(i64, i64)>;
    /// SUM(value) GROUP BY post_id → Vec<(post_id, Option<sum>)>.
    fn sum_value_grouped_by_post_id() -> Vec<(i64, Option<i64>)>;
    /// AVG(value) GROUP BY post_id → Vec<(post_id, Option<f64>)>.
    fn avg_value_grouped_by_post_id() -> Vec<(i64, Option<f64>)>;
}

Each declared method becomes an inherent method on the generated Pg* struct that returns a lazy GroupedAggregate<'_, K, V> builder — nothing runs until the terminal .load().await:

// Top-5 posts by score, highest first.
let top: Vec<(i64, Option<i64>)> = repo
    .sum_value_grouped_by_post_id()
    .order_by_aggregate_desc()
    .limit(5)
    .load()
    .await?;

// One post's tally: group by post_id, scope to it, take the single row.
let score = repo
    .sum_value_grouped_by_post_id()
    .filter_eq(post_id)
    .load()
    .await?
    .into_iter()
    .next()
    .and_then(|(_, sum)| sum)
    .unwrap_or(0);

// A day-bucketed time series over a bounded window.
use autumn_web::aggregate::DateBucket;
let per_day: Vec<(chrono::NaiveDateTime, i64)> = repo
    .count_grouped_by_created_at()
    .bucket(DateBucket::Day)
    .filter_range(window_start, window_end)
    .load()
    .await?;

Method-name shapes and the Vec<(K, V)> return

The trait method must declare its pair return type; the macro reads K and V from it and bakes the matching Postgres bind/result SQL types.

K is the group column's Rust type (or, under .bucket(..), the bucket-start timestamp's type). sum/min/max/avg are null-safe: a group whose values are all NULL yields None, and an empty result set is an empty Vec.

A nullable group-key type (K = Option<T>) is unsupported and rejected at compile time. A nullable group-key column is safe: rows whose group key is NULL are silently excluded from the results (the generated query guards the group column with IS NOT NULL), so the NULL group is omitted rather than deserialized into the non-nullable K. Nullable value columns are fine — an all-NULL group simply yields (key, None).

Grouped aggregates are not available on an #[encrypted(...)] column (as the group key or as an aggregated value): the stored value is ciphertext, so grouping would return ciphertext keys and .filter_eq(..) would compare plaintext against ciphertext and match nothing. Such a method returns an error at call time — use a raw query, or group on a non-encrypted column.

Builder chain

• .order_by_aggregate_desc() / .order_by_aggregate_asc() — order by the aggregated value; combine with .limit(n) for a top-N roll-up.
• .limit(n) — cap the number of groups returned.
• .filter_eq(v) / .filter_range(lo, hi) — scope rows before grouping; both filter the raw group column and are bound as query parameters (never interpolated). filter_range is inclusive and works for date/time windows. Note they filter the raw column even under .bucket(..), so to window a bucketed time series pass the raw-timestamp range to .filter_range(lo, hi) — .filter_eq(bucket_start) would match only rows on the exact bucket boundary.
• .bucket(DateBucket::{Day, Week, Month}) — group by date_trunc('<unit>', <col>), producing a time series keyed by bucket start. This method is only available when the group column is a timestamp type (NaiveDateTime or DateTime<Utc>); non-temporal group keys (e.g. an i64 post_id) have no .bucket() method, so an invalid date_trunc over a non-timestamp column is a compile error rather than a runtime failure. The truncation zone follows the key type: a NaiveDateTime (timestamp WITHOUT time zone) bucket truncates on the stored wall-clock value (a deterministic field truncation, independent of the session TimeZone), while a DateTime<Utc> (timestamptz) bucket is computed in UTC — the generated SQL uses date_trunc('<unit>', <col>, 'UTC') so bucket boundaries stay stable across deployments regardless of the DB session zone, consistent with the DateTime<Utc> key type.

Scoping comes for free

The generated query composes the repository's soft-delete filter and tenant predicate exactly like count, and acquires its connection through the same read-route helper — so replica routing and multi-tenancy work with no extra code. Because sum/avg/min/max cannot be merged across shards, a sharded, tenant-scoped repository used via across_tenants() rejects a grouped aggregate rather than returning a per-shard-partial answer; run it per shard with from_shard(..) instead.

Read Replicas: Automatic Read Routing

When database.replica_url is configured, every generated read-only method — find_by_id, find_all, count, exists_by_id, paginate, cursor_page, derived find_by_* / count_by_* / exists_by_* queries, and full-text search / search_page — automatically acquires its connection from the replica pool. Mutating methods (save, update, delete_by_id, the bulk operations, hook-driven writes, with_lock) always run on the primary. Provisioning a replica therefore offloads your primary with zero application code changes.

When no replica is configured, all methods use the primary — single-pool apps are unaffected.

The routing decision is snapshotted per request from AppState::read_pool(), so it honors the database.replica_fallback policy: when the replica is unready, reads either fall back to the primary (replica_fallback = "primary") or fail fast with 503 Service Unavailable (replica_fallback = "fail_readiness") rather than silently serving from the wrong role.

Opting Out: primary_reads

Replica reads can be stale by up to your replication lag. For aggregates where a stale read is worse than extra primary load (e.g. account balances, inventory counters), pin the whole repository to the primary:

#[autumn_web::repository(AccountBalance, primary_reads)]
pub trait AccountBalanceRepository {}

All generated reads on this repository use the primary pool, even when a replica is configured. Prefer the per-call escape hatch below when only some call sites are read-after-write-sensitive — a repository-wide opt-out gives up replica offloading everywhere.

Read-Your-Writes: on_primary()

After a handler performs a write, an immediate read may land on a replica that has not replayed it yet. The generated on_primary() method returns a clone of the repository whose reads are pinned to the primary, so you can read-your-writes without dropping to raw Diesel:

let created = repo.save(&new_post).await?;
// The replica may not have seen this row yet — read it from the primary.
let fresh = repo.on_primary().find_by_id(created.id).await?;

The original repo keeps routing reads to the replica; only the pinned clone (and call chains on it) use the primary.

Transactions

Reads executed inside an explicit transaction (db.tx(...) or repo.with_lock(...)) run on the transaction's own primary connection — a transaction never splits reads onto a replica.

Performance & Scaling Guidelines

Bulk operations are built for maximum performance, with the following built-in safeguards:

The Postgres Parameter Ceiling

Postgres supports a maximum of 65,535 parameters per statement. If you try to insert 10,000 rows with 8 columns, that requires 80,000 parameters, which ordinarily crashes. Autumn automatically calculates the optimal chunk size based on your model's columns and inserts in chunks (e.g. 1000 records at a time) to always remain well below the ceiling while maintaining peak batching throughput (>50x speedups over individual insertions).