Node.js Express Login with MySQL Example

Set up MySQL database

For the database 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 only need a table according to the following scheme:

Setting up an Express Router and creating routes

Our entry file is the index.js and contains the starting of our web servers and the integration of the routes we define in the file routes/router.js.

// index.js

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

// set up port
const PORT = process.env.PORT || 3000;

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

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

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

In the router.js we define our routes and then pack the logic into them. The reason why we use an extra file here is the clarity. If your application has 20 or more routes at some point, the index.js will be chaotic. That’s why we outsource our routes.

Creating Middleware (Validation)

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

Configure auth key

jsonwebtoken functions such as verify() or sign() use algorithm that needs a secret key (as String) to encode and decode token.

In the app/config folder, create auth.config.js file with following code:

module.exports = {
  secret: "bezkoder-secret-key"
};

You can create your own secret String.

Configure mysql database & sequelize

In the app folder, create config folder for configuration with db.config.js file like this:

Create node.js app

First, we create a folder for our project:

$ mkdir node-js-jwt-auth
$ cd node-js-jwt-auth

Then we initialize the Node.js App with a package.json file:

npm init
name: (node-js-jwt-auth) 
version: (1.0.0) 
description: Node.js Demo for JWT Authentication
entry point: (index.js) server.js
test command: 
git repository: 
keywords: node.js, express, jwt, authentication, mysql
author: bezkoder
license: (ISC)
Is this ok? (yes) yes

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:

Creating controllers for home and auth:

//type the following code in authControllers.js


const mysql = require('mysql');
const { hashSync, genSaltSync, compareSync } = require('bcrypt');
const { sign } = require('jsonwebtoken');
require('dotenv').config();

const pool = mysql.createPool({
    connectionLimit: 100,
    host: "localhost",
    user: "root",
    password: "",
    database: "users",
})


exports.ViewLoginPage = (req, res) => {
    res.render('login', { title: 'Login' })
}
exports.ViewRegisterPage = (req, res) => {
    res.render('register', { title: 'Register' })
}


//Registration of user
exports.RegisterNewPage = (req, res) => {
    const {name, email, password, bio} = req.body;

    //Generating salt for Hashing
    const salt = genSaltSync(10);

    //Hashing the password
    const hashPassword = hashSync(password, salt);


    // Connecting to DB
    pool.getConnection((err, connection) => {
        if (err) {
            throw err;
        }
        else {
            connection.query('select email from users where email = ?', [email], (err, data) => {
                console.log(data)
                if (data.length != 0) {
                    res.render('register', {message: 'Already Registered'})

                }
                else{
                    connection.query('INSERT INTO users SET name = ?, email = ?, bio = ?, password = ?', [name, email, bio, hashPassword], (err, newRegisteredUser) => {
                        if(err){
                            res.render('register', {message: 'Something went wrong, Please try again'})
                        }
                        res.redirect('/auth/login/')
                    })
                }
            })


        }
    });
}

//Login the user
exports.Loginuser = (req,res) => {
    const {email, password} = req.body;
    pool.getConnection((err, connection) => {
        if (err) {
            throw err;
        }
        else {
            connection.query('SELECT * FROM users WHERE email = ?', [email], (err,data) => {
                if(err){
                    res.render('login', {message: 'Email or Password is Incorrect'});
                }
                if(data.length == 0){
                    res.render('login', {message: `Email Doesn't exist, Try to register`})
                }
                else{
                    const checkPassword = compareSync(password, data[0].password);
                    if(checkPassword){

                    //Creating the token for logged in user
                        const userToken = sign({result: data[0].email}, process.env.SECRET_KEY, {
                            expiresIn: '600s'
                        })

                        //Sending the token to user's cookie
                        res.cookie('userToken', userToken, {
                            expires: new Date(Date.now()   600000),
                            httpOnly: true
                        })
                        console.log(userToken)
                        res.redirect('/')
                    };
                    else{
                        res.render('login', {message: 'Email or Password is Incorrect'})
                    };
                };
            });
        };
    });
};

Creating database and table:

Create a database and one table, Table should include field like id, name, email, password and bio. Make sure to crange the name of database and table from the code which I have provided.

Creating server:

Type the following code in app.js.

Further reading

Fullstack CRUD Application:- Vue.js Node.js Express MySQL example- Vue.js Node.js Express MongoDB example- Angular 8 Node.js Express MySQL example- Angular 10 Node.js Express MySQL example- Angular 11 Node.

Deployment:- Deploying/Hosting Node.js app on Heroku with MySQL database- Dockerize Node.js Express and MySQL example – Docker Compose

Install packages

I’ll detail the main libraries here.

Main code:

In app.js start typing the following code, Check the comments for better understanding.

Main libraries used:

• Node JS (latest)
• Sequelize ORM
• MySQL
• PassportJS

More by me

watch how to work with OAuth 2.0 Authentication using NodeJS, MySQL, JWT ReactJS

Overview of node.js express jwt authentication example

We will build a Node.js Express application in that:

This is our Node.js application demo running with MySQL database and test Rest Apis with Postman.

These are APIs that we need to provide:

Project structure

This is directory structure for our Node.js Express application:

– config

• configure MySQL database & Sequelize
• configure Auth Key

– routes

– middlewares

– controllers

– models for Sequelize Models

– server.js: import and initialize necessary modules and routes, listen for connections.

Set up the registration route

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

Setup express web server

In the root folder, let’s create a new server.js file:

Steps in this tutorial:

1. Install packages](#Install-packages)
2. Set up database
3. Set up app.js
4. Set up registration functionality
5. Set up login functionality

Technology

• Express 4.17.1
• bcryptjs 2.4.3
• jsonwebtoken 8.5.1
• Sequelize 5.21.3
• MySQL

Conclusion

➡️ Continue with part 2:Vue.js Login System with Vuex & Axios

That?s it! In the first part we have now written a complete Rest API for validation and session handling for our application. You can use this system for your frontend (no matter if Angular, React or Vue). Thanks for reading! 🙂

Install dependencies

So our Node.js app is ready to install the dependencies. We need the following modules:

We install these modules using the following CLI command:Advertisements

npm install bcryptjs express jsonwebtoken mysql uuid cors

Advertisements

Conclusion

Congratulation!

Today we’ve learned so many interesting things about Node.js Token Based Authentication with JWT – JSONWebToken in just a Node.js Express Rest Api example.Despite we wrote a lot of code, I hope you will understand the overall architecture of the application, and apply it in your project at ease.

You should continue to know how to implement Refresh Token:JWT Refresh Token implementation in Node.js example

If you need a working front-end for this back-end, you can find Client App in the post:- Vue.js JWT Authentication with Vuex and Vue Router- Angular 8 JWT Authentication example with Web Api- Angular 10 JWT Authentication example with Web Api- Angular 11 JWT Authentication example with Web Api- Angular 12 JWT Authentication example with Web Api- React JWT Authentication (without Redux) example- React Hooks: JWT Authentication (without Redux) example- React Redux: JWT Authentication example

Happy learning! See you again.

Initialize sequelize

Now create app/models/index.js with content like this:

Run & test with results

Run Node.js application with command: node server.js

Tables that we define in models package will be automatically generated in MySQL Database.If you check the database, you can see things like this: