mysql

What is mysql?

The mysql npm package is a Node.js client for interacting with MySQL databases. It allows users to connect to a MySQL database, execute queries, and handle database operations asynchronously.

What are mysql's main functionalities?

Connecting to a MySQL database

This code establishes a connection to a MySQL database using the provided credentials.

const mysql = require('mysql');
const connection = mysql.createConnection({
  host     : 'localhost',
  user     : 'me',
  password : 'secret',
  database : 'my_db'
});
connection.connect();

Executing a query

This code executes a SQL query to select all records from 'my_table' and logs the results.

connection.query('SELECT * FROM my_table', (error, results, fields) => {
  if (error) throw error;
  console.log(results);
});

Inserting data

This code inserts a new record into the 'posts' table with the provided data object.

const post  = {id: 1, title: 'Hello MySQL'};
const query = connection.query('INSERT INTO posts SET ?', post, (error, results, fields) => {
  if (error) throw error;
  // Neat!
});

Updating data

This code updates the title of a record in the 'posts' table where the id is 5.

connection.query('UPDATE posts SET title = ? WHERE id = ?', ['Hello World', 5], (error, results, fields) => {
  if (error) throw error;
  // Updated successfully
});

Closing the connection

This code closes the connection to the MySQL database.

connection.end();

Other packages similar to mysql

pg

The pg package is a PostgreSQL client for Node.js. Similar to mysql, it allows for connecting to a PostgreSQL database, executing queries, and handling database operations. It is designed specifically for PostgreSQL, whereas mysql is for MySQL databases.

sequelize

Sequelize is a promise-based Node.js ORM for Postgres, MySQL, MariaDB, SQLite, and Microsoft SQL Server. It provides a higher-level abstraction for database interactions and supports transactions, relations, eager and lazy loading, read replication, and more. It is more feature-rich and complex compared to the mysql package, which is a simple query client.

typeorm

TypeORM is an ORM that can run in Node.js and be used with TypeScript or JavaScript (ES5, ES6, ES7, ES8). It supports MySQL, PostgreSQL, MariaDB, SQLite, MS SQL Server, Oracle, WebSQL databases. It provides a lot of advanced features like automatic migrations, data mapping, and a powerful query builder. It is more advanced and feature-rich compared to the mysql package.

knex

Knex.js is a SQL query builder for Postgres, MSSQL, MySQL, MariaDB, SQLite3, Oracle, and Amazon Redshift, designed to be flexible, portable, and fun to use. It provides transaction support, connection pooling, and can be used as an SQL query builder in both Node.js and the browser. It is more versatile in terms of database support compared to the mysql package.

README

node-mysql

Introduction

This is a node.js driver for mysql. It is written in JavaScript, does not require compiling, and is 100% MIT licensed.

Here is an example on how to use it:

var mysql      = require('mysql');
var connection = mysql.createConnection({
  host     : 'localhost',
  user     : 'me',
  password : 'secret',
});

connection.connect();

connection.query('SELECT 1', function(err, rows, fields) {
  if (err) throw err;

  console.log('Query result: ', rows);
});

connection.end();

From this example, you can learn the following:

• Every method you invoke on a connection is queued and executed in sequence.
• Closing the connection is done using end() which makes sure all remaining queries are executed before sending a quit packet to the mysql server.

Contributors

Thanks goes to the people who have contributed code to this module, see the GitHub Contributors page.

Additionally I'd like to thank the following people:

• Andrey Hristov (Oracle) - for helping me with protocol questions.
• Ulf Wendel (Oracle) - for helping me with protocol questions.

Sponsors

The following companies have supported this project financially, allowing me to spend more time on it (ordered by time of contribution):

• Joyent
• pinkbike.com
• Holiday Extras (they are hiring)
• Newscope (they are hiring)

If you are interested in sponsoring a day or more of my time, please get in touch.

Community

So far all community activity has happened via the GitHub Issue system, however additionally I have just started a mailing list and IRC channel where people can ask questions and discuss things:

