Node.js – API JWT Authentication With SQLite Database

By -Danny B.
Posted on September 17, 2021November 20, 2021
Posted in Node.js, SQLite

Image by Artturi Mäntysaari from Pixabay

Overview

This article will go through the steps needed to create a Node.js API to authenticate and generate a JWT Token. The back end will use SQLite. The first time the API runs, a database file UsersDB.sqlite will be created along with a Users table and four unique users with emails and passwords. The API will have a registration, login, and a test action to verify the request has a valid token. The login will use an external file “middleware.js” to verify the token that was issued when logging in.

Note: This article does not go into refresh tokens. That will be a topic for a future article on Node.js.

Download the Example

Source Code

SQL Lite (Optional)

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.

Building The API

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/.

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

npm init
npm install express

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

Add the SQL Lite Driver.

npm install sqlite3

VSCode SQLite Plugin – Optional

After it is install hit Control + Shift + P then type sqlite

Choose usersdb.sqlite

Choose Show Table

Handling CORS

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

npm i cors express nodemon

Environment Variable

Gives the application access to .env files. This is where we can put the keys for now.

npm i dotenv

CORS

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

npm i cors

JSON Web Token

Library to generate JWT tokens.

npm i jsonwebtoken

BCRYPT

This library is used to hash the passwords. BCRYPT is based on the Blowfish cipher

npm i bcrypt

Environment Variable

Create .env file to store the token secret . This will be used in app.js login action and the middleware.js for token validation. Add the following to the .env file.

Add the following to the .env file

TOKEN_KEY = 'Secret 123'

Middleware

Create a file labeled middleware.js. This will verify the JWT Token using the secret in the .env file

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 JSON Web Token, BCryptJS, SQLite3, and Cross Origins Resource Sharing. The port is set to 3004.

const express = require('express'); // Using the express framework
const app = express(); 
require("dotenv").config(); // Get environment variables from .env file(s)
var sqlite3 = require('sqlite3').verbose()
const cors = require('cors'); 
var jwt = require('jsonwebtoken');
var bcrypt = require('bcryptjs');

const DBSOURCE = "usersdb.sqlite";
const auth = require("./middleware");

const port = 3004;

The following will connect to a database labeled usersdb.sqlite. If the database doesn’t exist it will create it and insert four records. Each entry will have unique email like user1@email.com with a the password user1 and the Salt that is generated using bcrypt.genSaltSync().

let db = new sqlite3.Database(DBSOURCE, (err) => {
    if (err) {
      // Cannot open database
      console.error(err.message)
      throw err
    } 
    else {        
        var salt = bcrypt.genSaltSync(10);
        
        db.run(`CREATE TABLE Users (
            Id INTEGER PRIMARY KEY AUTOINCREMENT,
            Username text, 
            Email text, 
            Password text,             
            Salt text,    
            Token text,
            DateLoggedIn DATE,
            DateCreated DATE
            )`,
        (err) => {
            if (err) {
                // Table already created
            } else{
                // Table just created, creating some rows
                var insert = 'INSERT INTO Users (Username, Email, Password, Salt, DateCreated) VALUES (?,?,?,?,?)'
                db.run(insert, ["user1", "user1@example.com", bcrypt.hashSync("user1", salt), salt, Date('now')])
                db.run(insert, ["user2", "user2@example.com", bcrypt.hashSync("user2", salt), salt, Date('now')])
                db.run(insert, ["user3", "user3@example.com", bcrypt.hashSync("user3", salt), salt, Date('now')])
                db.run(insert, ["user4", "user4@example.com", bcrypt.hashSync("user4", salt), salt, Date('now')])
            }
        });  
    }
});

After running and making an API call a usersDB.sqlite rill will be generated

Setting up CORS

Recommended if you plan to use an external web application to call this API.

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. The code below will accept requests from an external Web Application with the domain localhost:3000.

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

The following route will check if there is an Email and Password in the request. Then query the Users table in the Users database with the Email to get the password hash and compare it to the Password posted by the user. If successful, generate a JWT Token with the user ID and email in the payload which is base64 encoded. This gets sent back with a status code of 200.

For the JWT expiration portion.
Example: 60s = 60 seconds – (60m = 60 minutes, 2h = 2 hours, 2d = 2 days)

s = seconds
m = minutes
h = hours
d = days

const token = jwt.sign(
  { user_id: user[0].Id, username: user[0].Username, Email },
  process.env.TOKEN_KEY,
  {
    expiresIn: "1h", 
  } 
);

Code for the Login Action


