A query builder has been a request for sequelize for a long time (sequelize/sequelize#394)
This document is only going to talk about API design.
The approach taken here is different from the ones proposed in sequelize/sequelize#394. The Goal is to provide a small layer on top of Model.findAll
, Model.update
, etc…
While this API is designed to accomodate a future query builder for insert
, update
, and delete
, it currently focuses on select
as it is the biggest use case.
This Query Builder only builds a query object that Model static methods can consume.
const query = User
.select()
.alias('user')
.attributes(['name', 'id'])
.where({ email: { [Op.neq]: null } }) // using WhereOptions<User>
.andWhere('firstName', Op.iLike, 'Zoé') // using Sequelize.where(col, op, value)
.andWhere(`user.lastName <> $lastName`, { lastName }); // using Sequelize.literal(sql, bind)
const result: User[] = await query.limit(10).findAll();
// or
const result: User[] = await User.findAll(query.limit(10));
I'd also like to provide a WhereQueryBuilder
interface that only builds a WHERE
clause:
const whereBuilder = new WhereQueryBuilder()
.where({ email: { [Op.neq]: null } })
.andWhere({ firstName: { [Op.iLike]: 'Zoé' } });
// it can be used in as a value for `FindOptions.where`:
User.findAll({
where: whereBuilder,
});
// or used as a literal:
sequelize.query(`SELECT * FROM users WHERE ${whereBuilder.toSql({ alias: 'users' })}`, {
type: QueryTypes.SELECT,
bind: {
...whereBuilder.getBindParameters(),
},
})
- How does this work when used in associations? eg.
User.getProjects
Related to https://github.com/sequelize/meetings/issues/14 - How do we want to handle subqueries? Like TypeORM? Will need changes to
Model.find*
- How do we handle
distinct
&distinct on
?
Sequelize.literal(literalStr)
accepts a new parameter bind
to support escaping values in literals:
User.findAll({
where: Sequelize.literal('id = $id', { id: 5 }),
});
import { GroupOption, Order } from '../../../../../Documents/dev/sequelize/dist/index.js';
class Model<Attributes = any, CreationAttributes = any> {
static select<M extends Model>(): SelectQueryBuilder<M>;
}
abstract class QueryBuilder<M extends Model> {
/**
* Generates & Returns the SQL query without running it.
*/
abstract toSql(): string;
/**
* Resets the current value for `this.where` and sets it to
* Sequelize.literal(literalStr, bind)
*
* @example
* User.select()
* .where(`user.name = $name`, { name: 'Zoé' })
* .findAll();
*/
where(literalStr: string, bind: BindOrReplacements): this;
/**
* Resets the current value for `this.where` and sets it to `options`.
*
* @example
* User.select()
* .where({ name: 'Zoé' })
* .findAll();
*/
where(options: WhereOptions): this;
/**
* Adds a where clause using the AND operator.
* Behaves exactly like {@link #where} if this is the first added clause.
*
* Mixing with {@link #orWhere} is not allowed to avoid ambiguity.
* Use WhereBrackets.
*
* @example
* User.select()
* .where({ id: { [Op.gt]: 3 } })
* .andWhere(`user.name = $name`, { name: 'Zoé' })
* .findAll();
*/
andWhere(literal: string, bind: BindOrReplacements): this;
andWhere(options: WhereOptions): this;
/**
* Use to group `where` clauses together.
*
* @example
* User.select()
* .where({ id: { [Op.gt]: 3 } })
* .andWhere(qb => {
* qb.where({ name: 'Zoé' })
* .orWhere({ name: 'Name' })
* })
* .findAll();
*/
andWhere(options: WhereBrackets): this;
orWhere(literal: string, bind: BindOrReplacements): this;
orWhere(options: WhereOptions): this;
orWhere(options: WhereBrackets): this;
}
class SelectQueryBuilder<M extends Model<M>> extends QueryBuilder<M> {
// TODO: proper API for having
groupBy(group: GroupOption): this;
order(order: Order): this;
limit(limit: number): this;
offset(offset: number): this;
attributes(opts: FindAttributeOptions): this;
include(opts: Includeable): this;
// execution methods
findAll(options: FindOptionSubset): Promise<M[]>;
findOne(options: FindOptionSubset): Promise<M[]>;
count(): Promise<number>;
}
class WhereQueryBuilder<M extends Model<M>> extends QueryBuilder<M> {
toSql(options): string;
toFindOptions(): FindOptions<Attributes<M>>
getBindParameters(): Record<string, any>;
}
// inspired by TypeORM
type WhereBrackets = (callback: (qb: QueryBuilder) => void);
To avoid ambiguity, orWhere
and andWhere
methods cannot be used together.
// Invalid due to ambiguity. Is this
// WHERE (email IS NULL AND password IS NULL) OR id = 10
// or
// WHERE email IS NULL AND (password IS NULL OR id = 10)
await User
.select()
.where({ email: null })
.andWhere({ password: null })
.orWhere({ id: 10 })
.findAll();
You must use WhereBrackets
to avoid ambiguous queries.
// Ok! Generated as:
// WHERE email IS NULL AND (password IS NULL OR id = 10)
await User
.select()
.where({ email: null })
.andWhere(qb => {
qb.where({ password: null })
.orWhere({ id: 10 })
})
.findAll();
// Ok! Generated as:
// WHERE (email IS NULL AND password IS NULL) OR id = 10
await User
.select()
.where(qb => {
qb.where({ email: null })
.andWhere({ password: null })
})
.orWhere({ id: 10 })
.findAll();
Until sequelize introduces a way to have custom joins (sequelize/sequelize#11788), only .include
is provided:
await User
.select()
.include(User.associations.projects, includeBuilder => {
return includeBuilder
.attributes(['projectName']);
})
.findAll();
Sequelize escapes and inlines values passed to WhereOptions
.
Using Sequelize.bind
, we could tell sequelize "don't inline these, let the dbms handle it":
User.findAll({
where: { name: Sequelize.bind(name) }
});
// outputs roughtly:
// SELECT * FROM users WHERE name = ?
@klausXR
Your opinion is very much welcomed here :)
It is but I'm also working on the existing methods to support all of these new features (which is a lot more work than I would have thought, I've been working on this for more than 4 months).
For instance, there is currently no way to access the generated SQL, the main issue being that we don't know how to design a clean API for that.
A query builder could expose a
toSql
method even iffindAll
does notAttributes
It does look better :)
I think it should be named
attributes
, for consistency withfindAll
It's also very important, in my opinion, that the method is compatible with
FindOptions.attributes
, you should be able to re-use parts ofFindOptions
in your query builderI'm not a fan of how attributes are aliased in Sequelize. Knex uses this syntax instead:
Which we should be able to add with some magic, but it's likely that users will be confused by the fact that these two calls are interpreted differently:
Operators
I agree that operators are not ergonomic at all in Sequelize right now.
I wrote this RFC a little while ago to try and improve this but I'm still stuck on its API design.
The different RFC for improved raw SQL support should also help with this.
Still, I'm not opposed to dedicated methods for each operator :) but I don't think it will be part of the initial release, we support a lot of them
Execution
This is just my opinion but I'm personally not a fan of thenable objects, I consider them to be a bit too obscure
There is also a case for adding
findAll
: specifying options specific tofindAll
Still, if there is popular demand, it could be considered after the initial release :)
Absolutely. This is planned, its API design isn't quite complete yet
An other alternative to your example (as I personally don't plan to include
whereIn
in the first release due to the sheer amount of operators we would need to support):SubQueries in
WHERE
will be supported in the initial release, but supporting them inFROM
andJOIN
will require reworking more internals and will likely come in a follow-up releaseJoins
True but I wrote it in a very verbose way, in reality it's probably going to be closer to this:
The name of the association can also be used, further reducing the verbosity:
.include('projects', ...)
Note that
projects
is the name of an association (e.g.User.hasMany(Project, { as: 'projects' })
). Right now, all joins must have a corresponding association in Sequelize. Specifying a Model will make Sequelize look for an association with that model and use that instead. It's designed that way to the core of the project and changing it will break too many projects.I'm still interested in supporting it, but I think it will have to use another method name than
include
: