Procedue of building rest API using Node, Express, PostgreSQL and Docker

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

7. Run on Docker