knex/test/integration2/query/update/updates.spec.js

'use strict';

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

const { TEST_TIMESTAMP } = require('../../../util/constants');
const {
  getAllDbs,
  getKnexForDb,
} = require('../../util/knex-instance-provider');
const logger = require('../../../integration/logger');
const {
  dropTables,
  createAccounts,
} = require('../../../util/tableCreatorHelper');
const { insertAccounts } = require('../../../util/dataInsertHelper');
const { assertNumber } = require('../../../util/assertHelper');

describe('Updates', function () {
  getAllDbs().forEach((db) => {
    describe(db, () => {
      let knex;
      let accountId1;

      before(async () => {
        knex = logger(getKnexForDb(db));
        await dropTables(knex);
        await createAccounts(knex);
      });

      after(async () => {
        await dropTables(knex);
        return knex.destroy();
      });

      beforeEach(async () => {
        await knex('accounts').truncate();
        await insertAccounts(knex);
        const accounts = await knex('accounts').select().where({
          email: 'test1@example.com',
        });
        accountId1 = accounts[0].id;
      });

      it('should handle updates', function () {
        return knex('accounts')
          .where('id', 1)
          .update({
            first_name: 'User',
            last_name: 'Test',
            email: 'test100@example.com',
          })
          .testSql(function (tester) {
            tester(
              'mysql',
              'update `accounts` set `first_name` = ?, `last_name` = ?, `email` = ? where `id` = ?',
              ['User', 'Test', 'test100@example.com', 1],
              1
            );
            tester(
              'pg',
              'update "accounts" set "first_name" = ?, "last_name" = ?, "email" = ? where "id" = ?',
              ['User', 'Test', 'test100@example.com', 1],
              1
            );
            tester(
              'pg-redshift',
              'update "accounts" set "first_name" = ?, "last_name" = ?, "email" = ? where "id" = ?',
              ['User', 'Test', 'test100@example.com', 1],
              1
            );
            tester(
              'sqlite3',
              'update `accounts` set `first_name` = ?, `last_name` = ?, `email` = ? where `id` = ?',
              ['User', 'Test', 'test100@example.com', 1],
              1
            );
            tester(
              'mssql',
              'update [accounts] set [first_name] = ?, [last_name] = ?, [email] = ? where [id] = ?;select @@rowcount',
              ['User', 'Test', 'test100@example.com', 1],
              1
            );
          });
      });

      it('should allow for null updates', function () {
        return knex('accounts')
          .where('id', 1000)
          .update({
            email: 'test100@example.com',
            first_name: null,
            last_name: 'Test',
          })
          .testSql(function (tester) {
            tester(
              'mysql',
              'update `accounts` set `email` = ?, `first_name` = ?, `last_name` = ? where `id` = ?',
              ['test100@example.com', null, 'Test', 1000],
              0
            );
            tester(
              'mssql',
              'update [accounts] set [email] = ?, [first_name] = ?, [last_name] = ? where [id] = ?;select @@rowcount',
              ['test100@example.com', null, 'Test', 1000],
              0
            );
          });
      });

      it('should immediately return updated value for other connections when updating row to DB returns', function () {
        return knex('accounts').then((res) => {
          function runTest() {
            return Promise.all(
              res.map((origRow) => {
                return Promise.resolve()
                  .then(() => {
                    return knex.transaction((trx) =>
                      trx('accounts')
                        .where('id', origRow.id)
                        .update({ balance: 654 })
                    );
                  })
                  .then(() => {
                    return knex('accounts')
                      .where('id', origRow.id)
                      .then((res) => res[0]);
                  })
                  .then((updatedRow) => {
                    expect(updatedRow.balance).to.equal(654);
                    return knex.transaction((trx) =>
                      trx('accounts')
                        .where('id', origRow.id)
                        .update({ balance: origRow.balance })
                    );
                  })
                  .then(() => {
                    return knex('accounts')
                      .where('id', origRow.id)
                      .then((res) => res[0]);
                  })
                  .then((updatedRow) => {
                    expect(updatedRow.balance).to.equal(origRow.balance);
                  });
              })
            );
          }

          // run few times to try to catch the problem
          return runTest()
            .then(() => runTest())
            .then(() => runTest())
            .then(() => runTest())
            .then(() => runTest())
            .then(() => runTest())
            .then(() => runTest())
            .then(() => runTest())
            .then(() => runTest())
            .then(() => runTest())
            .then(() => runTest())
            .then(() => runTest())
            .then(() => runTest())
            .then(() => runTest())
            .then(() => runTest())
            .then(() => runTest())
            .then(() => runTest())
            .then(() => runTest())
            .then(() => runTest());
        });
      });

      it('should increment a value', function () {
        return knex('accounts')
          .select('logins')
          .where('id', accountId1)
          .then(function (accounts) {
            return knex('accounts')
              .where('id', accountId1)
              .increment('logins')
              .then(function (rowsAffected) {
                expect(rowsAffected).to.equal(1);
                return knex('accounts')
                  .select('logins')
                  .where('id', accountId1);
              })
              .then(function (accounts2) {
                assertNumber(
                  knex,
                  accounts2[0].logins,
                  parseInt(accounts[0].logins) + 1
                );
              });
          });
      });

      it('should increment a negative value', function () {
        return knex('accounts')
          .select('logins')
          .where('id', accountId1)
          .then(function (accounts) {
            return knex('accounts')
              .where('id', accountId1)
              .increment('logins', -2)
              .then(function (rowsAffected) {
                expect(rowsAffected).to.equal(1);
                return knex('accounts')
                  .select('logins')
                  .where('id', accountId1);
              })
              .then(function (accounts2) {
                assertNumber(knex, accounts2[0].logins, accounts[0].logins - 2);
              });
          });
      });

      it('should increment a float value', function () {
        return knex('accounts')
          .select('balance')
          .where('id', accountId1)
          .then(function (accounts) {
            return knex('accounts')
              .where('id', accountId1)
              .increment('balance', 22.53)
              .then(function (rowsAffected) {
                expect(rowsAffected).to.equal(1);
                return knex('accounts')
                  .select('balance')
                  .where('id', accountId1);
              })
              .then(function (accounts2) {
                expect(accounts[0].balance + 22.53).to.be.closeTo(
                  accounts2[0].balance,
                  0.001
                );
              });
          });
      });

      it('should decrement a value', function () {
        return knex('accounts')
          .select('logins')
          .where('id', accountId1)
          .then(function (accounts) {
            return knex('accounts')
              .where('id', accountId1)
              .decrement('logins')
              .then(function (rowsAffected) {
                expect(rowsAffected).to.equal(1);
                return knex('accounts')
                  .select('logins')
                  .where('id', accountId1);
              })
              .then(function (accounts2) {
                assertNumber(knex, accounts2[0].logins, accounts[0].logins - 1);
              });
          });
      });

      it('should decrement a negative value', function () {
        return knex('accounts')
          .select('logins')
          .where('id', accountId1)
          .then(function (accounts) {
            return knex('accounts')
              .where('id', accountId1)
              .decrement('logins', -2)
              .then(function (rowsAffected) {
                expect(rowsAffected).to.equal(1);
                return knex('accounts')
                  .select('logins')
                  .where('id', accountId1);
              })
              .then(function (accounts2) {
                assertNumber(
                  knex,
                  accounts2[0].logins,
                  parseInt(accounts[0].logins) + 2
                );
              });
          });
      });

      it('should decrement a float value', async function () {
        return knex('accounts')
          .select('balance')
          .where('id', accountId1)
          .then(function (accounts) {
            return knex('accounts')
              .where('id', accountId1)
              .decrement('balance', 10.29)
              .then(function (rowsAffected) {
                expect(rowsAffected).to.equal(1);
                return knex('accounts')
                  .select('balance')
                  .where('id', accountId1);
              })
              .then(function (accounts2) {
                expect(accounts[0].balance - 10.29).to.be.closeTo(
                  accounts2[0].balance,
                  0.001
                );
              });
          });
      });

      it('should allow returning for updates', async function () {
        await knex('accounts').where('id', accountId1).update({
          balance: 12.240000000000002,
        });

        await knex('accounts')
          .where('id', accountId1)
          .update(
            {
              email: 'test100@example.com',
              first_name: 'UpdatedUser',
              last_name: 'UpdatedTest',
            },
            '*'
          )
          .testSql(function (tester) {
            tester(
              'mysql',
              'update `accounts` set `email` = ?, `first_name` = ?, `last_name` = ? where `id` = ?',
              ['test100@example.com', 'UpdatedUser', 'UpdatedTest', 1],
              1
            );
            tester(
              'pg',
              'update "accounts" set "email" = ?, "first_name" = ?, "last_name" = ? where "id" = ? returning *',
              ['test100@example.com', 'UpdatedUser', 'UpdatedTest', '1'],
              [
                {
                  id: '1',
                  first_name: 'UpdatedUser',
                  last_name: 'UpdatedTest',
                  email: 'test100@example.com',
                  logins: 1,
                  balance: 12.24,
                  about: 'Lorem ipsum Dolore labore incididunt enim.',
                  created_at: TEST_TIMESTAMP,
                  updated_at: TEST_TIMESTAMP,
                  phone: null,
                },
              ]
            );
            tester(
              'cockroachdb',
              'update "accounts" set "email" = ?, "first_name" = ?, "last_name" = ? where "id" = ? returning *',
              ['test100@example.com', 'UpdatedUser', 'UpdatedTest', accountId1],
              [
                {
                  id: accountId1,
                  first_name: 'UpdatedUser',
                  last_name: 'UpdatedTest',
                  email: 'test100@example.com',
                  logins: '1',
                  balance: 12.24,
                  about: 'Lorem ipsum Dolore labore incididunt enim.',
                  created_at: TEST_TIMESTAMP,
                  updated_at: TEST_TIMESTAMP,
                  phone: null,
                },
              ]
            );
            tester(
              'pg-redshift',
              'update "accounts" set "email" = ?, "first_name" = ?, "last_name" = ? where "id" = ?',
              ['test100@example.com', 'UpdatedUser', 'UpdatedTest', 1],
              1
            );
            tester(
              'sqlite3',
              'update `accounts` set `email` = ?, `first_name` = ?, `last_name` = ? where `id` = ?',
              ['test100@example.com', 'UpdatedUser', 'UpdatedTest', 1],
              1
            );
            tester(
              'oracledb',
              'update "accounts" set "email" = ?, "first_name" = ?, "last_name" = ? where "id" = ? returning "ROWID" into ?',
              [
                'test100@example.com',
                'UpdatedUser',
                'UpdatedTest',
                1,
                (v) => v.toString() === '[object ReturningHelper:ROWID]',
              ],
              1
            );
            tester(
              'mssql',
              'update [accounts] set [email] = ?, [first_name] = ?, [last_name] = ? output inserted.* where [id] = ?',
              ['test100@example.com', 'UpdatedUser', 'UpdatedTest', '1'],
              [
                {
                  id: '1',
                  first_name: 'UpdatedUser',
                  last_name: 'UpdatedTest',
                  email: 'test100@example.com',
                  logins: 1,
                  balance: 12.240000000000002,
                  about: 'Lorem ipsum Dolore labore incididunt enim.',
                  created_at: TEST_TIMESTAMP,
                  updated_at: TEST_TIMESTAMP,
                  phone: null,
                },
              ]
            );
          });
      });
    });
  });
});