Querying

Everything you do against a QuerySet that isn't covered by Models, Column types, Relationships, Aggregates, Transactions, or Signals lives on this page.

A future split lands the read surface, the expressions surface, and the write surface on three pages (querying.mdx, expressions.mdx, writes.mdx); the design is in docs/decisions/2026-06-06-orm-docs-restructure.md. Until then, the H2 sections below group related material: F-expressions and Q-objects for composable predicates; values, DB-function helpers, and QuerySet sugar for read-side composition; Mutate-side terminals for the write-and-return-row APIs; Composition for subqueries, set ops, and in_bulk; JSON column ops at the end.

F-expressions

An F expression wraps a column name so it can appear on the right-hand side of a comparison or inside an atomic UPDATE. The value is evaluated in the database at query time, not read into Rust first.

Column-vs-column WHERE

Use FColExt::eq_f or ne_f to compare two columns on the same row:

use umbral::orm::{F, FColExt};
 
// WHERE author = editor
let posts = Post::objects()
    .filter(post::AUTHOR.eq_f(F::col("editor")))
    .fetch()
    .await?;

FColExt is automatically in scope for IntCol, ForeignKeyCol, and StrCol. The trait is re-exported from umbral::prelude.

Atomic update arithmetic

F::col("views").add(1) returns an FExpr that encodes views + 1 as a SQL expression tree. Pass it to QuerySet::update_expr:

use umbral::orm::F;
 
// SET views = views + 1, no read-modify-write round-trip
Post::objects()
    .filter(post::ID.eq(42))
    .update_expr("views", F::col("views").add(1))
    .await?;

FExpr supports four arithmetic operations (.add(n), .sub(n), .mul(n), .div(n)), each taking an i64 operand. Chaining is supported: F::col("x").add(3).mul(2) emits (x + 3) * 2.

update_expr returns WriteError::UnknownColumn when the column name is not present on the model. It silently skips the PK column (same as update_values).

For updates that mix JSON values and F-expressions on different columns, call update_values and update_expr separately; a combined API is deferred until a consumer surfaces the need.

Q-objects

Q provides named constructors for the three logical connectives. The existing & / | operator overloads on Predicate<T> continue to work; Q adds Q::not and makes deeply-nested trees more readable.

Q::or

use umbral::orm::Q;
 
// Published OR authored by this user
Post::objects()
    .filter(Q::or(
        post::PUBLISHED.eq(true),
        post::AUTHOR.eq(user_id),
    ))
    .fetch()
    .await?;

Q::and

// Published AND authored by this user (same as two .filter() calls)
Post::objects()
    .filter(Q::and(
        post::PUBLISHED.eq(true),
        post::AUTHOR.eq(user_id),
    ))
    .fetch()
    .await?;

Q::not

// NOT authored by the spam user
Post::objects()
    .filter(Q::not(post::AUTHOR.eq(spam_id)))
    .fetch()
    .await?;

Nesting

Q methods compose recursively:

// (Published AND authored by me) OR (not published)
Post::objects()
    .filter(Q::or(
        Q::and(post::PUBLISHED.eq(true), post::AUTHOR.eq(my_id)),
        Q::not(post::PUBLISHED.eq(true)),
    ))
    .fetch()
    .await?;

Chained .filter() calls still AND together as before; Q is purely additive.

.exclude(p): the negated complement of .filter(p)

QuerySet::exclude(p) and Manager::exclude(p) are sugar for .filter(Q::not(p)). They drop rows where the predicate holds; the negated predicate still ANDs into the chain alongside any filter calls.

// Every non-published post.
Post::objects()
    .exclude(post::PUBLISHED.eq(true))
    .fetch()
    .await?;
 
// Published, but NOT by the spam user.
// Renders as: WHERE published = TRUE AND NOT (author_id = <spam>)
Post::objects()
    .filter(post::PUBLISHED.eq(true))
    .exclude(post::AUTHOR_ID.eq(spam_id))
    .fetch()
    .await?;
 
