Knexjs: Transaction and raw queries

Question

we are using knexjs in nodejs. We are trying to use the transaction with raw query but it doesn't work as expected. When an error is thrown the rollback function is called but into the database I can see the data

  const trx = await knex.transaction();

  await trx.schema.createTable("test", function(table) {
    table.increments();
    table.string("name");
  });

  await trx("test").insert({ name: "foo1" });
  await trx("test").insert({ name: "foo2" });

  await trx.rollback();

Is it possible to use transaction with raw queries?

Answer 1

According to https://github.com/tgriesser/knex/issues/3452#issuecomment-534952063 the creating a table causes an implicit commit. So, the transaction is closed and the rollback has no effect. A possible workaround is to use the transaction for all statements except for the create table.

Below you can see my snippet modified according to this workaround

await knex.schema.createTable("test", function(table) {
  table.increments();
  table.string("name");
});

const trx = await knex.transaction();

await trx("test").insert({ name: "foo1" });
await trx("test").insert({ name: "foo2" });

await trx.rollback();