Product
Introducing License Enforcement in Socket
Ensure open-source compliance with Socket’s License Enforcement Beta. Set up your License Policy and secure your software!
fast mysql driver. Implements core protocol, prepared statements, ssl and compression in native JS
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.
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();
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.
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.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 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.
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
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:
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.
// get the client
const mysql = require('mysql2');
// create the connection to database
const connection = mysql.createConnection({
host: 'localhost',
user: 'root',
database: 'test'
});
// simple query
connection.query(
'SELECT * FROM `table` WHERE `name` = "Page" AND `age` > 45',
function(err, results, fields) {
console.log(results); // results contains rows returned by server
console.log(fields); // fields contains extra meta data about results, if available
}
);
// with placeholder
connection.query(
'SELECT * FROM `table` WHERE `name` = ? AND `age` > ?',
['Page', 45],
function(err, results) {
console.log(results);
}
);
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.
// get the client
const mysql = require('mysql2');
// create the connection to database
const connection = mysql.createConnection({
host: 'localhost',
user: 'root',
database: 'test'
});
// execute will internally call prepare and query
connection.execute(
'SELECT * FROM `table` WHERE `name` = ? AND `age` > ?',
['Rick C-137', 53],
function(err, results, fields) {
console.log(results); // results contains rows returned by server
console.log(fields); // fields contains extra meta data about results, if available
// If you execute same statement again, it will be picked from a LRU cache
// which will save query preparation time and give better performance
}
);
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.
// get the client
const mysql = require('mysql2');
// Create the connection pool. The pool-specific settings are the defaults
const pool = mysql.createPool({
host: 'localhost',
user: 'root',
database: 'test',
waitForConnections: true,
connectionLimit: 10,
maxIdle: 10, // max idle connections, the default value is the same as `connectionLimit`
idleTimeout: 60000, // idle connections timeout, in milliseconds, the default value 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()
):
// For pool initialization, see above
pool.query("SELECT `field` FROM `table`", function(err, rows, fields) {
// Connection is automatically released when query resolves
});
Alternatively, there is also the possibility of manually acquiring a connection from the pool and returning it later:
// For pool initialization, see above
pool.getConnection(function(err, conn) {
// Do something with the connection
conn.query(/* ... */);
// Don't forget to release the connection when finished!
pool.releaseConnection(conn);
});
MySQL2 also support Promise API. Which works very well with ES7 async await.
async function main() {
// get the client
const mysql = require('mysql2/promise');
// create the connection
const connection = await mysql.createConnection({host:'localhost', user: 'root', database: 'test'});
// query database
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.
// get the client
const mysql = require('mysql2/promise');
// get the promise implementation, we will use bluebird
const bluebird = require('bluebird');
// create the connection, specify bluebird as Promise
const connection = await mysql.createConnection({host:'localhost', user: 'root', database: 'test', Promise: bluebird});
// query database
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() {
// get the client
const mysql = require('mysql2');
// create the pool
const pool = mysql.createPool({host:'localhost', user: 'root', database: 'test'});
// now get a Promise wrapped instance of that pool
const promisePool = pool.promise();
// query database using promises
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.
// get the client
const mysql = require('mysql2');
// create the connection
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());
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).
const con = mysql.createConnection(
{ host: 'localhost', database: 'test', user: 'root', rowsAsArray: true }
);
con.query({ sql: 'select 1 as foo, 2 as foo', rowsAsArray: true }, function(err, results, fields) {
console.log(results); // in this query, results will be an array of arrays rather than an array of objects
console.log(fields); // fields are unchanged
});
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.
You can find more detailed documentation here. You should also check various code examples to understand advanced concepts.
ConnectionConfig
class taken from node-mysqlWant to improve something in node-mysql2
. Please check Contributing.md for detailed instruction on how to get started.
FAQs
fast mysql driver. Implements core protocol, prepared statements, ssl and compression in native JS
We found that mysql2 demonstrated a healthy version release cadence and project activity because the last version was released less than a year ago. It has 3 open source maintainers collaborating on the project.
Did you know?
Socket for GitHub automatically highlights issues in each pull request and monitors the health of all your open source dependencies. Discover the contents of your packages and block harmful activity before you install or update your dependencies.
Product
Ensure open-source compliance with Socket’s License Enforcement Beta. Set up your License Policy and secure your software!
Product
We're launching a new set of license analysis and compliance features for analyzing, managing, and complying with licenses across a range of supported languages and ecosystems.
Product
We're excited to introduce Socket Optimize, a powerful CLI command to secure open source dependencies with tested, optimized package overrides.