bun add bun-query-builder

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()

// 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')

// 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])

// 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)

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.
    },
  }
})

// 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()

// 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()

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)

# Generate a new seeder
bun qb make:seeder User

# This creates database/seeders/UserSeeder.ts

import { Seeder } from 'bun-query-builder'
import { faker } from 'ts-mocker'

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
  }
}

# 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

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 })

# 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