DryModelQueryBuilder (Type-Safe)

The DryModelQueryBuilder is a specialized, type-safe query builder designed specifically for debugging write operations (INSERT, UPDATE, DELETE, TRUNCATE) on models without executing them. Access it via User.dryQuery() or Model.dryQuery().

Key Features

Write Operations Only: Specifically designed for debugging model write operations
Type Safety: Full TypeScript support with model type inference
No Execution: Builds query statements without running them against the database
Model Integration: Supports model decorators, hooks, and relations
Query Inspection: Get the raw SQL string using toQuery() or unWrap()
Write Operation Debugging: Perfect for debugging complex model write operations before execution

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

Basic Usage

Getting the Query String for Write Operations

import { User } from './models/user';

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

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

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

Debugging Read Queries (Use Regular ModelQueryBuilder)

// For debugging READ queries, use the regular ModelQueryBuilder
const readQuery = User.query()
  .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);

Complex Model Write Operations

// Build a complex write operation with joins
const complexUpdateQuery = Post.dryQuery()
  .join(User, 'userId', 'id')
  .where('posts.published', true)
  .where('users.isActive', true)
  .update({ status: 'archived', archivedAt: new Date() });

const sqlString = complexUpdateQuery.toQuery();
console.log(sqlString);

Available Methods

Write Operations Only

DryModelQueryBuilder is designed specifically for write operations. For read queries, use the regular ModelQueryBuilder:

// ✅ CORRECT: Use DryModelQueryBuilder for write operations
const writeQuery = User.dryQuery()
  .where('id', 1)
  .update({ name: 'Updated Name', email: 'updated@example.com' });

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

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

// ✅ CORRECT: Use regular ModelQueryBuilder for read operations
const readQuery = User.query()
  .select('id', 'name', 'email')
  .where('age', '>', 18)
  .whereIn('status', ['active', 'pending'])
  .join('profiles', 'users.id', 'profiles.userId')
  .groupBy('status')
  .having('COUNT(*)', '>', 1)
  .orderBy('createdAt', 'desc')
  .limit(10)
  .offset(20);

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

Insert Operations

// Single insert with model data
const insertQuery = User.dryQuery()
  .insert({
    name: 'John Doe',
    email: 'john@example.com',
    age: 30
  });

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

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

const insertManySql = insertManyQuery.toQuery();

Update Operations

const updateQuery = User.dryQuery()
  .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 = User.dryQuery()
  .where('status', 'inactive')
  .delete();

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

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

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

Truncate Operations

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

Advanced Features

Model Relations with Write Operations

// Build write operations with model relations
const relationUpdateQuery = Post.dryQuery()
  .join(User, 'userId', 'id')
  .where('posts.published', true)
  .where('users.isActive', true)
  .update({ status: 'archived', archivedAt: new Date() });

const relationUpdateSql = relationUpdateQuery.toQuery();
console.log(relationUpdateSql);

// For SELECT with relations, use regular ModelQueryBuilder
const relationSelectQuery = Post.query()
  .join(User, 'userId', 'id')
  .where('posts.published', true)
  .select('posts.*', 'users.name as authorName');

const relationSelectSql = relationSelectQuery.toQuery();
console.log(relationSelectSql);

Annotations with Write Operations

// Annotations are typically used with SELECT operations
// For debugging SELECT with annotations, use regular ModelQueryBuilder
const annotationSelectQuery = User.query()
  .annotate('COUNT(*)', 'total')
  .annotate('AVG(age)', 'averageAge')
  .groupBy('status');

const annotationSelectSql = annotationSelectQuery.toQuery();
console.log(annotationSelectSql);

Common Table Expressions (CTEs) with Write Operations

// CTEs with model write operations
const cteUpdateQuery = User.dryQuery()
  .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 ModelQueryBuilder
const cteSelectQuery = User.query()
  .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 model write operations
const subqueryDeleteQuery = Post.dryQuery()
  .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 ModelQueryBuilder
const subquerySelectQuery = Post.query()
  .where('userId', (sub) =>
    sub.select('id').from('users').where('isActive', true)
  );

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

Use Cases

1. Write Operation Debugging

