No query builder? Can we fix that?

[polanm]

I was somewhat surprised (and dismayed) by the fact that Sequelize doesn't have a query builder. This means that in order to write generic selects with optional ands, or and ins, you have to start gluing strings together, which is unplesasant to say the least. When you have array of conditions (e.g. filtering) or more complicated queries, you risk a mental breakdown (giving up and becoming a hermit). A somewhat exorbitant example below.

Let's say we want to impose a condition if someCondition == true, and also have a filter object, that contains a key to filter and values to filter by, like so:

{ gender: ['male', 'female', 'huh?'] }

Ok, let's do this thing!

var sql = '';

if(someCondition) {
  sql += 'WHERE someColumn = "' + someValue + '" '; //dont forget the space!
} 

_.each(filter, function(conditions, index)  {
  // wait, did I already write WHERE?
  sql += (sql.indexOf('WHERE') == -1) ? 'WHERE' : 'AND'; 
  sql += ' ' + index + ' IN (';
  _.each(conditions, function(value, index) {
    //let's just hope conditions is an actual array,
    //and I'm not looping through a string
    sql += '"' + value + '"';
    //frigging trailing commas
    if(index < (conditions.length - 1)) sql += ',';
  });
});
sql += ')';

console.log(sql); //prints out '''(`'OR"]"`IN'``'AND`())``EREHW'`=z̙̜͔͈͇̺ͥ͐͠ͅa̒͏̞̝̬͖ḻ̱̲̯͉̐͒ͤ̿g̖͎̒o͓̥͉̮ͦ̍̓̉̂̾ͦ'``
console.log('f@©k this.s#!t');
console.log('(╯°□°）╯︵ ┻━┻');
console.log('I\'m going out');

Yeah, yeah, I know, but many people would write it this way or worse. Bear with me.

If the query builder was there (as is the case with all other Node.js/MySQL ORMs and all PHP MySQL/ORMs I ever saw), I would do this:

var sql = new MagicalQueryBuilderFairy();

if(someCondition) sql.where('someColumn', someValue);

_.each(filter, function(conditions, index) {
  sql.in(index, conditions);
}

//I'm done. I can spend the saved time meditating||
//writing my autobiography||doing charity work.

Neat, yes? With some effort, you can work around Sequelize with the help of some independent query builder, like Squel. You can do all the fun stuff from above:

var sql = squel.select().from('whatevs');
sql.where(...);

Cool. But then...you have to close your eyes and gut the query:

Model.findAll({
  where: sql.toString().split('WHERE')[1]
});

Oh, the humanity! Anyway, you get the picture. It can be worked around, but I think many people (me) expect an 'ORM library' to have this built in.

Judging by the syntax of Sequelize where queries, I suspect that part of the code is already there. I'm willing to help with this (although I should mention am a newbie to Node), but I hoped we could first have discussion about if and how to do this. Author(s)?

tl;dr: Why don't we make a query builder? All the cool kids are doing it. (Discuss.)

[janmeier]

We are cool kids too!

https://github.com/sdepold/sequelize/blob/master/lib/dialects/mysql/query-generator.js

Have a look at the selectQuery function ;-). You can access this magical SQL creator through Sequelize.getQueryInterface().QueryGenerator

[Meaglin]

That's not a real query builder, an is used internally by sequelize.

[polanm]

janmeier, yes, thank you, I was pretty sure something like that must exist - maybe that means that most of the work is done, and what's missing is some friendly query builder class that let's you build the query and then execute itself, much like Query in Mongoose.

For illustration, a modified example from Mongoose docs:

Person
.find()
.where('name.last').equals('Ghost')
.where('age').gt(17).lt(66)
.where('likes').in(['vaporizing', 'talking'])
.limit(10)
.sort('occupation DESC')
.select('name occupation')
.exec(callback);

Something like this + documenting this feature, and I think we're all set.

[sdepold]

+1 Dunno when it will be available but yeah: +1 :)
guess this should be part of 1.7

[mickhansen]

+1

[sdepold]

FYI: https://github.com/sdepold/sequelize#180

[polanm]

sdepold: Thanks, will try to lend a hand.

[vjpr]

http://betsmartmedia.github.io/gesundheit/ is an interesting and powerful query builder which I just found today. I found there was a lot I couldn't do with node-sql.

