w3resource logo


node.js Tutorial

Node.js with MySQL

Secondary Nav

node-mysql : A node.js module implementing the MySQL protocol

This is a node.js driver for mysql. It is written in JavaScript, does not require compiling. It provides all most all connection/query from MySQL. Node-mysql is probably one of the best modules used for working with MySQL database and the module is actively maintained.

We assume that you have already installed MySQL and node.js on Windows or Linux environment.

Here is an example to retrieve the first row from 'employees' table belongs to 'hr' database :

var mysql      = require('mysql');
var connection = mysql.createConnection({
  host     : 'localhost',
  user     : 'root',
  password : 'datasoft123',
  database : 'hr'
});
connection.connect();

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

  console.log(rows[0]);
});

connection.end();

Output :

{ EMPLOYEE_ID: 100,
  FIRST_NAME: 'Steven',
  LAST_NAME: 'King',
  EMAIL: [email protected]',
  PHONE_NUMBER: '515.123.4567',
  HIRE_DATE: Wed Jun 17 1987 00:00:00 GMT+0530 (India Standard Time),
  JOB_ID: 'AD_PRES',
  SALARY: 24000,
  COMMISSION_PCT: 0,
  MANAGER_ID: 0,
  DEPARTMENT_ID: 90 }

From the above example you can learn how to create a new connection and close the connection.

Contents:

Install MySQL node.js driver

$ npm install mysql

You can install the latest version from Github to check if a bugfix is working. In this case use the following command :

$ npm install felixge/node-mysql

Create connection

Here is the code to establish a connection :

var mysql      = require('mysql');
var connection = mysql.createConnection({
  host     : 'example.org',
  user     : 'root',
  password : 'datasoft123'
});

connection.connect(function(err) {
  if (err) {
    console.error('error connecting: ' + err.stack);
    return;
  }

 console.log('connected as id ' + connection.threadId);
});

Here is an another method to establish a connection by invoking a query :

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

connection.query('SELECT 1', function(err, rows) {

if (err) {
    console.error('error connecting: ' + err.stack);
    return;
  }
console.log('connected!');
});

Connection Option

Name Description
host The hostname of the database. Default is localhost.
port The port number to connect to. Default is 3306.
localAddress The source IP address to use for TCP connection. (Optional)
socketPath The path to a unix domain socket to connect to. When used host and port are ignored.
user The MySQL user to authenticate as.
password The password of that MySQL user.
database Name of the database to use for this connection (Optional).
charset The charset for the connection. This is called "collation" in the SQL-level of MySQL (like utf8_general_ci). If a SQL-level charset is specified (like utf8mb4) then the default collation for that charset is used. (Default: 'UTF8_GENERAL_CI')
timezone The timezone used to store local dates. Default is : 'local'.
connectTimeout The milliseconds before a timeout occurs during the initial connection to the MySQL server. (Default: 10 seconds)
stringifyObjects Stringify objects instead of converting to values. See issue #501. (Default: 'false')
insecureAuth Allow connecting to MySQL instances that ask for the old (insecure) authentication method. (Default: false)
typeCast Determines if column values should be converted to native JavaScript types. (Default: true)
queryFormat A custom query format function. See Custom format.
supportBigNumbers When dealing with big numbers (BIGINT and DECIMAL columns) in the database, you should enable this option (Default: false).
bigNumberStrings Enabling both supportBigNumbers and bigNumberStrings forces big numbers (BIGINT and DECIMAL columns) to be always returned as JavaScript String objects (Default: false).
Enabling supportBigNumbers but leaving bigNumberStrings disabled will return big numbers as String objects only when they cannot be accurately represented with JavaScript Number objects (which happens when they exceed the [-2^53, +2^53] range), otherwise they will be returned as Number objects. This option is ignored if supportBigNumbers is disabled.
dateStrings Force date types (TIMESTAMP, DATETIME, DATE) to be returned as strings rather then inflated into JavaScript Date objects. (Default: false)
debug Prints protocol details to stdout. (Default: false)
trace Generates stack traces on Error to include call site of library entrance ("long stack traces"). Slight performance penalty for most calls. Default is true.
multipleStatements Allow multiple mysql statements per query. Be careful with this, it exposes you to SQL injection attacks. (Default: false)
flags List of connection flags to use other than the default ones. It is also possible to blacklist default ones. For more information, check Connection Flags.
ssl object with ssl parameters or a string containing name of ssl profile. See SSL options.

