Enable named placeholders in node-mysql

mysql module uses ? characters as placeholders for values, e.g.

connection
 .query('SELECT ?, ?', [
 'foo',
 'bar'
 ]);

This example is equivalent to:

connection.query('SELECT ' + connection.escape('foo') + ', ' + connection.escape('bar'));

However, this approach becomes hard to read as query becomes large and the list of values long.

There is an alternative: named placeholders.

connection
 .query('SELECT :foo, :bar', {
 foo: 'FOO',
 bar: 'BAR'
 });

The latter is equivalent to:

connection.query('SELECT ' + connection.escape('FOO') + ', ' + connection.escape('BAR'));

Placeholder names can appear multiple times, e.g.

connection
 .query('SELECT :foo, :foo', {
 foo: 'FOO'
 });

The latter is equivalent to:

connection.query('SELECT ' + connection.escape('FOO') + ', ' + connection.escape('FOO'));

As of this writing, mysql does not support named parameters.

However, it is easy to patch Connection.prototype.query prototype to add the support:

First, you need to install named-placeholders package.

Then, patch the Connection.prototype.query:

const toUnnamed = require('named-placeholders')();
const originalQuery = require('mysql/lib/Connection').prototype.query;

require('mysql/lib/Connection').prototype.query = function (...args) {
 if (Array.isArray(args[0]) || !args[1]) {
 return originalQuery.apply(this, args);
 }

 ([
 args[0],
 args[1]
 ] = toUnnamed(args[0], args[1]));

 return originalQuery.apply(this, args);
};

That's it. You can now use named placeholders.

See a complete article about using MySQL in Node.js http://gajus.com/blog/8/using-mysql-in-node-js.

#node

#mysql