CRUD Guide

This guide covers the model repository methods currently provided by afastorm, how to find rows, compose filters, and handle updates and deletes. It uses SQLite in the connection example; the repository API is the same for PostgreSQL.

Define a model and connect

use afastorm::{Model, ModelRegistry, Orm};
use serde_json::Value;

#[derive(Debug, Model)]
#[orm(table = "users")]
struct User {
    #[orm(primary_key, auto_increment)]
    id: Option<i64>,
    #[orm(unique)]
    email: String,
    display_name: String,
    active: bool,
    profile: Value,
    #[orm(auto_create)]
    created_at: String,
    #[orm(soft_delete)]
    deleted_at: Option<i64>,
    #[orm(version)]
    version: i64,
}

let orm = Orm::connect("sqlite://app.db?mode=rwc")
    .await?
    .with_models(ModelRegistry::new().register::<User>());
let users = orm.repo::<User>()?;

Models must be registered before calling repo. Registration does not create tables; prefer reviewed migrations for application schema. Repository::create_table() is available for tests and simple development setups.

auto_create, soft_delete, and version behavior is described in Models. Field storage and JSON behavior are backend-specific; see SQLite Types and PostgreSQL Types.

Method overview

Methods returning u64 report affected rows, except insert with a generated key returns 1 after a successful insert. An affected-row count of 0 means no row was changed, unless a version-checked operation reports an optimistic-lock error.

Create: insert and save

insert always inserts, regardless of whether a primary-key value is already present. An auto-increment key is set on the input model:

let mut user = User {
    id: None,
    email: "ada@example.com".to_owned(),
    display_name: "Ada".to_owned(),
    active: true,
    profile: serde_json::json!({ "role": "author" }),
    created_at: String::new(),
    deleted_at: None,
    version: 0,
};

users.insert(&mut user).await?;
let id = user.id.expect("database-generated ID");
assert_eq!(user.version, 1);

When a model has auto_create/auto_update fields, the insert expression is generated by the dialect and the caller-provided value is ignored. Reload the model if you need the exact value written by the database.

save chooses insert versus update based on whether the model has a primary-key value:

users.save(&mut new_user).await?;       // primary key absent: insert
users.save(&mut existing_user).await?;  // primary key present: update

It does not first query whether a row with that key exists. A present key selects the update path; it is not an upsert. save requires model primary-key metadata.

Read: find_by_*, find_by_id, first_where, and all

find_by_id looks up the declared primary key and returns an optional model:

let found: Option<User> = users.find_by_id(id).await?;
if let Some(user) = found {
    println!("{}", user.email);
}

For generated field and composite lookups, annotate the model with #[orm(find_by)] attributes and import its generated extension trait. A field-level attribute creates a lookup for that field; a model-level attribute creates a lookup whose fields are combined with AND:

#[derive(Model)]
#[orm(find_by(tenant_id, username))]
struct User {
    #[orm(primary_key, auto_increment)]
    id: Option<i64>,
    #[orm(find_by)]
    email: String,
    tenant_id: i64,
    username: String,
}

use crate::models::{User, UserFindBy};

let by_email = users.find_by_email("ada@example.com".to_owned()).await?;
let by_tenant_and_name = users
    .find_by_tenant_id_username(7, "ada".to_owned())
    .await?;

This generates find_by_email and find_by_tenant_id_username; each returns Result<Option<User>>. See Generated find_by methods for naming rules and compile-time checks.

For fixed JSON paths and JSON-specific operations, declare model-level #[orm(find_json(...))] attributes and import the generated JSON extension trait:

#[derive(Model)]
#[orm(
    find_json(profile.settings.theme, eq, string_match),
    find_json(profile.settings, contains)
)]
struct User {
    #[orm(primary_key, auto_increment)]
    id: Option<i64>,
    profile: serde_json::Value,
}

use crate::models::{User, UserFindJson};
use serde_json::json;

let by_theme = users
    .find_json_eq_profile_settings_theme(json!("dark"))
    .await?;
let by_theme_prefix = users
    .find_json_starts_with_profile_settings_theme("da".to_owned())
    .await?;
let with_published_settings = users
    .find_json_contains_profile_settings(json!({"published": true}))
    .await?;

The declared operations determine which methods are generated and their parameter types. See Generated JSON lookups for the full operation list and behavior.

first_where takes a Condition. It returns None when no row matches:

let found = users
    .first_where(User::email.eq("ada@example.com".to_owned()))
    .await?;

let active_user = users
    .first_where(User::active.is_true())
    .await?;

first_where applies LIMIT 1 but does not add an order. If several rows match and which row is returned matters, build an ordered query with query() instead.

