Using Authentication With Express & PostgreSQL

5 min readOct 15, 2020

Many times when I’m developing a web app I will need a backend and for that I will use Express. I like Express because it’s a framework that uses JavaScript and is for Node, so that makes it easy to set up. Sometimes though you might need Authentication with your Express app. In this post I’m going to show you how to do that using PostgreSQL for the database and Passport with the local strategy.

The repo for this post is located at https://github.com/craigstroman/express-boilerplate-auth.

To get started if you don’t already have a NPM project run the following command to create it using the defaults:

npm init -y

Next I’m going to install all the dependencies needed. The first set I’m installing are the main dependencies and you can do that by running the following command:

npm --save install bcrypt@5.0.0 body-parser@1.19.0 express@4.17.1 express-session@1.17.1 passport@0.4.1 passport-local1.0.0 pg@8.3.3 pg-hstore@2.3.3 pug@3.0.0 sequelize@5.21.2

This will install those versions of the modules. The next command I’m going to run will install all the dev dependencies I’m using for this. The main one I’m using is Babel, and also Nodemon. I also like using Prettier. Todo this run the following command:

npm --save-dev install @babel/cli @babel/core @babel/node @babel/plugin-transform-modules-commonjs @babel/plugin-transform-runtime @babel/polyfil @babel/preset-env babel-loader dotenv nodemon prettier

Next we have to create a couple configuration files for some of the modules. The first one is for Babel and is the .babelrc file placed in the root of the project. The following is what goes in there:

{
  "presets": ["@babel/preset-env"],
  "plugins": [
     "@babel/plugin-transform-modules-commonjs", 
     "@babel/plugin-transform-runtime"
   ]
}

The next file is for Prettier and is the .prettierrc file that also goes in the root. I put the following in there:

{
  "semi": true,
  "trailingComma": "all",
  "singleQuote": true,
  "printWidth": 110
}

The next thing you should do is create a database in PostgreSQL. I then assigned a user to the database. The next configuration file that has to be setup is the .env file For this I’m using the following entries:

DB_NAME=
DB_USER=
DB_USER_PASSWORD=

For the DB_NAME I called my database express_boilerplate but you can call the database what you want and also use the DB_USER and DB_USER_PASSWORD that you set up for the database. I’ll be using Dotenv to include these.

Next we have to initialize Sequelize which we will be using to connect to the database. To do that run the following command:

sequelize init

That will actually create some folders and a config file in the config folder. I actually like using JavaScript instead of JSON though so delete the config.json file and create a new one named config.js. That way I can use Dotenv to include database passwords etc. The following would go inside

require('dotenv').config();module.exports = {
  development: {
    username: process.env.DB_USER,
    password: process.env.DB_USER_PASSWORD,
    database: process.env.DB_NAME,
    host: '127.0.0.1',
    dialect: 'postgres',
    operatorsAliases: false,
  },
  test: {
    username: process.env.DB_USER,
    password: process.env.DB_USER_PASSWORD,
    database: process.env.DB_NAME,
    host: '127.0.0.1',
    dialect: 'postgres',
    operatorsAliases: false,
  },
  production: {
    username: process.env.DB_USER,
    password: process.env.DB_USER_PASSWORD,
    database: process.env.DB_NAME,
    host: '127.0.0.1',
    dialect: 'postgres',
    operatorsAliases: false,
  },
};

The next thing we have to do is create our model. We can do that with Sequelize by running the following command:

sequelize model:generate --name user --attributes first_name:string,last_name:string,email:string,password:string --underscored true

I like to update the model file to ES6 code by replacing what’s in there with the following:

export default (sequelize, DataTypes) => {
  const user = sequelize.define(
    'user',
    {
      first_name: DataTypes.STRING,
      last_name: DataTypes.STRING,
      email: DataTypes.STRING,
      password: DataTypes.STRING,
    },
    {
      underscored: true,
    },
  );
  user.associate = function (models) {
    // associations can be defined here
  };
  return user;
};

I then also like to update the index.js file within the models folder to the following:

We then have to create the main config file for Passport to use for authentication. For organization I will place that file in the /config folder created by Sequelize and call it auth.js. The contents of that file are the following:

Once you have that done you should be able to use Sequelize to run the migrations and create the table for the database. To do that run the following command:

sequelize db:migrate

For this I will be using Passport for authentication. The next thing we have to do is create our controllers. With Express I usually create a folder called controllers. Within there I create a file called index.js and place the following in there:

Note the res.header in between lines 30 and 33. That will prevent a user from pressing the back button and seeing the dashboard page after they logout. Within this application they will be redirected to the login page instead.

I then create a file called user.js within the /controllers folder and that file contains all the controllers related to signing up, logging in, and logging at. The following is what goes inside there:

You can organize your controller code however you want. I just tend to do it this way so there’s not too much code in one file.

The next thing we have to do is setup our routes. Again with Express I usually create a folder named routes and put them within there. For this project I only need one file and that’s called index.js within the /routes folder. It contains the following:

import { Router } from 'express';
import passport from 'passport';
import { indexPage, loginPage, signupPage, dashboardPage } from '../controllers/index';
import { login, logout, join } from '../controllers/user';const router = new Router();router.route('/').get(indexPage);router.route('/login').get(loginPage);router.route('/signup').get(signupPage);router.route('/dashboard').get(dashboardPage);router.route('/join').post(join);router.route('/signin').post(
  passport.authenticate('local', {
    successRedirect: '/dashboard',
    failureRedirect: '/login',
  }),
  login,
);router.route('/logout').get(logout);export default router;

The next thing we have to do is setup our views. For that I create a folder named views and then place them within there. I’m using Pug to display these views.

The first thing I do is create a base file called base.pug. I’m putting that in a folder name partials and that file contains the following:

Then I create the index.pug file and place the following in there:

extends partials/base.pugblock pageSpecificCSSblock content
  div(class='row')
    div(class='col-md-12')
      h1(class='text-center') Welcome To The Site
  hrdiv(class='row')
    div(class='col-md-12 text-center')
      <a href='/signup'>Sign Up</a> | <a href='/login'>Login</a>block pageSpecificScripts

The next file I create is the dashboard.pug file for displaying when your logged in. I place the following inside there:

extends partials/base.pugblock pageSpecificCSSblock content
  div(class='row')
    div(class='col-md-12')
      h1(class='text-center') You are now logged in
  hrdiv(class='row')
    div(class='col-md-12 text-center') 
      a(href='/logout') Logoutblock pageSpecificScripts

The next file I create is signup.pug for the signup page:

You then have to create a JavaScript file for the form validation. For that I called it validation.js and put it within /public/js. The following is what I put there:

I then create a page for login called login.pug that contains the following:

The final page I have to create is lougout.pug that contains:

extends partials/base.pugblock pageSpecificCSSblock content
  div(class='row')
    div(class='col-md-12')
      h1(class='text-center') You are now logged out.
  hrblock pageSpecificScripts

Then one of the final files we have to create is the main server file which I name index.js and place within the root of the project. That file contains the following:

Once that is done one more final thing you have to do is add the script to start this project up to the package.json file and within the “scripts” object like so:

Once you do that and run “npm start” within the command line you should be able to go to http://localhost:8081 in your browser. Once there you’ll be able to view and use the project.

With the project set up now you should be able to create a user and then login to the site with that user.

Again the repo for this post is available at https://github.com/craigstroman/express-boilerplate-auth.

Using Authentication With Express & PostgreSQL

Written by Craig Stroman