Using Passport With Sequelize andMySQL

Sequelize is a promise-based Node.js ORM. It can be used with PostgreSQL, MySQL, MariaDB, SQLite, and MSSQL. In this tutorial, we will be implementing authentication for users of a web app. And we will use Passport, the popular authentication middleware for Node, together with Sequelize and MySQL to implement user registration and login.

Getting Started

Make sure you have the following installed on your machine:

• Node


• MySQL

For this tutorial, we will be using Node.js and the Express framework, so we go ahead and start installing what we need.

1. Generate a package.json File

Create a directory for your app. Inside this directory, run this from your terminal or command prompt:

 npm init

This initializes the npm dependency manager. This will present a series of prompts which we'll quickly go through.

• Type the name of your app without spaces for name.


• Press Enter to use the default version.


• Enter a description or leave it blank.


• For entry point, type server.js.


• You can press Enter to accept the default fro the rest of the prompts.

2. Install Dependencies

The major dependencies for this tutorial are:

• Express


• Sequelize


• MySQL


• Passport


• Passport Local Strategy


• Express Session


• Bcryptjs


• Express Handlebars for the views

To install them, from your terminal or command prompt, run the following one after another.

npm install express --save
npm install sequelize --save
npm install sequelize --save
npm install mysql --save
npm install passport --save
npm install passport-local --save
npm install express-session --save
npm install bcryptjs --save
npm install express-handlebars --save
npm install mysql2 --save

If you're using Git for this project, create a .gitignore file and add this line to it:

node_modules

3. Set Up the App

In  the root project directory, we will set up the application and create a new file called server.js. This will be the main file when you start the application.

Inside the server.js file, add the following code:

var express = require('express');

var app = express();

app.get('/', function (req, res) {
    res.send('Welcome to Passport with Sequelize');
});

app.listen(5000, function (err) {
    if (!err)
        console.log("Site is live");
    else console.log(err)
});

The first line assigns the express module to a variable express. We then initialize express and then assign it to the variable app.

Then we make tje app listen on port 5000. You can choose any free port number on your computer.

Next, we call the app.get() express routing function to respond with "Welcome to Passport with Sequelize" when a GET request is made to /.

We canrun the server we just created on our computer with the following command:

node server.js

If you see the text Welcome to Passport with Sequelize when you visit http://localhost:5000/ then congrats! Otherwise, check that you have done everything exactly as it is written above.

Next, we import some modules we need, like passport and express-session.

After the snippet var app = express() we add the following lines:

var passport = require('passport');
var session = require('express-session');

In the first two lines, we import the passport module and the express-session module and assign them to variables. The passport and express-session modules are needed to handle authentication.

We need a module that extracts the entire body of an incoming request and expose it in a format that is easier to work with. In this case, we will use the JSON format. Prior to recent versions of express, you needed to use the body-parser module, however with express versions 4.16+, express has a built-in middleware function that parses incoming requests with JSON payloads and is based on body-parser.

To let our app use the express built-in body parser, we add these lines some spaces below the import lines:

app.use(express.urlencoded({
        extended: true 
        })
    );
app.use(express.json());

Next, we initialize passport and the express session and passport session and add them both as middleware. We do this by adding these lines some spaces after the import line above.

// For Passport
    app.use(session({
        secret: 'keyboard cat',
        resave: true, 
        saveUninitialized:true
        })); // session secret
    app.use(passport.initialize());
app.use(passport.session()); // persistent login sessions

4. Create the Authentication Flow

We will begin to work on the actual authentication now. We'll do this in four steps:

• set up Sequelize with MySQL


• create the user model


• set up views


• write a passport strategy

Step 1: Set Up Sequelize With MySQL

First, we create a Database in MySQL. Give it your preferred name. For the sake of this tutorial, let's create a database named sequelize_passport in MySQL. 

Then we set up configuration to handle DB details.

First, let's import the dot-env module to handle environment variables.

Run this in your root project folder :

npm install --save dotenv

