Proposal for named parameters binding

[Koc]

It would be nice support this

conn.query('INSERT INTO table1 SER field1 = :field1, field2 = :field2 ON DUPLICATE KEY UPDATE field2 = :field2', {field1: 1, field2: 2});

[felixge]

Nice: yes. But a little too high-level for this driver, so I'm probably not adding this for now.

[ghost]

While waiting for this feature :

/**
 * Return array with modified instruction as first entry, and indexed anonymous parameters as second entry,  that can be intepreted by node-mysql mysql driver
 * @param {string} sqlCommandSring Instruction's raw content
 * @param {object} binds Named parameters plain object
 * @returns {Array}
 */
var getStatement = function( sqlCommandSring, binds )
{
    var indexedBinds = [];

    var commandFoundedBinds = sqlCommandSring.match(/(:[a-zA-Z_]+)/g);

    commandFoundedBinds.forEach(function(bindString, index)
    {
        var clean_name = bindString.replace(/:/, "");

        if( binds.hasOwnProperty(clean_name) )
        {
            var rgx = new RegExp("("+ bindString +")");

            sqlCommandSring = sqlCommandSring.replace(rgx, "?");

            indexedBinds.push( binds[ clean_name ] );
        }
    });

    return [ sqlCommandSring, indexedBinds ];
};

Usage :

var stmt = getStatement("INSERT INTO table1 SER field1 = :field1, field2 = :field2 ON DUPLICATE KEY UPDATE field2 = :field2", {field1: 1, field2: 2});
connection.execute( stmt[0], stmt[1], function(err, res)
{
    console.log( res );
});

Returns :

[
    "INSERT INTO table1 SER field1 = ?, field2 = ? ON DUPLICATE KEY UPDATE field2 = ?",
    [
        1,
        2,
        2
    ]
]

Very basic, can be enhanced

[sidorares]

I created module to transform query with named placeholders + parameters hash into unnamed + array

See similar discussion in node-mysql2 sidorares/node-mysql2#117

Example:

var mysql = require('mysql');
var toUnnamed = require('named-placeholders')();

var q = toUnnamed('select 1+:test', { test: 123});
var sql = q[0];
var parameters = q[1];
mysl.createConnection().query(sql, parameters);

Not sure if it should belong to core driver (maybe should, most other mysql clients expose pdo-style parameters).

[pihvi]

Here's a library for this also supporting ?? syntax:

var sql = require('yesql').mysql

var selectById = sql('SELECT * from ::table_name WHERE id = :id;')
connection.query(selectById({id: 5, table_name: 'pokemon'}), callback)

[pupudu]

@felixge Is this still a no no for this library? (since it's been almost 7 years)

[dougwilson]

The module is part of the mysqljs org. Use it when you need it.