node-mysql2: A JavaScript repository from 981746

#node-mysql2

Mysql client for node.js. Written in native JavaScript and aims to be mostly api compatible with node-mysql

Features

In addition to client-side query/escape and connection pooling

fast
MySQL server API for proxies and mocks
SSL and compression
prepared statements
binlog protocol client

Documentation

See node-mysql documentation. If you see api incompatibilities, please report via github issue.

Below is a list of extensions not supported by node-mysql:

Named placeholders

You can use named placeholders for parameters by setting namedPlaceholders config value or query/execute time option. Named placeholders are converted to unnamed ? on the client (mysql protocol does not support named parameters). If you reference parameter multiple times under the same name it is sent to server multiple times.

   connection.config.namedPlaceholders = true;
   connection.execute('select :x + :y as z', { x: 1, y: 2}, function(err, rows) {
     // statement prepared as "select ? + ? as z" and executed with [1,2] values
     // rows returned: [ { z: 3 } ]
   });

   connection.execute('select :x + :x as z', { x: 1 }, function(err, rows) {
     // select ? + ? as z, execute with [1, 1]
   });

   connection.query('select :x + :x as z', { x: 1 }, function(err, rows) {
     // query select 1 + 1 as z
   });

Prepared statements

Automatic creation, cached and re-used by connection

Similar to connection.query().

connection.execute('select 1 + ? + ? as result', [5, 6], function(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');

Manual prepare / execute

connection.prepare('select ? + ? as tests', function(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], function(err, rows, columns) {
    // -> [ { tests: 3 } ]
   });

   // 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.

Receiving rows as array of columns instead of hash with column name as key:

var options = {sql: 'select A,B,C,D from foo', rowsAsArray: true};
connection.query(options, function(err, results) {
  /* results will be an array of arrays like this now:
  [[
     'field A value',
     'field B value',
     'field C value',
     'field D value',
  ], ...]
  */
});

Sending tabular data with 'load infile' and local stream:

In addition to sending local fs files you can send any stream using infileStreamFactory query option. If set, it has to be a function that return a readable stream. It gets file path from query as a parameter.

// local file
connection.query('LOAD DATA LOCAL INFILE "/tmp/data.csv" INTO TABLE test FIELDS TERMINATED BY ? (id, title)', onInserted1);
// local stream
var sql = 'LOAD DATA LOCAL INFILE "mystream" INTO TABLE test FIELDS TERMINATED BY ? (id, title)';
connection.query({
  sql: sql,
  infileStreamFactory: function(path) { return getStream(); }
}, onInserted2);

Connecting using custom stream:

var net        = require('net');
var mysql      = require('mysql2');
var shape      = require('shaper');
var connection = mysql.createConnection({
   user: 'test',
   database: 'test',
   stream: net.connect('/tmp/mysql.sock').pipe(shape(10)) // emulate 10 bytes/sec link
});
connection.query('SELECT 1+1 as test1', console.log);

stream also can be a function. In that case function result has to be duplex stream, and it is used for connection transport. This is required if you connect pool using custom transport as new pooled connection needs new stream. Example connecting over socks5 proxy:

var mysql      = require('mysql2');
var SocksConnection = require('socksjs');
var pool = mysql.createPool({
  database: 'test',
  user: 'foo',
  password: 'bar'
  stream: function(cb) {
    cb(null, new SocksConnection({ host: 'remote.host', port: 3306}, { host: 'localhost', port: 1080 }));
  }
 });

In addition to password createConnection(), createPool() and changeUser() accept passwordSha1 option. This is useful when implementing proxies as plaintext password might be not available.

Known incompatibilities with node-mysql

In contrast to node-mysql, zeroFill flag is ignored in type conversion. You need to check corresponding field zeroFill flag and convert to string manually if this is of importance to you.

DECIMAL and NEWDECIMAL types always returned as string

Examples

Simple select:

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

connection.query('SELECT 1+1 as test1', function(err, rows) {
  //
});

Prepared statement and parameters:

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

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

Connecting over encrypted connection:

var fs         = require('fs');
var mysql      = require('mysql2');
var connection = mysql.createConnection({
   user: 'test',
   database: 'test',
   ssl: {
     key: fs.readFileSync('./certs/client-key.pem'),
     cert: fs.readFileSync('./certs/client-cert.pem')
   }
});
connection.query('SELECT 1+1 as test1', console.log);

You can use 'Amazon RDS' string as value to ssl property to connect to Amazon RDS mysql over ssl (in that case http://s3.amazonaws.com/rds-downloads/mysql-ssl-ca-cert.pem CA cert is used)

var mysql      = require('mysql2');
var connection = mysql.createConnection({
   user: 'foo',
   password: 'bar',
   host: 'db.id.ap-southeast-2.rds.amazonaws.com',
   ssl: 'Amazon RDS'
});

conn.query('show status like \'Ssl_cipher\'', function(err, res) {
  console.log(err, res);
  conn.end();
});

Simple mysql proxy server:

var mysql = require('mysql2');

var server = mysql.createServer();
server.listen(3307);
server.on('connection', function(conn) {
  console.log('connection');

  conn.serverHandshake({
    protocolVersion: 10,
    serverVersion: 'node.js rocks',
    connectionId: 1234,
    statusFlags: 2,
    characterSet: 8,
    capabilityFlags: 0xffffff
  });

  conn.on('field_list', function(table, fields) {
    console.log('field list:', table, fields);
    conn.writeEof();
  });

  var remote = mysql.createConnection({user: 'root', database: 'dbname', host:'server.example.com', password: 'secret'});

  conn.on('query', function(sql) {
    console.log('proxying query:' + sql);
    remote.query(sql, function(err) { // overloaded args, either (err, result :object)
                                      // or (err, rows :array, columns :array)
      if (Array.isArray(arguments[1])) {
        // response to a 'select', 'show' or similar
        var rows = arguments[1], columns = arguments[2];
        console.log('rows', rows);
        console.log('columns', columns);
        conn.writeTextResult(rows, columns);
      } else {
        // response to an 'insert', 'update' or 'delete'
        var result = arguments[1];
        console.log('result', result);
        conn.writeOk(result);
      }
    });
  });

  conn.on('end', remote.end.bind(remote));
});

MySQL Server API

Server

createServer() - creates server instance
Server.listen - listen port / unix socket (same arguments as net.Server.listen)

events:

connect - new incoming connection.

Connection

serverHandshake({serverVersion, protocolVersion, connectionId, statusFlags, characterSet, capabilityFlags}) - send server handshake initialisation packet, wait handshake response and start listening for commands
writeOk({affectedRows: num, insertId: num}) - send OK packet to client
writeEof(warnings, statusFlags) - send EOF packet
writeTextResult(rows, fields) - write query result to client. Rows and fields are in the same format as in connection.query callback.
writeColumns(fields) - write fields + EOF packets.
writeTextRow(row) - write array (not hash!) ov values as result row
TODO: binary protocol