all loads every row returned by the repository query:

let active_users = users
    .query()
    .filter(User::active.is_true())
    .order_by(User::email.asc())
    .build()?;
let active_users: Vec<User> = users.fetch_all(&active_users).await?;

Unlike first_where, all() itself takes no filter or pagination arguments. Normal repository reads (find_by_id, first_where, all, and query) automatically exclude soft-deleted rows.

Filter and count

The derive macro creates typed field proxies. Their comparison methods take the field’s Rust type; for an Option<T> field, compare using a T value. Supported comparisons are eq, ne, gt, gte, lt, lte, and like.

let condition = User::active
    .is_true()
    .and(User::email.like("%@example.com".to_owned()))
    .and(User::id.gte(10_i64));

let query = users
    .query()
    .filter(condition)
    .order_by(User::created_at.desc())
    .limit(25)
    .offset(0)
    .build()?;
let page: Vec<User> = users.fetch_all(&query).await?;

let total_active = users.count().await?;
let matching_active = users
    .count_where(User::active.is_true())
    .await?;

Conditions compose with and and or. Use is_null and is_not_null for nullable fields:

let not_deleted = User::deleted_at.is_null();
let has_deleted_at = User::deleted_at.is_not_null();
let no_email_or_inactive = User::email
    .eq("".to_owned())
    .or(User::active.eq(false));

count and count_where also exclude soft-deleted rows when the model declares #[orm(soft_delete)]. Conditions are parameterized; values are not interpolated into SQL.

For a dynamic column name, use the untyped Field API:

use afastorm::Field;

let statement = users
    .query()
    .filter(Field::new("email").like("%@example.com"))
    .build()?;

Unlike User::email, Field::new does not provide compile-time field/value type checking. Identifiers are validated when the statement is built. Only use column names controlled by the application, not arbitrary client input.

Update

Mutate a loaded model and call save or update:

let mut user = users.find_by_id(id).await?.expect("user exists");
user.display_name = "Ada Lovelace".to_owned();

// The model's current version is used automatically when #[orm(version)] is present.
users.save(&mut user).await?;
assert_eq!(user.version, 2);

update requires a primary key and updates mutable model fields. Primary-key, auto-increment, soft-delete, version, and auto-create fields are not written as ordinary values; auto-update fields are generated by the dialect. If a version field exists, the model’s version is used by default. Pass Some(expected_version) to explicitly supply the expected version, or None to use the model value:

users.update(&mut user, Some(2)).await?;

A stale version returns Error::OptimisticLock. On successful versioned update, afastorm increments the version in the in-memory model. Without a version field, zero affected rows are returned as Ok(0).

Delete, soft-delete, and restore

For a model with #[orm(soft_delete)], delete(id) writes the current-time marker and normal reads stop returning that row. If the model has a version field, delete also increments it:

users.delete(id).await?;
assert!(users.find_by_id(id).await?.is_none());

For optimistic locking, provide the expected version:

users.delete_versioned(id, 2).await?;
users.restore_versioned(id, 3).await?;

A stale version produces Error::OptimisticLock. restore / restore_versioned require a soft-delete field and only affect rows that are currently deleted. Successful restore clears the marker and increments the version when one is configured.

force_delete(id) always issues a physical DELETE, bypassing soft deletion. delete_where also physically deletes rows and should be used with care:

let deleted = users
    .delete_where(User::active.eq(false))
    .await?;

There is no soft-delete bulk-delete variant. To soft-delete matching rows, select them and call delete for each ID (preferably inside a transaction when the set must be handled atomically).

Transactions

For multiple related repository operations, use Orm::transaction; the callback commits on success and rolls back on error. Obtain each repository from the transaction so it uses the same connection:

orm.transaction(async |transaction| {
    let mut user = new_user;
    transaction.repo::<User>()?.insert(&mut user).await?;
    // Other operations through transaction.repo::<OtherModel>() go here.
    Ok(())
}).await?;

See Transactions for manual commit/rollback and error behavior.

Raw SQL and projections

For operations not covered by the repository API, use Orm::query or query_as and bind parameters:

let found: Option<User> = orm
    .query_as(
        "SELECT id, email, display_name, active, profile, created_at, deleted_at, version \
         FROM users WHERE email = ?",
    )
    .bind("ada@example.com")
    .fetch_optional()
    .await?;

The query string is developer-authored SQL. fetch_optional returns None for zero rows, fetch_one errors if there is no row, and fetch_all returns a vector. Raw SQL does not automatically apply the repository’s soft-delete filter. Use SQLx FromRow for custom projection types, or use the derive model decoder when selecting every field required by that model.

For custom query execution and typed field behavior, see Repositories and Queries.