QueryBuilder
The QueryBuilder class provides a fluent, chainable interface for constructing complex MongoDB queries in a type-safe manner. It’s the engine behind most query operations in Esix and is typically accessed through BaseModel static methods.
Overview
QueryBuilder follows the builder pattern, allowing you to chain multiple query constraints together before executing the final query. Each method returns this (or a Promise for terminal methods), enabling method chaining.
const results = await User
.where('age', '>=', 18)
.where('status', 'active')
.orderBy('createdAt', 'desc')
.limit(10)
.get();
Constructor
constructor(ctor: ObjectType<T>)
The model class constructor that this QueryBuilder operates on
Query Constraint Methods
These methods add constraints to the query and return the QueryBuilder instance for chaining.
where()
Adds a constraint to the current query. Supports multiple signatures for different use cases.
// Object syntax
where(query: Query): QueryBuilder<T>
// Two-parameter syntax (equality)
where<K extends keyof T>(key: K, value: any): QueryBuilder<T>
// Three-parameter syntax (comparison operators)
where<K extends keyof T>(
key: K,
operator: ComparisonOperator,
value: any
): QueryBuilder<T>
Object containing field-value pairs for filtering
Property name to filter by (must be a valid model field)
Comparison operator: '=', '!=', '<>', '>', '>=', '<', '<='
The QueryBuilder instance for chaining
'=' → Equality match'!=' or '<>' → MongoDB $ne'>' → MongoDB $gt'>=' → MongoDB $gte'<' → MongoDB $lt'<=' → MongoDB $lte
Examples
// Object syntax
const users = await User.where({ status: 'active', role: 'admin' }).get();
// Equality comparison
const published = await BlogPost.where('status', 'published').get();
// Greater than or equal
const adults = await User.where('age', '>=', 18).get();
// Less than
const young = await User.where('age', '<', 30).get();
// Not equal
const nonAdmins = await User.where('role', '!=', 'admin').get();
// Chaining multiple conditions
const results = await User
.where('age', '>=', 18)
.where('age', '<=', 65)
.where('status', 'active')
.get();
whereIn()
Filters models where the specified key’s value is in the given array.
whereIn<K extends keyof T>(key: K, values: any[]): QueryBuilder<T>
key
K extends keyof T
required
The property name to filter by
Array of values to match against
The QueryBuilder instance for chaining
const posts = await Post.whereIn('categoryId', [1, 2, 3]).get();
id field by mapping it to MongoDB’s _id.
whereNotIn()
Filters models where the specified key’s value is not in the given array.
whereNotIn<K extends keyof T>(key: K, values: any[]): QueryBuilder<T>
key
K extends keyof T
required
The property name to filter by
Array of values to exclude
The QueryBuilder instance for chaining
const users = await User.whereNotIn('status', ['banned', 'suspended']).get();
orderBy()
Sorts the results by the specified field.
orderBy<K extends keyof T>(key: K, order?: 'asc' | 'desc'): QueryBuilder<T>
key
K extends keyof T
required
The property name to sort by
order
'asc' | 'desc'
default:"'asc'"
Sort direction: ascending or descending
The QueryBuilder instance for chaining
// Ascending order (default)
const alphabetical = await User.orderBy('name').get();
// Descending order
const newest = await Post.orderBy('createdAt', 'desc').get();
// Multiple order clauses
const sorted = await User
.orderBy('lastName', 'asc')
.orderBy('firstName', 'asc')
.get();
limit()
Limits the number of results returned.
limit(length: number): QueryBuilder<T>
Maximum number of models to return
The QueryBuilder instance for chaining
const topTen = await Post.orderBy('views', 'desc').limit(10).get();
skip()
Skips the specified number of results. Useful for pagination.
skip(length: number): QueryBuilder<T>
The QueryBuilder instance for chaining
// Pagination: Page 3 with 20 items per page
const page = 3;
const perPage = 20;
const results = await Post
.skip((page - 1) * perPage)
.limit(perPage)
.get();
Execution Methods
These terminal methods execute the query and return results.
get()
Executes the query and returns an array of models matching the query constraints.
async get(): Promise<T[]>
Array of model instances matching the query
const users = await User.where('status', 'active').get();
first()
Executes the query and returns the first matching model.
async first(): Promise<T | null>
The first model instance matching the query, or null if none found
const newest = await Post.orderBy('createdAt', 'desc').first();
limit(1) internally.
find()
Finds a model by its ID.
async find(id: string): Promise<T | null>
The unique identifier of the model
The model instance if found, otherwise null
const builder = new QueryBuilder(User);
const user = await builder.find('507f1f77bcf86cd799439011');
count()
Returns the number of documents matching the query.
async count(): Promise<number>
Total count of documents matching the query
const activeCount = await Customer
.where('hasPaidTheLastInvoice', true)
.count();
delete()
Deletes all models matching the current query constraints.
async delete(): Promise<number>
Number of documents deleted
// Delete all inactive users
const deleted = await User.where('status', 'inactive').delete();
// Delete specific models
const count = await Post.whereIn('id', ['id1', 'id2']).delete();
Aggregation Methods
These methods perform aggregation operations on the queried documents.
sum()
Calculates the sum of values for a numeric field.
async sum<K extends keyof T>(key: K): Promise<number>
key
K extends keyof T
required
The property name to sum
Sum of all values for the specified field
const totalRevenue = await Order
.where('status', 'completed')
.sum('amount');
average()
Calculates the average of values for a numeric field.
async average<K extends keyof T>(key: K): Promise<number>
key
K extends keyof T
required
The property name to average
Average of all values (returns 0 if no values)
const avgAge = await User
.where('country', 'USA')
.average('age');
min()
Finds the minimum value for a numeric field.
async min<K extends keyof T>(key: K): Promise<number>
key
K extends keyof T
required
The property name to find minimum value
Minimum value for the specified field
const lowestPrice = await Product
.where('category', 'electronics')
.min('price');
max()
Finds the maximum value for a numeric field.
async max<K extends keyof T>(key: K): Promise<number>
key
K extends keyof T
required
The property name to find maximum value
Maximum value for the specified field
const highestScore = await GameScore
.where('gameId', 'game123')
.max('score');
percentile()
Calculates the nth percentile of values for a numeric field.
async percentile<K extends keyof T>(key: K, n: number): Promise<number>
key
K extends keyof T
required
The property name to calculate percentile
The percentile to calculate (0-100)
The nth percentile value (returns 0 if no values)
// Median (50th percentile)
const median = await ResponseTime.percentile('value', 50);
// 95th percentile
const p95 = await ResponseTime.percentile('value', 95);
// 99th percentile with query constraints
const p99 = await ApiRequest
.where('endpoint', '/api/users')
.percentile('duration', 99);
pluck()
Extracts an array of values for a specific field from all matching documents.
async pluck<K extends keyof T>(key: K): Promise<T[K][]>
key
K extends keyof T
required
The property name to extract values from
Array of values for the specified field
const ids = await Post.where('categoryId', 2).pluck('id');
// => ['1', '2', '3']
const emails = await User.where('verified', true).pluck('email');
// => ['user1@example.com', 'user2@example.com', ...]
aggregate()
Provides direct access to MongoDB’s aggregation pipeline for complex queries.
async aggregate(stages: Record<string, unknown>[])
stages
Record<string, unknown>[]
required
Array of MongoDB aggregation pipeline stages
The result of the aggregation
// Group by department and count
const results = await User.aggregate([
{ $group: { _id: '$department', count: { $sum: 1 } } }
]);
// Complex pipeline with multiple stages
const stats = await Order.aggregate([
{ $match: { status: 'completed' } },
{ $group: {
_id: '$userId',
totalSpent: { $sum: '$amount' },
orderCount: { $sum: 1 }
}},
{ $sort: { totalSpent: -1 } },
{ $limit: 10 }
]);
Internal Methods
These methods are used internally by Esix and are not typically called directly.
create()
Creates a new document with the given attributes.
async create(attributes: Record<string, any>): Promise<string>
attributes
Record<string, any>
required
Object containing the document’s attributes
The ID of the created document
BaseModel.create() instead.
save()
Persists the provided attributes to the database.
async save(attributes: Dictionary): Promise<string>
Object containing the document’s attributes
The ID of the saved document
save() method on models instead.
findOne()
Returns the first model matching the query.
async findOne(query: Query): Promise<T | null>
Query object to match against
The first matching model instance, or null if none found
Security
NoSQL Injection Prevention
QueryBuilder automatically sanitizes all input values using the internal sanitize() function to prevent NoSQL injection attacks. All user-provided values are sanitized before being passed to MongoDB.
// Safe - values are automatically sanitized
const user = await User.where('email', userInput).first();
// Safe - array values are sanitized
const posts = await Post.whereIn('id', userProvidedIds).get();
Type Safety
QueryBuilder leverages TypeScript generics to provide compile-time type safety:
class User extends BaseModel {
email: string = '';
age: number = 0;
}
// ✓ TypeScript knows 'email' and 'age' are valid
const adults = await User.where('age', '>=', 18).get();
// ✗ TypeScript error: 'invalidField' is not a property of User
const invalid = await User.where('invalidField', 'value').get();
// ✓ Return type is inferred as User[]
const users: User[] = await User.where('age', '>', 21).get();
Query Type
The Query type represents a flexible MongoDB query object:
export type Query = { [index: string]: any }
const builder = new QueryBuilder(User);
builder.where({
age: { $gte: 18, $lte: 65 },
status: { $in: ['active', 'pending'] }
});