How to handle optional db step in slick 3?

Question

I'm sure I'm simply facing a mental block with the functional model of Slick 3, but I cannot discern how to transactionally sequence an optional dependent db step in Slick 3. Specifically, I have a table with an optional (nullable) foreign key and I want it to be set to the ID of the inserted dependent record (if any, else null). That is, roughly:

if ( x is non null ) 
    start transaction
    id = insert x
    insert y(x = id)
    commit
else
    start transaction
    insert y(x = null)
    commit

Of course, I'd rather not have the big if around the choice. Dependencies without the Option[] seem (relatively) straightforward, but the option is throwing me.

Precise example code (sans imports) follows. In this example, the question is how to save both x (a) and y (b) in the same transaction both if y is None or not. Saving Y itself seems straightforward enough as every related C has a non-optional B reference, but addressing the optional reference in A is unclear (to me).

    object test {
      implicit val db = Database.forURL("jdbc:h2:mem:DataTableTypesTest;DB_CLOSE_DELAY=-1", driver = "org.h2.Driver")


      /* Data model */
      case class A(id: Long, b: Option[Long], s: String)

      class As(tag: Tag) extends Table[A](tag, "As") {
        def id = column[Long]("ID", O.PrimaryKey, O.AutoInc)
        def b = column[Option[Long]]("B")
        def s = column[String]("S")

        def * = (id, b, s) <> (A.tupled, A.unapply)
      }
      val as = TableQuery[As]

      case class B(id: Long, s: String)

      class Bs(tag: Tag) extends Table[B](tag, "Bs") {
        def id = column[Long]("ID", O.PrimaryKey, O.AutoInc)
        def s = column[String]("S")

        def * = (id, s) <> (B.tupled, B.unapply)
      }
      val bs = TableQuery[Bs]

      case class C(id: Long, b: Long, s: String)

      class Cs(tag: Tag) extends Table[C](tag, "Cs") {
        def id = column[Long]("ID", O.PrimaryKey, O.AutoInc)
        def b = column[Long]("B")
        def s = column[String]("S")

        def * = (id, b, s) <> (C.tupled, C.unapply)
      }
      val cs = TableQuery[Cs]


      /* Object model */
      case class X(id: Long, s: String, y: Option[Y])

      case class Y(id: Long, s: String, z: Set[Z])

      case class Z(id: Long, s: String)


      /* Mappers */
      def xToA(x: X, bId: Option[Long]): A = { A(x.id, bId, x.s) }
      def yToB(y: Y): B = { B(y.id, y.s) }
      def zToC(z: Z, bId: Long): C = { C(z.id, bId, z.s) }

      /* Given */
      val example1 = X(0, "X1", Some(Y(0, "Y1", Set(Z(0, "Z11"), Z(0, "Z12")))))
      val example2 = X(0, "X2", Some(Y(0, "Y2", Set())))
      val example3 = X(0, "X3", None)

      Await.result(db.run((as.schema ++ bs.schema ++ cs.schema).create), 10.seconds)

      val examples = Seq(example1, example2, example3)
      for ( example <- examples ) {
        val saveY = (for { y <- example.y }
            yield ( for {
              id <- (bs returning bs.map(_.id)) += yToB(y)
              _  <- cs ++= y.z.map(zToC(_, id))
            } yield id) transactionally)
        if ( saveY.isDefined ) Await.result(db.run(saveY.get), 10.seconds)
      }

      println(Await.result(
        db.run(
          (for { a <- as } yield a).result
        ),
        10.seconds
      ))

      println(Await.result(
        db.run(
          (for { b <- bs } yield b).result
        ),
        10.seconds
      ))

      println(Await.result(
        db.run(
          (for { c <- cs } yield c).result
        ),
        10.seconds
      ))
    }

Answer 1

This is fairly straightforward; just use the monadic-ness of DBIO:

// Input B value; this is your `x` in the question.
val x: Option[B] = _
// Assume `y` is fully-initialized with a `None` `b` value.
val y: A = _

// DBIO wrapping the newly-inserted ID, if `x` is set.
val maybeInsertX: DBIO[Option[Int]] = x match {
  case Some(xToInsert) =>
    // Insert and return the new ID.
    val newId: DBIO[Int] = bs.returning(bs.map(_.id)) += xToInsert
    // Map to the expected Option.
    newId.map(Some(_))
  case None =>
    // No x means no ID.
    DBIO.successful(None)
}

// Now perform your insert, copying in the newly-generated ID.
val insertA: DBIO[Int] = maybeInsertX.flatMap(bIdOption =>
  as += y.copy(b = bIdOption)
)

// Run transactionally.
db.run(insertA.transactionally)