Node.js Login System with Express, JWT & MySQL (Authentication)

In this tutorial we build a Node.js login with Express, JWT and MySQL – a very simple, but powerful Authentication. You can then connect this RestAPI to the frontend in a very flexible way.

This tutorial is the first part of a two-part series on creating a complete login system with Node.js and Vue.js. For this we use Node.js as backend and Vue.js as frontend.

The tutorial is split into two posts so that you are not tied to a specific frontend framework, but can equally use this Node login system we are developing in this post with a Vue.js, React or Angular framework.

As a bonus, I’ll provide you with the complete code in a git repository! 🙂

We are creating a Node.js application. For this we have a MySQL database where our user data is stored. Alternatively, you can replace the database with, for example, a MongoDB database. For authentication, we then need to query this data and can use the JWT (JSON Web Token) package to open a session for the user.

Here you can also find the tutorial in video form:

What is a Rest API?

A Rest API represents the interface between server and client. Using simple HTTP requests, we can reach the server and perform programmed functions, such as authenticating a user with the associated password.

If you are not familiar with Node.js yet, you can take a look at this Node.js beginner tutorial. After that, you’ll have all the basics down and can start working on your Node.js login system.

1. Install dependencies

After you initialize a new project with npm init, we can install the required dependencies. We need the following modules:

Package	Description
express	With this we create our own web server for our Rest API
mysql	To read/write to our database
uuid	To create IDs for later users
bcryptjs (Achtung: nicht bcrypt)	To encrypt/decrypt the passwords
jsonwebtoken	To handle the user sessions
cors	So that we can call the Rest API from our website.
dotenv	Manage environment variables

We install these modules using the following CLI command npm install express mysql uuid bcryptjs jsonwebtoken cors dotenv.

To give you a better overview of where to store which files, here is a small overview:

├── lib
│   └── db.js
├── middleware
│   └── users.js
├── routes
│   └── router.js
├── .env
└── index.js

2. Create MySQL database schema

As database engine I use XAMPP, so I can host my own database locally. Of course you can also use any other (remote) database.

For our login system we need only one table according to the following scheme:

In the database, our table then looks like this:

So that we can also access this connection through our Node.js application, we create a custom class file that we later include in our router.

// lib/db.js

const mysql = require('mysql');

const connection = mysql.createConnection({
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  database: process.env.DB_NAME,
  password: process.env.DB_PASSWORD,
});

connection.connect();
module.exports = connection;

Security relevant information like passwords, PINs or tokens should never be stored directly in the code! Therefore, you create environment variables for such data. These are variables that can be configured differently in the different stages of your application and are never stored in a repository.

Now, to fill these placeholders with the correct content, we create the .env file. Note the point at the beginning. There we create our four used variables:

DB_HOST=localhost
DB_USER=root
DB_PASSWORD=******
DB_NAME=node-login-tutorial

If you manage your code in a repository, be sure to include the .env file in your .gitignore.

3. Set up Express Router and create routes

Our initial file is index.js and includes starting our web server and including the routes we define in routes/router.js.

// index.js

const express = require('express');
const app = express();
const cors = require('cors');

require('dotenv').config();
const PORT = process.env.PORT || 3000;

app.use(express.json());
app.use(cors());

const router = require('./routes/router.js');
app.use('/api', router);

app.listen(PORT, () => console.log(`Server running on port ${PORT}`));

In router.js we define our routes and then wrap the logic in it. The reason why we use an extra file here is for clarity. If your application eventually has 20 or even more routes, there will be a big mess in the index.js. That’s why we outsource our routes.

// routes/router.js

const express = require('express');
const router = express.Router();

const bcrypt = require('bcryptjs');
const uuid = require('uuid');
const jwt = require('jsonwebtoken');

const db = require('../lib/db.js');
const userMiddleware = require('../middleware/users.js');

router.post('/sign-up', (req, res, next) => {});

router.post('/login', (req, res, next) => {});

router.get('/secret-route', (req, res, next) => {
  res.send('This is the secret content. Only logged in users can see that!');
});

module.exports = router;

Here we register the route /api/sign-up to register and /api/login to log in. Also we have the route /api/secret-route, you should be able to call this only if you are logged in. Currently every user can call this route. But more about that later.

In line 10 we also include our file for the database connection.

We also include the file ../middleware/users.js, which contains the code to verify the requests. That means, we check there if the user has entered a password and if the username complies with the guidelines. We later switch these queries as middleware into the call of our routes.

4. Create middleware (validation)

A middleware is a small program that is inserted between two components. In this case we have a middleware between our request and the actual registration, which validates the entered data. For the registration, a validation can look like this:

// middleware/users.js

const jwt = require("jsonwebtoken");

module.exports = {
  validateRegister: (req, res, next) => {
    // username min length 3
    if (!req.body.username || req.body.username.length < 3) {
      return res.status(400).send({
        message: 'Please enter a username with min. 3 chars',
      });
    }
    // password min 6 chars
    if (!req.body.password || req.body.password.length < 6) {
      return res.status(400).send({
        message: 'Please enter a password with min. 6 chars',
      });
    }
    // password (repeat) must match
    if (
      !req.body.password_repeat ||
      req.body.password != req.body.password_repeat
    ) {
      return res.status(400).send({
        message: 'Both passwords must match',
      });
    }
    next();
  }
};

When calling our /sign-up route, we want our middleware to be executed. For this we change the marked line as follows:

// routes/router.js

const express = require('express');
const router = express.Router();

const userMiddleware = require('../middleware/users.js');

