User management for Node.js & MySQL using Sequelize and PassportJS

If you come from the Symfony or Django world, you might be surprised that there isn't a major NodeJS framework that comes bundled with a full-stack user authentication and management system. That shouldn't be a surprise, and common frameworks like Express and NextJS are explicitly lightweight, avoiding some of the downsides of having a fat, opinionated framework (bloat, over-configuration, steep learning curve).

This tutorial covers how to build a user management framework in Node. I had to write this code when building my card game site, but I extracted & generalized it here for easier consumption. If you want to see how it works, feel free to check it out.

Main libraries used:

Node JS (latest)
Sequelize ORM
MySQL
PassportJS

Steps in this tutorial:

Install packages](#Install-packages)
Set up database
Set up app.js
Set up registration functionality
Set up login functionality

Install packages

npm install --save sequelize sequelize-cli mysql passport passport-local express-session express mysql-session crypto

I'll detail the main libraries here.

sequelize: We use Sequelize as the ORM that makes working with MySQL easier. If you use PostGres or MSSQL, this tutorial should work for you too. sequelize-cli is a handy CLI module to run database migrations.
passport: This is for PassportJS, which is a popular node authentication middleware. You should browse its documentation to see how the middleware works. We leverage the passport-local authentication strategy to connect to the backend.
express-session, mysql, & express-mysql-session: Session handling for user authentication, as well as the library that connects express session to MySQL directly (express-session doesn't use sequelize, so there's a bit of redundancy here we have to deal with).
crypto: For salting and encrypting passwords.

Set up database

You'll need two tables: one for your user data and one for your session data.

First set up sequelize:

$ sequelize init

This will generate a number of folders in your app, including /config, /migrations, /models, and /seeders. To learn more about these read the sequelize CLI documentation.

This also creates a config file. In your config file, update the development block with the credentials to your local mysql database server:

{
  "development": {
    "username": "",
    "password": "...",
    "database": "...",
    "host": "...",
    "dialect": "mysql"
  },
  "test": {
    ...
  },
  "production": {
    ...
  }
}

Create Users table

Run the following command:

$ sequelize model:create --name User --attributes first_name:string,last_name:string,email:string,role:enum:\{admin,user\},salt:string,password:string,last_login:date

This generates a migration in /migrations/, and a model, in /models/user.js, for the Users table.

To create the database table, you must now run the migration.

$ sequelize db:migrate

(Note: if you ever want to undo this migration, you can run sequelize db:migrate:undo and it runs the commands listed in the down section of the migration file.)

In your MySQL client, you should see a table named Users in your development database:

You'll see that most of the fields there have been defined in sequelize command above.

There are also a few fields (id, createdAt, updatedAt) that are fields that Sequelize uses when managing data. Leave these there.

Create Sessions table

PassportJS and 'express-session' support non-persistent sessions out of the box. However, in reality you probably want persistent sessions, so we're going to describe how to do that here. If you don't want persistent sessions you can skip this section.

Since express-session is agnostic, you need to pick a backend. As we're using MySQL here, we'll go with express-mysql-session. express-mysql-session works directly with MySQL (not Sequelize) so we have to create the sessions table directly.

In your MySQL client, run the following query:

CREATE TABLE `sessions` (
  `session_id` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  `expires` int(11) unsigned NOT NULL,
  `data` text CHARACTER SET utf8mb4 COLLATE utf8mb4_bin,
  PRIMARY KEY (`session_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Set up app.js

Add the necessary modules to app.js:

var crypto = require('crypto');
var mysql = require('mysql');
var session = require("express-session");
var MySQLStore = require('express-mysql-session')(session);
var passport = require('passport')
  , LocalStrategy = require('passport-local').Strategy;

Create a MySQL connection here and instantiate the session store.

var connection = mysql.createConnection({
  host: process.env.SESSIONSDB_HOST,
  port: process.env.SESSIONSDB_PORT,
  user: process.env.SESSIONSDB_USER,
  password: process.env.SESSIONSDB_PASS,
  database: process.env.SESSIONSDB_DB
});

var sessionStore = new MySQLStore({
  checkExpirationInterval: parseInt(process.env.SESSIONSDB_CHECK_EXP_INTERVAL, 10),
  expiration: parseInt(process.env.SESSIONSDB_EXPIRATION, 10)
}, connection);

Set up the session middleware:

/* Create a cookie that expires in 1 day */
var expireDate = new Date();
expireDate.setDate(expireDate.getDate() + 1);

app.use(session({
  resave: true,
  saveUninitialized: true,
  secret: process.env.SESSIONSDB_SECRET,
  store: sessionStore,
  cookie: { expires: expireDate }
}));

Initialize the PassportJS library:

app.use(passport.initialize());
app.use(passport.session());

Set up the registration route

Now that we're all set up, let's get cooking.

Create a user.js file

First create a user.js file in your routes folder. In addition to requiring the express and router boilerplate, include the following modules:

var crypto = require('crypto');
var passport = require('passport')
  , LocalStrategy = require('passport-local').Strategy;
var User = require('../models').User;

Instantiate the Passport LocalStrategy middleware:

passport.use(new LocalStrategy({
    usernameField: 'email',
    passwordField: 'password'
  },
  async function(email, password, done) {
    var user = await User.findOne(
      { where: {
          email: email
        }
      });
    if (user == null) {
      return done(null, false, { message: 'Incorrect email.' });
    }
    if (!user.validPassword(password)) {
      return done(null, false, { message: 'Incorrect password.' });
    }
    return done(null, user);
  }
));

If you're having any issues, you can also check out the PassportJS Local documentation.

Use the following code to make a register route.


//checks if password has > 8 chars
function isValidPassword(password) {
  if (password.length >= 8) {
    return true;
  }
  return false;
}

//uses a regex to check if email is valid
function isValidEmail(email) {
  var re = /^(([^<>()\[\]\\.,;:\s@"]+(\.[^<>()\[\]\\.,;:\s@"]+)*)|(".+"))@((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\])|(([a-zA-Z\-0-9]+\.)+[a-zA-Z]{2,}))$/;
  return re.test(String(email).toLowerCase());
}

//renders register view
router.get('/register', function(req, res, next) {
  res.render('user/register', { });
});

//handles register POST
router.post('/register', async function(req, res, next) {
  var salt = crypto.randomBytes(64).toString('hex');
  var password = crypto.pbkdf2Sync(req.body.password, salt, 10000, 64, 'sha512').toString('base64');

  if (!isValidPassword(req.body.password)) {
    return res.json({status: 'error', message: 'Password must be 8 or more characters.'});
  }
  if (!isValidEmail(req.body.email)) {
    return res.json({status: 'error', message: 'Email address not formed correctly.'});
  }

  try {
    var user = await User.create({
      first_name: req.body.first_name,
      last_name: req.body.last_name,
      email: req.body.email,
      role: "user",
      password: password,
      salt: salt
    });
  } catch (err) {
    return res.json({status: 'error', message: 'Email address already exists.'});
  }
  if (user) {
    passport.authenticate('local', function(err, user, info) {
      if (err) { return next(err); }
      if (!user) {
        return res.json({status: 'error', message: info.message});
      }
      req.logIn(user, function(err) {
        if (err) { return next(err); }
        return res.json({status: 'ok'});
      });
    })(req, res, next);
  }
});

You'll notice a few things:

We use 64 random bytes for the salt and password, to make a better encrypted string. However, it may be worth researching a bit more to ensure that this tutorial is up to date with the latest security best practices.
The validPassword function currently just checks for a password of 8 characters or more, but you can add additional validation if you'd like. Ditto for validEmail.
You may want to tuck these methods into the User model. Here's a handy way to add instance methods to a Sequelize model.

Add this route to `app.js`

In app.js:

var userRouter = require('./routes/user');

Further down in app.js where your indexRouter is defined, add:

app.use('/user', userRouter);

Add view

Create a register.pug view and add your form. The template I use leverages Bootstrap as the CSS framework, but any will do.

Note: while the User table has fields for First name and Last name, they're not in the view below because Solitaired doesn't collect that info. If you would like to add those fields in the view, feel free to.

extends layout

block content

  div.row
    div.col
      div.message.alert.alert-danger(style="display:none;")
        | #{message}
      form(onsubmit="return false;")
        div.form-group
          label(for="email") Email
          input.form-control.email(type="email", name="email", aria-describedby="emailHelp", autocomplete="username")
          small#emailHelp.form-text.text-muted We'll never share your email with anyone else.
        div.form-group
          label(for="password") Password
          input.form-control.password(type="password", name="password", aria-describedby="passwordHelp", autocomplete="current-password")
          small#passwordHelp.form-text.text-muted Password must be 8 characters or more.
        div.form-group
          button.submit.btn.btn-primary(type="button") Register

script.
  $('#registerModal .submit').on('click', function() {
    $.post('/user/register', {
      email: $('#registerModal .email').val(),
      password: $('#registerModal .password').val()
    }, function(resp) {
      if (resp.status == 'error') {
        $('#registerModal .message').text(resp.message).show();
      } else {
          window.alert('success - you should redirect your user');
        }
      }
    })
  });

Add session serialization code to app.js

This is better explained in the PassportJS documentation, but to be able to access your user data in your app, you need to leverage Passport's serialize and deserialize methods.

Add these methods to app.js:

passport.serializeUser(function(user, done) {
  done(null, {id: user.id, email: user.email, role: user.role});
});

passport.deserializeUser(function(user, done) {
  done(null, {id: user.id, email: user.email, role: user.role});
});

As you can see above, I'm only sharing certain aspects of the user's details. You can add more/less as preferred.

Test

This route should now show up when you go to http://localhost:3000/user/register (or replace the URL with your local server URL). You should see the fields listed. When submitting, we use jQuery to register and log the user in.

You should also be able to view the data in your database. Your user and your new session should be in there. The expiration date in your session should match what you've listed earlier in app.js.

Set up login route

Now that users can register, let's also let them log in.

Add the following code to user.js:

router.get('/login', function(req, res, next) {
  res.render('user/login', { });
});

router.post('/login', function(req, res, next) {
  passport.authenticate('local', function(err, user, info) {
    if (err) { return next(err); }
    if (!user) {
      return res.json({status: 'error', message: info.message});
    }
    req.logIn(user, function(err) {
      if (err) { return next(err); }
      return res.json({status: 'ok'});
    });
  })(req, res, next);
});

Add your login view code:


extends layout

block content

  div.row
    div.col
      div.message.alert.alert-danger(style="display:none;")
        | #{message}
      form(onsubmit="return false;")
        div.form-group
          label(for="email") Email
          input.form-control.email(type="email", name="email", aria-describedby="emailHelp", autocomplete="username")
        div.form-group
          label(for="password") Password
          input.form-control.password(type="password", name="password", autocomplete="current-password")
        div.form-group
          button.submit.btn.btn-primary(type="submit") Login

  script.
    $('#loginModal .submit').on('click', function() {
      $.post('/user/login', {
        email: $('#loginModal .email').val(),
        password: $('#loginModal .password').val()
      }, function(resp) {
        if (resp.status == 'error') {
          $('#loginModal .message').text(resp.message).show();
        } else {
          window.alert('success - you should redirect your user');
        }
      })
    });

Now you have a login route!

To test this, go to /user/login in your browser and you should be able to log in.

Set up logout

This one is easy. In your routes/user.js file, add:

router.get('/logout',
  function(req, res){
    req.logout();
    res.redirect('/');
});

You should be able to log out by going to /users/logout

Wire this up to your layout

Now you need to update your nav to reflect the state of your application, and give your users a way to register, log in, and log out.

Here's one way to do this.

In app.js, pass your user variable to the view template:

app.use(function (req, res, next) {
  res.locals.user_id = req.user.id;
  next();
});

In your layout template (or the file that contains your nav), do something like:

  if user_id
    li.nav-item
      a.mr-2(href="/user/logout") Logout
  else
    li.nav-item
      a.mr-2(href="/user/login") Login
    li.nav-item
      a.mr-2(href="/user/register") Register

What's next?

Now your users are able to register, log in, and log out. You should be able to see these users in your database and sensitive password data is encrypted.

But there's one last thing, which we won't get to in this lesson. Your user management system still needs Forgot password functionality. Given that we've covered a lot in this session we'll leave that for next time.

Blog

User management for Node.js & MySQL using Sequelize and PassportJS

Darshan Somashekar

Main libraries used:

Steps in this tutorial:

Install packages

Set up database

Create Users table

Create Sessions table

Set up app.js

Set up the registration route

Create a user.js file

Add this route to `app.js`

Add session serialization code to app.js

Test

Set up login route

Set up logout

Wire this up to your layout

What's next?

Join Our Newsletter. No Spam, Only the good stuff.

Related

User management for Node.js & MySQL using Sequelize and PassportJS

Darshan Somashekar

Main libraries used:

Steps in this tutorial:

Install packages

Set up database

Create Users table

Create Sessions table

Set up app.js

Set up the registration route

Create a user.js file

Add this route to app.js

Add session serialization code to app.js

Test

Set up login route

Set up logout

Wire this up to your layout

What's next?

Join Our Newsletter. No Spam, Only the good stuff.

Related

Add this route to `app.js`