[sdepold]

thanks for the hint!

[fixe]

Would be awesome to build something like this.

[mickhansen]

@fixe i really like the idea of https://github.com/goodybag/mongo-sql

[Meaglin]

Mongo SQL sounds awesome
Op 26 feb. 2015 09:11 schreef "Mick Hansen" notifications@github.com het
volgende:

@fixe https://github.com/fixe i really like the idea of
https://github.com/goodybag/mongo-sql

—
Reply to this email directly or view it on GitHub
#394 (comment).

[connor4312]

Any news on this? It would be great :)

This was our solution to add the Squel query builder to Sequelize. Not super beautiful, but it works.

var firstToUpper = require('../../util/firstToUpper');
var Squel = require('squel');
var _ = require('lodash');

/**
 * This plugin provides an extension for using the Squel query building
 * with Sequelize.
 *
 * @example
 * sequelize.select()
 *    .from('students')
 *    .then(function (result) {
 *
 *    });
 *
 * @param  {Sequelize} Sequelize
 */
module.exports = function (Sequelize) {
    // First bind all SELECT, UPDATE, DELETE, and INSERT queries onto
    // Sequelize. Also set the _sequelizeType in Squel so that we know
    // what to pass in Sequelize.query.
    var proxy = ['select', 'update', 'delete', 'insert'];
    proxy.forEach(function (method) {
        Squel.cls[firstToUpper(method)].prototype._sequelizeType =
            Sequelize.QueryTypes[method.toUpperCase()];

        Sequelize.prototype[method] = function () {
            var query = Squel[method].apply(Squel, arguments);
            query._sequelize = this;
            return query;
        };
    });

    /**
     * Executes the built query and returns a promise that resolves to
     * standard Sequelize results.
     * @param {Object=} options to be passed into Sequelize.query
     * @return {Promise}
     */
    Squel.cls.QueryBuilder.prototype.execute = function (options) {
        if (!this._sequelize) {
            throw new Error('Attempted to execute a query not bound to Sequelize.', this);
        }

        var query = this.toParam();
        return this._sequelize.query(query.text, _.extend({
            replacements: query.values,
            type: this._sequelizeType || Sequelize.QueryTypes.RAW
        }, options));
    };

    // Bind some additional promise methods for automatic chaining.
    var pm = ['then', 'catch', 'bind'];
    pm.forEach(function (method) {
        Squel.cls.QueryBuilder.prototype[method] = function () {
            var promise = this.execute();
            return promise[method].apply(promise, arguments);
        };
    });
};

[janmeier]

@connor4312 As far as I can see, this completely foregoes sequelize models? You'd get the same by using squel directly and calling sequelize.query with that, right?

We have improved support for mongo-sql like syntax (see below) considerably recently, so I'm not sure adding a procedural query generation style would be a good idea.

{
  where: {
    somenumber: { $gt: 42 }
    $or: [
     { someOthernumber: { $in: [1,2 ] },
     { name: { $like: 'jan%' }
   ]
  }
}

Of course we cannot yet build any kind of query you can think of (especially complicated joins) but we are definitely getting there.

What I'm saying is that I'm not sure we want to add procedural query building to sequelize core. First of all, it adds more code to maintain, and secondly we'd need to lock onto a single library - not an easy choice it seems from my short google search!

As you've seen, adding support for your favorite query builder to sequelize takes only a small amount of code, so that's the approach I'd recommend (maybe publish it as a plugin)?

[connor4312]

@janmeier you're correct in that it does not have baked-in support Sequelize models; it's mainly built for times where you're doing complex manipulation whose results, if any, would not fit into an existing model. However, adding model support would not be prohibitively difficult, and with the snippet you can manually called .exectue() and pass options to Sequelize's query function. You would indeed get the same result by calling sequelize.query, but this gives you a nice query builder 😉

Anyhow, the case for a baked-in query builder. There's already a kind of query builder in the Sequelize core, it is just not yet fully-featured or friendly for public use. I would not necessarily advocate dropping the existing query interface, but adding strong documentation, a more friendly API, and perhaps more flexibility if necessary (I've not dug into it too far) .

As you said, Sequelize continues to make progress, but I've never encountered any SQL ORM - no matter how grand or mature - which was able to build every query I eventually needed of it programmatically without a more direct way to build SQL. (SQLAlchemy is probably the closest, but that's had a decade of growth and battle-testing to get to where it is.) I think that trying to get 100% coverage, so to speak, on SQL queries using Mongo-ish syntax is a neigh impossible goal; they're fundamentally different types of data stores, and for anything more complex than basic CRUD operations querying each is fundamentally different task.

