ephys/sequelize-query-builder.md Secret

## sequelize-query-builder.md

      
    Raw
  

              sequelize-query-builder.md
            
          
    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(),
  },
})
API

Things we still need to work out


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?

Changes to existing APIs

Sequelize.literal(literalStr) accepts a new parameter bind to support escaping values in literals:
User.findAll({
  where: Sequelize.literal('id = $id', { id: 5 }),
});
New API

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);
orWhere & andWhere ambiguity

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();
Joins & Associations

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.bind

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 = ?