Friendly-SQL

Write SQL conveniently in node.js projects.

This lightweight library provides a way to separate SQL statements from the JavaScript file. Thus, writing and maintaining the SQL can become easier without a lot of string concatenation or joining with array of strings.

Install

npm install friendly-sql

Usage

Provided a SQL file family.sql with sqlite statements (pay attention to the id in the comment)

-- id: Create person 
CREATE person (
  person_id   INTEGER PRIMARY KEY,
  first_name  TEXT,
  last_name   TEXT,
  age         INTEGER
);
 
-- id: Read person 
-- inside the braces are the variables to be replaced. 
SELECT {field}
FROM person
WHERE first_name = $firstName
ORDER BY {field} {order};

We can use the definitions above in the JavaScript file as follows

var sql = require('friendly-sql').parse('family.sql');
 
// Get the SQL by id
console.log(sql('Create person'));
/*
CREATE person (
  person_id   INTEGER PRIMARY KEY,
  first_name  TEXT,
  last_name   TEXT,
  age         INTEGER
);
*/
 
// Get the SQL by id, and provide the values for the variables defined in the sql file.
console.log(sql('Read person', { field: 'first_name', order: 'DESC'}));
/*
SELECT first_name
FROM person
WHERE first_name = $firstName
ORDER BY first_name DESC;
*/

Options

For debugging, it can be useful to use the logger.

// Turn on the logger.
sql.log = true;
// It will log the details to the console.
sql('Read person', { field: 'first_name', order: 'DESC'}));

Examples

The above examples can be found in the /examples directory. To try it out, clone this repository and run

node examples/family