How to connect to a MySQL database with Node.js

Almost every popular programming language, like PHP, Java, C# etc provides drivers for connections with a MySql database. As MySQL is one of the most popular open source database in world and efficient as well, there's need to be support for it in Node.js  and thanks to this module, you'll be able to access from Node.js without any kind of problem. We are talking about the mysql module, a node.js driver for mysql. It is written in JavaScript, does not require compiling, and is 100% MIT licensed.

In this article you'll learn how to connect to any database in your MySQL server easily with Node.js in a couple of minutes.

Requirements

To interoperate with MySQL (in our case, we are using Xampp which includes PHPMyAdmin) using Node.js, you'll need the following node package named mysql.

You can add this package to your project executing the following command in the Node.js command prompt:

npm install mysql

Then you'll be able to require the mysql module.

Implementation

There are 2 ways to access a MySQL database, you can choose the simple connection to access quickly when you only have 1 database. You should use the pooled connection to ease sharing a single connection, or managing multiple connections.

Simple connection

The following example expose a connection with its most basic components. Require the mysql service, add the credentials as first parameter in the createConnection method, proceed to connect to the database, perform queries and finally close the connection.

You can read all the available options for a connection object in the official documentation here.

// Get the mysql service
var mysql = require('mysql');

// Add the credentials to access your database
var connection = mysql.createConnection({
    host     : 'localhost',
    user     : '<USERNAME that tipically is root>',
    password : '<PASSWORD or just use null if youre working lcocally',
    database : '<DATABASE-NAME>'
});

// connect to mysql
connection.connect(function(err) {
    // in case of error
    if(err){
        console.log(err.code);
        console.log(err.fatal);
    }
});

// Perform a query
$query = 'SELECT * from MyTable LIMIT 10';

connection.query($query, function(err, rows, fields) {
    if(err){
        console.log("An error ocurred performing the query.");
        return;
    }

    console.log("Query succesfully executed: ", rows);
});

// Close the connection
connection.end(function(){
    // The connection has been closed
});

Pooled connection

var mysql = require('mysql');

var pool  = mysql.createPool({
    host     : 'example.org',
    user     : 'bob',
    password : 'secret',
    database : 'my_dbName'
});

pool.getConnection(function(err, connection) {
    // Use the connection
    connection.query( 'SELECT something FROM sometable', function(err, rows) {
        // And done with the connection.
        connection.release();
        
        // Don't use the connection here, it has been returned to the pool.
    });
});

// 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) {
    console.log("Connected");
    // Set a session variable
    //connection.query('SET SESSION auto_increment_increment=1')
});

// <<< CLOSE THE CONNECTION USING pool.end >>>
// 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. 
// This is typically done if the pool is used in a script or when trying to gracefully shutdown a server.
// To end all the connections in the pool, use the end method on the pool:

pool.end(function (err) {
    // all connections in the pool have ended
});

Tips

You can use wildcards to create easily readable and maintainable code:

connection.query('SELECT * FROM `books` WHERE `author` = ?', ['David'], function (error, results, fields) {
     // error will be an Error if one occurred during the query
     // results will contain the results of the query
     // fields will contain information about the returned results fields (if any)
});

In addition to passing these options as an object, you can also use a url string for the connection. For example:

var connection = mysql.createConnection('mysql://user:pass@host/db?debug=true&charset=BIG5_CHINESE_CI&timezone=-0700');

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 mysql.escape(), 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) {
    // Results ...
});

Have fun !