bun-query-builder

Fully-typed, model-driven Query Builder for Bun’s native sql.

Define your data model once and get a type-safe query experience (a la Kysely/Laravel), powered by Bun’s tagged templates for safety and performance.

Features

Core Query Building

• Typed from Models: Infer tables/columns/PKs from your model files; selectFrom('users') and where({ active: true }) are typed.
• Fluent Builder: select/insert/update/delete, where/andWhere/orWhere, join/leftJoin/rightJoin/crossJoin, groupBy/having, union/unionAll.
• Aggregations: count(), avg(), sum(), max(), min() with full type safety.
• Batch Operations: insertMany(), updateMany(), deleteMany() for efficient bulk operations.

Advanced Features

• Relations: with(...), withCount(...), whereHas(...), has(), doesntHave(), selectAllRelations() with configurable aliasing and constraint callbacks.
• Query Scopes: Define reusable query constraints on models for cleaner, more maintainable code.
• Query Caching: Built-in LRU cache with TTL support via cache(ttlMs), clearQueryCache(), setQueryCacheMaxSize().
• Model Hooks: Lifecycle events - beforeCreate, afterCreate, beforeUpdate, afterUpdate, beforeDelete, afterDelete.

Utilities & Helpers

• Utilities: distinct/distinctOn, orderByDesc/latest/oldest/inRandomOrder, whereColumn/whereRaw/groupByRaw/havingRaw, JSON/date helpers.
• Pagination: paginate, simplePaginate, cursorPaginate, plus chunk/chunkById/eachById.
• Soft Deletes: withTrashed(), onlyTrashed() for logical deletion support.

Database Operations

• Transactions: transaction with retries/backoff/isolation/onRetry/afterCommit; savepoint; distributed tx helpers.
• Migrations: Generate and execute migrations from models with full diff support.
• Seeders: Database seeding with fake data generation via @stacksjs/ts-faker (faker alternative).
• Raw Queries: Tagged templates and parameterized queries with raw() and unsafe().

Configuration & Integration

• Configurable: Dialect hints, timestamps, alias strategies, relation FK formats, JSON mode, random function, shared lock syntax.
• Bun API passthroughs: unsafe, file, simple, pool reserve/release, close, ping/waitForReady.
• CLI: Introspection, query printing, connectivity checks, file/unsafe execution, explain.

Note: LISTEN/NOTIFY and COPY helpers are scaffolded and will be wired as Bun exposes native APIs.

Get Started

Installation

bun add bun-query-builder

Usage

import { buildDatabaseSchema, buildSchemaMeta, createQueryBuilder } from 'bun-query-builder'

// Load or define your model files (see docs for model shape)
const models = {
  User: { name: 'User', table: 'users', primaryKey: 'id', attributes: { id: { validation: { rule: {} } }, name: { validation: { rule: {} } }, active: { validation: { rule: {} } } } },
} as const

const schema = buildDatabaseSchema(models as any)
const meta = buildSchemaMeta(models as any)
const db = createQueryBuilder<typeof schema>({ schema, meta })

// Fully-typed query
const q = db
  .selectFrom('users')
  .where({ active: true })
  .orderBy('created_at', 'desc')
  .limit(10)

const rows = await q.execute()

Configuration

Drop a query-builder.config.ts in your project root. Every field is optional at every depth — supply only what you want to change, and the library defaults the rest:

import { defineConfig } from 'bun-query-builder'

export default defineConfig({
  dialect: 'postgres',
  database: { database: 'my_app' },
})

// Load it once at app boot, or configure from code with setConfig().
import { getConfig } from 'bun-query-builder'

await getConfig()

Aggregations

// Get average age of active users
const avgAge = await db.selectFrom('users')
  .where({ active: true })
  .avg('age')

// Count total posts
const totalPosts = await db.selectFrom('posts').count()

// Get max and min scores
const maxScore = await db.selectFrom('users').max('score')
const minScore = await db.selectFrom('users').min('score')

