Advanced Queries
@an5/adapters provides powerful query capabilities for complex data retrieval across SQL dialects (MSSQL, PostgreSQL, MySQL, SQLite) and Google Sheets.
Sqlite note: SQLite uses LIMIT/OFFSET pagination and ? placeholders in Python raw SQL. The Go example uses the sqlite3 driver name; register that driver before opening a connection.
Client query capabilities
The examples above use @an5/adapters for TypeScript. The generated clients cover
the same ground for the other languages, but not evenly — each one is generated
from the same schema and the query surface differs:
| TypeScript | Python | .NET | Go | Rust | Java | Kotlin | Swift | |
|---|---|---|---|---|---|---|---|---|
| Read rows | findMany |
find_many |
FindMany |
FindMany |
find_many |
findMany |
findMany |
findMany |
| Filter | where object |
where dict |
SQL predicate | typed structs | typed structs | An5Query.where |
query block | Query(filter:) |
| Sort | orderBy |
order_by |
in the SQL you write | *SortOrder |
SortOrder |
An5Query.orderBy |
orderBy(...) |
Query.orderBy |
| Paginate | skip / take |
skip / take |
in the SQL you write | Skip / Take |
skip / take |
skip / take |
skip / take |
skip / take |
| Select columns | select |
select |
— | Select |
select |
select |
select |
select |
| Load relations | include |
include |
— | — | — | include |
include |
include |
| Aggregate | aggregate |
aggregate |
Count only |
— | through table() |
aggregate |
aggregate |
aggregate |
| Group by | groupBy |
group_by |
— | — | through table() |
groupBy |
groupBy |
groupBy |
| Raw SQL | $queryRawUnsafe |
query_raw |
QueryRaw |
QueryRaw |
adapter().query_raw |
queryRaw |
query |
query |
The examples below use the fields of the generated example schema — User with
id, email, name, createdAt, and Order with userId, total — so the
queries line up across languages.
Python
from an5_client import An5Client
db = An5Client() # reads DATABASE_URL
users = db.user.find_many(
where={
"email": {"contains": "@example.com"},
"createdAt": {"gte": "2026-01-01"},
},
order_by={"createdAt": "desc"},
skip=0,
take=10,
select=["id", "email", "createdAt"],
)
The calls are synchronous and rows come back as plain dicts.
stats = db.order.aggregate(
where={"total": {"gte": 10}},
_count=True,
_sum={"total": True},
_avg={"total": True},
_min={"total": True},
_max={"total": True},
)
print(stats["_count"], stats["_sum_total"], stats["_avg_total"])
by_user = db.order.group_by(
by=["userId"],
where={"total": {"gte": 10}},
order_by={"userId": "asc"},
take=10,
_sum={"total": True},
)
# [{"userId": "...", "_count": 3, "_sum_total": 120.0}, ...]
Two differences from TypeScript are worth knowing:
- The aggregate result is flat. Keys are
_count,_sum_total,_avg_total; there is no_count._all. takeis what turns pagination on.skipon its own is ignored, so always passtakewhen paging.
Filter keys go into SQL as column names and stay camelCase. The generated
StringFilter dataclasses use snake_case names (starts_with, not_in) that the
parser does not read — pass plain dicts, not those dataclasses.
rows = db.query_raw(
"SELECT userId, SUM(total) AS revenue FROM orders GROUP BY userId",
)
There is no @p_0 rewriting, so write the placeholders your dialect expects:
%s on PostgreSQL, ? on SQL Server and SQLite.
Next Steps
- Relations - Model relationships & nested writes
- Deployment - Production configuration