MySQL 2
![License](https://img.shields.io/npm/l/mysql2.svg?maxAge=2592000)
English | 简体中文 | Português (BR)
MySQL client for Node.js with focus on performance. Supports prepared statements, non-utf8 encodings, binary log protocol, compression, ssl much more.
Table of contents
History and Why MySQL2
MySQL2 project is a continuation of MySQL-Native. Protocol parser code was rewritten from scratch and api changed to match popular mysqljs/mysql. MySQL2 team is working together with mysqljs/mysql team to factor out shared code and move it under mysqljs organisation.
MySQL2 is mostly API compatible with mysqljs and supports majority of features. MySQL2 also offers these additional features:
Installation
MySQL2 is free from native bindings and can be installed on Linux, Mac OS or Windows without any issues.
npm install --save mysql2
If you are using TypeScript, you will need to install @types/node
.
npm install --save-dev @types/node
For TypeScript documentation and examples, see here.
First Query
const mysql = require('mysql2');
const connection = mysql.createConnection({
host: 'localhost',
user: 'root',
database: 'test'
});
connection.query(
'SELECT * FROM `table` WHERE `name` = "Page" AND `age` > 45',
function(err, results, fields) {
console.log(results);
console.log(fields);
}
);
connection.query(
'SELECT * FROM `table` WHERE `name` = ? AND `age` > ?',
['Page', 45],
function(err, results) {
console.log(results);
}
);
Using Prepared Statements
With MySQL2 you also get the prepared statements. With prepared statements MySQL doesn't have to prepare plan for same query every time, this results in better performance. If you don't know why they are important, please check these discussions:
MySQL2 provides execute
helper which will prepare and query the statement. You can also manually prepare / unprepare statement with prepare
/ unprepare
methods.
const mysql = require('mysql2');
const connection = mysql.createConnection({
host: 'localhost',
user: 'root',
database: 'test'
});
connection.execute(
'SELECT * FROM `table` WHERE `name` = ? AND `age` > ?',
['Rick C-137', 53],
function(err, results, fields) {
console.log(results);
console.log(fields);
}
);
Using connection pools
Connection pools help reduce the time spent connecting to the MySQL server by reusing a previous connection, leaving them open instead of closing when you are done with them.
This improves the latency of queries as you avoid all of the overhead that comes with establishing a new connection.
const mysql = require('mysql2');
const pool = mysql.createPool({
host: 'localhost',
user: 'root',
database: 'test',
waitForConnections: true,
connectionLimit: 10,
maxIdle: 10,
idleTimeout: 60000,
queueLimit: 0,
enableKeepAlive: true,
keepAliveInitialDelay: 0
});
The pool does not create all connections upfront but creates them on demand until the connection limit is reached.
You can use the pool in the same way as connections (using pool.query()
and pool.execute()
):
pool.query("SELECT `field` FROM `table`", function(err, rows, fields) {
});
Alternatively, there is also the possibility of manually acquiring a connection from the pool and returning it later:
pool.getConnection(function(err, conn) {
conn.query();
pool.releaseConnection(conn);
});
Using Promise Wrapper
MySQL2 also support Promise API. Which works very well with ES7 async await.
async function main() {
const mysql = require('mysql2/promise');
const connection = await mysql.createConnection({host:'localhost', user: 'root', database: 'test'});
const [rows, fields] = await connection.execute('SELECT * FROM `table` WHERE `name` = ? AND `age` > ?', ['Morty', 14]);
}
MySQL2 use default Promise
object available in scope. But you can choose which Promise
implementation you want to use.
const mysql = require('mysql2/promise');
const bluebird = require('bluebird');
const connection = await mysql.createConnection({host:'localhost', user: 'root', database: 'test', Promise: bluebird});
const [rows, fields] = await connection.execute('SELECT * FROM `table` WHERE `name` = ? AND `age` > ?', ['Morty', 14]);
MySQL2 also exposes a .promise() function on Pools, so you can create a promise/non-promise connections from the same pool.
async function main() {
const mysql = require('mysql2');
const pool = mysql.createPool({host:'localhost', user: 'root', database: 'test'});
const promisePool = pool.promise();
const [rows,fields] = await promisePool.query("SELECT 1");
}
MySQL2 exposes a .promise() function on Connections, to "upgrade" an existing non-promise connection to use promise.
const mysql = require('mysql2');
const con = mysql.createConnection(
{host:'localhost', user: 'root', database: 'test'}
);
con.promise().query("SELECT 1")
.then( ([rows,fields]) => {
console.log(rows);
})
.catch(console.log)
.then( () => con.end());
Array Results
If you have two columns with the same name, you might want to get results as an array rather than an object to prevent them from clashing. This is a deviation from the Node MySQL library.
For example: select 1 as foo, 2 as foo
.
You can enable this setting at either the connection level (applies to all queries), or at the query level (applies only to that specific query).
Connection Level
const con = mysql.createConnection(
{ host: 'localhost', database: 'test', user: 'root', rowsAsArray: true }
);
Query Level
con.query({ sql: 'select 1 as foo, 2 as foo', rowsAsArray: true }, function(err, results, fields) {
console.log(results);
console.log(fields);
});
API and Configuration
MySQL2 is mostly API compatible with Node MySQL. You should check their API documentation to see all available API options.
One known incompatibility is that DECIMAL
values are returned as strings whereas in Node MySQL they are returned as numbers. This includes the result of SUM()
and AVG()
functions when applied to INTEGER
arguments. This is done deliberately to avoid loss of precision - see https://github.com/sidorares/node-mysql2/issues/935.
If you find any other incompatibility with Node MySQL, Please report via Issue tracker. We will fix reported incompatibility on priority basis.
Documentation
You can find more detailed documentation here. You should also check various code examples to understand advanced concepts.
Acknowledgements
- Internal protocol is written by @sidorares MySQL-Native
- Constants, SQL parameters interpolation, Pooling,
ConnectionConfig
class taken from node-mysql - SSL upgrade code based on @TooTallNate code
- Secure connection / compressed connection api flags compatible to MariaSQL client.
- Contributors
Contributing
Want to improve something in node-mysql2
. Please check Contributing.md for detailed instruction on how to get started.