Advanced Queries

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

Sqlserver note: SQL Server uses OFFSET/FETCH pagination. Python raw SQL uses ? placeholders; Go requires a registered SQL Server 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.

Swift

import An5Adapters
import An5Client

let db = try An5Db(connectionString: try An5Config.connectionString())

var byEmail = An5Orm.UserWhere()
byEmail.email = An5Orm.StringFilter.has("@example.com")

let users = try db.user.findMany(Query(
    filter: byEmail.build(),
    orderBy: [["createdAt": "desc"]],
    skip: 0,
    take: 10,
    select: ["id", "email", "createdAt"]
))

var orders = Aggregate()
orders.count = true
orders.sum = ["total"]
orders.average = ["total"]
let stats = try db.order.aggregate(orders)

var revenue = Aggregate()
revenue.by = ["userId"]
revenue.sum = ["total"]
revenue.orderBy = [["userId": "asc"]]
revenue.take = 10
let byUser = try db.order.groupBy(revenue)

let rows = try db.query(
    "SELECT userId, SUM(total) AS revenue FROM orders GROUP BY userId")

Query and Aggregate are plain values, so filter, sort, paging and projection are arguments rather than chained calls. Aggregate keys are flat — stats["_count"], stats["_sum_total"] — and db.query is the raw-SQL escape hatch.

Next Steps