[alirizwan]

How do we add a subquery in $in
I want to do something like: WHERE ID IN (SELECT * customerID FROM table WHERE id = 3).

Is this possible with $in??

[mickhansen]

@alirizwan $in does not currently support raw queries but it probably wouldn't be much work to support $in: {$raw: '(SELECT ...)'}

[jamesaspence]

I was wondering if this issue had been tabled? It seems to have. I'd really prefer to have some sort of query builder built into sequelize, if only to match the same syntax across the board. I come from PHP's Laravel, which has both a great model system (Eloquent) and the ability to write custom queries (Query Builder), both through the same syntax.

[mickhansen]

@jamesaspence No one is working on it. Requires a lot of work obviously, a full API design and then implementation with a good test suite. Ideally i'd just like to integrate mongo-sql but it only supports PG atm.

First step would simply be to let Sequelize inter-op with raw queries well, then you could use any query builder you'd like.

[Znarkus]

If someone else needs this:

sequelize.getQueryInterface().QueryGenerator.getWhereConditions({ a: 1, b: 2, c: 3 })
// "a" = 1 AND "b" = 2 AND "c" = 3

[sushantdhiman]

@mickhansen I like the mongo-sql syntax too, it compliments sequelize really well. Would this query builder be better off as separate module or inbuilt into sequelize?

[mickhansen]

@sushantdhiman Building it into Sequelize is likely not feasible at the moment. A good intermediate step would be much better raw query support (so a user can easily map custom queries to models).

[joshrickert]

I have had some success using a dedicated query builder like Knex.js or Squel.js with sequelize.query or sequelize.literal:

const subQuery = knex
  .select()
  .column()
  .innerJoin()
  // etc..
  .toString();

  MyModel.findAll({
    where: { id: { '$in': sequelize.literal(`( ${subQuery} )`) }},
  })

It really makes me wonder if Sequelize is better off leaving query-building to the libs that specialize in it. Then the focus could be shifted toward easier co-use with these libs through improved handling of raw SQL strings.

[mickhansen]

@joshrickert That's the current thinking, less own query builder, more support for integrating raw queries into general sequelize workflow.

[connor4312]

Note to the people who use Squel in that way ( @joshrickert ) be very aware that toString() does not property escape supposedly parameterized values. This code snippet can be used to override Squel's incorrect 'escaper' with Sequelize's:

// Override the built-in escaping to use Sequelize's escaper/formatter.
Squel.cls.BaseBuilder.prototype._escapeValue = (s) => db.escape(s).replace(/^'(.+)'$/, '$1');

e.g. without that:

squel.select()
  .from('foo')
  .where('bar = ?', `'); delete from users where ('' = '`)
  .toString();

// => SELECT * FROM foo WHERE (bar = ''); delete from users where ('' = '')

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. If this is still an issue, just leave a comment 🙂

[Meaglin]

Is there a proper query builder by now ?

[aat2703]

What is the status on this issue?

[papb]

@aat2703 there has been no progress that I know of

[papb]

Sorry, closing it was a misclick, this is definitely still open 😅

[aat2703]

Hehe, i would also suggest not reinventing the wheel and using the squel library internally. What do you think about that?

It's often that you need to do complex joins that does not fit in with the default relations setup by Sequelize when joining tables etc... I haven't worked/seen any other framework that doesn't have a proper query builder...

I'll be happy to help 😉

[papb]

@aat2703 Yeah, we definitely won't reinvent the wheel, especially because we don't have time for that anyway. Some people have suggested we somehow start to use knex under the hood, which would basically solve this issue as well. I haven't thought much about it.

I'll be happy to help 😉

This is fantastic... If I had time I would try to solve all open issues haha, but obviously I don't have time. I barely have time to reply to everyone here, so whenever anyone is willing to help that is great.

