Querying

All read methods on SqbCollectionService accept a shared set of options for filtering, shaping, and paginating results.

---

Filtering

SQB filters are built with operator methods on the sql object imported from @sqb/builder. There is no MongoDB-style $ prefix syntax.

Operator methods

import { sql } from '@sqb/builder';

// Equality
const customers = await svc.findMany({ filter: sql.Eq('status', 'active') });

// Comparison
const customers = await svc.findMany({ filter: sql.Gt('age', 18) });
const customers = await svc.findMany({ filter: sql.Between('createdAt', startDate, endDate) });

// Array membership
const customers = await svc.findMany({ filter: sql.In('country', ['US', 'CA', 'MX']) });

// Pattern matching
const customers = await svc.findMany({ filter: sql.Like('name', 'Ali%') });
const customers = await svc.findMany({ filter: sql.ILike('email', '%@gmail.com') }); // case-insensitive

// NULL checks
const customers = await svc.findMany({ filter: sql.Is('deletedAt', null) });
const customers = await svc.findMany({ filter: sql.IsNot('deletedAt', null) });

// AND / OR
const customers = await svc.findMany({
  filter: sql.And(sql.Eq('status', 'active'), sql.Gt('age', 18)),
});

const customers = await svc.findMany({
  filter: sql.Or(sql.Eq('country', 'US'), sql.Eq('country', 'CA')),
});

Plain object shorthand

For simple cases, pass a plain object. Equality is implied for plain values; operators are expressed as field name suffixes:

// Equality (implicit)
await svc.findMany({ filter: { status: 'active' } });

// Multiple conditions — implicit AND
await svc.findMany({ filter: { status: 'active', country: 'US' } });

// Operator suffixes
await svc.findMany({ filter: { 'age >': 18 } });
await svc.findMany({ filter: { 'age <=': 65 } });
await svc.findMany({ filter: { 'name like': 'Ali%' } });
await svc.findMany({ filter: { 'deletedAt is': null } });
await svc.findMany({ filter: { 'country in': ['US', 'CA'] } });

Array of filters — implicit AND

Pass an array to combine multiple conditions with AND:

await svc.findMany({
  filter: [
    sql.Eq('status', 'active'),
    sql.IsNot('deletedAt', null),
    sql.In('country', ['US', 'CA']),
  ],
});

OPRA filter string

Pass a filter as a plain string using OPRA filter expression syntax:

const customers = await svc.findMany({
  filter: 'country = "US" or country = "CA"',
});

const customers = await svc.findMany({
  filter: 'status = "active" and age > 18',
});

---

Filtering on associated fields

Use dot notation to filter across relationships:

await svc.findMany({ filter: sql.Eq('country.continent.code', 'EU') });
await svc.findMany({ filter: { 'country.code': 'DE' } });

---

commonFilter — global row-level constraint

commonFilter is applied to every read and write operation on a service instance. Use it for multi-tenancy, soft deletes, or any constraint that must never be bypassed:

export class CustomersService extends SqbCollectionService<Customer> {
  constructor(db: SqbClient) {
    super(Customer, {
      db,
      commonFilter: (_, _this) => sql.And(
        sql.Eq('tenantId', _this.context.tenantId),
        sql.Is('deletedAt', null),
      ),
    });
  }
}

tip

Use commonFilter for constraints that must always hold. Use the method-level filter option for ad-hoc query criteria.

---

Projection

Control which fields are returned by passing an array of field names.

const customers = await svc.findMany({
  filter: sql.Eq('status', 'active'),
  projection: ['id', 'name', 'email'],
});

// TypeScript return type narrows to PartialDTO<Customer> when projection is supplied
const partial = await svc.get(id, { projection: ['id', 'name'] });
partial.name;  // ✓
partial.email; // ✗ — not in projection, TypeScript error

---

Sorting

Pass an array of field names. Prefix with - for descending order.

const customers = await svc.findMany({
  sort: ['status', '-createdAt'], // status ASC, createdAt DESC
});

---

Pagination

Use limit and skip together, or call findManyWithCount to get the total in one round trip.

const PAGE_SIZE = 20;
const page = 3;

const { items, count } = await svc.findManyWithCount({
  filter: sql.Eq('status', 'active'),
  sort: ['-createdAt'],
  limit: PAGE_SIZE,
  skip: (page - 1) * PAGE_SIZE,
});

console.log(`Page ${page} of ${Math.ceil(count / PAGE_SIZE)}`);

findMany applies defaultLimit (default 100) when limit is not specified. Set it in the constructor to change the service default:

super(Customer, { defaultLimit: 50 });

---

Checking existence

// Does this specific row exist?
const exists = await svc.exists(id);

// Does any row match a filter?
const hasActive = await svc.existsOne({ filter: sql.Eq('status', 'active') });

// Assert existence — throws ResourceNotAvailableError if not found
await svc.assert(id);

---

Full API reference

→ SqbCollectionService