Stacks Query Builder

Stacks Query Builder

Key Paths

• Core package: storage/framework/core/query-builder/src/
• Configuration: config/query-builder.ts
• QB state: .qb/
• External library: bun-query-builder
• Package: @stacksjs/query-builder

API

The package re-exports everything from bun-query-builder plus a compatibility alias:

export * from 'bun-query-builder'
export { createQueryBuilder as QueryBuilder } from 'bun-query-builder'

Usage

import { db } from '@stacksjs/database'

// Select
const users = await db.selectFrom('users')
  .where('active', '=', true)
  .orderBy('name', 'asc')
  .limit(10)
  .get()

// Insert
await db.insertInto('users')
  .values({ name: 'Alice', email: 'alice@example.com' })
  .execute()

// Update
await db.update('users')
  .set({ active: false })
  .where('id', '=', 1)
  .execute()

// Delete
await db.deleteFrom('users')
  .where('id', '=', 1)
  .execute()

// Joins
const posts = await db.selectFrom('posts')
  .join('users', 'posts.user_id', '=', 'users.id')
  .select(['posts.*', 'users.name as author_name'])
  .get()

// Aggregates
const count = await db.selectFrom('users').count()
const total = await db.selectFrom('orders').sum('amount')

Production SQLite uses a compact deferred builder for the common select().where().limit().execute() chain when no query hooks or global soft-delete scope is active. Complex methods and arguments automatically replay onto the complete upstream builder. Use the normal API in either case; do not fork application code into a separate raw-query path for this optimization.

Configuration (config/query-builder.ts)

{
  verbose: true,
  dialect: env.DB_CONNECTION || 'sqlite',
  database: { database, username?, password?, host?, port? },

  timestamps: {
    createdAt: 'created_at',
    updatedAt: 'updated_at',
    defaultOrderColumn: 'created_at',
  },

  pagination: {
    defaultPerPage: 25,
    cursorColumn: 'id',
  },

  aliasing: {
    relationColumnAliasFormat: 'table_column',
  },

  relations: {
    foreignKeyFormat: 'singularParent_id',
    maxDepth: 10,
    maxEagerLoad: 50,
    detectCycles: true,
  },

  transactionDefaults: {
    retries: 2,
    isolation: 'read committed',
    sqlStates: ['40001', '40P01'],
    backoff: { baseMs: 50, factor: 2, maxMs: 2000, jitter: true },
  },

  sql: {
    randomFunction: 'RANDOM()',
    sharedLockSyntax: 'FOR SHARE',
    jsonContainsMode: 'operator',
  },

  features: { distinctOn: true },
  debug: { captureText: true },

  softDeletes: {
    enabled: false,
    column: 'deleted_at',
    defaultFilter: true,
  },
}

Gotchas

• Thin wrapper — re-exports bun-query-builder with no additions
• One config file — config/query-builder.ts. Its dialect and connection details are derived from DB_CONNECTION and the other DB_* env vars, so switching databases is an env change, not a config edit
• .qb/ directory — stores query builder state for migration diffing
• Prefer ORM models — query builder is the low-level interface; use models for most operations
• db proxy — the db export from @stacksjs/database is a lazy proxy that auto-initializes the query builder on first access
• Soft deletes disabled by default — must be explicitly enabled in config
• Transaction retries — defaults to 2 retries with exponential backoff + jitter
• Dialect detection — reads DB_CONNECTION env var, falls back to sqlite