// Multiple excludes AND together: "not unpublished AND not by author 2".
Post::objects()
    .exclude(post::PUBLISHED.eq(false))
    .exclude(post::AUTHOR_ID.eq(2))
    .fetch()
    .await?;

.exclude() is the negated complement of .filter(). Useful for soft-delete patterns (exclude(deleted_at.is_not_null())) and for "everything except" queries where reaching for Q::not directly would be noisy.

Column projection: .values()

When a list view only needs a couple of fields, swap the full-row read for a column-pinned projection that returns JSON objects instead of typed model rows. This skips both the unused-column transfer cost and the FromRow hydration overhead.

let rows: Vec<serde_json::Value> = Post::objects()
    .filter(post::PUBLISHED.eq(true))
    .order_by(post::ID.desc())
    .values(&["id", "title"])
    .await?;
// [
//   { "id": 3, "title": "third" },
//   { "id": 1, "title": "first" }
// ]

Each returned Value is an object keyed by the requested column names, with values typed per the column's declared SqlType (integers stay integers, booleans stay booleans, dates render as ISO strings).

values() composes with filter, exclude, order_by, limit, offset exactly like the typed terminals. Unknown column names fail loudly with sqlx::Error::Protocol naming the offending column before any SQL runs.

.values("id", "title") pins the projection to the named columns and returns JSON objects. The typed-tuple form (values_list) is deferred until a concrete consumer surfaces the need.

DB-function helpers: .lower(), .upper(), .length(), .trim(), .coalesce(), .concat(), .year(), .month(), .day()

Column extension traits add SQL function wrappers that compose with .filter. Import umbral::orm::column::{StrColExt, DateTimeColExt} to bring them into scope.

use umbral::orm::column::{StrColExt, DateTimeColExt};
 
// Case-insensitive search.
Post::objects()
    .filter(post::TITLE.lower().eq("hello"))
    .fetch()
    .await?;
 
// Filter by string length.
Post::objects()
    .filter(post::TITLE.length().lt(20))
    .fetch()
    .await?;
 
// Year/month/day archive pages.
Post::objects()
    .filter(post::CREATED_AT.year().eq(2026))
    .filter(post::CREATED_AT.month().eq(6))
    .fetch()
    .await?;
 
// Normalize before comparing.
Tagged::objects()
    .filter(tagged::LABEL.trim().eq("spaced"))          // TRIM(label) = 'spaced'
    .filter(tagged::NOTE.coalesce("none").ne("none"))   // COALESCE(note,'none') <> 'none'
    .filter(tagged::LABEL.concat("!").eq("alpha!"))      // label || '!' = 'alpha!'
    .fetch()
    .await?;

StrColExt ships .lower(), .upper(), .length(), .trim(), .coalesce(default), .concat(suffix); DateTimeColExt ships .year()/.month()/.day()/.hour()/.minute()/.second()/.week_day(). Each returns a ColExpr<T>; chain .eq / .ne / .lt / .le / .gt / .ge to finalise the predicate. Date extracts hide backend dispatch automatically (CAST(EXTRACT(YEAR FROM col) AS INTEGER) on Postgres, CAST(strftime('%Y', col) AS INTEGER) on SQLite), so the call site stays portable.

A DB-side now() is intentionally not provided: CURRENT_TIMESTAMP's text format differs from sqlx-chrono's stored ISO format on SQLite, so a column-vs-now comparison would be silently wrong there. Bind the Rust value instead, post::PUBLISHED_AT.lt(chrono::Utc::now()), which is correct on every backend.

QuerySet sugar: .earliest(), .latest(), .distinct(), .explain()

// Earliest / latest by column.
let first_post = Post::objects().earliest("created_at").await?;
let newest = Post::objects().latest("created_at").await?;
 
// SELECT DISTINCT, most useful with values() to dedupe a column projection.
let unique_tags = Post::objects()
    .distinct()
    .values(&["tag"])
    .await?;
 
