EN
Express.js - simple login / logout site with MySQL
4
points
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:
Source code
Below source code runs server over 3000 port.
Github link here.
Clone it, run
npm ci install, runnpm startand open http://localhost:3000 in the web browser.
Source code is composed of:
- common logic that contains inde.js file,
- back-end logic that contains REST API to login and logout user with MySQL database,
- front-end logic that contains 2 sites: for logged-in user and login form,
- 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.
Project structure
C:/Project
├───backend
│ ├───api.js
│ └───database.js
├───frontend
│ ├───api.js
│ ├───login.html
│ └───site.html
├───node_modules
├───index.js
└───package.json
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": "Dirask",
"dependencies": {
"body-parser": "^1.19.0",
"express": "^4.17.1",
"express-session": "^1.17.2",
"mysql": "^2.18.1"
},
"devDependencies": {
"nodemon": "^2.0.13"
}
}
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');