Node.js not recognising Exception from Postgres Procedure as an Error

39 views Asked by At

I have a procedure in postgres which completes some validations and inserts some data. If the validations are not passed then it raises an exception.

CREATE OR REPLACE PROCEDURE EnrolStudentToProgram(iProgramID int, iStudentID int, dEnrolDate date, vEnrolDesc varchar(255))
LANGUAGE plpgsql AS
$$
DECLARE
-- Variables
    d int;  
BEGIN
--validations and inserts
    ....
    
    IF d = 0 THEN
        raise exception  
        'No assessment seq configured for the requested Program_ID: %', iProgramID;
    END IF;  
  END;  
$$;

I call the procedure from a node.js back end using node-postgres pool:

const enrolStudentInProgram = async (req, res) => {
  const { studentId, program, enrolDesc } = req.body

  if (!studentId || !program || !enrolDesc) {
    return res.status(400).json({ message: 'All fields are required' })
  }

  try {
    const enrolled = await pool.query(
      `call EnrolStudentToProgram($1,$2,now()::date,$3::varchar(255))`,
      [program, studentId, enrolDesc]
    )
    return res.status(201).json({ message: 'Student Enrolled' })
  } catch (err) {
    console.error(err)
    return res.status(400).json({ message: 'Invalid data received' })
  }
}

The procedure functions as desired when executed in pdAdmin with the exception being raised, and everything flows in node.js when there are no exceptions, but if an exception is raised in the procedure I cannot get node.js to recognise this as an error or retrieve the exception message.

How do I cause an error to be recognised and retrieve an error message in Node.js when validations fail?

Update from comments: The console.log of enrolled is below, it is the same whether there is an exception raised or not.

 ~ enrolStudentInProgram ~ enrolled: Result {
  command: 'CALL',
  rowCount: null,
  oid: null,
  rows: [],
  fields: [],
  _parsers: undefined,
  _types: TypeOverrides {
    _types: {
      getTypeParser: [Function: getTypeParser],
      setTypeParser: [Function: setTypeParser],
      arrayParser: [Object],
      builtins: [Object]
    },
    text: {},
    binary: {}
  },
  RowCtor: null,
  rowAsArray: false,
  _prebuiltEmptyResultObject: null
}
0

There are 0 answers