How To Make Node.js MySQL Login & Signup System

In this step-by-step tutorial you will learn how to create a simple user signup and login system using Node.js with express.js framework and MySQL database.

Step 1: Setup MySQL Database

So first create a database and the database table for this project. Here is the MySQL database information:

  • Database name: node_login_system
  • Table name: users
  • “users” table columns: id, name, email, password

Use the following SQL code to create the users table and its columns:

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `email` varchar(50) NOT NULL,
  `password` varchar(70) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

Step 2: Create the Node Login & Signup Project Folder

Now create a new folder called node-login-signup-app, this is our project folder. Go inside the folder and Initialize npm:

mkdir node-login-signup-app
cd node-login-signup-app
npm init -y

Here is the structure of the folder:

node-login-signup-app/
├── node_modules/
├── views/
│   ├── home.ejs
│   ├── login.ejs
│   └── signup.ejs
├── app.js
├── controllers.js
├── database.js
├── routes.js
└── package.json

Step 3: Install the Necessary Node Packages

Now install the required node packages that we will use to build this node login and signup app:

npm i express express-session express-validator ejs bcrypt mysql2
  • express
  • express-session: For handling sessions.
  • express-validator: For user input validation.
  • ejs: Template engine.
  • bcrypt: Password Hashing.
  • mysql2: MySQL client for Node.js.

Step 4: Make MySQL Database Connection “database.js”

Create database.js for the establish connection with the databse:

import mysql from "mysql2";

// Create a connection pool to manage database connections
const connection = mysql.createPool({
    host: "localhost",      // MySQL server host
    user: "root",           // MySQL username
    password: "",           // MySQL password
    database: "node_login_system", // Name of the database
});

// Export the connection object with promise support
export default connection.promise();

Step 5: Define login, signup, home, logout Routes “routes.js”

The following code in the routes.js file sets up routes for signup, login, home, and logout pages using Express Router. It includes validation for signup and login forms using express-validator.

import { Router } from "express";
import { body } from "express-validator";
import controllers from "./controllers.js";
import connection from "./database.js";

// Initialize Express router
const routes = Router({ strict: true });

// Destructure controllers
const { isNotLoggedIn, signup, login, home } = controllers;

// Default route redirects to login page
routes.get("/", (req, res) => {
    res.redirect("/login");
});

// Routes for signup, login, and home pages
routes.get("/signup", isNotLoggedIn, signup.get);
routes.get("/login", isNotLoggedIn, login.get);
routes.get("/home", home);

// Logout route
routes.get("/logout", (req, res, next) => {
    req.session.destroy((err) => {
        if (err) {
            return next(err);
        }
        res.redirect("/login");
    });
});

// Signup route with validation
routes.post(
    "/signup",
    isNotLoggedIn,
    [
        body("username")
            .trim()
            .not()
            .isEmpty()
            .withMessage("Username must not be empty.")
            .isLength({ min: 3 })
            .withMessage("Username must be at least 3 characters long.")
            .unescape()
            .escape(),
        body("email", "Invalid email address")
            .trim()
            .toLowerCase()
            .isEmail()
            .custom(async (email) => {
                let sql = "SELECT * FROM `users` WHERE `email`=?";
                const [row] = await connection.execute(sql, [email]);
                if (row.length)
                    throw new Error(
                        "A user already exists with this email address."
                    );
                return true;
            }),
        body("password")
            .trim()
            .isLength({ min: 4 })
            .withMessage("Password must be at least 4 characters long."),
    ],
    signup.post
);

// Login route with validation
routes.post(
    "/login",
    isNotLoggedIn,
    [
        body("email", "Invalid email address")
            .trim()
            .toLowerCase()
            .isEmail()
            .custom(async (email, { req }) => {
                let sql = "SELECT * FROM `users` WHERE `email`=?";
                const [row] = await connection.execute(sql, [email]);
                if (row.length === 0)
                    throw new Error("Your email is not registered.");
                req.body.user = row[0];
                return true;
            }),
        body("password")
            .trim()
            .isLength({ min: 4 })
            .withMessage("Password must be at least 4 characters long."),
    ],
    login.post
);

// Export the routes
export default routes;

Step 6: Define Route Handlers “controllers.js”

// Import necessary modules
import bcrypt from "bcrypt";
import { validationResult } from "express-validator";
import connection from "./database.js";

// Customize validation result formatter
const validation_result = validationResult.withDefaults({
    formatter: (error) => error.msg,
});

