adrianblynch/sequels

:star2: Various styles of writing SQL strings in Node (and possibly other languages)

Sequels

An SQL string can be built in many ways in JavaScript and other languages. Below are the variations I've seen and have used.

My personal favourite at the moment is the Concatenated array items.

Have your own way? Add it below.

Single line

var sql = 'SELECT * FROM table WHERE column = value';

Pros

• Simple
• Minimal typing

Cons

• Can't grow without becoming unreadable

Multi line concatenated statements

var sql = 'SELECT * ';
sql += 'FROM table ';
sql += 'WHERE column = value ';

Or if you'd like things to line up:

var sql = '' ;
sql  = 'SELECT *' ;
sql += 'FROM table ';
sql += 'WHERE column = value ';

Pros

• Simple(ish)
• Can grow as needed
• Each line is consistent

Cons

• A space is needed on each line (beginning or end)
• A bunch more typing for each line

Concatenated array items

var sql = [
  'SELECT *',
  'FROM table',
  'WHERE column = value'
];

Pros

• Nice to read
• Will grow
• No leading/trailing space needed

Cons

• sql.join(' ') is needed when used

Escape newline

var sql = "SELECT * \
FROM table \
WHERE column = value";

Pros

• No extra whitespace required
• No need for closing/opening quotes repeatedly
• Will grow

Cons

• Having to escape newline looks ugly
• Whitespace after ecaped newline causes a vague error. The trailing space causes the issue FROM•table•\•

Multiline npm module

var sql = multiline(function(){/*
SELECT *
FROM table
WHERE column = value
*/});

Pros

• Write everything as is
• No newline escape
• Will grow
• No extra whitespace

Cons

• Extra package dependency
• Micro performance hit
• JavaScript only (AFAIK)

Template strings

ES6 natively supports multiline strings.

var sql = `
  SELECT *
  FROM table
  WHERE column = value`;

Pros

• Write everything as is
• No newline escape
• Will grow

Cons

• Requires using es6-templates to compile JavaScript written using template strings to use ES5-compatible syntax OR you can use io.js instead of NodeJS as it ships with template string support.

Knex

knex('table').where({column: value})

Pros

• Easy to multiline
• Easy to compose
• Limits SQL injection

Cons

• Extra package dependency
• New syntax to learn

querybox

https://www.npmjs.org/package/querybox

box.run('query-table', [rowId], function(err, rows) {
  console.log(rows[0].name)
})

Pros

• SQL in completely separate files
• Easy to multiline
• Easy to compose
• Limits SQL injection
• Easy copy and paste from SQL IDE

Cons

• Extra package dependency