sql-execute-tag

A template tag function for building and immediately executing SQL statements. Works with any database library - you provide your own execution function. Uses sql-template-tag under the hood.

import sqlTag from 'sql-execute-tag';

/**
 * You need to provide your own executor function with the following signature:
 * @param literals Array of the literal parts of the string, with a number of extra properties
 * @param literals.sql The array joined with the `?` character, for use with databases like mysql and sqlite
 * @param literals.text The array joined with `$n`, where n is the numbered position, for use with databases like postgres
 * @param literals.values An array of the values to pass as parameters to parameterised queries
 * @param parameters The same as `literals.values`
 */
function execute(literals, parameters) {
	return db.fetchAll(literals.text, parameters)
}

// Create the tag
const sql = sqlTag(execute);

// Make basic parameterised queries
const users = await sql`SELECT * FROM users`;
const [user] = await sql`SELECT * FROM users WHERE name=${'Paul'} LIMIT 1`;

// Arrays will be joined with a comma, with nested arrays enclosed in braces
await sql`INSERT INTO users (id, name) VALUES (${[1, 'Laura']})`;
await sql`INSERT INTO users (id, name) VALUES ${[[2, 'Shelley'], [3, 'Lee'], [4, 'Adam']]}`;

// Pass in raw values by using `sql` as a function
const rows = await sql`SELECT * FROM ${sql(tableName)}`;
await sql`${sql(fs.readFileSync('migrations.sql'))}`;

// Use functions for subqueries
const getAuthorIds = sql => sql`SELECT id FROM authors WHERE name = ${"Blake"}`;
const books = await sql`SELECT * FROM books WHERE author_id IN (${getAuthorIds})`;
// A nice side-effect of this is that subqueries are usable as regular queries
const authorIds = await getAuthorIds(sql);