Node-based SQL builder with Common Table Expression (WITH clause) support

Question

I'm building a Node.js app that needs to query a Redshift database (based on postgres 8.0.2) using CTEs. Unfortunately, the SQL query builders I've looked at thus far (node-sql, knex.js and sequelize) don't seem to support common table expressions (CTEs).

I had great success forming common table expressions in Ruby using Jeremy Evans' Sequel gem, which has a with method that takes two arguments to define the tables' aliased name and the dataset reference. I'd like something similar in Node.

Have I missed any obvious contenders for Node.js SQL query builders? From what I can tell these are the four most obvious:

• node-sql
• nodesql (no postgres support?)
• knex.js
• sequelize

Answer 1

knex.js now supports WITH clauses:

knex.with('with_alias', (qb) => {
  qb.select('*').from('books').where('author', 'Test')
}).select('*').from('with_alias')

Outputs:

with "with_alias" as (select * from "books" where "author" = 'Test') select * from "with_alias"

Answer 2

I was able to use common table expressions (CTEs) with knex.js and it was pretty easy.

Assuming you're using socket.io along with knex.js,

knex-example.js:

function knexExample (io, knex) {
  io.on('connection', function (socket) {
    var this_cte = knex('this_table').select('this_column');

    var that_cte = knex('that_table').select('that_column');

    knex.raw('with t1 as (' + this_cte + 
    '), t2 as (' + that_cte + ')' + 
    knex.select(['this', 'that'])
      .from(['t1', 't2'])
    )
    .then(function (rows) {
      socket.emit('this_that:update', rows);
    });
  })
}

module.exports = knexExample;