Query Builder

SlintORM's fluent query builder covers everything from simple filters to window functions, correlated subqueries, and automatic relation-graph traversal. All methods are chainable. No query fires until a terminal method (get, first, getPaginated) is called.

Builder entry points

Call one of four methods on any model to get a builder. Each returns a different class with its own set of additional methods on top of the base QueryBuilder<T>.

// Four builder entry points — each returns a different builder class

// QueryBuilder<T> — base, covers the majority of queries
model.query()

// AdvancedQueryBuilder<T> — extends QueryBuilder with aggregates,
// window functions, UNION, RIGHT/FULL OUTER joins, subqueries
model.advanced()

// SoftDeleteQueryBuilder<T> — extends QueryBuilder with
// withTrashed() and onlyTrashed() for soft-deleted rows
model.softDelete()

// ExtendedQueryBuilder<T> — extends QueryBuilder with scope()
model.extended()

Terminal methods: get() / first() / getPaginated() / delete() / update()

These methods execute the accumulated query and return results.get(), first(), and getPaginated() are read-only accumulators — they fire the query on call. delete() and update(data) fire immediately and are not chainable (they return a count, not the builder). All other methods accumulate clauses without hitting the database.

// Terminal methods — execute the query and return results

// .get() — execute and return all matching rows as EntityWithUpdate<T>[]
const users = await User.query()
  .where('role', '=', 'admin')
  .orderBy('name', 'ASC')
  .get();

// .first(condition?) — execute and return first row or null
// condition: raw SQL string OR partial filter object (both optional)
const user = await User.query()
  .orderBy('createdAt', 'DESC')
  .first();                         // no condition — first row

const admin = await User.query()
  .first('role = "admin"');         // raw SQL condition

const mod = await User.query()
  .first({ role: 'moderator' });    // partial filter object

// .getPaginated(page, perPage) — execute and return paginated result
const result = await Post.query()
  .where('published', '=', true)
  .getPaginated(1, 20);
// { data: Post[], total: number, page: number, lastPage: number }

// .delete() — bulk delete matching rows (non-terminal, executes immediately)
const deleted = await User.query()
  .where('status', '=', 'inactive')
  .delete();

// .update(data) — bulk update matching rows (non-terminal, executes immediately)
const updated = await User.query()
  .where('role', '=', 'guest')
  .update({ role: 'user' });

Chaining

// All non-terminal methods return 'this' — fully chainable
const result = await User.query()
  .select('id', 'name', 'email')
  .where('active', '=', true)
  .whereNotNull('verifiedAt')
  .whereIn('role', ['admin', 'moderator'])
  .orderBy('name', 'ASC')
  .limit(50)
  .offset(0)
  .get();

// The builder accumulates clauses until a terminal method is called
// No query fires until .get(), .first(), or .getPaginated()

Dialects — per-driver SQL output

The query builder automatically adapts placeholder style, identifier quoting, upsert syntax, and ILIKE support to the configured driver. You write the same builder code regardless of database.

// Dialects — the QB emits different SQL per driver automatically
// You write the same query builder code; SlintORM adapts the output

// SQLite
// - Placeholders:  ?  (positional)
// - Identifiers:   unquoted or "double-quoted"
// - AUTOINCREMENT: INTEGER PRIMARY KEY AUTOINCREMENT
// - Upsert:        INSERT OR REPLACE / manual fallback
// - ILIKE:         LIKE (SQLite LIKE is case-insensitive for ASCII)
//
// PostgreSQL
// - Placeholders:  $1, $2, $3  (numbered)
// - Identifiers:   "double-quoted"
// - AUTOINCREMENT: SERIAL / BIGSERIAL
// - Upsert:        ON CONFLICT (col) DO UPDATE SET ...
// - ILIKE:         ILIKE  (native)
// - RETURNING *:   used on every INSERT to return the full row
//
// MySQL
// - Placeholders:  ?  (positional)
// - Identifiers:   `backtick-quoted`
// - AUTOINCREMENT: AUTO_INCREMENT
// - Upsert:        ON DUPLICATE KEY UPDATE ...
// - ILIKE:         LIKE  (case-insensitive by default collation)
//
// MongoDB
// - No SQL — translates to: find / insertOne / updateMany / deleteMany / countDocuments
// - Aggregation pipeline for groupBy/having
// - No JOIN support (use preloads instead)
// - No transaction support

// Example: the same QB call emits different SQL per driver
User.query().where('email', '=', 'joe@example.com').get();

// SQLite/MySQL output:
// SELECT * FROM users WHERE email = ? AND deletedAt IS NULL
// params: ['joe@example.com']

// Postgres output:
// SELECT * FROM users WHERE email = $1 AND deletedAt IS NULL
// params: ['joe@example.com']

PreviousBatch Operations Nextselect()