app.post("/api/login", async (req, res) => {
  
  try {      
    const { Email, Password } = req.body;
        // Make sure there is an Email and Password in the request
        if (!(Email && Password)) {
            res.status(400).send("All input is required");
        }
            
        let user = [];
        
        var sql = "SELECT * FROM Users WHERE Email = ?";
        db.all(sql, Email, function(err, rows) {
            if (err){
                res.status(400).json({"error": err.message})
                return;
            }

            rows.forEach(function (row) {
                user.push(row);                
            })
            
            var PHash = bcrypt.hashSync(Password, user[0].Salt);
       
            if(PHash === user[0].Password) {
                // * CREATE JWT TOKEN
                const token = jwt.sign(
                    { user_id: user[0].Id, username: user[0].Username, Email },
                      process.env.TOKEN_KEY,
                    {
                      expiresIn: "1h", // 60s = 60 seconds - (60m = 60 minutes, 2h = 2 hours, 2d = 2 days)
                    }  
                );

                user[0].Token = token;

            } else {
                return res.status(400).send("No Match");          
            }

           return res.status(200).send(user);                
        });	
    
    } catch (err) {
      console.log(err);
    }    
});

The output will look like the following:

[
  {
   "Id": 1,
   "Username": "user1",
   "Email": "user1@example.com",
   "Password": "$2a$10$igiRTpEnziWS6zM2Td2ctuf/eYSl.8SiPlTAHyNAxAgfojmL9ox3W",
   "Token": "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJ1c2VyX2lkIjoxLCJFbWFpbCI6InVzZXIxQGV4YW1wbGUuY29tIiwiaWF0IjoxNjMxODg0MTQ2LCJleHAiOjE2MzIwNTY5NDZ9.yRFN5EpDz2ozILtZbpB7G2-iRktJ7Op312bAyRivCO0",
   "DateLoggedIn": null,
   "DateCreated": "Fri Sep 17 2021 06:01:56 GMT-0700 (Pacific Daylight Time)"
  }
]

Test Authorization Request

Call the following API route without the JWT Token then with it to make sure it is working.

app.post("/api/test", auth, (req, res) => {
    res.status(200).send("Valid Token - Yay!");
});

How “/api/test” this works with middleware.js

Last but not least add the listener for the port

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

Registration Action

The following route will create a new record with a Username, Email, and Password. It will check if there is an Email that already exists so it will be unique. Then generates a hash using the Password with Bcrypt and inserts a new record with a create date.


app.post("/api/register", async (req, res) => {
    var errors=[]
    try {
        const { Username, Email, Password } = req.body;

        if (!Username){
            errors.push("Username is missing");
        }
        if (!Email){
            errors.push("Email is missing");
        }
        if (errors.length){
            res.status(400).json({"error":errors.join(",")});
            return;
        }
        let userExists = false;
        
        
        var sql = "SELECT * FROM Users WHERE Email = ?"        
        await db.all(sql, Email, (err, result) => {
            if (err) {
                res.status(402).json({"error":err.message});
                return;
            }
            
            if(result.length === 0) {                
                
                var salt = bcrypt.genSaltSync(10);

                var data = {
                    Username: Username,
                    Email: Email,
                    Password: bcrypt.hashSync(Password, salt),
                    Salt: salt,
                    DateCreated: Date('now')
                }
        
                var sql ='INSERT INTO Users (Username, Email, Password, Salt, DateCreated) VALUES (?,?,?,?,?)'
                var params =[data.Username, data.Email, data.Password, data.Salt, Date('now')]
                var user = db.run(sql, params, function (err, innerResult) {
                    if (err){
                        res.status(400).json({"error": err.message})
                        return;
                    }
                  
                });           
            }            
            else {
                userExists = true;
                // res.status(404).send("User Already Exist. Please Login");  
            }
        });
  
        setTimeout(() => {
            if(!userExists) {
                res.status(201).json("Success");    
            } else {
                res.status(201).json("Record already exists. Please login");    
            }            
        }, 500);


    } catch (err) {
      console.log(err);
    }
})

All Together

const express = require('express');
const app = express();
require("dotenv").config();
const port = 3004;
var md5 = require('md5')
var sqlite3 = require('sqlite3').verbose()
const cors = require('cors');
var jwt = require('jsonwebtoken');
var bcrypt = require('bcryptjs');
const DBSOURCE = "usersdb.sqlite";
const auth = require("./middleware");

let db = new sqlite3.Database(DBSOURCE, (err) => {
    if (err) {
      // Cannot open database
      console.error(err.message)
      throw err
    } 
    else {        
        var salt = bcrypt.genSaltSync(10);
        
        db.run(`CREATE TABLE Users (
            Id INTEGER PRIMARY KEY AUTOINCREMENT,
            Username text, 
            Email text, 
            Password text,             
            Salt text,    
            Token text,
            DateLoggedIn DATE,
            DateCreated DATE
            )`,
        (err) => {
            if (err) {
                // Table already created
            } else{
                // Table just created, creating some rows
                var insert = 'INSERT INTO Users (Username, Email, Password, Salt, DateCreated) VALUES (?,?,?,?,?)'
                db.run(insert, ["user1", "user1@example.com", bcrypt.hashSync("user1", salt), salt, Date('now')])
                db.run(insert, ["user2", "user2@example.com", bcrypt.hashSync("user2", salt), salt, Date('now')])
                db.run(insert, ["user3", "user3@example.com", bcrypt.hashSync("user3", salt), salt, Date('now')])
                db.run(insert, ["user4", "user4@example.com", bcrypt.hashSync("user4", salt), salt, Date('now')])
            }
        });  
    }
});


