Examples
Examples

Express.js - MySQL Login & Logout with Session


In this tutorial, we will see how to implement login, logout, and session management in an Express.js application using MySQL.

Setup Project

Initialize the project

mkdir my-app
cd my-app
npm init -y

Install the required dependencies

  • hbs : A templating engine for rendering views with Handlebars syntax in Express.js.
  • mysql : To install the mysql package to connect to the database and perform queries.
  • body-parser : body-parser is middleware in Express.js. It processes incoming request data, such as form submissions or JSON, and makes it available in req.body.
  • express-session : This allows you to store user-specific data (such as login status) across multiple HTTP requests.

Install the dependencies using the command is given below

npm install hbs mysql body-parser express-session

Create views Folder

Inside the my-app directory, create a views folder.

mkdir views

Folder Structure

Here’s the structure of the project

Express MySQL Crud Folder Structure

Creating the Database and Table

Execute the following SQL query to create a table named users inside your MySQL database named db_sample.

CREATE TABLE  `db_sample`.`users` (
  `uid` int(11) NOT NULL AUTO_INCREMENT,
  `uname` varchar(150) NOT NULL,
  `upass` varchar(150) NOT NULL,
  PRIMARY KEY (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

Add Sample User Name and Password

After creating the users table, we need to add sample user details for login. Select the "Insert" tab and enter 'ram' in the uname field and '123' in the upass field then click the 'Go' button.

Express MySQL Login logout session table insert data

Setting Up an Express.js Server

This Express app handles user authentication with MySQL, session-based login management, and dynamic Handlebars (hbs) views, ensuring secure and efficient access control.

index.js 
const express = require('express');
const bodyParser = require('body-parser');
const mysql = require('mysql');
const session = require('express-session');
const path = require('path');
const hbs = require('hbs');

const app = express();

// Middleware to parse incoming form data
app.use(bodyParser.urlencoded({ extended: true }));

// Set up handlebars as the view engine
app.set('view engine', 'hbs');

// Set the location of the views
app.set('views', path.join(__dirname, 'views'));

// Session Middleware
app.use(
    session({
      secret: "Secret123",
      resave: false,
      saveUninitialized: true,
    })
);

// MySQL Connection
const db = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: '',
  database: 'db_sample', // Replace with your database name
});

db.connect(err => {
  if (err) throw err;
  console.log('Connected to MySQL');
});

// Middleware to Check If User is Logged In
const checkAuth = (req, res, next) => {
    if (!req.session.user) {
      return res.redirect("/login");
    }
    next();
};

// Home Page (Only Accessible if Logged In)
app.get("/home", checkAuth, (req, res) => {
    res.render("home", { uname: req.session.user.uname });
});

// Login Page
app.get(['/', '/login'], (req, res) => {
    res.render('login');
});

// Login User
app.post("/login", (req, res) => {
    const { uname, upass } = req.body;
    
    // SQL query to check if the username exists
    const sql = "SELECT * FROM users WHERE uname = ? AND upass = ?";
    
    // Execute query with parameters to prevent SQL injection
    db.query(sql, [uname, upass], (err, results) => {
        if (err) {
            console.error("Database error:", err);
            return res.render("login", { error: "Database error!" });
        }

        // Check if a user was found
        if (results.length === 0) {
            return res.render("login", { error: "Invalid credentials!" });
        }

        const user = results[0];

        // Store user session data
        req.session.user = { id: user.uid, uname: user.uname };
        res.redirect("/home"); 
    });
});


// Logout User
app.get("/logout", (req, res) => {
    req.session.destroy((err) => {
      if (err) return res.send("Logout error");
      res.redirect("/login");
    });
});


const PORT = 5000;

app.listen(PORT, () => {
  console.log(`Server is running on http://localhost:${PORT}`);
});

Create hbs Templates

This page displays user login credentials.

views/login.hbs 
<html>
    <head>
        <title>Login Page</title>
        <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css" rel="stylesheet">
    </head>
    <body class="d-flex align-items-center justify-content-center vh-100 bg-light">
        <div class="container">
            <div class="row justify-content-center">
                <div class="col-md-4">
                    <div class="card shadow-sm">
                        <div class="card-body">
                            <h3 class="text-center mb-3">Login Here</h3>
                            <form action="/login" method="POST">
                                <div class="mb-3">
                                    <label class="form-label">Username</label>
                                    <input type="text" class="form-control" name="uname" placeholder="Enter username" required>
                                </div>
                                <div class="mb-3">
                                    <label class="form-label">Password</label>
                                    <input type="password" class="form-control" name="upass" placeholder="Enter password" required>
                                </div>
                                <input type="submit" class="btn btn-primary w-100" value="Login">
                            </form>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </body>
</html>

Home Page

This page displays user login information.

views/home.hbs 
<html>
    <head>
        <title>Home Page</title>
        <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css" rel="stylesheet">
    </head>
    <body class="d-flex flex-column align-items-center justify-content-center vh-100 bg-light">
        <div class="text-center">
            <h1 class="mb-3">Welcome, {{uname}}!</h1>
            <a href="/logout" class="btn btn-danger">Logout</a>
        </div>
    </body>
</html>

Run the Server

Run the server using the command is given below.

node index.js
D:\my-app>node index.js
Server is running on http://localhost:5000
Connected to MySQL

Output

Login Page

Express mysql login logout session login output

Home Page

Express mysql login logout session home output