Protect APIs & Data filtering & paging in NextJs & MYSQL

In the earlier tutorial, you learnt to authentication with JWT by allowing a visitor to create an user account and login to the web app. Now, i am going to show how to protect APIs using a middleware and do data filtering and paging in NextJs with MYSQL database.

In the middleware folder, create auth.js file. The auth.js is a middleware. In the middleware, we specify routes to be protected, extract token from request header and verify the token using verifyToken function defined in middleare/utils.js file.

middleware/auth.js
import nextConnect from 'next-connect';
import { verifyToken } from './utils';

const middleware = nextConnect();

/* Set restricted routes or apis */
const restricted = ['/api/user/[slug]','/api/user/list'];


export default middleware.use(async (req, res, next) => {
  let authHeader = req.headers.authorization || '';
  let user = {};

  if (!restricted.includes(req.url) && !authHeader) {
    return next();
  }
  if (authHeader) {
    let token = authHeader.split(' ')[1];
    if (token) {
      user = verifyToken(token);
      if (user) {
          req.user = user;
      } else {
        res.statusCode = 401;
        return res.send({
          status: 'error',
          error: 'Expired',
        });
      }
    } else {
      res.statusCode = 401;
      return res.send({
        status: 'error',
        error: 'Wrong Token',
      });
    }
  } else {
    res.statusCode = 401;
    return res.send({
      status: 'error',
      error: 'Unauthorized',
    });
  }
  return next();
});

In the api/user folder, create [slug].js. A page that begins with [ and ends with ] is a dynamic route in NextJs. The dynamic route api/user/[slug].js can match api/user/1, api/user/list, api/user/add, or api/user/update, etc. To use the middleware to protect routes, call use(middleware) on next-connect. The token will be extracted from the request and verified. If the verification is successful, the next handler methods will be called.
To do pagination, we pass offset and limit parameters to findAndCountAll() function. The findAndCountAll() function provided by Sequelize allows you to find all rows that match a search query within a specified start (offset) and limit. It also returns the number of matched rows with ignoring the start and limit. So it is very useful for pagination.

api/user/[slug].js
import nextConnect from 'next-connect';
const db = require('../../../db/models/index');
import middleware from '../../../middleware/auth';
const {Op } = require("sequelize");

  
const handler = nextConnect()
.use(middleware) // verify token if successful, call next method (get)
.get(async (req, res) => {
    const { slug } = req.query;

  if(slug==='list') {

    let uname = req.query.name;
    let email = req.query.email;
    let sta=parseInt(req.query.start,10);
    let lim=parseInt(req.query.limit,10);
    let fromid=0;
    let toid=0;

    if(req.query.fromid && req.query.fromid!=='undefined'){
      fromid=parseInt(req.query.fromid,10);
    }
    if(req.query.toid && req.query.toid!=='undefined'){
      toid=parseInt(req.query.toid,10);
    }

    

    let orderby =[
        ['id', 'DESC'],
    ];

    //console.log(req.query);


    let condition1 = uname && uname!=='undefined' ? { username: { [Op.like]: `%${uname}%` } } : null;
    let condition2 = email && email!=='undefined' ? { email: { [Op.like]: `%${email}%` } } : null;
    let condition3 = fromid!==0 ? { id: { [Op.gte]: fromid } } : null;
    let condition4 = toid!==0 ? { id: { [Op.lte]: toid } } : null;
   
    db.User.findAndCountAll(
      {
      where:{
       [Op.and]:[ 
        condition1,
        condition2,
        condition3,
        condition4,
       ],
      

      },  
      offset: sta,
      limit: lim,
      order: orderby,
      attributes: ['id','username','email','photo']
    })
    .then(data => {
      /* send the number of all matched records (data.count) and
        a proportion (data.rows) of all matched records to client
       */
      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 data."
      });
    });
  }
  
  else{
    res.status(500).send({
      message:
         "Error"
    });
  }
  });


  export default handler;

Save the project. If you try to access http://localhost:3000/api/user/list from browser without login, you get "unauthorized" message as below:
{"status":"error","error":"Unauthorized"}

Now let create UI to allow an authorized user to display and filter data from users table. 
In the pages/user folder, create list.js file.

import React,{ useState, useEffect } from "react";
import axios from "axios";
import { FaTrash } from "react-icons/fa"
import { FaEdit } from "react-icons/fa"
import { BsFilterCircle,BsFileEarmarkExcel,BsPlus } from "react-icons/bs"
import Layout from "../../components/layout";
import { useRouter } from 'next/router';
import * as FileSaver from "file-saver";
import * as XLSX from "xlsx";
import { getAppCookies } from "../../middleware/utils";

