sqlt

Simple SQL Templating helper inspired by Yesql

Give this library the location of a SQL template file, and it will return a function that you can call with a mysql or pg connection, an optional array of parameters, and a callback.

Installation

This module is installed via npm:

$ npm install sqlt

Example Usage

Simple SQL query with no params

Given a SQL template file located in /path/to/queries/getUsers.sql

SELECT
  *
FROM
  users;

You can get a function that is easily callable with a database connection handle, and get a callback:

var sqlt = require('sqlt'),
    mysql = require('mysql');
 
var conn = mysql.createConnection({
  host: 'yourdatabase.com',
  database: 'yourdbname',
  user: 'yourdbusername',
  password: 'yourpassword'
});
 
var getUsers = sqlt('/path/to/queries/getUsers.sql');
getUsers(conn, function (err, results) {
  if (err) throw err;
  console.log(results);
});

SQL query with params

Given a SQL template file located in /path/to/queries/getUsersByIdOrEmail.sql

SELECT
  *
FROM
  users
WHERE
  id = ? OR email = ?;

You can get a function that is easily callable with a database connection handle, an array of parameters, and get a callback:

var sqlt = require('sqlt'),
    mysql = require('mysql');
 
var conn = mysql.createConnection({
  host: 'yourdatabase.com',
  database: 'yourdbname',
  user: 'yourdbusername',
  password: 'yourpassword'
});
 
var getUsersByIdOrEmail = sqlt('/path/to/queries/getUsersByIdOrEmail.sql');
getUsersByIdOrEmail(conn, [1234, '[e-mail]'], function (err, results) {
  if (err) throw err;
  console.log(results);
});

Load a directory full of queries

Given a folder that contains a list of .sql files (say the getUsers.sql and getUsersByIdOrEmail.sql file above:

You can get a single object where each .sql file is turned into a query helping function:

var sqlt = require('sqlt'),
    mysql = require('mysql');
 
var conn = mysql.createConnection({
  host: 'yourdatabase.com',
  database: 'yourdbname',
  user: 'yourdbusername',
  password: 'yourpassword'
});
 
var queries = sqlt.dir('/path/to/queries');
queries.getUsers(conn, function (err, results) {
  if (err) throw err;
  console.log(results);
});
queries.getUsersByIdOrEmail(conn, [1234, '[e-mail]'], function (err, results) {
  if (err) throw err;
  console.log(results);
});

Streaming support

By not passing through a callback the query functions will return streams (depends on the underlying database library - eg below is for mysql).

Given a SQL template file located in /path/to/queries/getUsersByIdOrEmail.sql

SELECT
  *
FROM
  users
WHERE
  id = ? OR email = ?;

You can get the stream by not passing through a callback:

var sqlt = require('sqlt'),
    mysql = require('mysql');
 
var conn = mysql.createConnection({
  host: 'yourdatabase.com',
  database: 'yourdbname',
  user: 'yourdbusername',
  password: 'yourpassword'
});
 
var getUsersByIdOrEmail = sqlt('/path/to/queries/getUsersByIdOrEmail.sql');
var stream = getUsersByIdOrEmail(conn, [1234, '[e-mail]']).stream();
stream.on('data', console.log);
// stream.pipe()

Pull Requests Welcome!