Node.js – API With SQLite Database

By -Danny B.
Posted on September 12, 2021October 6, 2021
Posted in Node.js

Overview

This article will go through the steps needed to create an API with Node.js. The back end will use the lightweight, portable, and feature rich SQLite. The API will create the database file if it doesn’t exist and insert four records to get things started. The API will also have actions for create, read, update, and delete.

Download the Example

Source Code

SQLite

The API should be able to run without SQLite installed. The sqlite3 library should be able to perform CRUD operations on a sqlite3 database file.

At the time this post was written version 3.36.0 was available.

Install SQL Lite https://www.sqlite.org/download.html.

For a quick how to article on installing SQLite please read this article.

Prerequisites

If you don’t already have Node.js installed then you can get the download and install information from their website at https://nodejs.org/.

Install Express

Create a folder for your project and go into that directory and run the following commands.

npm init
npm install express

Install Nodemon

Nodemon restarts the node.js application when changes are made.

npm install nodemon

Update the “scripts” section of the “package.json” file with the following:

  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1",
    "start": "nodemon  app.js"
  },

To start the application use npm start instead of node app.js

Install the SQL Lite Driver.

npm install sqlite3

Handling CORS

Cross Origin Resource Sharing is needed if you want to use the API with an external application.

npm i cors

Building The API

App.js

Create a file labeled “./app.js” in the root folder of the API.

All of the imports needed to run the API. This includes the SQLite3 library and Cross Origins. The port is set to 3004.

const express = require('express');
const app = express();
const port = 3004;
var sqlite3 = require('sqlite3').verbose()
const cors = require('cors');

The following will connect to a database labeled productsdb.sqlite. If the database doesn’t exist it will create it and insert four records.

const DBSOURCE = "productsdb.sqlite";

let db = new sqlite3.Database(DBSOURCE, (err) => {
    if (err) {
      // Cannot open database
      console.error(err.message)
      throw err
    }else{
        // ** EXAMPLE **
        // ** For a column with unique values **
        // email TEXT UNIQUE, 
        // with CONSTRAINT email_unique UNIQUE (email) 
        
        db.run(`CREATE TABLE Products (
            Id INTEGER PRIMARY KEY AUTOINCREMENT,
            Title TEXT,             
            Quantity INTEGER,             
            DateModified DATE,
            DateCreated DATE
            )`,
        (err) => {
            if (err) {
                // Table already created
            }else{
                // Table just created, creating some rows
                var insert = 'INSERT INTO Products (Title, Quantity, DateCreated) VALUES (?,?,?)'
                db.run(insert, ["Baseball", 3, Date('now')])
                db.run(insert, ["Football", 5, Date('now')])
                db.run(insert, ["Apple", 6, Date('now')])
                db.run(insert, ["Orange", 7, Date('now')])
            }
        });  
    }
});

The app.use() will need the “express.urlencoded()” to read the incoming packets and the CORS for API calls made from a JavaScript web application.

app.use(
    express.urlencoded(),
    cors({
        origin: 'http://localhost:3000'
    })
);

API CRUD Operations

The following are the API actions for Get All records.

app.get("/api/products", (req, res, next) => {
    var sql = "SELECT * FROM Products"
    var params = []
    db.all(sql, params, (err, rows) => {
        if (err) {
          res.status(400).json({"error":err.message});
          return;
        }
        res.json({
            "message":"success",
            "data":rows
        })
      });
});

Get Single Product

app.get("/api/product/:id", (req, res, next) => {
    var sql = "SELECT * FROM Products WHERE Id = ?"
    db.all(sql, req.params.id, (err, rows) => {
        if (err) {
          res.status(400).json({"error":err.message});
          return;
        }
        res.json({
            "message":"success",
            "data":rows
        })
      });
})

Insert a new record

app.post("/api/product", (req, res) => {
    var errors=[]
    
    if (!req.body.Title){
        errors.push("Title is missing");
    }
    if (!req.body.Quantity){
        errors.push("Quantity is missing");
    }
    if (errors.length){
        res.status(400).json({"error":errors.join(",")});
        return;
    }
    var data = {
        Title: req.body.Title,
        Quantity: req.body.Quantity,
        DateCreated: Date('now')
    }
    var sql ='INSERT INTO Products (Title, Quantity, DateCreated) VALUES (?,?,?)'
    var params =[data.Title, data.Quantity, Date('now')]
    db.run(sql, params, function (err, result) {
        if (err){
            res.status(400).json({"error": err.message})
            return;
        }
        res.json({
            "message": "success",
            "data": data,
            "id" : this.lastID
        })
    });   
})

Update an existing record

app.patch("/api/product/:id", (req, res, next) => {
    var data = [req.body.Title, req.body.Quantity, Date('now'), req.params.id];
    
    let sql = `UPDATE Products SET 
               Title = ?, 
               Quantity = ?, 
               DateModified = ?
               WHERE Id = ?`;
    
    db.run(sql, data, function(err) {
      if (err) {
        return console.error(err.message);
      }
      console.log(`Row(s) updated: ${this.changes}`);
    
    });
    res.json({
        message: "success",
        data: data,
        changes: this.changes
    })    
})

Delete a record by ID.

