Collections and list()

Hydrated objects

list(), get(), and related reads normally turn rows into the correct object or STI subclass. Use include to batch-load named relationships without N+1 queries.

Projected rows

list({ select }) validates logical field names and returns precisely typed plain rows. Projection and relationship inclusion are intentionally separate modes.

const rows = await items.list({
  select: ['id', 'title', 'status'] as const,
  where: { status: 'open' },
  orderBy: 'created_at DESC',
  limit: 50
});

A filter key carries its operator

Each where key is a field name with an optional space and operator. Equality is the default. Use the field names declared on the model. The collection converts them to database columns before it builds the statement. It validates each name against the model, so a typo reports the valid names instead of a SQL error.

const overdue = await invoices.list({
  where: {
    status: 'open',                 // status = ?
    'total >=': 100,                // total >= ?
    'currency in': ['CAD', 'USD'],  // currency IN (?, ?)
    'reference like': 'INV-2026-%', // reference LIKE ?
    voidedAt: null                  // voided_at IS NULL
  },
  orderBy: 'created_at DESC',
  limit: 50
});

The operator set

Nine operators reach SQL: =, >, <, >=, <=, !=, in, not in, and like. A null value turns = into IS NULL and != into IS NOT NULL. An array value with no explicit operator is read as in.

• in and not in require a non-empty array. An empty one is rejected rather than compiled into invalid SQL; listByIds([]) returns an empty list instead.
• like requires a string value, and you supply the % wildcards yourself.
• contains and dot-notation JSON paths such as metadata.userId are rejected at the API boundary because the SQL query builder cannot execute them. Use like for text matching.
• Fields marked @field({ sensitive: true }) are rejected as filter keys. Thus, a where clause cannot read a secret value one character at a time.
• Generated REST routes expose the same filters as field[op] query parameters. The operators are gt, gte, lt, lte, ne, in, and like. in takes a comma-separated list. not in has no query-parameter spelling.

What a where clause cannot express

Conditions in one where object are joined with AND. There is no OR, no negated group, no nested condition, no subquery, and no join. orderBy accepts a field name and a direction, not an expression.

• The underlying query builder can emit OR from a two-dimensional condition array. However, the collection validates where as a flat object of identifier keys and rejects that shape.
• Keys must be identifiers followed only by an optional supported operator. Expression text and dot-separated JSON paths are rejected, which keeps request-supplied filter names from reaching the SQL field position.

collection.query() is the escape hatch

query(sql, params) runs SQL you wrote and hydrates the rows into the same objects list() returns, including STI subclass resolution. Use it for OR, NOT EXISTS, joins, CTEs, and aggregates, and keep list() for everything it can already express.

• Names inside the SQL string are database columns. Returned rows are converted back to the model field names during hydration.
• Bind values as parameters; the placeholder style follows your database adapter. The collection does not parse the statement, so anything interpolated into the text is your own injection risk.
• Tenant scope is not added for you. A beforeQuery interceptor guards tenant-scoped models, and allowRawOnTenantScoped opts out of it deliberately — then the tenant predicate is yours to write.
• query() is documented for reads, but it can run a write. A statement that looks like a mutation invalidates this table in the read cache. Model hooks and save-time interceptors do not run.

const unbilled = await orders.query(
  `SELECT o.* FROM orders o
   WHERE o.status = ?
     AND NOT EXISTS (
       SELECT 1 FROM invoices i WHERE i.order_id = o.id
     )
   ORDER BY o.created_at DESC
   LIMIT ?`,
  ['fulfilled', 100]
);

Reading many records at once

listByIds(ids) issues a single IN query and returns hydrated objects. An empty array returns an empty list without touching the database, which is the graceful path an empty in filter refuses to take.

• Result order is not guaranteed. Index the result by id when the caller needs its own ordering back.
• listByIds does not divide a request into chunks. Databases limit the bound parameters in one statement. Framework relationship loaders divide their IN lists at 900 values. Use a similar size for long id lists.
• count() uses the same where conversion as list() and never uses the read cache. Thus, a cached page and a fresh count can briefly disagree during the TTL window.

const items = await products.listByIds(ids);
const byId = new Map(items.map((item) => [item.id, item]));

// Long id lists need splitting; listByIds does not do it for you.
const all = [];
for (let i = 0; i < ids.length; i += 900) {
  all.push(...(await products.listByIds(ids.slice(i, i + 900))));
}

Writes happen one statement at a time

create() and save() persist a single object per call; there is no bulk create on the collection. A loop of saves is a loop of round trips, and on a durable single-connection database each one commits on its own. Wrapping the loop in a transaction turns that into one commit.

• A collection accepts an initialized database instance as its db option. Give the same instance to a batch of collection writes to put them in one transaction.
• transaction() is an optional member of the adapter interface, so narrow it before calling; a bare call does not type-check under strict TypeScript.
• The callback result is the transaction result, and a thrown error rolls the whole batch back rather than leaving it half-applied.
• Nesting transaction() is adapter-specific: SQLite and PostgreSQL re-enter under a savepoint, while DuckDB and the JSON adapter throw. Pass the handle down instead of nesting.
• On single-connection adapters, a concurrent transaction waits its turn and rejects after transactionQueueTimeout — 30 seconds by default. Keep the batch bounded rather than holding one transaction open for a whole import.

const db = await getDatabase({ type: 'sqlite', url: 'app.db' });
if (!db.transaction) throw new Error('This adapter does not support transactions.');

await db.transaction(async (tx) => {
  const products = await ProductCollection.create({ db: tx });
  for (const row of rows) {
    await products.create(row);
  }
});

When raw SQL is the right batch tool

A set-based UPDATE or DELETE does in one statement what a read-modify-write loop does in several round trips per row. collection.query() runs it under the same caveats as any raw statement: model hooks, save-time interceptors, and embedding regeneration do not run.

• Keep the loop when per-object hooks, embeddings, auditing, or change-feed entries have to run for each record.
• Reach for one statement when the change is purely columnar and none of that per-object work applies.
• Measure on your own adapter before choosing. The gap between the two depends on durability settings, latency to the database, and how much work each hook does.

Tenant and cache behavior

Tenant interceptors run for list, get, count, and related reads. Read caching is opt-in; writes invalidate affected entries, while count always checks the database.