How to use transaction in slick

Question

I have insert method like this (weight is index)

implicit def run[A](action: DBIOAction[A, NoStream, _ <: slick.dbio.Effect]): Future[A] = {
    db.run(action)
  }

def insert(newCategory: CategoryExtractor): Future[Either[String, CategoryResponse]] = {
        category.map(_.weight).max.result.flatMap {
          case Some(weight) =>
            val temp = newCategory.copy(weight = weight+1)
            (category += temp).andThen(DBIO.successful(Right(toCategoryExtractor(temp))))
          case None =>
            val temp = newCategory.copy(weight = 1)
            (category += temp).andThen(DBIO.successful(Right(toCategoryExtractor(temp))))
        }
  }

and I call it 2 time

insert(CategoryExtractor("1", "name", "scala every where", 0, 0, 0, None)) onComplete {
    case Success(data) => println(data)
  }

insert(CategoryExtractor("2", "name", "haskell every where", 0, 0, 0, None)) onComplete {
    case Success(data) => println(data)
  }

and it return exception(Unique index).

How to fix this and I don't use for-comprehension or insert in first onComplete. Just call it 2 time.

Thank you.

Answer 1

That is a common mistake - converting to Future to early (in other words calling db.run(...) to early).

What you need to do is to remove this method as it (maybe a little unintuitively) brings more harm than good:

implicit def run[A](action: DBIOAction[A, NoStream, _ <: slick.dbio.Effect]): Future[A] = {
    db.run(action)
  }

Rule of thumb is basically that you usually would like to strictly control you actual database interactions (and transactional boundaries) so I would advice against any kind of implicits in this area. After all it's one of the driving ideas behind this library - Slick tries to be very explicit in your interactions between application and db (contrary to classic ORMs - where using accessor might have actually fired lazy call to db or setting a value through mutator might resulted in actual database update).

Then you need to change the return type to this (changed Future to DBIO):

def insert(newCategory: CategoryExtractor): DBIO[Either[String, CategoryResponse]] = {
...
  }

and than you do this:

val firstInsert = insert(CategoryExtractor("1", "name", "scala every where", 0, 0, 0, None)) map {
    data => println(data)
}

val secondInsert = insert(CategoryExtractor("2", "name", "haskell every where", 0, 0, 0, None)) map {
    data => println(data)
}

db.run(DBIO.seq(firstInsert, secondInsert).transactionally))

Basically the thing is: as soon as you convert DBIO to Future you loose capability of bundling actions into single transaction. So you basically do everything with use of different transformations of DBIO (usual stuff: map, flatMap, seq etc) and only as the very last step you fire db.run(yourComposedDbio.transactionally).

EDIT: Here is some more information about handling transactions and DBIO composition from the presentation I gave couple of weeks ago. Relevant slide: http://slides.com/pdolega/slick-101#/85 (and further).

Also there is a great workshop run by Dave Gurnell where he talks about this at around: 01:05:00 (link here: https://vimeo.com/148074461 )