DryQueryBuilder (Raw SQL)

The DryQueryBuilder is a specialized query builder designed specifically for debugging write operations (INSERT, UPDATE, DELETE, TRUNCATE) without executing them. Access it via sql.dryQuery('table') or sql.dryQuery(table, options).

Key Features

• Write Operations Only: Specifically designed for debugging INSERT, UPDATE, DELETE, and TRUNCATE operations
• No Execution: Builds query statements without running them against the database
• Query Inspection: Get the raw SQL string using toQuery() or unWrap()
• Write Operation Debugging: Perfect for debugging complex write operations before execution
• Migration Scripts: Ideal for building write queries in migration scripts

Important: Use DryQueryBuilder only for debugging write operations. For debugging read queries (SELECT), use the regular QueryBuilder with toQuery() and unWrap() methods.

Basic Usage

Getting the Query String for Write Operations

// After connecting with SqlDataSource

// Build a write operation without executing it
const insertQuery = sql.dryQuery('users')
  .insert({ name: 'John Doe', email: 'john@example.com' });

// Get the SQL string
const sqlString = insertQuery.toQuery();
console.log(sqlString); // INSERT INTO `users` (`name`, `email`) VALUES (?, ?)

// Get unwrapped query (alternative method)
const unwrappedQuery = insertQuery.unWrap();
console.log(unwrappedQuery);

Debugging Read Queries (Use Regular QueryBuilder)

// For debugging READ queries, use the regular QueryBuilder
const readQuery = sql.query('users')
  .where('status', 'active')
  .select('id', 'name', 'email');

// Debug the read query
const readSqlString = readQuery.toQuery();
console.log(readSqlString); // SELECT `id`, `name`, `email` FROM `users` WHERE `status` = ?

// Or get the unwrapped query
const readUnwrappedQuery = readQuery.unWrap();
console.log(readUnwrappedQuery);

Available Methods

Write Operations Only

DryQueryBuilder is designed specifically for write operations. For read queries, use the regular QueryBuilder:

// ✅ CORRECT: Use DryQueryBuilder for write operations
const writeQuery = sql.dryQuery('users')
  .where('id', 1)
  .update({ name: 'Updated Name' });

const writeSql = writeQuery.toQuery();
console.log(writeSql); // UPDATE `users` SET `name` = ? WHERE `id` = ?

// ❌ WRONG: Don't use DryQueryBuilder for read operations
// const readQuery = sql.dryQuery('users').select('*'); // This won't work as expected

// ✅ CORRECT: Use regular QueryBuilder for read operations
const readQuery = sql.query('users')
  .select('id', 'name', 'email')
  .where('age', '>', 18);

const readSql = readQuery.toQuery();
console.log(readSql); // SELECT `id`, `name`, `email` FROM `users` WHERE `age` > ?

Insert Operations

// Single insert
const insertQuery = sql.dryQuery('users')
  .insert({ name: 'John Doe', email: 'john@example.com' });

const insertSql = insertQuery.toQuery();
console.log(insertSql); // INSERT INTO `users` (`name`, `email`) VALUES (?, ?)

// Multiple inserts
const insertManyQuery = sql.dryQuery('users')
  .insertMany([
    { name: 'John Doe', email: 'john@example.com' },
    { name: 'Jane Smith', email: 'jane@example.com' }
  ]);

const insertManySql = insertManyQuery.toQuery();

Update Operations

const updateQuery = sql.dryQuery('users')
  .where('id', 1)
  .update({ name: 'Updated Name', email: 'updated@example.com' });

const updateSql = updateQuery.toQuery();
console.log(updateSql); // UPDATE `users` SET `name` = ?, `email` = ? WHERE `id` = ?

Delete Operations

// Hard delete
const deleteQuery = sql.dryQuery('users')
  .where('status', 'inactive')
  .delete();

const deleteSql = deleteQuery.toQuery();
console.log(deleteSql); // DELETE FROM `users` WHERE `status` = ?

// Soft delete
const softDeleteQuery = sql.dryQuery('users')
  .where('id', 1)
  .softDelete({ column: 'deletedAt' });

const softDeleteSql = softDeleteQuery.toQuery();
console.log(softDeleteSql); // UPDATE `users` SET `deletedAt` = ? WHERE `id` = ?

Truncate Operations

const truncateQuery = sql.dryQuery('users').truncate();
const truncateSql = truncateQuery.toQuery();
console.log(truncateSql); // TRUNCATE TABLE `users`

Advanced Features

Common Table Expressions (CTEs) with Write Operations

// CTEs with UPDATE operations
const cteUpdateQuery = sql.dryQuery('users')
  .with('active_users', (qb) =>
    qb.select('id').from('users').where('isActive', true)
  )
  .where('id', (sub) => sub.select('id').from('active_users'))
  .update({ lastLoginAt: new Date() });

const cteUpdateSql = cteUpdateQuery.toQuery();
console.log(cteUpdateSql);

// For CTEs with SELECT operations, use regular QueryBuilder
const cteSelectQuery = sql.query('users')
  .with('active_users', (qb) =>
    qb.select('id', 'name').where('isActive', true)
  )
  .select('*')
  .from('active_users');

const cteSelectSql = cteSelectQuery.toQuery();
console.log(cteSelectSql);

Subqueries with Write Operations

// Subqueries with DELETE operations
const subqueryDeleteQuery = sql.dryQuery('posts')
  .where('userId', (sub) =>
    sub.select('id').from('users').where('isActive', false)
  )
  .delete();

