📦
Bunty

Query Builder

The Bunty ORM Query Builder provides a fluent, type-safe interface for constructing complex database queries. Built on top of Bun’s native SQL drivers, it offers full TypeScript integration with automatic type inference and database-specific optimizations.

Overview

The Query Builder combines the power of raw SQL with the safety and convenience of an ORM:

  • Type Safety: Full TypeScript integration with compile-time query validation
  • Fluent Interface: Chainable methods for intuitive query construction
  • Database Agnostic: Write once, run on MySQL, PostgreSQL, or SQLite
  • Performance: Compiles to optimized native driver calls
  • IntelliSense Support: Full autocompletion for tables, columns, and methods
  • Raw SQL Escape Hatch: Drop down to raw SQL when needed

Basic Query Structure

All queries follow a consistent pattern across database types:

basic-select.ts 
import { db } from './db';

// Basic select with type inference
const users = await db
.select()
.from('users')
.where('active', '=', true)
.orderBy('created_at', 'desc')
.limit(10);

// TypeScript knows the shape of users[]
users.forEach(user => {
console.log(user.id, user.name, user.email);
});

SELECT Queries

The SELECT query builder offers comprehensive functionality for data retrieval:

Basic Selection

select-columns.ts 
// Select all columns
const users = await db.select().from('users');

// Select specific columns
const users = await db
.select(['id', 'name', 'email'])
.from('users');

// Select with aliases
const users = await db
.select({
  userId: 'id',
  fullName: 'name',
  emailAddress: 'email',
  isActive: 'active'
})
.from('users');

// Select with expressions
const stats = await db
.select({
  total: db.raw('COUNT(*)'),
  avgAge: db.raw('AVG(age)'),
  maxSalary: db.raw('MAX(salary)')
})
.from('users');

Advanced Selection

aggregation.ts 
// Group by with aggregates
const userStats = await db
.select({
  department: 'department',
  totalEmployees: db.raw('COUNT(*)'),
  avgSalary: db.raw('AVG(salary)'),
  maxSalary: db.raw('MAX(salary)'),
  minSalary: db.raw('MIN(salary)')
})
.from('employees')
.groupBy('department')
.having(db.raw('COUNT(*)'), '>', 5)
.orderBy('avgSalary', 'desc');

// Window functions (PostgreSQL/MySQL 8.0+)
const rankedUsers = await db
.select({
  id: 'id',
  name: 'name',
  salary: 'salary',
  rank: db.raw('ROW_NUMBER() OVER (ORDER BY salary DESC)'),
  departmentRank: db.raw('RANK() OVER (PARTITION BY department ORDER BY salary DESC)')
})
.from('employees');

// Subqueries
const highEarners = await db
.select()
.from('users')
.where('salary', '>', builder => {
  builder
    .select(db.raw('AVG(salary)'))
    .from('users')
    .where('department', '=', 'engineering');
});

INSERT Operations

The INSERT builder supports single records, bulk inserts, and upsert operations:

single-insert.ts 
// Basic insert
const user = await db
.insert({
  name: 'John Doe',
  email: 'john@example.com',
  active: true,
  created_at: new Date()
})
.into('users');

// Insert with returning
const newUser = await db
.insert({
  name: 'Jane Smith',
  email: 'jane@example.com'
})
.into('users')
.returning('*');

// Insert with specific columns returned
const userId = await db
.insert({
  name: 'Bob Wilson',
  email: 'bob@example.com'
})
.into('users')
.returning('id');

// Insert with default values
const user = await db
.insert({
  name: 'Alice Johnson',
  email: 'alice@example.com'
  // created_at will use database default
})
.into('users')
.returning(['id', 'created_at']);

UPDATE Operations

UPDATE queries support conditional updates, joins, and atomic operations:

basic-update.ts 
// Simple update
await db
.update('users')
.set({
  name: 'Updated Name',
  updated_at: new Date()
})
.where('id', '=', userId);

// Update with expressions
await db
.update('users')
.set({
  login_count: db.raw('login_count + 1'),
  last_login: new Date(),
  updated_at: db.raw('NOW()')
})
.where('id', '=', userId);

// Conditional update
await db
.update('posts')
.set({ status: 'published', published_at: new Date() })
.where('status', '=', 'draft')
.where('scheduled_for', '<=', new Date());

// Update with returning
const updatedUser = await db
.update('users')
.set({ last_seen: new Date() })
.where('id', '=', userId)
.returning(['id', 'name', 'last_seen']);

DELETE Operations

DELETE operations support soft deletes, cascading deletes, and cleanup operations:

basic-delete.ts 
// Simple delete
await db
.delete()
.from('users')
.where('id', '=', userId);

// Delete with conditions
await db
.delete()
.from('posts')
.where('status', '=', 'draft')
.where('created_at', '<', thirtyDaysAgo);

