v0.14.1 Transaction handling

3 new transaction handling methods are introduced for SQL databases on the jvm side.

Examples shown with the sync api - could as well be async, zio or io apis

Transaction bundle

Transact multiple actions in one commit with transact(<actions...>)

transact(
  Ns.int(1).save,         
  Ns.int.insert(2, 3),    
  Ns(1).delete,           
  Ns(3).int.*(10).update, 
) // result: List(2, 30)

It's easier to write than calling .transact on all 4 actions

Ns.int(1).save.transact         
Ns.int.insert(2, 3).transact   
Ns(1).delete.transact          
Ns(3).int.*(10).update.transact

Rollback all on any error

transact(<actions..>) rolls back all actions if any fails. If for instance Type.int has a validation defined in the Data Model that requires integers to be larger than 1, then both of the following actions will be rolled back:

try {
  transact(
    Type.int.insert(4, 5),
    Type.int(1).save, // not valid, triggers rollback of both actions
  )
} catch {
  case ValidationErrors(errorMap) =>
    errorMap.head._2.head ==>
      s"""Type.int with value `1` doesn't satisfy validation:
         |_ > 2
         |""".stripMargin
}

Unit of work

When you need to transact multiple actions and also at the same time query things, you can use unitOfWork:

// Initial balance in two bank accounts
Ns.s("fromAccount").int(100).save.transact
Ns.s("toAccount").int(50).save.transact

try {
  unitOfWork {
    Ns.s_("fromAccount").int.-(200).update.transact
    Ns.s_("toAccount").int.+(200).update.transact

    // Check that fromAccount had sufficient funds
    if (Ns.s_("fromAccount").int.query.get.head < 0) {
      // Abort all transactions in this unit of work
      throw new Exception(
        "Insufficient funds in fromAccount..."
      )
    }
  }
} catch {
  case e: Exception =>
    // Do something with failure...
    e.getMessage ==> "Insufficient funds in fromAccount..."
}

// No data transacted
Ns.s.int.query.get ==> List(
  ("fromAccount", 100),
  ("toAccount", 50),
)

Savepoints

Rollback scoped actions within a unitOfWork with savepoint:

Ns.int.insert(1 to 4).transact
Ns.int(count).query.get.head ==> 4

unitOfWork {
  savepoint { sp =>
    // Delete all
    Ns.int_.delete.transact
    Ns.int(count).query.get.head ==> 0

    // Roll back delete action within savepoint scope
    sp.rollback()
    Ns.int(count).query.get.head ==> 4
  }
}

// Nothing deleted
Ns.int(count).query.get.head ==> 4

Throwing an exception instead of calling sp.rollback() has the same effect.

Using savepoints can be a convenient way of rolling back only parts of a larger body of transactions/unitOfWork. Savepoints can even be nested (see tests).