Question 113822 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to wrap code that uses transcations in a transaction and then rollback?

I’m setting up my integration testing rig. I’m using the beforeEach and afterEach hooks to wrap every single test in a transaction that rollsback so that the tests don’t affect each other. A simplified example might be this:

const { repository } = require("library")

describe("Suite", function () {
  beforeEach(async function () {
    await knex.raw("BEGIN");
  });

  afterEach(async function () {
    await knex.raw("ROLLBACK");
  });

  it("A test", async function () {
    const user = await repository.createUser()
    user.id.should.equal(1)
  });
});

This worked fine because I configured knex to use a single DB connection for tests. Hence calling knex.raw("BEGIN"); created a global transaction.

Now however, the library’s repository which I can’t control started using transactions internally. I.e. createUser() begins and then commits the created user. This broke my tests as now my afterEach hook doesn’t rollback the changes because they were already committed.

Is there a way in Postgres to rollback a transaction that have (already committed) nested transactions?

Or maybe a way to use knex to prevent the repository from starting transactions in the first place? It uses knex.transaction() to create them.

Thanks!

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    As may be guessed from the tags the library in question is Strapi and I'm trying to write tests for the custom endpoints I implemented with it.

    As noted by @zagarek, Postgres itself can't rollback already committed transactions. Knex does support nested transactions (using save-points) but you must explicitly refer to the parent transaction when creating a new one for it to get nested.

    Many tried to achieve this setup. See the threads under e.g. here or here. It always boils down to somehow passing the test-wrapping transcation all the way down to your ORM/repository/code under test and instructing it to scope all queries under that transaction.

    Unfortunately, Strapi doesn't provide any way to be given a transaction nor to create a global one. Now, cover your eyes and I'll tell you how I hacked around this.

    I leverage one nice aspect of Knex: its Transaction object behaves (mostly) the same as a Knex instance. I mercilessly replace Strapi's reference of Knex instance with a Knex transaction and then rollback it in afterEach hook. To not make this too easy, Strapi extends its knex instance with a getSchemaName function. I therefore extend the transaction in disguise too and proxy to the original.

    This does it: (Note that I'm using Mocha where this can be used to pass state between hooks and/or tests.)

    const Strapi = require("@strapi/strapi");

before(async function () {
  // "Load" Strapi to set the global `strapi` variable.
  await Strapi().load();
  // "Listen" to register API routes.
  await strapi.listen();
  // Put aside Strapi's knex instance for later use in beforeEach and afterEach hooks.
  this.knex = strapi.db.connection;
});

after(async function () {
  // Put back the original knex instance so that Strapi can destroy it properly.
  strapi.db.connection = this.knex;
  await strapi.destroy();
});

beforeEach(async function () {
  // Replace Strapi's Knex instance with a transaction.
  strapi.db.connection = Object.assign(await this.knex.transaction(), {
    getSchemaName: this.knex.getSchemaName.bind(this.knex),
  });
});

afterEach(async function () {
  strapi.db.connection.rollback();
});

it("Health-check is available.", async function () {
  // Any changes made within here will get rolled back once the test finishes.
  await request(strapi.server.httpServer).get("/_health").expect(204);
});

    Lastly, it's worth noting that some Knex maintainers persistently discourage using transcations to isolate tests so consider if chasing this hacky setup is a good idea.


  2. 0

    Judging by the looks of an example debug log, knex does in fact detect transaction nesting automatically and switches nested transactions from using irreversible commit/rollback to manageable savepoint s1/release s1/rollback to s1 the way I was guessing in my comment.

    In this case, it should be enough for you to wrap your calls in a transaction, so that you "own" the top-level one. Knex should detect this and force the underlying transactions to use savepoints instead of commits, all of which you can then undo, rolling back the top-level transaction. If I read the doc right:

    const { repository } = require("library")
 
describe("Suite", function () {
  it("A test", async function () {
    try {
        await knex.transaction(async trx => {
          const user = await repository.createUser();
          user.id.should.equal(1);
          trx.rollback();
        })
    } catch (error) {
      console.error(error);
    }
  });
});

    That’s assuming none of the calls below issues a knex.raw("COMMIT") or somehow calls .commit() on the outer, top-level transaction.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search