The last time Hackerfall tried to access this page, it returned a not found error. A cached version of the page is below, or click here to continue anyway

Better SQL strings in io.js (NodeJS)

In Node.js, if you're writing SQL by hand (i.e. without an ORM, mongoDB or jQuery-styled query builder), then multiline strings and callbacks can be difficult to build and maintain.

Lets take a common SQL query using the excellent node-postgres package:

"insert into sample_table "+
"    (obj_name, obj_description, creator_user_id, some_number, json_column) "+
"  values "+
" ($1, $2, $3, $4, $5)",
[obj_name, obj_desc, req.session.user_id, num_array[0]],
function(err, results) {
  //do something with results

This contrasts with the new SQL statement syntax we've implemented:

var insertResult = yield pgClient.queryTmpl(sql`
  insert into sample_table
    (obj_name, obj_description, creator_user_id, some_number, json_column)
    (${obj_name},  ${obj_desc}, ${req.session.user_id}, ${num_array[0]}

Further inspection of the first query:

The first annoyance and notable contrast between the two is the strinct concatenation of the former. It makes it hard to take a plain .sql query and drop it into node.js, or debug in psql. There are other style syntaxes possible, such as :

insert into sample_table \

but it still is fragile. An extra space after newline escape breaks the whole query and is invisible to see.

Also, existing asyncronous methods such as callbacks (as seen above), the async package, or Promises are harder to deal with for a complex series of sql statements. They are not necessarily unweidly, but it certainly takes extra control structure than syncronous code.

Another issue is $1, $2, ..., $N can be hard to maintain in a larger query. What if you want a new parameter near the beginning (for logic readability)?

They could be renamed $2 to $3, $3 to $4, etc. Or of course have them out of order. Another option is to have a counter and litter the query building code ahead of time:


New Javascript features allow for a better design:

To solve both of these issues, we need to leverage ES6 features that have been enabled in io.js by utilizing a newer version of the V8 engine (currently

In Part 2, I'll explain how this works!

Continue reading on