Query Building

The APSO SDK query builder provides a fluent, chainable API for constructing NestJS CRUD-compatible query strings. Every method returns this, so you can chain calls in any order before executing with a terminal method like findMany() or findOne().

Query Builder Methods

Method	Signature	Description
`where()`	`.where(filter: Record<string, any>)`	Add filter conditions (AND)
`or()`	`.or(filter: Record<string, any>)`	Add OR filter conditions
`orderBy()`	`.orderBy(sort: Record<string, 'ASC' \| 'DESC'>)`	Sort results
`limit()`	`.limit(n: number)`	Maximum number of records to return
`offset()`	`.offset(n: number)`	Number of records to skip
`page()`	`.page(n: number)`	Page number (1-based)
`select()`	`.select(fields: string[])`	Return only specified fields
`join()`	`.join(relations: string[])`	Include related entities
`cache()`	`.cache(enable?: boolean, duration?: number)`	Enable response caching

Filtering with where()

The .where() method accepts an object where keys are field names and values define the condition. Filters use the NestJS CRUD operator syntax ($eq, $ne, $gt, $lt, etc.).

Basic Equality


// Explicit $eq operator
const active = await client.entity('Users')
  .where({ status: { $eq: 'Active' } })
  .findMany();
// GET /Users?filter=status||$eq||Active

Multiple Conditions (AND)

When you pass multiple fields to .where(), they are combined with AND logic:


const result = await client.entity('WorkspaceServices')
  .where({
    status: { $eq: 'Active' },
    build_status: { $eq: 'Ready' },
  })
  .findMany();
// GET /WorkspaceServices?filter=status||$eq||Active&filter=build_status||$eq||Ready

You can also call .where() multiple times — conditions are merged:


const result = await client.entity('Products')
  .where({ status: { $eq: 'Active' } })
  .where({ category: { $eq: 'Electronics' } })
  .findMany();
// Both conditions are applied

Filter Operators

The SDK supports all NestJS CRUD condition operators via the @dataui/crud-request library:

Operator	Description	Example
`$eq`	Equal	`{ status: { $eq: 'Active' } }`
`$ne`	Not equal	`{ status: { $ne: 'Archived' } }`
`$gt`	Greater than	`{ price: { $gt: 100 } }`
`$gte`	Greater than or equal	`{ price: { $gte: 100 } }`
`$lt`	Less than	`{ price: { $lt: 50 } }`
`$lte`	Less than or equal	`{ price: { $lte: 50 } }`
`$starts`	Starts with	`{ name: { $starts: 'Pro' } }`
`$ends`	Ends with	`{ email: { $ends: '@example.com' } }`
`$cont`	Contains (substring)	`{ name: { $cont: 'widget' } }`
`$excl`	Does not contain	`{ name: { $excl: 'test' } }`
`$in`	In array	`{ status: { $in: ['Active', 'Pending'] } }`
`$notin`	Not in array	`{ status: { $notin: ['Archived'] } }`
`$isnull`	Is null	`{ deleted_at: { $isnull: true } }`
`$notnull`	Is not null	`{ assigned_to: { $notnull: true } }`
`$between`	Between two values	`{ price: { $between: [10, 50] } }`

Filter Examples


// Products priced between $10 and $50
const affordable = await client.entity('Products')
  .where({ price: { $between: [10, 50] } })
  .findMany();
 
// Users whose email contains "example.com"
const users = await client.entity('Users')
  .where({ email: { $cont: 'example.com' } })
  .findMany();
 
// Orders created after a specific date
const recentOrders = await client.entity('Orders')
  .where({ created_at: { $gt: '2025-01-01' } })
  .findMany();
 
// Products in specific categories
const electronics = await client.entity('Products')
  .where({ category: { $in: ['Electronics', 'Computers'] } })
  .findMany();
 
// Records that have NOT been soft-deleted
const activeRecords = await client.entity('Products')
  .where({ deleted_at: { $isnull: true } })
  .findMany();

OR Conditions

Use .or() to add conditions with OR logic. OR conditions are separate from the AND conditions set by .where():


const result = await client.entity('Products')
  .where({ status: { $eq: 'Active' } })
  .or({ category: { $eq: 'Featured' } })
  .findMany();

