sustained.QueryBuilder builds every statement Sustained runs. Get a builder from Model.query() rather than constructing one yourself.

A builder changes in place. Each method adds to the same query and returns the same builder, so a shared base query collects the filters of every branch unless you clone() it first.

Guides: Building Queries, Filtering, Grouping, Relations and Joins, Executing Queries.

Construction

QueryBuilder(model_class, dialect=None)

dialect defaults to Dialects.DEFAULT. Use Model.query() instead.

str(query) -> str

The statement with values inlined as SQL literals. Use it to read and log a query. To execute a query, use run() or to_sql().

QueryBuilder.raw(sql) -> Expression

Static method. Marks SQL that renders unquoted and unvalidated.

Selecting columns

select(*columns)

Adds columns to the SELECT list. Accepts strings, the 'col AS alias' form, Model.column references, ColumnExpr, and any expression object. A string is a column name or dotted path, '*', 'table.*', or a call on one column such as 'COUNT(*)'. Sustained quotes each part of a name and writes the function name of a call as given, so a column string must not come from untrusted input. Check a column name from a request against a set of allowed names first. Pass other SQL through QueryBuilder.raw(). The SELECT list defaults to * when you never call select().

distinct()

Adds DISTINCT.

distinctOn(*columns)

DISTINCT ON (...). Raises ValueError with no columns, or when combined with distinct(). Raises DialectError at render time on every dialect but Postgres and DuckDB.

from_(table, alias=None)

Overrides the FROM source with a table name or a QueryBuilder. Raises ValueError when a subquery has no alias, and TypeError for any other type.

with_(table_alias, subquery, recursive=False)

Adds a CTE. Raises TypeError when subquery is not a QueryBuilder, and ValueError at render time when two different subqueries share an alias. A SELECT renders one top-level WITH for its own CTEs and for the CTEs of every subquery it contains. MSSQL always renders plain WITH.

Aggregates

Each aggregate takes (column, alias=None) and returns the builder, so chain them to select several at once. count() alone defaults its column to '*'.

Method	Renders
count	COUNT(column)
sum	SUM(column)
avg	AVG(column)
min	MIN(column)
max	MAX(column)

Functions, windows, and CASE

select_func(function_name, *args, alias=None)

Any SQL function. String arguments are column references; wrap literal values in Literal(). A registered function raises DialectError on a dialect that has no spelling for it. An unregistered name passes through unchecked.

select_window(function_name, alias, partition_by=None, order_by=None, args=None, frame=None)

A window function. An order_by entry may include a direction, as in 'price DESC'. args are the function’s own arguments. frame is a raw frame clause.

select_case(alias, else_result, when_clauses)

A CASE expression from (condition, result) tuples. A condition is a Predicate or a raw SQL string. A string result is a value, so wrap a result in col() when it names a column.

Every registered function is also a method of its own name, so query.coalesce('nick', 'name', alias='display') and query.select_func('COALESCE', ...) build the same expression. The registered functions and their per-dialect spellings are in Predicates and expressions.

Filtering

Sustained generates the where family, so every base below exists in a plain, an and, and an or form.

where(column_or_callable, op=None, val=None)

A comparison. Also accepts a single Predicate, or a callable that receives a builder for a parenthesized group.

whereIn(col, values_or_query)

IN over a list, a QueryBuilder, a callable, or SQL passed through QueryBuilder.raw(). An empty list or a string raises ValueError. whereNotIn builds the NOT IN form.

whereBetween(col, low, high)

Range comparison. whereNotBetween builds the NOT BETWEEN form.

whereExists(query_or_callable)

EXISTS over a QueryBuilder, a callable, or SQL passed through QueryBuilder.raw(). A string raises ValueError. whereNotExists builds the NOT EXISTS form.

whereLike(col, pattern)

LIKE. whereILike is the case-insensitive form.

whereNull(col)

IS NULL. whereNotNull renders IS NOT NULL.

whereRaw(sql, params=None)

A raw fragment with ? value markers, whose values still parameterize. The fragment renders wrapped in parentheses, and a question mark inside a string literal or a quoted identifier is text rather than a marker.

Prefix a base with and or or for the conjunction, as in andWhereIn or orWhereNotBetween. The first condition in a chain must be a plain where; an and or or form in that position raises RuntimeError.

Errors from the where family:

Condition	Raises
A Predicate passed together with op or val	ValueError
op is None on a clause that is not a callable	ValueError
val is None with an operator other than =, !=, <>, IS, IS NOT	ValueError
An operator outside the allowlist	ValueError
IS or IS NOT with a value other than None, True, or False	ValueError
A whereRaw marker count that does not match the parameter count	ValueError