Please feel free to start a PR if you have a concrete idea, or you can start by giving more ideas or asking me anything, however don't assume that I will have all the answers haha

Let's make this happen, thank you very much 😬 😬

[aat2703]

@papb no worries!

Yeah, knex would be the ideal solution it is also used in Adonis ORM etc

I'll take a look at the current api next week and what it takes to migrate to knex and i will reply back in this thread 👨‍💻

I'll be submitting a PR 📦

[papb]

@aat2703 Awesome!!

[carsonwright]

@aat2703 any news on this?

[kasvith]

I think adding knex under the hoods of sequelize will make it much cooler. Knex already has a ton of features that can help to solve many problems without an issue. I love Sequelize(been using it for a like year in prod) but I really miss this feature. Writing raw SQL is sometimes nasty with sequelize query interface.

PS: ORMs like TypeORM also provides a query builder.

This would be a great feature

ORM + QueryBuilder is a powerful duo

[bradisbell]

Is there any documentation on sequelize.queryInterface.QueryGenerator at the moment, as an interim? It seems to solve for some use cases.

[up9cloud]

@bradisbell
https://sequelize.org/master/manual/query-interface.html

And...

// v4
sequelize.queryInterface.QueryGenerator.selectQuery(tableName, conditions, options)

// v6, https://github.com/sequelize/sequelize/blob/main/lib/dialects/abstract/query-generator.js#L1125
sequelize.getQueryInterface().queryGenerator.selectQuery(tableName, conditions, options)

Enjoy 😊

[akudo7]

@bradisbell
https://sequelize.org/master/manual/query-interface.html

And...

// v4
sequelize.queryInterface.QueryGenerator.selectQuery(tableName, conditions, options)

// v6, https://github.com/sequelize/sequelize/blob/main/lib/dialects/abstract/query-generator.js#L1125
sequelize.getQueryInterface().queryGenerator.selectQuery(tableName, conditions, options)

Enjoy 😊

v6.6.4 not working

[HyopeR]

v6.6.4

v6.6.5 same issue.

[TomasKostadinov]

@HyopeR @akudo7 I just tested it with the following code, and it works:

    const queryInterface = await sequelize.getQueryInterface();
    // @see https://github.com/sequelize/sequelize/blob/main/lib/dialects/abstract/query-generator.js#L1125
    const queryGenerator: SequelizeQueryGenerator = queryInterface.queryGenerator;
	// typescipt
    const queryGenerator: SequelizeQueryGenerator = (queryInterface as any).queryGenerator as SequelizeQueryGenerator;


	const sql = queryGenerator.selectQuery("table_name", {attributes: ["title"]}, null) // should produce something like SELECT title from table_name

Typings

export type SortingDirection = 'ASC' | 'DESC';

export interface SequelizeQueryGenerator {
    selectQuery: (tableName: string, options: SequelizeQueryGeneratorOptions, model: any) => string
}

export interface SequelizeQueryGeneratorOptions {
    attributes?: string[]; // -> An array of attributes (e.g. ['name', 'birthday']). Default: *
    where?: string | number | { [key: string]: string }; // -> A hash with conditions (e.g. {name: 'foo'}) OR an ID as integer
    order?: Array<[string, SortingDirection]>;// -> e.g. 'id DESC'
    group?: string;//
    limit?: number;// -> The maximum count you want to get.
    offset?: number;//
}

With

sequelize 6.6.5
sequelize-typescript 2.1.0

[TomasKostadinov]

@bradisbell

Is there any documentation on sequelize.queryInterface.QueryGenerator at the moment, as an interim? It seems to solve for some use cases.

I could not find any documentation, but you can still just take a look at the implementation at https://github.com/sequelize/sequelize/blob/main/lib/dialects/abstract/query-generator.js

Unfortunately, sequelize's developer decided not to provide typings for the query-generator as they think it is not commonly used (see https://github.com/sequelize/sequelize/blob/main/types/lib/query-interface.d.ts#L289)

I found that most functions work just as expected, and the parameters are properly documented using jsodoc syntax. If I can help you get started let me know, or just use the code in my previous comment as a starting point.

[ephys]

I'd like to propose an alternative approach to what has been proposed so far for the Query Builder

Instead of designing it as something separate for raw queries, I've built it on top of Model.find{One,All}. The goals being:

• Have feature parity between the two systems.
• Improve support for writing sql literally:
  • Being able to bind parameters in literals.
  • Being able to use Attribute names (instead of column names) in .where(literal) (Maybe).
• Being able to use options from Model.find* in the Query Builder (eg. .where({ id: 5 }) being interchangeable with .where('id = 5')).

I've started a high level API draft here: https://gist.github.com/ephys/078ee6a2eb31dc209f0de6cea95316e7

Thoughts welcome.

[nicoabie]

We are cool kids too!

https://github.com/sdepold/sequelize/blob/master/lib/dialects/mysql/query-generator.js

Have a look at the selectQuery function ;-). You can access this magical SQL creator through Sequelize.getQueryInterface().QueryGenerator

That is true but it does not always work.
For example I have a use case were I need to check that all find queries contain certain where clause (multi tenant database)

For that I created a hook but I couldn't access the sequelize instance from there so I have to hack it like this

That's a POC, I will create the QueryGenerator somewhere else.

We cannot call the QueryGenerator a QueryBuilder bc it is not agnostic of sequelize (which I understand as it is part of the library)

So the hack is:

new QueryGenerator({
  sequelize: { options: {} },
  _dialect: { name: 'mysql' },
})

And luckily for whereItemsQuery method (the only one I need) it does not rely on the sequelize instance.

Maybe if QueryGenerator was agnostic of sequelize it could be really used as a QueryBuilder

[louislow]

@HyopeR @akudo7 I just tested it with the following code, and it works:

    const queryInterface = await sequelize.getQueryInterface();
    // @see https://github.com/sequelize/sequelize/blob/main/lib/dialects/abstract/query-generator.js#L1125
    const queryGenerator: SequelizeQueryGenerator = queryInterface.queryGenerator;
	// typescipt
    const queryGenerator: SequelizeQueryGenerator = (queryInterface as any).queryGenerator as SequelizeQueryGenerator;


	const sql = queryGenerator.selectQuery("table_name", {attributes: ["title"]}, null) // should produce something like SELECT title from table_name

Typings

export type SortingDirection = 'ASC' | 'DESC';

export interface SequelizeQueryGenerator {
    selectQuery: (tableName: string, options: SequelizeQueryGeneratorOptions, model: any) => string
}

export interface SequelizeQueryGeneratorOptions {
    attributes?: string[]; // -> An array of attributes (e.g. ['name', 'birthday']). Default: *
    where?: string | number | { [key: string]: string }; // -> A hash with conditions (e.g. {name: 'foo'}) OR an ID as integer
    order?: Array<[string, SortingDirection]>;// -> e.g. 'id DESC'
    group?: string;//
    limit?: number;// -> The maximum count you want to get.
    offset?: number;//
}

With

sequelize 6.6.5 sequelize-typescript 2.1.0

Hi @TomasKostadinov
Hi community,

Can this idea work for having alias in WHERE clause too?
const queryInterface = this.sequelize.getQueryInterface();

            const whereObject = [
                {
                    ['venueid']: {
                        [Op.lt]: 50
                    }
                },
                {
                    ['e.eventid']: {
                        [Op.eq]: 100
                    }
                }
            ];

I tried above whereObject Sequelize.WhereOptions but not able to have "e.eventid" instead i got "v"."e.eventid"

SELECT * FROM "venue" AS "v", "event" AS "e" WHERE ("v"."venueid" < 50 AND "v"."e.eventid" = 100);

[dberardo-com]

still no plan to include a "fluent interface" for sequelize, to concatenate statements and conditions without actually fire them ?

[henriquetroiano]

this is why Laravel still relevant in 2023..........................................................

[cfaherty]

Building on the above code using queryGenerator. I saw a post was having trouble with field aliases. Me too.

Here's a hack to swap aliases into the attributes, where, having of options.

/**
 * @param {*} model - Sequelize Model instance
 * @param {*} name - Raw field name
 * @returns {string|array} Either name or [alias, name] -- Sequelize will convert to `raw` AS `alias`
 */
const getAlias = (model, name) => {
  const rawAttributes = model.rawAttributes;
  const field = (rawAttributes[name] || {}).field;
  if (field) {
    // Allows queryGenerator to make ex: `_id` AS `id`
    return [field, name];
  }
  return name;
};