Then we import it in the main server file, server.js, just below the other imports.

var env = require('dotenv').config(); 

Next, we create a file in our project root folder and name it .env and if we're using Git add it to the .gitignore file.

After this, we add our environment to the .env file by adding this line:

NODE_ENV='development'

Then we create a config.json file which will be used by Sequelize to manage different environments.

The first thing to do is to create a folder in the root project directory named app with a folder inside it named config. Inside the config folder, we create a config.json file. This file should be ignored if you are pushing to a repository. To do this, add the following code to your .gitignore:

app/config/config.json

Then, we paste the following code in our config.json file.

{
    "development": {
        "username": "root",
        "password": yourPasswordHere,
        "database": "sequelize_passport",
        "host": "127.0.0.1",
        "dialect": "mysql"
    },
    "test": {
        "username": "",
        "password": null,
        "database": "",
        "host": "",
        "dialect": "mysql"
    },
    "production": {
        "username": "",
        "password": null,
        "database": "",
        "host": "127.0.0.1",
        "dialect": "mysql"
    }
}

Remember to replace the values in the development block above with your database authentication details.

Now it's time to create the models folder.

Inside the app folder, we create a new folder named models and create a new file named index.js in the models folder.

Inside the index.js file, we paste the code snippets below.

"use strict";
var fs = require("fs");
var path = require("path");
var Sequelize = require("sequelize");
var env = process.env.NODE_ENV || "development";
var config = require(path.join(__dirname, '..', 'config', 'config.json'))[env];
var sequelize = new Sequelize(config.database, config.username, config.password, config);
var db = {};
fs
    .readdirSync(__dirname)
    .filter(function (file) {
        return (file.indexOf(".") !== 0) && (file !== "index.js");
    })
    .forEach(function (file) {
        var model = sequelize.import(path.join(__dirname, file));
        db[model.name] = model;
    });
Object.keys(db).forEach(function (modelName) {
    if ("associate" in db[modelName]) {
        db[modelName].associate(db);
    }
});
db.sequelize = sequelize;
db.Sequelize = Sequelize;
module.exports = db;

This file is used to import all the models we place in the models folder, and export them.

To test that all is well, we add this in our server.js file.

//Models
var models = require("./app/models");
//Sync Database
models.sequelize.sync().then(function() {
    console.log('Nice! Database looks fine');
}).catch(function(err) {
    console.log(err, "Something went wrong with the Database Update!");
});

Here, we are importing the models, and then calling the models.sequelize sync function.

Run this to see if all is well:

node server.js

If you get the message "Site is live Nice! Database looks fine", then you have set up sequelize successfully.

If not, please go carefully over the steps above and try to debug the issue with help.

Step 2: Create the User Model

The next thing we are going to do is create the user model, which is basically the user table. This will contain basic user information.

In our models folder, we create a file and name it user.js. The full path for this file should be app/models/user.js.

Open the user.js file and add the following code:

module.exports = function(sequelize, Sequelize) {
    var User = sequelize.define('user', {
        id: {
            autoIncrement: true,
            primaryKey: true,
            type: Sequelize.INTEGER
        },
        firstname: {
            type: Sequelize.STRING,
            notEmpty: true
        },
        lastname: {
            type: Sequelize.STRING,
            notEmpty: true
        },
        username: {
            type: Sequelize.TEXT
        },
        about: {
            type: Sequelize.TEXT
        },
        email: {
            type: Sequelize.STRING,
            validate: {
                isEmail: true
            }
        },
        password: {
            type: Sequelize.STRING,
            allowNull: false
        },
        last_login: {
            type: Sequelize.DATE
        },
        status: {
            type: Sequelize.ENUM('active', 'inactive'),
            defaultValue: 'active'
        }
    });
    return User;
}

Now run:

node server.js

You should see the familiar "Site is live. Nice! Database looks fine." message. This means that our Sequelize models have been synced successfully, and if you check your database you should see a users table with the columns specified present.

