Languages
[Edit]
EN

Node.js / Express.js - simple login / logout site with MySQL

4 points
Created by:
Lillie-Rose-Finnegan
309

In this short article, we would like to show how to create a simple login / logout site that uses Express.js and MySQL database.

The below example sends AJAX/fetch request to REST API and then reloads the site. Backend logic uses sessions to store logged-in or logged-out user states requesting the database for the correct user credentials.

Final effect:

Simple login form with Express.js application.
Simple login form with Express.js application.
Simple site form with Express.js application.
Simple site form with Express.js application.

 

Source code

Below source code runs server over 3000 port.

Github link here.

Clone it, run npm ci install, run npm start and open http://localhost:3000 in the web browser.

Source code is composed of:

  1. common logic that contains inde.js file,
  2. back-end logic that contains REST API to login and logout user with MySQL database,
  3. front-end logic that contains 2 sites: for logged-in user and login form,
  4. configuration + installation + running part where simple package.json file is presented with commands necessary to run the application.

Note: at the end of this article you will find database preparation SQL query.

1. Common logic

index.js file:

const express = require('express');
const session = require('express-session');

const {createRestApi} = require('./backend/api.js');
const {createViewApi} = require('./frontend/api.js');

const port = 3000;           // port used to run server
const app = express();

app.use(express.json());

app.use(
    express.urlencoded({
        extended: true,
    })
);

app.use(
    session({
        name: 'SESSION_ID',      // cookie name stored in the web browser
        secret: 'my_secret',     // helps to protect session
        cookie: {
            maxAge: 30 * 86400000, // 30 * (24 * 60 * 60 * 1000) = 30 * 86400000 => session is stored 30 days
        }
    })
);

createRestApi(app);
createViewApi(app);

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

2. Back-end logic

backend/api.js file:

const mysql = require('mysql');

const database = require('./database.js');

const createRestApi = app => {
    app.post('/user/login', async (request, response) => {
        if (request.session.userId) {
            response.json({result: 'ERROR', message: 'User already logged in.'});
        } else {
            const user = {
                username: request.body.username,
                password: request.body.password
            };
            const connection = await database.createConnection();
            try {
                const result = await connection.query(`
                    SELECT id 
                    FROM users 
                    WHERE 
                            username=${mysql.escape(user.username)}
                        AND password=${mysql.escape(user.password)}
                    LIMIT 1
                `);
                if (result.length > 0) {
                    const user = result[0];
                    request.session.userId = user.id;
                    response.json({result: 'SUCCESS', userId: user.id});
                } else {
                    response.json({result: 'ERROR', message: 'Indicated username or/and password are not correct.'});
                }
            } catch(e) {
                console.error(e);
                response.json({result: 'ERROR', message: 'Request operation error.'});
            } finally {
                await connection.end();
            }
        }
    });
      
    app.get('/user/logout', async (request, response) => {
        if (request.session.userId) {
            delete request.session.userId;
            response.json({result: 'SUCCESS'});
        } else {
            response.json({result: 'ERROR', message: 'User is not logged in.'});
        }
    });
};

module.exports = {
    createRestApi
};

backend/database.js file (wraps API with async/await to simplify usage):

const mysql = require('mysql');

const config = {
    user: 'root',
    password: 'root',
    database: 'my_database',
    host: 'localhost',
    port: 3306,
    multipleStatements: true,
};

const createConnection = () => {
    const connection = mysql.createConnection(config); // replace it with connection pool for better performance

    const query = sql => {
        return new Promise((resolve, reject) => {
            connection.query(sql, (error, result) => {
                if (error) {
                    reject(error);
                } else {
                    resolve(result);
                }
            });
        });
    };

    const end = () => {
        return new Promise((resolve, reject) => {
            connection.end(error => {
                if (error) {
                    reject();
                } else {
                    resolve();
                }
            })
        });
    };

    return new Promise((resolve, reject) => {
        connection.connect(error => {
            if (error) {
                reject(error);
            } else {
                resolve({query, end});
            }
        });
    })
};

module.exports = {
    createConnection
};

3. Front-end logic

frontend/api.js file:

const path = require('path');

const createViewApi = app => {
    app.get('/', async (request, response) => {
        if (request.session.userId) {
            return response.sendFile(path.join(__dirname, 'site.html'));
        } else {
            return response.sendFile(path.join(__dirname, 'login.html'));
        }
    });
};

module.exports = {
    createViewApi
};

frontend/login.html file:

<!doctype html>
<html>
<head>
    <style>
        
        span {
            width: 80px;
            display: inline-block;
        }

    </style>
</head>
<body>
    <script>

        const loginUser = async form => {
            try {
                const response = await fetch('/user/login', {
                    method: 'POST',
                    headers: {
                        'Content-Type': 'application/json',
                    },
                    body: JSON.stringify({
                        username: form.username.value,
                        password: form.password.value
                    })
                });
                const data = await response.json();
                if (data.result === 'SUCCESS') {
                    alert('User login operation success.');
                    location.reload();
                } else {
                    alert('User login operation error (message: ' + data.message + ').');
                }
            } catch(e) {
                console.error(e);
                alert('Request error!');
            }
        };

    </script>
    <form method="post" onsubmit="loginUser(this); return false;">
        <span>Username:</span><input type="test" name="username" value="admin" /><br />
        <span>Password:</span><input type="test" name="password" value="admin" /><br />
        <button>Login</button>
    </form>
</body>
</html>

frontend/site.html file:

<!doctype html>
<html>
<body>
    <script>

        const logoutUser = async () => {
            try {
                const response = await fetch('/user/logout');
                const data = await response.json();
                if (data.result === 'SUCCESS') {
                    alert('User logout operation success.');
                    location.reload();
                } else {
                    alert('User logout operation error (message: ' + data.message + ').');
                }
            } catch(e) {
                console.error(e);
                alert('Request error!');
            }
        };

    </script>
    <div>User already logged in.</div>
    <button onclick="logoutUser()">Logout</button>
</body>
</html>

4. Configuration + installation + running

package.json file:

{
    "name": "example",
    "version": "1.0.0",
    "description": "",
    "main": "index.js",
    "scripts": {
        "test": "echo \"Error: no test specified\" && exit 1",
        "start": "node index.js",
        "dev": "nodemon index.js"
    },
    "keywords": [],
    "author": "",
    "license": "ISC",
    "dependencies": {
        "body-parser": "^1.19.0",
        "express": "^4.17.1",
        "express-session": "^1.17.2",
        "mysql": "^2.18.1",
        "nodemon": "^2.0.12"
    }
}

To install packages run the following command:

npm ci install

To start the application run one of the following commands:

npm start

or:

npx nodemon index.js

 

Database preparation

The application uses root as username with root as password - create that user or change database.js file.

schema+data.sql file:

CREATE DATABASE IF NOT EXISTS `my_database`;
USE `my_database`;

-- database schema

CREATE TABLE IF NOT EXISTS `users` (
    `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `username` varchar(255) NOT NULL,
    `password` varchar(255) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- example data

INSERT INTO `users`
    (`id`, `username`, `password`)
VALUES
    (1, 'admin', 'admin');

 

See also

  1. Node.js / Express.js - send HTML file in response

  2. Node.js / Express.js - add/set session variables

  3. Node.js / Express.js - remove session variable

  4. Node.js - how to connect with MySQL

  5. Node.js - MySQL Select query

Native Advertising
ūüöÄ
Get your tech brand or product in front of software developers.
For more information Contact us
Dirask - we help you to
solve coding problems.
Ask question.

‚̧ԳŹūüíĽ ūüôā

Join