sql-strings

Write SQL-injection protected SQL statements using template strings. Useful for longer queries and dynamically created queries where keeping the SQL and bind parameters separate becomes disorienting.

import { SQL } from 'sql-strings';

const username = 'bob'; // potentially unsafe input

// postgres:
await client.query(SQL`SELECT * FROM users WHERE username = ${username}`);
// is equivalent to:
await client.query('SELECT * FROM users WHERE username = ?', [username]);

// mysql:
connection.query(SQL`SELECT * FROM users WHERE username = ${username}`());
// is equivalent to:
connection.query('SELECT * FROM users WHERE username = ?', [username]);

// sqlite3:
db.all(...SQL`SELECT * FROM users WHERE username = ${username}`);
// is equivalent to:
db.all('SELECT * FROM users WHERE username = ?', [username]);

// sequelize:
sequelize.query(SQL`SELECT * FROM users WHERE username = ${username}`());
// is equivalent to:
sequelize.query({ query: 'SELECT * FROM users WHERE username = ?', values: [username] });

Compatible with node-sqlite3, Sequelize, mysql, postgres, and more!

Note: This is my first npm package and made for learning purposes. Feedback is welcome! I'll keep it updated with bug/security fixes but will not be adding new features. Consider an alternative for more features.

Installation

This is a Node.js module available through the npm registry. Node.js v18.17.0 or higher is recommended.

Installation is done using the npm install command:

$ npm install sql-strings

Recommended Extensions for Syntax Highlighting

These editor extensions will syntax highlight the SQL template strings for better readability:

• VS Code: ES6 String HTML.
• Sublime Text: javascript-sql-sublime-syntax.
• Vim: vim-javascript-sql.

Usage

Prefix your template strings with SQL and use ${} for bind parameters.

const username = 'bob';
const sql = SQL`SELECT * FROM users WHERE username = ${username}`;

This SQLString object can be called as a function using () to get an object compatible with most database drivers.

connection.query(sql());

To insert raw values into the SQL string without escaping them as bind parameters, pass them to the SQLString using parentheses:

const tablename = 'users';
connection.query(SQL`SELECT * FROM "`(tablename)`" WHERE username = ${username};`());

To append to an existing SQLString object, use the append method:

const sql = SQL`SELECT * FROM "`;
sql.append(tablename);
sql.append`" WHERE username = ${username}`;
sql.append` ID in (`;
for (const id of [1, 2, 3]) {
    sql.append`${id}, `;
}
sql.append`4)`;
connection.query(sql());

You can optionally leave out the .append:

const sql = SQL`SELECT * FROM "`;
sql(tablename);
sql`" WHERE username = ${username}`;
sql` ID in (`;
for (const id of [1, 2, 3]) {
    sql`${id}, `;
}
sql`4)`;
connection.query(sql());

SQL Driver Specific Syntax

• node-sqlite3 like APIs use the spread operator ...SQL`query`​ instead of the final parenthesis SQL`query`() syntax.

import sqlite3 from 'sqlite3';
import { SQL } from 'sql-strings';

const db = new sqlite3.Database(':memory:');
const username = 'bob';
const tablename = '"users"';

const sql = SQL`SELECT * FROM `;
sql.append(tablename);
sql.append` WHERE username = ${tablename}`;

db.all(...sql);
// is equivalent to:
db.all('SELECT * FROM "users" WHERE username = ?', [username]);

• node-postgres can optionally omit the final parenthesis and use SQL`query`​ syntax.

• sequelize by default replaces the parameters on the client. To use bind parameters on the database side, pass SQL.SEQUELIZE_USE_BIND to the final parenthesis with SQL`query`(SQL.SEQUELIZE_USE_BIND) syntax.

import { SQL } from 'sql-strings';
import { Sequelize } from 'sequelize';

const sequelize = new Sequelize('sqlite::memory:');
const username = 'bob';
const tablename = '"users"';

const sql = SQL`SELECT * FROM "`(tablename)`" WHERE username = ${username}`;
sequelize.query(sql(SQL.SEQUELIZE_USE_BIND));
// is equivalent to:
sequelize.query({ query: 'SELECT * FROM "users" WHERE username = $1', bind: [username] });

Examples

The following application uses the sql-strings package: Attendance Scanner. You can also take a look at the test suite for more examples.

Alternatives

• sql-template-strings does the same thing but doesn't support node-sqlite3 and has a different syntax.

Contributing

All constructive contributions are welcome including anything from bug fixes and new features to improved documentation, tests and more! Feel free to open an issue to discuss the proposed change and then submit a pull request :)

Security Issues

If you discover a security vulnerability in sql-strings, please contact the current main maintainer.

Running Tests

Tests run automatically pre-commit using Husky. To run the test suite manually, first install the dependencies, then run npm test:

$ npm install
$ npm test

You will need to set up a mysql and postgres database on localhost with username test, password test, and database test to run their respective tests.

Linting and Formatting

Eslint is used for static analysis, fixpack is used to standardize package.json and Prettier is used for automatic formatting. Linting will automatically run pre-commit using Husky and Lint-Staged. Formatting can be set up to happen automatically in your editor (e.g. on save). Formatting and linting can also be run manually:

$ npm install
$ npm run format
$ npm run lint

Generating TypeScript Types

Typescript types are automatically generated from the JSDoc in the /types folder when the npm package is packaged/published. To update the TypeScript types manually, run the following command:

$ npm run types

This will allow TypeScript users to benefit from the type information provided in the JSDoc.

If you also want to generate the readme badges, run the following command:

$ npm run build

Contributors

The author of sql-strings is Alexander Metzger.

Functionality is inspired by sql-template-strings.

All contributors will be listed here.

License

MIT