Step 3: Set Up Views

In this section, we will set up the views for the client side. First, let's create the view for signup and wire it up.

The first thing to do is import the express-handlebars module which will be used for views in this tutorial. Express HandleBars can be used to render data on the server-side as web pages to the client side.

Add this code snippet to the server.js file.

var exphbs = require('express-handlebars')

Your import block should look like this at this point.

var express = require('express');
var app = express();
var passport = require('passport');
var session = require('express-session');
var env = require('dotenv').config();
var exphbs = require('express-handlebars');

In order to use handlebars in express, we would save the HTML codes with a .hbs extension in the views folder, which we would create soon, in the root directory as handlebars look for the pages in the views folder.

Next, we add the following lines in our server.js file.

//For Handlebars
app.set('views', './app/views');
app.engine('hbs', exphbs.engine({
    extname: '.hbs',
    defaultLayout: false,
    layoutsDir: "views/layouts/"
}));
app.set('view engine', '.hbs');

Now, in our app folder, we create three folders named views, controllers, and routes.

In the views folder, we create a file named signup.hbs and paste the code snippets below in it.

<!DOCTYPE html>
<html>
<head>
    <title>Sign up layout</title>
    <link rel="stylesheet" href="https://code.tutsplus.com/styles.css">
</head>
<body>
    <h2>Passport With Sequelize and MySQL</h2>
    <form id="signup" name="signup" method="post" action="/signup">
        <label for="email">Email Address: </label>
        <input class="text" name="email" type="email" />
        <label for="firstname">First Name: </label>
        <input name="firstname" type="text" />
        <label for="lastname">Last Name: </label>
        <input name="lastname" type="text" />
        <label for="password">Password: </label>
        <input name="password" type="password" />
        <input class="btn" type="submit" value="Sign Up" />
    </form>
</body>
</html>

Then in our controllers folder, we create a new file called authController.js.

In this file, we paste the following controller for the signup route which we will create in a moment.

var exports = module.exports = {};
exports.signup = function(req, res) {
    res.render('signup');
};

Next, we create a route for signup. In the routes folder, we create a new file named auth.js and then, in this file, we import the authController file and define the signup route.

var authController = require('../controllers/authController.js');
module.exports = function(app) {
    app.get('/signup', authController.signup);
};

Now, we'll import this route in our server.js and pass the app as an argument.

In server, after the models import, add these lines:

//Routes
var authRoute = require('./app/routes/auth.js')(app);

Run this:

node server.js

Now, visit http://localhost:5000/signup and you will see the signup form.

Let's repeat the steps for the sign-in form. As before, we'll create a file named signin.hbs in our views folder and paste the following HTML code in it:

<!DOCTYPE html>
<html>
<head>
    <title>Sign in Layout</title>
    <link rel="stylesheet" href="https://code.tutsplus.com/styles.css">
</head>
<body>
    <h2>Passport With Sequelize and MySQL</h2>
    <form id="signin" name="signin" method="post" action="signin">
        <label for="email">Email Address</label>
        <input class="text" name="email" type="text" />
        <label for="password">Password</label>
        <input name="password" type="password" />
        <input class="btn" type="submit" value="Sign In" />
    </form>
</body>
</html>

Then, add a controller for the sign-in in app/controllers/authcontroller.js.

exports.signin = function(req, res) {
    res.render('signin');
};

Then in app/routes/auth.js, we add a route for sign-in like this:

app.get('/signin', authController.signin);

Now when you run:

 node server.js 

and visit http://localhost:5000/signin/, you should see the sign-in form.

The final and major step is writing our passport strategies.

Step 4: Write a Passport Strategy

In app/config, we create a new folder named passport.

Then in our new folder app/config/passport, we create a new file and name it passport.js. This file will contain our passport strategies.

In passport.js, we will use the user model and passport.

First, we import bcryptjs which we need to secure passwords.

var bCrypt = require('bcryptjs');

Then, we add a module.exports block like this:

