Node.js - Express Persistent Session Store with PostgreSQL + Sequelize

Your application may use session to authenticate user. If you're using Node.js, the most popular option to implement session is using a library called Passport.js. The problem is by default sessions are not stored persistently. If the application is restarted, all sessions will be lost. The easiest way to store sessions persistently is by using database to store them. In this tutorial, I'm going to show you how to store session persistently to PostgreSQL database with Sequelize as the ORM.

Add Dependencies to Your Project

Below are the dependencies you'll need to follow this tutorial. Add them to your package.json and run npm install.

package.json

  {
    "dependencies": {
"bcrypt": "~3.0.0",
"bluebird": "~3.5.1",
"connect-session-sequelize": "~6.0.0", "dotenv": "~6.1.0",
"express-session": "~1.15.6",
"lodash": "~4.17.11",
"passport": "~0.4.0",
"passport-local": "~1.0.0", "sequelize": "~4.38.0" } }

Edit your .env

Put these variables on your .env file and adjust the values according to your database setup. In addition to database config, you also need to set a session secret.

  DATABASE_NAME=your_db_name
  DATABASE_USERNAME=your_db_username
  DATABASE_PASSWORD=your_db_password
  DATABASE_HOST=your_db_ip
  DATABASE_PORT=your_db_port
  SESSION_SECRET=randomstringabcde123

Create Singleton Sequelize Object

You only need to create a singleton connection object to the same database and use the same object anywhere you need it.

utils/sequelize-singleton.js

  require('dotenv').config();

  const Sequelize = require('sequelize');

  const self = module.exports;
  let sequelize;

  /**
   * Construct a singleton sequelize object to query the database
   * 
   * @returns {object} - Sequelize object
   */
  exports.initialize = () => {
    if (!sequelize) {
const dbName = process.env.DATABASE_NAME;
const dbUsername = process.env.DATABASE_USERNAME;
const dbPassword = process.env.DATABASE_PASSWORD;
const dbHost = process.env.DATABASE_HOST;
const dbPort = process.env.DATABASE_PORT;
return new Sequelize(dbName, dbUsername, dbPassword, { host: dbHost, port: dbPort, dialect: 'postgres', }); } return sequelize; }; module.exports = self.initialize();

Create User Model

The user data is stored in database, so you need to define the model for it. We use bcrypt to hash the password which is done inside beforeSave hook. There is also compareMethod prototype function used to check whether a given password matches the hashed value stored in database.

models/User.js

  const _ = require('lodash');
  const bcrypt = require('bcrypt');
  const Bluebird = require('bluebird');
  const Sequelize = require('sequelize');
  
  const sequelize = require('../singleton/sequelize-singleton');
  
  const mappings = {
    userId: {
      type: Sequelize.UUID,
      primaryKey: true,
      defaultValue: Sequelize.DataTypes.UUIDV4,
    },
    name: {
      type: Sequelize.TEXT,
      allowNull: false,
    },
    email: {
      type: Sequelize.TEXT,
      allowNull: false,
      unique: true,
    },
    password: {
      type: Sequelize.TEXT,
      allowNull: false,
    },
  };
  
  const User = sequelize.define('User', mappings, {
    indexes: [
      {
        name: 'user_userId_index',
        method: 'BTREE',
        fields: ['userId'],
      },
      {
        name: 'user_email_index',
        method: 'BTREE',
        fields: ['email'],
      },
      {
        name: 'user_role_index',
        method: 'BTREE',
        fields: ['role'],
      },
      {
        name: 'user_status_index',
        method: 'BTREE',
        fields: ['status'],
      },
    ],
  });
  
  User.prototype.comparePassword = function (password) { // eslint-disable-line func-names
    return Bluebird.resolve()
      .then(() => bcrypt.compareSync(password, this.password))
      .catch((err) => {
        console.log(err);
  
        return false;
      });
  };
  
  User.hook('beforeSave', (user) => {
    user.name = _.trim(user.name);
  
    if ((user.previous('password') !== user.password) && (!_.isEmpty(user.password))) {
      const salt = bcrypt.genSaltSync(10);
      const hash = bcrypt.hashSync(user.password, salt);
      user.password = hash;
    }
  
    return user;
  });
  
  exports.getMapping = () => mappings;
  
  exports.getModel = () => User;

Create Query Helpers

In order to get user data from database, we need to use some queries. First for getting user by ID (used to deserialize user) and the other is for getting user by email (used on login).

queries/user.js

  const User = require('../models/User').getModel();

  exports.getUserById = userId => User.findOne({
    where: { userId },
  });

  exports.getUserByEmail = email => User.findOne({
    where: { email },
  });

Create Passport.js Configuration.

To use Passport.js, we need to create the configuration by implementing required methods (serializeUser and deserializeUser) and defining the authentication strategy we want to use. In this tutorial, we use local authentication using passport-local.

config/passport.js

  const Bluebird = require('bluebird');
  const LocalStrategy = require('passport-local').Strategy;

  const userQueries = require('../queries/user');

  module.exports = (passport) => {
    passport.serializeUser((user, done) => {
      done(null, user.userId);
    });

    passport.deserializeUser((id, done) => Bluebird.resolve()
      .then(async () => {
        const user = await userQueries.getUserById(id);

        done(null, user);
      })
      .catch(done));

    passport.use('local', new LocalStrategy(
      {
        usernameField: 'email',
        passwordField: 'password',
        passReqToCallback: true,
      },
      (req, email, password, done) => Bluebird.resolve()
        .then(async () => {
          const user = await userQueries.getUserByEmail(email);

          if (!user || !await user.comparePassword(password)) {
            return done(null, null);
          }

          return done(null, user);
        })
        .catch(done),
    ));
  };

