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.