Convert CSV File to Restful API Using FeathersJS

Adam C. | 4 years ago

CSV is still a popular way to keep data because it's so easy to use Excel to enter data, but from the web application view, we would like the data source to be JSON format or via API. In this article, we will learn how to create a Restful API from a CSV file using FeathersJS and MongoDB.

We use Swimming World Records CSV data downloaded from SwimRankings.net as an example.  And we choose MongoDB for the database because Data in MongoDB has a flexible schema, which gives us a lot of freedom. And we use NoSQLBooster for MongoDB (a Powerful FREE MongoDB GUI)  to import CSV. Finally, we use FeathersJS to create a Restful API. BWT,  you may check out “Create a Restful API within 5 minutes”. 

Import CSV into MongoDB

To make a job easier, let's use NoSQLBooster. After connecting to the MongoDB server, you can create a database if you don't have one, in our case, we have a database name ‘deniapps’, and we just need to create a collection named “swimmingworldrecords”, and then import the CSV file into the collection. 

Note: To follow the naming conventions of Mongoose, we should use the lowercase for collection name. Please ignore the name in the screenshot below.  Sorry that I am too lazy to update it.

Mongoose will lowercase and pluralize with an ‘s’ of the Model name when it accesses the collection (‘SwimmingWorldRecord’ (Model Name) » ‘swimmingworldrecords’ (Collection Name))

Note that we should keep the first row of CSV file as fields, so then the Mongoimport knows how to set fields automatically for each document, and even better, the Mongoimport can auto-detect and set the data type of each field for us. 

Generate API with FeathersJS

We use FeathersJS CLI, a command-line interface for Feathers applications to build API.  Since we have feathers-CLI installed and the feathers app created already, we only need to generate a service. If this is the first time you are using FeathersJS, then checkout “Create a Restful API within 5 minutes”. 

To generate a new service, we can use the following commands:

$ feathers generate service
? What kind of service is it? Mongoose
? What is the name of the service? swimming world records
? Which path should the service be registered on? /swimming-world-records
? Does the service require authentication? No
  create src/services/swimming-world-records/swimming-world-records.service.js
….

Believe it or not, we just make an API. Run ‘npm run dev’, and the new service is ready at http://localhost:3030/swimming-world-records. Or you can test it at https://api.deniapps.com/swimming-world-records

Add Hooks

As we mentioned above “Mongoimport can auto-detect and set the data type of each field for us.” - Good and bad. It may help when we do sorting by a field, but it may cause issues when we search by a field.  Let's see one of the records:

{
  "_id": "5f7a7e2f174630d0c079e2e2",
  "COURSE": "LCM",
  "GENDER": "M",
  "DISTANCE": 50,
  "STROKE": "Fr",
  "FULLNAME": "CIELO, Cesar",
  "BIRTHDATE": "10.01.1987",
  "NATION": "BRA",
  "SWIMTIME": 20.91,
  "SWIMTIME_N": 20.91,
  "MEETDATE": "18.12.2009",
  "MEETCITY": "Sao Paulo",
  "MEETNAME": "Brasilian Championships",
  "SPLIT50": 20.91
},

For example, the “DISTANCE” field is integer, but if we try to find this record by http://localhost:3030/swimming-world-records?DISTANCE=50, we will get no result, that is because the HTTP parameter is String, to fix this, we can write a simple hook to fix this.

Again, we can use feathers CLI - feathers generate hook, like below:

$ feathers generate hook
? What is the name of the hook? type cast
? What kind of hook should it be? before
? What service(s) should this hook be for (select none to add it yourself)?
 swimming-world-records
? What methods should the hook be for (select none to add it yourself)? find
   create src/hooks/type-cast.js
    force src/services/swimming-world-records/swimming-world-records.hooks.js

And then update swimming-world-records.hooks.js to be:

module.exports = (options = {}) => {
  return async (context) => {
    if (context.params.query.DISTANCE)
      context.params.query.DISTANCE = parseInt(context.params.query.DISTANCE);
    return context;
  };
};

Now the DISTANCE parameter will be cast into an integer before being used for record lookup. Try http://localhost:3030/swimming-world-records?DISTANCE=50 again, you will see the records.

That's it! :-)