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.

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