// Export controller functions
export default {
    // Middleware to check if user is not logged in
    isNotLoggedIn: (req, res, next) => {
        if (typeof req.session.user_id !== "undefined") {
            return res.redirect("/home");
        }
        next();
    },

    // Signup route handlers
    signup: {
        // GET request handler for signup page
        get: (req, res) => {
            res.render("signup");
        },

        // POST request handler for signup form submission
        post: async (req, res, next) => {
            // Validate form input
            const errors = validation_result(req).mapped();
            if (Object.keys(errors).length) {
                return res.render("signup", {
                    validation_errors: JSON.stringify(errors),
                    prev_values: req.body,
                });
            }

            try {
                // Hash password
                const { username, email, password } = req.body;
                const saltRounds = 10;
                const hashPassword = await bcrypt.hash(password, saltRounds);

                // Insert user data into database
                await connection.execute(
                    "INSERT INTO `users` (`name`,`email`,`password`) VALUES (?,?,?)",
                    [username, email, hashPassword]
                );

                res.render("signup", { success: true });
            } catch (err) {
                next(err);
            }
        },
    },

    // Login route handlers
    login: {
        // GET request handler for login page
        get: (req, res) => {
            res.render("login");
        },

        // POST request handler for login form submission
        post: async (req, res, next) => {
            // Validate form input
            const errors = validation_result(req).mapped();
            if (Object.keys(errors).length) {
                return res.render("login", {
                    validation_errors: JSON.stringify(errors),
                    prev_values: req.body,
                });
            }

            try {
                // Verify user credentials
                const { user, password } = req.body;
                const verifyPassword = await bcrypt.compare(
                    password,
                    user.password
                );
                if (!verifyPassword) {
                    return res.render("login", {
                        validation_errors: JSON.stringify({
                            password: "Incorrect Password",
                        }),
                        prev_values: req.body,
                    });
                }

                // Set user session
                req.session.user_id = user.id;
                res.redirect("/home");
            } catch (err) {
                next(err);
            }
        },
    },

    // Home route handler
    home: async (req, res, next) => {
        // Check user session
        const user_id = req.session.user_id;
        if (typeof user_id === "undefined") {
            return res.redirect("/logout");
        }
        try {
            // Retrieve user data from database
            let sql = "SELECT * FROM `users` WHERE `id`=?";
            const [row] = await connection.execute(sql, [user_id]);
            if (row.length) {
                const user = row[0];
                return res.render("home", {
                    name: user.name,
                    email: user.email,
                });
            }
            // Redirect to logout if user data not found
            res.redirect("/logout");
        } catch (err) {
            next(err);
        }
    },
};

This code implements a basic authentication system using bcrypt for password hashing and Express.js for handling routes. Here’s a breakdown:

  1. Imports: It imports necessary modules such as bcrypt for password hashing, express-validator for form validation, and connection for database access.
  2. Validation Result Formatter: It customizes the validation result formatter to simplify error handling.
  3. Middleware (isNotLoggedIn): Checks if the user is not logged in before allowing access to certain routes. If the user is already logged in, it redirects to the home page.
  4. Signup Routes:
    • GET Handler: Renders the signup form.
    • POST Handler: Handles form submission, validates input, hashes the password, and stores user data into the database.
  5. Login Routes:
    • GET Handler: Renders the login form.
    • POST Handler: Handles login form submission, validates input, compares hashed password with stored hash in the database, and sets user session upon successful login.
  6. Home Route: Retrieves user data based on session information from the database and renders the home page with user information. If the session is not valid, it redirects to the logout page.
  7. Error Handling: Errors are handled using Express.js middleware, forwarding any encountered errors to the next middleware for processing.

Overall, this code provides a secure authentication system with user registration, login, and session management functionalities.

Step 7: Create Signup, Login & Home Views for this Node App

Note: create all the views in the views folder.

1. signup.ejs: Contains the user Signup form with JS code that displays the validation error message. You can also see prev_value function to retrieve previous input values based on field name.

<% 
const prev_value = (field_name) => {
    if(typeof prev_values !== 'undefined'){
        return prev_values[field_name] ? prev_values[field_name] : "";
    }
    return "";
}
%>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>User Sign Up</title>
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/water.css@2/out/water.css">
    <style>
        .err-msg {
            color: #ff4137;
        }
        .err-input {
            border: 1px solid #ff4137 !important;
        }
    </style>
</head>
<body>
    <h1>Sign Up</h1>
    <form action="" method="POST">
        <label for="userName">Name: <span class="err-msg username"></span></label>
        <input type="text" name="username" required id="userName" placeholder="Enter your name" value="<%- prev_value("username"); %>">
        <label for="userEmail">Email: <span class="err-msg email"></span></label>
        <input type="email" name="email" required id="userEmail" placeholder="Enter your email" value="<%- prev_value("email"); %>">
        <label for="userPass">Password: <span class="err-msg password"></span></label>
        <input type="password" name="password" required id="userPass" placeholder="Enter new password" value="<%- prev_value("password"); %>">
        <% if(typeof success !== "undefined" && success){ %>
        <p>You have successfully signed up. <a href="/login">Login</a></p>
        <% } else{ %><br><% } %>
        <button type="submit">Sign Up</button> <a href="/login">Login</a>
    </form>
    <% if(typeof validation_errors !== "undefined"){ %>
    <script>
        let spanItem;
        let item;
        const errs = <%- validation_errors %>;

        for (const property in errs) {
            spanItem = document.querySelector(`.err-msg.${property}`);
            item = document.querySelector(`[name="${property}"]`);
            item.classList.add('err-input');
            spanItem.innerText = errs[property];
        }
    </script>
    <% } %>
