By default, as all the query builders in Drizzle try to conform to SQL as much as possible, you can only invoke most of the methods once.
For example, in a
SELECT statement there might only be one
WHERE clause, so you can only invoke
In the previous ORM versions, when such restrictions weren’t implemented, this example in particular was a source of confusion for many users, as they expected the query builder to “merge” multiple
.where() calls into a single condition.
This behavior is useful for conventional query building, i.e. when you create the whole query at once.
However, it becomes a problem when you want to build a query dynamically, i.e. if you have a shared function that takes a query builder and enhances it.
To solve this problem, Drizzle provides a special ‘dynamic’ mode for query builders, which removes the restriction of invoking methods only once.
To enable it, you need to call
.$dynamic() on a query builder.
Let’s see how it works by implementing a simple
withPagination function that adds
OFFSET clauses to a query based on the provided page number and an optional page size:
Note that the
withPagination function is generic, which allows you to modify the result type of the query builder inside it, for example by adding a join:
This is possible, because
PgSelect and other similar types are specifically designed to be used in dynamic query building. They can only be used in dynamic mode.
Here is the list of all types that can be used as generic parameters in dynamic query building:
...QueryBuilder types are for usage with standalone query builder
instances. DB query builders are
subclasses of them, so you can use them as well.