What is named-placeholders?
The named-placeholders npm package is used to convert SQL query strings with named placeholders into a format that can be used with parameterized queries. This is particularly useful for preventing SQL injection attacks and making SQL queries more readable and maintainable.
What are named-placeholders's main functionalities?
Convert named placeholders to positional placeholders
This feature allows you to convert a SQL query with named placeholders into a query with positional placeholders, which can then be executed using a database client that supports parameterized queries.
const named = require('named-placeholders')();
const query = 'SELECT * FROM users WHERE name = :name AND age = :age';
const params = { name: 'John', age: 30 };
const [sql, values] = named(query, params);
console.log(sql); // 'SELECT * FROM users WHERE name = ? AND age = ?'
console.log(values); // ['John', 30]
Support for repeated placeholders
This feature allows you to use the same named placeholder multiple times in a query. The named-placeholders package will correctly replace all instances with the appropriate positional placeholders.
const named = require('named-placeholders')();
const query = 'SELECT * FROM users WHERE name = :name OR nickname = :name';
const params = { name: 'John' };
const [sql, values] = named(query, params);
console.log(sql); // 'SELECT * FROM users WHERE name = ? OR nickname = ?'
console.log(values); // ['John', 'John']
Support for array parameters
This feature allows you to use arrays as parameters in your SQL queries. The named-placeholders package will expand the array into the appropriate number of positional placeholders.
const named = require('named-placeholders')();
const query = 'SELECT * FROM users WHERE id IN (:ids)';
const params = { ids: [1, 2, 3] };
const [sql, values] = named(query, params);
console.log(sql); // 'SELECT * FROM users WHERE id IN (?, ?, ?)'
console.log(values); // [1, 2, 3]
Other packages similar to named-placeholders
mysql2
The mysql2 package is a MySQL client for Node.js that supports named placeholders directly in its query method. It provides similar functionality to named-placeholders but is specific to MySQL databases.
pg-promise
The pg-promise package is a PostgreSQL client for Node.js that supports named parameters in queries. It offers similar functionality to named-placeholders but is tailored for PostgreSQL databases.
sequelize
Sequelize is an ORM for Node.js that supports named replacements in raw SQL queries. It provides a higher-level abstraction for database interactions, including support for named placeholders.
named-placeholders
compiles "select foo where foo.id = :bar and foo.baz < :baz" into "select foo where foo.id = ? and foo.baz < ?" + ["bar", "baz"]
usage
npm install named-placeholders
see this mysql2 discussion
var mysql = require('mysql');
var toUnnamed = require('named-placeholders')();
var q = toUnnamed('select 1+:test', { test: 123});
mysql.createConnection().query(q[0], q[1]);
credits
parser is based on @mscdex code of his excellent node-mariasql library