import {
   FormGroup,Input,
    Button,Col,Table,
    Popover, PopoverHeader, PopoverBody,
  } from "reactstrap";
  

var start=0;
var step=4;

function UserList(props) {
    // state vairables/
    const { origin, referer, baseApiUrl,token } = props;
    const [users,setUsers]=useState(null);
    const [popover,setsetPopover]=useState(false);
    const [inputs, setInputs] = useState({});
    const [sorted,setSorted]=useState(false);
    const [numRows,setNumRows]=useState(0);
    const [refresh,setRefresh]=useState(false);
    const hstyle={textDecoration: 'none'};
    const router=useRouter();
    
  
    const pstyle={
      marginRight: '20px',
      textDecoration: 'none',
     };
  
    useEffect(() => {
      axios
      .get(`${baseApiUrl}/user/list?name=${inputs.name}&email=${inputs.email_addr}&fromid=${inputs.fromid}&toid=${inputs.toid}&start=${start}&limit=${step}`,{
        headers:{
            "Content-type":"application/json",
            "authorization": `Bearer ${token}`,
        },

       
      })
      .then((res) => {
        parseResult(res.data);
      
      }) 
      .catch((err) => console.log(err));
     }, [refresh]);


    const searchUser= () =>{

            start=0; // reset start
            axios({
              method: 'get',
              url: `/api/user/list?name=${inputs.name}&email=${inputs.email_addr}&fromid=${inputs.fromid}&toid=${inputs.toid}&start=${start}&limit=${step}`,
              headers: { "Content-type":"application/json","authorization": `Bearer ${token}`}
            }).then(res=>{
              parseResult(res.data);
              
            });
            
        
      };
    

    async function handleSubmit(e) {
     
      };
   
    const deleteUser = (id) => {
      
      }; 

    const editUser = (item) => {
      
      };     
    

  function togglePop() {
    setsetPopover(!popover);
    
  }

 
  const handleChange = (event) =>{
    const name = event.target.name;
    const value = event.target.value;
    setInputs(values => ({...values, [name]: value}))
  };

  const handlePaging = (mv) =>{
      if(mv==='next') {
        if(start<numRows-step) start=start+step;
      }
      else if(mv==='prev') {
        if(start>=step) start=start-step;
      
      }
       
       axios({
        method: 'get',
        url: `/api/user/list?name=${inputs.name}&email=${inputs.email_addr}&fromid=${inputs.fromid}&toid=${inputs.toid}&start=${start}&limit=${step}`,
        headers: { "Content-type":"application/json","authorization": `Bearer ${token}`}
      }).then(res=>{
        parseResult(res.data);
        
      });

  }
  const handleHeaderClick = (field) =>{
    // sort records
    if(field!=='id') users.sort((a,b) => a[field].localeCompare(b[field]));
    else users.sort((a,b) => b[field]-a[field]);
    // refresh UI
    setSorted(!sorted);
    
      
  }
  const parseResult = (data) =>{
      let dt=JSON.parse(JSON.stringify(data));
      if(dt.message==='success'){
        setUsers(dt.data);
        setNumRows(dt.numrow);
      }
  }

   // export to excel
   const fileType =
   "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8";
 const fileExtension = ".xlsx";

 const exportToCSV = (apiData, fileName) => {
   const ws = XLSX.utils.json_to_sheet(apiData);
   const wb = { Sheets: { data: ws }, SheetNames: ["data"] };
   const excelBuffer = XLSX.write(wb, { bookType: "xlsx", type: "array" });
   const data = new Blob([excelBuffer], { type: fileType });
   FileSaver.saveAs(data, fileName + fileExtension);
 };


    return (
     
      <Layout
      url={`${origin}${router.asPath}`}
      origin={origin}
      token={token}
    >
  
    
    <div className="container">
      <div style={{float: 'right'}}>
        <Button color="primary" id="btfilter"><BsFilterCircle/></Button>
      </div>
      <Popover placement="top" isOpen={popover} target="btfilter" toggle={togglePop}>
          <PopoverHeader>Filter</PopoverHeader>
          <PopoverBody>
          <FormGroup row>
         
          <Col sm={10}>
            <Input type="text" value={inputs.name || ""} name="name" id="name" placeholder="By User name" bsSize="lg" onChange={handleChange}/>
          </Col>
        </FormGroup>
        <FormGroup row>
         
          <Col sm={10}>
            <Input type="text" value={inputs.email_addr || ""} name="email_addr" id="email_addr" placeholder="By Email"  onChange={(e) =>handleChange(e)} />
          </Col>
        </FormGroup>

        <FormGroup row>
        <Col sm={6}>
           <Input type="number"  value={inputs.fromid || ""}  name="fromid" id="fromid" placeholder="From ID"  onChange={(e) =>handleChange(e)} />
         </Col>
         <Col sm={6}>
           <Input type="number"  value={inputs.toid || ""}  name="toid" id="toid" placeholder="To ID"  onChange={(e) =>handleChange(e)} />
         </Col>
       </FormGroup>

        <Button color="primary" onClick={(e) =>searchUser()}>Ok</Button>
          </PopoverBody>
      </Popover>
      
      <div style={{float: 'right',marginRight: '10px'}}>
        <Button color="primary"  onClick={(e) => exportToCSV(users, 'users')}><BsFileEarmarkExcel/></Button>
      </div>
      <div>Users List ({numRows} items)</div> 
      
      {
        
      <Table striped bordered hover>
      <thead> 
      <tr>
        <th>
        <a style={hstyle} href="#" onClick={() => handleHeaderClick("id")}>ID</a></th>
        <th >
        <a style={hstyle} href="#" onClick={() => handleHeaderClick("username")}>User name</a></th>
        <th><a style={hstyle} href="#" onClick={() => handleHeaderClick("email")}>Email</a></th>
        <th colSpan={3}></th>
        </tr>
      </thead>
      <tbody>
      {
       users && users.map((item, i) => {
        return (
            <tr key={item.id}>
            <td >{item.id}</td>
            <td >{item.username}</td>
            <td >{item.email}</td>
            <td><img style={{width: '50px',height:'50px'}} src={"/"+item.photo}/></td>
            <td > <Button color="primary"  onClick={() =>editUser(item)}>
            <FaEdit style={{ color: "white", fontSize: "12px" }} />

            </Button></td>
            <td > <Button color="primary" onClick={() =>deleteUser(item.id)}>
            <FaTrash style={{ color: "red", fontSize: "12px" }} />

            </Button></td>
            </tr>
        );	

         })
      }
      </tbody>
    </Table> 
    }
    {

      (start-step>=0 && numRows>step) && <a href="#" onClick={() =>handlePaging('prev')} style={pstyle} to={"#"}>{"<"}</a>
      
      }  
    {

     (numRows-start>=step  && numRows>step) && <a href="#" abc={numRows} onClick={() =>handlePaging('next')}   style={pstyle} to={"#"}>{">"}</a>  
    }
    
      
    </div>
    </Layout>
  );
}
/* getServerSideProps */
export async function getServerSideProps(context) {
  const { req } = context;

  const token = getAppCookies(req).token || '';
  return {
    props: {
      token,
    },
  };
}