router.post('sign-up', userMiddleware.validateRegister, (req, res, next) => {});

router.post('login', (req, res, next) => {});

module.exports = router;

5. Create Express Routes

5.1 Implement Register Route

To add a new user to the database, we need to check if the username does not already exist. If the user exists, an error message is displayed. If the user does not exist yet, by means of our module bcryptjs, the entered password is hashed (encrypted) and then all data is entered into the database.

// routes/router.js

router.post('/sign-up', userMiddleware.validateRegister, (req, res, next) => {
  db.query(
    'SELECT id FROM users WHERE LOWER(username) = LOWER(?)',
    [req.body.username],
    (err, result) => {
      if (result && result.length) {
        // error
        return res.status(409).send({
          message: 'This username is already in use!',
        });
      } else {
        // username not in use
        bcrypt.hash(req.body.password, 10, (err, hash) => {
          if (err) {
            return res.status(500).send({
              message: err,
            });
          } else {
            db.query(
              'INSERT INTO users (id, username, password, registered) VALUES (?, ?, ?, now());',
              [uuid.v4(), req.body.username, hash],
              (err, result) => {
                if (err) {
                  return res.status(400).send({
                    message: err,
                  });
                }
                return res.status(201).send({
                  message: 'Registered!',
                });
              }
            );
          }
        });
      }
    }
  );
});

If the user is successfully entered, the status code 201 (“created”) is returned and the function call is terminated.

Ever heard of double opt-in? This is when confirmation emails are sent to verify a signup. In this tutorial you will learn how to implement double opt-in in your Node.js application (building on this tutorial).

5.2 Implement Login Route

Besides the registration process, we have a login route to log in for already registered users. Here the matching database entry is searched based on the username. Then the entered one is checked with the encrypted password from the database using jwt.compare(). A short SQL query in line 45 sets the last login date/time to the current value.

// routes/router.js

[...]
router.post('/login', (req, res, next) => {
  db.query(
    `SELECT * FROM users WHERE username = ?;`,
    [req.body.username],
    (err, result) => {
      if (err) {
        return res.status(400).send({
          message: err,
        });
      }
      if (!result.length) {
        return res.status(400).send({
          message: 'Username or password incorrect!',
        });
      }

      bcrypt.compare(
        req.body.password,
        result[0]['password'],
        (bErr, bResult) => {
          if (bErr) {
            return res.status(400).send({
              message: 'Username or password incorrect!',
            });
          }
          if (bResult) {
            // password match
            const token = jwt.sign(
              {
                username: result[0].username,
                userId: result[0].id,
              },
              'SECRETKEY',
              { expiresIn: '7d' }
            );
            db.query(`UPDATE users SET last_login = now() WHERE id = ?;`, [
              result[0].id,
            ]);
            return res.status(200).send({
              message: 'Logged in!',
              token,
              user: result[0],
            });
          }
          return res.status(400).send({
            message: 'Username or password incorrect!',
          });
        }
      );
    }
  );
});

In line 33 and 34 we pass variables that we want to “store” in the JWT token. This gives us access to these variables in the protected routes.

In line 36 you have to pass a key which is used to generate the JWT token, this key is important for the verification later. Here you can enter any string.

Furthermore you can define in line 37 how long the token should be valid. Values like “1h” or “3m” are valid here. The individual values and parameters you can also read in the documentation.

If the password is wrong or the username does not exist, an error message is displayed. This message is intentionally identical, because otherwise a potential attacker can get information about the existence of individual user profiles.

On successful login the user object and the token generated by JWT is returned. This token is important for all routes where you should be logged in. In part 2 (Vue.js Frontend) you will learn how to pass it with every request. If you test the Rest API with Postman, you can specify the token with the key “Authorization” as value according to the following syntax: “Bearer KEY”.

5.3 Protect routes with login

The main routes are now ready. We can add new users and log in with existing accounts. Now we still want to protect routes. This means that only logged in users have access to them.

To do this, we create a new middleware in our users.js. Here, the token is taken from the request header and verified by JWT.

// middleware/users.js

  [...]
  isLoggedIn: (req, res, next) => {
    if (!req.headers.authorization) {
      return res.status(400).send({
        message: 'Your session is not valid!',
      });
    }
    try {
      const authHeader = req.headers.authorization;
      const token = authHeader.split(' ')[1];
      const decoded = jwt.verify(token, 'SECRETKEY');
      req.userData = decoded;
      next();
    } catch (err) {
      return res.status(400).send({
        message: 'Your session is not valid!',
      });
    }
  }
  [...]

In the marked line you have to enter the same key you already used to generate the JWT.

Now, to protect a route, you simply include this middleware when calling the route as follows:

// routes/router.js

[...]
router.get('/secret-route', userMiddleware.isLoggedIn, (req, res, next) => {
  console.log(req.userData);
  res.send('This is the secret content. Only logged in users can see that!');
});
[...]

In req.userData are the data that we have stored in the JWT key (in this case username and userId). This allows us to read user-defined values from the database based on the userId, e.g. for protected routes.

When developing a Rest API, errors can occur – this is completely normal. Often some edge cases are not considered. I have summarized how you can select these test cases and test them automatically here: Test Rest API correctly.

View complete code in Git repository

6. Deploy Node Login System

Great, your Node.js login with Express, JWT and MySQL is ready. Now you can build a nice frontend and connect your backend.

How you can then deploy your finished app – i.e. make it live – is described here.

➡️ Click here for part 2: Vue.js Login System with Vuex & Axios