knex/test/integration/execution/transaction.js

'use strict';

const { expect } = require('chai');

const Knex = require('../../../knex');
const _ = require('lodash');
const sinon = require('sinon');
const { KnexTimeoutError } = require('../../../lib/util/timeout');
const delay = require('../../../lib/execution/internal/delay');
const {
  isRedshift,
  isOracle,
  isMssql,
  isPostgreSQL,
  isCockroachDB,
} = require('../../util/db-helpers');
const { DRIVER_NAMES: drivers } = require('../../util/constants');
const {
  dropTables,
  createAccounts,
  createTestTableTwo,
} = require('../../util/tableCreatorHelper');
const {
  insertTestTableTwoData,
  insertAccounts,
} = require('../../util/dataInsertHelper');
const { assertNumber } = require('../../util/assertHelper');

module.exports = function (knex) {
  // Certain dialects do not have proper insert with returning, so if this is true
  // then pick an id to use as the "foreign key" just for testing transactions.
  const constid = isRedshift(knex);
  let fkid = 1;

  describe('Transactions', function () {
    before(async () => {
      await dropTables(knex);
      await createAccounts(knex);
      await createTestTableTwo(knex);

      await insertAccounts(knex);
      await insertTestTableTwoData(knex);
    });

    it('can run with asCallback', function (ok) {
      knex
        .transaction(function (t) {
          t.commit();
        })
        .asCallback(ok);
    });

    it('should throw when undefined transaction is sent to transacting', function () {
      return knex
        .transaction(function (t) {
          knex('accounts').transacting(undefined);
        })
        .catch(function handle(error) {
          expect(error.message).to.equal(
            'Invalid transacting value (null, undefined or empty object)'
          );
        });
    });

    it('supports direct retrieval of a transaction without a callback', () => {
      const trxPromise = knex.transaction();
      const query = isOracle(knex) ? '1 as "result" from DUAL' : '1 as result';

      let transaction;
      return trxPromise
        .then((trx) => {
          transaction = trx;
          expect(trx.client.transacting).to.equal(true);
          return knex.transacting(trx).select(knex.raw(query));
        })
        .then((rows) => {
          assertNumber(knex, rows[0].result, 1);
          return transaction.commit();
        });
    });

    it('should throw when null transaction is sent to transacting', function () {
      return knex
        .transaction(function (t) {
          knex('accounts').transacting(null);
        })
        .catch(function handle(error) {
          expect(error.message).to.equal(
            'Invalid transacting value (null, undefined or empty object)'
          );
        });
    });

    it('should throw when empty object transaction is sent to transacting', function () {
      return knex
        .transaction(function (t) {
          knex('accounts').transacting({});
        })
        .catch(function handle(error) {
          expect(error.message).to.equal(
            'Invalid transacting value (null, undefined or empty object)'
          );
        });
    });

    it('should be able to commit transactions', function () {
      let id = null;
      return knex
        .transaction(function (t) {
          knex('accounts')
            .transacting(t)
            .returning('id')
            .insert({
              first_name: 'Transacting',
              last_name: 'User',
              email: 'transaction-test1@example.com',
              logins: 1,
              about: 'Lorem ipsum Dolore labore incididunt enim.',
              created_at: new Date(),
              updated_at: new Date(),
            })
            .then(function (resp) {
              return knex('test_table_two')
                .transacting(t)
                .insert({
                  account_id: constid ? ++fkid : (id = resp[0]),
                  details: '',
                  status: 1,
                });
            })
            .then(function () {
              t.commit('Hello world');
            });
        })
        .then(function (commitMessage) {
          expect(commitMessage).to.equal('Hello world');
          return knex('accounts').where('id', id).select('first_name');
        })
        .then(function (resp) {
          if (!constid) {
            expect(resp).to.have.length(1);
          }
        });
    });

    it('should be able to rollback transactions', function () {
      let id = null;
      const err = new Error('error message');
      return knex
        .transaction(function (t) {
          knex('accounts')
            .transacting(t)
            .returning('id')
            .insert({
              first_name: 'Transacting',
              last_name: 'User2',
              email: 'transaction-test2@example.com',
              logins: 1,
              about: 'Lorem ipsum Dolore labore incididunt enim.',
              created_at: new Date(),
              updated_at: new Date(),
            })
            .then(function (resp) {
              return knex('test_table_two')
                .transacting(t)
                .insert({
                  account_id: constid ? ++fkid : (id = resp[0]),
                  details: '',
                  status: 1,
                });
            })
            .then(function () {
              t.rollback(err);
            });
        })
        .catch(function (msg) {
          expect(msg).to.equal(err);
          return knex('accounts').where('id', id).select('first_name');
        })
        .then(function (resp) {
          expect(resp.length).to.equal(0);
        });
    });

    it('should be able to commit transactions with a resolved trx query', function () {
      let id = null;
      return knex
        .transaction(function (trx) {
          return trx('accounts')
            .returning('id')
            .insert({
              first_name: 'Transacting',
              last_name: 'User',
              email: 'transaction-test3@example.com',
              logins: 1,
              about: 'Lorem ipsum Dolore labore incididunt enim.',
              created_at: new Date(),
              updated_at: new Date(),
            })
            .then(function (resp) {
              return trx('test_table_two').insert({
                account_id: constid ? ++fkid : (id = resp[0]),
                details: '',
                status: 1,
              });
            })
            .then(function () {
              return 'Hello World';
            });
        })
        .then(function (commitMessage) {
          expect(commitMessage).to.equal('Hello World');
          return knex('accounts').where('id', id).select('first_name');
        })
        .then(function (resp) {
          if (!constid) {
            expect(resp).to.have.length(1);
          }
        });
    });

    it('should be able to rollback transactions with rejected trx query', function () {
      let id = null;
      const err = new Error('error message');
      let __knexUid,
        count = 0;
      return knex
        .transaction(function (trx) {
          return trx('accounts')
            .returning('id')
            .insert({
              first_name: 'Transacting',
              last_name: 'User2',
              email: 'transaction-test4@example.com',
              logins: 1,
              about: 'Lorem ipsum Dolore labore incididunt enim.',
              created_at: new Date(),
              updated_at: new Date(),
            })
            .then(function (resp) {
              return trx
                .insert({
                  account_id: constid ? ++fkid : (id = resp[0]),
                  details: '',
                  status: 1,
                })
                .into('test_table_two');
            })
            .then(function () {
              throw err;
            });
        })
        .on('query', function (obj) {
          count++;
          if (!__knexUid) __knexUid = obj.__knexUid;
          expect(__knexUid).to.equal(obj.__knexUid);
        })
        .catch(function (msg) {
          // oracle & mssql: BEGIN & ROLLBACK not reported as queries
          const expectedCount = isOracle(knex) || isMssql(knex) ? 2 : 4;
          expect(count).to.equal(expectedCount);
          expect(msg).to.equal(err);
          return knex('accounts').where('id', id).select('first_name');
        })
        .then(function (resp) {
          expect(resp).to.eql([]);
        });
    });

    it('should be able to run schema methods', async function () {
      // CockroachDB requires schema changes to happen before any writes, so trying to execute migrations in transaction directly fails due to attempt to get lock first
      if (isCockroachDB(knex)) {
        return this.skip();
      }

      let __knexUid,
        count = 0;
      const err = new Error('error message');
      if (isPostgreSQL(knex)) {
        return knex
          .transaction(function (trx) {
            return trx.schema
              .createTable('test_schema_transactions', function (table) {
                table.increments();
                table.string('name');
                table.timestamps();
              })
              .then(function () {
                return trx('test_schema_transactions').insert({ name: 'bob' });
              })
              .then(function () {
                return trx('test_schema_transactions').count('*');
              })
              .then(function (resp) {
                const _count = parseInt(resp[0].count, 10);
                expect(_count).to.equal(1);
                throw err;
              });
          })
          .on('query', function (obj) {
            count++;
            if (!__knexUid) __knexUid = obj.__knexUid;
            expect(__knexUid).to.equal(obj.__knexUid);
          })
          .catch(function (msg) {
            expect(msg).to.equal(err);
            expect(count).to.equal(5);
            return knex('test_schema_migrations').count('*');
          })
          .then(() => expect.fail('should never reach this line'))
          .catch(function (e) {
            // https://www.postgresql.org/docs/8.2/static/errcodes-appendix.html
            expect(e.code).to.equal('42P01');
          });
      } else {
        let id = null;
        const promise = knex
          .transaction(function (trx) {
            return trx('accounts')
              .returning('id')
              .insert({
                first_name: 'Transacting',
                last_name: 'User3',
                email: 'transaction-test5@example.com',
                logins: 1,
                about: 'Lorem ipsum Dolore labore incididunt enim.',
                created_at: new Date(),
                updated_at: new Date(),
              })
              .then(function (resp) {
                return trx('test_table_two').insert({
                  account_id: constid ? ++fkid : (id = resp[0]),
                  details: '',
                  status: 1,
                });
              })
              .then(function () {
                return trx.schema.createTable(
                  'test_schema_transactions',
                  function (table) {
                    table.increments();
                    table.string('name');
                    table.timestamps();
                  }
                );
              });
          })
          .on('query', function (obj) {
            count++;
            if (!__knexUid) __knexUid = obj.__knexUid;
            expect(__knexUid).to.equal(obj.__knexUid);
          })
          .then(function () {
            if (isMssql(knex)) {
              expect(count).to.equal(3);
            } else if (isOracle(knex)) {
              expect(count).to.equal(4);
            } else {
              expect(count).to.equal(5);
            }
            return knex('accounts').where('id', id).select('first_name');
          })
          .then(function (resp) {
            if (!constid) {
              expect(resp).to.have.length(1);
            }
          });

        try {
          await promise;
        } finally {
          await knex.schema.dropTableIfExists('test_schema_transactions');
        }
      }
    });

    it('should resolve with the correct value, #298', function () {
      return knex
        .transaction(function (trx) {
          trx.debugging = true;
          return Promise.resolve(null);
        })
        .then(function (result) {
          expect(result).to.equal(null);
        });
    });

    it('does not reject promise when rolling back a transaction', async () => {
      const trxProvider = knex.transactionProvider();
      const trx = await trxProvider();

      await trx.rollback();
      await trx.executionPromise;
    });

    it('should allow for nested transactions', function () {
      if (isRedshift(knex)) {
        return Promise.resolve();
      }
      return knex.transaction(function (trx) {
        return trx
          .select('*')
          .from('accounts')
          .then(function () {
            return trx.transaction(function () {
              return trx.select('*').from('accounts');
            });
          });
      });
    });

    it('#2213 - should wait for sibling transactions to finish', function () {
      if (isRedshift(knex)) {
        return this.skip();
      }

      const first = delay(50);
      const second = first.then(() => delay(50));
      return knex.transaction(function (trx) {
        return Promise.all([
          trx.transaction(function (trx2) {
            return first;
          }),
          trx.transaction(function (trx3) {
            return second;
          }),
        ]);
      });
    });

    it('#2213 - should not evaluate a Transaction container until all previous siblings have completed', async function () {
      if (isRedshift(knex)) {
        return this.skip();
      }

      const TABLE_NAME = 'test_sibling_transaction_order';
      await knex.schema.dropTableIfExists(TABLE_NAME);
      await knex.schema.createTable(TABLE_NAME, function (t) {
        t.string('username');
      });

      await knex.transaction(async function (trx) {
        await Promise.all([
          trx.transaction(async function (trx1) {
            // This delay provides `trx2` with an opportunity to run first.
            await delay(200);
            await trx1(TABLE_NAME).insert({ username: 'bob' });
          }),
          trx.transaction(async function (trx2) {
            const rows = await trx2(TABLE_NAME);

            // Even though `trx1` was delayed, `trx2` waited patiently for `trx1`
            // to finish.  Therefore, `trx2` discovers that there is already 1 row.
            expect(rows.length).to.equal(1);
          }),
        ]);
      });
    });

    it('#855 - Query Event should trigger on Transaction Client AND main Client', function () {
      let queryEventTriggered = false;

      knex.once('query', function (queryData) {
        queryEventTriggered = true;
        return queryData;
      });

      function expectQueryEventToHaveBeenTriggered() {
        expect(queryEventTriggered).to.equal(true);
      }

      return knex
        .transaction(function (trx) {
          trx.select('*').from('accounts').then(trx.commit).catch(trx.rollback);
        })
        .then(expectQueryEventToHaveBeenTriggered)
        .catch(expectQueryEventToHaveBeenTriggered);
    });

    it('#1040, #1171 - When pool is filled with transaction connections, Non-transaction queries should not hang the application, but instead throw a timeout error', async () => {
      //To make this test easier, I'm changing the pool settings to max 1.
      const knexConfig = _.clone(knex.client.config);
      knexConfig.pool.min = 0;
      knexConfig.pool.max = 1;
      knexConfig.acquireConnectionTimeout = 1000;

      const knexDb = new Knex(knexConfig);

      //Create a transaction that will occupy the only available connection, and avoid trx.commit.

      await knexDb.transaction(function (trx) {
        let sql = 'SELECT 1';
        if (isOracle(knex)) {
          sql = 'SELECT 1 FROM DUAL';
        } else if (isMssql(knex)) {
          // MSSQL does not have a boolean type.
          sql = 'SELECT CASE WHEN 1 = 1 THEN 1 ELSE 0 END';
        }

        trx
          .raw(sql)
          .then(function () {
            //No connection is available, so try issuing a query without transaction.
            //Since there is no available connection, it should throw a timeout error based on `aquireConnectionTimeout` from the knex config.
            return knexDb.raw('select * FROM accounts WHERE username = ?', [
              'Test',
            ]);
          })
          .then(function () {
            //Should never reach this point
            expect(false).to.be.ok();
          })
          .catch(function (error) {
            expect(error.bindings).to.be.an('array');
            expect(error.bindings[0]).to.equal('Test');
            expect(error.sql).to.equal(
              'select * FROM accounts WHERE username = ?'
            );
            expect(error.message).to.equal(
              'Knex: Timeout acquiring a connection. The pool is probably full. Are you missing a .transacting(trx) call?'
            );
            trx.commit(); //Test done
          });
      });

      await knexDb.destroy();
    });

    it('#1694, #1703 it should return connections to pool if acquireConnectionTimeout is triggered', async function () {
      const knexConfig = _.clone(knex.client.config);
      knexConfig.pool = {
        min: 0,
        max: 1,
      };
      knexConfig.acquireConnectionTimeout = 300;

      const db = new Knex(knexConfig);

      await expect(
        db.transaction(() => db.transaction(() => ({})))
      ).to.be.rejectedWith(KnexTimeoutError);

      await db.destroy();
    });

    /**
     * In mssql, certain classes of failures will "abort" a transaction, which
     * causes the subsequent ROLLBACK to fail (because the transaction has
     * been rolled back automatically).
     * An example of this type of auto-aborting error is creating a table with
     * a foreign key that references a non-existent table.
     */
    if (isMssql(knex)) {
      it('should rollback when transaction aborts', function () {
        let insertedId = null;
        let originalError = null;

        function transactionAbortingQuery(transaction) {
          return transaction.schema.createTable(
            'test_schema_transaction_fails',
            function (table) {
              table.string('name').references('id').on('non_exist_table');
            }
          );
        }

        function insertSampleRow(transaction) {
          return transaction('accounts')
            .returning('id')
            .insert({
              first_name: 'Transacting',
              last_name: 'User2',
              email: 'transaction-test2@example.com',
              logins: 1,
              about: 'Lorem ipsum Dolore labore incididunt enim.',
              created_at: new Date(),
              updated_at: new Date(),
            })
            .then(function (res0) {
              insertedId = res0[0];
            });
        }

        function querySampleRow() {
          return knex('accounts').where('id', insertedId).select('first_name');
        }

        function captureAndRethrowOriginalError(err) {
          originalError = err;
          throw err;
        }

        return knex
          .transaction(function (t) {
            return insertSampleRow(t)
              .then(function () {
                return transactionAbortingQuery(t);
              })
              .catch(captureAndRethrowOriginalError);
          })
          .then(function () {
            //Should never reach this point
            expect(false).to.be.ok;
          })
          .catch(function (err) {
            expect(err).to.exist;
            expect(err.originalError).to.equal(originalError);
            // confirm transaction rolled back
            return querySampleRow().then(function (resp) {
              expect(resp).to.be.empty;
            });
          });
      });
    }

    it('Rollback without an error should not reject with undefined #1966', function () {
      return knex
        .transaction(
          function (tr) {
            tr.rollback();
          },
          {
            doNotRejectOnRollback: false,
          }
        )
        .then(function () {
          expect(true).to.equal(false, 'Transaction should not have commited');
        })
        .catch(function (error) {
          expect(error instanceof Error).to.equal(true);
          expect(error.message).to.equal(
            'Transaction rejected with non-error: undefined'
          );
        });
    });

    it('#1052 - transaction promise mutating', function () {
      const transactionReturning = knex.transaction(function (trx) {
        return trx
          .insert({
            first_name: 'foo',
            last_name: 'baz',
            email: 'fbaz@example.com',
            logins: 1,
            about: 'Lorem ipsum Dolore labore incididunt enim.',
            created_at: new Date(),
            updated_at: new Date(),
          })
          .into('accounts');
      });

      return Promise.all([transactionReturning, transactionReturning]).then(
        ([ret1, ret2]) => {
          expect(ret1).to.equal(ret2);
        }
      );
    });

    it('should pass the query context to wrapIdentifier', function () {
      const originalWrapIdentifier = knex.client.config.wrapIdentifier;
      const spy = sinon.spy().named('calledWithContext');

      function restoreWrapIdentifier() {
        knex.client.config.wrapIdentifier = originalWrapIdentifier;
      }

      knex.client.config.wrapIdentifier = (value, wrap, queryContext) => {
        spy(queryContext);
        return wrap(value);
      };

      return knex
        .transaction(function (trx) {
          return trx.select().from('accounts').queryContext({ foo: 'bar' });
        })
        .then(function () {
          expect(spy.callCount).to.equal(1);
          expect(spy.calledWith({ foo: 'bar' })).to.equal(true);
        })
        .then(function () {
          restoreWrapIdentifier();
        })
        .catch(function (e) {
          restoreWrapIdentifier();
          throw e;
        });
    });

    it('connection should contain __knexTxId which is also exposed in query event', function () {
      return knex.transaction(function (trx) {
        const builder = trx.select().from('accounts');

        trx.on('query', function (obj) {
          expect(typeof obj.__knexTxId).to.equal(typeof '');
        });

        builder.on('query', function (obj) {
          expect(typeof obj.__knexTxId).to.equal(typeof '');
        });

        return builder;
      });
    });

    it('#3690 does not swallow exception when error during transaction occurs', async function () {
      const mysqlKillConnection = async (connection) =>
        knex.raw('KILL ?', [connection.threadId]);
      const killConnectionMap = {
        [drivers.MySQL]: mysqlKillConnection,
        [drivers.MySQL2]: mysqlKillConnection,
        [drivers.PostgreSQL]: async (connection) =>
          knex.raw('SELECT pg_terminate_backend(?)', [connection.processID]),
        /* TODO FIX
        mssql: async (connection, trx) => {
          const id = (await trx.raw('select @@SPID as id'))[0].id;
          return knex.raw(`KILL ${id}`);
        },
        oracledb: async (connection, trx) => {
          // https://stackoverflow.com/a/28815685
          const row = (await trx.raw(
            `SELECT * FROM V$SESSION WHERE AUDSID = Sys_Context('USERENV', 'SESSIONID')`
          ))[0];
          return knex.raw(
            `Alter System Kill Session '${row.SID}, ${
              row['SERIAL#']
            }' immediate`
          );
        },
      */
      };

      const killConnection = killConnectionMap[knex.client.driverName];
      if (!killConnection) {
        this.skip();
        return;
      }

      await expect(
        knex.transaction(async (trx2) => {
          await killConnection(await trx2.client.acquireConnection(), trx2);
          await trx2.transaction(async () => 2);
        })
      ).to.be.rejected;
    });

    it('handles promise rejections in nested Transactions (#3706)', async function () {
      const fn = sinon.stub();
      process.on('unhandledRejection', fn);
      try {
        await knex.transaction(async function (trx1) {
          // These two lines together will cause the underlying Transaction
          // to be rejected.  Prior to #3706, this rejection would be unhandled.
          const trx2 = await trx1.transaction(undefined, {
            doNotRejectOnRollback: false,
          });
          await trx2.rollback();

          await expect(trx2.executionPromise).to.have.been.rejected;
        });

        expect(fn).have.not.been.called;
      } finally {
        process.removeListener('unhandledRejection', fn);
      }
    });

    context('when a `connection` is passed in explicitly', function () {
      beforeEach(function () {
        this.sandbox = sinon.createSandbox();
      });

      afterEach(function () {
        this.sandbox.restore();
      });

      it('assumes the caller will release the connection', async function () {
        this.sandbox.spy(knex.client, 'releaseConnection');
        const conn = await knex.client.acquireConnection();
        try {
          await knex.transaction(
            async function (trx) {
              // Do nothing!
            },
            { connection: conn }
          );
        } catch (err) {
          // Do nothing.  The transaction could have failed due to some other
          // bug, and it might have still released the connection in the process.
        }

        expect(knex.client.releaseConnection).to.have.not.been.calledWith(conn);

        // By design, this line will only be reached if the connection
        // was never released.
        knex.client.releaseConnection(conn);

        // Note: It's still possible that the test might fail due to a Timeout
        // even after concluding.  This is because the underlying implementation
        // might have opened another connection by mistake, but never actually
        // closed it. (Ex: this was the case for OracleDB before fixing #3721)
      });
    });
  });
};