Create Session Model

As you need to store session in database and use Sequelize as the ORM, you need to create a model for the session. The table has 3 columns:

  • sid (STRING) : The session ID
  • expires (DATE): Time when the token becomes expired
  • data (STRING): Contains cookie data and user ID in JSON format. Example: {"cookie":{"originalMaxAge":2592000000,"expires":"2018-12-22T07:29:53.051Z","httpOnly":true,"path":"/"},"passport":{"user":"4b946762-b931-4bc2-b285-0a7464ad3c3a"}}
  const Sequelize = require('sequelize');

  const sequelize = require('../singleton/sequelizeSingleton');

  /**
   * Sessions table is used to store user session persistently.
   * 
   *
   * Read more on https://www.npmjs.com/package/connect-session-sequelize
   */
  const mappings = {
    sid: {
      type: Sequelize.STRING,
      primaryKey: true,
    },
    expires: Sequelize.DATE,
    data: Sequelize.STRING(50000),
  };

  const Session = sequelize.define('Session', mappings, {
    indexes: [
      {
        name: 'session_sid_index',
        method: 'BTREE',
        fields: ['sid'],
      },
    ],
  });

  exports.getMapping = () => mappings;

  exports.getModel = () => Session;

Add Controllers to Handle Sign In and Sign Out

Of course we need to create a controller to handle sign in and another one to handle sign out. Below is the controller for sign in. It authenticates user by using local strategy (the name of the strategy must match what we've already defined on passport configuration). Every time a user successfully logged in, a new session is regenerated for that user.

routes/sign-in.js

const Bluebird = require('bluebird');
  const passport = require('passport');

  /**
   * Authenticate with passport.
   * @param {Object} req
   * @param {Object} res
   * @param {Function} next
   */
  const authenticate = (req, res, next) => new Bluebird((resolve, reject) => {
    passport.authenticate('local', (err, user) => {
      if (err) {
        return reject(err);
      }

      return resolve(user);
    })(req, res, next);
  });

  /**
   * Login
   * @param {Object} req
   * @param {Object} user
   */
  const login = (req, user) => new Bluebird((resolve, reject) => {
    req.login(user, (err) => {
      if (err) {
        return reject(err);
      }

      return resolve();
    });
  });

  /**
   * Regenerate user session.
   * @param {Object} req
   */
  const regenerateSession = req => new Bluebird((resolve, reject) => {
    req.session.regenerate((err) => {
      if (err) {
        return reject(err);
      }

      return resolve();
    });
  });

  /**
   * Save user session.
   * @param {Object} req
   */
  const saveSession = req => new Bluebird((resolve, reject) => {
    req.session.save((err) => {
      if (err) {
        return reject(err);
      }

      return resolve();
    });
  });

  /**
   * HTTP handler for sign in.
   *
   * @param {Object} req
   * @param {Object} res
   * @param {Function} next
   */
  module.exports = (req, res, next) => Bluebird.resolve()
    .then(async () => {
      const user = await authenticate(req, res, next);

      if (!user) {
        return res.status(401).send('Invalid email or password');
      }

      await login(req, user);
      const temp = req.session.passport;

      await regenerateSession(req);
      req.session.passport = temp;

      await saveSession(req);

      return res.send();
    })
    .catch(next);

The logout controller is very simple, just make the session expired by calling req.logout().

routes/sign-out.js

  /**
   * HTTP handler for sign out.
   *
   * @param {Object} req
   * @param {Object} res
   */
  module.exports = (req, res) => {
    req.logout();
  
    res.send();
  };

Use Passport.js and Session Store on Your Application

Finally, load the Passport.js configurtaion in your application. Then use express-session along with the configuration. To save sessions persistently, you need to add store configuration with a new instance of connect-session-sequelize. As for db, pass the sequelize singletion object, while the table should be filled with the name of the table that stores the sessions - in this tutorial the table name is Session. Don't forget to load the models of Staff and Session beforehand.

app.js

  const app = require('express')();
  const session = require('express-session');

  const passport = require('passport');
  const SequelizeStore = require('connect-session-sequelize')(session.Store);

  const passportConfig = require('./config/passport');
  const sequelize = require('./utils/sequelize-singleton');

  require('./models/Staff');
  require('./models/Session');

  passportConfig(passport);
  app.use(session({
    secret: process.env.SESSION_SECRET,
    resave: false,
    saveUninitialized: false,
    cookie: {
      maxAge: 30 * 24 * 60 * 60 * 1000, // 1 month
    },
    store: new SequelizeStore({
      db: sequelize,
      table: 'Session',
   }),
  }));
  app.use(passport.initialize());
  app.use(passport.session());

  app.post('/sign-in', require('./routes/sign-in'));
  app.post('/sign-out', require('./routes/sign-out'));

Now you can try the code by sending HTTP request to the sign in and sign out endpoints. If it works, you should see user session stored in Sessions table. The session of a user will become expired if the user calls the sign out endpoint.

That's all about how to store session persistently in PostgreSQL database using Passport authentication and Sequelize ORM.