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()

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().

Querying Across Relationships¶

Every ForeignKey field gets a shadow *_id column you can filter on like any scalar:

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

Reverse relations (BackRef) are chainable queries themselves — filter, order, and slice them before executing:

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()

Joins across relations inside a single where() are not supported — filter on shadow FK columns or use the reverse-relation query. See Relationships for the full picture.

Not Yet Supported¶

See Also¶

• Mutations — creating, updating, and deleting records
• Relationships — forward and reverse relations
• Typed Query Predicates — why three predicate styles exist
• Raw SQL — the escape hatch for queries the ORM can't express
• Pagination — efficient pagination patterns