Code With Wolf

How to Use JWT with Node.js, Express, and SQLite

January 19, 2021

How to Use JWT with Node.js, Express, and SQLite

I start a lot of side projects (and occasionally even finish them). They tend to have a few things in common, like data persistence, authentication, and session storage.

These items can be a bit of a pain to write from scratch every single time. I haven't found a lightweight and simple template for this that I enjoy yet. To be honest, the developer portion of me hasn't really looked because I was planning on building this out myself one day.


The Template

I started a template that will serve the following purposes.

  • Local Authentication using JWT and Session Storage.
  • Data Persistence with SQLite (by default running in memory).
  • Simple/Lightweight template to quickly create REST APIs for MVPs.

Disclaimer

This project is not intended for production use.

This is for dev purposes only and should not be used in a production environment.


Source Code

The entire project can be found here.

This is an open liscence so fork it, modify it, make it yours. Feel free to file any issues or make PRs as well.


Dependencies

Here are the dependencies used for this project.

npm i -s bcrypt body-parser cookie-parser 
cors express express-session express-session-sqlite
njwt nnode sqlite3

Package.json

{
  "name": "node-jwt-sqlite-starter",
  "version": "1.0.0",
  "description": "",
  "main": "index.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1",
    "start": "node index.js",
  },
  "author": "",
  "license": "ISC",
  "dependencies": {
    "bcrypt": "^5.0.0",
    "body-parser": "^1.19.0",
    "cookie-parser": "^1.4.5",
    "cors": "^2.8.5",
    "express": "^4.17.1",
    "express-session": "^1.17.1",
    "express-session-sqlite": "^2.0.8",
    "njwt": "^1.0.0",
    "nnode": "^0.3.3",
    "sqlite3": "^5.0.1"
  }
}

index.js

This is all that is needed in the index.js. The reason for this file is more to import nnode which allows us to use the latest ECMAScript features in JS.

If you don't want to use nnode and are cool writing ES5, then you really can just add all of the app.js contents here.

My tech debt is showing, but I kept getting errors trying to include all of my server code in the same file I require nnode in so I split them out like this. 

require('nnode');
require('./app');

app.js

Here is where our express server will live.

I included quite a bit of middleware in here, which we will create in just a moment. 

import express from 'express';
import bodyParser from 'body-parser';
import dao from './repositories/dao';
import { authenticated, authMiddleware } from './controllers/auth.controller';
import authRoutes from './routes/auth.routes';
import itemsRoutes from './routes/items.routes';
const session = require('express-session');
const cookieParser = require('cookie-parser');
import * as sqlite3 from 'sqlite3'
import sqliteStoreFactory from 'express-session-sqlite'

const port = 3000;
export const app = express();

app.listen(port, () => console.log(`Node-JWT-SQLite-Starer is listening on port ${port}!`));
app.use(bodyParser.json());
app.use(authMiddleware);
app.use(cookieParser());

app.use(session({ secret: "super secret string" }));
const SqliteStore = sqliteStoreFactory(session)
app.use(session({
    store: new SqliteStore({
        driver: sqlite3.Database,
        path: ':memory:',
        ttl: 604800000, // 1 week in miliseconds
    }),
}));

//  Script to setup sqlite DB in memory //
dao.setupDbForDev();
////////////////////////////////////

app.use('/api/auth', authRoutes);
app.use('/api/items', authenticated, itemsRoutes);

You will want to create a config file to hold sensitive values such as the session secret.

You will see that body-parser, cookie-parser, expresss-session and a lot of middleware is setup here.

Go through this and refactor for your needs.

By default, I have a session with a ttl for 1 week. And you can see it is using a SQLite data store.

I also have abstracted out some routes, controllers, and a repository.

Let's get into some of that now.


repositorys/dao.js

The purpose of this DAO file is to:

  1. Setup the DB with the setupDbForDev() function. This will create tables and insert values.

    Because the DB is running in memory in this template, it will need to create the table and insert values each time the server is restarted.

    You can update the tables and values to fit your needs.

    You can also host the SQLite db one your local machine or elsewhere instead of using memory


  2. Abstracting out common SQLite functionality

    This projects uses the sqlite3 node module to connect and query the SQLite DB. I find myself constantly copying code that runs the library's three main commands so I abstracted those commands here and return a promise. 

const sqlite3 = require('sqlite3').verbose();
const db = new sqlite3.Database(':memory:');
const bcrypt = require('bcrypt');
const saltRounds = 10;



export default class {

    static setupDbForDev() {
        //  This sets up a DB in memory to be used by creating tables, inserting values, etc.
        db.serialize(function () {
            const createUsersTable = "CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY AUTOINCREMENT,username TEXT, password text)";
            db.run(createUsersTable);
            const createItemsTable = "CREATE TABLE IF NOT EXISTS items (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, price NUMERIC)";
            db.run(createItemsTable);
            let password = '123'

            bcrypt.hash(password, saltRounds, function (err, hash) {
                const insertUsers = `INSERT INTO users (username, password) VALUES ('foo', '${hash}'), ('bar', '${hash}');`
                db.run(insertUsers);
            });
            const insertItems = `INSERT INTO items (name, price) VALUES ('book', 12.99), ('t-shirt', 15.99), ('milk', 3.99);`
            db.run(insertItems);
        });
    }