The operator allowlist is =, !=, <>, <, <=, >, >=, LIKE, NOT LIKE, ILIKE, NOT ILIKE, IS, and IS NOT. Comparing to None with = or != renders IS NULL or IS NOT NULL. IS or IS NOT with True or False writes the truth value into the SQL rather than binding it, because IS ? is a syntax error on Postgres and DuckDB. Presto and Athena render IS NOT DISTINCT FROM TRUE and IS DISTINCT FROM TRUE, because Trino has no IS TRUE. MSSQL has no IS TRUE either and renders (col IS NOT NULL AND col = 1) for IS True and (col IS NULL OR col <> 1) for IS NOT True, so a NULL row reads as neither value on every dialect. ILIKE is native on Postgres and DuckDB and compiles to LOWER(col) LIKE LOWER(pattern) everywhere else, so ILIKE never raises.

Grouping and window filters

groupBy(*columns)

GROUP BY. Takes column strings under the same rule as select(), or QueryBuilder.raw() expressions. A Literal raises ValueError, because GROUP BY 1 names a select-list position. Write raw('1') for a position. groupByRollup(), groupByCube(), and groupByGroupingSets() raise the same error.

groupByRollup(*columns)

GROUP BY ROLLUP (...). Raises ValueError with no columns.

groupByCube(*columns)

GROUP BY CUBE (...). Raises ValueError with no columns.

groupByGroupingSets(*tuples)

GROUP BY GROUPING SETS (...). An empty tuple is the grand total. Raises ValueError with no sets.

qualify(condition)

Filters on window results. Takes a Predicate or a raw string. Raises DialectError at render time on every dialect but DuckDB.

The having family mirrors the where family: the same bases, the same three prefixes, the same arguments, and the same errors. having, andHavingIn, orHavingNotBetween, and havingRaw all exist. Filter on the aggregate call itself, as in having('COUNT(id)', '>', 10), because standard SQL does not expose SELECT aliases to HAVING.

Ordering, paging, and locking

orderBy(column, direction='asc')

Sorts the result. Chain calls to sort by several columns. Any direction other than asc or desc raises ValueError. The column follows the same rule as select(). A string such as 'pg_sleep(id)' is a call, and Sustained writes its function name as given, so check a sort parameter from a request against a set of allowed column names before you pass it. Pass an expression through QueryBuilder.raw(). A Literal raises ValueError, because ORDER BY 1 names a select-list position. Write raw('1') for a position.

limit(value)

Caps the row count. Raises ValueError for a negative value, when set twice, or when combined with top(), and TypeError for a value that is not an integer.

offset(value)

Skips rows. Raises ValueError when set twice.

top(value)

TOP n. MSSQL only; every other dialect raises DialectError at render time. top() and limit() are mutually exclusive. top() and offset() in one query raise ValueError, because T-SQL refuses TOP beside OFFSET; use orderBy() with limit() and offset() to skip rows.

page(page, page_size)

LIMIT and OFFSET from a zero-based page number.

cursor_page(column, page_size, after=None)

Keyset pagination: orders by the column, filters past the after value, and limits the row count. The database does not scan the skipped rows, so a deep page costs the same as the first one.

for_update(skip_locked=False, nowait=False)

Row locking. Postgres and MySQL only. Raises ValueError when both flags are set, or when combined with a union.

clone() -> QueryBuilder

A deep copy of the builder. Every other method returns the same builder.

On MSSQL, limit() and offset() compile to OFFSET ... FETCH, which T-SQL allows only after an ORDER BY. Without an ORDER BY they raise DialectError at render time. On Presto and Athena, OFFSET renders before LIMIT.

Joining

Every join type exists in a raw form and a relation form. The types are join, innerJoin, leftJoin, leftOuterJoin, rightJoin, rightOuterJoin, fullJoin, fullOuterJoin, and crossJoin.

Raw form. join(table, ...) takes one of three argument forms:

query.join('venues', 'shows.venue_id', '=', 'venues.id')   # ON condition
query.join('profiles', using=['profile_id'])               # USING list
query.join('venues', lambda j: j.on(...).orOn(...))        # lambda

crossJoin(table) also takes the table on its own, because a cross join has no condition. Every other join type raises ValueError without one.

The table argument must be a table name. To join a derived result set, put it in a CTE with with_() and join the CTE by its alias. Mixing using with positional arguments raises ValueError, and a using value that is not a list raises TypeError.

Relation form. joinRelated(relation_name, alias=None), and the same prefixed variant for every other join type. The join condition comes from relationMappings. A ManyToManyRelation joins the through table first, and the join type you name applies to the second hop. The first hop is a LEFT JOIN (or LEFT OUTER JOIN) for a left or full join, so a row with no link row stays, and an INNER JOIN for every other type. A second join through the same link table renders it as <alias>_<link table>, and raises ValueError when you pass no alias. An unknown relation name raises ValueError, and so does a mapping that is missing modelClass, join, from, or to.

