node-jsql

jsql

• Turn SQL queries into javascript functions
• Allow named parameters
• Compatible with mysql, postgres & sqlite any-db adapters

Usage

• jsql.loadSync(str[, opts]): load a query or queryfile synchronously
• jsql.load(str[, opts], callback): load a query or queryfile asynchronously

parameters

• str: a single SQL query, or a path to a query file
• opts:
  • db: an any-db adapter
  • promisify: a promisifier
  • postgres: set to true to enable postgres style parameters

Examples

Simple (no parameters, no promise or event)

var db = require('any-db').createConnection(/* DSN */);
var opts = {db: db};
var query = jsql.loadSync('SELECT * FROM foobar', opts);
query(function(err, result) { /* ... */ });

With named parameters

var db = require('any-db').createConnection(/* DSN */);
var opts = {db: db};
var query = jsql.loadSync('SELECT * FROM foobar WHERE id = :foo', opts);
query({foo: 123}, function(err, result) { /* ... */ });

With promises

var db = require('any-db').createConnection(/* DSN */);
var opts = {db: db, promisify: Bluebird.promisify};
var query = jsql.loadSync('SELECT * FROM foobar', opts);
query().then(function(result) { /* ... */ });

With events

var db = require('any-db').createConnection(/* DSN */);
var opts = {db: db};
var query = jsql.loadSync('SELECT * FROM foobar', opts);
query().on('data', function() { /* ... */ })

(events are defined by the underlying any-db adapter)

With several db adapters

var dbOne = require('any-db').createConnection(/* DSN1 */);
var dbTwo = require('any-db').createConnection(/* DSN2 */);
var query = jsql.loadSync('SELECT * FROM foobar', {db: dbOne});
query(function(err, result) { /*   */ }); // uses default db, dbOne
query(dbTwo, function(err, result) { /* ... */ }); // uses dbTwo

Named parameters

Named parameters (such as :foobar) are turned into positional parameters (?) or numbered parameters ($1,$2,...) depending on db type. Parameters can they be passed as a javascript object at query time. Since named parameters are merely positional/numbered parameters they cannot be used on column or table names.

Query files

Queries can also be loaded from a file.

Single query

var db = require('any-db').createConnection(/* DSN */);
var opts = {db: db};
var query = jsql.loadSync('file://my-query.sql', opts);
query(opts)

Multiple queries

Multiple queries can be added to the same query file. Queries must be named using valid javascript names ([a-z_$][a-z0-9_$]*) and delimited using a special delimiter: /*: valid_javascript_name */.

/*: my_first_query */
SELECT * FROM foo WHERE id = :id
/*: my_second_query */
SELECT * FROM bar WHERE id = :id

var db = require('any-db').createConnection(/* DSN */);
var opts = {db: db, promisify: Bluebird.promisify};
var queries = jsql.loadSync('file://my-queries.sql', opts);
queries.my_first_query({id: 1234}).then(/* ... */);
queries.my_second_query({id: 1234}).then(/* ... */);

Nested query names

If the query name contains a dot, it will be nested in the resulting object

/*: foo.one */
SELECT * FROM foo
/*: foo.two */
SELECT * FROM bar

var db = require('any-db').createConnection(/* DSN */);
var opts = {db: db, promisify: Bluebird.promisify};
var queries = jsql.loadSync('file://my-queries.sql', opts);
queries.foo.one().then(/* ... */);
queries.foo.two().then(/* ... */);

Load a query file asynchronously

var db = require('any-db').createConnection(/* DSN */);
var opts = {db: db};
jsql.load('file://my-queries.sql', function(err, queries) {
  /* ... */
});

postgres support

var db = require('any-db').createConnection(/* DSN */);
jsql.loadSync('file://my-queries', {postgres: true});
/* ... */

postgres & non-postgres queries can be contained in the same query file, but adapters should either be omitted at load time or overloaded at call time.