Transaction stuff with knex

transactions.ts

declare const longApiCallThatTakesUpTo30seconds: () => Promise<any>
declare const sendEmailToCustomer: () => Promise<any>
declare const logSuccess: () => Promise<any>

/**
 * A silly example of moving money from one account to another. The basic use
 * case with no problems.
 */
export const myTransactionalDatabaseOperation = () => {
  return knex.transaction(async (trx) => {
    // now trx is a transaction object => all operations on it are executed in a transaction

    // reduce money from account 1, that has 150 moneys to begin with
    await trx('accounts').update({ balance: 100 }).where({ id: 1 })
    // increase money to account 2, that also has 150 moneys to begin with
    await trx('accounts').update({ balance: 200 }).where({ id: 2 })

    // at the end of the block, the transaction is committed
  })
}

/**
 * Here we introduce an API call that takes a long duration to complete. The
 * transaction must wait for that to complete before it can move on and commit.
 */
export const myTransactionalDatabaseOperationWithLongApiCall = () => {
  return knex.transaction(async (trx) => {
    // now trx is a transaction object => all operations on it are executed in a transaction

    // reduce money from account 1, that has 150 moneys to begin with
    await trx('accounts').update({ balance: 100 }).where({ id: 1 })

    // make a long API call, e.g. to validate something
    await longApiCallThatTakesUpTo30seconds()

    // The second operation needs to wait for the 30 second API call to complete
    // before it can be executed. During this time, the transaction hogs the DB
    // connection.

    // increase money to account 2, that also has 150 moneys to begin with
    await trx('accounts').update({ balance: 200 }).where({ id: 2 })

    // at the end of the block, the transaction is committed
  })
}

/**
 * Here we introduce an call to the global knex object from inside the transaction.
 * That call will not be executed with the transaction's DB connection, but will
 * instead require its own connection. This can lead to deadlock situations: if there
 * are no available connections, the non-transactional connection will wait for a
 * connection to become available, while the transaction will be reserving its own
 * connection until it can complete. Also, if the transaction is rolled back, the
 * non-transactional operation will not be.
 */
export const myTransactionalDatabaseOperationWithNonTransactionalOperation = () => {
  return knex.transaction(async (trx) => {
    // now trx is a transaction object => all operations on it are executed in a transaction

    // reduce money from account 1, that has 150 moneys to begin with
    await trx('accounts').update({ balance: 100 }).where({ id: 1 })

    // make a non-transactional call to knex, this starts a separate db connection
    await knex('logs').insert({ message: 'something happened' })

    // increase money to account 2, that also has 150 moneys to begin with
    await trx('accounts').update({ balance: 200 }).where({ id: 2 })

    // an error happens! the transaction is rolled back, but the non-transactional
    // operation cannot be
    throw new Error('something went wrong')

    // so we are left with no money having moved, but we have still inserted a log
    // to the database
  })
}

/**
 * Here we perform some not-database-related operations inside the transaction, in
 * this case logging that we had successfully moved money from one account to the
 * other, and sending some email to the customer. If the transaction is rolled back,
 * not-database-related operations cannot be rolled back. Therefore we should ideally
 * trigger any side effects that depend on the transaction result only after it has
 * completed.
 */
export const myTransactionalDatabaseOperationWithTooOptimisticSideEffects = () => {
  return knex.transaction(async (trx) => {
    // now trx is a transaction object => all operations on it are executed in a transaction

    // reduce money from account 1, that has 150 moneys to begin with
    await trx('accounts').update({ balance: 100 }).where({ id: 1 })

    // increase money to account 2, that also has 150 moneys to begin with
    await trx('accounts').update({ balance: 200 }).where({ id: 2 })

    // all good right? perform some side effects sending emails and updating our monitoring
    await sendEmailToCustomer()
    await logSuccess()

    // but oops, something goes wrong in the transaction still, so the database
    // operations are rolled back, but the side effects are not
    throw new Error('something went wrong')

    // so we are left with no money having moved, but we have still sent an email
    // and logged something telling us we have
  })
}

/**
 * Here we try to read some data written by the transaction, from outside the transaction,
 * before the transaction is committed. This will not work, because that data is not yet
 * available for the non-transactional connection to read.
 */
 export const myTransactionalDatabaseOperationWithNonTransactionalRead = () => {
  return knex.transaction(async (trx) => {
    // now trx is a transaction object => all operations on it are executed in a transaction

    // reduce money from account 1, that has 150 moneys to begin with
    await trx('accounts').update({ balance: 100 }).where({ id: 1 })

    // read the balance of account 1, _outside the transaction_
    await knex('accounts').select('balance').where({ id: 1 })

    // -> The balance is still 150, because the transaction has not yet committed!
    // If we try to use that balance to do something, we introduce data integrity
    // errors.

    // increase money to account 2, that also has 150 moneys to begin with
    await trx('accounts').update({ balance: 200 }).where({ id: 2 })

    // only after this function returns (or, the returned Promise resolves), the
    // transaction is committed, and the updated data can be read.
  })
}