Batch Operations

// Insert multiple records at once
await db.insertMany('users', [
  { name: 'Alice', email: 'alice@example.com' },
  { name: 'Bob', email: 'bob@example.com' },
  { name: 'Charlie', email: 'charlie@example.com' },
])

// Update multiple records matching conditions
await db.updateMany('users', { verified: false }, { status: 'pending' })

// Delete multiple records by IDs
await db.deleteMany('users', [1, 2, 3, 4, 5])

Query Caching

// Cache query results for 60 seconds (default)
const users = await db.selectFrom('users')
  .where({ active: true })
  .cache()
  .get()

// Custom cache TTL (5 seconds)
const posts = await db.selectFrom('posts')
  .orderBy('created_at', 'desc')
  .limit(10)
  .cache(5000)
  .get()

// Clear all cached queries
clearQueryCache()

// Configure cache size
setQueryCacheMaxSize(500)

Model Hooks

const db = createQueryBuilder<typeof schema>({
  schema,
  meta,
  hooks: {
    beforeCreate: async ({ table, data }) => {
      console.log(`Creating ${table}:`, data)
      // Modify data, validate, or throw to prevent creation
    },
    afterCreate: async ({ table, data, result }) => {
      console.log(`Created ${table}:`, result)
      // Trigger notifications, update caches, etc.
    },
    beforeUpdate: async ({ table, data, where }) => {
      // Audit logging, validation, etc.
    },
    afterUpdate: async ({ table, data, where, result }) => {
      // Clear related caches, send webhooks, etc.
    },
    beforeDelete: async ({ table, where }) => {
      // Prevent deletion, check constraints, etc.
    },
    afterDelete: async ({ table, where, result }) => {
      // Clean up related data, update aggregates, etc.
    },
  }
})

Query Scopes

// Define scopes on your models
const User = {
  name: 'User',
  table: 'users',
  scopes: {
    active: (qb) => qb.where({ status: 'active' }),
    verified: (qb) => qb.where({ email_verified_at: ['IS NOT', null] }),
    premium: (qb) => qb.where({ subscription: 'premium' }),
  },
  // ... other model properties
}

// Use scopes in queries
const activeUsers = await db.selectFrom('users')
  .scope('active')
  .scope('verified')
  .get()

Relations with Constraints

// Eager load with constraints
const users = await db.selectFrom('users')
  .with({
    posts: (qb) => qb.where('published', '=', true).orderBy('created_at', 'desc')
  })
  .get()

// Check for related records
const usersWithPosts = await db.selectFrom('users')
  .has('posts')
  .get()

// Query by relationship existence
const activeAuthors = await db.selectFrom('users')
  .whereHas('posts', (qb) => qb.where('published', '=', true))
  .get()

Migrations

Generate and execute migrations from your models:

import { generateMigration, executeMigration } from 'bun-query-builder'

// Generate migration from models directory
const migration = await generateMigration('./models', {
  dialect: 'postgres',
  apply: true,
  full: true
})

// Execute the migration
await executeMigration(migration)

Database Seeding

Populate your database with test data using seeders powered by @stacksjs/ts-faker:

Creating a Seeder

# Generate a new seeder
bun qb make:seeder User

# This creates database/seeders/UserSeeder.ts

Writing a Seeder

import { Seeder } from 'bun-query-builder'
import { faker } from '@stacksjs/ts-faker'

export default class UserSeeder extends Seeder {
  async run(qb: any): Promise<void> {
    // Generate 50 users with realistic fake data
    const users = Array.from({ length: 50 }, () => ({
      name: faker.person.fullName(),
      email: faker.internet.email(),
      age: faker.number.int(18, 80),
      role: faker.helpers.arrayElement(['admin', 'user', 'moderator']),
      created_at: new Date(),
      updated_at: new Date(),
    }))

    await qb.table('users').insert(users).execute()
  }

  // Control execution order (lower runs first)
  get order(): number {
    return 10 // Default is 100
  }
}

