Database Query Builder

Overview

The Orchestr Query Builder provides a fluent, chainable interface for constructing and executing database queries. Inspired by Laravel's elegant syntax, it brings type-safe database operations to TypeScript with full IntelliSense support.

The Query Builder enables you to construct complex SQL queries using an intuitive, method-chaining API without writing raw SQL. It supports all major SQL operations including SELECT, INSERT, UPDATE, DELETE, joins, aggregates, and transactions.

Database Manager

The DatabaseManager orchestrates multiple database connections and manages their lifecycle throughout your application.

Configuration

Configure database connections in your application bootstrap:

import { Application, DatabaseServiceProvider } from '@orchestr-sh/orchestr';

const app = new Application(__dirname);

app.config.set('database', {
  default: 'mysql',
  connections: {
    mysql: {
      adapter: 'mysql',
      host: process.env.DB_HOST || 'localhost',
      port: parseInt(process.env.DB_PORT || '3306'),
      database: process.env.DB_NAME,
      username: process.env.DB_USER,
      password: process.env.DB_PASSWORD,
    },
    postgres: {
      adapter: 'postgres',
      host: 'localhost',
      port: 5432,
      database: 'my_database',
      username: 'postgres',
      password: 'secret',
    },
    sqlite: {
      adapter: 'sqlite',
      database: ':memory:',
    }
  }
});

app.register(DatabaseServiceProvider);

Connection Management

Access different database connections throughout your application:

import { DB } from '@orchestr-sh/orchestr';

// Use default connection
const users = await DB.table('users').get();

// Use specific connection
const logs = await DB.connection('postgres').table('logs').get();

// Switch connections mid-query
const data = await DB.connection('analytics')
  .table('events')
  .where('type', 'click')
  .get();

Connection Lifecycle

// Get current connection
const connection = DB.connection();

// Disconnect specific connection
await DB.disconnect('postgres');

// Disconnect all connections
await DB.disconnectAll();

// Get all configured connection names
const connections = DB.getConnections();
console.log(connections); // ['mysql', 'postgres', 'sqlite']

// Get/set default connection
const defaultConn = DB.getDefaultConnection(); // 'mysql'
DB.setDefaultConnection('postgres');

Basic Queries

Retrieving Data

Get All Records

import { DB } from '@orchestr-sh/orchestr';

// Get all users
const users = await DB.table('users').get();

// Get all with selected columns
const names = await DB.table('users')
  .select('id', 'name', 'email')
  .get();

Get First Record

// Get first matching record
const user = await DB.table('users')
  .where('email', 'john@example.com')
  .first();

if (!user) {
  console.log('User not found');
}

Find by Primary Key

// Find user by ID
const user = await DB.table('users').find(1);

// Returns null if not found
const missing = await DB.table('users').find(9999);

Get Single Value

// Get single column value from first row
const email = await DB.table('users')
  .where('id', 1)
  .value('email');

console.log(email); // 'john@example.com'

Pluck Column Values

// Get array of column values
const emails = await DB.table('users')
  .pluck('email');

console.log(emails);
// ['john@example.com', 'jane@example.com', ...]

// Pluck with key-value pairs
const userNames = await DB.table('users')
  .pluck('name', 'id');

console.log(userNames);
// { 1: 'John Doe', 2: 'Jane Smith', ... }

Selecting Columns

// Select specific columns
const users = await DB.table('users')
  .select('id', 'name', 'email')
  .get();

// Add additional columns
const users = await DB.table('users')
  .select('id', 'name')
  .addSelect('email', 'created_at')
  .get();

// Select distinct values
const statuses = await DB.table('orders')
  .select('status')
  .distinct()
  .get();

Raw Expressions

// Use raw SQL in select
const users = await DB.table('users')
  .select(DB.raw('count(*) as user_count'))
  .get();