/**
 * Several issues:
 * 
 * 1. In SQL you cannot WHERE with columns which are subselects.
 *    You will need to use HAVING.
 * 2. Sequelize count (i.e. findAndCountAll) will not use attributes
 *    and thus will not have the subselect columns which are referenced
 *    in HAVING.
 * 
 * So we end up that findAndCountAll will not work when referencing virtual
 * attributes in the HAVING clause.
 * 
 * Furthermore, the nature of using HAVING means that we must actualize
 * the rows in order to evaluate the HAVING. Thus a normal count(*) will
 * not work.
 * 
 * We have to make SELECT with the rows, and then a SELECT of those results
 * with count(*).
 * 
 * A variation of what is mentioned here:
 * https://github.com/sequelize/sequelize/issues/9747
 */

/**
 * @param {*} db - Sequelize instance
 * @param {*} model - Model instance
 * @param {*} options - Query options (attributes, where, having etc)
 * @returns {int} count
 */
const havingCount = async (db, model, options) => {
  const { queryGenerator } = db.getQueryInterface();
  // Fixup attributes, where, having field names to use aliases if present
  const newAttributes = options.attributes.map(e => getAlias(model, e));
  const innerQuery = queryGenerator.selectQuery(
    model.name,
    { ...options, attributes: newAttributes },
    model
  );
  const [{ count }] = await db.query(
    `SELECT COUNT(*) AS count FROM (${innerQuery.replace(/;$/, '')}) AS outerQuery`,
    {
      type: Sequelize.QueryTypes.SELECT
    }
  );
  return count;
};

Edited because I realized that where and having need not be updated since updating the attributes with the [] alias format is all that is needed.

[klondikemarlen]

If you just need the ability to use a query builder and mix the code in with Sequelize code, I've been using

// knex-query-to-sequelize-raw.ts
import { Knex } from "knex"
import { QueryTypes, QueryOptionsWithType } from "@sequelize/core"

import db from "@/db/db-client"

type QueryOptions = Omit<QueryOptionsWithType<QueryTypes.SELECT>, "bind" | "type">

// TODO: fix types to show that it might return null
export async function knexQueryToSequelizeSelect<T extends object>(
  knexQuery: Knex.QueryBuilder,
  options: QueryOptions = {}
) {
  const { sql: knexSql, bindings } = knexQuery.toSQL().toNative()
  const { sql: sequelizeSql, bind } = knexSqlNativeToSequelizeQueryWithBind({
    sql: knexSql,
    bindings,
  })
  return db.query<T>(sequelizeSql, {
    ...options,
    bind,
    type: QueryTypes.SELECT,
  })
}

/**
 * Note siganture is chosen so you can pass knexQuery.toSQL().toNative() directly
 *
 * Currently only tested with MSSQL dialect
 *
 * @param sqlWithKnexBindings knexQuery.toSQL().toNative().sql
 * @param bindings knexQuery.toSQL().toNative().bindings
 * @returns { sql: string, bind: unknown[] } in Sequelize format
 */
export function knexSqlNativeToSequelizeQueryWithBind({
  sql: sqlWithKnexBindings,
  bindings,
}: {
  sql: string
  bindings: readonly unknown[]
}): { sql: string; bind: unknown[] } {
  let sqlWithSequelizeBindings = sqlWithKnexBindings
  // converts "@p0" to "$1", "@p1" to "$2", etc.
  bindings.forEach((_, i) => {
    const pattern = new RegExp(`@p${i}\\b`, "g")
    sqlWithSequelizeBindings = sqlWithSequelizeBindings.replace(pattern, `$${i + 1}`)
  })

  const mutableBindings = [...bindings]

  return {
    sql: sqlWithSequelizeBindings,
    bind: mutableBindings,
  }
}

Usage is

const someResult = await knexQueryToSequelizeSelect<SomeReturnType>(knex().some().chainable().query().code())

See https://knexjs.org/guide/query-builder.html. It lets you mix Knex code into Sequelize transaction blocks.
Not that I'm advocating for Knex, I'm actually in the process of re-writing a bunch of codebases to use Sequelize instead of Knex; but there are things that Knex does things well, so I'm migrating incrementally.