Creating a RESTful API with NodeJS and SQLite

Handling a database table with large number of rows with SQL is not an easy job. Either we get issues with insertions or retrieval of data from the database table. I myself have tested with SQLite database for testing and I found it working well with both the operations. Hence, I decided to share my knowledge with you on creating a RESTful API with NodeJS and SQLite.

There are plenty of blogs available on the Internet which helps you to know when to use which database, just google it.

Before we proceed with this tutorial, you need to know how to build an API with NodeJS and ExpressJS as a prerequisite.

I hope you are now aware of how to build an API with NodeJS. In this tutorial, we will create an API which will return recipe of Indian food that user will look for. You can follow this process when running small scale applications.

The API code is placed in my github account. Click here if you would like to read the code or copy the URL directly -

https://github.com/Sachinart/Indian-Recipe-API

Now, open your Google Cloud terminal and enter below command to clone the repository code -

git clone https://github.com/Sachinart/Indian-Recipe-API

You would find a new folder "Indian Recipe API" created in the file explorer. Move inside the folder in terminal using command - 

cd Indian Recipe API

Now, we have to install all basic libraries of NPM and SQLite3 library to use SQL queries. Run below commands -

npm install

follwed by

npm i sqlite3

Now that SQLite3 library is installed, we need to open SQLite3 database. To create a SQLite3 database, run the below command -

sqlite3 recipe.sqlite

You can name anything for your database such as recipe.db or recipe alone or something else.

Once you open SQLite3 database, you are allowed to run SQL queries. 

We are using a Kaggle dataset to get the data of Indian Recipes. You can find a CSV file in the same folder of your project in file explorer with name - IndianFoodDataset.csv which have fourteen columns in it. To create the database of same columns, we need to use below query - 

CREATE TABLE recipe (
   RecipeName TEXT,
   TranslatedRecipeName TEXT,
   Ingredients TEXT,
   TranslatedIngredients TEXT,
   PrepTimeInMins INTEGER,
   CookTimeInMins INTEGER,
   TotalTimeInMins INTEGER,
   Servings TEXT,
   Cuisine TEXT,
   Course TEXT,
   Diet TEXT,
   Instructions TEXT,
   TranslatedInstructions TEXT,
   URL TEXT
);

Now that the database table is created, we are almost there in creating a RESTful API with NodeJS and SQLite.

We have now to import the data of CSV file to the database table Recipe we just created. To do this, keep the SQLite3 terminal open and run below query - 

.mode csv
.header on
.separator "|"
.import IndianFoodDataset.csv recipe

As you could see above, the mode is kept csv because we are using CSV file to import data into the table. The file have a header with all the column labels, hence we kept header on. The CSV file is separated by Pipe operator (|) to avoid any import issues, because the file column may have comma in the data. And lastly the import statement to import IndianFoodDataset.csv file data to recipe table.

Once this is done, you can test the data by running any basis SQL query such as - 

SELECT * FROM recipe LIMIT 5;

You can come out of SQLite3 terminal by pressing CTRL + X and run the command below to start your API -

npm start

If you get any error, it must be the port issue. Try changing the port number in file - bin/www.

Below is the complete code of index.js - 

var express = require('express');
var sqlite3 = require('sqlite3').verbose();
var path = require('path');
var router = express.Router();

/* GET users listing. */
router.get('/', function(req, res, next) {

    if(Object.keys(req.query).length === 0){
        res.send("No input given");
    } else {

        let query   = req.query.q;

        let SQLquery = 'SELECT * FROM recipe ';

        if(query){
            SQLquery += 'WHERE RecipeName LIKE "%' + query + '%"';
        }

        //console.log(SQLquery);

        let result = [];

        var db = new sqlite3.Database(path.resolve(__dirname, '../recipe.sqlite'));
        
        db.serialize(function() {
            db.each(SQLquery, (err, row) => {
                if (err) {
                    console.error(err.message);
                } else {
                    result.push(row); 
                }
            }, function(){
                res.send(result);
            });
        });

        db.close();
    }
});

module.exports = router;

As you could see, the parameter we use for search is q. It will look like: ?q=poha.

You may get to see question mark (?) in the response. They appears when the language is non-supportable by system. The column in table - RecipeName, Ingredients and Instructions contain Hindi letters which may not work well. So, to use the API data in your front end application, you could use the translated columns - TranslatedRecipeName, TranslatedIngredients and TranslatedInstructions.

The coding is very easy if you already gone through the other tutorials - Building a simple REST API Node JS And Express JS in Google Cloud and Learn Web Scraping Using Node JS and Express JS in Google Cloud. If you haven't, please do it now.

My Experience in this project

I opted this solution when I had to create an API returning data based on the CSV file I had. The only issue with MySQL was that it was very slow in retrieving data. For a database table with 0.6 Million rows, the retrieval was taking 4-6 seconds. When choosen SQLite3 over MySQL, the speed increased drastically. I tested SQLite3 with a database table of 23 Million rows and it hardly took 3 seconds to retrieve the data and return JSON as an API output.

I hope you would try it and like it. Thanks for reading it. :)