1. create a database.sql file so that we can create the table by terminal; For Example
CREATE DATABASE todo_database;
CREATE TABLE todo(
todo_id SERIAL PRIMARY KEY,
description VARCHAR(255) );
2. create db.js file to connect the database;
const Pool = require('pg').Pool; //a connection pool allows you to make multiple connection requests to your server.
const pool = new Pool({
user: "runyao.xia",
password: "******",
database: '******',
host: 'localhost',
port: 5432
}
);
const query = (sql)=>{
return new Promise((res, rej)=>{
pool.connect(async (err, conn) => {
let result = await conn.query(sql);
if (result) res(result);
else rej(err);
conn.release();
});
})
}
async function query(query, params) {
const {rows, fields} = await pool.query(query, params);
return rows;
}
module.exports = { query, pool };
By now, we are able to use query by import db.js
3. Process User Registration and Authentication
- (1) login parameters, depends on database like email/username, password…and use bcrypt to hash the password. Do check the username and password not be empty, and the user is exist or not, need to check it from database schema. For postgresql, have to check by username or phone number;
(2) if everything goes well, sign a json web token
const bcrypt = require("bcrypt");
const client = require("../configs/database");
const jwt = require("jsonwebtoken");
//Registration Function
exports.register = async (req, res) => {
const { name, email, phonenumber, password } = req.body;
try {
const data = await client.query(`SELECT * FROM users WHERE email= $1;`, [email]); //Checking if user already exists
const arr = data.rows;
if (arr.length != 0) {
return res.status(400).json({
error: "Email already there, No need to register again.",
});
}
else {
bcrypt.hash(password, 10, (err, hash) => {
if (err)
res.status(err).json({
error: "Server error",
});
const user = {
name,
email,
phonenumber,
password: hash,
};
var flag = 1; //Declaring a flag
//Inserting data into the database
client
.query(`INSERT INTO users (name, email, phonenumber, password) VALUES ($1,$2,$3,$4);`, [user.name, user.email, user.phonenumber, user.password], (err) => {
if (err) {
flag = 0; //If user is not inserted is not inserted to database assigning flag as 0/false.
console.error(err);
return res.status(500).json({
error: "Database error"
})
}
else {
flag = 1;
res.status(200).send({ message: 'User added to database, not verified' });
}
})
if (flag) {
const payload = {
user: {
id: user.email
}
};
jwt.sign(
payload,
config.get('jwtSecret'), { expiresIn: '5 days' }, (err, token) => {
if (err) throw err;
res.json({ token });
}
);
};
});
}
}
catch (err) {
console.log(err);
res.status(500).json({
error: "Database error while registring user!", //Database connection error
});
};
}
- For the config directory, usually has db.js and a json file for config, similar to the .env file, we can also put jwtsecret in the .env file
{
“mongoURI”: “mongodb+srv://",
“jwtSecret”: “ryantoken”,
“githubClientId”: "",
“githubSecret”: "****”
}
for .env file, might like this
PORT=5000
PG_HOST=localhost
PG_PORT=5432
PG_DBNAME=YOUR_PROJECT
PG_USER=postgres
PG_PASSWORD=<YOUR_PASSWORD>
JWT_SECRET=<YOUR_SECRET>
If we want to use these configurations, just import the file.
4. Send the token back to authenticate a access
Create a middleware to verify the token
const jwt = require('jsonwebtoken');
const config = require('config');
module.exports = function (req, res, next) {
// Get token from header
const token = req.header('x-auth-token');
// Check if not token
if (!token) {
return res.status(401).json({ msg: 'No token, authorization denied' });
}
// Verify token
try {
jwt.verify(token, config.get('jwtSecret'), (error, decoded) => {
if (error) {
return res.status(401).json({ msg: 'Token is not valid' });
} else {
req.user = decoded.user;
next();
}
});
} catch (err) {
console.error('something wrong with auth middleware');
res.status(500).json({ msg: 'Server Error' });
}
};
next is a promise we want to do after this. Then for a protected route, we are able to pass the auth as a parameter like this
router.get('/', auth, async (req, res) => {
try {
const user = await User.findById(req.user.id).select('-password');
res.json(user);
} catch (err) {
console.error(err.message);
res.status(500).send('Server Error');
}
});
(Codes above are using mongoDB, if using any relational database, we have to write sql to get the user) like this
const addToken = async (userid) => {
const token = await jwt.sign({ id: userid }, process.env.JWT_SECRET)
const updateUserTokensStatement = `
update users
set tokens = tokens || $1
where id = $2
returning *
`
const { rows: [user] } = await query(updateUserTokensStatement, [[token], userid])
return { user, token }
}
Seperate addToken as a function, then use it in the register api,
const { user, token } = await addToken(rows[0].id)
After doing that, use postman to test if it works, remember we put token in the req.header as a key ‘x-auth-token’, we can test it as this
5. User Login Authentication
PostgreSQL version
const getPublicUser = (user) => {
delete user.password
delete user.tokens
return user
}
router.post('/login', async (req, res) => {
try {
const { username, password } = req.body
if (!username || !password) {
throw new Error('Username and password are required')
}
const selectUserStatement = `select * from users where username = $1`
const { rows } = await query(selectUserStatement, [username])
const failedLoginError = { error: 'Username or password was incorrect' }
if (!rows[0]) {
return res.status(401).send(failedLoginError)
}
const isMatch = await bcrypt.compare(password, rows[0].password)
if (!isMatch) {
return res.status(401).send(failedLoginError)
}
const { user, token } = await addToken(rows[0].id)
res.send({
user: getPublicUser(user),
token
})
} catch (e) {
res.status(400).send({ error: e.message })
}
})
Note: Express has a middle ware called “express-validator”, actually we are able to check if the email or username input text is empty, or its length by express-validator.
check('email', 'Please include a valid email').isEmail().custom(email => {
let result = email.substring(email.indexOf('@') + 1, email.length);
6. PostgreSQL configuration
onst Pool = require('pg').Pool; //a connection pool allows you to make multiple connection requests to your server.
const pool = new Pool({
user: "runyao.xia", // process.env.***
password: "*****", // process.env.***
database: '*****', // process.env.databaseName
host: 'localhost',
port: 5432
}
);
const query = (sql)=>{
return new Promise((res, rej)=>{
pool.connect(async (err, conn) => {
let result = await conn.query(sql);
if (result) res(result);
else rej(err);
conn.release();
});
})
}
module.exports = { query, pool};
At the same time, we need a sql file to init the database, run the file in your databse cmd line.
postgres=# CREATE DATABASE test;
CREATE DATABASE