Fix Mixing of Knex and Sequelize Transactions

[klondikemarlen]

Fixes https://github.com/icefoganalytics/travel-authorization/issues/190

Relates to:

• https://github.com/icefoganalytics/travel-authorization/pull/188 (and various child PRs)

Context

Is your feature request related to a problem? Please describe. I had thought that mixing Knex and Sequelize transactions would work, but had not tested it. It does not work, as identified in https://github.com/icefoganalytics/wrap/pull/16

Describe the solution you'd like Switch to only using Sequelize code when running in a transaction. Use https://github.com/icefoganalytics/wrap/pull/16/commits/ec6cae86f72f7af0342396513da057628ccf1c22.

Describe alternatives you've considered Removing all transaction related code during migration to Sequelize. I don't like this solution because the transaction code might never be added back in.

Additional context

const knexTransaction = await knex.transaction()
const [newPlayer] = await knexTransaction("workflow_players").insert(player).returning<WorkflowPlayer[]>("*")
knexTransaction.comit()

becomes

sequelize.transaction(async () => {
  // other code

  const [newPlayer] = await knexQueryToSequelizeSelect<WorkflowPlayer>(
    knex("workflow_players").insert(player).returning<WorkflowPlayer[]>("*")
  )
})

// other code

Implementation

Add api/src/db/utils/knex-query-to-sequelize-select.ts to transform knex queries to Sequelize ones.

Testing Instructions

1. Run the test suite via dev test (or dev test_api)
2. Boot the app via dev up
3. Log in to the app at http://localhost:8080

In a api/src/now.ts files run this code

import { knexQueryToSequelizeSelect } from "@/db/utils/knex-query-to-sequelize-select"
import dbLegacy from "@/db/db-client-legacy"

async function main() {
  const newFlightOption = {
    flightOptionID: 1,
    flightRequestID: 1,
    cost: "100",
    flightPreference: "Economy",
    leg: "1",
    duration: "1h",
  }

  console.log(
    `dbLegacy("travelDeskFlightOption").insert(newFlightOption, "flightOptionID").toSQL().toNative():`,
    dbLegacy("travelDeskFlightOption").insert(newFlightOption, "flightOptionID").toSQL().toNative()
  )
  const travelDeskFlightOption = await knexQueryToSequelizeSelect(
    dbLegacy("travelDeskFlightOption").insert(newFlightOption, "flightOptionID")
  )

  console.log(`travelDeskFlightOption:`, travelDeskFlightOption)
}

if (require.main === module) {
  ;(async () => {
    try {
      await main()
      process.exit(0)
    } catch (error) {
      console.error("Error occurred:", error)
      process.exit(1)
    }
  })()
}