// Delete with returning (PostgreSQL)
const deletedUsers = await db
.delete()
.from('users')
.where('active', '=', false)
.where('last_login', '<', oneYearAgo)
.returning(['id', 'email']);

// Delete with limit (MySQL)
await db
.delete()
.from('logs')
.where('created_at', '<', oneWeekAgo)
.orderBy('created_at', 'asc')
.limit(1000);

Raw SQL and Advanced Features

When you need the full power of SQL, the query builder provides escape hatches:

raw-sql.ts 
// Raw expressions in SELECT
const results = await db
.select({
  id: 'id',
  name: 'name',
  age: db.raw('YEAR(CURDATE()) - YEAR(birth_date)'),
  rank: db.raw('ROW_NUMBER() OVER (ORDER BY salary DESC)'),
  percentile: db.raw('PERCENT_RANK() OVER (ORDER BY salary)')
})
.from('employees');

// Raw WHERE conditions
const users = await db
.select()
.from('users')
.whereRaw('MATCH(name, bio) AGAINST(? IN BOOLEAN MODE)', [searchTerm])
.whereRaw('created_at >= DATE_SUB(NOW(), INTERVAL ? DAY)', [30]);

// Complex raw queries
const analyticsData = await db
.select(db.raw(`
  DATE(created_at) as date,
  COUNT(*) as total_orders,
  SUM(total) as revenue,
  AVG(total) as avg_order_value,
  COUNT(DISTINCT user_id) as unique_customers
`))
.from('orders')
.whereRaw('created_at >= ?', [startDate])
.groupByRaw('DATE(created_at)')
.orderByRaw('DATE(created_at) DESC');

Query Optimization

The query builder includes several optimization features:

Query Analysis

explain.ts 
// Analyze query performance
const query = db
.select()
.from('users')
.join('orders', 'users.id', '=', 'orders.user_id')
.where('users.active', '=', true)
.where('orders.total', '>', 100);

// Get execution plan
const plan = await query.explain();
console.log('Execution plan:', plan);

// Get actual execution statistics
const stats = await query.analyze();
console.log('Query stats:', stats);

// Debug query compilation
const compiledQuery = query.toSQL();
console.log('SQL:', compiledQuery.sql);
console.log('Bindings:', compiledQuery.bindings);

// Performance monitoring
const startTime = Date.now();
const results = await query;
const duration = Date.now() - startTime;
console.log(`Query completed in ${duration}ms`);

Error Handling and Debugging

Comprehensive error handling and debugging capabilities:

error-handling.ts 
// Basic error handling
try {
const user = await db
  .select()
  .from('users')
  .where('id', '=', userId)
  .first();
  
if (!user) {
  throw new Error('User not found');
}

return user;
} catch (error) {
if (error.code === 'ER_NO_SUCH_TABLE') {
  console.error('Table does not exist');
} else if (error.code === 'ER_DUP_ENTRY') {
  console.error('Duplicate entry');
} else {
  console.error('Database error:', error.message);
}
throw error;
}

// Constraint violation handling
try {
await db.insert({
  email: 'user@example.com',
  name: 'John Doe'
}).into('users');
} catch (error) {
if (error.constraint === 'users_email_unique') {
  throw new Error('Email already exists');
}
if (error.constraint === 'users_name_not_null') {
  throw new Error('Name is required');
}
throw error;
}

// Validation before database operations
async function createUser(userData: any) {
// Validate required fields
if (!userData.email || !userData.name) {
  throw new Error('Email and name are required');
}

// Check if email already exists
const existing = await db
  .select('id')
  .from('users')
  .where('email', '=', userData.email)
  .first();
  
if (existing) {
  throw new Error('Email already registered');
}

return db.insert(userData).into('users').returning('*');
}

Performance Best Practices

Guidelines for optimal query performance:

Query Optimization Tips

  1. Use Indexes Effectively

    • Create indexes on frequently queried columns
    • Use composite indexes for multi-column conditions
    • Avoid over-indexing (impacts insert/update performance)

  2. Limit Result Sets

    • Always use LIMIT for pagination
    • Select only needed columns
    • Use WHERE clauses to filter early

  3. Optimize JOIN Operations

    • Ensure JOIN columns are indexed
    • Use appropriate JOIN types
    • Consider denormalization for read-heavy workloads

  4. Batch Operations

    • Use bulk inserts instead of multiple single inserts
    • Batch updates when possible
    • Use transactions for related operations

  5. Query Structure

    • Avoid SELECT * in production
    • Use EXISTS instead of IN for large datasets
    • Consider using WITH clauses for complex queries

Connection Management

  • Use connection pooling in production
  • Monitor connection pool utilization
  • Set appropriate timeout values
  • Close connections properly

What’s Next?

Now that you understand the Query Builder, explore these related topics:

Have questions? Join our Discord community
Found an issue? Edit this page on GitHub