// Raw expressions with bindings
const users = await DB.table('users')
  .selectRaw('UPPER(name) as upper_name')
  .selectRaw('price * ? as price_with_tax', [1.2])
  .get();

Checking Existence

// Check if records exist
const hasUsers = await DB.table('users')
  .where('status', 'active')
  .exists();

if (hasUsers) {
  console.log('Active users found');
}

// Check if records don't exist
const isEmpty = !(await DB.table('users').exists());

Query Constraints

Where Clauses

Basic Where Clauses

// Simple equality
const users = await DB.table('users')
  .where('status', 'active')
  .get();

// With operator
const users = await DB.table('users')
  .where('votes', '>', 100)
  .get();

// Multiple conditions (AND)
const users = await DB.table('users')
  .where('status', 'active')
  .where('votes', '>', 100)
  .get();

// Array of conditions
const users = await DB.table('users')
  .where([
    ['status', '=', 'active'],
    ['votes', '>', 100]
  ])
  .get();

Or Where Clauses

// OR conditions
const users = await DB.table('users')
  .where('status', 'active')
  .orWhere('votes', '>', 100)
  .get();

// Multiple OR conditions
const users = await DB.table('users')
  .where('name', 'John')
  .orWhere('name', 'Jane')
  .orWhere('name', 'Bob')
  .get();

Where In / Not In

// Where in array
const users = await DB.table('users')
  .whereIn('id', [1, 2, 3, 4, 5])
  .get();

// Where not in array
const users = await DB.table('users')
  .whereNotIn('status', ['banned', 'suspended'])
  .get();

// Or where in
const users = await DB.table('users')
  .where('role', 'admin')
  .orWhereIn('id', [1, 2, 3])
  .get();

Where Null / Not Null

// Where column is null
const users = await DB.table('users')
  .whereNull('deleted_at')
  .get();

// Where column is not null
const users = await DB.table('users')
  .whereNotNull('email_verified_at')
  .get();

// Or where null
const users = await DB.table('users')
  .where('status', 'active')
  .orWhereNull('last_login')
  .get();

Where Between / Not Between

// Where between range
const users = await DB.table('users')
  .whereBetween('votes', [1, 100])
  .get();

// Where not between
const users = await DB.table('users')
  .whereNotBetween('age', [18, 65])
  .get();

// Between dates
const orders = await DB.table('orders')
  .whereBetween('created_at', [
    '2024-01-01',
    '2024-12-31'
  ])
  .get();

Where Date Comparisons

// Where date equals
const posts = await DB.table('posts')
  .whereDate('created_at', '2024-01-15')
  .get();

// Where year, month, day
const posts = await DB.table('posts')
  .whereYear('created_at', 2024)
  .whereMonth('created_at', 1)
  .get();

// Where time
const logs = await DB.table('logs')
  .whereTime('created_at', '>', '12:00:00')
  .get();

Raw Where Clauses

// Raw where condition
const users = await DB.table('users')
  .whereRaw('age > ? and votes < ?', [18, 100])
  .get();

// Or where raw
const users = await DB.table('users')
  .where('status', 'active')
  .orWhereRaw('votes > 100')
  .get();

Grouped Where Clauses

// Complex grouped conditions
const users = await DB.table('users')
  .where('name', 'John')
  .where((query) => {
    query.where('votes', '>', 100)
         .orWhere('title', 'Admin');
  })
  .get();

// SQL: SELECT * FROM users
//      WHERE name = 'John'
//      AND (votes > 100 OR title = 'Admin')

// Nested groups
const products = await DB.table('products')
  .where((query) => {
    query.where('category', 'electronics')
         .where((subQuery) => {
           subQuery.where('price', '<', 1000)
                   .orWhere('on_sale', true);
         });
  })
  .get();

Ordering & Limiting

Order By

// Order by single column ascending
const users = await DB.table('users')
  .orderBy('name')
  .get();

// Order by descending
const users = await DB.table('users')
  .orderBy('created_at', 'desc')
  .get();

