Overview The QueryBuilder class is the heart of Esix’s querying system. It provides a fluent interface for building complex MongoDB queries with type safety and automatic sanitization.
How It Works Most static methods on BaseModel return a QueryBuilder instance, allowing you to chain multiple query methods together before executing the query.
const users = await User . where ( 'age' , '>=' , 18 ) . where ( 'status' , 'active' ) . orderBy ( 'createdAt' , 'desc' ) . limit ( 10 ) . get ()
Query Methods where() Filter results based on field values. Supports three syntax styles:
Object Syntax // Pass an object with multiple conditions const users = await User . where ({ status: 'active' , role: 'admin' }). get ()
Two-Parameter Syntax (Equality) // Simple equality check const posts = await Post . where ( 'status' , 'published' ). get ()
Three-Parameter Syntax (Comparison Operators) // Using comparison operators const adults = await User . where ( 'age' , '>=' , 18 ). get () const recentPosts = await Post . where ( 'createdAt' , '>' , Date . now () - 86400000 ). get () const affordableProducts = await Product . where ( 'price' , '<=' , 50 ). get () const activeUsers = await User . where ( 'status' , '!=' , 'banned' ). get ()
Supported operators:
= - Equal to (same as two-parameter syntax)!= or <> - Not equal to> - Greater than>= - Greater than or equal to< - Less than<= - Less than or equal to
Comparison operators are automatically converted to MongoDB operators ($gt, $gte, $lt, $lte, $ne).
whereIn() Filter results where a field’s value is in an array of values:
const posts = await Post . whereIn ( 'categoryId' , [ '1' , '2' , '3' ]). get () const users = await User . whereIn ( 'role' , [ 'admin' , 'moderator' ]). get ()
whereNotIn() Filter results where a field’s value is NOT in an array of values:
const users = await User . whereNotIn ( 'status' , [ 'banned' , 'suspended' ]). get () const posts = await Post . whereNotIn ( 'id' , archivedPostIds ). get ()
orderBy() Sort results by a field in ascending or descending order:
// Ascending order (default) const users = await User . orderBy ( 'name' ). get () // Descending order const posts = await Post . orderBy ( 'createdAt' , 'desc' ). get () // Multiple sort fields const products = await Product . orderBy ( 'category' ) . orderBy ( 'price' , 'desc' ) . get ()
limit() Limit the number of results returned:
const latestPosts = await Post . orderBy ( 'createdAt' , 'desc' ) . limit ( 10 ) . get ()
skip() Skip a number of results (useful for pagination):
// Get page 2 with 20 items per page const page = 2 const perPage = 20 const users = await User . orderBy ( 'createdAt' , 'desc' ) . skip (( page - 1 ) * perPage ) . limit ( perPage ) . get ()
Execution Methods get() Execute the query and return an array of models:
const users : User [] = await User . where ( 'status' , 'active' ). get ()
first() Execute the query and return the first result (or null):
count() Return the number of documents matching the query:
const activeUserCount = await User . where ( 'status' , 'active' ). count ()
delete() Delete all documents matching the query and return the count of deleted documents:
const deletedCount = await Post . where ( 'status' , 'draft' ) . where ( 'createdAt' , '<' , Date . now () - 2592000000 ) // 30 days old . delete () console . log ( `Deleted ${ deletedCount } old draft posts` )
The delete() method permanently removes documents. Use with caution.
Aggregation Methods The QueryBuilder provides methods for calculating aggregate values:
sum() Calculate the sum of a numeric field:
const totalSales = await Order . where ( 'status' , 'completed' ) . sum ( 'amount' )
average() Calculate the average of a numeric field:
const avgAge = await User . where ( 'role' , 'customer' ). average ( 'age' )
min() and max() Find the minimum or maximum value:
const lowestPrice = await Product . where ( 'inStock' , true ). min ( 'price' ) const highestScore = await Test . where ( 'subject' , 'math' ). max ( 'score' )
percentile() Calculate the nth percentile:
const median = await ResponseTime . percentile ( 'value' , 50 ) const p95 = await ResponseTime . percentile ( 'value' , 95 ) const p99 = await ResponseTime . percentile ( 'value' , 99 )
pluck() Extract an array of values for a specific field:
const userIds = await User . where ( 'status' , 'active' ). pluck ( 'id' ) const titles = await Post . where ( 'published' , true ). pluck ( 'title' )
Advanced: Direct Aggregation For complex queries, you can use MongoDB’s aggregation pipeline directly:
const results = await User . aggregate ([ { $group: { _id: '$department' , count: { $sum: 1 }, avgAge: { $avg: '$age' } } }, { $sort: { count: - 1 } } ])
Real-World Examples function paginate ( page : number , perPage : number = 20 ) { return User . where ( 'status' , 'active' ) . orderBy ( 'createdAt' , 'desc' ) . skip (( page - 1 ) * perPage ) . limit ( perPage ) } const page1 = await paginate ( 1 ). get () const page2 = await paginate ( 2 ). get ()
Complex Filtering const premiumActiveUsers = await User . where ( 'status' , 'active' ) . where ( 'subscriptionTier' , 'premium' ) . where ( 'lastLoginAt' , '>' , Date . now () - 604800000 ) // Active in last 7 days . orderBy ( 'lastLoginAt' , 'desc' ) . get ()
Analytics Query // Get statistics for completed orders this month const startOfMonth = new Date () startOfMonth . setDate ( 1 ) startOfMonth . setHours ( 0 , 0 , 0 , 0 ) const orderQuery = Order . where ( 'status' , 'completed' ) . where ( 'createdAt' , '>=' , startOfMonth . getTime ()) const totalOrders = await orderQuery . count () const totalRevenue = await orderQuery . sum ( 'amount' ) const avgOrderValue = await orderQuery . average ( 'amount' ) console . log ({ totalOrders , totalRevenue , avgOrderValue })
Batch Operations // Archive old inactive users const sixMonthsAgo = Date . now () - 15552000000 const archivedCount = await User . where ( 'status' , 'inactive' ) . where ( 'lastLoginAt' , '<' , sixMonthsAgo ) . limit ( 1000 ) // Batch size . delete () console . log ( `Archived ${ archivedCount } users` )
Type Safety The QueryBuilder is fully type-safe. TypeScript will:
Ensure field names exist on the model
Check that values match the field types
Provide autocomplete for field names and methods
Catch errors at compile time
class User extends BaseModel { public name = '' public age = 0 public email = '' } // ✅ TypeScript approves const users = await User . where ( 'age' , '>' , 18 ). get () // ❌ TypeScript error: 'invalidField' doesn't exist const invalid = await User . where ( 'invalidField' , 'value' ). get () // ✅ TypeScript knows the return type const names : string [] = await User . pluck ( 'name' )
Implementation Details The QueryBuilder class (query-builder.ts) provides:
Automatic sanitization : All inputs are sanitized to prevent NoSQL injectionLazy execution : Queries are only executed when you call an execution method like get(), first(), or count()Method chaining : All query methods return this for fluent chainingMongoDB integration : Translates to native MongoDB queriesCollection naming : Automatically pluralizes and kebab-cases model names
For example, a User model maps to the users collection, and BlogPost maps to blog-posts.
Next Steps
Relationships Learn how to query related models with hasMany()
Security Understand how QueryBuilder prevents NoSQL injection