Sustained.py

A Python ORM, SQL query builder, and schema migration tool, inspired by Objection.js and Knex

Guides

Filtering Queries

You can write a condition as three arguments or as a typed predicate:

Show.query().where('sold_out', '=', True)
# SELECT * FROM shows WHERE sold_out = TRUE

Show.query().where(Show.c.sold_out == True)
# SELECT * FROM shows WHERE shows.sold_out = TRUE

The three-argument form takes a column name, an operator, and a value, and quotes the name for the dialect. The typed form uses Python’s own comparison operators against Model.c, checks the name against the model’s declared columns, and qualifies it with the table. Strings are quicker to type and work on any column, including ones not declared on a model. Typed predicates combine with &, |, and ~, so they suit compound and nested WHERE clauses.

A column string is a column name such as 'city' or 'venues.city', the star forms '*' and 'venues.*', or a call on one column such as 'COUNT(id)' or 'COUNT(DISTINCT city)'. The call can name any function, and Sustained writes the function name into the SQL as given. Sustained splits a name on its dots and quotes each part for the dialect, so a name with a space or a non-ASCII letter works too:

Venue.query().select('Seat Count').where('région', '=', 'Bretagne')
# Postgres: SELECT "Seat Count" FROM "venues" WHERE "région" = %s
# MSSQL:    SELECT [Seat Count] FROM [venues] WHERE [région] = ?

To put a dot inside one part, write that part in quotes. Sustained accepts "...", [...], and `...` on every dialect, removes them, and quotes the part again for the target dialect. A doubled closing quote inside the part is one quote character. So 'dbo."a.b"' names the column a.b in the schema dbo, and '[Employee ID]' renders as "Employee ID" on Postgres.

Never pass a column string from untrusted input, such as a sort parameter in a request, to the builder as it is. Sustained quotes each part of a name, so 'id; DROP TABLE x' renders as "id; DROP TABLE x" and the database reports that no such column exists. A call does not get the same protection. In 'pg_sleep(id)', Sustained quotes only the argument and writes pg_sleep as given, so the database runs that function.

Check a column or sort key from a request against a set of the column names you allow, and only then pass it to the builder:

SORTABLE = {'name', 'city', 'capacity'}

sort = request.args.get('sort', 'name')
if sort not in SORTABLE:
    raise ValueError(f'Cannot sort by {sort!r}.')
Venue.query().orderBy(sort)

The default dialect writes names without quotes, so it raises ValueError for a part that is not letters, digits, underscores, and dollar signs, the same as insert() does. A string with an empty part, such as 'a..b', raises ValueError on every dialect.

In select(), 'column AS alias' sets an alias. Sustained reads the text after ` AS ` as the alias, so write a name that contains ` AS ` in quotes, such as '"Cost AS Pct"'. The rules apply to select(), where(), having(), orderBy(), groupBy(), distinctOn(), returning(), and ColumnExpr, and from_() takes a plain table name only.

If you want a position to read its argument another way, wrap it. col() is always a column, Literal is always a value, and raw() is always raw SQL, in every position. orderBy() and groupBy() raise ValueError for a Literal, because ORDER BY 1 sorts by the first select-list column. Use raw('1') for that. Sustained writes the text of raw() into the query as it is and does not quote it, so never build it from a request. Predicates and expressions lists the column and value positions.

from sustained import raw

Venue.query().orderBy(raw('LOWER(name)'))
# SELECT * FROM venues ORDER BY LOWER(name) ASC

The examples on this page use the venue booking schema from Getting Started. You can use either syntax in any of them.

Comparisons

where(), andWhere(), and orWhere() chain to build the clause:

Show.query().where('title', '=', 'Nightcrawler')
# SELECT * FROM shows WHERE title = 'Nightcrawler'

Venue.query().where('capacity', '>', 1000).andWhere('city', '=', 'Minneapolis')
# SELECT * FROM venues WHERE capacity > 1000 AND city = 'Minneapolis'

Venue.query().where('city', '=', 'Minneapolis').orWhere('city', '=', 'St Paul')
# SELECT * FROM venues WHERE city = 'Minneapolis' OR city = 'St Paul'

The first condition in a chain must be a plain where(). Starting with andWhere() or orWhere() raises RuntimeError, because there is nothing for the conjunction to join to.

The operator has to be one of =, !=, <>, <, <=, >, >=, LIKE, NOT LIKE, ILIKE, NOT ILIKE, IS, or IS NOT, and anything else raises ValueError. The allowlist exists because the operator is the one part of the clause that always renders exactly as written. Use whereRaw() if you need an operator outside it.