• Mailing list: https://groups.google.com/forum/#!forum/node-mysql
• IRC Channel: #node-mysql (on freenode.net)

Establishing connections

The recommended way to establish a connection is this:

var mysql      = require('mysql');
var connection = mysql.createConnection({
  host       : String, // defaults to 'localhost'
  port       : Number, // defaults to 3306
  socketPath : String, // defaults to undefined
  user       : String, // defaults to undefined
  password   : String, // defaults to undefined
  database   : String, // defaults to undefined
  charset    : String, // defaults to 'UTF8_GENERAL_CI'
  typeCast   : Boolean, // defaults to true
  debug      : Boolean, // defaults to false
});

connection.connect(function(err) {
  // connected! (unless `err` is set)
});

However, a connection can also be implicitly established by invoking a query:

var mysql      = require('mysql');
var connection = mysql.createConnection(...);

connection.query('SELECT 1', function(err, rows) {
  // connected! (unless `err` is set)
});

Depending on how you like to handle your errors, either method may be appropriate. Any type of connection error (handshake or network) is considered a fatal error, see the Error Handling section for more information.

Terminating connections

There are two ways to end a connection. Terminating a connection gracefully is done by calling the end() method:

connection.end(function(err) {
  // The connection is terminated now
});

This will make sure all previously enqueued queries are still before sending a COM_QUIT packet to the MySQL server. If a fatal error occurs before the COM_QUIT packet can be sent, an err argument will be provided to the callback, but the connection will be terminated regardless of that.

An alternative way to end the connection is to call the destroy() method. This will cause an immediate termination of the underlaying socket. Additionally destroy() guarantees that no more events or callbacks will be triggered for the connection.

connection.destroy();

Unlike end() the destroy() method does not take a callback argument.

Server disconnects

You may loose the connection to a MySQL server due to network problems, the server timing you out, or the server crashing. All of these events are considered fatal errors, and will have the err.code = 'PROTOCOL_CONNECTION_LOST'. See the Error Handling section for more information.

The best way to be notified about a connection termination is to listen for the 'close' event:

connection.on('close', function(err) {
  if (err) {
    // We did not expect this connection to terminate
    connection = mysql.createConnection(connection.config);
  } else {
    // We expected this to happen, end() was called.
  }
});

As you can see in the example above, re-connecting a connection is done by establishing a new connection. Once terminated, an existing connection object cannot be re-connected by design.

Please note that you will also receive a 'close' event with an err argument when a connection attempt fails because of bad credentials. If you find this cumbersome to work with, please post to the node-mysql mailing list to discuss improvements.

Escaping query values

In order to avoid SQL Injection attacks, you should always escape any user provided data before using it inside a SQL query. You can do so using the connection.escape() method:

var userId = 'some user provided value';
var sql    = 'SELECT * FROM users WHERE id = ' + connection.escape(userId);
connection.query(sql, function(err, results) {
  // ...
});

Alternatively, you can use ? characters as placeholders for values you would like to have escaped like this:

connection.query('SELECT * FROM users WHERE id = ?', [userId], function(err, results) {
  // ...
});

This looks similar to prepared statements in MySQL, however it really just uses the same connection.escape() method internally.

Different value types are escaped differently, here is how:

• Numbers are left untouched
• Booleans are converted to true / false strings
• Date objects are converted to 'YYYY-mm-dd HH:ii:ss' strings
• Buffers are converted to hex strings, e.g. X'0fa5'
• Strings are safely escaped
• Arrays are turned into list, e.g. ['a', 'b'] turns into 'a', 'b'
• Objects are turned into key = 'val' pairs. Nested objects are cast to strings.
• undefined / null are converted to NULL
• NaN / Infinity are left as-is. MySQL does not support these, and trying to insert them as values will trigger MySQL errors until they implement support.

If you paid attention, you may have noticed that this escaping allows you to do neat things like this:

var post  = {id: 1, title: 'Hello MySQL'};
var query = connection.query('INSERT INTO posts SET ?', post, function(err, result) {
  // Neat!
});
console.log(query.sql); // INSERT INTO posts SET `id` = 1, `title` = 'Hello MySQL'

Getting the id of an inserted row

If you are inserting a row into a table with an auto increment primary key, you can retrieve the insert id like this:

connection.query('INSERT INTO posts SET ?', {title: 'test'}, function(err, result) {
  if (err) throw err;

  console.log(result.insertId);
});

Executing queries in parallel

The MySQL protocol is sequential, this means that you need multiple connections to execute queries in parallel. Future version of this module may ship with a connection pool implementation, but for now you have to figure out how to manage multiple connections yourself if you want to execute queries in parallel.

One simple approach is to create one connection per incoming http request.

Streaming query rows

Sometimes you may want to select large quantities of rows and process each of them as they are received. This can be done like this:

var query = connection.query('SELECT * FROM posts');
query
  .on('error', function(err) {
    // Handle error, an 'end' event will be emitted after this as well
  })
  .on('fields', function(fields) {
    // the field packets for the rows to follow
  })
  .on('result', function(row) {
    // Pausing the connnection is useful if your processing involves I/O
    connection.pause();

    processRow(row, function() {
      connection.resume();
    });
  })
  .on('end', function() {
    // all rows have been received
  });

Please note a few things about the example above:

• Usually you will want to receive a certain amount of rows before starting to throttle the connection using pause(). This number will depend on the amount and size of your rows.
• pause() / resume() operate on the underlaying socket and parser. You are guaranteed that no more 'result' events will fire after calling pause().
• You MUST NOT provide a callback to the query() method when streaming rows.
• The 'result' event will fire for both rows as well as OK packets confirming the success of a INSERT/UPDATE query.

Additionally you may be interested to know that it is currently not possible to stream individual row columns, they will always be buffered up entirely. If you have a good use case for streaming large fields to and from MySQL, I'd love to get your thoughts and conributions on this.

Multiple statement queries

Support for multiple statements is disabled for security reasons (it allows for SQL injection attacks if values are not properly escaped). To use this feature you have to enable it for your connection:

var connection = mysql.createConnection({multipleStatements: true});

Once enabled, you can execute multiple statement queries like any other query:

connection.query('SELECT 1; SELECT 2', function(err, results) {
  if (err) throw err;

  // `results` is an array with one element for every statement in the query:
  console.log(results[0]); // [{1: 1}]
  console.log(results[1]); // [{2: 2}]
});

Additionally you can also stream the results of multiple statement queries:

var query = connection.query('SELECT 1; SELECT 2');

query
  .on('fields', function(fields, index) {
    // the fields for the result rows that follow
  })
  .on('result', function(row, index) {
    // index refers to the statement this result belongs to (starts at 0)
  });

If one of the statements in your query causes an error, the resulting Error object contains a err.index property which tells you which statement caused it. MySQL will also stop executing any remaining statements when an error occurs.

Please note that the interface for streaming multiple statement queries is experimental and I am looking forward to feedback on it.

Stored procedures

You can call stored procedures from your queries as with any other mysql driver. If the stored procedure produces several result sets, they are exposed to you the same way as the results for multiple statement queries.

Joins with overlapping column names

When executing joins, you are likely to get result sets with overlapping column names.

By default, node-mysql will overwrite colliding column names in the order the columns are received from MySQL, causing some of the received values to be unavailable.

However, you can also specify that you want your columns to be nested below the table name like this:

var options = {sql: '...', nestTables: true};
connection.query(options, function(err, results) {
  /* results will be an array like this now:
  [{
    table1: {
      fieldA: '...',
      fieldB: '...',
    },
    table2: {
      fieldA: '...',
      fieldB: '...',
    },
  }, ...]
  */
});

Error handling

This module comes with a consistent approach to error handling that you should review carefully in order to write solid applications.

All errors created by this module are instances of the JavaScript Error object. Additionally they come with two properties:

