Queries¶

Ferro provides a fluent, type-safe API for building queries in Python and executing them on the Rust engine. All values are parameterized — user input is never concatenated into SQL.

The examples on this page use this model:

from ferro import Field, Model, connect, engines


class User(Model):
    id: int | None = Field(default=None, primary_key=True)
    name: str
    age: int
    role: str = "member"
    archived: bool = False

from typing import Annotated

from ferro import Field, Model, connect, engines


class User(Model):
    id: Annotated[int | None, Field(default=None, primary_key=True)]
    name: str
    age: int
    role: str = "member"
    archived: bool = False

Queries Are Immutable¶

Every chain call — .where(), .order_by(), .limit(), .offset() — returns a new Query. None of them mutate the query they were called on, so a partially-built query is safe to keep around and reuse as the base for several different follow-ups:

base = User.where(lambda user: user.role == "admin")

page1 = base.limit(10)             # first 10 admins
page2 = base.limit(10).offset(10)  # next 10 admins

base still matches every admin with no limit() applied — building page1 and page2 from it doesn't change it, and page1 and page2 don't affect each other either. This is what makes patterns like "build a filtered base query, then branch into a count and a page of results" safe:

active = User.where(lambda user: user.archived == False)  # noqa: E712

total = await active.count()
first_page = await active.order_by(lambda user: user.id).limit(20).all()

active is never consumed or altered by either await — you can keep branching off it as many times as you like.

Fetching by Primary Key¶

Model.get(pk) loads exactly one row and returns your model type — not YourModel | None. If no row exists it raises ModelDoesNotExist, a LookupError subclass carrying .model and .pk (handy for HTTP 404s and structured logging). When a missing row is a normal outcome, use Model.get_or_none(pk) instead:

from ferro import ModelDoesNotExist

user = await User.get(42)  # User — raises if missing

try:
    user = await User.get(client_supplied_id)
except ModelDoesNotExist:
    ...  # e.g. return 404 from your HTTP layer

maybe = await User.get_or_none(999)  # User | None — never raises for "not found"

Both methods also exist on Model.using("name") for named connections.

Filtering with where()¶

Model.where(...) starts a chainable query; terminals like .all() execute it. Predicates are written as lambdas — the parameter (t by convention) is a query proxy whose attributes stand in for your model's columns:

        adults = await User.where(lambda user: user.age >= 18).all()

Model.select() starts an unfiltered query — useful when you only want ordering, slicing, or a count.

Predicate Style¶

where() accepts a lambda predicate — a callable that receives a query proxy and returns a comparison. The proxy's attributes are validated against your model's columns at build time (a misspelled column raises AttributeError naming the closest match, before any query reaches the database):

        admins = await User.where(lambda user: (user.role == "admin") & (user.archived == False)).all()  # noqa: E712

Typo a column name and you find out immediately, not after the query round-trips to the database:

>>> await User.where(lambda user: user.naem == "alice").all()
AttributeError: User has no queryable column 'naem'. Did you mean 'name'? Valid columns: age, archived, id, name, role.

The valid-columns list includes shadow {fk}_id foreign-key columns (see Querying Across Relationships), so the error is always a complete picture of what you can filter on.

Lambda predicates keep the call site fully type-checked: the proxy's attributes are real FieldProxy objects in the type checker's eyes, not your Pydantic annotations. See Typed Query Predicates for the full reasoning.

Operators¶

        teens = await User.where(lambda user: (user.age >= 13) & (user.age <= 19)).all()
        a_names = await User.where(lambda user: user.name.like("a%")).all()
        staff = await User.where(lambda user: user.role.in_(["admin", "moderator"])).all()

Combining Conditions¶

Combine predicates with & (AND) and | (OR), or chain multiple .where() calls (which AND together):

        # & is AND, | is OR — parenthesize each side
        flagged = await User.where(lambda user: (user.age < 18) | (user.archived == True)).all()  # noqa: E712

        # Chained .where() calls also AND together
        young_members = await User.where(lambda user: user.role == "member").where(lambda user: user.age < 21).all()

Negating Conditions¶

Prefix ~ negates any predicate — a comparison, .in_(), .like(), or a whole &/| group. It is the one negation rule in Ferro: there are no per-operator negative forms (no not_in(), no not_like()), because ~ already covers every predicate the same way:

        # ~ negates ANY predicate: a comparison, .in_(), .like(), or a whole
        # &/| group. There are no per-operator negative forms to memorize.
        not_staff = await User.where(lambda user: ~user.role.in_(["admin", "moderator"])).all()
        not_a_names = await User.where(lambda user: ~user.name.like("a%")).all()
        active_adults = await User.where(lambda user: ~((user.age < 18) | (user.archived == True))).all()  # noqa: E712

Each ~ renders as a faithful SQL NOT (...) over the condition it wraps. The last example above ships to the database as:

SELECT ... FROM user WHERE NOT (user.age < 18 OR user.archived = TRUE)

Negated conditions compose exactly like un-negated ones — mix them into &/| trees at any depth, chain .order_by()/.limit() after them, and double negation (~~p) means what it says. Adding a ~ never restructures your query.

Negation and NULL values¶

SQL comparisons follow three-valued logic: a comparison against NULL is neither true nor false but unknown, and a WHERE clause only keeps rows whose condition is true. NOT maps unknown to unknown — so a negated comparison still excludes rows where the column is NULL. ~ is SQL's NOT, not Python's set complement.

Concretely, with a nullable column:

class Invoice(Model):
    id: int | None = Field(default=None, primary_key=True)
    reference: str
    amount: float | None = None  # None until the invoice is issued

class Invoice(Model):
    id: Annotated[int | None, Field(default=None, primary_key=True)]
    reference: str
    amount: float | None = None  # None until the invoice is issued

~(invoice.amount > 100) renders as:

SELECT ... FROM invoice WHERE NOT (invoice.amount > 100)

