ES6 tag functions for SQL template strings.
WARNING: This project is still under development and the API is subject to change.
sql-tags uses ES6 template strings to generate a function that can then be used to create a query string based on rules, fragments, specified in the template string. The primary objective sql-tags is to create a lightweight API to make it easy to work with SQL queries and JavaScript objects.
The generated function's arguments are used by the fragments to generate a query.
const sql = require('sql-tags');
const usersTable = 'users';
const usersTableColumns = ['id', 'email', 'password', 'active', 'created', 'modified'];
const findUsersQuery = sql`
SELECT ${usersTableColumns}
FROM ${usersTable}
WHERE ${{ equals: { join: 'AND', whenEmpty: 'TRUE' } }}
`;
userSelectQuery({ email: '[email protected]', active: true });
// => {
// => text: 'SELECT "id", "email", "password", "active", "created", "modified" FROM users WHERE "email" = $1 AND "active" = $2',
// => values: ['[email protected]', true]
// => }
userSelectQuery({});
// => {
// => text: 'SELECT "id", "email", "password", "active", "created", "modified" FROM users WHERE TRUE',
// => values: []
// => }
userSelectQuery();
// => {
// => text: 'SELECT "id", "email", "password", "active", "created", "modified" FROM users WHERE TRUE',
// => values: []
// => }
const updateUsersQuery = sql`
UPDATE ${usersTable}
SET ${{ equals: { join: ',', skip: ['id'] } }}
WHERE "id" = ${{ value: 'id' }}
`;
updateUsersQuery({ id: 4, email: '[email protected]', modified: new Date() });
// => {
// => text: 'UPDATE users SET "email" = $1 , "modified" = $2 WHERE "id" = $3',
// => values: ['[email protected]', "Mon Dec 07 2015 09:36:00 GMT-0500 (EST)", 4]
// => }The Value Fragment generates a parameterized query string in the form of value.
{
value: <value key>
}The Equals Fragment generates a parameterized query string in the form of "key" = value and joins each string with the join param. It also has options available to skip keys and for empty args.
{
equals: {
join: <join string>,
skip: [<skip args>],
whenEmpty: <empty string>
}
}Custom Fragments are just a query with the signature of:
function (args, paramIndexStart) {
return {
text: <query fragment>,
values: [<query params>]
};
}Custom Fragments must return an object that contains the query fragment text and values. Values must be in parameterized query form of $<num> start with the paramIndexStart.
function simpleTrueFragment(args, paramIndexStart) {
return {
text: '$' + paramIndexStart,
values: [true]
};
}
const query = sql`
SELECT ${simpleTrueFragment}
`;
query();
// => {
// => text: 'SELECT $1',
// => values: [true]
// => }MIT