Expressjs — Api Rest with sequelize

This article is a continuation of an article on connecting to a database through Sequelize. In this article, our goal is to create an API (Application Programming Interface) in REST (Representational State Transfer).

JSON API

Modify app.js and add

// Api rest Controllers
var apiRestRouter = require('./routes/rest/api');

To set the url/api, we also add this in the same file

app.use('/api', apiRestRouter);

Then create the routes/rest/api.js file. The contents of the file will be as follows

var express = require('express');

const db = require('../../models');

var router = express.Router();

/**
 * @GET /api/
 */
router.get('/', function(req, res, next) {
    db.api.findAll().then(apis => res.json({
        error: false,
        data: apis,
    }))
    .catch(error => res.json({
        error: true,
        data: [],
        error: error
    }));
});

module.exports = router;

Then run POSTMAN . Go to URL http://localhost:3000/api

img

Let’s add other actions: CRUD (Create, Read, Update, Delete) to our router.

Recall the data contained in our api table

img

CREATE

Let’s move on to creating an entry in this table. Let’s add the following code.

/**
 * @POST /api/
 */
router.post('/', function(req, res, next) {
    const {
        name,
        created_at,
        updated_at
    } = req.body;

    db.api.create({
        name: name,
        created_at: created_at,
        updated_at: updated_at
    })
    .then(api => res.status(201).json({
        error: false,
        data: api,
        message: 'New api created.'
    }))
    .catch(error => res.json({
        error: true,
        data: [],
        error: error
    }));
});

And in Postman, let’s call the url like this

img

A line has been added to the table

img

… but the “created_at” and “updated_at” dates saved in the table do not correspond to the data I sent that is 2018–06–18 00:00:00 but to the date of the URL call. How to force dates?

It’s simple, go back to model models/api.js and add timestamp: false

{
  tableName: 'api',
  underscored: true,
  timestamps: false
}

Test is repeated by modifying the “name” field slightly

img

Let’s observe the recorded line

img

The given date has been saved with the information we have provided;)

READ

We until now know how to retrieve all the lines. We will now retrieve a line from the table. This line for example…

img

The following code is added:

/**
 * @GET /api/1
 */
router.get('/:id', function(req, res, next) {
    const apiId = req.params.id;
    db.api.findByPk(apiId).then(api => res.json({
        error: false,
        data: api,
    }));
});

Another variant of the call with the same result: (convenient to search for a field other than “id”)

db.api.findOne({ where: { id: apiId } }).then(api => res.json({
    error: false,
    data: api,
}));

We have the following result

img

UPDATE

Let’s now change the next row’s “name” field.

img

in “Android flutter”

Modify the routes/rest/api.js file and add the following code:

router.put('/:id', function(req, res, next) {

    const apiId = req.params.id;

    const {
        name,
        created_at,
        updated_at
    } = req.body;

    db.api.update({
        name: name,
        created_at: created_at,
        updated_at: updated_at
    },{
        where: {
            id: apiId
        }
    })
    .then(api => res.status(201).json({
        error: false,
        message: 'Api updated.'
    }))
    .catch(error => res.json({
        error: true,
        error: error
    }));
});

In Postman, we have this.

img

And in the table, you can note the modified line.

img

DELETE

We want at present to delete a line.

The code is similar to UPDATE (PUT).

/**
 * @DELETE /api/1 - Delete
 */
router.delete('/:id', function(req, res, next) {
    const apiId = req.params.id;

    db.api.destroy({ where: { id: apiId } })
        .then(api => res.json({
            error: false,
            message: 'Api deleted.'
        }))
        .catch(error => res.json({
            error: true,
            error: error
        }));
});

In postman

img

Voilà. We’ve gone over all the methods.

GET with complex query

In the concrete life of an application, it is rare to want to display a simple list of a table in a database.

Imagine if you wanted to have a page with lines containing the name “desk” or retrieve only 2 lines per page or a list sorted by date, or combine several conditions…

To do this, it would be unproductive to create a “query” function. We will manage to set all of this on the same route :

router.get('/', function(req, res, next) {

To add an operator of the “LIKE” type to a request, Sequelize requires us to add the Op variable. So we will add at the beginning :

const Sequelize = require('sequelize');
const Op = Sequelize.Op;

in the get() function, add

// queryStrings
let { name, order, sort, limit, offset } = req.query;

This narrows the list of permissible querystrings. With this variable, you will be limited to the following query strings :

http://localhost:3000/api?name=&order=&sort=&limit=&offset=

  • name: search in the “name” field of the api table
  • order: the name of the field of the table to be sorted.
  • sort: sort order: (asc or desc)
  • limit: the number of records to be returned
  • offset: the offset

We define a variable that will be fed as the query strings are passed

let paramQuerySQL = {};

A default value is then given to the “sort” variable if it’s empty. This value will be “ASC” for “ASCENDING order”.

// sort par defaut si param vide ou inexistant
if (typeof sort === 'undefined' || sort == '') {
    sort = 'ASC';
}

The value of the “sort” variable is forced to ASC if the value is filled in but incorrect. Indeed, in SQL a sort accepts only 2 values: ASC and DESC.

// Si sort n'est pas vide et n'est ni asc ni desc
if (typeof sort !== 'undefined' && !['asc','desc'].includes(sort.toLowerCase())) {
    sort = 'ASC';
}

If the query string name exists and is filled ==> a LIKE search is performed

// Recherche LIKE '%%'
if (name != '' && typeof name !== 'undefined') {
    paramQuerySQL.where = {
            name: {
                [Op.like]: '%' + name + '%'
            }
        }

}

If the String order query is filled

// order by
if (order != '' && typeof order !== 'undefined' && ['name'].includes(order.toLowerCase())) {
    paramQuerySQL.order = [
        [order, sort]
    ];
}

If the String limit query is an integer greater than 0.

// limit
if (limit != '' && typeof limit !== 'undefined' && limit > 0) {
    paramQuerySQL.limit = parseInt(limit);
}

If the String offset query is an integer greater than 0.

// offset
if (offset != '' && typeof offset !== 'undefined' && offset > 0) {
    paramQuerySQL.offset = parseInt(offset);
}

We have now created all the opportunities we need. We modify a little using findAndCountAll instead of findAll because we want to recover the total number of rows returned by our request.

db.api.findAndCountAll(paramQuerySQL).then(apis => res.json({
    error: false,
    count: apis.count,
    data: apis.rows,
}))

Here is an example of URL http://localhost:3000/api?order=name&sort=DESC&limit=1&offset=2 .
This URL presents as : return to me a line (limit=1) from the 2nd line (offset=2) and order this list by name (order=name) in a decreasing way (sort=DESC).

img

Sources: https://github.com/rabehasy/nodejs-express/tree/step2

Thanks to A.A for translating

this post is published on Medium too