For a row where amount is NULL, amount > 100 is unknown, NOT unknown is still unknown, and the row is excluded — from the negated query and from the original one. This matches the != operator you already use (invoice.amount != 100 excludes NULL rows too); ~ just makes the rule visible on every predicate. When you want the NULL rows kept, say so explicitly with an IS NULL branch:

        big = await Invoice.where(lambda invoice: invoice.amount > 100).all()
        not_big = await Invoice.where(lambda invoice: ~(invoice.amount > 100)).all()

        # inv-3 (amount is NULL) appears in NEITHER list
        assert {invoice.reference for invoice in big} == {"inv-1"}
        assert {invoice.reference for invoice in not_big} == {"inv-2"}

        # keeping the NULL rows is an explicit extra condition
        not_big_or_unissued = await Invoice.where(
            lambda invoice: ~(invoice.amount > 100) | (invoice.amount == None)  # noqa: E711
        ).all()
        assert {invoice.reference for invoice in not_big_or_unissued} == {"inv-2", "inv-3"}

Ordering, Limit & Offset¶

Sort with .order_by(field, direction) (direction defaults to ascending; pass "desc" to reverse) and slice with .limit() / .offset(). field is a lambda naming the column (order_by(lambda u: u.created_at, "desc"), matching the where() predicate style) or a column-name string (order_by("created_at", "desc")). Both forms are validated against the model's queryable columns at build time:

        oldest_first = await User.select().order_by(lambda user: user.age, "desc").all()
        second_page = (
            await User.select().order_by(lambda user: user.id).limit(2).offset(2).all()
        )

Chain .order_by() multiple times for multi-column sorts. For robust pagination patterns, see Pagination.

Executing Queries¶

Queries are lazy — nothing hits the database until you await a terminal:

        everyone = await User.all()
        first_admin = await User.where(lambda user: user.role == "admin").first()
        headcount = await User.select().count()
        any_minors = await User.where(lambda user: user.age < 18).exists()

Model.all() is shorthand for Model.select().all().

On a projected query (select(lambda t: (t.id, t.amount))), .all() returns Rows[Row] and .first() returns Row | None — records, not model instances; count() and exists() are unchanged on a plain projection (on an aggregate projection they raise with guidance). See Selecting a Column Subset and Aggregations & Grouped Queries.

Querying Across Relationships¶

A where() or order_by() lambda can reach through a ForeignKey to a column on the related model. Write the relation field, then keep going:

ledger_a = await Ledger.get(1)

rows = await Transaction.where(
    lambda transaction: transaction.account.ledger_id == ledger_a.id
).all()

That is one SQL statement — a SELECT over transactions with a join to accounts — and it returns the transactions whose account belongs to ledger A. You never write the join; the relation you traverse (transaction.account) is the join.

The examples below use this schema — a Transaction points at an Account, and an Account points at both a Ledger and an Owner:

class Ledger(Model):
    id: int | None = Field(default=None, primary_key=True)
    name: str
    accounts: Relation[list["Account"]] = BackRef()


class Owner(Model):
    id: int | None = Field(default=None, primary_key=True)
    email: str
    accounts: Relation[list["Account"]] = BackRef()


class Account(Model):
    id: int | None = Field(default=None, primary_key=True)
    label: str
    ledger: Annotated[Ledger, ForeignKey(related_name="accounts")]
    owner: Annotated[Owner, ForeignKey(related_name="accounts")]
    transactions: Relation[list["Transaction"]] = BackRef()
    notes: Relation[list["Note"]] = BackRef()


class Transaction(Model):
    id: int | None = Field(default=None, primary_key=True)
    amount: int
    account: Annotated[Account, ForeignKey(related_name="transactions")]

class Ledger(Model):
    id: Annotated[int | None, Field(default=None, primary_key=True)]
    name: str
    accounts: Relation[list["Account"]] = BackRef()


class Owner(Model):
    id: Annotated[int | None, Field(default=None, primary_key=True)]
    email: str
    accounts: Relation[list["Account"]] = BackRef()


class Account(Model):
    id: Annotated[int | None, Field(default=None, primary_key=True)]
    label: str
    ledger: Annotated[Ledger, ForeignKey(related_name="accounts")]
    owner: Annotated[Owner, ForeignKey(related_name="accounts")]
    transactions: Relation[list["Transaction"]] = BackRef()
    notes: Relation[list["Note"]] = BackRef()


class Transaction(Model):
    id: Annotated[int | None, Field(default=None, primary_key=True)]
    amount: int
    account: Annotated[Account, ForeignKey(related_name="transactions")]

Traversing at any depth¶

Each hop resolves against the related model, so you can chain as many as the schema allows. transaction.account.owner.email walks Transaction → Account → Owner in a single statement with two joins:

        rows = await Transaction.where(
            lambda transaction: transaction.account.owner.email == "[email protected]"
        ).all()

Every hop is validated when you build the query — before anything reaches the database. A misspelled column or relation raises AttributeError naming the model at that hop and the closest match, and the suggestion pool spans both columns and relations:

>>> Transaction.where(lambda transaction: transaction.accont.ledger_id == 1)
AttributeError: Transaction has no queryable column 'accont'. Did you mean 'account'? Valid columns: account_id, amount, id. Valid relations: account.

>>> Transaction.where(lambda transaction: transaction.account.owner.emial == "x")
AttributeError: Owner has no queryable column 'emial'. Did you mean 'email'? Valid columns: email, id.

Every traversed hop is an INNER join¶

Traversal always renders an INNER join, at every hop, no matter whether the foreign key is nullable (ADR-0006). To see what that means for nullable relations, the narrowing examples in this and the following sections add a Note model whose account relation is optional — a note may or may not be attached to an account:

class Note(Model):
    id: int | None = Field(default=None, primary_key=True)
    body: str
    account: Annotated[Account | None, ForeignKey(related_name="notes")] = None

class Note(Model):
    id: Annotated[int | None, Field(default=None, primary_key=True)]
    body: str
    account: Annotated[Account | None, ForeignKey(related_name="notes")] = None

The practical consequence of INNER-everywhere: traversing a relation narrows the result to rows where that relation exists. A Note whose account FK is NULL simply does not appear in a query that filters through note.account:

        # Filtering through note.account drops the orphan note (NULL account FK).
        ledger_a_notes = await Note.where(
            lambda note: note.account.ledger_id == ledger_a.id
        ).all()