module.exports = function(passport, user) {
}

Inside this block, we initialize the passport-local strategy, and the user model, which will be passed as an argument. Here's how we do this:

module.exports = function(passport, user) {
    var User = user;
    var LocalStrategy = require('passport-local').Strategy;
}

Then we define our custom strategy with our instance of the LocalStrategy like this:

passport.use('local-signup', new LocalStrategy(
    {
        usernameField: 'email',
        passwordField: 'password',
        passReqToCallback: true // allows us to pass back the entire request to the callback
    },
))

Now we have declared what request fields our usernameField and passwordField (passport variables) are.

The last variable passReqToCallback allows us to pass the entire request to the callback, which is particularly useful for signing up.

After the last comma, we add this callback function.

  function(req, email, password, done) {
}

In this function, we will handle storing a user's details.

First, we add our hashed password generating function inside the callback function.

 var generateHash = function(password) {
     return bCrypt.hashSync(password, bCrypt.genSaltSync(8), null);
};

Then, using the Sequelize user model we initialized earlier as User, we check to see if the user already exists, and if not we add them.

User.findOne({
    where: {
        email: email
    }
}).then(function(user) {
    if (user)
    {
        return done(null, false, {
            message: 'That email is already taken'
        });
    } else
    {
        var userPassword = generateHash(password);
        var data =
            {
                email: email,
                password: userPassword,
                firstname: req.body.firstname,
                lastname: req.body.lastname
            };
        User.create(data).then(function(newUser, created) {
            if (!newUser) {
                return done(null, false);
            }
            if (newUser) {
                return done(null, newUser);
            }
        });
    }
});

User.create() is a Sequelize method for adding new entries to the database. Notice that the values in the data object are gotten from the req.body object which contains the input from our signup form.

Your passport.js should look like this:

//load bcrypt
var bCrypt = require('bcryptjs');
module.exports = function(passport, user) {
    var User = user;
    var LocalStrategy = require('passport-local').Strategy;
    passport.use('local-signup', new LocalStrategy(
        {
            usernameField: 'email',
            passwordField: 'password',
            passReqToCallback: true // allows us to pass back the entire request to the callback
        },
        function(req, email, password, done) {
            var generateHash = function(password) {
                return bCrypt.hashSync(password, bCrypt.genSaltSync(8), null);
            };
            User.findOne({
                where: {
                    email: email
                }
            }).then(function(user) {
                if (user)
                {
                    return done(null, false, {
                        message: 'That email is already taken'
                    });
                } else
                {
                    var userPassword = generateHash(password);
                    var data =
                        {
                            email: email,
                            password: userPassword,
                            firstname: req.body.firstname,
                            lastname: req.body.lastname
                        };
                    User.create(data).then(function(newUser, created) {
                        if (!newUser) {
                            return done(null, false);
                        }
                        if (newUser) {
                            return done(null, newUser);
                        }
                    });
                }
            });
        }
    ));
}

Now we will import the strategy in server.js.

To do this, we add these lines below the routes import in server.js.

//load passport strategies
require('./app/config/passport/passport.js')(passport, models.user);

Your server.js should look like this at this time:

var express = require('express');
var app = express();
var passport = require('passport');
var session = require('express-session');
var env = require('dotenv').config();
var exphbs = require('express-handlebars');
app.use(express.urlencoded({
    extended: true
})
);
app.use(express.json());
// For Passport
app.use(session({
    secret: 'keyboard cat',
    resave: true,
    saveUninitialized: true
})); // session secret
app.use(passport.initialize());
app.use(passport.session()); // persistent login sessions
//For Handlebars
app.set('views', './app/views');
app.engine('hbs', exphbs.engine({
    extname: '.hbs',
    defaultLayout: false,
    layoutsDir: "views/layouts/"
}));
app.set('view engine', '.hbs');
app.get('/', function(req, res) {
    res.send('Welcome to Passport with Sequelize');
});
//Models
var models = require("./app/models");
//Routes
var authRoute = require('./app/routes/auth.js')(app);
//load passport strategies
require('./app/config/passport/passport.js')(passport, models.user);
//Sync Database
models.sequelize.sync().then(function() {
    console.log('Nice! Database looks fine');
}).catch(function(err) {
    console.log(err, "Something went wrong with the Database Update!");
});
app.listen(5000, function(err) {
    if (!err)
        console.log("Site is live");
    else console.log(err);
});