This finds products that match the .where() conditions AND also applies the OR conditions as an alternative filter set. The exact behavior follows the NestJS CRUD or parameter specification.

Sorting with orderBy()

Sort results by one or more fields. Each field can be sorted 'ASC' (ascending) or 'DESC' (descending):


// Single field sort
const newest = await client.entity('Products')
  .orderBy({ created_at: 'DESC' })
  .findMany();
// GET /Products?sort=created_at,DESC
 
// Multiple field sort
const sorted = await client.entity('Tasks')
  .orderBy({ priority: 'ASC', created_at: 'DESC' })
  .findMany();
// GET /Tasks?sort=priority,ASC&sort=created_at,DESC

Note: Sort direction values must be uppercase strings: 'ASC' or 'DESC'.

Pagination

The SDK supports two pagination strategies: page-based and offset-based.

Page-Based Pagination

Use .page() with .limit() for page-based pagination:


// Page 1: first 20 records
const page1 = await client.entity('Products')
  .limit(20)
  .page(1)
  .findMany();
 
// Page 2: records 21-40
const page2 = await client.entity('Products')
  .limit(20)
  .page(2)
  .findMany();

The generated query string uses the page and limit parameters:


GET /Products?limit=20&page=1
GET /Products?limit=20&page=2

When your backend has pagination enabled, the response includes pagination metadata:


const result = await client.entity('Products').limit(20).page(1).findMany();
 
console.log(result.data);      // Product[] -- the records for this page
console.log(result.total);     // 150 -- total number of matching records
console.log(result.page);      // 1 -- current page number
console.log(result.pageCount); // 8 -- total number of pages

Offset-Based Pagination

Use .offset() with .limit() for offset-based pagination:


// First 10 records
const batch1 = await client.entity('Products')
  .limit(10)
  .offset(0)
  .findMany();
// GET /Products?limit=10&offset=0
 
// Next 10 records
const batch2 = await client.entity('Products')
  .limit(10)
  .offset(10)
  .findMany();
// GET /Products?offset=10&limit=10

Combining Page and Offset

You can use both .page() and .offset() together, though in practice you typically use one or the other:


const data = await client.entity('Products')
  .offset(5)
  .page(2)
  .findMany();
// GET /Products?offset=5&page=2

Limit Only

Use .limit() without pagination to cap the number of results:


const top10 = await client.entity('Products')
  .orderBy({ sales_count: 'DESC' })
  .limit(10)
  .findMany();
// GET /Products?sort=sales_count,DESC&limit=10

Field Selection with select()

Return only the fields you need. This reduces response size and can improve query performance:


const names = await client.entity('Products')
  .select(['id', 'name', 'price'])
  .findMany();
// GET /Products?fields=id,name,price

Field selection works with all other query builder methods:


const result = await client.entity('Products')
  .select(['id', 'name', 'status'])
  .where({ status: { $eq: 'Active' } })
  .orderBy({ name: 'ASC' })
  .limit(50)
  .findMany();
// GET /Products?fields=id,name,status&filter=status||$eq||Active&sort=name,ASC&limit=50

Joining Relations with join()

Load related entities in a single query using .join(). Pass an array of relation names that correspond to the entity’s defined relationships:


// Load orders with their customer and items
const orders = await client.entity('Orders')
  .join(['customer', 'orderItems'])
  .findMany();
// GET /Orders?join=customer&join=orderItems
 
// Combine joins with filtering and sorting
const recentOrders = await client.entity('Orders')
  .join(['customer', 'orderItems'])
  .where({ status: { $eq: 'Pending' } })
  .orderBy({ created_at: 'DESC' })
  .limit(10)
  .findMany();
// GET /Orders?join=customer&join=orderItems&filter=status||$eq||Pending&sort=created_at,DESC&limit=10

The relation names must match the relationship names defined in your .apsorc schema.

Caching with cache()

Enable in-memory caching for GET requests. Subsequent identical queries return the cached response until the TTL expires:


// Cache with default 60-second TTL
const products = await client.entity('Products')
  .cache(true)
  .findMany();
 
// Cache with custom 5-minute TTL
const products = await client.entity('Products')
  .cache(true, 300)
  .findMany();
 
// Disable cache (default behavior)
const fresh = await client.entity('Products')
  .cache(false)
  .findMany();

The .cache() method accepts two parameters:

