Express/Postgres User registration controller

Question

To simplify the situation I'll just post the following controller for an express route which interactions with a Postgres Database. My question is about error handling. If an error occurs it will be caught within the catch clause. But how can I access the errors thrown by the database queries itself. If I make several await several queries and one of them fails I need probably to restore stuff in the database? For example if the insertion of the user in the user table is a success, but the following query of inserting the user in another table fails, I need to delete the user from the user table again. How does one model such flows?

    //
    // Register User
    //
    export const registerUser = async (request, response, next) => {
        try {
          const usersWithSameMail = await client.query(`SELECT * FROM public.users WHERE email = '${user.email}'`);
          if(usersWithSameMail.rows.length > 0){
            return response.status(403).json({"code": "ERROR", "message": "Email is already registered"})
          } else {
            await client.query(`
                INSERT INTO public.users(first_name, last_name, email, password) 
                VALUES ('${user.first_name}', '${user.last_name}', '${user.email}', crypt('${user.password}', gen_salt('bf', 8)));
            `);

            // more await statements...

            return response.status(200).json({"code": "INFO", "message": "Verification mail sent to user"});
          }
        } catch (error) {
            return response.status(500).json({"code": "ERROR", "message": "Error occured while registering the user. Please try again."});
        }
    }```

Answer 1

You can use middlewares chaining your routes handler. In order to it work, you will have to change your current working code to use Single-responsibility principle. Do only one responsability per middleware and chain all handlers to work as one.

Lets say you want to insert new user, to perform this operation we should:

• lookup if email is unique
• hash password
• Insert new user
• return inserted data in postgres back as a response

Following the middleware chaining we should implement a function for each action and chain each action in route definition:

const postgres = require('../../lib/postgres');
const crypto = require('crypto');

exports.insertedData = (req, res) => {
    res.status(200).json(req.employee);
};

exports.hashPassword = (req, res, next) => {
    crypto.scrypt(req.body.password.toString(), 'salt', 256, (err, derivedKey) => {
        if (err) {
            return res.status(500).json({ errors: [{ location: req.path, msg: 'Could not hash password'}] });
        }
        req.body.kdfResult = derivedKey.toString('hex');
        next();
    });
};

exports.lookupEmailUnique = (req, res, next) => {
    const sql = 'SELECT e.email FROM public.users e WHERE e.email=$1';
    postgres.query(sql, [req.body.email], (err, result) => {
        if (err) {
            return res.status(500).json({ errors: [{ location: req.path, msg: 'Could not query database' }] });
        }
        if (result.rows.length > 0) {
            return response.status(403).json({"code": "ERROR", "message": "Email is already registered"})
        }
        next()
    });
}

exports.insertNewUser = (req, res, next) => {
    
    const sql = 'INSERT INTO public.users(first_name, last_name, email, password) VALUES ($1,$2,$3,$4} RETURNING *';
    postgres.query(sql, [req.body.first_name, req.body.last_name, req.body.email,  req.body.kdfResult], (err, result) => {
        if (err) {
            return res.status(500).json({ errors: [{ location: req.path, msg: 'Could not query database'}] });
        }
        req.employee = result.rows[0];
        next();
    });
};

here is your route declaration:

const router = require('express').Router();
const userService = require('../controllers/user.controller');

router.post('/register', userService.lookupEmailUnique, userService.hashPassword, userService.insertNewUser, userService.insertedData);

module.exports = router;

Here in routes you are using the middeware to do the chaning, you only pass the control to next middleware if all conditions are met and has full control from database erros.

In my example I do not used the async/await but I can change my example to have a version using async/await.

example middleware with transaction

exports.deletePostagem = async (req, res, next) => {
    try {
        await postgres.query('BEGIN');
        const sql2 = 'UPDATE comentario SET postagem = null WHERE postagem = $1';
        await postgres.query(sql2, [req.params.id]);
        const sql3 = 'DELETE FROM postagem WHERE id = $1';
        await postgres.query(sql3, [req.params.id]);
        await postgres.query('COMMIT');
        res.status(204).json();
        res.end();
    } catch (err) {
        await postgres.query('ROLLBACK');
        return res.status(500).json({ errors: [{msg: 'Could not perform operation' }]})
    }
}

I used this only as an example, but in my projects I always have a middeware for validate/sanitize the data that comes in request before using in database query prepared statements.

Answer 2

based on transaction documentation in node.js you can use rollback

export const registerUser = async (request, response, next) => {
  try {
    let error = null;
    const client; // create a client, connect to the db
    try {
      await client.query("begin");

      await client.query("first query");
      await client.query("second query");
      await client.query("third query");

      await client.query("commit"); //do commit when is finished all queries
    } catch (error) {
      error = error;
      await client.query("rollback");
    } finally {
      client.release(); // close the connection
    }
    if (error) {
      return response.status(500).json({ message: error }); // error message
    }
    return response.status(200).json({ message: "My message" }); // success message
  } catch (err) {
    return response.status(500).json({ message: err });
  }
}