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.

Kotlin

import an5.adapters.Sort
import an5.client.An5Config
import an5.client.An5Db
import an5.client.An5Orm

An5Db(An5Config.connectionString()).use { db ->
    val users = db.user.findMany {
        where(An5Orm.UserWhere(email = An5Orm.StringFilter.has("@example.com")).build())
        orderBy("createdAt", Sort.DESC)
        skip(0)
        take(10)
        select("id", "email", "createdAt")
    }

    val stats = db.order.aggregate {
        count()
        sum("total")
        avg("total")
    }

    val byUser = db.order.groupBy("userId") {
        sum("total")
        orderBy("userId", Sort.ASC)
        take(10)
    }

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

The query block is a QueryBuilder, so where, orderBy, skip, take, select and include read top to bottom. Aggregate keys are flat — stats["_count"], stats["_sum_total"] — and db.query is the raw-SQL escape hatch.

Next Steps