CODEWITHSUNDEEP
mysqlnode.js
Gary Krohmer
Gary Krohmer

Reputation: 106

How do I use prepared queries using the mysql2 package on node.js

The example given in the documentation is as follows:

var mysql      = require('mysql2');
var connection = mysql.createConnection({ user: 'test', database: 'test'});

connection.execute('SELECT 1+? as test1', [10], function(err, rows) {
  //
});

The idea here is that the "?" in the query gets replaced with the value of "10". This example shows how to use the query once with the replacement value of "10". How can I use the prepared query multiple times with different replacement values? The hope here is that a prepared query has been compiled and so multiple invocations of that query will not incur the performance hit of compiling the query each time it is run.

thanks - Gary

Upvotes: 1

Views: 1064

Answers (1)

Paul Mougel
Paul Mougel

Reputation: 17038

It looks like the statements are really prepared and cached, as suggested by the source code:

Execute.prototype.start = function(packet, connection) {
  var cachedStatement = connection.statements[this.query];
  if (!cachedStatement) { // prepare first
    connection.writePacket(new Packets.PrepareStatement(this.query).toPacket(1));
  } else {
    this.statementInfo = cachedStatement;
    return this.doExecute(connection);
  }
  return Execute.prototype.prepareHeader;
};

This is confirmed by this comment on the issue of node-mysql that talks about node-mysql2, by the author of the library:

its prepared once and can be used many times.

This comment thread also talks about the eviction politic of the prepared statements: LRU or MRU, which is in agreement with the fact that node-mysql2 does indeed prepare and cache the statements (and this is even noted as a TODO in the source code).

Upvotes: 1

Related Questions