This is deliberate and stable. A hop's nullability never changes the join type, so making a foreign key nullable later never silently rewrites the meaning of an existing query, and an N-hop path is trivial to reason about — no hop poisons the ones after it. Keeping the relation-less rows is an explicit opt-in (left_join, below).

The narrowing is query-wide, not per-clause: the join is rendered once for the whole statement, so a traversal branch inside an | still narrows the entire result. (note.account.ledger_id == 1) | (note.body == "orphan") drops every relation-less note — the INNER account join removes it before the OR is ever evaluated, so the body == "orphan" branch can never rescue it. Reach for left_join when a traversal branch of an | must keep relation-less rows.

count() and the other terminals see exactly this narrowed set — a many-to-one join never multiplies root rows, so .count() equals the number of matching transactions, not the number of joined pairs:

        ledger_a_count = await Transaction.where(
            lambda transaction: transaction.account.ledger_id == ledger_a.id
        ).count()

Ordering by a related column¶

order_by() traverses the same way, to any depth, and shares its joins with where():

        ordered = await (
            Transaction.select()
            .order_by(lambda transaction: transaction.account.label)
            .order_by(lambda transaction: transaction.id)
            .all()
        )

Because the sort join is INNER too, ordering by a related column drops relation-less rows — the same narrowing as where(). (Use left_join to keep them; see below.)

One join per relation path¶

The relation path is the join's identity. Reference the same path in two where() calls, in an &/| tree, or across where() and order_by(), and it renders as one join. Distinct paths — even to the same table — render as distinct joins. There is no alias to name and none to manage; the path does that job.

That is what lets a traversal filter compose cleanly with plain root-column clauses — the motivating query pairs one account join with a root-column filter, ordering, and a limit, all in a single statement:

        top = await (
            Transaction.where(lambda transaction: transaction.account.ledger_id == ledger_a.id)
            .where(lambda transaction: transaction.amount >= 20)
            .order_by(lambda transaction: transaction.amount, "desc")
            .limit(2)
            .all()
        )

Comparing to a related instance¶

Every ForeignKey also exposes a shadow {fk}_id column, so you can filter by a related row's primary key directly — no join at all:

posts = await Post.where(lambda post: post.author_id == user.id).all()

Comparing the relation itself to a persisted instance is sugar for exactly that shadow-column check — still join-free:

        # `== instance` filters by the shadow FK column, with no join.
        on_a1 = await Transaction.where(
            lambda transaction: transaction.account == account_a1
        ).all()
        not_on_a1 = await Transaction.where(
            lambda transaction: transaction.account != account_a1
        ).all()

Deep instance equality compares the shadow column of the last hop under the prefix join: transaction.account.owner == some_owner filters owner_id on the joined accounts row.

        owned_by_o2 = await Transaction.where(
            lambda transaction: transaction.account.owner == owner_o2
        ).all()

Comparing an unpersisted instance is a build-time error naming the model (cannot compare relation 'account' to an unpersisted Account instance …) — there is no primary key to match on yet.

Testing for existence or absence¶

A bare .join() with no predicate is a meaningful existence filter on a nullable relation — it narrows to rows where the relation is present (the same INNER narrowing traversal gives you, expressed directly):

        # Bare .join() keeps only rows whose nullable relation exists.
        with_account = await Note.select().join(lambda note: note.account).all()

For the opposite question — "has no related row" — compare the relation to None. relation == None / != None lower to IS NULL / IS NOT NULL on the shadow column, join-free, so "has no account" needs no left_join:

        # Join-free IS NULL / IS NOT NULL on the shadow FK.
        orphans = await Note.where(lambda note: note.account == None).all()  # noqa: E711
        attached = await Note.where(lambda note: note.account != None).all()  # noqa: E711

Both spellings here are the forward direction — the FK column lives on the queried table. Asking the same question in the reverse direction ("has at least one / no related child row") is an existence test: t.lines.exists() / ~t.lines.exists().

Keeping rows that have no relation¶

When you want the relation-less rows kept rather than filtered out, opt into a left_join. It marks every edge of its path LEFT (the whole-path rule), so a left-marked two-hop path retains rows missing the relation at either hop:

        # .left_join() keeps the orphan row that traversal would drop.
        all_notes = await Note.select().left_join(lambda note: note.account).all()

A bare left_join on a path also traversed by where() lifts the shared edge to LEFT — an explicit LEFT always beats an implicit INNER on the same edge (declaring join and left_join on one edge is a build-time ValueError).

Two foreign keys to the same table¶

Distinct relation paths are distinct joins even when they point at the same table, so two FKs to one model just work — no alias ceremony:

class Airport(Model):
    id: int | None = Field(default=None, primary_key=True)
    code: str
    departures: Relation[list["Flight"]] = BackRef()
    arrivals: Relation[list["Flight"]] = BackRef()


class Flight(Model):
    id: int | None = Field(default=None, primary_key=True)
    origin: Annotated[Airport, ForeignKey(related_name="departures")]
    destination: Annotated[Airport, ForeignKey(related_name="arrivals")]

class Airport(Model):
    id: Annotated[int | None, Field(default=None, primary_key=True)]
    code: str
    departures: Relation[list["Flight"]] = BackRef()
    arrivals: Relation[list["Flight"]] = BackRef()


class Flight(Model):
    id: Annotated[int | None, Field(default=None, primary_key=True)]
    origin: Annotated[Airport, ForeignKey(related_name="departures")]
    destination: Annotated[Airport, ForeignKey(related_name="arrivals")]

    # Each FK is its own relation path, so each traversal is its own join —
    # no aliases to name.
    departing_jfk = await Flight.where(lambda flight: flight.origin.code == "JFK").all()
    arriving_jfk = await Flight.where(
        lambda flight: flight.destination.code == "JFK"
    ).all()

Self-referential traversal¶

A self-referencing FK traverses like any other — the join target is the same table, reached by a distinct path:

class Employee(Model):
    id: int | None = Field(default=None, primary_key=True)
    name: str
    manager: Annotated["Employee", ForeignKey(related_name="reports", nullable=True)] = None
    reports: Relation[list["Employee"]] = BackRef()

