Build a REST API with Node.js SQLite and Express JS

In this guide you can build a REST API with Node.js SQLite and Express.js. Then, in a next part, we will be building automated Unit tests using Mocha and Chai, ready for a Test-Driven Development (TDD).

The main components of this REST service are:

• A Node.js base project, created as a NPM module
• An Express.js web server to manage the API endpoints, requests and responses
• A SQLite database for application storage

Previous Requirements

If you haven’t installed Node.js, visit https://nodejs.org/es/download/ for download and installation instructions. Also, you may want to install a Git client to create a local repository for your code.

The Node.js base project

To start with a Node.js and Express.js project, create a new project folder. Then, create an empty NPM project, using the npm init command :

mkdir node-express
cd node-express
npm init

The npm command will ask some information about your project. Is not necessary to fill out every field. The most important are: package name, and entry point. Our entry point will be a file named server.js(our Express.js API server)

package name: (express) express-api
version: (1.0.0)
description: Express REST API
entry point: (index.js) server.js
test command:
git repository:
keywords:
author: Me
license: (ISC)

Then, you need to install some base dependencies we need in this project:

npm install express
npm install sqlite3
npm install md5

Now, our package.json file will be like this.

{
 "name": "express-api",
 "version": "1.0.0",
 "description": "Express REST API",
 "main": "server.js",
 "scripts": {
   "test": "echo "Error: no test specified" && exit 1"
  },
 "author": "Me",
 "license": "ISC",
 "dependencies": {
   "express": "^4.16.4",
   "md5": "^2.2.1",
   "sqlite3": "^4.0.4"
  }
}

Also, you may want to create a local Git repository to store your project. In this case, you can run the git init command. Then, create a .gitignore file to ignore the node_modules folder created by npm.

git init
echo node_modules > .gitignore

Then you can perform the common git operations to add and commit your changes. If you are new to Git, you may want to read this basic Git intro.

The Express.js Server

Now, we can create the main server script (server.js) using Express. Express.js is a very simple implementation for a web server, and you need only a few lines of code to get a first functional result:

// Create express app
var express = require("express")
var app = express()

// Server port
var HTTP_PORT = 8000 
// Start server
app.listen(HTTP_PORT, () => {
    console.log("Server running on port %PORT%".replace("%PORT%",HTTP_PORT))
});
// Root endpoint
app.get("/", (req, res, next) => {
    res.json({"message":"Ok"})
});

// Insert here other API endpoints

// Default response for any other request
app.use(function(req, res){
    res.status(404);
});

In the server.js code example, we can identify:

1. Creation of the express server (app)
2. Definition of a local server port (HTTP_PORT)
3. Start the web server, running on HTTP_PORT
4. A response for the root endpoint (http://localhost:HTTP_PORT/)
5. A default response for any other request, defaults to HTTP 404 response (Not found)

A note about Arrow Functions

Also, as an additional note, we are using the ES6 arrow functions instead of the common ES5 notation for functions callbacks, for example:

function (req, res, next) {
    res.json({"message":"Ok"})
}

Has the same result using ES6 arrow function notation, with a more simple and shorter syntax:

(req, res, next) => {
    res.json({"message":"Ok"})
}

The main difference is, according to the documentation, an arrow function does not have references to this, and the arguments variables, for example.

Running the web server

Now, we can add a new "start" entry in our package.json file to run our server using the npm run command:

  "scripts": {
    "start": "node server.js",
    "test": "echo "Error: no test specified" && exit 1"
  },

After you create this line, now we can run our server using the command:

npm run start

> node server.js

Server running on port 8000

You can point your browser to the server URL http://localhost:8000/ to see the initial result (our server response for the root endpoint "/"):

{"message":"Ok"}

Note that every time you made changes to your server, you need to stop it (Ctrl + C) and then start the server again with npm run start.

Connecting a Database

At this point we have a web server running. Now, we need a local database to store information to be consumed by the REST API. In this case, we use a SQLite local database, using the sqlite3 node package.

In a new file database.js we will create the main database connection, and the database initialization:

var sqlite3 = require('sqlite3').verbose()
var md5 = require('md5')

const DBSOURCE = "db.sqlite"

let db = new sqlite3.Database(DBSOURCE, (err) => {
    if (err) {
      // Cannot open database
      console.error(err.message)
      throw err
    }else{
        console.log('Connected to the SQLite database.')
        db.run(`CREATE TABLE user (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name text, 
            email text UNIQUE, 
            password text, 
            CONSTRAINT email_unique UNIQUE (email)
            )`,
        (err) => {
            if (err) {
                // Table already created
            }else{
                // Table just created, creating some rows
                var insert = 'INSERT INTO user (name, email, password) VALUES (?,?,?)'
                db.run(insert, ["admin","admin@example.com",md5("admin123456")])
                db.run(insert, ["user","user@example.com",md5("user123456")])
            }
        });  
    }
});


module.exports = db

In the database.js code example,  we can identify:

1. Loading the sqlite3 and md5 modules. The .verbose() modifier is to get extra information for debugging. MD5 is used to create a hash for stored passwords, avoiding to save plain text passwords.
2. Definition of the SQLite database file (DBSOURCE)
3. Initialization of the SQLite database as db. By default, an empty database file is created, if not exists. There are other modes to initialize this database.
   1. A callback function is called after initialization of the database
   2. The err parameter is null when everything runs ok. Otherwise, it contains the error information to inform the user (err.message)
   3. After initialization, now we can create some tables and data. In this case, we create a user table with a CREATE TABLE statement.
      1. If the table was just created (err is null in this callback), then we create some user rows using the INSERT statement.
4. The last line is to make public (export) the database connection object (db), to be imported in another script, for example:
   var db1 = require('./database.js')

Extending the REST API with data endpoints

Now we can use the database connection created in the last step. First of all, import the reference to the database script in server.js:

var express = require("express")
var app = express()
var db = require("./database.js")

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

{
 "message":"success",
 "data":[ {"id":1,"name":"admin","email":"admin@example.com","password":"a66abb5684c45962d887564f08346e8d"},{"id":2,"name":"user","email":"user@example.com","password":"4da49c16db42ca04538d629ef0533fe8"}
 ]
}

app.get("/api/user/:id", (req, res, next) => {
    var sql = "select * from user where id = ?"
    var params = [req.params.id]
    db.get(sql, params, (err, row) => {
        if (err) {
          res.status(400).json({"error":err.message});
          return;
        }
        res.json({
            "message":"success",
            "data":row
        })
      });
});

var express = require("express")
var app = express()
var db = require("./database.js")
var md5 = require("md5")

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

We use md5() again to hash the password created. The body-parser middleware module, added to the Express.js app, will try to parse the body content (URL encoded or JSON) of the post request and store it in req.body object. Then, add the following Express.js endpoint code:

app.post("/api/user/", (req, res, next) => {
    var errors=[]
    if (!req.body.password){
        errors.push("No password specified");
    }
    if (!req.body.email){
        errors.push("No email specified");
    }
    if (errors.length){
        res.status(400).json({"error":errors.join(",")});
        return;
    }
    var data = {
        name: req.body.name,
        email: req.body.email,
        password : md5(req.body.password)
    }
    var sql ='INSERT INTO user (name, email, password) VALUES (?,?,?)'
    var params =[data.name, data.email, data.password]
    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
        })
    });
})

