How to use named parameters in node-mysql2 prepared statements?

Question

The prepared statements document for mysql2 shows positional parameters. While there is some discussion about named parameters, as far as I can tell, they're not documented. If they're supported, how can named parameters be used?

For example, how would the following be converted to using named parameters?

const config = require('config'); // for DB connection info
const mysql = require('mysql2/promise');
const connection = await mysql.createConnection({
    ...config.get('db.conn'),
    // (or however connection information is retrieved locally)
})

let sql = "SELECT ? AS a, ? AS b, ? + ? AS ab",
    values = [2, 3, 2, 3];
// note values[2:3] must equal values[0:1], otherwise results will be incorrect

let [result, columns] = await connection.execute(sql, values);

(Note that DB connection information is left out of the sample and is represented by the use of the config package. Before being run locally, the sample must be completed by providing connection information in whatever manner is preferred.)

Answer 1

mysql2 has some support for named parameters (sometimes called "named placeholders" in mysql2) via the named-placeholders package. The feature is disabled by default, and so must be enabled with the namedPlaceholders option when the connection or pool is created, or when the query is executed.

Named placeholders use a similar syntax to that in PHP: a colon followed by the name. Unlike PHP, the name is allowed to be a number as well as an identifier. Also unlike PHP, the name used in the values object must not be preceded by a colon, else the value won't be found.

Named placeholders are supported in the query and execute methods, and Connection.format if namedPlaceholders is true for the connection. They aren't supported by prepare, mysql.format or Pool.format.

Connection.format also supports named placeholders for identifiers; the name must be preceded by two colons, rather than one. Unlike with positional parameters, all parameters must be passed to Connection.format if positional parameters are being processed; any unspecified parameters, whether named or positional, become NULL (for values) or `undefined` (for identifiers).

const config = require('config'); // for DB connection info
const mysql = require('mysql2/promise');

let sql = "SELECT :one AS a, :2 AS b, :one + :2 AS ab",
    values = {one: 2, 2: 3};
/* following won't work; colon prefix prevents values from being determined: */
//values = {":one": 2, ":2": 3};

/*****
 * Enable named placeholders for all queries through `pool`. 
 */
const pool = await mysql.createPool({
    namedPlaceholders: true,
    ...config.get('db.conn')
});
let [result, columns] = await pool.execute(sql, values);

/* Not supported; named placeholders remain unchanged */
pool.format(sql, values);
// result: "SELECT :one AS a, :2 AS b, :one + :2 AS ab"

/*****
 * Enable named placeholders only for some queries through `connection`.
 * Note also lack of `await` to continue before connection is completed.
 */
const connection = mysql.createConnection({
    //namedPlaceholders: false,
    ...config.get('db.conn')
});

/* also demonstrates lazy connection */ 
[result, columns] = await (await connection).execute({
    sql, values,
    namedPlaceholders: true,
});

/* Won't work because connection.config.namedPlaceholders is false */
(await connection).format(sql, values);
// result: SELECT :one AS a, :2 AS b, :one + :2 AS ab

/*****
 * Enable placeholders for all queries, which also enables them for 
 * `Connection.format` 
 */
const connection = await mysql.createConnection({
    namedPlaceholders: true,
    ...config.get('db.conn')
});

[result, columns] = await connection.execute(sql, values);

connection.format(sql, values);
// result: 'SELECT 2 AS a, 3 AS b, 2 + 3 AS ab'

/*****
 * Named and positional placeholders can be mixed, including "??" for identifiers. 
 * However, note how positional parameters are resolved.
 */
conn.format(
    "SELECT :one, :2, ? AS ::third FROM ??", 
    {one: 'a', 2: 'b', third:'3rd', 3:'c', 0:'zed', 1:'i'}
);
// result: "SELECT 'a', 'b', 'zed' AS `3rd` FROM `i`"

/* Missing parameters become NULL or `undefined` */
conn.format(
    "SELECT :one, :2 AS ::second, ? AS ::third FROM ??", 
    {one: 'a', 2: 'b', 3:'c', third: '3rd'}
);
// result: "SELECT 'a', 'b' AS `undefined`, NULL AS `3rd` FROM `undefined`"

/* If only positional parameters are passed, then not all values need to be given. 
 * Named placeholders and positional placeholders past the end of the array remain 
 * as-is in the statement.
 */
conn.format(
    "SELECT :one, :2, ? AS ::third FROM ??", 
    ['zed']
);
// result: "SELECT :one, :2, 'zed' AS ::third FROM ??"

/* The values for named placeholders must be an object, not an array. */
conn.format("SELECT :0 AS a, :1 AS b, :0+:1 AS ab", [2,3]);
// result: "SELECT :0 AS a, :1 AS b, :0+:1 AS ab"
conn.format("SELECT :0 AS a, :1 AS b, :0+:1 AS ab", {0:2, 1:3});
// result: "SELECT 2 AS a, 3 AS b, 2+3 AS ab"