class Employee(Model):
    id: Annotated[int | None, Field(default=None, primary_key=True)]
    name: str
    manager: Annotated["Employee", ForeignKey(related_name="reports", nullable=True)] = None
    reports: Relation[list["Employee"]] = BackRef()

    boss_reports = await Employee.where(
        lambda employee: employee.manager.name == "boss"
    ).all()

Traversing from a many-to-many¶

An association query (post.tags) composes with forward-FK traversal on the target model: the association join and the traversal join coexist in one statement.

class Author(Model):
    id: int | None = Field(default=None, primary_key=True)
    role: str
    tags: Relation[list["Tag"]] = BackRef()


class Tag(Model):
    id: int | None = Field(default=None, primary_key=True)
    name: str
    created_by: Annotated[Author, ForeignKey(related_name="tags")]
    posts: Relation[list["Post"]] = BackRef()


class Post(Model):
    id: int | None = Field(default=None, primary_key=True)
    title: str
    tags: Relation[list["Tag"]] = ManyToMany(related_name="posts")

class Author(Model):
    id: Annotated[int | None, Field(default=None, primary_key=True)]
    role: str
    tags: Relation[list["Tag"]] = BackRef()


class Tag(Model):
    id: Annotated[int | None, Field(default=None, primary_key=True)]
    name: str
    created_by: Annotated[Author, ForeignKey(related_name="tags")]
    posts: Relation[list["Post"]] = BackRef()


class Post(Model):
    id: Annotated[int | None, Field(default=None, primary_key=True)]
    title: str
    tags: Relation[list["Tag"]] = ManyToMany(related_name="posts")

    # The association context (post.tags) and forward-FK traversal on the tag
    # compose in one statement.
    admin_tags = await post.tags.where(
        lambda tag: tag.created_by.role == "admin"
    ).all()

Filtering reverse relations¶

Reverse relations (BackRef) are chainable queries in their own right — filter, order, and slice them before executing, and their predicates can traverse too:

published = await author.posts.where(lambda post: post.published == True).all()  # noqa: E712
latest = await author.posts.order_by(lambda post: post.created_at, "desc").limit(5).all()
n = await author.posts.count()

That is a query from an instance. To filter the root query on membership in a reverse relation — "authors who have at least one published post" — use an existence test: Author.where(lambda a: a.posts.exists(lambda post: post.published == True)).

Results are plain root instances¶

A traversed query is shape-preserving: filtering Transaction through transaction.account.ledger_id still returns Transaction instances, no matter how deep the predicate reaches. Traversal does not pre-load the related rows onto the results — await transaction.account still issues its own query, exactly as it does without any traversal. Attaching related data is a separate, explicit request: include().

update() and delete() cannot traverse¶

Portable SQL has no UPDATE … JOIN / DELETE … JOIN, so a traversed predicate on update()/delete() is rejected before any SQL runs:

>>> await Transaction.where(lambda transaction: transaction.account.label == "a1").delete()
ValueError: delete() does not support relation traversal: portable SQL has no DELETE ... JOIN. Fetch primary keys via the joined query first, then delete by primary-key set. (A join-free relation filter like `t.account == instance` is allowed.)

Do the two-step: fetch the primary keys with the joined query, then mutate by that key set (a join-free relation == instance or == None filter is allowed on mutations):

        # Traversed predicates cannot mutate — fetch primary keys first...
        ids = [
            note.id
            for note in await Note.where(
                lambda note: note.account.ledger_id == ledger_a.id
            ).all()
        ]
        # ...then delete by that key set with a join-free predicate.
        await Note.where(lambda note: note.id.in_(ids)).delete()

Guardrails¶

• A predicate lambda that returns a bare relation (lambda transaction: transaction.account) is meaningless as a filter and raises TypeError pointing you at == None, == an instance, or a column comparison. The same bare relation in order_by() is likewise rejected.
• Combining predicates with Python's and/or/not (instead of &/|/~) coerces a node to bool and raises TypeError: QueryNode cannot be used in a boolean context; use & / | to combine predicates and ~ to negate them. Always parenthesize and use the bitwise operators.

See Relationships for the schema-declaration side of foreign keys and reverse relations.

Existence Tests on Reverse & Many-to-Many Relations¶

Traversal reaches forward along a foreign key. The reverse question — "which transactions have at least one split line?", "which transactions appear in any transfer?" — is a different shape: the related rows live on the other table, keyed back at you. In a predicate, a reverse (BackRef) or many-to-many relation supports exactly one verb for that question, the existence test:

matches = await Transaction.where(lambda t: t.lines.exists()).all()

.exists() renders as a correlated EXISTS subquery — never a join — so the result stays root-shaped: each matching row comes back exactly once (a transaction with three lines is one result, no DISTINCT bookkeeping), rows are never multiplied, and the test composes with every other predicate, ordering, and paging. One verb covers every cardinality: a one-to-one BackRef, a to-many BackRef, and an M2M edge all spell the same, so a schema cardinality change never breaks a call site.

The examples below use this schema — a transfer links two transactions through unique FKs (one-to-one BackRefs), split lines hang off a transaction (to-many), and a category is referenced by both layers:

class Category(Model):
    id: int | None = Field(default=None, primary_key=True)
    name: str
    transactions: Relation[list["Transaction"]] = BackRef()
    lines: Relation[list["SplitLine"]] = BackRef()


class Transaction(Model):
    id: int | None = Field(default=None, primary_key=True)
    amount: int
    category: Annotated[
        Category | None, ForeignKey(related_name="transactions", on_delete="SET NULL")
    ] = None
    # One-to-one BackRefs: a transfer references a transaction via a unique FK
    transfer_out: "Transfer" = BackRef()
    transfer_in: "Transfer" = BackRef()
    # To-many BackRef: a split transaction carries its lines
    lines: Relation[list["SplitLine"]] = BackRef()


