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

Question

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.

Answer 1

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.