Pg-promise: Transaction based database cleanup for tests

[pex]

Transaction strategy is a common method to have a clean database for each test run. Which means a transaction is created before and rolled back after each test run.

I'd like to explicitly rollback or commit transactions to achieve this. Maybe we could add an option parameter to switch between auto and manual transactions?

Expected behavior

// Pseudo example with Jest

jest.mock('./db.js')

const db = require('./db.js') // Mocked adapter used by app
const originalDb = require.requireActual('./db.js')

beforeEach(() => {
  originalDb().tx(tx => {
    db.mockReturnValue(tx) // All queries now run on tx
  })
})

afterEach(() => {
  db().rollback() // tx has to be rollbacked
})

Actual behavior

pg-promise doesn't support explicit rollbacks

[vitaly-t]

A transaction in this library manages its integrity by fully encapsulating the connection inside it, as well as result of the operation. What you are asking, is to compromise both of those things, by exposing the connection outside of the transaction, and its ability to handle errors correctly.

For any application it should be strictly a no-no.

But if you want to hack your tests that way, you can manage the connection manually all the way, but be careful not to leak the connections, and to handle errors properly...

let cn;

try {
    cn = await db.connect(); // manually created connection;   

    await cn.query('BEGIN');

    /* and so on */

    await cn.query('COMMIT'); // or `ROLLBACK`

} catch(error) {

// handle errors
// like, you can do your ROLLBACK here

} finally() {
    if(cn) {
        cn.done(); // must release the connection in the end
    }
}

You can do all that, but it is generally not a good idea.

pg-promise doesn't support explicit rollbacks

It does. If a transaction throws an error or returns a rejected promise, it will ROLLBACK right away.

[vitaly-t]

You are looking at testing database integrity the wrong way. The right way is to test for data that was either rolled back or committed. Then you do not need to reinvent those dangerous things here.

There is no scenario that would require breaking a transaction, the way you are trying to break it.

I know it, as I helped along with a number of large projects at this point, which involved testing hugely complex transactions.

[vitaly-t]

I take it the issue is resolved, since there was no response, so closing it.

[clintonmedbery]

@vitaly-t I am not sure you are understanding the request, or perhaps I don't understand your answer. When I am writing tests I will insert a user for example and maybe other rows to test against. When I am done I want to remove them so I can test with a clean db. It would be nice to be able to roll these back. I don't need to test rollback functionality. Is there a solution for this?

[vitaly-t]

@clintonmedbery You should use a transaction per test then, because transaction logic doesn't map well over multiple tests, being connection-dependent. Alternatively, you would have to manage connection manually, but that's awkward.