Using MySQL Database from Node.js [Guide]

MySQL is very popular among developers, and is recognized as the second most popular database, according to the DB-Engines webpage. Node.js is a server side JavaScript framework that's being widely accepted by the community, and has been getting a lot of support in the last couple of years. In many cases, Web applications need to be rewritten using new technology, but the database behind the application should not be changed or moved. So if you have an older application with data stored in MySQL and you want to rewrite the application without moving the database, then this is for you.

This article demonstrates how MySQL can be accessed and how data can be manipulated from node.js. The source code presented in the article can be accessed on GitHub in my jsExpense repository. The project is an expense tracking software that supports different currencies, allowing users to store and track family expenses.

Installation/Prerequisites

You should have access to a MySQL server where you can create a database and tables. The easiest way is to install MySQL locally, and you can find the installation steps here.

Node.js should be installed too. The installation process for Node.js and its packages can be found here. There is a MySQL driver written for Node.js that can be found on NPM (the command will install the mysql package globally):

      npm install –g mysql

Since the project is developed using Express.js, this should be installed too, and can be done using:

      npm install –g express

Creating a Project

To start the project, let's create a new Express.js application using the express command line generator:

      express jsexpense

You can read more about Express.js and the philosophy behind the framework from one of my previous articles, Express.js in Action.

To clone and run the application, use these commands:

      git clone https://github.com/gergob/jsexpense.git
      cd jsexpense
      npm install
      DEBUG=jsexpense npm start

The application should start locally running on http://localhost:3000/

Project Structure

Every Express.js application has a default structure:

express project structure

The standard express project has bin, public, routes and views folders, plus the app.js file. The bin folder has only one script file called www, which is used to start the application. The public folder contains all the JavaScript, html, CSS and image files, which can be accessed when running the Web application.

Data Models

The application is simple, so there are only three models:

  1. Currency – has name, conversionRate, forDate properties (ex: USD, 1.37, 2015. Oct 10)

  2. Member – has id, name, role properties (ex: 1, Jane Doe, Mother)

  3. Expense – has id, name, amount, units, unitPrice, currency (ex: 1, car wash, 15, 1, 15, USD)

Besides these, each of the models has an updatedOn field which stores a timestamp of the last modification date of the entry. The sql folder has the SQL code for creating the MySQL tables for these data models.

Managers

The folder managers is added by me and it holds 4 files:

Connection Manager (create a new connection)

The connectionManager.js contains the connection details for the MySQL server and has a method for creating connections. First I import he mysql package, which was installed by the npm install command that was done when setting up the project. For the moment, please ignore the q package—that is included to use promises as an implementation approach. I will cover promises in another article.

The getConnection method creates a new MySQL connection using the createConnection function from the mysql package. The parameter of the function should be a JSON object with host, port, user, password and database properties. If you're working with UNIX, there is an option to specify the socketPath for the createConnection method, but if it's already specified, the host and the port properties are ignored by the driver.

Once the connection has been created, we should try to connect. The connect method uses an argument with a function that will get an error if the connection wasn't successful. If an error is received, then the promise should be rejected, basically throwing an error up the stack to notify the invoker that there was an error with the connection. If are no errors, we can return the connection object.

The connection has a unique identifier that's stored in the threadId property of the connection. I log this property when the connection was successfully created.

var mysql = require('mysql');
var q = require('q');


function getConnection() {
    var deferred = q.defer();

    var connection = mysql.createConnection({
        host: '127.0.0.1',
        port: 3306,
        user: 'ujs',
        password: 'ujs1234',
        database: 'jsexpense'
    });

    connection.connect(function (err) {
        if (err) {
            console.error(err);
            deferred.reject(err);
        }
        console.log('[CONN] – Connection created with id:'+ connection.threadId);
        deferred.resolve(connection);
    });

    return deferred.promise;
}

function prepareQuery(query, parameters){
    if(!query || !parameters) {
        throw  new Error('query and parameters function parameters should be specified.');
    }
    return mysql.format(query, parameters);
}

module.exports = {
    getConnection : getConnection,
    prepareQuery: prepareQuery
};

The prepareQuery is a helper function, which is used for parameterized queries sent towards MySQL (more on this later). At the end of the file, I update the exports and return the two methods.

Currency Manager (select, insert, update)

The currencyManager.js contains all the data manipulation methods related to the currency data model.

The code starts with importing the connectionManager.

var connectionManager = require('./connectionManager');