Now we will actually apply the strategy to our /signup route.

Here's how we do that:

First, we go to app/routes/auth.js, and add a route for posting to signup like this.

app.post('/signup', passport.authenticate('local-signup', {
        successRedirect: '/dashboard',
        failureRedirect: '/signup'
    }
));

Since we need passport, we need to pass it to this method. We can import passport in this script or pass it from server.js. Let's do the latter.

Modify the function exported in this file app/routes/auth.js to have passport as a parameter. The code in app/routes/auth.js should look like this after your modification.

var authController = require('../controllers/authcontroller.js');
module.exports = function(app, passport) {
    app.get('/signup', authController.signup);
    app.get('/signin', authController.signin);
    app.post('/signup', passport.authenticate('local-signup', {
            successRedirect: '/dashboard',
            failureRedirect: '/signup'
        }
    ));
};

Then in server.js, we modify the routes import and add passport as an argument like this:

var authRoute = require('./app/routes/auth.js')(app,passport);

Now, go to the signup URL http://localhost:5000/signup/ and try to sign up.

When you try to sign up, you will get an error "Failed to serialize user into session". This is because passport has to save a user ID in the session, and it uses this to manage retrieving the user details when needed.

To solve this, we are going to implement both the serialize and deserialize functions of passport in our app/config/passport/passport.js file.

First we the add the serialize function. In this function, we will be saving the user id to the session.

To do this, we add the following lines below the initialization of the local strategy.

//serialize
passport.serializeUser(function(user, done) {
    done(null, user.id);
});

Next, we implement the deserialize function. Add the function just below the serialize function.

// deserialize user 
passport.deserializeUser(function (id, done) {
        User.findByPk(id).then(function (user) {
            if (user) {
                done(null, user.get());
            } else {
                done(user.errors, null);
            }
        });
});

In the deserialize function above, we use the Sequelize findByPk promise to get the user, and if successful, an instance of the Sequelize model is returned. To get the User object from this instance, we use the Sequelize getter function like this: user.get().

Now run again:

node server.js

And attempt to sign up. Hurray if you got the "Cannot GET /dashboard" message! It means our authentication was successful. Remember we redirected to /dashboard in our passport.authenticate method in routes/auth.js.

Now let's go ahead and add that route. Then, add a middleware to make sure the page can only be accessed when a user is logged into the session.

In our app/views folder, we create a new file named dashboard.hbs and add the following HTML code in it.

<!DOCTYPE html>
<html>
<head>
    <title>Passport with Sequelize</title>
    <link rel="stylesheet" href="https://code.tutsplus.com/styles.css">
</head>
<body>
    <h2>Dashboard</h2>
    <h5>Hurray! you are logged in 🥳.</h5>
</body>
</html>

In routes/auth.js, we add this line inside the module.exports block:

app.get('/dashboard',authController.dashboard);

Next, we go to app/controllers/authController.js and add the dashboard controller.

exports.dashboard = function(req, res) {
    res.render('dashboard');
};

Your AuthController.js should look like this:

var exports = module.exports = {}
exports.signup = function(req, res) {
    res.render('signup');
}
exports.signin = function(req, res) {
    res.render('signin');
}
exports.dashboard = function(req, res) {
    res.render('dashboard');
}

Now, run the app again, and try to sign up with a different email address from the one you used earlier. You'll be appropriately redirected to the /dashboard route.

But /dashboard isn't a protected route, which means even if a user is not logged in, they can see it. We don't want this, so we'll add a /logout route to log the user out, and then protect the route and test what we have done.