module.exports = db

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

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


// * R E G I S T E R   N E W   U S E R

app.post("/api/register", async (req, res) => {
    var errors=[]
    try {
        const { Username, Email, Password } = req.body;

        if (!Username){
            errors.push("Username is missing");
        }
        if (!Email){
            errors.push("Email is missing");
        }
        if (errors.length){
            res.status(400).json({"error":errors.join(",")});
            return;
        }
        let userExists = false;
        
        
        var sql = "SELECT * FROM Users WHERE Email = ?"        
        await db.all(sql, Email, (err, result) => {
            if (err) {
                res.status(402).json({"error":err.message});
                return;
            }
            
            if(result.length === 0) {                
                
                var salt = bcrypt.genSaltSync(10);

                var data = {
                    Username: Username,
                    Email: Email,
                    Password: bcrypt.hashSync(Password, salt),
                    Salt: salt,
                    DateCreated: Date('now')
                }
        
                var sql ='INSERT INTO Users (Username, Email, Password, Salt, DateCreated) VALUES (?,?,?,?,?)'
                var params =[data.Username, data.Email, data.Password, data.Salt, Date('now')]
                var user = db.run(sql, params, function (err, innerResult) {
                    if (err){
                        res.status(400).json({"error": err.message})
                        return;
                    }
                  
                });           
            }            
            else {
                userExists = true;
                // res.status(404).send("User Already Exist. Please Login");  
            }
        });
  
        setTimeout(() => {
            if(!userExists) {
                res.status(201).json("Success");    
            } else {
                res.status(201).json("Record already exists. Please login");    
            }            
        }, 500);


    } catch (err) {
      console.log(err);
    }
})


// * L O G I N

app.post("/api/login", async (req, res) => {
  
  try {      
    const { Email, Password } = req.body;
        // Make sure there is an Email and Password in the request
        if (!(Email && Password)) {
            res.status(400).send("All input is required");
        }
            
        let user = [];
        
        var sql = "SELECT * FROM Users WHERE Email = ?";
        db.all(sql, Email, function(err, rows) {
            if (err){
                res.status(400).json({"error": err.message})
                return;
            }

            rows.forEach(function (row) {
                user.push(row);                
            })
            
            var PHash = bcrypt.hashSync(Password, user[0].Salt);
       
            if(PHash === user[0].Password) {
                // * CREATE JWT TOKEN
                const token = jwt.sign(
                    { user_id: user[0].Id, username: user[0].Username, Email },
                      process.env.TOKEN_KEY,
                    {
                      expiresIn: "1h", // 60s = 60 seconds - (60m = 60 minutes, 2h = 2 hours, 2d = 2 days)
                    }  
                );

                user[0].Token = token;

            } else {
                return res.status(400).send("No Match");          
            }

           return res.status(200).send(user);                
        });	
    
    } catch (err) {
      console.log(err);
    }    
});

  
// * T E S T  

app.post("/api/test", auth, (req, res) => {
    res.status(200).send("Token Works - Yay!");
});


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

Try it with Postman

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 with PostMan or Insomnia.

Examples with Postman

POST – Register New User

http://localhost:3004/api/register

Username user98
Email user98@email.com
Password test123

The table should have a new entry

POST – Login

http://localhost:3004/api/login

Email user98@email.com
Password test123

GET – Test Token Validation

http://localhost:3004/api/test

Copy the token from the login response

Add a header labeled x-access-token and paste the token into the value block

You should get the following response

Change the token value by one character and it should respond with an Invalid Token message.

Verify the Token

Check the key on https://jwt.io/#debugger

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

Node.js – API JWT Authentication With SQLite Database

Overview

Download the Example

SQL Lite (Optional)

Building The API

Nodemon

Add the SQL Lite Driver.

Handling CORS

Environment Variable

CORS

JSON Web Token

BCRYPT

Environment Variable

Middleware

App.js

Setting up CORS

Test Authorization Request

Registration Action

All Together

Try it with Postman

Previous Article

Next Article

Node.js – API JWT Authentication With SQLite Database

Overview

Download the Example

SQL Lite (Optional)

Building The API

Nodemon

Add the SQL Lite Driver.

Handling CORS

Environment Variable

CORS

JSON Web Token

BCRYPT

Environment Variable

Middleware

App.js

Setting up CORS

API Login Action

Test Authorization Request

Registration Action

All Together

Try it with Postman

Previous Article

Next Article