// Debug complex model write operations before execution
const debugUpdateQuery = Order.dryQuery()
  .join(User, 'userId', '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 ModelQueryBuilder
const debugReadQuery = Order.query()
  .join(User, 'userId', 'id')
  .join(Product, 'productId', '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 with model structure
const migrationInsertQuery = User.dryQuery()
  .insert({ name: 'Migrated User', email: 'migrated@example.com' });

const migrationUpdateQuery = User.dryQuery()
  .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 ModelQueryBuilder
const migrationReadQuery = User.query()
  .select('id', 'name', 'email', 'createdAt')
  .where('migrated', false);

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

3. Write Operation Analysis

// Analyze write operation structure without execution
const analysisUpdateQuery = Product.dryQuery()
  .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 buildUserUpdateQuery(filters: Record<string, any>, updateData: Record<string, any>) {
  let query = User.dryQuery();

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

  if (filters.minAge) {
    query = query.where('age', '>=', filters.minAge);
  }

  if (filters.maxAge) {
    query = query.where('age', '<=', filters.maxAge);
  }

  return query.update(updateData);
}

const updateQuery = buildUserUpdateQuery(
  { status: 'active', minAge: 18 },
  { lastLoginAt: new Date(), loginCount: 1 }
);
console.log('Update SQL:', updateQuery.toQuery());

// For dynamic READ queries, use regular ModelQueryBuilder
function buildUserSearchQuery(filters: Record<string, any>) {
  let query = User.query();

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

  if (filters.minAge) {
    query = query.where('age', '>=', filters.minAge);
  }

  if (filters.maxAge) {
    query = query.where('age', '<=', filters.maxAge);
  }

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

const searchQuery = buildUserSearchQuery({ status: 'active', minAge: 18 });
console.log('Search SQL:', searchQuery.toQuery());

5. Hook Testing

// Test model hooks without database execution
const hookTestQuery = User.dryQuery()
  .where('id', 1)
  .update({ name: 'New Name' });

// This will trigger beforeUpdate hook but not execute the query
const hookTestSql = hookTestQuery.toQuery();
console.log('Hook test SQL:', hookTestSql);

Model-Specific Features

Type Safety

// Full type safety with model properties
const typedQuery = User.dryQuery()
  .select('id', 'name', 'email') // Only model properties allowed
  .where('age', '>', 18) // Type-safe where conditions
  .orderBy('createdAt', 'desc'); // Type-safe ordering

const typedSql = typedQuery.toQuery();

Model Hooks

// Model hooks are triggered even in dry mode
const hookQuery = User.dryQuery()
  .where('id', 1)
  .update({ name: 'Updated Name' });

// beforeUpdate hook will be called
const hookSql = hookQuery.toQuery();

Decorator Support

// Model decorators are respected
const decoratorQuery = User.dryQuery()
  .where('isActive', true)
  .select('id', 'name', 'email');

const decoratorSql = decoratorQuery.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, load, clearRelations, havingRelated, notHavingRelated, union, unionAll, annotate, removeAnnotations, clearRemoveAnnotations (these are for read operations)

Modification Methods

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

Limitations

No Execution: Queries are never executed against the database
No Results: Cannot retrieve actual data
No Database Validation: Database-level constraints are not validated
Hook Limitations: Some hooks may not behave exactly as in execution mode

Comparison to Regular ModelQueryBuilder

Feature	DryModelQueryBuilder	ModelQueryBuilder
Execution	No	Yes
Results	None	Model instances
Type Safety	Full	Full
Hooks	Triggered	Triggered
Use Case	Debugging, Analysis	Data retrieval
Performance	No DB overhead	Full DB interaction

Comparison to DryQueryBuilder

Feature	DryModelQueryBuilder	DryQueryBuilder
Type Safety	Full model typing	No typing
Model Support	Full model features	Basic SQL only
Relations	Supported	Not supported
Hooks	Model hooks triggered	No hooks
Use Case	Model debugging	Raw SQL debugging

Next: Relations

Key Features​

Basic Usage​

Getting the Query String for Write Operations​

Debugging Read Queries (Use Regular ModelQueryBuilder)​

Complex Model Write Operations​

Available Methods​

Write Operations Only​

Insert Operations​

Update Operations​

Delete Operations​

Truncate Operations​

Advanced Features​

Model Relations with Write Operations​

Annotations with Write Operations​

Common Table Expressions (CTEs) with Write Operations​

Subqueries with Write Operations​

Use Cases​

1. Write Operation Debugging​

2. Migration Scripts​

3. Write Operation Analysis​

4. Dynamic Write Query Building​

5. Hook Testing​

Model-Specific Features​

Type Safety​

Model Hooks​

Decorator Support​

API Reference​

Core Methods​

Query Building Methods​

Modification Methods​

Limitations​

Comparison to Regular ModelQueryBuilder​

Comparison to DryQueryBuilder​