Using Passport With Sequelize and MySQL


Web Development

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:

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:

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.

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

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:

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:

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:

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:

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.

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 :

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

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:


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:

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

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.

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.

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

Run this to see if all is well:

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:

Now run:

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.

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.

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.

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.

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.

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:

Run this:

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

Sign-up LayoutSign-up LayoutSign-up Layout
Sign-up Layout

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:

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

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

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

Now when you run:

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

LayoutSign-in LayoutSign-in Layout
Layout

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:

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:

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

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.

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

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

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

Now we will import the strategy in server.js.

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

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

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.

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.

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.

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

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:

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.

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

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

Your AuthController.js should look like this:

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:

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

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.

Dashboard LayoutDashboard LayoutDashboard Layout
Dashboard Layout

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.

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

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.

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.

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

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.


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.

Leave a Reply

Your email address will not be published. Required fields are marked *