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.

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