MySQL Data Access API Development Using Express.JS, Node.JS

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

As we all know, Node.JS is the most trending open source technology that’s directly or indirectly partitioning into advanced web development, and let’s have lookup what is Node.JS?

Node.JS Definition

Node.JS is a platform that provides environment (outside the web browser) to execute JavaScript and which is built on Chrome’s V8 JavaScript engine developed by Ryan Dahl.

Why Node.JS?

NodeJs processing model depends on a single thread with event looping.

When clients send HTTP requests to NodeJs server, the Event loop is woken up by OS. It will pass the request and response objects as JavaScript closures to worker functions with callbacks.

For long-running jobs like I/O operations running on non-blocking worker threads, when the job is complete, responses are sent to the main thread via a callback. Event loop returns result to the client.

NodeJs has following features,

  • Consistently asynchronous
  • Event Driven, Lightweight
  • Same language on clientserver
  • NPM, Yarn (built-in support for package management)
  • Built Http/https server, sockets
  • Develop Real-time, Web, Mobile applications

Node.JS is not best platform for

  • Heavy Server-Side Computation/Processing

Node.JS System Module

  • events – To handle events
  • fs – To handle the file system
  • http – To make Node.js act as an HTTP server
  • https – To make Node.js act as an HTTPS server.
  • net – To create servers and clients
  • os – Provides information about the operation system
  • path – To handle file paths

Node.JS Installation

  • Download the Windows installer from the js® web site .
  • Run the installer (the .msi file you downloaded in the previous step.)
  • Follow the prompts in the installer (Accept the license agreement, click the NEXT button a bunch of times and accept the default installation settings).
  • To see if Node is installed, open the Windows Command Prompt, PowerShell or a similar command line tool, and type node -v.
  • To see if NPM is installed, type npm –v. (NPM is a package manager for Node.js packages or modules)

Express.JS

The express framework provides an abstraction layer above HTTP module to make handling web traffic (Request/ Response) with developing middleware and APIs. There are also tons of middleware available to complete the common task for express frameworks, such as- CORS, XSRF, POST parsing, Cookies etc.

  • JS framework is built on Node.JS HTTP module.
  • We can install Express.Js global by just type npm install -g express in command prompt.

Now, we have our development environment ready with NodeJS, Express.js installation. And, 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.

  • http://localhost:8000/api/users
  • http://localhost:8000/api/transactions/1

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 .

Please provide your feedback and let me know your thoughts on Node.JS/Express.JS.

C-Sharpcorner责编内容来自:C-Sharpcorner (源链) | 更多关于

阅读提示:酷辣虫无法对本内容的真实性提供任何保证,请自行验证并承担相关的风险与后果!
本站遵循[CC BY-NC-SA 4.0]。如您有版权、意见投诉等问题,请通过eMail联系我们处理。
酷辣虫 » 前端开发 » MySQL Data Access API Development Using Express.JS, Node.JS

喜欢 (0)or分享给?

专业 x 专注 x 聚合 x 分享 CC BY-NC-SA 4.0

使用声明 | 英豪名录