function getCurrencies() {
    var deferred = q.defer();

    connectionManager.getConnection()
        .then(function (connection) {
            connection.query('SELECT * FROM currency', function (error, results) {
                if (error) {
                    console.error(error);
                    deferred.reject(error);
                }
                deferred.resolve(results);
            });
        })
        .fail(function (err) {
            console.error(JSON.stringify(err));
            deferred.reject(err);
        });

    return deferred.promise;
}

The getCurrencies method loads all the data from the currency table. Here I use the same promise structure than before. I create a new promise, then ask for a new connection from the connectionManager using the getConnection method. Once the connection is ready, I invoke the connection.query() function. The first parameter is the SQL query which will be executed by the MySQL query engine, SELECT * FROM currency, will load all the currencies stored in the database. The second parameter for the query method is a callback function, which is invoked when the database engine has executed the query. The callback function has two parameters, error and results. If there is an error, log that to the console and reject the request to pass the error up the stack. Otherwise, return the results.

The getCurrencies method is executed when the /api/currencies URL is accessed in the express application. Here you can see an example of a response:

The next method in the currencyManager.js is addCurrency.

function addCurrency(name, conversionRate, forDate) {
    var deferred = q.defer();

    var currencyInsert = 'INSERT INTO currency(name, conversionRate, forDate) VALUES (?,?,?)';
    
    connectionManager.getConnection()
        .then(function (connection) {
            var query = connectionManager.prepareQuery(currencyInsert, [name, conversionRate, forDate]);
            console.log('Query to execute:' + query);
            connection.query(query, function (error, result) {
                if (error) {
                    console.error(error);
                    deferred.reject(error);
                }
                deferred.resolve(result.insertId);
            });
        })
        .fail(function (err) {
            console.error(JSON.stringify(err));
            deferred.reject(err);
        });

    return deferred.promise;
}

This method should receive name, conversionRate and forDate parameters. These are the fields of the currency. Then define the SQL for inserting a new currency in the database. Please note the ? symbol in the currencyInsert string. The question marks will be replaced by the connectionManager’s prepareQuery method with the values of the name, conversionRate and forDate parameters. In this case, I used the same query method of the connection to insert a currency used for loading the currencies.

While the error handling and happy flow handling is the same, there's a difference. Inserting the mysql node driver can return the ID of the newly inserted item (results.insertId). The addCurrency method is invoked by the express framework when there is a POST request with form data sent to the /api/currencies url.

On the screenshot above, the currencyId is 0. That is the default value of what the framework returns, which is not correct in our case. Because the id in the case of the currency is the name field, there is no id field available in the database.

When the creation is successful, send an HTTP 201 CREATED response code to the client to confirm that the creation of the entity was successful.

The deletion of a currency is implemented in the deleteCurrency method.

function deleteCurrency(name) {
    var deferred = q.defer();

    var currencyDelete = 'DELETE FROM currency where name = ?';

    connectionManager.getConnection()
        .then(function (connection) {
            var query = connectionManager.prepareQuery(currencyDelete, [name]);
            console.log('Delete query:' + query);
            connection.query(query, function (error, result) {
                if (error) {
                    console.error(error);
                    deferred.reject(error);
                }
                deferred.resolve({affectedRows: result.affectedRows});
            });
        })
        .fail(function (err) {
            console.error(JSON.stringify(err));
            deferred.reject(err);
        });

    return deferred.promise;

}

The structure of the delete query and operation is the same as in the case of insert. In this case, I only changed the query string (DELETE FROM currency WHERE name = ?) and replaced the question mark with the name parameter given to the deleteCurrency function. In case of a delete operation, the result object has an affectedRows property that contains the number of the deleted rows. The deleteCurrency method is invoked when an HTTP DELETE request is sent to the /api/currencies/:name URL.

Besides insert, update, and delete, the mysql driver for node.js supports executing multiple queries, stored procedures, executing joins, and of course, running transactions. The MySQL Node.js driver offers a clean interface for manipulating data stored in MySQL data.

Greg Bogdan
Greg Bogdan Hire Me

Software Engineer, Blogger, Tech Enthusiast

I am a Software Engineer with over 7 years of experience in different domains(ERP, Financial Products and Alerting Systems). My main expertise is .NET, Java, Python and JavaScript. I like technical writing and have good experience in creating tutorials and how to technical articles. I am passionate about technology and I love what I do and I always intend to 100% fulfill the project which I am ...

Hire Me

Next Article

Freelancing Tips: Why You Shouldn't Be Afraid of Following Up