export default UserList;

We are using xlsx and file-saver packages to generate Excel report file and download the file. Thus, install the packages into the Nextjs app.
npm install xlsx file-saver

Save the project. Access http//localhost:3000. Log into the app. Then from the left menu, Users -> User List.
Currently, you can click column headers to sort user list by id, username, or email, export the list to Excel file, filter the list by username, email, or range of ids, and paging.



The next tutorial, you will learn to submit form data to add new user with profile image, update, and delete an existing user from the MYSQL database.



Comments

Post a Comment

Popular posts from this blog

Upload form data with file using Multer & MYSQL in NextJs

Image
The previous tutorial taught you how to create API endpoints to filter MYSQL database, pagination, and protect the API using middleware. This tutorial teaches you how to upload user form data with image file using Multer and delete data from MYSQL database. From the project folder, execute the following command to install Multer: npm install multer Then open api/user/[slug].js file to add post, put, and delete routes. To use multer to parse request body and upload files, you need to disable default body parser by setting bodyParser to false. We use diskStorage() function from multer to configure local storage and file name. Multer needs file field and max count of the file to be uploaded. In this project, we use array() function to specify file field (photo) and the max count of the file to be uploaded (1).  We can access text parts of a submitted form data by extracting body from the request. To access file parts, you need to extract files from the request. ..... import bcrypt
Read more

Migration using Sequelize with MYSQL database in NextJs

Sequelize is a Node based - Object Relational Mapper. It makes you easy to work with a database engine such as MYSQL, PostgreSQL, MongoDB. etc. You can create data models in NextJs and let Sequelize synchronizes them with MYSQL database.  From the models, you are able to retrieve, insert, update, and delete data in the database. To Work with MYSQL database using Sequelize, you have to install the following dependencies: sequelize, sequelize-cli, and mysql2. From the project folder (nestjs-app), execute the following command: npm install sequelize sequelize-cli mysql2 sequelize helps us synchronize  models with Mysql database.  sequelize-cli  is the Sequelize Command Line Interface helps us create models, migrations, and database. mysql2   is a fast mysql driver to work with mysql database from NestJs. In the project folder, create and change to db folder. From the db folder, run the following command to initialize sequelize: npx sequelize-cli init The command creates config, migration
Read more