In routes/auth.js we add this line:

app.get('/logout',authController.logout);

Then we add the controller in app/controllers/authController.js.

 exports.logout = function(req, res) {
     req.session.destroy(function(err) {
         res.redirect('/');
     });
}

Now run the app again and sign up with a different email address.

After that, visit http://localhost:5000/logout to log the user out. Now visit http://localhost:5000/dashboard.

You'll notice that it is quite accessible. Let's add a custom middleware to protect that route.

To do this, we open app/routes/auth.js and add this function in the module.exports block, below all the other lines of code.

function isLoggedIn(req, res, next) {
    if (req.isAuthenticated())
    
        return next();
        
    res.redirect('/signin');
}

Then we modify the dashboard route handler to look like this:

app.get('/dashboard',isLoggedIn, authController.dashboard);

Now when you run the app again and try to visit the dashboard page and you are not logged in, you should be redirected to the sign-in page.

Whew! It is time to implement the final part: the sign-in.

First, we'll add a new local strategy for sign-in in app/config/passport/passport.js.

//LOCAL SIGNIN
passport.use('local-signin', new LocalStrategy(
    {
        // by default, local strategy uses username and password, we will override with email
        usernameField: 'email',
        passwordField: 'password',
        passReqToCallback: true // allows us to pass back the entire request to the callback
    },
    function(req, email, password, done) {
        var User = user;
        var isValidPassword = function(userpass, password) {
            return bCrypt.compareSync(password, userpass);
        }
        User.findOne({
            where: {
                email: email
            }
        }).then(function(user) {
            if (!user) {
                return done(null, false, {
                    message: 'Email does not exist'
                });
            }
            if (!isValidPassword(user.password, password)) {
                return done(null, false, {
                    message: 'Incorrect password.'
                });
            }
            var userinfo = user.get();
            return done(null, userinfo);
        }).catch(function(err) {
            console.log("Error:", err);
            return done(null, false, {
                message: 'Something went wrong with your Signin'
            });
        });
    }
));

In this strategy, the isValidPassword function compares the password entered with the bCrypt comparison method since we stored our password with bcrypt.

If details are correct, our user will be signed in.

Now go to routes/auth.js and add the route for posting to /signin.

app.post('/signin', passport.authenticate('local-signin', {
        successRedirect: '/dashboard',
        failureRedirect: '/signin'
    }
));

Your routes/auth.js should look like this when you're done.

var authController = require('../controllers/authcontroller.js');
module.exports = function(app, passport) {
    app.get('/signup', authController.signup);
    app.get('/signin', authController.signin);
    app.post('/signup', passport.authenticate('local-signup', {
            successRedirect: '/dashboard',
            failureRedirect: '/signup'
        }
    ));
    app.get('/dashboard', isLoggedIn, authController.dashboard);
    app.get('/logout', authController.logout);
    app.post('/signin', passport.authenticate('local-signin', {
            successRedirect: '/dashboard',
            failureRedirect: '/signin'
        }
    ));
    function isLoggedIn(req, res, next) {
        if (req.isAuthenticated())
            return next();
        res.redirect('/signin');
    }
}

Now run the app and try to sign in. You should be able to sign in with any of the details you used while signing up, and you'll be directed to http://localhost:5000/dashboard/.

Congratulations if you made it to the end of this tutorial! We have successfully used Sequelize and Passport with a MySQL database.

In order to make our application a lot more appealing, we would add a bit of  CSS styling. The codes for styling and full code for this tutorial can be found on GitHub.

Conclusion

This concludes our tutorial on using Passport for authentication of users with Sequelize and MySQL. Sequelize is a really useful ORM for dealing with MySQL when using Node. I have personally found it to be very useful, and you should definitely consider using it in your next Node-MySQL app.

This post has been updated with contributions from Mary Okosun. Mary is a software developer based in Lagos, Nigeria, with expertise in Node.js, JavaScript, MySQL, and NoSQL technologies.