CODEWITHSUNDEEP
scalaforeign-keysauto-incrementslick
Chris Beach
Chris Beach

Reputation: 4392

Slick - Inserting a row into two tables linked with an auto-incrementing key?

I'm new to Slick and struggling to find a good canonical example for the following.

I'd like to insert a row into two tables. The first table has a primary key which auto-increments. The second table is related to the first via its primary key.

So I'd like to:

  1. Start a transaction
  2. Insert a row into table 1, which generates a key
  3. Insert a row into table 2, with a foreign key generated in the previous step
  4. End transaction (rollback steps 2 & 3 if either fail)

Would appreciate a canonical example for the above logic, and any related suggestions on my definitions below (I'm very new to Slick!). Thanks!

Insert logic for table 1

private def insertAndReturn(entry: Entry) = 
  entries returning entries.map(_.id) 
  into ((_, newId) => entry.copy(id = newId))

def insert(entry: Entry): Future[Entry] =
  db.run(insertAndReturn(entry) += entry)

(similar for table 2)

Table 1

class EntryTable(tag: Tag) extends Table[Entry](tag, "tblEntry") {
  def id = column[EntryId]("entryID", O.PrimaryKey, O.AutoInc)
  ... 

  def * = (id, ...).shaped <> (Entry.tupled, Entry.unapply)
}

Table 2

class UsernameChangeTable(tag: Tag) extends Table[UserNameChange](tag, "tblUserNameChange") {
  def entryId = column[EntryId]("entryID")
  ...

  def entry = foreignKey("ENTRY_FK", entryId, entryDao.entries)(
    _.id, onUpdate = Restrict, onDelete = Cascade
  )

I'm using a MySQL database and Slick 3.1.0.

Upvotes: 5

Views: 2061

Answers (2)

tanson
tanson

Reputation: 82

Here is a canonical example implementing this functionality

package models
import scala.concurrent.{Future, Await}
import scala.concurrent.ExecutionContext.Implicits.global
import scala.concurrent.duration.Duration
import slick.backend.DatabasePublisher
import slick.driver.H2Driver.api._

case class Supplier1(id:Int,name:String)

class Suppliers1(tag:Tag) extends Table[Supplier1](tag,"SUPPLIERS") {
  def id:Rep[Int] = column[Int]("SUP_ID",O.PrimaryKey,O.AutoInc)
  def name:Rep[String] = column[String]("NAME")

  def * = (id,name) <> 
    (Supplier1.tupled,Supplier1.unapply)
}

case class Coffee1(id:Int,name:String,suppId:Int)

class Coffees1(tag:Tag) extends Table[Coffee1](tag,"COFFEES"){
  def id:Rep[Int] = column[Int]("C_ID",O.PrimaryKey,O.AutoInc)
  def name:Rep[String] = column[String]("COFFEE_NAME")
  def suppId:Rep[Int] = column[Int]("SUP_ID")


  def * = (id,name,suppId) <> (Coffee1.tupled,Coffee1.unapply)

  def supplier = foreignKey("supp_fk", suppId, TableQuery[Suppliers])(_.id)
}

object HelloSlick1 extends App{
val db = Database.forConfig("h2mem1")
val suppliers = TableQuery[Suppliers1]
val coffees = TableQuery[Coffees1]
val setUpF = (suppliers.schema ++ coffees.schema).create
val insertSupplier = suppliers returning suppliers.map(_.id)
//val tx =  (insertSupplier += Supplier1(0,"SUPP 1")).flatMap(id=>(coffees += Coffee1(0,"COF",id))).transactionally
val tx = for{
   supId  <- insertSupplier += Supplier1(0,"SUPP 1")

  cId <- coffees += Coffee1(0,"COF",supId)
} yield ()
tx.transactionally

def exec[T](action: DBIO[T]): T =
    Await.result(db.run(action), Duration.Inf)
exec(setUpF) 
exec(tx)       
exec(suppliers.result.map(println))
exec(coffees.result.map(println))
}

Upvotes: 0

Nagarjuna Pamu
Nagarjuna Pamu

Reputation: 14825

All that you have to do is 

val tx = 
 insertAndReturn(entry).flatMap { id =>
   insertUserNameChange(UserNameChange(id, ...))
 }.transactionally

db.run(tx)

Note that insertUserNameChange is the function which inserts the UserNameChange instance into the database. It needs the EntryId which you get back from the previous insertion action.

Compose actions using flatMap and use transactionally to run the whole query in a transaction.

Your Slick tables look fine.

Upvotes: 6

Related Questions