SQL Compose

A tool for safely building SQL queries using Javascript string interpolation.

Purpose of this library

When interfacing with the database, there are two main approaches one can use:

1. Building queries by writing explicitly parameterized SQL templates: This allows for full control over the SQL but is verbose and hard to maintain - especially as the number of parameters grows.
2. Building queries using an ORM: This results in succinct and easy to maintain code but at the cost of losing control of the SQL that runs - sometimes with significant performance implications.

The purpose of this library is therefore to provide a solution that results in terse code whilst also affording the user full control of the SQL that runs.

How does it work

The library exposes a single function that constructs a dynamic parameterized SQL query, along with its parameters: build :: Thunk => ( SQL, Params ).

A Thunk is an alias for a function of type: Wrapper => String, where Wrapper is a function provided by the library that can wrap values that are to be interpolated in the following ways:

• If the value is itself a thunk, the Wrapper evaluates the thunk and returns the evaluated String. This allows you to compose SQL queries in a modular fashion.
• Else, the value is assigned to a unique parameter and the Wrapper returns the parameter template string.

The auto utility

The library also exports a function: auto :: Wrapper => AutoWrapper. which takes a standard wrapper, and returns one that instead uses tagged template literals to automatically wrap interpolated values for us. Check the code snippet below for details on usage.

Parameter formatting

This library allows you to override how parameters are formatted. See the example below for details!

A quick example

const { build, config, auto } = require( "sql-compose" );

// Optionally override the default parameter formatting style of "$[param]".
config.formatFn = x => `%(${x}]s`;

const autoQuery = (opts) => (w) => auto(w) ` 
    val > ${ opts.min_val }
`;

const query = (opts) => (w) => `
    SELECT * FROM table
    WHERE id IN (${ ",".join( opts.ids.map( w ) ) })
    AND val < ${ w( opts.max_val ) }
    AND ${ w( autoQuery( opts ) ) }
`;

var opts = { ids : [ 1, 2, 3 ], max_val : 8, min_val : 5 };
var [ sql, params ] = build( query( opts ) );
console.log( sql, params );

Prints the following:

"SELECT * FROM table
WHERE id IN ( $[ param_0 ], $[ param_1 ], $[ param_2 ] )
AND val < $[ param_3 ]
AND val > $[ param_4 ]"

{ param_0 : 1, param_1 : 2, param_2 : 3, param_3 : 8, param_4 : 5 }