How do I structure an IF statement in node-postgres?

Question

I'm trying to write the following IF statement in my node-postgres transaction. This is to make the query more robust by checking the boolean of a field for a unique ID before inserting and updating in a separate Table.

DO $$
BEGIN 
   IF (SELECT has_submitted FROM schema1.analysis_sent WHERE unique_link = 'aaa') THEN
      RAISE EXCEPTION 'Already submitted questions.';
   ELSE
      INSERT INTO schema1.question_breakdown (question_id) VALUES (1);
      UPDATE schema1.analysis_sent SET has_submitted = TRUE WHERE unique_link = 'aaa';
      RAISE NOTICE 'Added to question_breakdown and updated has_submitted field.';
   END IF;
END $$;

How do I structure this in node.js, specifically using the node-postgres package? Currently, it looks like the following and I'd like to replace the bottom two queries (saveQuery and updateQuery) with the IF statement above.

I'm confused between the BEGIN/END syntax of the IF statement, and the BEGIN/COMMIT/ROLLBACK syntax of the transaction I usually use in node-postgres. Do I just fit the former into the latter block?

const saveQuery = format('INSERT INTO %I.question_breakdown_result(question_id, student_id, marks_scored) VALUES %L', org, questions);
const updateQuery = format('UPDATE %I.analysis_sent SET has_submitted = TRUE WHERE unique_link = %L', org, link.uniqueLink);

(async() => {
            const client = await pool.connect();
            try {
                await client.query('BEGIN');
                await client.query(saveQuery);
                await client.query(updateQuery);
                await client.query('COMMIT');
                return res.status(201).json(helper.setResponsePayload(true, 'Your results have been saved and submitted.', null));
            } catch (err) {
                await client.query('ROLLBACK');
                res.status(200).json(helper.setResponsePayload(false, 'Failed Database Query: Save Question Result.', null));
                throw err;
            } finally {
                client.release();
            }
        })().catch(err => console.log(err.stack))

Second question - is this IF statement above considered a transaction, i.e. if one part of the query fails, the whole thing fails? Thanks.

score 0 · Accepted Answer · edited Dec 27 '19 at 19:33

I came up with the following solution after some trial and error with a lot of help from this clarifying post.

is this IF statement above considered a transaction, i.e. if one part of the query fails, the whole thing fails? Thanks.

The answer is no - the BEGIN for an IF statement is different compared to the BEGIN of a transaction. With that knowledge, I figured it is alright to insert the "IF" BEGIN/END statement within the BEGIN/COMMIT/ROLLBACK block, and can now answer the below question:

How do I structure this in my node.js code?

        const checkQuery = format('SELECT has_submitted FROM %I.analysis_sent WHERE unique_link = %L', org, link.uniqueLink);
        const saveQuery = format('INSERT INTO %I.question_breakdown_result(question_id, student_id, marks_scored) VALUES %L', org, questions);
        const updateQuery = format('UPDATE %I.analysis_sent SET has_submitted = TRUE WHERE unique_link = %L', org, link.uniqueLink);

        const ifQuery = `
            DO $$
            BEGIN 
            IF (${checkQuery}) THEN
                RAISE EXCEPTION 'User has already submitted paper questions.';
            ELSE
                ${saveQuery};
                ${updateQuery};
                RAISE NOTICE 'Added to question_breakdown and updated has_submitted field.';
            END IF;
            END $$;
        `;

        (async() => {
            const client = await pool.connect();
            try {
                await client.query('BEGIN');
                await client.query(ifQuery);
                await client.query('COMMIT');

            } catch (err) {
                await client.query('ROLLBACK');

                throw err;
            } finally {
                client.release();
            }
        })().catch(err => console.log(err.stack));

Now it works as planned: checks whether a field for a unique ID is TRUE; if FALSE, then the insert and update statements are executed. If either the insert/update statement fails, then the whole transaction fails.

How do I structure an IF statement in node-postgres?

1 Answers1