class Transfer(Model):
    id: int | None = Field(default=None, primary_key=True)
    outflow_transaction: Annotated[
        Transaction | None, ForeignKey(related_name="transfer_out", unique=True)
    ] = None
    inflow_transaction: Annotated[
        Transaction | None, ForeignKey(related_name="transfer_in", unique=True)
    ] = None


class SplitLine(Model):
    id: int | None = Field(default=None, primary_key=True)
    txn: Annotated[Transaction, ForeignKey(related_name="lines", on_delete="CASCADE")]
    category: Annotated[
        Category | None, ForeignKey(related_name="lines", on_delete="SET NULL")
    ] = None
    amount: int = 0

class Category(Model):
    id: Annotated[int | None, Field(default=None, primary_key=True)]
    name: str
    transactions: Relation[list["Transaction"]] = BackRef()
    lines: Relation[list["SplitLine"]] = BackRef()


class Transaction(Model):
    id: Annotated[int | None, Field(default=None, primary_key=True)]
    amount: int
    category: Annotated[
        Category | None, ForeignKey(related_name="transactions", on_delete="SET NULL")
    ] = None
    # One-to-one BackRefs: a transfer references a transaction via a unique FK
    transfer_out: "Transfer" = BackRef()
    transfer_in: "Transfer" = BackRef()
    # To-many BackRef: a split transaction carries its lines
    lines: Relation[list["SplitLine"]] = BackRef()


class Transfer(Model):
    id: Annotated[int | None, Field(default=None, primary_key=True)]
    outflow_transaction: Annotated[
        Transaction | None, ForeignKey(related_name="transfer_out", unique=True)
    ] = None
    inflow_transaction: Annotated[
        Transaction | None, ForeignKey(related_name="transfer_in", unique=True)
    ] = None


class SplitLine(Model):
    id: Annotated[int | None, Field(default=None, primary_key=True)]
    txn: Annotated[Transaction, ForeignKey(related_name="lines", on_delete="CASCADE")]
    category: Annotated[
        Category | None, ForeignKey(related_name="lines", on_delete="SET NULL")
    ] = None
    amount: int = 0

Bare tests and negation¶

A bare .exists() asks "is any related row there?". Negation is the uniform ~ — NOT EXISTS is not a separate spelling:

        # "Is this transaction part of any transfer?" — membership via either
        # FK column, one EXISTS per side, each matching row exactly once.
        in_transfer = await Transaction.where(
            lambda t: t.transfer_out.exists() | t.transfer_in.exists()
        ).all()

        # The negated branch: ~ renders NOT EXISTS
        not_in_transfer = await Transaction.where(
            lambda t: ~t.transfer_out.exists() & ~t.transfer_in.exists()
        ).all()

The first query ships to the database as:

SELECT ... FROM transaction t
WHERE EXISTS (SELECT 1 FROM transfer WHERE transfer.outflow_transaction_id = t.id)
   OR EXISTS (SELECT 1 FROM transfer WHERE transfer.inflow_transaction_id = t.id)

Scoping with an inner predicate¶

Pass a lambda to filter which related rows count. The inner lambda is a full ferro predicate over the related model — every operator, &/|/~, forward traversal, even nested existence tests — not a sub-language:

        # The inner lambda is a full ferro predicate over the related model:
        # "transactions carrying the category at the root OR on any line".
        # A line-less transaction survives through the OR's root branch, and
        # a transaction with several matching lines comes back exactly once.
        matching = await Transaction.where(
            lambda t: t.category_id.in_(ids)
            | t.lines.exists(lambda line: line.category_id.in_(ids))
        ).all()

Rendered SQL — the root branch keeps line-less transactions, the EXISTS branch finds categories on any line:

SELECT ... FROM transaction t
WHERE t.category_id IN (...)
   OR EXISTS (SELECT 1 FROM split_line l
              WHERE l.txn_id = t.id AND l.category_id IN (...))

Because the result is root-shaped, keyset ordering and paging compose unchanged:

        # Root-shaped results: existence tests compose with every other
        # predicate, ordering, and paging — nothing about the query changes.
        page = (
            await Transaction.where(
                lambda t: t.category_id.in_(ids)
                | t.lines.exists(lambda line: line.category_id.in_(ids))
            )
            .order_by("amount", "desc")
            .limit(1)
            .all()
        )

Grouping is explicit¶

With conditions on the same relation, there are two different questions: does one related row match all conditions, or does some related row match each? The lambda scope makes the choice visible — one test with a compound inner predicate, or two tests combined outside:

        # Grouping is YOUR choice, spelled explicitly — the two shapes below
        # are different questions with different answers.

        # One line matches BOTH conditions:
        one_line_both = await Transaction.where(
            lambda t: t.lines.exists(
                lambda line: line.category_id.in_(ids) & (line.amount <= -50)
            )
        ).all()

        # SOME line matches each condition (possibly different lines):
        some_line_each = await Transaction.where(
            lambda t: t.lines.exists(lambda line: line.category_id.in_(ids))
            & t.lines.exists(lambda line: line.amount <= -50)
        ).all()

This is why reverse relations have an explicit combinator rather than implicit path traversal (t.lines.category_id == x raises): with implicit traversal, nothing on the page says which of those two questions (t.lines.a == 1) & (t.lines.b == 2) asks (ADR-0007).

Traversal inside the test, and nesting¶

Forward-FK traversal works inside the inner lambda — its joins render inside the EXISTS subquery with the same INNER semantics as everywhere else — and existence tests nest to any depth:

        # Forward traversal works inside the test (joins render INSIDE the
        # EXISTS subquery, ADR-0006 semantics unchanged), and tests nest.
        by_name = await Transaction.where(
            lambda t: t.lines.exists(lambda line: line.category.name == "Groceries")
        ).all()

        active_categories = await Category.where(
            lambda c: c.lines.exists(lambda line: line.txn.amount < -50)
        ).all()

Many-to-many¶

An M2M relation spells identically, from either side — the test correlates through the association table and the inner lambda scopes over the target model:

class Tag(Model):
    id: int | None = Field(default=None, primary_key=True)
    name: str
    users: Relation[list["User"]] = ManyToMany(related_name="tags")


