How To Connect To MySql Database Using Sequlize In Express (Node) Application

In the previous tutorial, we already saw how to get started with node.js. We also created basic web APIs in Node JS using express framework.

Here, we are going to see how to connect to MySQL database using Sequlize. We will create a web API for a book library. So, a user will be able to fetch, add, update, and delete books. Again, we will be using postman for testing our APIs.

This entire tutorial is available on github

Software needed-

      1. Node JS - Environment

      2. MySQL – Your database provider

      3. Postman – Used for creating http requests like GET, PUT, POST, DELETE

      4. Sublime/Notepad++ - IDE for writing code.

Install MySQL from official website according to your platform (Win/ Mac/ Linux). It is recommended that you use some Integrated Development Environment so that it easy to read and interpret code.

Throughout these courses, we will be using sublime which is a cool IDE.

Before starting tutorial, I would like you to create a user and a database in your MySQL.

We will be continuing with the previous tutorial example. It is strongly encouraged that you watch previous tutorial before continuing.

1.  MySQL Configuration – Create a config.json file in your project directory and paste the following snippet. You need to add the database name, username, and password so that express can talk to your database. 

   "dev": {
   "driver": "mysql",
   "user": "user of database",
   "database": "your database name",
   "password": "your database password"
   }

2.  Installing Dependencies - You need to install the following packages in your node.js project. Sequelize is  ORM through which you can connect and run sql queries on your MySql Database. It is actually a wrapper on mysql package. It is very easy to learn and has got excellent documentation on their website.

   Body-parser will help you to read body content of http requests. 

   npm install sequelize
   npm install body-parser
   npm install mysql

3.  Setting up- Paste the following code snippet.

   You need to create a table in MySQL. In the database, you added in config.json. Name the table as books and add 3 rows - id (datatype int), bookName (datatype varchar(25) and bookPrice (datatype int)
   
                                                

var express = require('express');
var Sequelize = require('sequelize');
var config = require('./config.json');
var bodyParser = require('body-parser');

var password = config.password ? config.password : null;

var sequelize = new Sequelize(
    config.database,
    config.user,
    config.password, {
        logging: console.log,
        define: {
            timestamps: false
        }
    }
);


var app = express();
var port = 3000;
var router = express.Router(); // will help in adding routes
app.use(bodyParser.urlencoded());
app.use(bodyParser.json());

Now, we will define a model for our book library. We will keep only 2 fields for books, i.e. bookName and bookPrice.

var bookModel = sequelize.define('books', { //define your model
"bookName": Sequelize.INTEGER,
"bookPrice": Sequelize.INTEGER
});

      4. Add GET Request-  

app.use('/api', router); //this will add routing to your api.
//your api endpoint  localhost:3000/api/books

router.get('/books', function(req, res) {
    bookModel.findAll().
    then(function(books) {
        res.status(200).json(books);
    }, function(error) {
       res.status(500).send(error);
    });
});

app.listen(port, function() {

});
console.log('my api is running on port:' + port);

       5. Start up- Start you application through command line.      

Node server.js

       6. Testing up - Go to postman and enter this URL.

http://localhost:3000/api/books

      and select GET request and click SEND.

 

In response, if you see status 200, then it shows everything is working properly. You will not see any data because we don’t have any data stored in our table.

 

       7. Add other Requests- PUT, POST, DELETE

router.get('/book/:id', function(req, res) {
    bookModel.findAll({
        where: {
            id: req.params.id
        }
    }).
    then(function(books) {
        res.status(200).json(books);
    }, function(error) {
        res.status(500).send(error);
    });
});

router.post('/book', function(req, res) {
    console.log(req.body);
    var data = {
        "bookName": req.body.bookName,
        "bookPrice": req.body.bookPrice
    };

    bookModel.create(data).
    then(function(books) {
        res.status(200).json(books);
    }, function(error) {
        res.status(500).send(error);
    });
});

router.put('/book/:id', function(req, res) {
    var data = {
        id: req.params.id,
        bookName: req.body.bookName,
        bookPrice: req.body.bookPrice
    };

    bookModel.update(data, {
        where: {
            id: data.id
        }
    }).
    then(function(book) {
        res.status(200).json(book);
    }, function(error) {
        res.status(500).send(error);
    });
});

router.delete('/book/:id', function(req, res) {
    var data = {
        id: req.params.id
    };

    bookModel.destroy({
        where: {

            id: data.id

        }
    }).
    then(function(book) {
        res.status(200).json(book);
    }, function(error) {
        res.status(500).send(error);
    });
});


app.listen(port, function() {

});
console.log('my api is running on port:' + port);

      8.  Final Testing

      We are done with creating all operation in our web API. We will be testing our API by adding book content.

 

 

      

         Here is a sample of GET request for fetching all books in our database. 

 

 

      Here is a sample of PUT request for updating a particular book.

 

 

 

      Here is a sample of Delete request for removing particular book.