ProtectedconnectionProtected ReadonlydreamReadonlydreamInternaldreamProtected Readonly Internalinnerstores the Dream models joined in this Query instance
Internalquerypurely for typing
Static Readonly InternalBATCH_stores the default batch sizes for various provided batching methods
Returns true. Useful for distinguishing Query instances from other objects.
true
Retrieves an array containing all records matching the Query.
Be careful using this, since it will attempt to pull every
record into memory at once. When querying might return a large
number of records, consider using .findEach, which will pull
the records in batches.
await User.query().all()
// [User{id: 1}, User{id: 2}, ...] (ordered by id)
// With specific columns (always includes primary key)
await User.query().all({ columns: ['name'] })
// Users will have both 'id' and 'name' properties
// With custom ordering
await User.order('email').all()
// [User{email: 'a@a.com'}, User{email: 'b@b.com'}, ...]
Query options
Optionalcolumns?: (Array of column names to select. The primary key is always included automatically.
an array of dreams
Retrieves the average value of the specified column for this Query
await Game.query().avg('score')
// 1
a column name on the model
the average of the values of the specified column for this Query
InternalReturns a cloned version of the Query
const clonedQuery = User.query().clone()
Statements to override when cloning the Query
A cloned Query with the provided overrides clause applied
Forces use of a database connection (e.g. 'primary') during the query.
NOTE: all queries within a transaction always use the 'primary' replica, so explicitly setting connection within a transaction has no effect.
The connection you wish to access ('primary' or 'replica')
A Query with the requested connection
Retrieves the number of records matching the Query
await User.query().count()
// 42
await User.where({ email: ops.ilike('%gmail.com') }).count()
// 15
The number of records matching the Query
Paginates the results of your query using cursor-based pagination, accepting a pageSize and cursor argument. This method provides better performance for large datasets by using cursor-based pagination instead of offset-based pagination.
Default order is descending primary key. If an order has already been
set on the query, and it includes the primary key (e.g.: id: 'asc'),
then the implicit primary key ordering will be omitted.
// First page (using undefined to start from beginning)
const firstPage = await User.order('email').cursorPaginate({ pageSize: 100, cursor: undefined })
firstPage.results
// [ { User{id: 777}, User{id: 776}, ...}]
firstPage.cursor
// "100" (or null if no more pages)
// Next page using cursor from previous result
const nextPage = await User.order('email').cursorPaginate({
pageSize: 100,
cursor: firstPage.cursor
})
cursor pagination options
results.cursor - identifier for the next page, or null if no more pages
Deletes all records matching query using a single database query, but does not call underlying callbacks. Ignores association dependent destroy declarations, though cascading may still happen at the database level.
To apply model hooks and association dependent destroy, use Query.destroy instead.
await User.where({ email: ops.ilike('%burpcollaborator%').delete() })
// 12
The number of records that were removed
Destroys all records matching the Query,
calling model hooks and cascading destroy
to associations with dependent: 'destroy',
and returns the number of records that
were destroyed.
To delete in a single database query, ignoring model hooks and association dependent-destroy declarations, use delete instead.
await User.where({ email: ops.ilike('%burpcollaborator%') }).destroy()
// 12
Options for destroying the instance
Optionalcascade?: booleanIf false, skips destroying associations marked dependent: 'destroy'. Defaults to true
OptionalskipHooks?: booleanIf true, skips applying model hooks during the destroy operation. Defaults to false
The number of records that were removed
Returns new Query with distinct clause applied. If no column is specified, applies distinct to the primary key. Pass true to apply distinct to primary key, false to disable distinct.
await User.query().distinct('name').pluck('name')
// Returns unique names
await User.query().distinct(true).pluck('id')
// Returns unique primary keys
await User.query().distinct(false).pluck('name')
// Disables distinct, returns all names including duplicates
The column name to apply distinct to, true to enable distinct on primary key, or false to disable a previously applied distinct call
A cloned Query with the distinct clause applied
Returns true if a record exists for the given Query, false otherwise.
await User.query().exists()
// false
await User.create({ email: 'how@yadoin' })
await User.query().exists()
// true
await User.where({ email: 'nonexistent@example.com' }).exists()
// false
boolean - true if any records match the query, false otherwise
Finds a record matching the Query with the specified primary key. Returns null if not found.
await User.query().find(123)
// User{id: 123}
await User.query().find(null)
// null
await User.query().find(undefined)
// null
The primary key of the record to look up.
Either the found record, or else null
Finds a record matching the Query and the specified where statement. If not found, null is returned.
await User.query().findBy({ email: 'how@yadoin' })
// User{email: 'how@yadoin'}
The where statement used to locate the record
Either the first record found matching the attributes, or else null
Finds all records matching the Query in batches, and then calls the provided callback for each found record. Once all records have been passed for a given batch, the next set of records will be fetched and passed to your callback, until all records matching the Query have been fetched.
await User.order('id').findEach(user => {
DreamApp.log(user)
})
// User{id: 1}
// User{id: 2}
The callback to call for each found record
Options for batch processing
OptionalbatchSize?: numberThe batch size you wish to collect records in. If not provided, it will default to 1000
void
Finds a record matching the Query with the specified primary key. If not found, an exception is raised.
await User.query().findOrFail(123)
// User{id: 123}
The primary key of the record to look up
The found record
Finds a record matching the Query and the specified where statement. If not found, an exception is raised.
await User.query().findOrFailBy({ email: 'how@yadoin' })
// User{email: 'how@yadoin'}
The where statement used to locate the record
The first record found matching the attributes
Returns the first record in the database matching the Query. If the Query is not ordered, it will automatically order by primary key.
await User.query().first()
// User{id: 1} (first by primary key)
await User.order('email').first()
// User with lowest email alphabetically
First record in the database, or null if no record exists
Returns the first record in the database matching the Query. If the Query is not ordered, it will automatically order by primary key. If no record is found, an exception is raised.
await User.query().firstOrFail()
// User{id: 1}
First record in the database
Returns a new Query instance, with the provided joins statement attached
await User.query().innerJoin('posts').first()
A cloned Query with the joins clause applied
Returns the last record in the database matching the Query. If the Query is not ordered, it will automatically order by primary key.
await User.query().last()
// User{id: 99} (last by primary key)
await User.order('email').last()
// User with highest email alphabetically
Last record in the database, or null if no record exists
Returns the last record in the database matching the Query. If the Query is not ordered, it will automatically order by primary key. If no record is found, it will raise an exception.
await User.where(...).lastOrFail()
// User{id: 99}
Last record in the database
InternalA cloned Query with the joins clause applied
Load each specified association using a single SQL query. See Query.preload for preloading in separate queries.
Note: since leftJoinPreload loads via single query, it has some downsides and that may be avoided using Query.preload:
limit and offset will be automatically removedthrough associations will bring additional namespaces into the query that can conflict with through associations from other associations, creating an invalid query.leftJoinPreload('a', 'b', 'c'), if each a has 10 b and each b has 10 c, then for one a, 100 records will be returned, each of which has all of the columns of a. .preload('a', 'b', 'c') would perform three separate SQL queries, but the data for a single a would only be returned once..preload(...).findEach(...) avoids instantiating massive amounts of data at onceconst posts = await user.associationQuery('posts').leftJoinPreload('comments', { visibilty: 'public' }, 'replies').all()
console.log(posts[0].comments[0].replies[0])
// [Reply{id: 1}, Reply{id: 2}]
A chain of association names and and/andNot/andAny clauses
A cloned Query with the joinLoad statement applied
Returns a new Query instance, specifying a limit
await User.order('id').limit(2).all()
// [User{id: 1}, User{id: 2}]
A cloned Query with the limit clause applied
Retrieves the max value of the specified column for this Query
await User.query().max('id')
// 99
a column name on the model
the max value of the specified column for this Query
Retrieves the min value of the specified column for this Query
await User.query().min('id')
// 1
a column name on the model
the min value of the specified column for this Query
Returns a new Kysely SelectQueryBuilder instance to be used in a sub Query
const records = await User.where({
id: Post.query().nestedSelect('userId'),
}).all()
// [User{id: 1}, ...]
the column to use for your nested Query
A Kysely SelectQueryBuilder instance
Returns a new Query instance, specifying an offset
await User.order('id').offset(2).limit(2).all()
// [User{id: 3}, User{id: 4}]
A cloned Query with the offset clause applied
Returns a new Query instance, attaching the provided order statement
await User.query().order('id').all()
// [User{id: 1}, User{id: 2}, ...]
await User.query().order({ name: 'asc', id: 'desc' }).all()
// [User{name: 'a', id: 99}, User{name: 'a', id: 97}, User{ name: 'b', id: 98 } ...]
Either a string or an object specifying order. If a string, the order is implicitly ascending. If the orderStatement is an object, statements will be provided in the order of the keys set in the object
A cloned Query with the order clause applied
Paginates the results of your query, accepting a pageSize and page argument, which it uses to segment your query into pages, leveraging limit and offset to deliver your query to you in pages.
const paginated = await User.order('email').paginate({ pageSize: 100, page: 2 })
paginated.results
// [ { User{id: 101}, User{id: 102}, ...}]
paginated.recordCount
// 350
paginated.pageCount
// 4
paginated.currentPage
// 2
Pagination options
results.recordCount - A number representing the total number of records matching your query
Sends data through for use as passthrough data for the associations that require it.
class Post {
@deco.HasMany('LocalizedText')
public localizedTexts: LocalizedText[]
@deco.HasOne('LocalizedText', {
and: { locale: DreamConst.passthrough },
})
public currentLocalizedText: LocalizedText
}
await User.query().passthrough({ locale: 'es-ES' })
.preload('posts', 'currentLocalizedText')
.first()
where statement used for associations that require passthrough data
A cloned Query with the passthrough data
Plucks the provided fields from the given dream class table
await User.order('id').pluck('id')
// [1, 2, 3]
If more than one column is requested, a multi-dimensional array is returned:
await User.order('id').pluck('id', 'email')
// [[1, 'a@a.com'], [2, 'b@b.com']]
The column or array of columns to pluck
An array of pluck results
Plucks the specified column names from the given dream class table in batches, passing each found columns into the provided callback function
await User.order('id').pluckEach('id', (id) => {
console.log(id)
})
// 1
// 2
// 3
a list of column names to pluck, followed by a callback function to call for each set of found fields
void
Load each specified association using a separate SQL query. See Query.leftJoinPreload for preloading in a single query.
const user = await User.query().preload('posts', 'comments', { visibilty: 'public' }, 'replies').first()
console.log(user.posts[0].comments[0].replies[0])
// [Reply{id: 1}, Reply{id: 2}]
A chain of association names and and/andNot/andAny clauses
A cloned Query with the preload statement applied
Recursively preloads all Dream associations referenced by rendersOne and rendersMany
in a DreamSerializer. This traverses the entire content tree of serializers to automatically
load all necessary associations, eliminating N+1 query problems and removing the need to
manually remember which associations to preload for serialization.
This method decouples data loading code from data rendering code by having the serializer
(rendering code) inform the query (loading code) about which associations are needed.
As serializers evolve over time - adding new rendersOne and rendersMany calls or
modifying existing ones - the loading code automatically adapts without requiring
corresponding modifications to preload statements.
This method analyzes the serializer (specified by serializerKey or 'default') and
automatically preloads all associations that will be needed during serialization.
// Instead of manually specifying all associations:
await User.preload('posts', 'comments', 'replies').all()
// Automatically preload everything needed for serialization:
await User.preloadFor('summary').all()
// Add where conditions to specific associations during preloading:
await User.preloadFor('detailed', (associationName, dreamClass) => {
if (dreamClass.typeof(Post) && associationName === 'comments') {
return { and: { published: true } }
}
})
.all()
// Skip preloading specific associations to handle them manually:
await User.preloadFor('summary', (associationName, dreamClass) => {
if (dreamClass.typeof(User) && associationName === 'posts') {
return 'omit' // Handle posts preloading separately with custom logic
}
})
.preload('posts', { and: { featured: true } }) // Custom preloading
.all()
The serializer key to use for determining which associations to preload.
OptionalmodifierFn: LoadForModifierFnOptional callback function to modify or omit specific associations during preloading. Called for each association with the Dream class and association name. Return an object with and, andAny, or andNot properties to add where conditions, return 'omit' to skip preloading that association (useful when you want to handle it manually), or return undefined to use default preloading
A Query with all serialization associations preloaded
Destroys all records matching the Query, ignoring the SoftDelete decorator and permanently removing records from the database.
Calls model hooks and applies cascade destroy
to associations with dependent: 'destroy',
returning the number of records that
were destroyed.
To destroy without bypassing the SoftDelete decorator, use Query.destroy instead.
await User.where({ email: ops.ilike('%burpcollaborator%') }).reallyDestroy()
// 12
Options for destroying the instance
Optionalcascade?: booleanIf false, skips destroying associations marked dependent: 'destroy'. Defaults to true
OptionalskipHooks?: booleanIf true, skips applying model hooks during the destroy operation. Defaults to false
The number of records that were removed
Prevents default scopes from applying when the Query is executed
A new Query which will prevent default scopes from applying
ProtectedremovePrevents default scopes from applying when the Query is executed, but not when applying to associations
A new Query which will prevent default scopes from applying, but not when applying to asociations
Prevents a specific default scope from applying when the Query is executed
A new Query which will prevent a specific default scope from applying
ProtectedremovePrevents a specific default scope from applying when the Query is executed, but not when applying to asociations
A new Query which will prevent a specific default scope from applying, but not when applying to asociations
scroll pagination options
results.cursor - identifier for the next page, or null if no more pages
Use cursorPaginate instead.
Paginates the results of your query using cursor-based pagination, accepting a pageSize and cursor argument. This method provides better performance for large datasets by using cursor-based pagination instead of offset-based pagination.
Default order is ascending primary key. If an order has already been
set on the query, and it includes the primary key (e.g.: id: 'asc'),
then the implicit primary key ordering will be omitted.
// First page (using undefined to start from beginning)
const firstPage = await User.order('email').scrollPaginate({ pageSize: 100, cursor: undefined })
firstPage.results
// [ { User{id: 1}, User{id: 2}, ...}]
firstPage.cursor
// "100" (or null if no more pages)
// Next page using cursor from previous result
const nextPage = await User.order('email').scrollPaginate({
pageSize: 100,
cursor: firstPage.cursor
})
Returns the sql that would be executed by this Query
User.where({ email: 'how@yadoin' }).sql()
// {
// query: {
// kind: 'SelectQueryNode',
// from: { kind: 'FromNode', froms: [Array] },
// selections: [ [Object] ],
// distinctOn: undefined,
// joins: undefined,
// groupBy: undefined,
// orderBy: undefined,
// where: { kind: 'WhereNode', where: [Object] },
// frontModifiers: undefined,
// endModifiers: undefined,
// limit: undefined,
// offset: undefined,
// with: undefined,
// having: undefined,
// explain: undefined,
// setOperations: undefined
// },
// sql: 'select "users".* from "users" where ("users"."email" = $1 and "users"."deleted_at" is null)',
// parameters: [ 'how@yadoin' ]
//}
An object representing the underlying sql statement
Retrieves the sum value of the specified column for this Query
await Game.query().sum('score')
// 1
a column name on the model
the sum of the values of the specified column for this Query
Converts the given dream class into a Kysely query, enabling you to build custom queries using the Kysely API
await User.query().toKysely('select').where('email', '=', 'how@yadoin').execute()
the type of Kysely query builder instance you would like to obtain
A Kysely query. Depending on the type passed, it will return either a SelectQueryBuilder, DeleteQueryBuilder, or an UpdateQueryBuilder
Applies transaction to the Query instance
await ApplicationModel.transaction(async txn => {
await User.query().txn(txn).create({ email: 'how@yadoin' })
})
A DreamTransaction instance (usually collected by calling ApplicationModel.transaction)
A cloned Query with the transaction applied
Undestroys a SoftDelete model, unsetting
the deletedAt field in the database.
If the model is not a SoftDelete model, this will raise an exception.
await User.where({ email: ops.ilike('%burpcollaborator%') }).undestroy()
// 12
Options for undestroying the instance
Optionalcascade?: booleanIf false, skips undestroying associations marked dependent: 'destroy'. Defaults to true
OptionalskipHooks?: booleanIf true, skips applying model hooks during the undestroy operation. Defaults to false
The number of records that were removed
Updates all records matching the Query
await User.where({ email: ops.ilike('%burpcollaborator%') }).update({ email: null })
// 12
The attributes used to update the records
Options for updating the instance
OptionalskipHooks?: booleanIf true, skips applying model hooks. Defaults to false
The number of records that were updated
Applies a where statement to the Query instance
await User.where({ email: 'how@yadoin' }).first()
// User{email: 'how@yadoin'}
Where statement to apply to the Query
A cloned Query with the where clause applied
Accepts a list of where statements, each of
which is combined via OR
await User.query().whereAny([{ email: 'how@yadoin' }, { name: 'fred' }]).first()
// [User{email: 'how@yadoin'}, User{name: 'fred'}, User{name: 'fred'}]
a list of where statements to OR together
A cloned Query with the whereAny clause applied
Applies a whereNot statement to the Query instance
await User.query().whereNot({ email: 'how@yadoin' }).first()
// User{email: 'hello@world'}
A where statement to negate and apply to the Query
A cloned Query with the whereNot clause applied
Staticdb
stores the dream transaction applied to the current Query instance