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.
Java
import an5.adapters.An5Aggregate;
import an5.adapters.An5GroupBy;
import an5.adapters.An5Query;
import an5.client.An5DbContext;
import an5.client.An5OrmTypes.StringFilter;
import an5.client.An5OrmTypes.UserWhere;
import an5.client.User;
import java.util.List;
import java.util.Map;
try (An5DbContext db = new An5DbContext(System.getenv("DATABASE_URL"))) {
UserWhere where = new UserWhere();
where.Email = StringFilter.has("@example.com");
List<User> users = db.getUser().findMany(new An5Query()
.where(where.toMap())
.orderBy("createdAt", "desc")
.skip(0)
.take(10)
.select("id", "email", "createdAt"));
}
Every model shares one An5Query: where takes the map UserWhere.toMap() produces,
and orderBy, skip, take, select and include chain onto it. Calls are
synchronous and throw SQLException.
Aggregates and grouping live on the same client:
Map<String, Object> stats = db.getOrder().aggregate(
new An5Aggregate().count().sum("total").avg("total"));
List<Map<String, Object>> byUser = db.getOrder().groupBy(
new An5GroupBy().by("userId").sum("total").orderBy("userId", "asc").take(10));
count(), sum(), avg(), min() and max() all come back flat and as Number
values: stats.get("_count"), stats.get("_sum_total").
Raw SQL goes through the context, or through db.adapter() for anything the typed
clients miss:
List<Map<String, Object>> rows = db.queryRaw(
"SELECT userId, SUM(total) AS revenue FROM orders GROUP BY userId");
Next Steps
- Relations - Model relationships & nested writes
- Deployment - Production configuration