NodeJs Tutorials
- NodeJs
- Install NodeJs
- Why use NodeJs
- NodeJs Process Model
- create First Application
- Run NodeJs Application
- Node.js Console
- Node.Js Modules
- URL Modules
- node.js Callback
- Node.js Events
- Upload Files
- Upload single & Multiple Files
- NodeJs File System
- NodeJs Email
- Debugging NodeJs
- .ENV
- NodeJs Mysql
- Helpers
- encription and decription in nodeJs
- Query string
- Date & Time
- Express Js
- Template Engine with Express
- MVC Pattern in Node.Js
- list of NPM Module
- Middleware
- Body Parser
- Render
- Nodemon module
- Morgan module
- Flash Message in ExpressJs
- Session
- Session store in database
- Cookies
- Helmet
- Multer
- Router: How To Use Routing In Node.Js
- App.Js
- express.json() and express.urlencoded()
- REST APIs in NodeJs
- Gloabal Objects
- Submit Form Data
- How to get Post Data in Node.js
- How to Get URL Parameters in Node.js
- How to create Node Project
- How to Insert Form Data Into the MySql Table Using Node.js
- How to fetch Data from MySQL database table using Node.js
- CRUD Example
- Await and Async
- Promises
- Login Example
- Password Encription
- How to validate Form (Form Validation) in Node.Js?
- Registration & Login form usingn Node.Js & MySql?
- Forgot & Reset Password
- File Upload in Node.Js with ExpressJs
- Resize Image Before Upload using Multer Sharp
- Upload multiple file using node.js with multer module
- Upload file using node.js with multer module
- Client IP Address
- REST API Downloading File in NodeJs
- Export and Download CSV From MySQL Database
- CRUD REST API
- CRUD REST API Example 2
- Enable HTTPS using Node
- How to send EMAIL using NodeJs?
- Dynamic dependent dropdown using NodeJs?
- Autocomplete Search
- Get and Send Data From Ajax Request
- Get and Post Data using Ajax
- Passport Authentication
- Node Js Data type
- Node Js Error
- Node Js Array Function
- Node Js String Function
- Puppeter Module
How to store session data in mysql database Using NodeJs?
A session creates a unique ID for every user and store on the browser as a cookie and stores some information of the user in the server. Whenever that user makes a request, the server will match a user information with the stored session data.
Why storing sessions in a Database?
Sessions create unique id for each visitor. When you save data into the session, the session will store this information into a specific file on the server. The unique id is also stored as a cookie on your browser. in that case One problem you may encounter with storing sessions in a file system on the server is using a shared hosting plan. In this case, if the server is not configured correctly, it would be possible for someone to get access to your Sessions data.
Store session in MySQL Database
Express-mysql-session module is used to store session data in MySQL database. This module creates a database table to save session data, but if you are using an older version of MySQL than MySQL 5.5.3, create your sessions table before initializing the MySQLStore.
File Name :
install express-mysql-session for session data store in MySql Database.
npm install --save express-session
npm install --save express-mysql-session
https://devdotcode.com/how-to-store-session-in-mysql-database-using-express-mysql-session/
It create a MySQL connection pool that handles the connection to the database. The pool consists of 1 connection by default, but you can override this using the connectionLimit option.
File Name :
const mysqlStore = require('express-mysql-session')(session);
const sessionStore = new mysqlStore(options);
Create Server.js
File Name : Server.js
const express= require('express');
require("dotenv").config();
const session = require('express-session');
const bodyParser = require('body-parser');
const app=express();
const cors=require("cors");
app.use(cors());
const mysqlStore = require('express-mysql-session')(session);
const PORT= process.env.APP_PORT;
const IN_PROD = process.env.NODE_ENV === 'production'
const TWO_HOURS = 1000 * 60 * 60 * 2
const options ={
connectionLimit: 10,
password: process.env.DB_PASS,
user: process.env.DB_USER,
database: process.env.MYSQL_DB,
host: process.env.DB_HOST,
port: process.env.DB_PORT,
createDatabaseTable: true
}
const sessionStore = new mysqlStore(options);
app.use(bodyParser.urlencoded({
extended: true
}));
app.use(bodyParser.json())
app.use(session({
name: process.env.SESS_NAME,
resave: false,
saveUninitialized: false,
store: sessionStore,
secret: process.env.SESS_SECRET,
cookie: {
maxAge: TWO_HOURS,
sameSite: true,
secure: IN_PROD
}
}))
app.listen(PORT, ()=>{console.log(`server is listening on ${PORT}`)});
Table create
The sessions database table should be automatically created using default options, which means setting the createDatabaseTable option to TRUE. If the table is not created for whatever reason, you can use the following schema to create it.
File Name :
CREATE TABLE IF NOT EXISTS `sessions` (
`session_id` varchar(128) COLLATE utf8mb4_bin NOT NULL,
`expires` int(11) unsigned NOT NULL,
`data` mediumtext COLLATE utf8mb4_bin,
PRIMARY KEY (`session_id`)
) ENGINE=InnoDB
For a custom database table schema, you have to set the createDatabaseTable option to FALSE. So the session store does not automatically create a sessions table, and then you use the schema option to provide the custom table and column names to the session store.
File Name :
const options ={
connectionLimit: 10,
password: process.env.DB_PASS,
user: process.env.DB_USER,
database: process.env.MYSQL_DB,
host: process.env.DB_HOST,
port: process.env.DB_PORT,
createDatabaseTable: FALSE,
schema: {
tableName: 'custom_sessions_table_name',
columnNames: {
session_id: 'custom_session_id',
expires: 'custom_expires_column_name',
data: 'custom_data_column_name'
}
}
}
.ENV
File Name :
APP_PORT = 3000
DB_PORT = 3306
DB_HOST = localhost
DB_USER = root
DB_PASS = yourpassword
MYSQL_DB = yourdatabasename
SESS_LIFETIME = TWO_HOURS
NODE_ENV = 'development'
SESS_NAME = 'enter_the_session_name'
SESS_SECRET = 'yoursecret'
Set the express-session object
File Name :
app.use(session({
name: process.env.SESS_NAME,
resave: false,
saveUninitialized: false,
store: sessionStore,
secret: process.env.SESS_SECRET,
cookie: {
httpOnly: true,
maxAge: Three_HOURS,
sameSite: true,
secure: IN_PROD
}
}))
Name :- The default one is just called ID. but you write your unique session name. so people don't know the cookie name you are using this library.
session secret :- you choose session secret code is unique and it is better to keep it in your environment Variables file.
Then we will get it from the environment file instead of just having the secret in server.js because it is not a good practice to have your secrets in your source code.
resave :- you don t want to resave their session every single time, you can turn that on, but we are just going to keep it off because we only really need to store it once and then just update it if we want to; we don’t need to save every time.
saveUninitialized :- if we don't want to store anything about the user or we don't create a cookie for the user then set false.
cookies:- set httponly is true. because it is very important. when it is true then JavaScript cannot access your cookie. which is essential for security.
secure :- secure: process.env.NODE_ENV === "production". The secure is for HTTPS so that it will send the cookie over HTTPS.
This setting is crucial for production, and you should run HTTPS for production, so this shouldn’t be a problem.
To check whether we are production if we are, we turn it on, and if we are not running HTTPS in development, we turn it off.
store session in MySQL database
File Name : server.js
const mysql = require('mysql');
// create your connection pool
const pool = mysql.createPool(options);
Complete Code
File Name : server.js
const express= require('express');
require("dotenv").config();
const session = require('express-session');
const bodyParser = require('body-parser');
const db = require('./db');
const mysql = require('mysql');
const app=express();
const cors=require("cors");
app.use(cors());
const mysqlStore = require('express-mysql-session')(session);
const PORT= process.env.APP_PORT;
const IN_PROD = process.env.NODE_ENV === 'production'
const TWO_HOURS = 1000 * 60 * 60 * 2
const options ={
connectionLimit: 10,
password: process.env.DB_PASS,
user: process.env.DB_USER,
database: process.env.MYSQL_DB,
host: process.env.DB_HOST,
port: process.env.DB_PORT,
createDatabaseTable: true
}
const pool = mysql.createPool(options);
const sessionStore = new mysqlStore(options);
app.use(bodyParser.urlencoded({
extended: true
}));
app.use(bodyParser.json())
app.use(session({
name: process.env.SESS_NAME,
resave: false,
saveUninitialized: false,
store: sessionStore,
secret: process.env.SESS_SECRET,
cookie: {
maxAge: TWO_HOURS,
sameSite: true,
secure: IN_PROD
}
}))
app.get('/', (req, res)=>{
const { userId } = req.session
console.log(userId);
res.send(`
<h1> Welcome!</h1>
${userId ?`<a href = '/home'> Home </a>
<form method='post' action='/logout'>
<button>Logout</button>
</form>` : `<a href = '/login'> Login </a>
<a href = '/register'> Register </a>
`}
`)
})
app.get('/home', async(req,res)=>{
const {userId} =req.session
if(userId){
try{
const user = await db.getUser(userId);
console.log(user)
req.user = user;
res.send(`
<h1>Home</h1>
<a href='/'>Main</a>
<ul>
<li> Name: ${user[0].first_name} </li>
<li> Email:${user[0].email} </li>
</ul>
`)
} catch(e) {
console.log(e);
res.sendStatus(404);
}
}
})
app.get('/login', (req,res)=>{
res.send(`
<h1>Login</h1>
<form method='post' action='/login'>
<input type='email' name='email' placeholder='Email' required />
<input type='password' name='password' placeholder='password' required/>
<input type='submit' />
</form>
<a href='/register'>Register</a>
`)
})
app.get('/register', (req,res)=>{
res.send(`
<h1>Register</h1>
<form method='post' action='/Register'>
<input type='text' name='firstName' placeholder='First Name' required />
<input type='text' name='lastName' placeholder='Last Name' required />
<input type='email' name='email' placeholder='Email' required />
<input type='password' name='password' placeholder='password' required/>
<input type='submit' />
</form>
<a href='/login'>Login</a>
`)
})
app.post('/login', async(req, res, next)=>{
try{
const email = req.body.email;
let password = req.body.password;
user = await db.getUserByEmail(email);
if(!user){
return res.send({
message: "Invalid email"
})
}
if(user.password !== password){
return res.send({
message: "Invalid password"
})
}
req.session.userId = user.id
return res.redirect('/home');
} catch(e){
console.log(e);
}
});
app.post('/register', async (req, res, next)=>{
try{
const firstName = req.body.firstName;
const lastName = req.body.lastName;
const email = req.body.email;
let password = req.body.password;
if (!firstName || !lastName || !email || !password) {
return res.sendStatus(400);
}
const user = await db.insertUser(firstName, lastName, email, password).then(insertId=>{return db.getUser(insertId);});
req.session.userId = user.id
return res.redirect('/register')
} catch(e){
console.log(e);
res.sendStatus(400);
}
});
app.post('/logout', (req, res)=>{
req.session.destroy(err => {
if(err){
return res.redirect('/home')
}
sessionStore.close()
res.clearCookie(process.env.SESS_NAME)
res.redirect('/login')
})
})
app.listen(PORT, ()=>{console.log(`server is listening on ${PORT}`)});
File Name :
File Name :
File Name :
File Name :
File Name :
File Name :