• err.code: Either a MySQL server error (e.g. 'ER_ACCESS_DENIED_ERROR'), a node.js error (e.g. 'ECONNREFUSED') or an internal error (e.g. 'PROTOCOL_PARSER_EXCEPTION').
• err.fatal: Boolean, indicating if this error is terminal to the connection object.

Fatal errors are propagated to all pending callbacks. In the example below, a fatal error is triggered by trying to connect to an invalid port. Therefore the error object is propagated to both pending callbacks:

var connection = require('mysql').createConnection({
  port: 84943, // WRONG PORT
});

connection.connect(function(err) {
  console.log(err.code); // 'ECONNREFUSED'
  console.log(err.fatal); // true
});

connection.query('SELECT 1', function(err) {
  console.log(err.code); // 'ECONNREFUSED'
  console.log(err.fatal); // true
});

Normal errors however are only delegated to the callback they belong to. So in the example below, only the first callback receives an error, the second query works as expected:

connection.query('USE name_of_db_that_does_not_exist', function(err, rows) {
  console.log(err.code); // 'ER_BAD_DB_ERROR'
});

connection.query('SELECT 1', function(err, rows) {
  console.log(err); // null
  console.log(rows.length); // 1
});

Last but not least: If a fatal errors occurs and there are no pending callbacks, or a normal error occurs which has no callback belonging to it, the error is emitted as an 'error' event on the connection object. This is demonstrated in the example below:

connection.on('error', function(err) {
  console.log(err.code); // 'ER_BAD_DB_ERROR'
});

connection.query('USE name_of_db_that_does_not_exist');

Note: 'error' are special in node. If they occur without an attached listener, a stack trace is printed and your process is killed.

tl;dr: This module does not want you to to deal with silent failures. You should always provide callbacks to your method calls. If you want to ignore this advice and suppress unhandled errors, you can do this:

// I am Chuck Noris:
connection.on('error', function() {});

Type casting

For your convenience, this driver will cast mysql types into native JavaScript types by default. The following mappings exist:

Number

• TINYINT
• SMALLINT
• INT
• MEDIUMINT
• YEAR
• FLOAT
• DOUBLE

Date

• TIMESTAMP
• DATE
• DATETIME

Buffer

• TINYBLOB
• MEDIUMBLOB
• LONGBLOB
• BLOB
• BINARY
• VARBINARY
• BIT (last byte will be filled with 0 bits as neccessary)

String

• CHAR
• VARCHAR
• TINYTEXT
• MEDIUMTEXT
• LONGTEXT
• TEXT
• ENUM
• SET
• DECIMAL (may exceed float precision)
• BIGINT (may exceed float precision)
• TIME (could be mapped to Date, but what date would be set?)
• GEOMETRY (never used those, get in touch if you do)

It is not recommended (and may go away / change in the future) to disable type casting, but you can currently do so on either the connection:

var connection = require('mysql').createConnection({typeCast: false});

Or on the query level:

var options = {sql: '...', typeCast: false};
var query = connection.query(options, function(err, results) {

}):

Debugging and reporting problems

If you are running into problems, one thing that may help is enabling the debug mode for the connection:

var connection = mysql.createConnection({debug: true});

This will print all incoming and outgoing packets on stdout.

If that does not help, feel free to open a GitHub issue. A good GitHub issue will have:

• The minimal amount of code required to reproduce the problem (if possible)
• As much debugging output and information about your environment (mysql version, node version, os, etc.) as you can gather.

FAQ

I have yet to write this, but it will include:

• What benefits does this driver have over compiled alternatives? (portability, license, docs)
• How is the performance of this module compared to the alternatives? (great)
• Is there commercial support for this driver? (nodefirm?)
• What features are missing? (stored procedures, transactions, pause, etc.)
• How can I contribute? (explain)
• What are the goals of this project?

Todo

• Prepared statements
• setTimeout() for Connection / Query
• connection pooling
• Support for encodings other than UTF-8 / ASCII
• API support for transactions, similar to php