app.delete("/api/product/:id", (req, res, next) => {
    db.run(
        'DELETE FROM Products WHERE id = ?',
        req.params.id,
        function (err, result) {
            if (err){
                res.status(400).json({"error": res.message})
                return;
            }
            res.json({"message":"Deleted", changes: this.changes})
    });
})

Last but not least add the listener for the port

app.listen(port, () => console.log(`API listening on port ${port}!`));

All Together

const express = require('express');
const app = express();
const port = 3004;
var sqlite3 = require('sqlite3').verbose()
const cors = require('cors');

const DBSOURCE = "productsdb.sqlite";

let db = new sqlite3.Database(DBSOURCE, (err) => {
    if (err) {
      // Cannot open database
      console.error(err.message)
      throw err
    }else{
        // ** EXAMPLE **
        // ** For a column with unique values **
        // email TEXT UNIQUE, 
        // with CONSTRAINT email_unique UNIQUE (email) 
        
        db.run(`CREATE TABLE Products (
            Id INTEGER PRIMARY KEY AUTOINCREMENT,
            Title TEXT,             
            Quantity INTEGER,             
            DateModified DATE,
            DateCreated DATE
            )`,
        (err) => {
            if (err) {
                // Table already created
            }else{
                // Table just created, creating some rows
                var insert = 'INSERT INTO Products (Title, Quantity, DateCreated) VALUES (?,?,?)'
                db.run(insert, ["Baseball", 3, Date('now')])
                db.run(insert, ["Football", 5, Date('now')])
                db.run(insert, ["Apple", 6, Date('now')])
                db.run(insert, ["Orange", 7, Date('now')])
            }
        });  
    }
});


module.exports = db
app.use(
    express.urlencoded(),
    cors({
        origin: 'http://localhost:3000'
    })
);

app.get('/', (req, res) => res.send('API Root'));

// GET ALL
app.get("/api/products", (req, res, next) => {
    var sql = "SELECT * FROM Products"
    var params = []
    db.all(sql, params, (err, rows) => {
        if (err) {
          res.status(400).json({"error":err.message});
          return;
        }
        res.json({
            "message":"success",
            "data":rows
        })
      });
});

// GET SINGLE USER
app.get("/api/product/:id", (req, res, next) => {
    var sql = "SELECT * FROM Products WHERE Id = ?"
    db.all(sql, req.params.id, (err, rows) => {
        if (err) {
          res.status(400).json({"error":err.message});
          return;
        }
        res.json({
            "message":"success",
            "data":rows
        })
      });
})

// CREATE
app.post("/api/product", (req, res) => {
    var errors=[]
    
    if (!req.body.Title){
        errors.push("Title is missing");
    }
    if (!req.body.Quantity){
        errors.push("Quantity is missing");
    }
    if (errors.length){
        res.status(400).json({"error":errors.join(",")});
        return;
    }
    var data = {
        Title: req.body.Title,
        Quantity: req.body.Quantity,
        DateCreated: Date('now')
    }
    var sql ='INSERT INTO Products (Title, Quantity, DateCreated) VALUES (?,?,?)'
    var params =[data.Title, data.Quantity, Date('now')]
    db.run(sql, params, function (err, result) {
        if (err){
            res.status(400).json({"error": err.message})
            return;
        }
        res.json({
            "message": "success",
            "data": data,
            "id" : this.lastID
        })
    });   
})

// UPDATE
app.patch("/api/product/:id", (req, res, next) => {
    var data = [req.body.Title, req.body.Quantity, Date('now'), req.params.id];
    
    let sql = `UPDATE Products SET 
               Title = ?, 
               Quantity = ?, 
               DateModified = ?
               WHERE Id = ?`;
    
    db.run(sql, data, function(err) {
      if (err) {
        return console.error(err.message);
      }
      console.log(`Row(s) updated: ${this.changes}`);
    
    });
    res.json({
        message: "success",
        data: data,
        changes: this.changes
    })    
})


app.delete("/api/product/:id", (req, res, next) => {
    db.run(
        'DELETE FROM Products WHERE id = ?',
        req.params.id,
        function (err, result) {
            if (err){
                res.status(400).json({"error": res.message})
                return;
            }
            res.json({"message":"Deleted", changes: this.changes})
    });
})

app.listen(port, () => console.log(`API listening on port ${port}!`));

Try It

Run the application

If you installed nodemon and updated the package.json file then use npm start instead of node app.js

npm start

Without nodemon installed

node app.js

Try calling the API actions in something like PostMan or Insomnia.

Examples with Postman

GET – Gets all records

http://localhost:3004/api/products

GET – Gets a single record

http://localhost:3004/api/product/4

Post

http://localhost:3004/api/product

Click on Body and x-www-form-urlencoded and input the Title and Quantity fields.

DELETE

http://localhost:3004/api/product

Troubleshooting

If the API Cannot Read the Request Data

If the API cannot read the request data for Inserts and Updates you may need the body-parser library.

Some of the versions of Node.js before version 4.16 needed the body-parser library to get the contents of an API request.

npm install body-parser

In the top of the app.js file add the following

var bodyParser = require('body-parser')
app.use(bodyParser.urlencoded({ extended: false }))
app.use(bodyParser.json())

Full Stack Soup