querymodelling
build consistent api query models for fastapi
Requirements
At least you will need to have the pydantic package installed. Of course it make sense to have some kind of service supporting query models like fast api. Currently sql models are implemented - but feel free to create new ones.
Installation
Example
Typed SQL query models
SqlQueryModel generates a fully typed query model (incl. JSON schema)
with joins, fields, where, group_by and subqueries, which is compiled
into a SQLAlchemy select statement. Only the registered models can be
queried.
from sqlmodel import Session
from querymodelling.sql_query import SqlQueryModel
BookQuery = SqlQueryModel(Book, models=[Author, Review])
query = BookQuery.model_validate({
"joins": [
{
"model": "author",
"type": "left",
"on": {"eq": [
{"model": "book", "field": "author_id"},
{"model": "author", "field": "id"}
]}
}
],
"fields": [
{"model": "book", "field": "id"},
{"model": "author", "field": "id", "alias": "author_id"},
{"lower": {"model": "author", "field": "name"}, "alias": "author_name"}
],
"where": {"and": [
{"eq": [{"model": "book", "field": "id"}, 123]},
{"startswith": [{"model": "author", "field": "name"}, "M"]}
]}
})
statement = query.to_select() # SQLAlchemy select with labeled columns
rows = query.fetch(session) # list of typed result rows
RowModel = query.result_model() # typed row, without executing the query
SELECT book.id AS id, author.id AS author_id, lower(author.name) AS author_name
FROM book LEFT OUTER JOIN author ON book.author_id = author.id
WHERE book.id = 123 AND author.name LIKE 'M' || '%'
One rule: a plain JSON value (string, number, boolean) is always a constant, an object is an expression identified by its keys:
| Object | Expression |
|---|---|
{"model": "book", "field": "id"} |
field ("as" selects a joined instance) |
{"model": "review", "fields": [...], "where": ...} |
subquery (no field) |
{"lower": ...}, {"eq": [...]} |
function (its only key besides alias) |
{"ref": "author_name"} |
alias of fields (group_by only) |
- Query:
model(defaults to the base model),as,joins,fields(all fields of the queried model if not set),where,group_by. Subqueries have the same structure. - Joins:
{"model", "as", "type", "on"}withtypeinner(default),leftorfull.onmay reference the queried model and the previous joins. A model joined twice needsas, it is referenced by{"model": "author", "as": "reviewer", "field": "name"}. Fields of the outer side of an outer join areOptionalin the result model. - Conditions (
where,on) are functions returningbool:eq,ne,gt,gte,lt,lte,between,in,not_in({"in": [x, [1, 2]]}or{"in": [x, <subquery>]}),contains,icontains,startswith,istartswith,endswith,iendswith,like,not_like,ilike,is_null,is_not_null,and,or,notandexists({"exists": <subquery>}). - Functions:
lower,upper,trim,length,concat,substring,replace,abs,round,ceil,floor,add,sub,mul,div,date,year,month,day,hour,minute,coalesce,nullif,cast({"cast": [x, "string"]}) and the aggregatescount({"count": "*"}counts all rows),count_distinct,sum,avg,min,max. - Subqueries used as value or in
inreturn exactly one field, its type is the type of the expression. Subqueries may reference the models of the outer queries, inner models shadow outer ones with the same name (useas). - Typing: all expressions are typed per result type, e.g.
{"lower": <int field>}or{"eq": [<int field>, "x"]}are rejected by the JSON schema and the validation. - Grouping: fields outside of aggregate functions must be part of
group_by, aggregates must not be nested and are not allowed inwhere,onandgroup_by. An emptygroup_bymeans no grouping. - Limits:
max_joins(default 5) per (sub)query andmax_depth(default 3) of nested subqueries.
{
"joins": [{"model": "author", "type": "left", "on": {"eq": [
{"model": "book", "field": "author_id"}, {"model": "author", "field": "id"}
]}}],
"fields": [
{"lower": {"model": "author", "field": "name"}, "alias": "author_name"},
{"count": "*", "alias": "books"},
{"model": "review", "fields": [{"avg": {"model": "review", "field": "rating"}}],
"where": {"eq": [{"model": "review", "field": "book_id"}, {"model": "book", "field": "id"}]},
"alias": "avg_rating"}
],
"where": {"exists": {"model": "review", "where": {"eq": [
{"model": "review", "field": "book_id"}, {"model": "book", "field": "id"}
]}}},
"group_by": [{"ref": "author_name"}, {"model": "book", "field": "id"}]
}
Models are registered by their table name (models=[Author]) or explicitly
(models={"writer": Author}). sources overrides the table of a model,
include_fields / exclude_fields restrict the fields (a list for the base
model or a mapping of model names to lists).
Functions and conditions are provided by generators (annotation) -> Iterable[SqlExpressionFunction] which are called for every known type:
from sqlalchemy import Integer, func
from querymodelling.sql_filter import without
from querymodelling.sql_query import (
Expr, SqlExpressionFunction, default_sql_expression_mapping
)
def instr(annotation):
if annotation is str:
yield SqlExpressionFunction(
"instr", int, (Expr(str), Expr(str)),
lambda value, search: func.instr(value, search, type_=Integer()))
mapping = default_sql_expression_mapping()
mapping.function_generators = [
without(generator, "cast") for generator in mapping.function_generators
]
mapping.add_functions(instr)
BookQuery = SqlQueryModel(Book, models=[Author], mapping=mapping)
Argument placeholders are Expr(*types) (an expression of the types, any
type if empty), Many(type) (a list or a subquery, see in) and Query()
(a subquery, see exists). Referenceable fields are provided by reference
generators (reference_generators=..., e.g. virtual fields based on SQL
expressions).
Result schema
The result of a validated query is known without executing it:
query.result_model() is the typed model of a result row,
query.result_schema() the JSON schema of the result rows
(result_schema(many=False) of a single row).
- Fields copy the information of their source field: type,
title,description,examples,json_schema_extra,deprecatedand the constraints (max_length,ge, ...). They may beNULLif their field is optional or on the outer side of an outer join. - Constants are of their type and never
NULL. - Functions define their result:
result_type,nullableandresult_field:nullable(Nullability):PROPAGATE(default,NULLif any argument may beNULL),COALESCE(if all arguments may be),ALWAYS(e.g.sum,avg,min,max,nullif,div) orNEVER(e.g.count,is_null,exists),True/Falsefor always / never.result_field: field information from the arguments,inherit()(all of the argument, e.g.min,max,nullif) ordescribe()(only its title and description, e.g.lower,round,avg,coalesce). Without a rule the result has no field information.
- Subqueries are the result of their single field,
NULLif they may return no row (an aggregate withoutgroup_byalways returns one row).
SqlExpressionFunction(
"initials", str, (Expr(str),), lambda value: func.substr(value, 1, 1),
nullable=Nullability.PROPAGATE, result_field=describe(),
)
Access rules
Models and fields can be restricted per access context - any object of the
application, e.g. the user. Without rules everything is allowed. Rules are
fixed values or callables (context) -> flags | bool receiving the context
directly, None if no context is given. The declarations are generic in the
type of the context (None if not given, SqlFieldAccess[User],
SqlQueryFactory[User]), SqlModelAccess also in the model of the rows
rule (SqlModelAccess[User, Self] within the class body, the model defaults
to Any).
from typing import Annotated, Self
from querymodelling.sql_query import (
INVISIBLE, FieldUsage, ModelUsage, SqlFieldAccess, SqlModelAccess,
SqlQueryFactory
)
class Book(SQLModel, table=True):
__sql_access__ = SqlModelAccess[User, Self](
# rows of the user's tenant only - in every position
rows=lambda user, book: book.tenant_id == user.tenant_id,
)
id: int | None = Field(default=None, primary_key=True)
title: str
cost: Annotated[float, SqlFieldAccess[User](lambda user: (
FieldUsage.ALL if user.is_admin else FieldUsage.AGGREGATE))]
tenant_id: Annotated[int, SqlFieldAccess(INVISIBLE)]
class Salary(SQLModel, table=True):
__sql_access__ = SqlModelAccess[User](usage=lambda user: user.is_admin)
...
queries = SqlQueryFactory[User](Book, models=[Author, Salary])
queries.schema(user) # JSON schema of the user
query = queries.validate(data, user) # validated for the user
query.fetch(session) # rows rules applied for the user
- Fields:
FieldUsage.SELECT(fields),FILTER(where,on),GROUP(group_by),AGGREGATE(within aggregate functions),ALL/NONE(VISIBLE/INVISIBLE), declared byAnnotated[..., SqlFieldAccess(rule)]orSqlModelAccess(fields={"name": rule}). - Models:
ModelUsage.QUERY(queried model),JOIN,SUBQUERY, declared by__sql_access__ = SqlModelAccess(usage=rule). rows:(context, entity) -> conditionrestricts the rows of a model wherever it is used (queried model, joins incl. outer joins, subqueries), the model is replaced by a derived table of the permitted rows.SqlQueryModel(..., access={"book": SqlModelAccess(...)})overrides the declared rules,SqlQueryModel(..., context=user)evaluates them for a context.- Models and fields without any access are removed from the query model and
its JSON schema (and are reported as unknown), partial access is checked
by the validation.
SqlQueryFactorycaches the query models per permission signature: contexts with the same permissions share them. - Partial access may still reveal values:
FILTERby comparisons,AGGREGATEbymin/maxof a single row.
Metadata
Release files for querymodelling 0.0.18
For a detailed explanation of source distributions (sdists) and built distributions (wheels), please see the package formats documentation.
Source distribution (sdist)
| File | Size | Uploaded | Provenance | Actions |
|---|---|---|---|---|
| querymodelling-0.0.18.tar.gz | 77.0 kB | With provenance | Details for querymodelling-0.0.18.tar.gz |
Built distribution (wheel)
| File | Interpreter | ABI | Platform | Actions Reset |
|---|---|---|---|---|
| querymodelling-0.0.18-py3-none-any.whl | Python 3 | none | any | Details for querymodelling-0.0.18-py3-none-any.whl |
Total release size: 151.6 kB
Release files / querymodelling-0.0.18.tar.gz
| Download URL | querymodelling-0.0.18.tar.gz |
|---|---|
| Size | 77.0 kB |
| Tags | Source |
|
SHA-256 checksum How to use checksums |
22d36b6c0723b1312974af98e08e700938c32e532a0df5d511bcca91e00f90c2
|
|
BLAKE2b-256 checksum How to use checksums |
687e7f1cca4a71c32c7115f96ee60077d5ed89e1daad7cca715a78a17b649cc2
|
| Upload date | |
|
Uploaded using Trusted Publishing? What is trusted publishing? |
Yes |
| Uploaded via |
twine/7.0.0 CPython/3.13.16
|
Provenance
Provenance describes where a file came from. On PyPI, provenance is shared via attestations, which provide a verifiable record of the build or publishing details. View details, limitations and caveats.
PyPI Publish Attestation
PyPI verified that this artifact, at this checksum, originated from the publisher listed below.
Signed by GitHub Actions, verified by PyPI on Nov 13, 2026.
Transparency logRelease files / querymodelling-0.0.18-py3-none-any.whl
| Download URL | querymodelling-0.0.18-py3-none-any.whl |
|---|---|
| Size | 74.7 kB |
| Tags | Python 3 |
|
SHA-256 checksum How to use checksums |
0c378b107067a259cd0bc22571362c20ada2fa7ec6f1b902ff549517fc65efd8
|
|
BLAKE2b-256 checksum How to use checksums |
29af9f6e9b99881640f361811eb006ff61ace06f4c95876dd8249fa350dccf29
|
| Upload date | |
|
Uploaded using Trusted Publishing? What is trusted publishing? |
Yes |
| Uploaded via |
twine/7.0.0 CPython/3.13.16
|
Provenance
Provenance describes where a file came from. On PyPI, provenance is shared via attestations, which provide a verifiable record of the build or publishing details. View details, limitations and caveats.
PyPI Publish Attestation
PyPI verified that this artifact, at this checksum, originated from the publisher listed below.
Signed by GitHub Actions, verified by PyPI on Nov 13, 2026.
Transparency logSecurity for querymodelling 0.0.18
Provenance
Provenance describes where a file came from. On PyPI, provenance is shared via attestations, which provide a verifiable record of the build or publishing details. View details, limitations and caveats.
Full provenance
Both files in this release have been uploaded with provenance metadata.
Attestation for querymodelling-0.0.18.tar.gz
PyPI Publish Attestation
PyPI verified that this artifact, at this checksum, originated from the publisher listed below.
Signed by GitHub Actions, verified by PyPI on Nov 13, 2026.
Transparency logA note on package security
PyPI is a repository for distributing packages, not a service for auditing them. While third-party security researchers and our staff do their best to detect and remove malicious packages, PyPI cannot guarantee the quality, security or suitability of any given package.
To learn more about protecting yourself from malicious actors, visit our package security documentation.