Transactions

ExoQuery supports transactions! Once a query or action is built e.g. once you do .buildFor.Postgres() you will get one of three possbile things:

1. A SqlCompiledQuery object. This is a query that can be executed on the database.
2. A SqlCompiledAction object. This is an action that can be executed on the database.
3. A SqlCompiledBatchAction object This is a SQL batch action, typically it is not supported for transactions.

Once you have imported a ExoQuery runner project (e.g. exoquery-runner-jdbc) and created a DatabaseController (e.g. JdbcControllers.Postgres), you can run the query or action:

val ds: DataSource = ...
val controller = JdbcControllers.Postgres(ds)

val getJoes = sql {
  Table<Person>().filter { p -> p.name == "Joe" }
}.buildFor.Postgres()

fun setJoesToJims(ids: List<String>) = sql {
  update<Person> { set(name to "Jim").where { p -> p.id in params(ids) } }
}.buildFor.Postgres()

controller.transaction {
  val allJoes = getJoes.runOnTransaction()
  // Execute some runtime logic to filter the people we want to update i.e. `shouldActuallyBeUpdated`
  val someJoes = allJoes.filter { p -> shouldActuallyBeUpdated(p) }
  setJoesToJims(someJoes).runOnTransaction()
}

Rollback Behavior

If the transaction block throws an exception, the transaction is automatically rolled back:

controller.transaction {
  Sql("INSERT INTO Person (id, name) VALUES (1, 'Joe')").action().runOnTransaction()
  throw RuntimeException("Something went wrong") // Transaction is rolled back
}

Nested Transactions

ExoQuery supports nesting transaction blocks. The semantics are:

• The first (outermost) transaction actually begins the database transaction and is responsible for committing or rolling back.
• Inner transaction blocks detect that a transaction is already in progress and simply execute within the same connection/transaction scope. They do not commit independently; any exception thrown will still roll back the entire outer transaction.

controller.transaction {
  Sql("INSERT INTO Person (id, name) VALUES (1, 'Joe')").action().runOnTransaction()
  
  // Inner transaction - runs in the same transaction scope
  controller.transaction {
    Sql("INSERT INTO Person (id, name) VALUES (2, 'Jane')").action().runOnTransaction()
  }
  // Both inserts commit together when the outer block completes
}

Coroutine Support

ExoQuery controllers are implemented to be coroutine-first. Instead of relying on thread-local storage or manually passing a JDBC Connection around, ExoQuery attaches the current session to the CoroutineContext. This means the session/transaction information is automatically propagated to child coroutines created with coroutineScope/withContext/launch within the same parent scope.

controller.transaction {
  coroutineScope {
    launch {
      Sql("INSERT INTO Person (id, name) VALUES (1, 'Joe')").action().runOnTransaction()
    }
    launch {
      Sql("INSERT INTO Person (id, name) VALUES (2, 'Jane')").action().runOnTransaction()
    }
  }
  // Both inserts are part of the same transaction
}

If any child fails and the exception escapes the transaction block, the whole transaction is rolled back.