mysql2

What is mysql2?

The mysql2 npm package is a fast and efficient MySQL client for Node.js that provides an easy-to-use API for interacting with MySQL databases. It supports promises and async/await, connection pooling, prepared statements, and more.

What are mysql2's main functionalities?

Basic Connection

This feature allows you to create a basic connection to a MySQL database and execute a query.

const mysql = require('mysql2');
const connection = mysql.createConnection({ host: 'localhost', user: 'root', database: 'test' });
connection.query('SELECT * FROM `table` WHERE `name` = "Page"', function(err, results) { console.log(results); });
connection.end();

Promise Wrapper

This feature provides a promise-based API for working with MySQL, which allows for the use of async/await for better asynchronous control flow.

const mysql = require('mysql2/promise');
async function queryDatabase() {
  const connection = await mysql.createConnection({ host: 'localhost', user: 'root', database: 'test' });
  const [rows, fields] = await connection.execute('SELECT * FROM `table` WHERE `name` = ?', ['Page']);
  console.log(rows);
  connection.end();
}
queryDatabase();

Connection Pooling

This feature allows you to create a pool of connections that can be reused, which is more efficient than creating a new connection for every query.

const mysql = require('mysql2');
const pool = mysql.createPool({ host: 'localhost', user: 'root', password: 'root', database: 'test', waitForConnections: true, connectionLimit: 10, queueLimit: 0 });
pool.query('SELECT * FROM `table`', function(err, results, fields) { console.log(results); });
pool.end();

Prepared Statements

This feature allows you to use prepared statements, which can improve performance and security by pre-compiling SQL queries and avoiding SQL injection.

const mysql = require('mysql2');
const connection = mysql.createConnection({ host: 'localhost', user: 'root', database: 'test' });
const statement = connection.prepare('SELECT * FROM `table` WHERE `id` = ?');
statement.execute([1], function(err, results) { console.log(results); });
statement.close();
connection.end();

Other packages similar to mysql2

mysql

The 'mysql' package is the original MySQL client for Node.js. It is similar to mysql2 but does not support promises natively, which means you would need to use a wrapper or callbacks for asynchronous operations.

mariadb

The 'mariadb' package is a Node.js client specifically designed for MariaDB databases. It is API-compatible with mysql2 but includes additional features and optimizations for MariaDB.

knex

Knex.js is a SQL query builder for Node.js that supports multiple database systems, including MySQL. It provides a more abstract way to build queries and can be used with mysql2 as the underlying database driver.

sequelize

Sequelize is an ORM (Object-Relational Mapping) library for Node.js. It provides a higher-level abstraction for database interactions and supports MySQL among other databases. Unlike mysql2, Sequelize allows you to work with data as objects and automatically handles the SQL generation.

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