class User(Model):
    id: int | None = Field(default=None, primary_key=True)
    username: str
    tags: Relation[list["Tag"]] = BackRef()

class Tag(Model):
    id: Annotated[int | None, Field(default=None, primary_key=True)]
    name: str
    users: Relation[list["User"]] = ManyToMany(related_name="tags")


class User(Model):
    id: Annotated[int | None, Field(default=None, primary_key=True)]
    username: str
    tags: Relation[list["Tag"]] = BackRef()

        # Many-to-many spells identically — the test correlates through the
        # join table, and the inner lambda scopes over the target model.
        admins = await User.where(
            lambda u: u.tags.exists(lambda tag: tag.name == "admin")
        ).all()
        tagged = await User.where(lambda u: u.tags.exists()).all()
        untagged = await User.where(lambda u: ~u.tags.exists()).all()

One verb, loud dead ends¶

Reverse relations are tested, never traversed (ADR-0007). Every other way of naming a reverse or M2M relation in a query fails at build time with the supported spelling in the message:

• Column access (t.lines.category_id) raises AttributeError — scope the columns with the inner lambda instead: t.lines.exists(lambda line: line.category_id == ...).
• Comparisons, including the tempting t.transfer_out != None, raise TypeError — a reverse relation has no root-side column to be NULL; the spelling is t.transfer_out.exists() (and ~t.transfer_out.exists() for absence).
• in_() with a query (t.id.in_(subquery)) raises TypeError — the workloads an IN (subquery) serves are existence-test workloads, without hand-correlating on id columns.
• join() / left_join() on a reverse edge raise TypeError — a join there would multiply root rows; membership is the existence test's job.
• The inner lambda sees only its own parameter. Referencing the outer lambda's parameter (or comparing column-to-column) is a build-time error — cross-scope correlation is a tracked future capability (#309), never a silent misrender.

Existence tests answer membership only. Populating a reverse collection onto results (the data axis) is a separate future mechanism — see Not Yet Supported; until it lands, fetch collections through the relation itself (await txn.lines.all()).

Populating Relations with include()¶

Every relation access is a query. A list view that renders 100 transactions with their account labels awaits transaction.account 100 times — 101 statements for one screen. include() cures that N+1: ask the query to bring the related rows along, and each result's relation arrives populated:

        # One SQL statement; every transaction's account arrives populated.
        transactions = await Transaction.select().include(lambda t: t.account).all()

        for transaction in transactions[:3]:
            # Plain attribute access — no await, no query.
            print(transaction.amount, transaction.account.label)

One SQL statement. The query still returns the same list[Transaction] it always did — and transaction.account is now a plain attribute holding the complete Account instance, exactly as the field's annotation (account: Account) always claimed. No await, no query.

The examples in this section use this schema (both foreign keys nullable, so you can see what happens when a relation is absent):

class Owner(Model):
    id: int | None = Field(default=None, primary_key=True)
    name: str
    accounts: Relation[list["Account"]] = BackRef()


class Account(Model):
    id: int | None = Field(default=None, primary_key=True)
    label: str
    owner: Annotated[Owner | None, ForeignKey(related_name="accounts")] = None
    transactions: Relation[list["Transaction"]] = BackRef()


class Transaction(Model):
    id: int | None = Field(default=None, primary_key=True)
    amount: int
    account: Annotated[Account | None, ForeignKey(related_name="transactions")] = None

class Owner(Model):
    id: Annotated[int | None, Field(default=None, primary_key=True)]
    name: str
    accounts: Relation[list["Account"]] = BackRef()


class Account(Model):
    id: Annotated[int | None, Field(default=None, primary_key=True)]
    label: str
    owner: Annotated[Owner | None, ForeignKey(related_name="accounts")] = None
    transactions: Relation[list["Transaction"]] = BackRef()


class Transaction(Model):
    id: Annotated[int | None, Field(default=None, primary_key=True)]
    amount: int
    account: Annotated[Account | None, ForeignKey(related_name="transactions")] = None

The population contract¶

A relation is in exactly one of two states on any given instance:

• Populated (the query included it): access is a plain attribute returning the complete related instance. A nullable FK with no target populates as None — truthful to the declared Account | None.
• Unpopulated (everything else): access keeps today's awaitable contract, unchanged — a coroutine that runs its own query:

        # Without include(), a relation keeps the awaitable contract — access
        # is a coroutine that runs its own query.
        transaction = await Transaction.get(2)

        account = await transaction.account
        assert account.label == "a1"

There is no separate "loaded" model type: .all() on an included query still returns list[Transaction], and a populated instance is an ordinary instance of the target model. (Why no Loaded[Transaction]? See Typed Query Predicates.)

Include never changes membership¶

Joins decide membership, projection decides shape, include decides attached data — three orthogonal axes. Adding .include(...) to any query returns exactly the rows that query returned without it:

        # include() never changes which rows come back: the transaction whose
        # account FK is NULL is still in the result, populated as None.
        every = await Transaction.select().include(lambda t: t.account).all()

        orphan = next(t for t in every if t.id == 5)
        assert len(every) == 5
        assert orphan.account is None

Under the hood, an edge only the include touches renders a LEFT join, and an edge any other clause references keeps that clause's join type. So a where() traversal on the same path keeps its INNER narrowing — include attaches data to the surviving rows, it never rewrites what a filter matches:

        # A predicate on the same path keeps its stage-1 INNER semantics —
        # include attaches data, it never rewrites what a filter matches.
        a1_rows = await (
            Transaction.where(lambda t: t.account.label == "a1")
            .include(lambda t: t.account)
            .all()
        )
        assert [t.id for t in a1_rows] == [1, 2]
        assert all(t.account.label == "a1" for t in a1_rows)

count() and exists() are likewise unaffected — they measure the same rows with or without the include:

        # count()/exists() measure the same rows with or without the include.
        base = Transaction.where(lambda t: t.amount >= 20)
        assert await base.include(lambda t: t.account).count() == await base.count()

Multi-hop paths populate every hop¶