OnClauseBuilder

OnClauseBuilder is the object a join lambda receives.

on(col1, op, col2)

The first ON condition. col2 may be a QueryBuilder, which renders as a parenthesized subquery. andOn and orOn add further conditions with the same arguments; either one raises RuntimeError as the first call.

A lambda that adds no condition raises RuntimeError at render time.

Combining queries

union(*queries, all=False)

UNION, or UNION ALL when all=True. unionAll(*queries) is the UNION ALL shorthand.

intersect(*queries)

INTERSECT.

except_(*queries)

EXCEPT. The trailing underscore avoids a clash with the Python keyword except.

ORDER BY, LIMIT, and OFFSET on the outer query apply to the whole result. The same clauses on a member query render inside that member’s parentheses. Sustained lifts the CTEs from every member into one top-level WITH.

Writing data

insert(values)

INSERT from a dict or a list of dicts. Raises ValueError on an empty list, on a row with no columns, and on rows whose columns differ from each other.

insert_from(columns, query)

INSERT ... SELECT. columns=None inserts positionally. Raises TypeError when query is not a QueryBuilder.

update(values)

UPDATE. Raises ValueError on an empty dict, and at render time when the query has no where().

delete()

DELETE. Raises ValueError at render time when the query has no where().

onConflict(*columns)

Declares the upsert conflict target. Each column is one name, read like an insert() key, so '"id"' and '[id]' both name the column id. Raises ValueError when it does not follow insert(), when it gets no columns, and when a conflict column was not one of the inserted columns.

merge(columns=None)

Updates the row on conflict. Defaults to every inserted column that is not a conflict column. columns lists the columns to update, each read like an insert() key. Raises ValueError without onConflict(), and at render time when no column is left to update.

ignore()

Skips the row on conflict. Raises ValueError without onConflict().

returning(*columns)

RETURNING, which defaults to *. The statement then returns dicts instead of a row count.

create_table_as(table_name, temporary=False)

CREATE TABLE AS from a SELECT. Raises ValueError on a statement that is not a SELECT, and on an empty table name.

An UPDATE or DELETE with no filter raises instead of touching every row. To update or delete every row on purpose, add where(QueryBuilder.raw('1'), '=', 1).

Dialect refusals: upserts raise on Presto; RETURNING raises on MSSQL, Presto, and Athena; create_table_as raises on MSSQL, and on Athena when temporary=True.

Rendering and execution

to_sql() -> (sql, params)

The parameterized statement, and its values in SQL order.

run(connection=None)

Executes the statement. A SELECT returns hydrated instances with eager relations attached. A write commits, unless it runs inside a transaction() block, and returns the affected row count, or dicts when RETURNING is set.

first(connection=None) -> Model | None

Runs a clone of the builder with LIMIT 1. The original builder is unchanged.

to_dicts(connection=None) -> list[dict]

Rows keyed by column name, with no hydration and no eager loading.

to_df(connection=None) -> pandas.DataFrame

Raises RuntimeError when pandas is not installed.

to_arrow(connection=None) -> pyarrow.Table

Raises RuntimeError when pyarrow is not installed.

total(connection=None) -> int

SELECT COUNT(*) over the query with the ordering, LIMIT, OFFSET, and TOP stripped. The builder is unchanged.

explain(connection=None, analyze=False) -> list[tuple]

The query plan. analyze=True executes the statement. Raises DialectError on MSSQL.

withGraphFetched(*relation_names)

Eager loads relations, with one query per relation per level. A name may be a dotted path such as 'shows.tickets'. Raises ValueError for an unknown relation name or path segment.

to_dicts, to_df, to_arrow, and total raise ValueError on any statement that is not a SELECT.

A multi-row insert() with no RETURNING runs through the driver’s executemany(). Rows that contain a QueryBuilder.raw() value run as one statement instead, because raw SQL renders in the statement itself and has no value to bind.

Async

await arun(adapter=None)

Returns the same result types as run().

await afirst(adapter=None) -> Model | None

The async form of first().

await ato_dicts(adapter=None) -> list[dict]

The async form of to_dicts().

The adapter resolves in this order: the adapter argument, then an open async_transaction(), then Model.bind_async(). When none of the three resolves, the call raises RuntimeError.

Connection resolution

Every execution method resolves a connection the same way: the connection argument first, then a connection pinned by an open transaction() block, then the binding from Model.bind(). When none of the three resolves, the call raises RuntimeError.