Node.js with MySQL

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();

{ 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 }

$ npm install mysql

$ npm install felixge/node-mysql

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);
});

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!');
});

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.

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

connection.destroy();

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

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).

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);  
});

ER_ACCESS_DENIED_ERROR
true
ER_ACCESS_DENIED_ERROR
true

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();

E:\nodejs>node test.js
ER_ACCESS_DENIED_ERROR
true

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); 
});

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

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
  }
});

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);
});

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

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, @x-@y; 
END$$ 

Output when it was executed in MySQL :

mysql> CALL my_procedure_User_Variables() ; 
+------+------+-------+ 
| @x   | @y   | @x-@y | 
+------+------+-------+ 
| 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 :

[ [ { '@x': 15, '@y': 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 :

Previous: OS Module
Next: Node-SQLite3