include(lambda t: t.account.owner) populates the whole path — a populated graph is never missing its intermediate nodes. A NULL somewhere along the chain ends the chain as a populated None, with the root row retained:

        # Including a path populates EVERY hop along it.
        transactions = await (
            Transaction.select().include(lambda t: t.account.owner).order_by("id").all()
        )

        first = transactions[0]
        assert first.account.label == "a1"  # hop 1 populated
        assert first.account.owner.name == "o1"  # hop 2 populated

        # A NULL mid-chain ends the chain truthfully: b1 has no owner.
        ownerless = transactions[3]
        assert ownerless.account.label == "b1"
        assert ownerless.account.owner is None

Includes are cumulative, order-free, and idempotent: .include(lambda t: t.account) plus .include(lambda t: t.account.owner) — in either order — is the same query as .include(lambda t: t.account.owner) alone.

Populated instances are session instances¶

In a session, population runs through the identity map like every other fetch: same row, same object. A populated Account is the Account a direct fetch returns — deduped across result rows, and attached onto instances the session already holds:

        # In a session, a populated instance IS the instance — the same object
        # a direct fetch returns, deduped across result rows.
        held = await Account.get(1)
        transactions = await (
            Transaction.select()
            .include(lambda t: t.account)
            .where(lambda t: t.amount <= 20)
            .all()
        )

        assert transactions[0].account is held
        assert transactions[0].account is transactions[1].account

Populations accumulate across a session's queries — an account include and a later attachment include both stick, so shared objects get richer, never forked:

        # Populations accumulate across a session's queries: a later query's
        # include attaches onto the instances the session already holds.
        plain = await Transaction.get(3)  # no population yet
        await Transaction.select().include(lambda t: t.account).all()

        assert plain.account.label == "a2"  # now populated

Outside a session there is no identity map, so an included query returns fresh instances per row — including a fresh related instance per row, with no query-local dedup. Sessionless Ferro keeps exactly one identity story: the session.

Refreshes drop populations that stopped being true¶

Every fetch refreshes instances the session already holds. A refresh keeps each population iff the row's foreign key still points at it; if the FK changed (or went NULL) underneath you, the now-lying population is dropped and access reverts to the awaitable — a populated relation never points at the wrong row:

        # A refresh keeps a population only while it is still true. Change the
        # row's FK underneath the ORM and re-fetch: the stale population is
        # dropped, and access reverts to the awaitable.
        transaction = await (
            Transaction.select()
            .include(lambda t: t.account)
            .where(lambda t: t.id == 1)
            .first()
        )
        assert transaction.account.label == "a1"

        await execute('UPDATE "transaction" SET account_id = ? WHERE id = ?', 2, 1)
        await Transaction.get(1)  # refreshes in place

        pending = transaction.account  # awaitable again
        assert inspect.iscoroutine(pending)
        assert (await pending).label == "a2"

The loud limits¶

Misuse raises at build time, before any SQL:

• Forward foreign keys only. Including a BackRef or ManyToMany relation raises TypeError: reverse and M2M population will be a separate mechanism (a batched second query stitched onto the results), not include(). Until it lands, fetch collections through the relation itself (await author.posts.all()). Filtering on reverse membership is a different axis and already works — the existence test.
• Lambda selectors only. include("account") raises pointing at the lambda form — strings never traverse. Selecting a column (include(lambda t: t.account.label)) raises too: every populated hop is a complete row, so there is nothing to select per column.
• No include × projection. A query carries exactly one materialization plan — populated instances or projected records, never both — and record results are flat, permanently. Either order raises ValueError pointing at traversed projection, the record-shaped way across a relation.
• No mutations. update()/delete() on an included query raise — a mutation returns no instances to populate.

        # The loud limits: reverse relations, projections, and mutations.
        try:
            Account.select().include(lambda a: a.transactions)
        except TypeError as exc:
            assert "reverse (BackRef) or many-to-many" in str(exc)

        try:
            Transaction.select().include(lambda t: t.account).select(lambda t: (t.id,))
        except ValueError as exc:
            assert "traversed projection" in str(exc)

        try:
            await Transaction.select().include(lambda t: t.account).delete()
        except ValueError as exc:
            assert "does not support include()" in str(exc)