// Multiple order by
const users = await DB.table('users')
  .orderBy('status', 'asc')
  .orderBy('name', 'asc')
  .get();

// Latest and oldest helpers
const latestUsers = await DB.table('users')
  .latest('created_at')
  .get();

const oldestPosts = await DB.table('posts')
  .oldest('published_at')
  .get();

Limit & Offset

// Limit results
const users = await DB.table('users')
  .limit(10)
  .get();

// Skip and take (pagination)
const users = await DB.table('users')
  .skip(20)
  .take(10)
  .get();

// Offset and limit
const users = await DB.table('users')
  .offset(50)
  .limit(25)
  .get();

// Simple pagination
const page = 3;
const perPage = 15;
const users = await DB.table('users')
  .offset((page - 1) * perPage)
  .limit(perPage)
  .get();

Random Ordering

// Get random records
const randomUsers = await DB.table('users')
  .inRandomOrder()
  .limit(5)
  .get();

Aggregates

Count

// Count all records
const count = await DB.table('users').count();
console.log(count); // 150

// Count with conditions
const activeCount = await DB.table('users')
  .where('status', 'active')
  .count();

// Count specific column
const verifiedCount = await DB.table('users')
  .count('email_verified_at');

// Count distinct
const uniqueStatuses = await DB.table('orders')
  .countDistinct('status');

Sum

// Sum column values
const totalSales = await DB.table('orders')
  .sum('amount');

console.log(totalSales); // 15420.50

// Sum with conditions
const januarySales = await DB.table('orders')
  .whereMonth('created_at', 1)
  .sum('amount');

Average

// Calculate average
const avgPrice = await DB.table('products')
  .avg('price');

// Average with conditions
const avgRating = await DB.table('reviews')
  .where('verified', true)
  .avg('rating');

Min & Max

// Find minimum value
const minPrice = await DB.table('products')
  .min('price');

// Find maximum value
const maxPrice = await DB.table('products')
  .max('price');

// With conditions
const cheapestBook = await DB.table('products')
  .where('category', 'books')
  .min('price');

Group By & Having

// Group by with aggregates
const stats = await DB.table('orders')
  .select('status')
  .selectRaw('count(*) as count')
  .selectRaw('sum(amount) as total')
  .groupBy('status')
  .get();

// Having clause for filtered groups
const popularProducts = await DB.table('order_items')
  .select('product_id')
  .selectRaw('count(*) as order_count')
  .groupBy('product_id')
  .having('order_count', '>', 10)
  .get();

// Multiple group by columns
const sales = await DB.table('orders')
  .select('year', 'month')
  .selectRaw('sum(amount) as total')
  .groupBy('year', 'month')
  .orderBy('year', 'desc')
  .orderBy('month', 'desc')
  .get();

Joins

Inner Join

// Basic inner join
const users = await DB.table('users')
  .join('contacts', 'users.id', '=', 'contacts.user_id')
  .select('users.*', 'contacts.phone', 'contacts.email')
  .get();

// Multiple joins
const orders = await DB.table('orders')
  .join('users', 'orders.user_id', '=', 'users.id')
  .join('products', 'orders.product_id', '=', 'products.id')
  .select(
    'orders.*',
    'users.name as user_name',
    'products.name as product_name'
  )
  .get();

Left Join

// Left outer join
const users = await DB.table('users')
  .leftJoin('posts', 'users.id', '=', 'posts.user_id')
  .select('users.name', 'posts.title')
  .get();

// Left join with conditions
const users = await DB.table('users')
  .leftJoin('orders', (join) => {
    join.on('users.id', '=', 'orders.user_id')
        .where('orders.status', 'completed');
  })
  .select('users.*')
  .get();

Right Join

// Right outer join
const data = await DB.table('posts')
  .rightJoin('users', 'posts.user_id', '=', 'users.id')
  .select('users.name', 'posts.title')
  .get();