Parameter	Type	Default	Description
`useCache`	`boolean`	`true`	Enable or disable caching
`duration`	`number`	`60`	Cache TTL in seconds

Note: When cache(true) is set, the SDK also adds cache=0 to the query string. This signals the NestJS CRUD backend to reset its server-side cache for this query, ensuring you get fresh data that then gets cached client-side.

Combining Everything

All query builder methods can be combined in a single chain. The order of method calls does not matter — the SDK builds the query string when you call the terminal method:


const result = await client.entity('WorkspaceServices')
  .select(['id', 'name', 'status', 'build_status', 'created_at'])
  .where({ status: { $eq: 'Active' }, build_status: { $eq: 'Ready' } })
  .join(['workspace'])
  .orderBy({ created_at: 'DESC' })
  .limit(10)
  .page(1)
  .cache(true, 120)
  .findMany();

This produces:


GET /WorkspaceServices?fields=id,name,status,build_status,created_at&filter=status||$eq||Active&filter=build_status||$eq||Ready&join=workspace&sort=created_at,DESC&limit=10&page=1&cache=0

Low-Level Query Parameters

When using the low-level client.get() method, you pass a QueryParams object instead of chaining methods:


interface QueryParams {
  fields?: string[];                        // Field selection
  filter?: Record<string, any>;             // AND filter conditions
  or?: Record<string, any>;                 // OR filter conditions
  join?: string[];                          // Relations to join
  sort?: Record<string, 'ASC' | 'DESC'>;   // Sort order
  limit?: number;                           // Max records
  offset?: number;                          // Records to skip
  page?: number;                            // Page number
  cache?: boolean;                          // Reset server cache
}


const result = await client.get('/WorkspaceServices', {
  fields: ['id', 'name', 'status'],
  filter: {
    status: { $eq: 'Active' },
    build_status: { $eq: 'Ready' },
  },
  join: ['workspace'],
  sort: { created_at: 'DESC' },
  limit: 10,
  page: 1,
  cache: true,
});

Both the fluent API and the low-level API produce identical query strings. The fluent API is recommended for readability and discoverability.

Using QueryBuilder Standalone

The QueryBuilder class can be used independently to build query parameters without executing a request. This is useful if you need to construct queries programmatically:


import { QueryBuilder } from '@apso/sdk';
 
const qb = new QueryBuilder();
qb.where({ status: { $eq: 'Active' } })
  .orderBy({ created_at: 'DESC' })
  .limit(10);
 
const { params, useCache, cacheDuration } = qb.build();
// params: { filter: { status: { $eq: 'Active' } }, sort: { created_at: 'DESC' }, limit: 10 }
// useCache: false
// cacheDuration: 60

Common Query Patterns

Paginated List with Total Count


async function getProductPage(page: number, pageSize: number = 20) {
  return client.entity('Products')
    .where({ status: { $eq: 'Active' } })
    .orderBy({ created_at: 'DESC' })
    .limit(pageSize)
    .page(page)
    .findMany();
}
 
const page1 = await getProductPage(1);
console.log(`Showing ${page1.data.length} of ${page1.total} products`);

Search by Partial Match


async function searchProducts(query: string) {
  return client.entity('Products')
    .where({ name: { $cont: query } })
    .orderBy({ name: 'ASC' })
    .limit(20)
    .findMany();
}

Load Record with Relations


async function getOrderDetails(orderId: string) {
  return client.entity('Orders')
    .where({ id: orderId })
    .join(['customer', 'orderItems', 'payments'])
    .findOne();
}

Recent Records


async function getRecentActivity(limit: number = 10) {
  return client.entity('ActivityLog')
    .orderBy({ created_at: 'DESC' })
    .limit(limit)
    .findMany();
}

Filtered Dashboard Data


async function getDashboardServices(workspaceId: string) {
  return client.entity('WorkspaceServices')
    .where({
      workspace_id: { $eq: workspaceId },
      status: { $ne: 'Deleted' },
    })
    .select(['id', 'name', 'status', 'build_status', 'updated_at'])
    .join(['workspace'])
    .orderBy({ updated_at: 'DESC' })
    .cache(true, 30)
    .findMany();
}

CRUD Operations — Create, update, and delete records
Configuration — Set up authentication and transport options
Installation — Install the SDK in your project