    static all(stmt, params) {
        return new Promise((res, rej) => {
            db.all(stmt, params, (error, result) => {
                if (error) {
                    return rej(error.message);
                }
                return res(result);
            });
        })
    }
    static get(stmt, params) {
        return new Promise((res, rej) => {
            db.get(stmt, params, (error, result) => {
                if (error) {
                    return rej(error.message);
                }
                return res(result);
            });
        })
    }

    static run(stmt, params) {
        return new Promise((res, rej) => {
            db.run(stmt, params, (error, result) => {
                if (error) {
                    return rej(error.message);
                }
                return res(result);
            });
        })
    }
}

repository/repository.js

This file is where some of the data access code can live. You will see shortly that the controllers are calling these functions.

This starter project is very simple in terms of data needs and there may need to be more processing and data mapping performed here (or in another service).

For this starter template, the repository is more or less just a pass-through. 

  
import dao from './dao';
const bcrypt = require('bcrypt');
const saltRounds = 10;

export default class {

    static async getAllItems() {
        return await dao.all("SELECT * FROM items", [])
    }

    static async getItemById(id) {
        return await dao.get("SELECT * FROM items WHERE id = ?", [id])
    }

    static async getUserByUsername(username) {
        return dao.get("SELECT * FROM users WHERE username =?", [username]);
    }

    static async getUserById(id) {
        return dao.get('SELECT * FROM users WHERE id = ?', [id]);
    }
}

controllers/auth.controller.js

This is where the auth code is.

The project uses bcrypt to hash password and save it to our in-memory SQLite data store.

JWT tokens are encoded/decoded by njwt.

This file calls the repository we just created to find the matching user on login and check for a password match.

import njwt from 'njwt';
import repository from '../repositories/repository';
const bcrypt = require('bcrypt');

const {
  APP_SECRET = 'secret' } = process.env;

const encodeToken = (tokenData) => {
  return njwt.create(tokenData, APP_SECRET).compact();
}

const decodeToken = (token) => {
  return njwt.verify(token, APP_SECRET).setExpiration(new Date().getTime() + 604800000).body; //1 week
}

export const authMiddleware = async (req, res, next) => {
  const token = req.header('Access-Token');
  if (!token) {
    return next();
  }

  try {
    const decoded = decodeToken(token);
    const { userId } = decoded;
    const user = await repository.getUserById(userId)
    if (user) {
      req.userId = userId;
    }
  } catch (e) {
    return next();
  }
  next();
};

export const authenticated = (req, res, next) => {
  if (req.userId) {
    return next();
  }

  res.status(401);
  res.json({ error: 'User not authenticated' });
}

const returnInvalidCredentials = (res) => {
  res.status(401);
  return res.json({ error: 'Invalid username or password' });

}

export const login = async (req, res) => {
  const { username, password } = req.body;


  const user = await repository.getUserByUsername(username)

  if (!user) {
    returnInvalidCredentials(res)
  }

  bcrypt.compare(password, user.password, (err, result) => {
    if (result) {
      const accessToken = encodeToken({ userId: user.id });
      return res.json({ accessToken });
    } else {
      return returnInvalidCredentials(res);
    }
  });
}

controllers/items.controller.js

This is just a sample controller that calls the repository to get DB data.

The ./repository/dao.js's setupDbForDev() function creates a table of items and inserts a few items so these controller methods are used to get those items.

import repository from '../repositories/repository';
import dao from '../repositories/dao'

export default class {
    static async getAllItems(req, res) {
        let items = await repository.getAllItems();
        return res.send({ items });
    };

    static async getItemById(req, res) {
        let item = await repository.getItemById(req.params.id)
        return res.send({ item });
    }

}

routes/auth.js

Here is a route file that handles our one auth route, /login.

You will likely modify this and add a logout and what not. A lot of the other auth related code you already saw lives in the auth contorller.

import { login } from '../controllers/auth.controller';
import * as express from 'express';
const router = express.Router()

router.post('/login', login)

module.exports = router

routes/items.js

Last, here are our item routes to get items from the in-memory SQLite data store. 

import itemsController from '../controllers/items.controller';
import * as express from 'express';
const router = express.Router()

router.get("/", itemsController.getAllItems);
router.get("/:id", itemsController.getItemById)

module.exports = router

Tying It All Together

Those are all of the files to start the project. If you haven't installed the node modules be sure to run npm i.

After that, run the project with npm run start.


Authenticating

If you make a request to any of the item endpoints, you should get a 401/unauthorized error.

Let's authenticate.

We can do that by sending a POST request to localhost:3000/api/auth/login.

We need our request body to include one of the username/password combos in repositories/dao.js like 

{
    "username":"foo",
    "password":"123"
}

After that we will get back a 200 status with a response that includes an access-token.

The response should look like this ...

{
    "access_token":"<YOUR ACCESS TOKEN>"
}

Now, take our access token and add it to your headers. 

{
    "Content-Type": "application/json",
    "Accept":"*/*",
    "Access-Token: "YOUR ACCESS TOKEN FROM ABOVE"
}

You should now be authenticated.

Try out a GET request to localhost:3000/api/items and you should get the list of 3 items as a response.


Conclusion

That's about it. It's a lightweight starter for any node.js API. It has authentication, session storage, data storage, and would work great to use as a template for dev side projects, MVPs, etc.

Again, this is not intended for production use.

If you found this useful, consider buying me a coffee. I hope you will enjoy using this project to start your own node.js APIs.

© 2022 Code With Wolf