Cross Join

// Cross join (Cartesian product)
const combinations = await DB.table('colors')
  .crossJoin('sizes')
  .get();

Advanced Join Clauses

// Join with multiple conditions
const users = await DB.table('users')
  .join('contacts', (join) => {
    join.on('users.id', '=', 'contacts.user_id')
        .on('users.account_id', '=', 'contacts.account_id')
        .orOn('users.email', '=', 'contacts.email');
  })
  .get();

// Join with where clauses
const posts = await DB.table('posts')
  .join('users', (join) => {
    join.on('posts.user_id', '=', 'users.id')
        .where('users.status', 'active')
        .where('posts.published', true);
  })
  .get();

Sub-Query Joins

// Join with subquery
const latestPosts = DB.table('posts')
  .select('user_id')
  .selectRaw('MAX(created_at) as last_post_date')
  .groupBy('user_id');

const users = await DB.table('users')
  .joinSub(latestPosts, 'latest_posts', (join) => {
    join.on('users.id', '=', 'latest_posts.user_id');
  })
  .select('users.*', 'latest_posts.last_post_date')
  .get();

Inserts

Simple Insert

// Insert single record
await DB.table('users').insert({
  name: 'John Doe',
  email: 'john@example.com',
  created_at: new Date()
});

// Insert multiple records
await DB.table('users').insert([
  { name: 'John', email: 'john@example.com' },
  { name: 'Jane', email: 'jane@example.com' },
  { name: 'Bob', email: 'bob@example.com' }
]);

Insert and Get ID

// Insert and return auto-increment ID
const userId = await DB.table('users').insertGetId({
  name: 'John Doe',
  email: 'john@example.com'
});

console.log(userId); // 42

// With custom sequence (PostgreSQL)
const id = await DB.table('users').insertGetId(
  { name: 'John Doe' },
  'user_id_seq'
);

Insert or Ignore

// Insert and ignore duplicates
await DB.table('users').insertOrIgnore([
  { id: 1, name: 'John' },
  { id: 2, name: 'Jane' }
]);

Upserts (Insert or Update)

// Insert or update on conflict
await DB.table('users').upsert(
  [
    { email: 'john@example.com', name: 'John Doe', votes: 0 },
    { email: 'jane@example.com', name: 'Jane Smith', votes: 0 }
  ],
  ['email'], // Unique columns
  ['name', 'votes'] // Columns to update on conflict
);

// Update all columns except unique identifiers
await DB.table('users').upsert(
  { email: 'john@example.com', name: 'John Updated' },
  ['email']
);

Updates

Basic Update

// Update records matching condition
await DB.table('users')
  .where('id', 1)
  .update({
    name: 'John Updated',
    updated_at: new Date()
  });

// Update multiple records
await DB.table('users')
  .where('status', 'pending')
  .update({ status: 'active' });

Update with Increment/Decrement

// Increment column value
await DB.table('users')
  .where('id', 1)
  .increment('votes');

// Increment by amount
await DB.table('users')
  .where('id', 1)
  .increment('votes', 5);

// Decrement
await DB.table('users')
  .where('id', 1)
  .decrement('credits', 10);

// Increment with additional updates
await DB.table('users')
  .where('id', 1)
  .increment('votes', 1, {
    last_vote_at: new Date()
  });

Update or Insert

// Update existing or insert new
await DB.table('users').updateOrInsert(
  { email: 'john@example.com' }, // Search criteria
  { name: 'John Doe', votes: 100 } // Values to update/insert
);

Deletes

Basic Delete

// Delete matching records
await DB.table('users')
  .where('votes', '<', 10)
  .delete();

// Delete by ID
await DB.table('users')
  .where('id', 1)
  .delete();

// Delete with multiple conditions
await DB.table('users')
  .where('status', 'inactive')
  .where('last_login', '<', '2023-01-01')
  .delete();

Truncate Table

