How to handle non traditional queries in Node.js REST APIs

Question

I have some PHP and front-end JavaScript experience, but am trying to make a Node.js REST API to serve the Vue.js client-side of one of my applications, however I am struggling to get around a certain concept. So far I have primarily been following documentation and guides online.

I am using Express.js and a MySQL database.

In this example, I have a customer model defining the Customer itself, and methods to interact with the database.

// constructor
const Customer = function(customer) {
  this.email = customer.email;
  this.name = customer.name;
  this.active = customer.active;
  this.created_at = customer.created_at
};

Customer.getAll = result => {
  sql.query("SELECT * FROM customers", (err, res) => {
    if (err) {
      console.log("error: ", err);
      result(null, err);
      return;
    }

    console.log("customers: ", res);
    result(null, res);
  });
};

...

Then I have a customer controller which controls methods being called and does any manipulation before retrieving it to the front-end.

const Customer = require("../models/customer.model.js");

exports.findAll = (req, res) => {
  Customer.getAll((err, data) => {
    if (err)
      res.status(500).send({
        message:
          err.message || "Some error occurred while retrieving customers."
      });
    else res.send(data);
  });
};

...

This setup works for basic CRUD operations, but what if I want to do pull back a specific set of users and perform manipulations instead of the whole set.

For example, I would like to retrieve a list of the number of customers created per month for the last 3 months:

11/2019 200
12/2019 234
01/2020 122

Should I be adding a new method to my model which returns the data in the desired format, or should I add a new method to the controller which utilises the getAll method and filters it down depending on supplied parameters? I think adding a new method to the model could get out of hand quickly if I have a lot of specific requirements for queries, however if I filter down the full-set of data in the controller, that seems inefficient as I am retrieving all of the data from the database initially, when I am just going to disregard the majority of it.

Possibly I am completely off the mark and both of these approaches would be incorrect.

Edit

This is to offer additional clarity.

I can achieve the desired output my adding the following pieces of code to my model and routes:

Customer.getCreatedByMonth = result => {
    sql.query("select DATE_FORMAT(created_at, '%m/%Y') as month, count(*) as count from customers group by DATE_FORMAT(created_at, '%m/%Y')", (err, res) => {
        if (err) {
            console.log("error: ", err);
            result(null, err);
            return;
        }

        console.log(res);
        result(null, res);
    });
};

app.get("/customers/monthly", customers.findCreatedByMonth);

This doesn't feel like a very clean way to do it though, and I feel like I am breaking some CRUD conventions with the addition of the new route and the way it is named / accessed.

I know SQL and ideally I would like to avoid an ORM seeing as I am still learning Node.

Answer 1

I believe this really depends on the consumer of your API, and isn't really a question specific to NodeJS.

You have a resource at /customers, and for basic CRUD you might have GET,PUT, POST, DELETE methods.

In taking a cursory glance at a RESTful API that is designed this way, I would assume that a GET at the /customers endpoint would return me a list or array of customers.

In creating a path of /customers/monthly, you're trying to describe an additional functionality that sounds similar to a filter. Monthly sounds like it is trying to return a list of customers either filtered or grouped into months.

What others in the comments appear to be alluding to is that this could potentially be solved using query string parameters. Rather than add an additional path to /customers, you offer something like /customers?groupBy=month.

It sounds like you want to avoid adding complexity to your model, but I think that is unavoidable when you're moving outside of basic CRUD operations. You need to have some way to translate an HTTP request into a SQL operation.

Another question would be, should the responsibility of the model or the controller to fetch the appropriate data? The model lives closest to the data, so it likely should be responsible.

Depending on how big your dataset could get, it might become impractical to fetch all records and filter down at the controller level. The controller should really be responsible for translating a client's request for the model, so the appropriate data could be retrieved, and then ensuring that the data that is retrieved is formatted so that the client could receive it.