curl -d "name=test&email=test%40example.com&password=test123" -X POST http://localhost:8000/api/user/

{
"message":"success",
"data":{"name":"test","email":"test@example.com","password":"cc03e747a6afbbcbf8be7668acfebee5"},
"id":5
}

Update an user

For update operations we use the PATCH method (replace data). We can send a subset of fields to be updated in the entity (user). The server.js code to implement the user update is:

app.patch("/api/user/:id", (req, res, next) => {
    var data = {
        name: req.body.name,
        email: req.body.email,
        password : req.body.password ? md5(req.body.password) : null
    }
    db.run(
        `UPDATE user set 
           name = COALESCE(?,name), 
           email = COALESCE(?,email), 
           password = COALESCE(?,password) 
           WHERE id = ?`,
        [data.name, data.email, data.password, req.params.id],
        function (err, result) {
            if (err){
                res.status(400).json({"error": res.message})
                return;
            }
            res.json({
                message: "success",
                data: data,
                changes: this.changes
            })
    });
})

1. We use the PATCH method (app.patch()), an endpoint with a variable expression (:id), mapped to a variable in req.params (req.params.id)
2. Since each field could be empty (not updated), we use COALESCE function to keep the current value if there is no new value (null). The UPDATE command filter the user ID selected in the request (:id)
3. After checking if there is any error (err), returns a successful JSON response.
   1. Again, we use a classical callback function to get access to this.changes (the number of rows updated).
   2. You can use this number if you need to verify if the row was modified or not (same original data), to trigger a UI update, for example.

Delete an user

The last method to implement is DELETE. This endpoint takes an user id to be deleted. The server.js code to implement the user delete action is:

app.delete("/api/user/:id", (req, res, next) => {
    db.run(
        'DELETE FROM user 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})
    });
})

1. In this case, we use app.delete() and a variable expression (:id)  to map the user id of the request.
2. Then, we run a DELETE command filtered by the user id (req.params.id)
3. If everything is ok, we return a JSON response, including the number of rows affected (changes). If the user was already deleted, or the id was not found, the value will be 0.

Conclusion

In this first part, we created a Node.js/Express server, a SQLite database connection, and the main REST API endpoints to manage users (list users, get single user, create user, update user, delete user) as an example of how to implement a fully functional REST API in Node.js. Express is the de facto standard server framework for Node.js.

In the next part we will add some important topics in a REST API implementation:

1. Authentication (Token-based) using a Middleware function
2. Automated testing of our API endpoints using Mocha and Chai

Source Code

Feel free to download, clone or fork the Git repository at https://github.com/fraigo/node-express-rest-api-example