Note: The query values are first attempted to be parsed as JSON, and if that fails assumed to be plaintext strings.

Terminating connection

There are two ways to end a connection :

  • end() method
  • destroy() method

You can terminate a connection 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.

destroy() method :

This method terminates a connection immediately. 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.

Switching users / altering connection state :

MySQL offers a changeUser command that allows you to alter the current user and other aspects of the connection without shutting down the underlying socket:

connection.changeUser({user : 'user2'}, function(err) {
  if (err) throw err;
});

The available options:

Name Description
user The name of the new user (defaults to the previous one).
password The password of the new user (defaults to the previous one).
charset The new charset (defaults to the previous one).
database The new database (defaults to the previous one).

A sometimes useful side effect of this functionality is that this function also resets any connection state (variables, transactions, etc.).

Errors encountered during this operation are treated as fatal connection errors by this module.

Error handling

There are several options to handle error within this module :

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

  • err.code :
    • A MySQL server error (e.g. 'ER_ACCESS_DENIED_ERROR')
    • A node.js error (e.g. 'ECONNREFUSED')
    • An internal error (e.g. 'PROTOCOL_CONNECTION_LOST')
  • err.fatal : Boolean, indicating if this error is terminal to the connection object.

Fatal errors are propagated (to cause to multiply by any process ) to all pending callbacks. In the example below, a fatal error is triggered by trying to an invalid user name. Therefore in the following example error object is propagated to both pending callbacks :

var mysql      = require('mysql');
var connection = mysql.createConnection({
  host     : 'localhost',
  user     : 'roott',
  password : 'datasoft123',
});
connection.connect(function(err) {
  console.log(err.code);  
  console.log(err.fatal);  
});

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

Output :

ER_ACCESS_DENIED_ERROR
true
ER_ACCESS_DENIED_ERROR
true

In this example, a fatal error is triggered by an invalid user.

var mysql      = require('mysql');
var connection = mysql.createConnection({
  host     : 'localhost',
  user     : 'roott',
  password : 'datasoft123'
});
connection.query('SELECT 1', function(err){ 
if (err){ 
 console.log(err.code);  
 console.log(err.fatal);  
 }
});
connection.end();

Output :

E:\nodejs>node test.js
ER_ACCESS_DENIED_ERROR
true

Normal errors are delegated only to the callback they belong to. In the following example, only the first callback receives an error (wrong db name), the second query works as expected :

var mysql      = require('mysql');
var connection = mysql.createConnection({
  host     : 'localhost',
  user     : 'root',
  password : 'datasoft123',

});
//Wrong database name
connection.query('USE kkk', function(err, rows) {
  console.log(err.code); // 'ER_BAD_DB_ERROR'
});
connection.query('SELECT 1', function(err, rows) {
  // null
  console.log(err); 
  // length of the row 1
  console.log(rows.length); 
});
Server disconnects :

You may lose the connection to a MySQL server due to network problems, the server timing you out, the server being restarted, or 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.

Re-connecting a connection is done by establishing a new connection. Once terminated, an existing connection object cannot be re-connected by design.

With Pool, disconnected connections will be removed from the pool freeing up space for a new connection to be created on the next getConnection call.

SSL options

The ssl option in the connection options takes a string or an object. When given a string, it uses one of the predefined SSL profiles included. The following profiles are included:

  • "Amazon RDS": this profile is for connecting to an Amazon RDS server and contains the ca from https://rds.amazonaws.com/doc/rds-ssl-ca-cert.pem

When connecting to other servers, you will need to provide an object of options, in the same format as crypto.createCredentials. Please note the arguments expect a string of the certificate, not a file name to the certificate. Here is a simple example:

var connection = mysql.createConnection({
  host : 'localhost',
  ssl  : {
    ca : fs.readFileSync(__dirname + '/mysql-ca.crt')
  }
});

You can also connect to a MySQL server without properly providing the appropriate CA to trust. You should not do this.

var connection = mysql.createConnection({
  host : 'localhost',
  ssl  : {
    // DO NOT DO THIS
    // set up your ca correctly to trust the connection
    rejectUnauthorized: false
  }
});
var connection = mysql.createConnection({
  host : 'localhost',
  ssl  : {
    // DO NOT DO THIS
    // set up your ca correctly to trust the connection
    rejectUnauthorized: false
  }
});

Pooling connections

A connection pool is a cache of database connections maintained so that the connections can be reused when future requests to the database are required. Connection pools are used to enhance the performance of executing commands on a database.

Use pool directly.

var mysql = require('mysql');
var pool  = mysql.createPool({
  connectionLimit : 10,
  host            : 'localhost',
  user            : 'root',
  password        : 'datasoft123'
});

pool.query('SELECT 1 + 1 AS solution', function(err, rows, fields) {
  if (err) throw err;
  console.log('The solution is: ', rows[0].solution);
});

When you are done with a connection, just call connection.release() and the connection will return to the pool, ready to be used again by someone else.

var mysql = require('mysql');
var pool  = mysql.createPool({
  connectionLimit : 10,
  host            : 'localhost',
  user            : 'root',
  password        : 'datasoft123',
  database        : 'hr'
});

pool.getConnection(function(err, connection) {
 // Use the connection
  connection.query( 'SELECT * from employees', function(err, rows) {
 // And done with the connection.
    console.log(rows[0]);
    connection.release();
 // Don't use the connection here, it has been returned to the pool.
  });
});

If you would like to close the connection and remove it from the pool, use connection.destroy() instead. The pool will create a new connection the next time one is needed.

Closing all the connections in a pool

When you are done using the pool, you have to end all the connections or the Node.js event loop will stay active until the connections are closed by the MySQL server. The end method takes an optional callback that you can use to know once all the connections have ended. The connections end gracefully, so all pending queries will still complete and the time to end the pool will vary. Once pool.end() has been called, pool.getConnection and other operations can no longer be performed

Pool options

Pool supports all the options of normal connection. In addition here are some extra options :

Name Description
acquireTimeout The milliseconds before a timeout occurs during the connection acquisition. This is slightly different from connectTimeout, because acquiring a pool connection does not always involve making a connection. (Default: 10 seconds)
waitForConnections Determines the pool's action when no connections are available and the limit has been reached. If true, the pool will queue the connection request and call it when one becomes available. If false, the pool will immediately call back with an error. (Default: true)
connectionLimit The maximum number of connections to create at once. (Default: 10)
queueLimit The maximum number of connection requests the pool will queue before returning an error from getConnection. If set to 0, there is no limit to the number of queued connection requests. (Default: 0)

Pool events

connection :

The pool will emit a connection event when a new connection is made within the pool. If you need to set session variables on the connection before it gets used, you can listen to the connection event.

pool.on('connection', function (connection) {
  connection.query('SET SESSION auto_increment_increment=1')
});

enqueue :

The pool will emit an enqueue event when a callback has been queued to wait for an available connection.

pool.on('enqueue', function () {
  console.log('Waiting for available connection slot');
});

Escaping query values

SQL injection is a technique (like other web attack mechanisms) to attack data driven applications. This attack can bypass a firewall and can affect a fully patched system. The attacker takes the advantage of poorly filtered or not correctly escaped characters embedded in SQL statements into parsing variable data from user input. The attacker inject arbitrary data, most often a database query, into a string that’s eventually executed by the database through a web application (e.g. a login form).

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() or pool.escape() methods :

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) {
  // ...
});

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'
  • Nested arrays are turned into grouped lists (for bulk inserts), e.g. [['a', 'b'], ['c', 'd']] turns into ('a', 'b'), ('c', 'd')
  • 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.

