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.
#node-mysql2
Mysql client for node.js. Written in native JavaScript and aims to be mostly api compatible with node-mysql
In addition to client-side query/escape and connection pooling
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:
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
});
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');
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.
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',
], ...]
*/
});
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);
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.
All numeric types converted to numbers. 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
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));
});
events:
connection.query callback.events:
MIT
npm run benchmarksFeel free to create pull requests. TODO in order of importance:
FAQs
fast mysql driver. Implements core protocol, prepared statements, ssl and compression in native JS
The npm package mysql2 receives a total of 2,311,075 weekly downloads. As such, mysql2 popularity was classified as popular.
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.
Research
Security News
Socket researchers uncover a malicious npm package posing as a tool for detecting vulnerabilities in Etherium smart contracts.
Security News
Research
A supply chain attack on Rspack's npm packages injected cryptomining malware, potentially impacting thousands of developers.
Research
Security News
Socket researchers discovered a malware campaign on npm delivering the Skuld infostealer via typosquatted packages, exposing sensitive data.