Can you please help me with multiple queries in one route (node.js/express)

[leurs247]

I'm using express as a framework for my node.js backend. Currently, a route looks like this (simplified):

export const myRoute = async (req, res, next) => {
db.tx(async (pg) => {
    // do stuff here like SELECT, INSERT, UPDATE, DELETE
})
.catch((err) => {
    next(err);
});

I have routes for selecting data, inserting, updating, ... but I also have a lot of routes where data is first selected, and then inserted/updated/deleted (the select query is for a check if the user can do the insert/update/delete operation).

As you can see, currently, my "setup" is just starting a transaction in the beginning of the route and then adding ALL the necessary code in the then-block of the transaction, even if I only have select-queries.

First of all: I know I should change the select queries to tasks instead of transactions. But what if I have select-queries and then insert-queries? Should I do something like this:

export const myRoute = async (req, res, next) => {
try {
    const t = await db.task();
    const tx = await db.tx();

    const userId = req.body.userId;
    const sessionId = generateId();

    // select some data
    // use the task
    const user = await t.oneOrNone('SELECT * FROM users WHERE user_id = $1', [userId]);

    if (user) {
      // a user is found

     // insert a session
     // use the transaction
     await tx.none('INSERT INTO sessions (user_id, session_id) VALUES ($1, $2)', [userId, sessionId]);

     // delete the login flow
    // use the transaction
     await tx.none('DELETE FROM login_flows WHERE user_id = $1', [userId]);

     // select all the other sessions of the user
     // use the task
    const sessions = await t.any('SELECT session_id FROM session WHERE user_id = $1', [userId]);

    return res.status(200).json({ sessionId, sessions }).end();
    } else {
        throw new Error('user_not_found');
    }
} catch (err) {
    next(err);
}
};

This code is really simplified, but I think you get my point.

The second code block, is this the way to go?

For routes with only 1 select-query, I'm planning to use the db-connection directly (without task).
For routes with only 1 insert-query, I probably also need to use the transaction stuff?

[leurs247]

I just tested my edited code and this does not work. The problem is with

const t = await db.task();
const tx = await db.tx();

The functions db.task and db.tx require a callback function. How can I solve this?

[vitaly-t]

It is well within the library's protocol, to create a task and then a transaction from it, if needed:

await db.task(async t => {
    if(/* a transaction is needed */) {
        await t.tx(async t1 => {
            // transaction queries against t1 context
        });
    }
});

For more flexibility, the library has methods taskIf and txIf.

@leurs247