Here is an example on INSERT INTO statement :

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'

You can also use the escaping function directly, see the following example :

var query = "SELECT * FROM posts WHERE title=" + mysql.escape("Hello MySQL");

console.log(query); // SELECT * FROM posts WHERE title='Hello MySQL'

Escaping query identifiers

As SQL identifier (database / table / column name) is provided by a user, you should escape it with mysql.escapeId(identifier), connection.escapeId(identifier) or pool.escapeId(identifier) like this :

var sorter = 'date';
var sql    = 'SELECT * FROM posts ORDER BY ' + connection.escapeId(sorter);
connection.query(sql, function(err, results) {
  // ...
});

It also supports adding qualified identifiers. It will escape both parts.

var sorter = 'date';
var sql    = 'SELECT * FROM posts ORDER BY ' + connection.escapeId('posts.' + sorter);
connection.query(sql, function(err, results) {
  // ...
});

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

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

console.log(query.sql); // SELECT `username`, `email` FROM `users` WHERE id = 1

Note : The last character sequence is experimental and syntax might change.

When you pass an Object to .escape() or .query(), .escapeId() is used to avoid SQL injection in object keys.

Preparing Queries

MySQL 5.6 provides support for server-side prepared statements. This support takes advantage of the efficient client/server binary protocol available since MySQL 4.1. You can use mysql.format to prepare a query with multiple insertion points, utilizing the proper escaping for ids and values. A simple example of this follows:

var sql = "SELECT * FROM ?? WHERE ?? = ?";
var inserts = ['users', 'id', userId];
sql = mysql.format(sql, inserts);

Following this you then have a valid, escaped query that you can then send to the database safely. This is useful if you are looking to prepare the query before actually sending it to the database. As mysql.format is exposed from SqlString.format you also have the option (but are not required) to pass in stringifyObject and timezone, allowing you provide a custom means of turning objects into strings, as well as a location-specific/timezone-aware Date.

Custom format :

If you prefer to have another type of query escape format, there's a connection configuration option you can use to define a custom format function. You can access the connection object if you want to use the built-in .escape() or any other connection function.

Here's an example of how to implement another format:

connection.config.queryFormat = function (query, values) {
  if (!values) return query;
  return query.replace(/\:(\w+)/g, function (txt, key) {
    if (values.hasOwnProperty(key)) {
      return this.escape(values[key]);
    }
    return txt;
  }.bind(this));
};

connection.query("UPDATE posts SET title = :title", { 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);
});

When dealing with big numbers (above JavaScript Number precision limit), you should consider enabling support BigNumbers option to be able to read the insert id as a string, otherwise it will throw.

This option is also required when fetching big numbers from the database, otherwise you will get values rounded to hundreds or thousands due to the precision limit.

Getting the number of affected rows.

You can get the number of affected rows from an insert, update or delete statement.

"changedRows" differs from "affectedRows" in that it does not count updated rows whose values were not changed.

connection.query('DELETE FROM posts WHERE title = "wrong"', function (err, result) {
  if (err) throw err;

  console.log('deleted ' + result.affectedRows + ' rows');
})
Getting the connection ID

You can get the MySQL connection ID ("thread ID") of a given connection using the threadId property.

connection.connect(function(err) {
  if (err) throw err;
  console.log('connected as id ' + connection.threadId);
});
Executing queries in parallel :

The MySQL protocol is sequential, this means that you need multiple connections to execute queries in parallel. You can use a Pool to manage connections, 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 underlying 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 contributions 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. Here is the original procedure code :

DELIMITER $$ 
CREATE PROCEDURE my_procedure_User_Variables() 
BEGIN 
SET @x = 15; 
SET @y = 10; 
SELECT @x, @y, @[email protected]; 
END$$ 

Output when it was executed in MySQL :

mysql> CALL my_procedure_User_Variables() ; 
+------+------+-------+ 
| @x   | @y   | @[email protected] | 
+------+------+-------+ 
| 15   | 10   | 5     | 
+------+------+-------+ 
1 row in set (0.04 sec) Query OK, 0 rows affected (0.05 sec) 

Now call the stored procedure from node.js :

var mysql      = require('mysql');
var connection = mysql.createConnection({
  host     : 'localhost',
  user     : 'root',
  password : 'datasoft123',
  database : 'hr'
});

connection.connect();

connection.query("CALL my_procedure_User_Variables();", function(err, rows)
{
  if (err) throw err;

  console.log(rows);
});

connection.end();

Output :

[ [ { [email protected]': 15, [email protected]': 10, '@x-@y': 5 } ],
  { fieldCount: 0,
    affectedRows: 0,
    insertId: 0,
    serverStatus: 2,
    warningCount: 0,
    message: '',
    protocol41: true,
    changedRows: 0 } ]

Joins

You can call JOINS from your queries as with any other mysql driver. Here is an example :

Write a query to display the department ID, department name and manager first name. See 'hr' database.

var mysql      = require('mysql');
var connection = mysql.createConnection({
  host     : 'localhost',
  user     : 'root',
  password : 'datasoft123',
  database : 'hr'
});

connection.connect();

connection.query("SELECT d.department_id, d.department_name, e.manager_id,
 e.first_name FROM departments d INNER JOIN employees e ON 
 (d.manager_id = e.employee_id);", function(err, rows)
{
  if (err) throw err;

  console.log(rows);
});

connection.end();

Output :

[ { department_id: 10,
    department_name: 'Administration',
    manager_id: 101,
    first_name: 'Jennifer' },
  { department_id: 20,
    department_name: 'Marketing',
    manager_id: 100,
    first_name: 'Michael' },
  { department_id: 30,
    department_name: 'Purchasing',
    manager_id: 100,
    first_name: 'Den' },
  { department_id: 40,
    department_name: 'Human Resources',
    manager_id: 101,
    first_name: 'Susan' },
  ------------------------------------
  ------------------------------------

Transactions

MySQL (here we maintain version 5.6) supports local transactions (within a given client session) through statements such as SET autocommit, START TRANSACTION, COMMIT, and ROLLBACK. Here is the syntax of START TRANSACTION, COMMIT, and ROLLBACK :

START TRANSACTION 
   transaction_characteristic [, transaction_characteristic] ...] 
transaction_characteristic: 
WITH CONSISTENT SNAPSHOT 
| READ WRITE 
| READ ONLY 

BEGIN [WORK] 
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE] 
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE] 
SET autocommit = {0 | 1}

In node.js simple transaction support is available at the connection level :

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

    var log = 'Post ' + result.insertId + ' added';

    connection.query('INSERT INTO log SET data=?', log, function(err, result) {
      if (err) { 
        connection.rollback(function() {
          throw err;
        });
      }  
      connection.commit(function(err) {
        if (err) { 
          connection.rollback(function() {
            throw err;
          });
        }
        console.log('success!');
      });
    });
  });
});

Please note that beginTransaction(), commit() and rollback() are simply convenience functions that execute the START TRANSACTION, COMMIT, and ROLLBACK commands respectively. It is important to understand that many commands in MySQL can cause an implicit commit, as described in the MySQL documentation

Timeouts

Every operation takes an optional inactivity timeout option. This allows you to specify appropriate timeouts for operations. It is important to note that these timeouts are not part of the MySQL protocol, and rather timeout operations through the client. This means that when a timeout is reached, the connection it occurred on will be destroyed and no further operations can be performed.

// Kill query after 60s
connection.query({sql: 'SELECT COUNT(*) AS count FROM big_table', timeout: 60000}, function (err, rows) {
  if (err && err.code === 'PROTOCOL_SEQUENCE_TIMEOUT') {
    throw new Error('too long to count table rows!');
  }

  if (err) {
    throw err;
  }

  console.log(rows[0].count + ' rows');
});

Structure of 'hr' database :

hr database



Join our Question Answer community to learn and share your programming knowledge.