Running Seeders

# Run all seeders
bun qb seed
bun qb db:seed

# Run a specific seeder
bun qb seed --class UserSeeder

# Drop all tables, re-run migrations and seed
bun qb db:fresh

Programmatic Usage

import { runSeeders, runSeeder } from 'bun-query-builder'

// Run all seeders
await runSeeders({
  seedersDir: './database/seeders',
  verbose: true
})

// Run specific seeder
await runSeeder('UserSeeder', { verbose: true })

CLI

# Model Generation
query-builder make:model User
query-builder make:model Post --table=blog_posts
query-builder model:show User                   # Show detailed model info

# Schema Introspection
query-builder introspect ./app/Models --verbose
query-builder sql ./app/Models users --limit 5

# Migrations
query-builder migrate ./app/Models --dialect postgres
query-builder migrate:generate                  # Generate migration from drift
query-builder migrate:status                    # Show migration status
query-builder migrate:list                      # List all migrations
query-builder migrate:rollback --steps 2        # Rollback migrations
query-builder migrate:fresh ./app/Models
query-builder reset ./app/Models

# Database Info
query-builder db:info                           # Show database statistics
query-builder db:stats                          # Alias for db:info
query-builder inspect users                     # Inspect table structure
query-builder table:info users                  # Alias for inspect
query-builder db:wipe --force                   # Drop all tables
query-builder db:optimize                       # Optimize database (VACUUM, ANALYZE)
query-builder db:optimize --aggressive          # Aggressive optimization

# Interactive Console
query-builder console                           # Start REPL
query-builder tinker                            # Alias for console

# Seeders
query-builder make:seeder User
query-builder seed
query-builder db:seed --class UserSeeder
query-builder db:fresh

# Data Management
query-builder export users --format json
query-builder export users --format csv --output users.csv
query-builder import users users.json --truncate
query-builder dump --tables users,posts

# Cache Management
query-builder cache:clear
query-builder cache:stats
query-builder cache:config --size 500

# Performance
query-builder benchmark --iterations 1000
query-builder benchmark --operations select,insert
query-builder query:explain-all ./queries        # Batch EXPLAIN analysis

# Schema Validation
query-builder validate:schema ./app/Models
query-builder check                             # Alias for validate:schema

# Connectivity
query-builder ping
query-builder wait-ready --attempts 30 --delay 250

# Execute SQL
query-builder file ./migrations/seed.sql
query-builder unsafe "SELECT * FROM users WHERE id = $1" --params "[1]"
query-builder explain "SELECT * FROM users WHERE active = true"

# Diagrams & Visualization
query-builder relation:diagram                   # Generate Mermaid ER diagram
query-builder relation:diagram --format dot     # Generate Graphviz DOT
query-builder relation:diagram --output schema.mmd

Performance

Benchmarked on Apple M3 Pro, Bun 1.3.11, SQLite with 1,000 users and 5,000 posts. Kysely uses the official kysely-bun-sqlite adapter.

Basic Queries

Advanced Queries

Batch Operations

Lowest time per benchmark is bolded. bun-query-builder wins 16 of 16 benchmarks. Full benchmark details →

Testing

bun test

Changelog

Please see our releases page for more information on what has changed recently.

Contributing

Please see CONTRIBUTING for details.

Community

For help, discussion about best practices, or any other conversation that would benefit from being searchable:

Discussions on GitHub

For casual chit-chat with others using this package:

Join the Stacks Discord Server

Postcardware

“Software that is free, but hopes for a postcard.” We love receiving postcards from around the world showing where Stacks is being used! We showcase them on our website too.

Our address: Stacks.js, 12665 Village Ln #2306, Playa Vista, CA 90094, United States 🌎

Sponsors

We would like to extend our thanks to the following sponsors for funding Stacks development. If you are interested in becoming a sponsor, please reach out to us.

• JetBrains
• The Solana Foundation

License

The MIT License (MIT). Please see LICENSE for more information.

Made with 💙