v1.13.0

What's Changed

• Add DISTINCT support
• Add additional JOIN types (RIGHT, FULL, CROSS, NATURAL)
• Add UNION/INTERSECT/EXCEPT set operations
• Add CTEs (WITH clause) support
• Add toSQL() and toDebugSQL() for query inspection
• Add pagination helper
• Add EXPLAIN support
• Add transaction support
• Add query hooks (beforeQuery/afterQuery)
• Improve error messages with context and hints

DISTINCT Support

Select unique rows with .distinct(), with PostgreSQL DISTINCT ON support:

// Simple DISTINCT
const uniqueEmails = await qb.select('users').distinct().fields(['email']).all();

Additional JOIN Types

New convenience methods for all JOIN types:

const result = await qb.select('users')
  .innerJoin({table: 'orders', on: 'users.id = orders.user_id'})
  .leftJoin({table: 'profiles', on: 'users.id = profiles.user_id'})
  .rightJoin({table: 'teams', on: 'users.team_id = teams.id'})
  .fullJoin({table: 'projects', on: 'users.id = projects.owner_id'})
  .naturalJoin('departments')
  .all();

Set Operations (UNION/INTERSECT/EXCEPT)

Combine query results with set operations:

const allUsers = await qb.select('active_users').fields(['id', 'name'])
  .union(qb.select('archived_users').fields(['id', 'name']))
  .orderBy({name: 'ASC'})
  .all();

const commonIds = await qb.select('users').fields(['id'])
  .intersect(qb.select('admins').fields(['user_id']))
  .all();

CTEs (WITH Clause)

Define reusable subqueries with Common Table Expressions:

const result = await qb.select('orders')
  .with('active_users', qb.select('users').where('status = ?', 'active'))
  .innerJoin({table: 'active_users', on: 'orders.user_id = active_users.id'})
  .all();

Query Inspection

Debug queries without executing them:

const {sql, params} = qb.select('users').where('id = ?', 1).toSQL();
// sql: "SELECT * FROM users WHERE id = ?"
// params: [1]

const debugSql = qb.select('users').where('id = ?', 1).toDebugSQL();
// "SELECT * FROM users WHERE id = 1"

Pagination Helper

Get results with pagination metadata in one call:

const result = await qb.select('users')
  .where('active = ?', true)
  .paginate({page: 2, perPage: 20});

// result.results: [...users...]
// result.pagination: { page: 2, perPage: 20, total: 150, totalPages: 8, hasNext: true, hasPrev: true }

Transaction Support

Execute multiple queries atomically:

// D1 (async, batch-based)
const results = await qb.transaction(async (tx) => [
  tx.insert({tableName: 'orders', data: {user_id: 1, total: 100}}),
  tx.update({
    tableName: 'users',
    data: {balance: new Raw('balance - 100')},
    where: {conditions: 'id = ?', params: [1]}
  }),
]);

// DOQB (sync, SQLite transactions)
qb.transaction((tx) => {
  tx.insert({tableName: 'orders', data: {user_id: 1, total: 100}}).execute();
  tx.update({
    tableName: 'users',
    data: {balance: new Raw('balance - 100')},
    where: {conditions: 'id = ?', params: [1]}
  }).execute();
});

Query Hooks

Add middleware for logging, metrics, or tenant filtering:

qb.beforeQuery(async (query, type) => {
  console.log(`Executing ${type}: ${query.query}`);
  return query;
});

qb.afterQuery(async (result, query, duration) => {
  metrics.record(query.query, duration);
  return result;
});

Full Changelog: v1.12.0...v1.13.0