// Remove all records and reset auto-increment
await DB.table('users').truncate();

// Warning: This cannot be undone and is faster than delete

Raw Expressions

Creating Raw Expressions

import { DB } from '@orchestr-sh/orchestr';

// Create raw expression
const raw = DB.raw('NOW()');

// Raw with bindings (safe from SQL injection)
const raw = DB.raw('price * ? as total_price', [1.2]);

Raw Selects

// Raw select expression
const users = await DB.table('users')
  .selectRaw('count(*) as user_count, status')
  .groupBy('status')
  .get();

// Raw with calculations
const products = await DB.table('products')
  .selectRaw('price * quantity as total_value')
  .selectRaw('UPPER(name) as upper_name')
  .get();

// Complex raw expressions
const stats = await DB.table('orders')
  .selectRaw(`
    DATE_TRUNC('month', created_at) as month,
    SUM(amount) as revenue,
    COUNT(*) as order_count,
    AVG(amount) as avg_order_value
  `)
  .groupByRaw('DATE_TRUNC('month', created_at)')
  .orderByRaw('month DESC')
  .get();

Raw Where Clauses

// Raw where conditions
const users = await DB.table('users')
  .whereRaw('age > ?', [18])
  .whereRaw('votes > 100 OR is_admin = ?', [true])
  .get();

// Raw where for complex logic
const results = await DB.table('sales')
  .whereRaw('amount > (SELECT AVG(amount) FROM sales)')
  .get();

Raw Joins

// Raw join condition
const users = await DB.table('users')
  .join('contacts', DB.raw('LOWER(users.email) = LOWER(contacts.email)'))
  .get();

Raw Order By

// Raw ordering
const users = await DB.table('users')
  .orderByRaw('FIELD(status, ?, ?, ?)', ['active', 'pending', 'inactive'])
  .get();

// Custom sorting logic
const products = await DB.table('products')
  .orderByRaw('CASE WHEN featured = true THEN 0 ELSE 1 END')
  .orderBy('name')
  .get();

Complete Raw Queries

// Execute completely raw query
const results = await DB.raw(`
  SELECT u.name, COUNT(o.id) as order_count
  FROM users u
  LEFT JOIN orders o ON u.id = o.user_id
  WHERE u.status = ?
  GROUP BY u.name
  HAVING COUNT(o.id) > ?
  ORDER BY order_count DESC
`, ['active', 5]);

// Raw query with no bindings
const results = await DB.raw('SELECT * FROM users WHERE status = 'active'');

Transactions

Transactions ensure data consistency by grouping multiple database operations into a single atomic unit. If any operation fails, all changes are rolled back.

Basic Transaction

import { DB } from '@orchestr-sh/orchestr';

// Automatic transaction management
await DB.transaction(async (trx) => {
  // All queries within this callback use the transaction
  await trx.table('users').insert({
    name: 'John Doe',
    email: 'john@example.com'
  });

  const userId = await trx.table('users')
    .where('email', 'john@example.com')
    .value('id');

  await trx.table('profiles').insert({
    user_id: userId,
    bio: 'Hello World'
  });

  // If we reach here without errors, transaction commits
  // If any error is thrown, transaction rolls back automatically
});

Manual Transaction Control

// Begin transaction manually
const trx = await DB.beginTransaction();

try {
  await trx.table('accounts')
    .where('id', 1)
    .decrement('balance', 100);

  await trx.table('accounts')
    .where('id', 2)
    .increment('balance', 100);

  await trx.table('transactions').insert({
    from_account: 1,
    to_account: 2,
    amount: 100
  });

  // Commit transaction
  await trx.commit();

} catch (error) {
  // Rollback on error
  await trx.rollback();
  throw error;
}

Nested Transactions (Savepoints)