</body>
</html>

2. login.ejs: This code represents a login form HTML template with dynamic rendering of error messages and input values from the server-side.

<% 
const prev_value = (field_name) => {
    if(typeof prev_values !== 'undefined'){
        return prev_values[field_name] ? prev_values[field_name] : "";
    }
    return "";
}
%>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>User Login</title>
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/water.css@2/out/water.css">
    <style>
        .err-msg {
            color: #ff4137;
        }
        .err-input {
            border: 1px solid #ff4137 !important;
        }
    </style>
</head>
<body>
    <h1>Login</h1>
    <form action="" method="POST" novalidate>
        <label for="userEmail">Email: <span class="err-msg email"></span></label>
        <input type="email" name="email" required id="userEmail" placeholder="Enter your email" value="<%- prev_value("email"); %>">
        <label for="userPass">Password: <span class="err-msg password"></span></label>
        <input type="password" name="password" required id="userPass" placeholder="Enter your password" value="<%- prev_value("password"); %>"><br>
        <button type="submit">Login</button> <a href="/signup">Sign Up</a>
    </form>
    <% if(typeof validation_errors !== "undefined"){ %>
    <script>
        let spanItem;
        let item;
        const errs = <%- validation_errors %>;

        for (const property in errs) {
            spanItem = document.querySelector(`.err-msg.${property}`);
            item = document.querySelector(`[name="${property}"]`);
            item.classList.add('err-input');
            spanItem.innerText = errs[property];
        }
    </script>
    <% } %>
</body>
</html>

3. home.ejs: This is the home page of the logged in users. user cannot access this page without logging in.

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Home</title>
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/water.css@2/out/water.css">
    <style>
        div{
            background-color: rgba(0, 0, 0, .03);
            padding: 20px;
            border-radius: 20px;
        }
        h2{
            margin-top: 0;
        }
    </style>
</head>
<body>
    <h1>Profile page:</h1>
    <div>
        <img src="https://api.dicebear.com/6.x/bottts/png?seed=<%- name %>" alt="<%- name %>" width="128" height="128">
        <h2><%- name %></h2>
        <p><%- email %></p>
        <a href="/logout">Logout</a>
    </div>
</body>
</html>

Step 8: Create the Node Express Server “app.js”

The following code in the app.js initializes an Express server, configures session management, handles request data parsing, defines routes, manages errors, and establishes a connection to a database. It’s a basic setup for a web application backend.

// Import necessary modules
import express from "express";
import session from "express-session";
import path, { dirname } from "path";
import { fileURLToPath } from "url";
import connection from './database.js' // Import database connection module
import routes from "./routes.js"; // Import routes module

// Resolve file paths
const __filename = fileURLToPath(import.meta.url);
const __dirname = dirname(__filename);

// Create Express app instance
const app = express();
const PORT = process.env.PORT || 3000; // Set port

// Configure view engine and views directory
app.set('view engine', 'ejs');
app.set('views', path.join(__dirname, 'views' ));

// Configure session middleware
app.use(session({
    secret: 'your secret key', // Secret key for session encryption
    resave: false,
    saveUninitialized: true,
  }))

// Parse URL-encoded request bodies
app.use(express.urlencoded({ extended: false }));

// Mount application routes
app.use(routes);

// Global error handling middleware
app.use((err, req, res, next) => {
    err.statusCode = err.statusCode || 500; // Set status code
    err.message = err.message || 'Internal Server Error'; // Set error message
    res.status(err.statusCode).send(err.message); // Send error message
});

// Establish database connection and start server
connection.getConnection().then(() => {
    app.listen(PORT, () => console.log(`Server is running on port ${PORT}`)); // Log server start
}).catch(err => {
    console.log(err.message); // Log database connection error
});

Step 9: Test the Node Express user Login & Signup System

Start the application and test it:

node app.js
Node.js Express MySQL user login and sign up or registration system

Note: This is a session based login application and the default server-side session storage is MemoryStore which is not suitable for production. Instead of storing session data in memory use one of the compatible session-stores to save session data in the database.

Download the Project Source Code From GitHub

Download

Leave a Reply

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

We use cookies to ensure that we give you the best experience on our website. Privacy Policy