Reference: The QueryBuilder¶

A QueryBuilder instance is created whenever you start a query (e.g., by calling User.where(...)). It provides a chainable, fluent interface for constructing a database query. The query is not executed until a terminal method like .all() or .first() is called.

Quick examples¶

# Count active users
await User.where(User.is_active.is_(True)).count()

# Bulk update with guard + hooks
await User.where(User.signup_source == "beta").update_bulk(
    {"plan": "pro"},
    with_hooks=True,         # run validate() + timestamp hooks per row
    require_filter=True,     # keep the safety guard on (default)
)

# Stream in batches of 500 without loading everything into memory
async for batch in User.order_by("id").iterate(batch=True, batch_size=500):
    ...

Query Construction Methods¶

These methods are used to build the query and are chainable.

A chainable, fluent query builder.

This class is the core of the ORM's query-building API. It constructs a SQLAlchemy statement internally and provides terminal methods (like .first(), .all()) to execute it.

Instances of this class are created by calling class-level methods on a DuoORM model (e.g., User.where(...)).

where ¶

where(*args)

Adds one or more WHERE clauses to the query, joined by AND.

Parameters:

Name	Type	Description	Default
`*args`	`ClauseElement`	SQLAlchemy column expressions (e.g., `User.age > 18`).	`()`

Returns:

Type	Description
`'QueryBuilder[T]'`	The `QueryBuilder` instance for further chaining.

order_by ¶

order_by(*args)

Adds an ORDER BY clause to the query.

Parameters:

Name	Type	Description	Default
`*args`	`str`	Field names to order by. Prefix a name with `-` for descending order (e.g., `"-id"`).	`()`

Returns:

Type	Description
`'QueryBuilder[T]'`	The `QueryBuilder` instance for further chaining.

limit ¶

limit(number)

Adds a LIMIT clause to the query.

offset ¶

offset(number)

Adds an OFFSET clause to the query.

paginate ¶

paginate(limit, offset=0)

Applies LIMIT and OFFSET clauses for pagination.

Parameters:

Name	Type	Description	Default
`limit`	`int`	The number of records to return per page.	required
`offset`	`int`	The number of records to skip. Defaults to 0.	`0`

Returns:

Type	Description
`'QueryBuilder[T]'`	The `QueryBuilder` instance for further chaining.

related(
    relationship_attr,
    *,
    where=None,
    aggregate=None,
    having=None,
    order_by=None,
    loader=None
)

Configures eager loading and/or filtering based on a relationship. One path per call; chain multiple related() calls for siblings.

This is the primary tool for solving N+1 query problems and for filtering a model based on its related data.

Parameters:

Name	Type	Description	Default
`relationship_attr`	`InstrumentedAttribute \| RelationshipPath`	The relationship attribute on the model (e.g., `User.posts`) or a `path()` object for multi-hop relationships.	required
`where`	`Optional[Sequence[ClauseElement]]`	A list of filter clauses to apply to the related model.	`None`
`aggregate`	`Optional[str]`	The aggregation mode. Can be "exists" (default), "all", or "count".	`None`
`having`	`Optional[Sequence[Callable[[Any], ClauseElement]]]`	A list of filter clauses to apply to the result of a "count" aggregate.	`None`
`order_by`	`Optional[Sequence[str]]`	A list of ordering clauses for a "count" aggregate.	`None`
`loader`	`Optional[str]`	Optional eager loading strategy override for this path. When omitted, heuristics pick joined for scalar hops and selectin for collections.	`None`

Returns:

Type	Description
`'QueryBuilder[T]'`	The `QueryBuilder` instance for further chaining.

alchemize ¶

alchemize()

Returns the underlying SQLAlchemy Select object.

This "escape hatch" allows for advanced customization of the query using the full power of SQLAlchemy Core.

Returns:

Type	Description
`select`	A SQLAlchemy `select` construct.

iterate ¶

iterate(*, batch_size=200, batch=False)

Streams results using batched queries. When batch=False (default), yields one model at a time. When batch=True, yields lists of models.

If no explicit ORDER BY is present on the query, primary key ordering is applied for deterministic paging.

Terminal (Execution) Methods¶

These methods execute the constructed query against the database.

A chainable, fluent query builder.

This class is the core of the ORM's query-building API. It constructs a SQLAlchemy statement internally and provides terminal methods (like .first(), .all()) to execute it.

Instances of this class are created by calling class-level methods on a DuoORM model (e.g., User.where(...)).

all ¶

all()

Executes the query and returns a list of all matching records.

first ¶

first()

Executes the query and returns the first matching record or None.

one ¶

one()

Executes the query and returns exactly one record.

Raises:

Type	Description
`ObjectNotFoundError`	If no records are found.
`MultipleObjectsFoundError`	If more than one record is found.

count ¶

count()

Executes the query and returns the total number of matching records.

exists ¶

exists()

Executes the query and returns True if at least one record exists.

update_bulk ¶

update_bulk(
    values,
    *,
    with_hooks=False,
    batch_size=200,
    require_filter=True,
    per_batch_transaction=True
)

Performs a bulk update on all records matched by the query.

Parameters:

Name	Type	Description	Default
`values`	`dict[str, Any] \| Any`	mapping of columns to new values.	required
`with_hooks`	`bool`	when True, loads instances and runs per-row validation/hooks.	`False`
`batch_size`	`int`	batch size for the hooked path.	`200`
`require_filter`	`bool`	guard against accidental full-table updates.	`True`
`per_batch_transaction`	`bool`	commit each batch separately (True) or wrap the whole hooked run in a single transaction (False).	`True`

delete_bulk ¶

delete_bulk(
    *,
    with_hooks=False,
    batch_size=200,
    require_filter=True,
    per_batch_transaction=True
)

Performs a bulk delete on all records matched by the query.

Parameters:

Name	Type	Description	Default
`with_hooks`	`bool`	when True, loads instances and runs per-row delete hooks.	`False`
`batch_size`	`int`	batch size for the hooked path.	`200`
`require_filter`	`bool`	guard against accidental full-table deletes.	`True`
`per_batch_transaction`	`bool`	commit each batch separately (True) or wrap the whole hooked run in a single transaction (False).	`True`

JSON and ARRAY Helpers¶

These helper functions are used inside a .where() clause to build expressions for querying JSON and ARRAY column types.

`json()` Helper¶

duo_orm.query.json ¶

json(column)

Entry point for building JSON-aware query expressions.

Wraps a model's JSON column attribute to provide a fluent API for path navigation and comparison operators.

Parameters:

Name	Type	Description	Default
`column`	`InstrumentedAttribute`	The SQLAlchemy model attribute representing a JSON column.	required

Returns:

Type	Description
`JSONExpression`	A `JSONExpression` object to build the query clause.

Source code in duo_orm/query.py

def json(column: InstrumentedAttribute) -> JSONExpression:
    """
    Entry point for building JSON-aware query expressions.

    Wraps a model's JSON column attribute to provide a fluent API for
    path navigation and comparison operators.

    Args:
        column: The SQLAlchemy model attribute representing a JSON column.

    Returns:
        A `JSONExpression` object to build the query clause.
    """
    return JSONExpression(column)

`array()` Helper¶

duo_orm.query.array ¶

array(column)

Entry point for building ARRAY-aware query expressions.

Wraps a model's ARRAY column attribute to provide a fluent API for membership and comparison operators.

Parameters:

Name	Type	Description	Default
`column`	`InstrumentedAttribute`	The SQLAlchemy model attribute representing an ARRAY column.	required

Returns:

Type	Description
`ArrayExpression`	An `ArrayExpression` object to build the query clause.

Source code in duo_orm/query.py

def array(column: InstrumentedAttribute) -> ArrayExpression:
    """
    Entry point for building ARRAY-aware query expressions.

    Wraps a model's ARRAY column attribute to provide a fluent API for
    membership and comparison operators.

    Args:
        column: The SQLAlchemy model attribute representing an ARRAY column.

    Returns:
        An `ArrayExpression` object to build the query clause.
    """
    return ArrayExpression(column)

Reference: The QueryBuilder¶

Quick examples¶

Query Construction Methods¶

where ¶

order_by ¶

limit ¶

offset ¶

paginate ¶

related ¶

alchemize ¶

iterate ¶

Terminal (Execution) Methods¶

all ¶

first ¶

one ¶

count ¶

exists ¶

update_bulk ¶

delete_bulk ¶

JSON and ARRAY Helpers¶

json() Helper¶

duo_orm.query.json ¶

array() Helper¶

duo_orm.query.array ¶

`json()` Helper¶

`array()` Helper¶