Prepared Statements

Automatic creation, cached and re-used by connection

Similar to connection.query().

connection.execute('select 1 + ? + ? as result', [5, 6], (err, rows) => {
  // rows: [ { result: 12 } ]
  // internally 'select 1 + ? + ? as result' is prepared first. On subsequent calls cached statement is re-used
});

// close cached statement for 'select 1 + ? + ? as result'. noop if not in cache
connection.unprepare('select 1 + ? + ? as result');

Note that connection.execute() will cache the prepared statement for better performance, remove the cache with connection.unprepare() when you're done.

Manual prepare / execute

Manually prepared statements doesn't comes with LRU cache and SHOULD be closed using statement.close() instead of connection.unprepare().

connection.prepare('select ? + ? as tests', (err, statement) => {
  // statement.parameters - array of column definitions, length === number of params, here 2
  // statement.columns - array of result column definitions. Can be empty if result schema is dynamic / not known
  // statement.id
  // statement.query

  statement.execute([1, 2], (err, rows, columns) => {
    // -> [ { tests: 3 } ]
  });

  // don't use connection.unprepare(), it won't work!
  // note that there is no callback here. There is no statement close ack at protocol level.
  statement.close();
});

Note that you should not use statement after connection reset (changeUser() or disconnect). Statement scope is connection, you need to prepare statement for each new connection in order to use it.

Configuration

maxPreparedStatements : We keep the cached statements in a lru-cache. Default size is 16000 but you can use this option to override it. Any statements that are dropped from cache will be closed.

Serialization of bind parameters

The bind parameter values passed to execute are serialized JS -> MySQL as:

• null -> NULL
• number -> DOUBLE
• boolean -> TINY (0 for false, 1 for true)
• object -> depending on prototype:
  • Date -> DATETIME
  • JSON like object - JSON
  • Buffer -> VAR_STRING
• Other -> VAR_STRING

Passing in undefined or a function will result in an error.

Prepared Statements Helper

MySQL2 provides execute helper which will prepare and query the statement. You can also manually prepare / unprepare statement with prepare / unprepare methods.

connection.execute(
  'select ?+1 as qqq, ? as rrr, ? as yyy',
  [1, null, 3],
  (err, rows, fields) => {
    console.log(err, rows, fields);
    connection.execute(
      'select ?+1 as qqq, ? as rrr, ? as yyy',
      [3, null, 3],
      (err, rows, fields) => {
        console.log(err, rows, fields);
        connection.unprepare('select ?+1 as qqq, ? as rrr, ? as yyy');
        connection.execute(
          'select ?+1 as qqq, ? as rrr, ? as yyy',
          [3, null, 3],
          (err, rows, fields) => {
            console.log(err, rows, fields);
          }
        );
      }
    );
  }
);

Examples

For Prepared Statements examples, please see here.