Include composes with the M2M association context (post.tags.include(lambda tag: tag.created_by) populates each tag's forward FK) and with everything else a query does: where() (traversal included), order_by(), limit()/offset(), first(), and query branching.

Selecting a Column Subset¶

Every query so far loads complete rows into complete model instances. When a list view only reads two columns of a wide table, ask for less: pass select() a lambda naming the columns, and the query becomes a projection:

        rows = await Transaction.select(lambda t: (t.id, t.amount)).all()

        rows[0].amount  # attribute access, like a model
        rows.model_dump()  # [{"id": 1, "amount": 10}, ...]

The examples in this section use this schema:

class Owner(Model):
    id: int | None = Field(default=None, primary_key=True)
    email: str
    accounts: Relation[list["Account"]] = BackRef()


class Account(Model):
    id: int | None = Field(default=None, primary_key=True)
    label: str
    owner: Annotated[Owner | None, ForeignKey(related_name="accounts")] = None
    transactions: Relation[list["Transaction"]] = BackRef()


class Transaction(Model):
    id: int | None = Field(default=None, primary_key=True)
    amount: int
    memo: str
    account: Annotated[
        Account | None, ForeignKey(related_name="transactions")
    ] = None

class Owner(Model):
    id: Annotated[int | None, Field(default=None, primary_key=True)]
    email: str
    accounts: Relation[list["Account"]] = BackRef()


class Account(Model):
    id: Annotated[int | None, Field(default=None, primary_key=True)]
    label: str
    owner: Annotated[Owner | None, ForeignKey(related_name="accounts")] = None
    transactions: Relation[list["Transaction"]] = BackRef()


class Transaction(Model):
    id: Annotated[int | None, Field(default=None, primary_key=True)]
    amount: int
    memo: str
    account: Annotated[
        Account | None, ForeignKey(related_name="transactions")
    ] = None

What you get back — and why it isn't a Transaction¶

A projected query does not return Transaction instances. In Ferro, a model instance always carries a complete row — there is no such thing as a partial or deferred-field model instance, anywhere. If projection handed you a two-column Transaction, every save(), every refresh, and every helper that takes a model would have to wonder which query produced its argument, and touching an unselected field would blow up far from the query that caused it.

So anything narrower than a full row comes back honestly typed as what it is: a projected record — a Row, delivered in the list-like Rows container:

        row = rows[0]
        assert not isinstance(row, Transaction)  # a Row, not a Transaction
        assert not hasattr(row, "save")  # records cannot be persisted

A Row is read-only in the persistence sense: it has no save(), no refresh, and never enters the identity map — a record can never masquerade as a row in the database. It carries exactly the columns you selected, in selection order, decoded by the same machinery as full hydration — a projected datetime, UUID, enum, or Decimal column has the same Python type and value it would have on the model, on both backends. Under the hood the query declares this result shape explicitly (a materialization plan travels with the query), which is also why asking for less is never slower per row than asking for everything: records are built on the same zero-validation path as models.

Selecting a single column needs no tuple ceremony:

        amounts = await Transaction.select(lambda t: t.amount).order_by("amount").all()
        assert [r.amount for r in amounts] == [10, 20, 30]

Rows is a list you can ship¶

Rows behaves like a list — index, slice, iterate, len() — and both Rows and Row are pydantic-shaped: model_dump() yields list[dict], and Rows[Row] drops straight into a FastAPI response_model:

        newest = await (
            Transaction.select(lambda t: (t.id, t.memo)).order_by("id", "desc").all()
        )

        assert len(newest) == 3  # list-like: len ...
        assert newest[0].memo == "dinner"  # ... index ...
        top_two = newest[:2]  # ... and slice (a Rows again)
        assert isinstance(top_two, Rows)
        assert top_two.model_dump() == [
            {"id": 3, "memo": "dinner"},
            {"id": 2, "memo": "lunch"},
        ]

Column-name strings¶

Quick scripts can pass column names as strings — the same string contract as order_by(): root columns only (shadow {fk}_id columns included), validated at build time. The lambda form remains the documented style:

        rows = await Transaction.select("id", "amount").order_by("id").all()
        assert rows[0].model_dump() == {"id": 1, "amount": 10}

Reaching across a relation¶

A selected field may traverse a forward-FK relation, at any depth — the same attribute chaining as a where() predicate. Unaliased, a traversed field takes the bare leaf column name:

        # A selected field may reach across a relation, at any depth.
        # Unaliased, the field takes the bare leaf column name.
        rows = await (
            Transaction.select(lambda t: (t.memo, t.account.label))
            .order_by("id")
            .all()
        )
        assert rows[0].model_dump() == {"memo": "coffee", "label": "a1"}

Strings never traverse (select("account.label") is rejected permanently) — traversed projection is lambda-only.

Naming output fields¶

Return a dict from the selector and the keys name the record's fields — an output alias. Aliases name output fields only, never joins or tables, and the dict's insertion order is the record's field order:

        # A dict-returning selector names the output fields — keys are the
        # record's field names, values may traverse.
        rows = await (
            Transaction.select(
                lambda t: {
                    "memo": t.memo,
                    "account_label": t.account.label,
                    "owner_email": t.account.owner.email,
                }
            )
            .order_by("id")
            .all()
        )
        assert rows[0].model_dump() == {
            "memo": "coffee",
            "account_label": "a1",
            "owner_email": "[email protected]",
        }

The dict form is also how you resolve a name collision: t.id and t.account.id both want to be called id, so selecting them unaliased is a build-time error naming this fix:

        # Two selected fields resolving to the same output name is a
        # build-time error naming the fix: the dict form.
        try:
            Transaction.select(lambda t: (t.id, t.account.id))
        except ValueError as exc:
            assert "two fields named 'id'" in str(exc)
            assert "dict selector form" in str(exc)

Traversal narrows; left_join() opts out¶

Projection traversal is ordinary traversal (ADR-0006): it renders an INNER join per relation path — shared with any where()/order_by() traversal of the same path — so rows without the relation drop out. left_join() keeps them, and their traversed fields decode to None, even when the source column is non-nullable — a projected record describes the row you got, not the related model:

        # Traversal narrows exactly like a where() predicate on the same
        # path: rows without the relation drop out (b1 has no owner)...
        rows = await Transaction.select(
            lambda t: {"memo": t.memo, "owner_email": t.account.owner.email}
        ).all()
        assert {r.memo for r in rows} == {"coffee", "lunch"}

        # ... and left_join() is the opt-out: rows are kept, and their
        # traversed fields decode to None — even from a non-nullable column.
        rows = await (
            Transaction.select(
                lambda t: {"memo": t.memo, "owner_email": t.account.owner.email}
            )
            .left_join(lambda t: t.account.owner)
            .order_by("id")
            .all()
        )
        assert rows.model_dump()[2] == {"memo": "dinner", "owner_email": None}

Projections compose like any other query¶

where() (relation traversal included), order_by() (even by columns the projection does not select), limit()/offset(), and first() all work unchanged; on a plain projection count() and exists() are unaffected — they measure the same matching rows a full query would. (On an aggregate projection they raise with guidance instead — see Aggregations & Grouped Queries.)

        # Projection composes with everything a read query can do: traversal
        # predicates, ordering by unselected columns, limit/offset, first().
        rows = await (
            Transaction.select(lambda t: (t.id, t.memo))
            .where(lambda t: t.account.label == "a1")
            .order_by("amount", "desc")  # amount is not selected — still sorts
            .limit(1)
            .all()
        )
        assert rows.model_dump() == [{"id": 2, "memo": "lunch"}]

        row = await Transaction.select(lambda t: t.memo).order_by("id").first()
        assert row is not None and row.memo == "coffee"

        # count()/exists() are unaffected by projection: they measure the
        # same matching rows a full query would.
        q = Transaction.select(lambda t: (t.id,)).where(lambda t: t.amount >= 20)
        assert await q.count() == 2
        assert await q.exists() is True

Build-time validation and the loud limits¶

A misspelled column fails when you build the query, with a did-you-mean — exactly like where() and order_by(), and a traversed field validates every hop against that hop's model: