How to Build a simple MySQL data access API using ExpressJS and NodeJS

In this article, we are looking into the introduction of Node.JS and Express.JS and then, I am going to build a simple MySQL data access API using Express.js, NodeJS.

I am going to demonstrate how we can create MySQL data access API using Node.JS and Express.js.

Let’s start with creating a folder as “sample-api”.

Open a folder in command prompt and type ‘npm init’ to initiate the project.
Enter details like project name, version, description etc. then type ’Yes’.
It will create a JSON file inside sample-api folder which contains the information regarding the project and NPM package that are needed into this project. We called dependencies.
Now, we are creating a MySQL table for users and their transaction details, as below.

--  
-- Table structure for table `users`  
--  
DROP TABLE IF EXISTS `users`;  
CREATE TABLE `users` (  
  `UserID` int(11) NOT NULL AUTO_INCREMENT,  
  `Name` varchar(45) DEFAULT NULL,  
  PRIMARY KEY (`UserID`)  
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;  
  
--  
-- Table structure for table `transactions`  
--  
DROP TABLE IF EXISTS `transactions`;  
CREATE TABLE `transactions` (  
  `TransactionId` int(11) NOT NULL AUTO_INCREMENT,  
  `UserId` int(11) DEFAULT NULL,  
  `TransactionAmount` decimal(10,2) DEFAULT NULL,  
  `Balance` decimal(10,2) DEFAULT NULL,  
  `TransactionDate` date DEFAULT NULL,  
  PRIMARY KEY (`TransactionId`)  
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

Then, we are installing NPM packages for MySQL to make MySQL connection and fetch insert, update records, express to build HTTP server, maintaining server route, body-parser to parsing body into JSON format by entering this command.
We can see that the packages are added to the node_modules folder in our project folder (sample-api). And, I have created this type of folder structure for our project. There are no specific rules regarding project structure, but we are maintaining everything with the modular design to keep it separate and well-maintained.
Now, we are creating MySQLConnect module for establishing the connection with MySQL and executing the MySQL queries.
For that, we have created ‘connection/MySQLConnect.JS’ file.

// establish Mysql Connection  
var mysql = require('mysql');  
  
function MySQLConnect() {  
  
  this.pool = null;  
    
  // Init MySql Connection Pool  
  this.init = function() {  
    this.pool = mysql.createPool({  
      connectionLimit: 10,  
      host     : 'localhost',  
      user     : 'root',  
      password : 'admin@123',  
      database: 'sample-db'  
    });  
  };  
  
  // acquire connection and execute query on callbacks  
  this.acquire = function(callback) {  
  
    this.pool.getConnection(function(err, connection) {  
      callback(err, connection);  
    });  
  
  };  
  
}  
  
module.exports = new MySQLConnect();

Then, I have created ‘data_access/transaction.js’ file for acquiring the MySQL connection and return the response with data.

//methods for fetching mysql data  
var connection = require('../connection/MySQLConnect');  
  
function Transaction() {  
  
    // get all users data   
    this.getAllUsers = function (res) {  
        // initialize database connection  
        connection.init();  
        // calling acquire methods and passing callback method that will be execute query  
        // return response to server   
        connection.acquire(function (err, con) {  
            con.query('SELECT DISTINCT * FROM users', function (err, result) {  
                con.release();  
                res.send(result);  
            });  
        });  
    };  
  
    this.getTransactionById = function (id, res) {  
        // initialize database connection  
        connection.init();  
        // get id as parameter to passing into query and return filter data  
        connection.acquire(function (err, con) {  
            var query = 'SELECT date_format(t.TransactionDate,\'%d-%b-%Y\') as date, ' +  
                'CASE WHEN t.TransactionAmount >= 0 THEN t.TransactionAmount ' +  
                'ELSE 0 END AS Credit, CASE WHEN t.TransactionAmount < 0 THEN ' +  
                't.TransactionAmount ELSE 0 END AS Debit, t.Balance FROM ' +  
                'transactions t INNER JOIN users u ON t.UserId=u.UserID WHERE t.UserId = ?;';  
            con.query(query, id, function (err, result) {  
                    con.release();  
                    res.send(result);  
                });  
        });  
    };  
  
}  
  
module.exports = new Transaction();

Now, I have developed routes for returning the data based on each request. So, I have added ‘route/route.js’ file.

//custom route for fetching data  
var transactions = require('../data_access/transaction');  
  
module.exports = {  
    //set up route configuration that will be handle by express server  
    configure: function (app) {  
  
        // adding route for users, here app is express instance which provide use  
        // get method for handling get request from http server.   
        app.get('/api/users', function (req, res) {  
            transactions.getAllUsers(res);  
        });  
  
        // here we gets id from request and passing to it transaction method.  
        app.get('/api/transactions/:id/', function (req, res) {  
            transactions.getTransactionById(req.params.id, res);  
        });  
  
    }  
};

Now, we are writing the code for setting up the server using Express and here, we initialize the database connection and set route configuration with application.

/**  
 * Creating server using express.js 
 * http://localhost:8000/api/users 
 * http://localhost:8000/api/transactions/1 
*/  
var express = require('express');  
var bodyparser = require('body-parser');  
  
var routes = require('./routes/route');  
  
// creating server instance  
var app = express();  
  
// for posting nested object if we have set extended true  
app.use(bodyparser.urlencoded({ extended : true}));  
  
// parsing JSON  
app.use(bodyparser.json());  
  
//set application route with server instance  
routes.configure(app);  
  
// listening application on port 8000  
var server = app.listen(8000, function(){  
    console.log('Server Listening on port ' + server.address().port);  
});

Our API is created; we can verify the API by running the server on localhost. To run our Server, we enter ‘npm start’ command in console. The npm start command is run on the node with starting page which we have set ‘main’ (server.js) key in package.json file. The server is ready for listening on port 8000.

We are verifying our API call by requesting it from the below URL.

Conclusion

In this article, we have looked into introduction of Node.JS and Express.JS, and we have created simple MySQL data access API using Express.js.

You can find the entire code on GitHub.

Thank you for reading!

#mysql #api #node-js #express-js #data