Node based SQL constructor with support for Common Table Expression (WITH clause)

Question

I am creating a Node.js application that needs to query a Redshift database (based on postgres 8.0.2) using CTE. Unfortunately, the SQL query creators that I have looked at so far ( node-sql , knex.js, and sequelize) do not seem to support common table expressions (CTEs).

I have had great success creating common table expressions in Ruby using Gemememememememememememememem.ru Jeremy Evans, who has a with method that takes two arguments to define table name aliases and a dataset reference. I need something like this in Node.

Am I missing any obvious applicants for Node.js SQL query collectors? From what I can say, these are the four most obvious:

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

Answer 1

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

Assuming you are using socket.io 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; 

Answer 2

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 3

From this problem and this problem I understand that you can use CTE with Sequelize.

You need to use raw queries and possibly refine the type to make sure that Sequelize understands it as a Select query. See the first link.

Code example:

 sequelize.query( query, //raw SQL tableName, {raw: true, type: Sequelize.QueryTypes.SELECT} ).success(function (rows) { // ... }) 

See here for more details.