await DB.transaction(async (trx) => {
  await trx.table('users').insert({ name: 'John' });

  // Create savepoint
  await trx.transaction(async (trx2) => {
    await trx2.table('posts').insert({
      user_id: 1,
      title: 'First Post'
    });

    // If this throws, only inner transaction rolls back
    if (someCondition) {
      throw new Error('Post validation failed');
    }
  });

  // Outer transaction continues
  await trx.table('profiles').insert({ user_id: 1 });
});

Transaction with Return Value

// Return value from transaction
const user = await DB.transaction(async (trx) => {
  const userId = await trx.table('users').insertGetId({
    name: 'John Doe',
    email: 'john@example.com'
  });

  await trx.table('profiles').insert({
    user_id: userId,
    bio: 'New user'
  });

  // Return the created user
  return trx.table('users').find(userId);
});

console.log(user.name); // 'John Doe'

Transaction Isolation Levels

// Set transaction isolation level
await DB.transaction(async (trx) => {
  // Your queries here
}, {
  isolationLevel: 'READ COMMITTED'
  // Options: READ UNCOMMITTED, READ COMMITTED,
  //          REPEATABLE READ, SERIALIZABLE
});

Type Safety

Typed Query Results

interface User {
  id: number;
  name: string;
  email: string;
  created_at: Date;
}

// Type-safe query results
const users = await DB.table<User>('users').get();
// users is typed as User[]

const user = await DB.table<User>('users').find(1);
// user is typed as User | null

// Type-safe column selection
const emails = await DB.table<User>('users').pluck<string>('email');
// emails is typed as string[]

Generic Query Builder

class UserRepository {
  private query() {
    return DB.table<User>('users');
  }

  async findActive(): Promise<User[]> {
    return this.query()
      .where('status', 'active')
      .orderBy('name')
      .get();
  }

  async findByEmail(email: string): Promise<User | null> {
    return this.query()
      .where('email', email)
      .first();
  }

  async create(data: Omit<User, 'id'>): Promise<number> {
    return this.query().insertGetId(data);
  }
}

Best Practices

1. Prevent N+1 Queries

// BAD: N+1 query problem
const users = await DB.table('users').get();
for (const user of users) {
  const posts = await DB.table('posts')
    .where('user_id', user.id)
    .get();
  user.posts = posts;
}

// GOOD: Single query with join
const usersWithPosts = await DB.table('users')
  .leftJoin('posts', 'users.id', '=', 'posts.user_id')
  .select(
    'users.*',
    DB.raw('JSON_AGG(posts.*) as posts')
  )
  .groupBy('users.id')
  .get();

// ALTERNATIVE: Two queries (better for large datasets)
const users = await DB.table('users').get();
const userIds = users.map(u => u.id);
const posts = await DB.table('posts')
  .whereIn('user_id', userIds)
  .get();

// Group posts by user_id
const postsByUser = posts.reduce((acc, post) => {
  if (!acc[post.user_id]) acc[post.user_id] = [];
  acc[post.user_id].push(post);
  return acc;
}, {});

users.forEach(user => {
  user.posts = postsByUser[user.id] || [];
});

2. Use Indexes

// Always add indexes for columns used in WHERE, JOIN, ORDER BY

// In your migrations:
await DB.raw(`
  CREATE INDEX idx_users_email ON users(email);
  CREATE INDEX idx_posts_user_id ON posts(user_id);
  CREATE INDEX idx_orders_created_at ON orders(created_at);
  CREATE INDEX idx_products_category_status ON products(category, status);
`);

// Verify index usage with EXPLAIN
const plan = await DB.raw('EXPLAIN ANALYZE SELECT * FROM users WHERE email = ?',
  ['john@example.com']
);

3. Use Transactions for Data Integrity

// Always use transactions for related operations
await DB.transaction(async (trx) => {
  const orderId = await trx.table('orders').insertGetId({
    user_id: userId,
    total: 0
  });

  let total = 0;
  for (const item of items) {
    await trx.table('order_items').insert({
      order_id: orderId,
      product_id: item.product_id,
      quantity: item.quantity,
      price: item.price
    });
    total += item.quantity * item.price;
  }

  await trx.table('orders')
    .where('id', orderId)
    .update({ total });
});

