Advanced Queries

@an5/adapters provides powerful query capabilities for complex data retrieval across SQL dialects (MSSQL, PostgreSQL, MySQL, SQLite) and Google Sheets.

Postgresql note: PostgreSQL uses LIMIT/OFFSET pagination. Python raw SQL uses %s placeholders; Go requires a registered PostgreSQL driver.

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.
  • take is what turns pagination on. skip on its own is ignored, so always pass take when 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