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.

Rust

use an5_client::{An5Client, DateTimeFilter, SortOrder, StringFilter,
                 UserFindManyArgs, UserOrderByInput, UserWhereInput};

let db = An5Client::connect(&conn_str).await?;

let users = db.user().find_many(&UserFindManyArgs {
    where_: Some(UserWhereInput {
        email: Some(StringFilter {
            contains: Some("@example.com".to_string()),
            ..Default::default()
        }),
        created_at: Some(DateTimeFilter {
            gte: Some(since),
            ..Default::default()
        }),
        ..Default::default()
    }),
    order_by: Some(UserOrderByInput {
        created_at: Some(SortOrder::Desc),
        ..Default::default()
    }),
    take: Some(10),
    skip: 0,
    select: Some(vec!["id".to_string(), "email".to_string()]),
}).await?;

Everything is async, and the result is a Result. The per-model handles do not expose aggregate or groupBy, but the adapter underneath does — reach it with table():

use an5_adapters::AggregateArgs;
use serde_json::json;

let agg = db.table("Order").aggregate(&AggregateArgs {
    sum: Some(json!(["total"])),
    ..Default::default()
}).await?;

println!("{}", agg["_count"]);       // COUNT(*) is always included
println!("{}", agg["_sum_total"]);

let rows = db.adapter()
    .query_raw("SELECT userId, SUM(total) AS revenue FROM orders GROUP BY userId", &[])
    .await?;

Two things to know about the Rust client:

  • The aggregate result is flat — agg["_sum_total"], not agg["_sum"]["total"] — and _sum/_avg/_min/_max take a JSON array of column names.
  • in_list on the filter types serializes to inList, which is not an operator the query builder knows, so it is dropped. Use not_in, or pass the filter as JSON: json!({ "email": { "in": ["a@b.test", "c@d.test"] } }).

Next Steps