4. Avoid SELECT *

// BAD: Fetches unnecessary data
const users = await DB.table('users').get();

// GOOD: Select only needed columns
const users = await DB.table('users')
  .select('id', 'name', 'email')
  .get();

// Reduces memory usage and network transfer

5. Use Query Builder Methods Over Raw SQL

// BAD: SQL injection risk
const email = req.body.email;
const user = await DB.raw(`SELECT * FROM users WHERE email = '${email}'`);

// GOOD: Safe from SQL injection
const user = await DB.table('users')
  .where('email', email)
  .first();

// Raw SQL with bindings is also safe
const user = await DB.raw('SELECT * FROM users WHERE email = ?', [email]);

6. Batch Operations

// BAD: Multiple queries in loop
for (const user of users) {
  await DB.table('notifications').insert({
    user_id: user.id,
    message: 'Welcome!'
  });
}

// GOOD: Single batch insert
await DB.table('notifications').insert(
  users.map(user => ({
    user_id: user.id,
    message: 'Welcome!'
  }))
);

7. Use Proper Data Types

// Store dates as Date objects
await DB.table('users').insert({
  created_at: new Date(),
  birth_date: new Date('1990-01-15')
});

// Store JSON data properly
await DB.table('settings').insert({
  user_id: 1,
  preferences: JSON.stringify({ theme: 'dark', notifications: true })
});

// Store booleans correctly
await DB.table('users').insert({
  is_active: true,
  email_verified: false
});

8. Query Optimization

// Use EXISTS instead of COUNT for existence checks
// BAD
const count = await DB.table('posts')
  .where('user_id', userId)
  .count();
const hasPosts = count > 0;

// GOOD
const hasPosts = await DB.table('posts')
  .where('user_id', userId)
  .exists();

// Use LIMIT when you only need a few records
const recentPosts = await DB.table('posts')
  .orderBy('created_at', 'desc')
  .limit(10)
  .get();

// Use SELECT with aggregates efficiently
const stats = await DB.table('orders')
  .select(DB.raw('COUNT(*) as count, SUM(total) as revenue'))
  .where('status', 'completed')
  .first();

9. Connection Pooling

// Configure connection pool in your database config
app.config.set('database', {
  default: 'mysql',
  connections: {
    mysql: {
      adapter: 'mysql',
      host: 'localhost',
      database: 'mydb',
      username: 'root',
      password: 'secret',
      pool: {
        min: 2,
        max: 10,
        acquireTimeoutMillis: 30000,
        idleTimeoutMillis: 30000
      }
    }
  }
});

// Always close connections when shutting down
process.on('SIGTERM', async () => {
  await DB.disconnectAll();
  process.exit(0);
});

10. Query Logging and Monitoring

// Enable query logging in development
if (process.env.NODE_ENV === 'development') {
  DB.enableQueryLog();

  // Log queries after execution
  DB.listen('query', (query) => {
    console.log('Query:', query.sql);
    console.log('Bindings:', query.bindings);
    console.log('Time:', query.time, 'ms');
  });
}

// Monitor slow queries
DB.listen('query', (query) => {
  if (query.time > 1000) { // Slower than 1 second
    console.warn('Slow query detected:', {
      sql: query.sql,
      time: query.time,
      bindings: query.bindings
    });
  }
});

Conclusion

The Orchestr Query Builder provides a powerful, expressive interface for database operations while maintaining Laravel's elegant syntax. By following the best practices outlined above and leveraging TypeScript's type safety, you can build robust, performant database layers for your applications.

For more advanced patterns including the Ensemble ORM, soft deletes, and model relationships, see the ORM Documentation.

Additional Resources

• Database Configuration
• Migrations
• Ensemble ORM
• GitHub Repository
• npm Package