Comparing to None with = or != renders IS NULL or IS NOT NULL, because = NULL matches nothing in SQL:

Show.query().where('starts_at', '=', None)
# SELECT * FROM shows WHERE starts_at IS NULL

None with any other operator raises ValueError.

The value side does not have to be data. col() renders as a quoted column, raw() renders as SQL text, and a Func or a Subquery renders as the function call or the parenthesized SELECT. A subquery there renders through the statement, so its own values become placeholders and join the parameter tuple in the order they appear in the SQL:

from sustained.expressions import Func, Literal, Subquery

Ticket.query().where('show_id', '=', col('shows.id'))
# SELECT * FROM tickets WHERE show_id = shows.id

quota = Venue.query().select('capacity').where('id', '=', 3)

Show.query().where(col('sold') > Func('COALESCE', Subquery(quota, 'q'), Literal(0)))
# SELECT * FROM shows
# WHERE sold > COALESCE((SELECT capacity FROM venues WHERE id = ?), 0)

A Subquery keeps its alias in a select list and drops it here, because a comparison and a function argument take a bare SELECT.

You can also pass the query itself as the value, without the Subquery wrapper. where('id', '=', Ticket.query().max('id')) renders WHERE id = (SELECT MAX(id) FROM tickets), and so does the same query as a having() value or an update() value.

Typed predicates

Model.c gives every column a reference that builds a Predicate from a Python comparison. col() does the same for a dotted path when no model is in scope:

from sustained import col

Venue.query().where((Venue.c.capacity > 1000) & (Venue.c.city == 'Minneapolis'))
# SELECT * FROM venues WHERE (venues.capacity > 1000 AND venues.city = 'Minneapolis')

Show.query().innerJoinRelated('venue').where(col('venues.capacity') > 1400)

You can combine predicates with & for AND, | for OR, and ~ for NOT. Python’s precedence rules apply, so parenthesize each comparison:

Show.query().where(
    ((Show.c.sold_out == True) & (Show.c.venue_id == 1)) |
    ~(Show.c.title.like('Cancelled%'))
)

Use & and |, never the and and or keywords. Python evaluates those by truthiness, which would silently discard half your condition, so a Predicate raises TypeError in a boolean context instead.

Typed columns also provide methods for LIKE, IN, BETWEEN, and NULL tests:

Show.c.title.like('The %')
Show.c.title.not_like('The %')

Show.c.title.ilike('the %')

Show.c.venue_id.in_([1, 2, 3])
Show.c.venue_id.not_in([1, 2, 3])

Ticket.c.price.between(20, 50)
Ticket.c.price.not_between(20, 50)

Show.c.starts_at.is_null()
Show.c.starts_at.not_null()

A predicate goes to where() or to having(), and works anywhere the three-argument form does.

The where family

You can use each method in the table as is or with an and or or prefix. For example, you can write whereBetween as andWhereBetween or orWhereBetween when it joins an earlier condition.

Method Arguments Renders
where column, operator, value col = ?
whereIn / whereNotIn column, list or query col IN (...)
whereBetween / whereNotBetween column, low, high col BETWEEN ? AND ?
whereLike / whereILike column, pattern col LIKE ?
whereNull / whereNotNull column col IS NULL
whereExists / whereNotExists query or callable EXISTS (...)
whereRaw SQL, parameters the fragment, in parentheses

Each base method also has a having counterpart for filtering groups, which Grouping covers.

IN and NOT IN

Show.query().whereIn('venue_id', [1, 2, 3])
# SELECT * FROM shows WHERE venue_id IN (1, 2, 3)

Show.query().where('sold_out', '=', True).andWhereNotIn('venue_id', [4, 5])
# SELECT * FROM shows WHERE sold_out = TRUE AND venue_id NOT IN (4, 5)

An empty list raises ValueError, because IN () is a syntax error on most engines and an empty list usually comes from a filter that got no values rather than a query meant to match nothing.

A None in a NOT IN list also raises ValueError. The member renders as NULL, and x NOT IN (1, NULL) is never true, so the query would return no rows. To keep the rows where the column is NULL, take None out of the list and add orWhereNull(). In an IN list, None matches no row, and the other members match as usual. A NOT IN subquery that returns a NULL also matches no rows, and Sustained cannot detect this before the query runs.

The values can be another query instead of a list:

Show.query().whereIn('venue_id', Venue.query().select('id').where('capacity', '>', 5000))
# SELECT * FROM shows WHERE venue_id IN (SELECT id FROM venues WHERE capacity > 5000)

