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.
Setup
import { createAn5Adapter } from '@an5/adapters';
const db = createAn5Adapter({
connectionString: process.env.DATABASE_URL!,
});
Filtering
Comparison Operators
const users = await db.user.findMany({
where: {
// Equal
status: 'active',
// Not equal
status: { not: 'banned' },
// Comparison
age: { gte: 18 },
score: { gt: 100 },
views: { lt: 1000 },
orderCount: { lte: 10 }
}
});
String Operators
const users = await db.user.findMany({
where: {
// Contains
email: { contains: '@example.com' },
// Starts with
name: { startsWith: 'John' },
// Ends with
name: { endsWith: 'son' },
// In list
role: { in: ['admin', 'moderator'] },
// Not in list
role: { notIn: ['banned', 'suspended'] }
}
});
Logical Operators
const users = await db.user.findMany({
where: {
OR: [
{ role: 'admin' },
{ role: 'moderator' }
],
AND: [
{ isActive: true },
{ createdAt: { gte: lastMonth } }
],
NOT: {
status: 'banned'
}
}
});
Sorting
const users = await db.user.findMany({
orderBy: [
{ role: 'asc' },
{ createdAt: 'desc' }
]
});
Pagination
Offset-based
const users = await db.user.findMany({
skip: 20, // Skip first 20
take: 10 // Take next 10
});
When skip is used, dialect-aware pagination is automatically generated (LIMIT/OFFSET for PostgreSQL/MySQL/SQLite, OFFSET/FETCH for MSSQL).
Aggregations
Basic Aggregations
const stats = await db.order.aggregate({
_count: true,
_sum: { total: true },
_avg: { total: true },
_min: { total: true },
_max: { total: true }
});
console.log({
count: stats._count._all,
total: stats._sum.total,
average: stats._avg.total,
min: stats._min.total,
max: stats._max.total
});
Group By
const ordersByUser = await db.order.groupBy({
by: ['status'],
where: { total: { gte: 10 } },
orderBy: { status: 'asc' },
skip: 0,
take: 10,
_count: true,
_sum: { total: true }
});
Nested Relation Pagination
Inside include, skip and take paginate the related rows per parent row:
const users = await db.user.findMany({
include: {
posts: {
orderBy: { createdAt: 'desc' },
skip: 0,
take: 5, // first 5 posts for each user
}
}
});
Select Specific Fields
const users = await db.user.findMany({
select: {
id: true,
email: true,
name: true,
_count: true,
}
});
Complex Query Example
// Find active users with published posts, ordered by join date
const users = await db.user.findMany({
where: {
isActive: true,
},
include: {
posts: {
select: {
id: true,
title: true,
createdAt: true
},
orderBy: { createdAt: 'desc' },
take: 5
},
_count: true
},
orderBy: { createdAt: 'desc' },
take: 10
});
Raw SQL Queries
For queries where you need direct SQL execution:
const users = await db.$queryRawUnsafe(
'SELECT id, email FROM users WHERE email = @p_0',
'reader@example.com'
);
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.
Next Steps
- Relations - Model relationships & nested writes
- Deployment - Production configuration