const subqueryDeleteSql = subqueryDeleteQuery.toQuery();
console.log(subqueryDeleteSql);

// For subqueries with SELECT operations, use regular QueryBuilder
const subquerySelectQuery = sql.query('posts')
  .where('userId', (sub) =>
    sub.select('id').from('users').where('isActive', true)
  );

const subquerySelectSql = subquerySelectQuery.toQuery();
console.log(subquerySelectSql);

Unions with Write Operations

// Note: Unions are typically used with SELECT operations
// For debugging SELECT with unions, use regular QueryBuilder
const unionSelectQuery = sql.query('users')
  .select('name')
  .union('SELECT name FROM admins');

const unionSelectSql = unionSelectQuery.toQuery();
console.log(unionSelectSql);

Use Cases

1. Write Operation Debugging

// Debug complex write operations before execution
const debugUpdateQuery = sql.dryQuery('orders')
  .join('users', 'orders.userId', 'users.id')
  .where('orders.status', 'pending')
  .where('users.isActive', true)
  .update({ status: 'processed', processedAt: new Date() });

console.log('Generated SQL:', debugUpdateQuery.toQuery());
console.log('Bindings:', debugUpdateQuery.unWrap().bindings);

// For debugging READ queries, use regular QueryBuilder
const debugReadQuery = sql.query('orders')
  .join('users', 'orders.userId', 'users.id')
  .join('products', 'orders.productId', 'products.id')
  .where('orders.status', 'pending')
  .where('users.isActive', true)
  .select('orders.*', 'users.name', 'products.title');

console.log('Read SQL:', debugReadQuery.toQuery());

2. Migration Scripts

// Build write queries for data migration
const migrationInsertQuery = sql.dryQuery('new_users')
  .insert({ name: 'Migrated User', email: 'migrated@example.com' });

const migrationUpdateQuery = sql.dryQuery('old_users')
  .where('migrated', false)
  .update({ migrated: true, migratedAt: new Date() });

console.log('Insert SQL:', migrationInsertQuery.toQuery());
console.log('Update SQL:', migrationUpdateQuery.toQuery());

// For reading data during migration, use regular QueryBuilder
const migrationReadQuery = sql.query('old_users')
  .select('id', 'name', 'email', 'created_at')
  .where('migrated', false);

console.log('Read SQL:', migrationReadQuery.toQuery());

3. Write Operation Analysis

// Analyze write operation structure without execution
const analysisUpdateQuery = sql.dryQuery('products')
  .where('category', 'electronics')
  .where('price', '>', 100)
  .update({ discount: 0.1, updatedAt: new Date() });

const queryInfo = analysisUpdateQuery.unWrap();
console.log('Update Query:', queryInfo.sql);
console.log('Bindings:', queryInfo.bindings);
console.log('Table:', queryInfo.table);

4. Dynamic Write Query Building

function buildUpdateQuery(filters: Record<string, any>, updateData: Record<string, any>) {
  let query = sql.dryQuery('products');

  if (filters.category) {
    query = query.where('category', filters.category);
  }

  if (filters.minPrice) {
    query = query.where('price', '>=', filters.minPrice);
  }

  if (filters.maxPrice) {
    query = query.where('price', '<=', filters.maxPrice);
  }

  return query.update(updateData);
}

const updateQuery = buildUpdateQuery(
  { category: 'electronics', minPrice: 50 },
  { discount: 0.1, updatedAt: new Date() }
);
console.log('Update SQL:', updateQuery.toQuery());

// For dynamic READ queries, use regular QueryBuilder
function buildSearchQuery(filters: Record<string, any>) {
  let query = sql.query('products');

  if (filters.category) {
    query = query.where('category', filters.category);
  }

  if (filters.minPrice) {
    query = query.where('price', '>=', filters.minPrice);
  }

  if (filters.maxPrice) {
    query = query.where('price', '<=', filters.maxPrice);
  }

  return query.select('*').orderBy('name');
}

const searchQuery = buildSearchQuery({ category: 'electronics', minPrice: 50 });
console.log('Search SQL:', searchQuery.toQuery());

API Reference

Core Methods

• toQuery() - Returns the SQL string with bindings
• unWrap() - Returns the complete query object with SQL and bindings
• clone() - Creates a copy of the query builder

Query Building Methods

Note: These methods are available for building WHERE clauses and conditions for write operations:

• Filtering: where, orWhere, andWhere, whereIn, whereNull, whereBetween, whereLike, whereExists
• Joins: join, leftJoin, rightJoin, innerJoin (for complex write operations)
• Grouping: groupBy, having (for grouped updates/deletes)
• Ordering: orderBy, orderByRaw (for ordered updates/deletes)
• Limiting: limit, offset (for limited updates/deletes)
• CTEs: with, withRecursive, withMaterialized (for complex write operations)

Not Available: select, selectRaw, clearSelect, union, unionAll (these are for read operations)

Modification Methods

• Insert: insert, insertMany, upsert
• Update: update
• Delete: delete, softDelete
• Truncate: truncate

Limitations

• No Execution: Queries are never executed against the database
• No Results: Cannot retrieve actual data
• No Hooks: Model hooks are not triggered
• No Validation: Database-level validation is not performed

Comparison to Regular QueryBuilder

Feature	DryQueryBuilder	QueryBuilder
Execution	No	Yes
Results	None	Data returned
Use Case	Debugging, Analysis	Data retrieval
Performance	No DB overhead	Full DB interaction
Hooks	Not triggered	Triggered

---

Next: DryModelQueryBuilder (Type-Safe)