A subquery written as SQL goes through QueryBuilder.raw(). A plain string raises ValueError, so an id that arrives as a string, such as whereIn('id', request.args['id']), fails instead of adding its text to the SQL.

LIKE and case-insensitive LIKE

Show.query().whereLike('title', 'The %')
# SELECT * FROM shows WHERE title LIKE 'The %'

whereILike() matches without regard to case. On Postgres and DuckDB it renders native ILIKE. Everywhere else it compiles to a lowercased comparison, so it never raises DialectError:

Show.query().whereILike('title', 'the %')
# POSTGRES: SELECT * FROM "shows" WHERE "title" ILIKE 'the %'
# DEFAULT:  SELECT * FROM shows WHERE LOWER(title) LIKE LOWER('the %')

Sustained ignores case and underscores when it matches a method name, so where_i_like and where_ilike both reach whereILike.

NULL and BETWEEN

Show.query().whereNull('starts_at')
# SELECT * FROM shows WHERE starts_at IS NULL

Ticket.query().whereBetween('price', 20, 50)
# SELECT * FROM tickets WHERE price BETWEEN 20 AND 50

BETWEEN is inclusive at both ends on every supported engine.

EXISTS

whereExists() takes a query, a callable that receives one, or SQL passed through QueryBuilder.raw(). A plain string raises ValueError. Reference the outer query’s columns with QueryBuilder.raw(), which keeps the builder from rendering the name as a string value:

from sustained.builder import QueryBuilder

Show.query().whereExists(
    Ticket.query()
    .select(QueryBuilder.raw('1'))
    .where('tickets.show_id', '=', QueryBuilder.raw('shows.id'))
)
# SELECT * FROM shows
# WHERE EXISTS (SELECT 1 FROM tickets WHERE tickets.show_id = shows.id)

The callable form builds the subquery in place, which keeps a one-use subquery next to the condition that uses it:

(Show.query()
    .whereNotExists(lambda q: (
        q.from_('tickets')
            .select(QueryBuilder.raw('1'))
            .where('tickets.show_id', '=', QueryBuilder.raw('shows.id'))
            .andWhere('tickets.sold_at', 'IS NOT', None)
    ))
)

# SELECT * FROM shows
# WHERE NOT EXISTS (
#   SELECT 1 FROM tickets
#   WHERE tickets.show_id = shows.id AND tickets.sold_at IS NOT NULL
# )

The builder the callable receives has no model behind it, so from_() takes a table name string rather than a model class.

Grouping conditions

Pass a callable to any where method and it receives a builder for a parenthesized group, which is how you write A AND (B OR C):

(Show.query()
    .where('sold_out', '=', True)
    .andWhere(
        lambda q: (
            q.where('venue_id', '=', 1).orWhere('venue_id', '=', 2)
        )
    )
)

# SELECT * FROM shows WHERE sold_out = TRUE AND (venue_id = 1 OR venue_id = 2)

Groups nest as deep as the logic needs:

(Ticket.query()
    .where('sold_at', 'IS NOT', None)
    .andWhere(
        lambda q: (
            q.where(
                lambda a: a.where('price', '<', 20).andWhere('show_id', '=', 1)
            )
            .orWhere(
                lambda b: b.where('price', '>', 100).andWhere('show_id', '=', 2)
            )
        )
    )
)

# SELECT * FROM tickets
# WHERE sold_at IS NOT NULL
#   AND ((price < 20 AND show_id = 1) OR (price > 100 AND show_id = 2))

The typed form builds the same groups with parentheses instead of a lambda at each level:

Ticket.query().where(
    Ticket.c.sold_at.not_null() &
    (
        ((Ticket.c.price < 20) & (Ticket.c.show_id == 1)) |
        ((Ticket.c.price > 100) & (Ticket.c.show_id == 2))
    )
)

Raw predicates

whereRaw(sql, params) is a fallback for anything the builder cannot otherwise express. Mark each value with ? so that it travels as a parameter, which keeps user input out of the SQL text even in a raw fragment:

Ticket.query().whereRaw('price % ? = ?', [10, 0])
# SELECT * FROM tickets WHERE (price % 10 = 0)

The marker count must match the parameter count, or the call raises ValueError. The fragment renders wrapped in parentheses, so it composes with the rest of the chain without precedence surprises. havingRaw() is the same method for HAVING.

The fragment passes through untouched, so it does not follow set_dialect() the way the rest of the builder does. You are responsible for writing raw SQL that is valid for your database engine.

Where to go next

You want to Read
Filter groups rather than rows Grouping
Filter across a join Relations and Joins
Run the filtered query Executing Queries
See every method and what it raises QueryBuilder reference
See every operator on a typed column Predicates reference