Everything in sustained.expressions, plus the function registry in sustained.functions. These objects keep columns, literals, and conditions apart from one another, so the builder never has to guess which one a string was meant to be.

Guide: Filtering.

Typed columns

col(name) returns a ColumnExpr. Model.c.<column> returns one too, and also checks the name against the model’s declared columns.

from sustained import col

col('venues.capacity') > 1400
Venue.c.capacity > 1400          # the same predicate, with a typo check

ColumnExpr

The name attribute is the column path as you wrote it.

Comparison operators return a Predicate. A ColumnExpr on the right side of a comparison renders as a column, not as a bound value.

Operator	Renders	Notes
==	=	== None renders IS NULL.
!=	!=	!= None renders IS NOT NULL.
> >= < <=	the same operator	Comparing to None raises ValueError.

Each method below returns a Predicate.

Method	Renders
like(pattern)	LIKE
not_like(pattern)	NOT LIKE
ilike(pattern)	ILIKE, native on Postgres and DuckDB, LOWER() LIKE LOWER() elsewhere
in_(values)	IN (...) over a list or a QueryBuilder. An empty list raises ValueError.
not_in(values)	NOT IN (...). An empty list raises ValueError.
between(low, high)	BETWEEN low AND high
not_between(low, high)	NOT BETWEEN low AND high
is_null()	IS NULL
not_null()	IS NOT NULL

Predicate

A composable condition. Pass a Predicate to where() or having() as the only argument.

Operator	Renders
a & b	(a AND b)
a | b	(a OR b)
~a	NOT (a)

bool(predicate) always raises TypeError, so a and b fails instead of evaluating to one side of the expression. Use & and |.

Marking columns and literals

Sustained decides whether a bare string is a column name or a value. In function arguments and CASE results it reads the string as a column. These two classes override that reading.

Column(name)

The string is a column reference or raw SQL. Sustained does not quote it and does not treat it as a value.

Literal(value)

The value is a literal, even in a position where Sustained would read a column.

from sustained import Literal

query.select_func('COALESCE', 'nickname', 'name', Literal('unknown'), alias='display')

# COALESCE(nickname, name, 'unknown') AS display

A string argument that is not a plain column path raises ValueError at render time.

A col() reference is a column reference in the same two places: a function argument, and the value side of a comparison. It renders quoted for the active dialect and binds no parameter.

Expression(value), in sustained.types and re-exported from sustained.schema, does the same job for schema defaults: raw SQL that renders as written in both the inline and the parameterized forms.

Expression objects

The fluent methods on QueryBuilder build these objects for you. Construct one directly when you need a form the fluent method does not cover.

Func(function_name, *args, alias=None)

A function call, the object select_func() builds.

AggregateExpression(function_name, column, alias=None)

An aggregate, the object count() and its siblings build.

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

A window function, the object select_window() builds.

CaseExpression(alias, else_result)

A CASE expression. when(condition, result) appends a WHEN/THEN pair and returns the CaseExpression, so pairs chain. whens returns a copy of the pairs.

Subquery(query, alias)

Embeds a QueryBuilder in a SELECT list or a join:

from sustained.expressions import Subquery

ticket_count = (Ticket.query()
    .count()
    .where('show_id', '=', Column('shows.id'))
)

Show.query().select('title', Subquery(ticket_count, 'tickets_sold'))

render(ctx) renders the subquery with the outer statement’s render context, so its values parameterize with the rest of the statement. str() inlines them as literals, for reading and logging.

render_operand(ctx) renders it with no alias, for the places where the subquery stands as a value: a function argument, or one side of a comparison. The compiler calls it there. Passing None for the context inlines the values.

Aliases in a nested position

An alias belongs to the select list. Where one of these objects stands as a value, the alias is left off: a function argument, or the value side of a comparison. Func, AggregateExpression, WindowExpression, CaseExpression and Subquery all drop it there, so you can pass the same object to select() and to a function call and get valid SQL from both.

A nested object also renders through the compiler of the statement that contains it, not through the default dialect. A CASE with boolean results renders 1 and 0 on MS SQL Server and TRUE and FALSE elsewhere, in the select list and inside a function call alike.

Function registry

select_func() and the fluent function methods check the name against FunctionRegistry in sustained.functions. A registered function that the active dialect cannot spell raises DialectError while the query builds. An unregistered name passes through unchecked, so you can call a function the registry does not list.

Function	Available on	Per-dialect spelling
COUNT, SUM, AVG, MIN, MAX	every dialect	one spelling
LOWER, UPPER, COALESCE, CONCAT, SUBSTRING, TRIM, ROUND, ABS, CEILING, FLOOR, MOD	every dialect	one spelling
LENGTH	every dialect	LEN on MSSQL
STRING_AGG	Postgres, DuckDB, Presto, Athena	one spelling
NOW	Postgres, MySQL, DuckDB, Presto, Athena	GETDATE on MSSQL
GETDATE	MSSQL	NOW on Postgres, MySQL, DuckDB, Presto, and Athena

Write either NOW() or GETDATE() and the dialect renders its own spelling. Neither one is registered for the default dialect, so both raise DialectError there.

STRING_AGG is left off MySQL on purpose. MySQL spells the same idea as GROUP_CONCAT, which takes its separator as a SEPARATOR keyword rather than a second argument, so a renamed function would produce SQL that does not parse. Write GROUP_CONCAT through raw SQL there.

Registry API

FunctionRegistry.register(name, metadata)

Registers or overwrites an entry. The key is the uppercased name.

FunctionRegistry.get_metadata(name) -> FunctionMetadata

Case-insensitive lookup. Raises KeyError when the name is unregistered.

FunctionRegistry.resolve_name(name, dialect) -> str

The dialect’s spelling, or the name uppercased.

FunctionRegistry.is_supported(name, dialect) -> bool

True for any unregistered name.

FunctionMetadata(supported_dialects, dialect_names={}) is a NamedTuple. Register your own metadata to get build-time checking for a function the registry does not list:

from sustained.dialects import Dialects
from sustained.functions import FunctionMetadata, FunctionRegistry

FunctionRegistry.register(
    'DATE_TRUNC',
    FunctionMetadata(supported_dialects=[Dialects.POSTGRES, Dialects.DUCKDB]),
)

Type aliases

These live in sustained.types. Use them to annotate code that accepts what the builder accepts.

Alias	Definition
DbReturnValue	str | int | float | bool | datetime | date | Decimal | bytes
Selectable	Anything select() takes
CaseResult	DbReturnValue | Column
QueryResolvable	QueryBuilder | Callable[..., QueryBuilder] | str
Join	BasicJoinMapping | JoinMappingWithThrough

The relation-mapping types are TypedDicts: RelationMapping, BasicJoinMapping, JoinMappingWithThrough, ThroughJoinMapping, and ThroughJoinValue. See Model.