Create APIs with Express & Mysql database

 In the previous tutorial, your learnt to create models and migrations to generate schemas in Mysql database and insert sample data using Sequelize. Now move on creating APIs in Express to access data from Mysql database.

In the root folder of the Express project (prod_product), create routes folder and add 

product.routes.js file. 

const db = require("../db/models");
const router = require("express").Router();
const {Op } = require("sequelize");

router.get("/products", function(req,res){
  // get search query
  const q = req.query.search; 
  // define matching conditions
  const condition1 = q ? { title: { [Op.like]: `%${q}%` } } : null;
  const condition2 = q ? { description: { [Op.like]: `%${q}%` } } : null;
  const condition=condition1 && condition2? {[Op.or]:[
    condition1,
    condition2,
  ]}:null;

  // offset and limit - paging 
  const sta=parseInt(req.query.start,10);
  const lim=parseInt(req.query.step,10);
  
  db.Product.findAndCountAll(
    { 
      distinct: true,
      col: 'id', // count column
      include: [
        { model: db.Image, as: 'Images'} // also include relation table
      ],
      where: condition,
      offset: sta,
      limit: lim,
    
    },
    
    
    )
    .then(data => { // return success result
     
      res.status(200).send({message:'success', numrow:data.count, data:data.rows});
 
    })
    .catch(err => {
      res.status(500).send({
        message:
          err.message || "Some error occurred while retrieving products"
      });
    });
});
module.exports = router;

We define one route (products) using get method to fetch records from the Products table. Sequelize provides the FindAndCountAll() method. The function returns all rows that match the conditions defined in where clause. Its also counts the matched rows by ignoring offset and limit.

To include the data from relation table, you need to specify "include" parameter to the function. In the code above, we include data from Images table in products query result. 

The products route allows passing in three query parameters: search, start, and step. The search string allows a client app to filter products by title or description. The start, step, and count will be used in paging functionality.

To allow access to the products route, you need to register it in app.js file as shown below:

prod_product/app.js

const express = require('express');
const app = express();
const bodyParser = require('body-parser');
const cors = require('cors');

app.use(cors()); // allow cross domain access
app.use(bodyParser.json()); // parse request JSON objects to body.req
app.use(bodyParser.urlencoded({extended: true})); //accepts only UTF-8 encoding of the body and supports automatic inflation of gzip and deflate encodings

app.use('/api', require('./routes/product.routes')); // register products route

var port = process.env.PORT || 5000; // server running port
app.listen(port, "0.0.0.0", function() { // listen from any host (0.0.0.0)
  console.log("Listening on Port 5000");
});

console.log('Web Server is listening at port '+ (process.env.port || 5000));

Start Mysql server. Then, from the root folder, execute the following command to start Express server:

node app.js

After the server successfully starts, access the products route as below:

http://localhost:5000/api/products?start=0&step=5
Output:

To filter products by title or description, simply add search string to the request url. For example, to fetch products that contain "Metal Cap", append &search=Metal Cap to the url:

http://localhost:5000/api/_products?start=0&step=5&search=Metal Cap