// EXPLAIN: eyeball the execution plan.
let plan: String = Post::objects()
    .filter(post::PUBLISHED.eq(true))
    .explain()
    .await?;
println!("{plan}");

Mutate-side terminals: update_or_create, bulk_update, raw

// Upsert with predicate + defaults.
let (post, created) = Post::objects()
    .update_or_create(
        Predicate::col_eq("slug", "first-post"),
        Post { id: 0, slug: "first-post".into(), title: "Hello".into(), ..Default::default() },
    )
    .await?;
 
// Bulk update: per-row differing values in one statement via CASE WHEN.
let n = Post::objects().bulk_update(modified_posts).await?;
 
// Raw SQL escape hatch returning typed Vec<T>.
let recent: Vec<Post> = Post::objects()
    .raw("SELECT * FROM post WHERE created_at > datetime('now', '-7 days')")
    .await?;

update_or_create returns (row, created: bool). Both get_or_create and update_or_create are safe under concurrent callers: if two writers race and both try to INSERT the same row, the one that loses the race catches the UniqueViolation and re-fetches the winning row, so callers always converge rather than seeing an error. A UNIQUE constraint on the predicate columns is required for this guarantee. bulk_update returns rows-affected. raw skips select_related/prefetch_related chains and binds no parameters; sanitise input before calling.

Composition: subqueries + set ops + in_bulk

// col IN (SELECT col FROM ...), sub-builders bind cleanly.
let staff_posts = Post::objects()
    .filter(post::AUTHOR.in_subquery(
        User::objects()
            .filter(user::IS_STAFF.eq(true))
            .into_subquery("id"),
    ))
    .fetch()
    .await?;
 
// union / intersect / except across two QuerySet<T>.
let combined = Post::objects()
    .filter(post::ID.le(10))
    .union(Post::objects().filter(post::AUTHOR.eq(self.id)))
    .fetch()
    .await?;
 
// in_bulk: cached-IDs lookup.
let users_by_id: HashMap<i64, User> =
    User::objects().in_bulk(vec![1, 2, 3]).await?;

into_subquery(col) clears the SELECT list to just col and hands the result to IntCol::in_subquery / ForeignKeyCol::in_subquery. union / intersect / except work on same-typed QuerySets; column-shape compatibility is enforced by the shared T param. in_bulk(pks) takes a Vec<T::PrimaryKey> and returns a HashMap<T::PrimaryKey, T>, so i64-, String/slug-, and Uuid-keyed models all work; missing ids are simply absent from the map.

JSON column ops

JsonCol and NullableJsonCol carry backend-aware helpers for the everyday "is this key present" / "drill into nested value" patterns:

// has_key: true when the JSON object has the named top-level key.
// Postgres: meta ? 'name'   /   SQLite: json_extract(meta, '$.name') IS NOT NULL
Doc::objects()
    .filter(doc::META.has_key("name"))
    .fetch()
    .await?;
 
// path_text: drill into a nested path; compare as text.
// Postgres: meta -> 'nested' ->> 'role' = 'admin'
// SQLite:   json_extract(meta, '$.nested.role') = 'admin'
Doc::objects()
    .filter(doc::META.path_text(&["nested", "role"]).eq("admin"))
    .fetch()
    .await?;

path_text returns a chainable supporting .eq / .ne / .is_null / .is_not_null. Both helpers compose with the rest of QuerySet::filter.

See also

• Declaring models: the QuerySet API (filter, update_values, fetch, get, etc.) that F and Q plug into.
• Relationships: ForeignKey<T> and select_related; ForeignKeyCol supports eq_f / ne_f for column-vs-column WHERE.
• Aggregates: Count, Sum, Avg, Max, Min plus annotate(group_cols, ...).
• Transactions: combining F-expression updates with other writes atomically.

Design rationale

See arch.md §M1 (QuerySet design) and docs/specs/03-orm-querysets.md for the predicate composition model.