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
Retrieves the average of the specified column within each group, keyed by
the value of the provided group column (a SQL GROUP BY).
await CompositionAsset.query().avgBy('name', 'score')
// Map(2) { 'primary' => 5, 'secondary' => 4 }
Any where clauses, base scopes (soft-delete / STI), and joined-association
and clauses on the Query carry through automatically. A joined-association
column may be grouped on or aggregated by passing its namespaced name (e.g.
'compositionAssets.name').
Only groups with at least one matching row appear in the Map (inherent to
GROUP BY). When the group column is nullable, records with a null value
are grouped under a real null key; a group whose aggregated values are all
null yields a null value.
the column to group by (base or joined-association-namespaced)
the column to average within each group
A Map from each present group value to the average of the aggregated column in that group
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
Retrieves the number of records in each group, keyed by the
value of the provided group column (a SQL GROUP BY).
await User.query().countBy('name')
// Map(2) { 'fred' => 2, 'zed' => 1 }
await User.where({ email: ops.ilike('%gmail.com') }).countBy('name')
// Map(1) { 'fred' => 3 }
Any where clauses, base scopes (soft-delete / STI), and joined-association
and clauses on the Query carry through automatically. A joined-association
column may be grouped on by passing its namespaced name (e.g.
'compositionAssets.name').
Only groups with at least one matching row appear in the Map (inherent to
GROUP BY); seed absent groups yourself with map.get(key) ?? 0. When the
group column is nullable, records with a null value are grouped under a real
null key.
await Composition.query().innerJoin('compositionAssets').countBy('compositionAssets.name')
// Map(2) { 'primary' => 3, null => 1 }
the column to group by (base or joined-association-namespaced)
A Map from each present group value to the number of records in that group
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.
Pages follow the Query's order, preceded by the order declared on any
association the Query is built on or joins (so an associationQuery of
an ordered association pages in that association's order), with
descending primary key as the final tiebreaker; with neither, pages run
in descending primary key order. If those orders already include the
primary key (e.g.: id: 'asc'), the implicit primary key ordering is
omitted.
A Query that joins a HasOne or HasMany association (directly, through an
association it joins, or as the association an associationQuery goes
through) returns a record once for each row the join matches, as
Query.all does, and the pages return each of those rows once,
sorting the rows of one record by the joined rows' primary keys after the
primary key. The cursor of such a Query is an opaque string that names
its last row by primary keys, the record's and the joined rows' (readable
by whoever holds it; it is not encrypted), and the next page starts after
that row, or, if the row is gone, after the record's first row. Any other
Query's cursor is its last record's primary key. A record's primary key
is always accepted as a cursor: the next page starts after that record's
first row. A cursor carries no other value its row sorts by: those are
read from the database when the next page is.
A Query that calls distinct on its primary key (as distinct() does,
e.g.: User.innerJoin('posts').distinct()) returns each record once, as
Query.all does, and so do its pages, whose cursor is the last
record's primary key. A Query that calls distinct on another column has
to be ordered by that column first (e.g.:
Post.distinct('category').order({ category: 'asc', createdAt: 'desc' })),
or the database rejects its pages. Its pages return the records
Query.all returns, the one its order keeps for each value of that
column (the newest post of each category), and its cursor is also the
last record's primary key. Over an association that declares distinct
and an order led by its distinct column (an associationQuery of it, or
a Query that joins it), the pages return the rows Query.all
returns: the row the association's order keeps for each value of its
distinct column.
A similarity condition (ops.similarity, ops.wordSimilarity,
ops.strictWordSimilarity) still decides which records match, but its
relevance ranking does not order the pages: a cursor can only resume from
values it compares, and the rank is not one of them. For best-match-first
pages, use Query.paginate.
// 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
By default, destroy reads the matching records and then destroys each of
them, so a concurrent writer can change a record in between — the record is
destroyed even though it no longer matches the Query that selected it.
Passing lock: true closes that window, making destroy a compare-and-set
the way update(..., { skipHooks: true }) already is:
const destroyed = await Order.where({ status: 'pending' }).destroy({ lock: true })
// 0 — someone else moved every one of them out of 'pending' first
With lock: true, records are processed in batches, and each batch runs in
its own transaction which first re-selects that batch's records with an
exclusive row lock and then destroys them before committing. Because the
database re-checks the Query's conditions after acquiring each row lock,
a record a concurrent transaction has already moved out of the Query drops
out of the locked read and is never destroyed. The returned count is the
number of records actually claimed, so a caller can detect that it lost a
race by comparing the count against what it expected.
Things to know before relying on it:
Order.query().txn(txn).where({ ... }).destroy({ lock: true }) —
buys two things: the destroys become all-or-nothing (a rollback undoes the
whole run), and the row locks taken by every batch are held until you
commit. It does not extend the compare-and-set to the part of the set
the run has not reached yet: under READ COMMITTED each statement takes a
fresh snapshot, so a record moved out of the Query between batch 1 and
batch 2 still drops out of batch 2's locked read. A genuinely set-wide
guarantee needs REPEATABLE READ / SERIALIZABLE, or a single locking
statement over the whole set.Model.transaction(...) does not thread that transaction onto the
Query — Dream.transaction establishes no ambient transaction, it only
hands a txn to your callback — so each batch would open its own
transaction on a different pooled connection. Worse, if the surrounding
transaction has already written to any of the matched rows, each batch's
locked read blocks on locks the surrounding transaction holds while that
transaction awaits the destroy: a cycle across two connections, which
Postgres's deadlock detector cannot see, so it hangs rather than aborting.
Always pass the transaction with .txn(txn).batchSize defaults to 10 here, not findEach's 1000, and the
default is deliberately small. Every row in a batch stays locked for the
whole batch, including the time spent running each record's hooks and
dependent: 'destroy' cascade, so an oversized batch blocks unrelated
writers touching those rows and surfaces as timeouts somewhere else in the
application, which is very hard to trace back to this destroy. Too small a
batch, by contrast, only slows down this call, which you can see and fix.
Lower it further for models with deep dependent: 'destroy' cascades or
expensive destroy hooks.FOR UPDATE: if another transaction already holds a lock
on one of the batch's rows, this call blocks until that transaction ends,
holding its own transaction and its pooled connection open the whole
time. Dream sets no lock_timeout or statement_timeout on a row-lock
wait — the one it does set, around a sortable model's scope-lock
acquisition, is restored before the rows are claimed — so a long-running
writer on the other side stalls the run indefinitely and, at scale, ties
up connections. If that is a risk in your application, set a
lock_timeout on the connection so a stuck batch fails instead of
hanging.limit or offset on the Query is incompatible with destroy on
every path. The batches — locked and unlocked alike — re-apply the
Query's conditions to each window, where a limit or offset would skip
or truncate rows inside every window instead of bounding the run as a
whole, so destroy throws rather than silently corrupting the
windows.lock. Plain destroy() (hooks and cascade, no locking) or
delete (a single statement, no hooks or cascade)
are the right tools for bulk removal.To bypass a default scope, chain removeDefaultScope onto the Query
before calling this method; it is Query state, not an option, and it
carries through to the cascade.
Options for destroying the instance
OptionalbatchSize?: numberThe number of records to process per batch. Must be a positive integer. Defaults to 10 when lock is true, and to 1000 otherwise
Optionalcascade?: booleanIf false, skips destroying associations marked dependent: 'destroy'. Defaults to true
Optionallock?: booleanIf true, each batch is re-selected with an exclusive row lock inside its own transaction before being destroyed, making the destroy a compare-and-set. Defaults to false
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.where({ deletedAt: null }).findEach(user => {
DreamApp.log(user)
})
// User{id: 1}
// User{id: 2}
IMPORTANT: findEach always iterates in ascending primary key order.
Any order applied to the Query is discarded; passing one has no effect
on the order records are visited in. The same goes for the order an
association declares, when the Query is an associationQuery or joins
that association, and for the relevance ranking of a similarity condition
(ops.similarity, ops.wordSimilarity, ops.strictWordSimilarity),
which still decides which records are visited, but not in what order.
findEach visits the records Query.all returns: a Query that
joins a HasOne or HasMany association (directly, through an association
it joins, or as the association an associationQuery goes through)
returns a record once for each row the join matches, and findEach
visits it once for each of those rows, the rows of one record in
ascending order of the joined rows' primary keys. Call distinct() to
visit each record once.
This is not a limitation that can be lifted. findEach guarantees that
every matching row is visited exactly once, and it delivers that by
paginating on a keyset: each batch selects the next batchSize rows
whose primary keys (the record's, then the joined rows') sort after the
last row of the previous batch. That guarantee only holds over a stable,
unique key. Ordering by a mutable, non-unique column would mean a record
whose sort value changed partway through the iteration could be skipped
entirely or visited twice. (Query.cursorPaginate can honor an
arbitrary order precisely because a paginated UI tolerates that anomaly;
a full traversal cannot.)
A Query that keeps one record of each group (one that calls distinct,
or an associationQuery of an association that declares distinct, or
a Query that joins one) cannot be paginated that way: its own order, or
the association's, chooses which record of each group it returns, and a
batch that narrowed it to the rows after the previous batch would let it
choose another. So findEach reads the keys of all of its rows before
visiting the first, and visits those whose rows still match when their
batch is read. It visits the rows the Query returned when the walk began,
less any destroyed or changed so that they no longer match the Query's
conditions, and not a row the Query returns only after the walk began,
such as the next record of a group whose record the callback destroyed.
If you need records processed in a particular order, load them with a
regular ordered query instead of findEach.
A limit or offset on the Query cannot compose with the batches —
each batch re-applies the Query's conditions, so the limit would be
silently replaced by batchSize and the offset re-applied to every
batch — so a limit- or offset-carrying Query throws at runtime.
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 oncepreload/load, and saves — which tolerate
schema/image skew (e.g. a rolling deploy dropping a column while
containers compiled against the previous schema are still draining) —
leftJoinPreload must enumerate every compiled column of every joined
model under per-alias names (per-alias * is not expressible in a
single flat row), so an unplanned column drop breaks
leftJoinPreload queries for the duration of the rollout window. A
planned drop is safe when performed via the two-deploy process
documented on the ignoredColumns getter of Dream: declaring the
column ignored removes it from the generated schema, so leftJoinPreload
stops naming it a full deploy before the column is actually dropped.const posts = await user.associationQuery('posts').leftJoinPreload('comments', { and: { visibility: '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 limit is incompatible with paginate and leftJoinPreload, and with
the batched and set-write operations — findEach, pluckEach,
destroy, reallyDestroy, undestroy, and update — none of which can
honor one: the batched and set-write operations throw
BatchingIncompatibleWithLimitOrOffset on a limit-carrying Query.
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 max value of the specified column within each group, keyed by
the value of the provided group column (a SQL GROUP BY).
await CompositionAsset.query().maxBy('name', 'score')
// Map(2) { 'primary' => 9, 'secondary' => 4 }
Any where clauses, base scopes (soft-delete / STI), and joined-association
and clauses on the Query carry through automatically. A joined-association
column may be grouped on or aggregated by passing its namespaced name (e.g.
'compositionAssets.name').
Only groups with at least one matching row appear in the Map (inherent to
GROUP BY). When the group column is nullable, records with a null value
are grouped under a real null key; a group whose aggregated values are all
null yields a null value.
the column to group by (base or joined-association-namespaced)
the column to take the max of within each group
A Map from each present group value to the max of the aggregated column in that group
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
Retrieves the min value of the specified column within each group, keyed by
the value of the provided group column (a SQL GROUP BY).
await CompositionAsset.query().minBy('name', 'score')
// Map(2) { 'primary' => 1, 'secondary' => 4 }
Any where clauses, base scopes (soft-delete / STI), and joined-association
and clauses on the Query carry through automatically. A joined-association
column may be grouped on or aggregated by passing its namespaced name (e.g.
'compositionAssets.name').
Only groups with at least one matching row appear in the Map (inherent to
GROUP BY). When the group column is nullable, records with a null value
are grouped under a real null key; a group whose aggregated values are all
null yields a null value.
the column to group by (base or joined-association-namespaced)
the column to take the min of within each group
A Map from each present group value to the min of the aggregated column in that group
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}]
An offset is incompatible with paginate and leftJoinPreload, and with
the batched and set-write operations — findEach, pluckEach,
destroy, reallyDestroy, undestroy, and update — none of which can
honor one: the batched and set-write operations throw
BatchingIncompatibleWithLimitOrOffset on an offset-carrying Query.
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
Under a similarity condition (ops.similarity, ops.wordSimilarity,
ops.strictWordSimilarity), pages come best match first: records are
ordered by relevance ahead of the Query's order (or, without one, the
descending primary key). For best-match-first pages, use paginate
rather than Query.cursorPaginate, which does not order by
relevance.
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
pluckEach reads the rows Query.findEach visits, in the same
batches and the same order: ascending primary key, then, for a Query
that joins a HasOne or HasMany association, the joined rows' primary
keys, so each row the join returns is plucked once. Like findEach, it
discards the Query's order, an association's declared order and the
relevance ranking of a similarity condition, and it reads a Query that
keeps one record of each group (one that calls distinct, or over an
association that declares distinct) from the keys of all of its rows,
read before the first batch.
A limit or offset on the Query cannot compose with the batches —
each batch re-applies the Query's conditions, so the limit would be
silently replaced by the batch size and the offset re-applied to every
batch — so a limit- or offset-carrying Query throws at runtime.
a list of column names to pluck, followed by a callback function to call for each set of found fields
void
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
pluckEach reads the rows Query.findEach visits, in the same
batches and the same order: ascending primary key, then, for a Query
that joins a HasOne or HasMany association, the joined rows' primary
keys, so each row the join returns is plucked once. Like findEach, it
discards the Query's order, an association's declared order and the
relevance ranking of a similarity condition, and it reads a Query that
keeps one record of each group (one that calls distinct, or over an
association that declares distinct) from the keys of all of its rows,
read before the first batch.
A limit or offset on the Query cannot compose with the batches —
each batch re-applies the Query's conditions, so the limit would be
silently replaced by the batch size and the offset re-applied to every
batch — so a limit- or offset-carrying Query throws at runtime.
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', { and: { visibility: '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, permanently removing each matched record from the database instead of soft deleting it.
The SoftDelete decorator is bypassed when
deleting each matched record, not when
matching: the Query selects under its default
scopes, dream:SoftDelete among them, so on a
SoftDelete model it matches only records that
have not already been soft deleted. Reaching
soft deleted records takes
removeDefaultScope('dream:SoftDelete') on
the Query.
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
await User.removeDefaultScope('dream:SoftDelete')
.where({ email: ops.ilike('%burpcollaborator%') })
.reallyDestroy()
// 12
A limit or offset on the Query is incompatible with reallyDestroy,
exactly as it is with Query.destroy: a limit- or offset-carrying
Query throws at runtime.
To bypass a default scope, chain removeDefaultScope onto the Query
before calling this method; it is Query state, not an option, and it
carries through to the cascade.
Options for destroying the instance
OptionalbatchSize?: numberThe number of records to process per batch. Must be a positive integer. Defaults to 10 when lock is true, and to 1000 otherwise
Optionalcascade?: booleanIf false, skips destroying associations marked dependent: 'destroy'. Defaults to true
Optionallock?: booleanIf true, each batch is re-selected with an exclusive row lock inside its own transaction before being destroyed, making the destroy a compare-and-set. See Query.destroy for the full semantics. Defaults to false
OptionalskipHooks?: booleanIf true, skips applying model hooks during the destroy operation. Defaults to false
The number of records that were removed
A new Query which bypasses user-removable default scopes
Use removeDefaultScope instead. This method will become private in v3.
Prevents user-removable default scopes from applying when the Query is executed, including when associations are loaded. An STI child's reserved type discriminator remains enforced; query the STI base model to span its registered children.
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.
Pages follow the Query's order, preceded by the order declared on any
association the Query is built on or joins (so an associationQuery of
an ordered association pages in that association's order), with
ascending primary key as the final tiebreaker; with neither, pages run in
ascending primary key order. If those orders already include the primary
key (e.g.: id: 'asc'), the implicit primary key ordering is omitted.
A Query that joins a HasOne or HasMany association (directly, through an
association it joins, or as the association an associationQuery goes
through) returns a record once for each row the join matches, as
Query.all does, and the pages return each of those rows once,
sorting the rows of one record by the joined rows' primary keys after the
primary key. The cursor of such a Query is an opaque string that names
its last row by primary keys, the record's and the joined rows' (readable
by whoever holds it; it is not encrypted), and the next page starts after
that row, or, if the row is gone, after the record's first row. Any other
Query's cursor is its last record's primary key. A record's primary key
is always accepted as a cursor: the next page starts after that record's
first row. A cursor carries no other value its row sorts by: those are
read from the database when the next page is.
A Query that calls distinct on its primary key (as distinct() does,
e.g.: User.innerJoin('posts').distinct()) returns each record once, as
Query.all does, and so do its pages, whose cursor is the last
record's primary key. A Query that calls distinct on another column has
to be ordered by that column first (e.g.:
Post.distinct('category').order({ category: 'asc', createdAt: 'desc' })),
or the database rejects its pages. Its pages return the records
Query.all returns, the one its order keeps for each value of that
column (the newest post of each category), and its cursor is also the
last record's primary key. Over an association that declares distinct
and an order led by its distinct column (an associationQuery of it, or
a Query that joins it), the pages return the rows Query.all
returns: the row the association's order keeps for each value of its
distinct column.
A similarity condition (ops.similarity, ops.wordSimilarity,
ops.strictWordSimilarity) still decides which records match, but its
relevance ranking does not order the pages: a cursor can only resume from
values it compares, and the rank is not one of them. For best-match-first
pages, use Query.paginate.
// 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
Retrieves the sum of the specified column within each group, keyed by
the value of the provided group column (a SQL GROUP BY).
await CompositionAsset.query().sumBy('name', 'score')
// Map(2) { 'primary' => 10, 'secondary' => 4 }
Any where clauses, base scopes (soft-delete / STI), and joined-association
and clauses on the Query carry through automatically. A joined-association
column may be grouped on or aggregated by passing its namespaced name (e.g.
'compositionAssets.name').
Only groups with at least one matching row appear in the Map (inherent to
GROUP BY). When the group column is nullable, records with a null value
are grouped under a real null key; a group whose aggregated values are all
null yields a null value.
the column to group by (base or joined-association-namespaced)
the column to sum within each group
A Map from each present group value to the sum of the aggregated column in that group
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
A limit or offset on the Query cannot compose with the batch windows
undestroy iterates in — each batch re-applies the Query's conditions,
so the limit would be silently replaced by the batch size and the offset
re-applied to every window — so a limit- or offset-carrying Query throws
at runtime.
To bypass a default scope, chain removeDefaultScope onto the Query
before calling this method; it is Query state, not an option, and it
carries through to the cascade. The SoftDelete scope is always bypassed.
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
Instead of an attributes object, the first argument may be a callback
that derives each record's attributes from the record itself. The
callback (sync or async) receives each matching record and returns the
attributes to write for it, or undefined to skip it — a skipped record
gets no write, runs no hooks, and is not counted (a null return, from
untyped callers, is treated the same). Only the returned attributes are
written: the callback receives a clone of the record, so mutations made
directly on the received record are discarded rather than riding along
with the write. With a callback, the options object and its lock key
are mandatory, and lock is a full boolean, so every call site says out
loud whether the callback runs under row locks:
await User.query().update(
user => (user.email.endsWith('@example.com') ? { name: 'example user' } : undefined),
{ lock: false }
)
Under lock: false, the callback runs once per matching record —
batched reads (batchSize defaults to 1000 here), no locks, no claim
step, no compare-and-set guarantee — the right shape for a derived
backfill where racing writers are not a concern. (On a @ReplicaSafe
model, these unlocked batch reads may come from a lagging replica,
feeding stale column values into the callback; use lock: true to
derive from the freshest committed state — its transactional reads pin
the primary.) Under lock: true, the callback runs once per claimed
record, under that record's row lock, inside the batch's transaction,
receiving a clone of the freshly locked read; Dream applies the returned
attributes to the record it holds and writes before the batch commits,
so there is no transaction handle for the caller to forget. (The record
the callback receives is not bound to the batch's transaction — see the
callback bullet below before touching the database from inside a
callback.) That makes the callback form the tool
for serializing writers on state a WHERE clause cannot express — for
example an @Encrypted column, whose ciphertext changes on every write
and whose plaintext has no column to compare against in the database:
const rotated = await User.where({ id: userId }).update(
user => (user.secret === expectedSecret ? { secret: newSecret } : undefined),
{ lock: true }
)
// 1 — or 0, if a competing writer got there first (the callback saw a
// different secret and skipped the record)
With a callback, the returned count is the number of records the
callback returned attributes for: skipped records are not counted, and
under lock: true records that lost the race never reach the callback
at all. (As with the attributes form, a write that turns out to be a
no-op still counts — the count means "the callback chose to write", not
"SQL was emitted".)
The lock option turns the update into a guarded (compare-and-set)
update: each record is written only if it still matches the Query at the
moment an exclusive row lock on it is acquired. Use it when the Query's
conditions are the thing being changed and a concurrent writer racing this
call must not have its write clobbered:
const updated = await Order.where({ status: 'pending' })
.update({ status: 'processing' }, { lock: true })
// 0 — someone else moved every one of them out of 'pending' first
With lock: true, records are processed in batches, and each batch runs
in its own transaction which first re-selects that batch's records with an
exclusive row lock and then updates them before committing. Because the
database re-checks the Query's conditions after acquiring each row lock, a
record a concurrent transaction has already moved out of the Query drops
out of the locked read and is never updated. The returned count is the
number of records claimed and submitted for writing, so a caller can
detect that it lost a race by comparing the count against what it
expected. (A claimed record whose write turns out to be a no-op — the
attributes already match, or a hook cleared every dirty attribute — still
counts; the count is not a guarantee that SQL was emitted per counted
record.)
Things to know before relying on it:
Order.query().txn(txn).where({ ... }).update({ ... }, { lock: true }) — buys two things: the updates become all-or-nothing (a
rollback undoes the whole run), and the row locks taken by every batch
are held until you commit. It does not extend the compare-and-set to
the part of the set the run has not reached yet: under READ COMMITTED
each statement takes a fresh snapshot, so a record moved out of the
Query between batch 1 and batch 2 still drops out of batch 2's locked
read. A genuinely set-wide guarantee needs REPEATABLE READ /
SERIALIZABLE, or a single locking statement over the whole set.Model.transaction(...) does not thread that transaction
onto the Query — Dream.transaction establishes no ambient transaction,
it only hands a txn to your callback — so each batch would open its
own transaction on a different pooled connection. Worse, if the
surrounding transaction has already written to any of the matched rows,
each batch's locked read blocks on locks the surrounding transaction
holds while that transaction awaits the update: a cycle across two
connections, which Postgres's deadlock detector cannot see, so it hangs
rather than aborting. Always pass the transaction with .txn(txn).batchSize defaults to 10 here, not findEach's 1000, and the
default is deliberately small. Every row in a batch stays locked for the
whole batch, including the time spent running each record's hooks — and,
on the callback form, the time spent running the Query's preload
queries, which are fetched inside the lock window (the attributes form
strips preloads, which it never consumes) — so an oversized batch blocks
unrelated writers touching those rows and surfaces as timeouts somewhere
else in the application, which is very hard to trace back to this
update. Too small a batch, by contrast, only slows down this call, which
you can see and fix. Lower it further for models with expensive update
hooks.await record.update(...)) goes out on a different pooled
connection and blocks on the batch's own row locks while the batch
transaction waits on the callback — a cycle across two connections that
the database's deadlock detector cannot see, so with no lock_timeout
the run hangs indefinitely and pins both connections. Reads through the
record (reload, associationQuery, ...) likewise run on a separate
connection and snapshot. Derive the attributes and return them; Dream
performs the write inside the batch's transaction.lock: true, a record is not re-checked before it is
written. The unlocked per-record paths — an attributes object without
lock: true or skipHooks: true, and a callback under lock: false —
read a batch, then write each record by its primary key alone, with no
compare-and-set. What a concurrent delete between that read and a
record's write does depends on what the delete leaves behind. A soft
delete (destroy() on a @SoftDelete model, which generated models are
by default) leaves the row in place, so the write lands on the
soft-deleted record: its update hooks run, it is counted, and its
deletedAt stays set. A hard delete (reallyDestroy(), a Query's
delete(), or destroy() on a model without @SoftDelete) removes the
row, so that record's write throws CannotSaveMissingDream — after its
beforeUpdate and beforeSave hooks have run — and the run rejects
without returning a count. Every record written before it, in its own
batch as well as earlier ones, stays committed, unless the Query carries
.txn(txn), which puts those writes in the caller's transaction. (A
record whose write turns out to be a no-op emits no SQL, so it does not
throw even if its row is gone, and it is counted.) The single-statement
skipHooks attributes form is one UPDATE ... WHERE that carries the
Query's default scopes, so it never writes a soft-deleted record and
cannot fail part-way through. Use lock: true when deleters may race
the update: its locked re-read re-applies the Query's conditions and
default scopes, so a soft- or hard-deleted record drops out of the run,
neither written nor counted. (A Query that removes the
dream:SoftDelete default scope reads and writes soft-deleted records
on every path.)limit or offset on the Query is incompatible with update on
every path. The batched paths re-apply the Query's conditions to each
batch window, where a limit or offset would skip or truncate rows
inside every window instead of bounding the run as a whole, and the
single-statement skipHooks attributes form cannot express either in
its SQL UPDATE. Every form throws rather than silently corrupting the
windows or ignoring the caller's scope.FOR UPDATE: if another transaction already holds a
lock on one of the batch's rows, this call blocks until that transaction
ends, holding its own transaction and its pooled connection open the
whole time. Dream sets no lock_timeout or statement_timeout on a
row-lock wait — the one it does set, around a sortable model's scope-lock
acquisition, is restored before the rows are claimed — so a long-running
writer on the other side stalls the run indefinitely and, at scale, ties
up connections. If that is a risk in your application, set a
lock_timeout on the connection so a stuck batch fails instead of
hanging.skipHooks composes with lock, but stays on the per-instance
path. With an attributes object and no lock, skipHooks: true
issues a single UPDATE ... WHERE statement that writes raw columns —
with one refusal: it will not write a non-null value into an
@Encrypted backing column, since nothing on that path encrypts it, and
throws instead; null, which clears the column, is permitted.
With lock: true, skipHooks: true skips the model hooks on each
claimed record but still writes through the instance, so custom setters
— including the setters encrypted attributes encrypt through — run as
they would for a plain instance update. (Virtual attributes whose
processing lives in a hook are still skipped, exactly as in an instance
update with skipHooks.) A callback never takes the single-statement
path: skipHooks beside a callback only skips the hooks on each
per-record write, under lock: true and lock: false alike.lock. Plain update(attributes) (hooks, no locking) or
update(attributes, { skipHooks: true }) (a single statement, no hooks)
are the right tools for bulk updates.Options for updating the instance
OptionalbatchSize?: numberThe number of records to process per batch. Must be a positive integer. Defaults to 10 when lock is true, and to 1000 for the unlocked callback form
If true, each batch is re-selected with an exclusive row lock inside its own transaction before being updated, making the update a compare-and-set. Defaults to false for the attributes form; with a callback there is no default — an explicit boolean is required. One typing asymmetry to know: with an attributes object, an explicit lock: false keeps the raw single-statement (DreamTableSchema) typing that skipHooks uses, so virtual/encrypted attributes type-check only when the options object is omitted entirely or lock is true
OptionalskipHooks?: booleanIf true, skips applying model hooks. Defaults to false
The number of records that were updated
Updates all records matching the Query
await User.where({ email: ops.ilike('%burpcollaborator%') }).update({ email: null })
// 12
Instead of an attributes object, the first argument may be a callback
that derives each record's attributes from the record itself. The
callback (sync or async) receives each matching record and returns the
attributes to write for it, or undefined to skip it — a skipped record
gets no write, runs no hooks, and is not counted (a null return, from
untyped callers, is treated the same). Only the returned attributes are
written: the callback receives a clone of the record, so mutations made
directly on the received record are discarded rather than riding along
with the write. With a callback, the options object and its lock key
are mandatory, and lock is a full boolean, so every call site says out
loud whether the callback runs under row locks:
await User.query().update(
user => (user.email.endsWith('@example.com') ? { name: 'example user' } : undefined),
{ lock: false }
)
Under lock: false, the callback runs once per matching record —
batched reads (batchSize defaults to 1000 here), no locks, no claim
step, no compare-and-set guarantee — the right shape for a derived
backfill where racing writers are not a concern. (On a @ReplicaSafe
model, these unlocked batch reads may come from a lagging replica,
feeding stale column values into the callback; use lock: true to
derive from the freshest committed state — its transactional reads pin
the primary.) Under lock: true, the callback runs once per claimed
record, under that record's row lock, inside the batch's transaction,
receiving a clone of the freshly locked read; Dream applies the returned
attributes to the record it holds and writes before the batch commits,
so there is no transaction handle for the caller to forget. (The record
the callback receives is not bound to the batch's transaction — see the
callback bullet below before touching the database from inside a
callback.) That makes the callback form the tool
for serializing writers on state a WHERE clause cannot express — for
example an @Encrypted column, whose ciphertext changes on every write
and whose plaintext has no column to compare against in the database:
const rotated = await User.where({ id: userId }).update(
user => (user.secret === expectedSecret ? { secret: newSecret } : undefined),
{ lock: true }
)
// 1 — or 0, if a competing writer got there first (the callback saw a
// different secret and skipped the record)
With a callback, the returned count is the number of records the
callback returned attributes for: skipped records are not counted, and
under lock: true records that lost the race never reach the callback
at all. (As with the attributes form, a write that turns out to be a
no-op still counts — the count means "the callback chose to write", not
"SQL was emitted".)
The lock option turns the update into a guarded (compare-and-set)
update: each record is written only if it still matches the Query at the
moment an exclusive row lock on it is acquired. Use it when the Query's
conditions are the thing being changed and a concurrent writer racing this
call must not have its write clobbered:
const updated = await Order.where({ status: 'pending' })
.update({ status: 'processing' }, { lock: true })
// 0 — someone else moved every one of them out of 'pending' first
With lock: true, records are processed in batches, and each batch runs
in its own transaction which first re-selects that batch's records with an
exclusive row lock and then updates them before committing. Because the
database re-checks the Query's conditions after acquiring each row lock, a
record a concurrent transaction has already moved out of the Query drops
out of the locked read and is never updated. The returned count is the
number of records claimed and submitted for writing, so a caller can
detect that it lost a race by comparing the count against what it
expected. (A claimed record whose write turns out to be a no-op — the
attributes already match, or a hook cleared every dirty attribute — still
counts; the count is not a guarantee that SQL was emitted per counted
record.)
Things to know before relying on it:
Order.query().txn(txn).where({ ... }).update({ ... }, { lock: true }) — buys two things: the updates become all-or-nothing (a
rollback undoes the whole run), and the row locks taken by every batch
are held until you commit. It does not extend the compare-and-set to
the part of the set the run has not reached yet: under READ COMMITTED
each statement takes a fresh snapshot, so a record moved out of the
Query between batch 1 and batch 2 still drops out of batch 2's locked
read. A genuinely set-wide guarantee needs REPEATABLE READ /
SERIALIZABLE, or a single locking statement over the whole set.Model.transaction(...) does not thread that transaction
onto the Query — Dream.transaction establishes no ambient transaction,
it only hands a txn to your callback — so each batch would open its
own transaction on a different pooled connection. Worse, if the
surrounding transaction has already written to any of the matched rows,
each batch's locked read blocks on locks the surrounding transaction
holds while that transaction awaits the update: a cycle across two
connections, which Postgres's deadlock detector cannot see, so it hangs
rather than aborting. Always pass the transaction with .txn(txn).batchSize defaults to 10 here, not findEach's 1000, and the
default is deliberately small. Every row in a batch stays locked for the
whole batch, including the time spent running each record's hooks — and,
on the callback form, the time spent running the Query's preload
queries, which are fetched inside the lock window (the attributes form
strips preloads, which it never consumes) — so an oversized batch blocks
unrelated writers touching those rows and surfaces as timeouts somewhere
else in the application, which is very hard to trace back to this
update. Too small a batch, by contrast, only slows down this call, which
you can see and fix. Lower it further for models with expensive update
hooks.await record.update(...)) goes out on a different pooled
connection and blocks on the batch's own row locks while the batch
transaction waits on the callback — a cycle across two connections that
the database's deadlock detector cannot see, so with no lock_timeout
the run hangs indefinitely and pins both connections. Reads through the
record (reload, associationQuery, ...) likewise run on a separate
connection and snapshot. Derive the attributes and return them; Dream
performs the write inside the batch's transaction.lock: true, a record is not re-checked before it is
written. The unlocked per-record paths — an attributes object without
lock: true or skipHooks: true, and a callback under lock: false —
read a batch, then write each record by its primary key alone, with no
compare-and-set. What a concurrent delete between that read and a
record's write does depends on what the delete leaves behind. A soft
delete (destroy() on a @SoftDelete model, which generated models are
by default) leaves the row in place, so the write lands on the
soft-deleted record: its update hooks run, it is counted, and its
deletedAt stays set. A hard delete (reallyDestroy(), a Query's
delete(), or destroy() on a model without @SoftDelete) removes the
row, so that record's write throws CannotSaveMissingDream — after its
beforeUpdate and beforeSave hooks have run — and the run rejects
without returning a count. Every record written before it, in its own
batch as well as earlier ones, stays committed, unless the Query carries
.txn(txn), which puts those writes in the caller's transaction. (A
record whose write turns out to be a no-op emits no SQL, so it does not
throw even if its row is gone, and it is counted.) The single-statement
skipHooks attributes form is one UPDATE ... WHERE that carries the
Query's default scopes, so it never writes a soft-deleted record and
cannot fail part-way through. Use lock: true when deleters may race
the update: its locked re-read re-applies the Query's conditions and
default scopes, so a soft- or hard-deleted record drops out of the run,
neither written nor counted. (A Query that removes the
dream:SoftDelete default scope reads and writes soft-deleted records
on every path.)limit or offset on the Query is incompatible with update on
every path. The batched paths re-apply the Query's conditions to each
batch window, where a limit or offset would skip or truncate rows
inside every window instead of bounding the run as a whole, and the
single-statement skipHooks attributes form cannot express either in
its SQL UPDATE. Every form throws rather than silently corrupting the
windows or ignoring the caller's scope.FOR UPDATE: if another transaction already holds a
lock on one of the batch's rows, this call blocks until that transaction
ends, holding its own transaction and its pooled connection open the
whole time. Dream sets no lock_timeout or statement_timeout on a
row-lock wait — the one it does set, around a sortable model's scope-lock
acquisition, is restored before the rows are claimed — so a long-running
writer on the other side stalls the run indefinitely and, at scale, ties
up connections. If that is a risk in your application, set a
lock_timeout on the connection so a stuck batch fails instead of
hanging.skipHooks composes with lock, but stays on the per-instance
path. With an attributes object and no lock, skipHooks: true
issues a single UPDATE ... WHERE statement that writes raw columns —
with one refusal: it will not write a non-null value into an
@Encrypted backing column, since nothing on that path encrypts it, and
throws instead; null, which clears the column, is permitted.
With lock: true, skipHooks: true skips the model hooks on each
claimed record but still writes through the instance, so custom setters
— including the setters encrypted attributes encrypt through — run as
they would for a plain instance update. (Virtual attributes whose
processing lives in a hook are still skipped, exactly as in an instance
update with skipHooks.) A callback never takes the single-statement
path: skipHooks beside a callback only skips the hooks on each
per-record write, under lock: true and lock: false alike.lock. Plain update(attributes) (hooks, no locking) or
update(attributes, { skipHooks: true }) (a single statement, no hooks)
are the right tools for bulk updates.Options for updating the instance
OptionalbatchSize?: numberThe number of records to process per batch. Must be a positive integer. Defaults to 10 when lock is true, and to 1000 for the unlocked callback form
If true, each batch is re-selected with an exclusive row lock inside its own transaction before being updated, making the update a compare-and-set. Defaults to false for the attributes form; with a callback there is no default — an explicit boolean is required. One typing asymmetry to know: with an attributes object, an explicit lock: false keeps the raw single-statement (DreamTableSchema) typing that skipHooks uses, so virtual/encrypted attributes type-check only when the options object is omitted entirely or lock is true
OptionalskipHooks?: booleanIf true, skips applying model hooks. Defaults to false
The number of records that were updated
Updates all records matching the Query
await User.where({ email: ops.ilike('%burpcollaborator%') }).update({ email: null })
// 12
Instead of an attributes object, the first argument may be a callback
that derives each record's attributes from the record itself. The
callback (sync or async) receives each matching record and returns the
attributes to write for it, or undefined to skip it — a skipped record
gets no write, runs no hooks, and is not counted (a null return, from
untyped callers, is treated the same). Only the returned attributes are
written: the callback receives a clone of the record, so mutations made
directly on the received record are discarded rather than riding along
with the write. With a callback, the options object and its lock key
are mandatory, and lock is a full boolean, so every call site says out
loud whether the callback runs under row locks:
await User.query().update(
user => (user.email.endsWith('@example.com') ? { name: 'example user' } : undefined),
{ lock: false }
)
Under lock: false, the callback runs once per matching record —
batched reads (batchSize defaults to 1000 here), no locks, no claim
step, no compare-and-set guarantee — the right shape for a derived
backfill where racing writers are not a concern. (On a @ReplicaSafe
model, these unlocked batch reads may come from a lagging replica,
feeding stale column values into the callback; use lock: true to
derive from the freshest committed state — its transactional reads pin
the primary.) Under lock: true, the callback runs once per claimed
record, under that record's row lock, inside the batch's transaction,
receiving a clone of the freshly locked read; Dream applies the returned
attributes to the record it holds and writes before the batch commits,
so there is no transaction handle for the caller to forget. (The record
the callback receives is not bound to the batch's transaction — see the
callback bullet below before touching the database from inside a
callback.) That makes the callback form the tool
for serializing writers on state a WHERE clause cannot express — for
example an @Encrypted column, whose ciphertext changes on every write
and whose plaintext has no column to compare against in the database:
const rotated = await User.where({ id: userId }).update(
user => (user.secret === expectedSecret ? { secret: newSecret } : undefined),
{ lock: true }
)
// 1 — or 0, if a competing writer got there first (the callback saw a
// different secret and skipped the record)
With a callback, the returned count is the number of records the
callback returned attributes for: skipped records are not counted, and
under lock: true records that lost the race never reach the callback
at all. (As with the attributes form, a write that turns out to be a
no-op still counts — the count means "the callback chose to write", not
"SQL was emitted".)
The lock option turns the update into a guarded (compare-and-set)
update: each record is written only if it still matches the Query at the
moment an exclusive row lock on it is acquired. Use it when the Query's
conditions are the thing being changed and a concurrent writer racing this
call must not have its write clobbered:
const updated = await Order.where({ status: 'pending' })
.update({ status: 'processing' }, { lock: true })
// 0 — someone else moved every one of them out of 'pending' first
With lock: true, records are processed in batches, and each batch runs
in its own transaction which first re-selects that batch's records with an
exclusive row lock and then updates them before committing. Because the
database re-checks the Query's conditions after acquiring each row lock, a
record a concurrent transaction has already moved out of the Query drops
out of the locked read and is never updated. The returned count is the
number of records claimed and submitted for writing, so a caller can
detect that it lost a race by comparing the count against what it
expected. (A claimed record whose write turns out to be a no-op — the
attributes already match, or a hook cleared every dirty attribute — still
counts; the count is not a guarantee that SQL was emitted per counted
record.)
Things to know before relying on it:
Order.query().txn(txn).where({ ... }).update({ ... }, { lock: true }) — buys two things: the updates become all-or-nothing (a
rollback undoes the whole run), and the row locks taken by every batch
are held until you commit. It does not extend the compare-and-set to
the part of the set the run has not reached yet: under READ COMMITTED
each statement takes a fresh snapshot, so a record moved out of the
Query between batch 1 and batch 2 still drops out of batch 2's locked
read. A genuinely set-wide guarantee needs REPEATABLE READ /
SERIALIZABLE, or a single locking statement over the whole set.Model.transaction(...) does not thread that transaction
onto the Query — Dream.transaction establishes no ambient transaction,
it only hands a txn to your callback — so each batch would open its
own transaction on a different pooled connection. Worse, if the
surrounding transaction has already written to any of the matched rows,
each batch's locked read blocks on locks the surrounding transaction
holds while that transaction awaits the update: a cycle across two
connections, which Postgres's deadlock detector cannot see, so it hangs
rather than aborting. Always pass the transaction with .txn(txn).batchSize defaults to 10 here, not findEach's 1000, and the
default is deliberately small. Every row in a batch stays locked for the
whole batch, including the time spent running each record's hooks — and,
on the callback form, the time spent running the Query's preload
queries, which are fetched inside the lock window (the attributes form
strips preloads, which it never consumes) — so an oversized batch blocks
unrelated writers touching those rows and surfaces as timeouts somewhere
else in the application, which is very hard to trace back to this
update. Too small a batch, by contrast, only slows down this call, which
you can see and fix. Lower it further for models with expensive update
hooks.await record.update(...)) goes out on a different pooled
connection and blocks on the batch's own row locks while the batch
transaction waits on the callback — a cycle across two connections that
the database's deadlock detector cannot see, so with no lock_timeout
the run hangs indefinitely and pins both connections. Reads through the
record (reload, associationQuery, ...) likewise run on a separate
connection and snapshot. Derive the attributes and return them; Dream
performs the write inside the batch's transaction.lock: true, a record is not re-checked before it is
written. The unlocked per-record paths — an attributes object without
lock: true or skipHooks: true, and a callback under lock: false —
read a batch, then write each record by its primary key alone, with no
compare-and-set. What a concurrent delete between that read and a
record's write does depends on what the delete leaves behind. A soft
delete (destroy() on a @SoftDelete model, which generated models are
by default) leaves the row in place, so the write lands on the
soft-deleted record: its update hooks run, it is counted, and its
deletedAt stays set. A hard delete (reallyDestroy(), a Query's
delete(), or destroy() on a model without @SoftDelete) removes the
row, so that record's write throws CannotSaveMissingDream — after its
beforeUpdate and beforeSave hooks have run — and the run rejects
without returning a count. Every record written before it, in its own
batch as well as earlier ones, stays committed, unless the Query carries
.txn(txn), which puts those writes in the caller's transaction. (A
record whose write turns out to be a no-op emits no SQL, so it does not
throw even if its row is gone, and it is counted.) The single-statement
skipHooks attributes form is one UPDATE ... WHERE that carries the
Query's default scopes, so it never writes a soft-deleted record and
cannot fail part-way through. Use lock: true when deleters may race
the update: its locked re-read re-applies the Query's conditions and
default scopes, so a soft- or hard-deleted record drops out of the run,
neither written nor counted. (A Query that removes the
dream:SoftDelete default scope reads and writes soft-deleted records
on every path.)limit or offset on the Query is incompatible with update on
every path. The batched paths re-apply the Query's conditions to each
batch window, where a limit or offset would skip or truncate rows
inside every window instead of bounding the run as a whole, and the
single-statement skipHooks attributes form cannot express either in
its SQL UPDATE. Every form throws rather than silently corrupting the
windows or ignoring the caller's scope.FOR UPDATE: if another transaction already holds a
lock on one of the batch's rows, this call blocks until that transaction
ends, holding its own transaction and its pooled connection open the
whole time. Dream sets no lock_timeout or statement_timeout on a
row-lock wait — the one it does set, around a sortable model's scope-lock
acquisition, is restored before the rows are claimed — so a long-running
writer on the other side stalls the run indefinitely and, at scale, ties
up connections. If that is a risk in your application, set a
lock_timeout on the connection so a stuck batch fails instead of
hanging.skipHooks composes with lock, but stays on the per-instance
path. With an attributes object and no lock, skipHooks: true
issues a single UPDATE ... WHERE statement that writes raw columns —
with one refusal: it will not write a non-null value into an
@Encrypted backing column, since nothing on that path encrypts it, and
throws instead; null, which clears the column, is permitted.
With lock: true, skipHooks: true skips the model hooks on each
claimed record but still writes through the instance, so custom setters
— including the setters encrypted attributes encrypt through — run as
they would for a plain instance update. (Virtual attributes whose
processing lives in a hook are still skipped, exactly as in an instance
update with skipHooks.) A callback never takes the single-statement
path: skipHooks beside a callback only skips the hooks on each
per-record write, under lock: true and lock: false alike.lock. Plain update(attributes) (hooks, no locking) or
update(attributes, { skipHooks: true }) (a single statement, no hooks)
are the right tools for bulk updates.Optionaloptions: { lock?: false; skipHooks?: boolean }Options for updating the instance
Optionallock?: falseIf true, each batch is re-selected with an exclusive row lock inside its own transaction before being updated, making the update a compare-and-set. Defaults to false for the attributes form; with a callback there is no default — an explicit boolean is required. One typing asymmetry to know: with an attributes object, an explicit lock: false keeps the raw single-statement (DreamTableSchema) typing that skipHooks uses, so virtual/encrypted attributes type-check only when the options object is omitted entirely or lock is true
OptionalskipHooks?: booleanIf true, skips applying model hooks. Defaults to false
The number of records that were updated
Updates all records matching the Query
await User.where({ email: ops.ilike('%burpcollaborator%') }).update({ email: null })
// 12
Instead of an attributes object, the first argument may be a callback
that derives each record's attributes from the record itself. The
callback (sync or async) receives each matching record and returns the
attributes to write for it, or undefined to skip it — a skipped record
gets no write, runs no hooks, and is not counted (a null return, from
untyped callers, is treated the same). Only the returned attributes are
written: the callback receives a clone of the record, so mutations made
directly on the received record are discarded rather than riding along
with the write. With a callback, the options object and its lock key
are mandatory, and lock is a full boolean, so every call site says out
loud whether the callback runs under row locks:
await User.query().update(
user => (user.email.endsWith('@example.com') ? { name: 'example user' } : undefined),
{ lock: false }
)
Under lock: false, the callback runs once per matching record —
batched reads (batchSize defaults to 1000 here), no locks, no claim
step, no compare-and-set guarantee — the right shape for a derived
backfill where racing writers are not a concern. (On a @ReplicaSafe
model, these unlocked batch reads may come from a lagging replica,
feeding stale column values into the callback; use lock: true to
derive from the freshest committed state — its transactional reads pin
the primary.) Under lock: true, the callback runs once per claimed
record, under that record's row lock, inside the batch's transaction,
receiving a clone of the freshly locked read; Dream applies the returned
attributes to the record it holds and writes before the batch commits,
so there is no transaction handle for the caller to forget. (The record
the callback receives is not bound to the batch's transaction — see the
callback bullet below before touching the database from inside a
callback.) That makes the callback form the tool
for serializing writers on state a WHERE clause cannot express — for
example an @Encrypted column, whose ciphertext changes on every write
and whose plaintext has no column to compare against in the database:
const rotated = await User.where({ id: userId }).update(
user => (user.secret === expectedSecret ? { secret: newSecret } : undefined),
{ lock: true }
)
// 1 — or 0, if a competing writer got there first (the callback saw a
// different secret and skipped the record)
With a callback, the returned count is the number of records the
callback returned attributes for: skipped records are not counted, and
under lock: true records that lost the race never reach the callback
at all. (As with the attributes form, a write that turns out to be a
no-op still counts — the count means "the callback chose to write", not
"SQL was emitted".)
The lock option turns the update into a guarded (compare-and-set)
update: each record is written only if it still matches the Query at the
moment an exclusive row lock on it is acquired. Use it when the Query's
conditions are the thing being changed and a concurrent writer racing this
call must not have its write clobbered:
const updated = await Order.where({ status: 'pending' })
.update({ status: 'processing' }, { lock: true })
// 0 — someone else moved every one of them out of 'pending' first
With lock: true, records are processed in batches, and each batch runs
in its own transaction which first re-selects that batch's records with an
exclusive row lock and then updates them before committing. Because the
database re-checks the Query's conditions after acquiring each row lock, a
record a concurrent transaction has already moved out of the Query drops
out of the locked read and is never updated. The returned count is the
number of records claimed and submitted for writing, so a caller can
detect that it lost a race by comparing the count against what it
expected. (A claimed record whose write turns out to be a no-op — the
attributes already match, or a hook cleared every dirty attribute — still
counts; the count is not a guarantee that SQL was emitted per counted
record.)
Things to know before relying on it:
Order.query().txn(txn).where({ ... }).update({ ... }, { lock: true }) — buys two things: the updates become all-or-nothing (a
rollback undoes the whole run), and the row locks taken by every batch
are held until you commit. It does not extend the compare-and-set to
the part of the set the run has not reached yet: under READ COMMITTED
each statement takes a fresh snapshot, so a record moved out of the
Query between batch 1 and batch 2 still drops out of batch 2's locked
read. A genuinely set-wide guarantee needs REPEATABLE READ /
SERIALIZABLE, or a single locking statement over the whole set.Model.transaction(...) does not thread that transaction
onto the Query — Dream.transaction establishes no ambient transaction,
it only hands a txn to your callback — so each batch would open its
own transaction on a different pooled connection. Worse, if the
surrounding transaction has already written to any of the matched rows,
each batch's locked read blocks on locks the surrounding transaction
holds while that transaction awaits the update: a cycle across two
connections, which Postgres's deadlock detector cannot see, so it hangs
rather than aborting. Always pass the transaction with .txn(txn).batchSize defaults to 10 here, not findEach's 1000, and the
default is deliberately small. Every row in a batch stays locked for the
whole batch, including the time spent running each record's hooks — and,
on the callback form, the time spent running the Query's preload
queries, which are fetched inside the lock window (the attributes form
strips preloads, which it never consumes) — so an oversized batch blocks
unrelated writers touching those rows and surfaces as timeouts somewhere
else in the application, which is very hard to trace back to this
update. Too small a batch, by contrast, only slows down this call, which
you can see and fix. Lower it further for models with expensive update
hooks.await record.update(...)) goes out on a different pooled
connection and blocks on the batch's own row locks while the batch
transaction waits on the callback — a cycle across two connections that
the database's deadlock detector cannot see, so with no lock_timeout
the run hangs indefinitely and pins both connections. Reads through the
record (reload, associationQuery, ...) likewise run on a separate
connection and snapshot. Derive the attributes and return them; Dream
performs the write inside the batch's transaction.lock: true, a record is not re-checked before it is
written. The unlocked per-record paths — an attributes object without
lock: true or skipHooks: true, and a callback under lock: false —
read a batch, then write each record by its primary key alone, with no
compare-and-set. What a concurrent delete between that read and a
record's write does depends on what the delete leaves behind. A soft
delete (destroy() on a @SoftDelete model, which generated models are
by default) leaves the row in place, so the write lands on the
soft-deleted record: its update hooks run, it is counted, and its
deletedAt stays set. A hard delete (reallyDestroy(), a Query's
delete(), or destroy() on a model without @SoftDelete) removes the
row, so that record's write throws CannotSaveMissingDream — after its
beforeUpdate and beforeSave hooks have run — and the run rejects
without returning a count. Every record written before it, in its own
batch as well as earlier ones, stays committed, unless the Query carries
.txn(txn), which puts those writes in the caller's transaction. (A
record whose write turns out to be a no-op emits no SQL, so it does not
throw even if its row is gone, and it is counted.) The single-statement
skipHooks attributes form is one UPDATE ... WHERE that carries the
Query's default scopes, so it never writes a soft-deleted record and
cannot fail part-way through. Use lock: true when deleters may race
the update: its locked re-read re-applies the Query's conditions and
default scopes, so a soft- or hard-deleted record drops out of the run,
neither written nor counted. (A Query that removes the
dream:SoftDelete default scope reads and writes soft-deleted records
on every path.)limit or offset on the Query is incompatible with update on
every path. The batched paths re-apply the Query's conditions to each
batch window, where a limit or offset would skip or truncate rows
inside every window instead of bounding the run as a whole, and the
single-statement skipHooks attributes form cannot express either in
its SQL UPDATE. Every form throws rather than silently corrupting the
windows or ignoring the caller's scope.FOR UPDATE: if another transaction already holds a
lock on one of the batch's rows, this call blocks until that transaction
ends, holding its own transaction and its pooled connection open the
whole time. Dream sets no lock_timeout or statement_timeout on a
row-lock wait — the one it does set, around a sortable model's scope-lock
acquisition, is restored before the rows are claimed — so a long-running
writer on the other side stalls the run indefinitely and, at scale, ties
up connections. If that is a risk in your application, set a
lock_timeout on the connection so a stuck batch fails instead of
hanging.skipHooks composes with lock, but stays on the per-instance
path. With an attributes object and no lock, skipHooks: true
issues a single UPDATE ... WHERE statement that writes raw columns —
with one refusal: it will not write a non-null value into an
@Encrypted backing column, since nothing on that path encrypts it, and
throws instead; null, which clears the column, is permitted.
With lock: true, skipHooks: true skips the model hooks on each
claimed record but still writes through the instance, so custom setters
— including the setters encrypted attributes encrypt through — run as
they would for a plain instance update. (Virtual attributes whose
processing lives in a hook are still skipped, exactly as in an instance
update with skipHooks.) A callback never takes the single-statement
path: skipHooks beside a callback only skips the hooks on each
per-record write, under lock: true and lock: false alike.lock. Plain update(attributes) (hooks, no locking) or
update(attributes, { skipHooks: true }) (a single statement, no hooks)
are the right tools for bulk updates.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