JSPM – mysql2@0.8.14

Package Exports

mysql2
mysql2/index.js
mysql2/lib/commands/index.js
mysql2/lib/connection
mysql2/lib/connection.js
mysql2/lib/connection_config
mysql2/lib/connection_config.js
mysql2/lib/constants/charsets
mysql2/lib/constants/charsets.js
mysql2/lib/constants/client
mysql2/lib/constants/client.js
mysql2/lib/constants/errors.js
mysql2/lib/constants/types.js
mysql2/lib/packet_parser.js
mysql2/lib/packets/index.js
mysql2/lib/packets/packet
mysql2/lib/packets/packet.js
mysql2/lib/pool
mysql2/lib/pool.js
mysql2/lib/pool_config.js
mysql2/lib/server.js

This package does not declare an exports field, so the exports above have been automatically detected and optimized by JSPM instead. If any package subpath is missing, it is recommended to post an issue to the original package (mysql2) to support the "exports" field. If that is not possible, create a JSPM override to customize the exports field for this package.

Readme

#node-mysql2

TODO:

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

Installation

npm install mysql2

Features

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

MySQL server API for proxies and mocks
SSL and compression
prepared statements

Documentation

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

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);

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);

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

events:

query(sql) - query from client

License

MIT

Acknowledgements

Internal protocol is written from scratch using my experience with mysql-native
constants, sql parameters interpolation, pool, connection config class taken from node-mysql (I tried to preserve git history)
SSL upgrade code based on @TooTallNate code
Secure connection / compressed connection api flags compatible to mariasql client.
contributors

Benchmarks

see node-mysql-benchmarks
try to run example benchmarks on your system

Contributing

Feel free to create pull requests. TODO in order of importance:

node-mysql api incompatibility fixes
documentation
tests
benchmarks
bug fixes
TODOs in source code
performance improvements
features

Features TODO

more server side commands support (binary protocol, etc)
named parameters interpolarion into unnamed parameters translation for prepared statements
mysql-postgres bridge example
mysql-mongo bridge example using js-based sql parser