Understanding Foreign Key with Slick and Scala

Currently, I am trying to build a simple web app with Play framework while learning the framework and scala language. Then I later found out that I need to learn Slick in order to store data. Since I am learning Slick, I might just as well know a little about relational database. And then I realize that how am I support to relate two tables in Slick. That’s where I found Foreign Key. One key to rule them all. Maybe.

Github reference I refer from:

1. UnderscoreIo

1. Setup database config

This is the typesafe config at ‘application.conf’. You can refer to this doc. BTW, You don’t need to setup any database like MySQL or Postgresql because we are using h2 driver.

scalaxdb = {
connectionPool = disabled
url = "jdbc:h2:mem:scalaxdb"
driver = "org.h2.Driver"
keepAliveConnection = true
}

2. Setup Tables

Here we config how the album and song table look like. Take note of def album = foreignKey("album_fk", albumId, AlbumTable)(_.id, onDelete = ForeignKeyAction.Cascade) in the song table configuration. We will store ablum id as foreign key in the song table.

case class Album(artist: String,
                   title: String,
                   id: Long = 0L)

case class Song(name: String,
                 albumId: Long,
                 id: Long = 0L)

  class AlbumTable(tag: Tag) extends Table[Album](tag, "albums") {
    def artist = column[String]("artist")
    def title = column[String]("title")
    def id = column[Long]("id", O.PrimaryKey, O.AutoInc)

    def * = (artist, title, id) <> (Album.tupled, Album.unapply)
  }

  class SongTable(tag: Tag) extends Table[Song](tag, "songs") {
    def name = column[String]("title")
    def albumId = column[Long]("albumId")
    def id = column[Long]("id", O.PrimaryKey, O.AutoInc)
    def * = (name, albumId, id) <> (Song.tupled, Song.unapply)

    def album = foreignKey("album_fk", albumId, AlbumTable)(_.id, onDelete = ForeignKeyAction.Cascade)
  }

3. Create convenient functions

There are convenient functions to query. Take note here: albumId insertAlbum += Album("Keyboard", "Hit it Bro"). This action will generate auto increment ID after Album is created. Then this ID will be stored in Song("Hit me one more time", albumId), as albumId so that they can link together.

lazy val AlbumTable = TableQuery[AlbumTable]
lazy val SongTable = TableQuery[SongTable]
lazy val ddl = AlbumTable.schema ++ SongTable.schema
lazy val insertAlbum = AlbumTable returning AlbumTable.map(_.id)


 // Actions ------------------------------------
val createTables = (AlbumTable.schema ++ SongTable.schema).create

val populateFirstTime =
    for {
      albumId <- insertAlbum += Album("Keyboard", "Hit it Bro")
      count <- SongTable ++= Seq(
        Song("Hit me one more time", albumId),
        Song("Press them all", albumId)
      )
    } yield count

val populateSecondTime =
    for {
      albumId <- insertAlbum += Album("Mouse", "Click it Bro")
      count <- SongTable ++= Seq(
        Song("Jumping around", albumId),
        Song("Sneaking around", albumId)
      )
    } yield count


val join = for {
    song <- SongTable
    album <- song.album
  } yield (album.artist, song.name)


 // Database -----------------------------------

val db = Database.forConfig("scalaxdb")

  // Let's go! ----------------------------------
def exec[T](action: DBIO[T]): T =
  Await.result(db.run(action), 2 seconds)

4. Executing Actions

Finally, we can run these actions to see the results. Again, take note of this exec(deleteHitItBroAlbum). Once the album is deleted, songs in that album are deleted as well. That makes sense.

def main(args: Array[String]): Unit = {
    exec(createTables) //create tables
    println("start")
    exec(populateFirstTime) //populate table with data
    exec(populateSecondTime)
    println("finish init")
    println("\nResult of foreign key join:")
    println(exec(join.result)) // join artist and song title

    exec(SongTable.result).foreach(println) //print out songs on SongTable
    exec(AlbumTable.result).foreach(println) //print out albums on AlbumTable

    val deleteHitItBroAlbum = for {
      mouseAlbumId <- AlbumTable.filter(_.title === "Hit it Bro").map(_.id).result.headOption
      rowsAffected <- SongTable.filter(_.albumId === mouseAlbumId).delete
    } yield rowsAffected

    exec(deleteHitItBroAlbum) //Delete a album// this action will delete songs associated with that album

    println("after delete")
    exec(SongTable.result).foreach(println)
    exec(AlbumTable.result).foreach(println)
  }

5. As One Whole file

Here, I added everything that was written before as one whole file. This file can be run provided sbt and database config are setup correctly. Good luck.

package ForeignKeyTest

import slick.driver.H2Driver.api._
import scala.concurrent.Await
import scala.concurrent.duration._
import scala.concurrent.ExecutionContext.Implicits.global

object Main {

  // Tables -------------------------------------
  case class Album(artist: String,
                   title: String,
                   id: Long = 0L)

  case class Song(name: String,
                  albumId: Long,
                  id: Long = 0L)
                  
  class AlbumTable(tag: Tag) extends Table[Album](tag, "albums") {
    def artist = column[String]("artist")
    def title = column[String]("title")
    def id = column[Long]("id", O.PrimaryKey, O.AutoInc)

    def * = (artist, title, id) <> (Album.tupled, Album.unapply)
  }

  class SongTable(tag: Tag) extends Table[Song](tag, "songs") {
    def name = column[String]("title")
    def albumId = column[Long]("albumId")
    def id = column[Long]("id", O.PrimaryKey, O.AutoInc)
    def * = (name, albumId, id) <> (Song.tupled, Song.unapply)

    def album = foreignKey("album_fk", albumId, AlbumTable)(_.id, onDelete = ForeignKeyAction.Cascade)
  }

  lazy val AlbumTable = TableQuery[AlbumTable]
  lazy val SongTable = TableQuery[SongTable]
  lazy val ddl = AlbumTable.schema ++ SongTable.schema
  lazy val insertAlbum = AlbumTable returning AlbumTable.map(_.id)

  // Actions ------------------------------------
  val createTables = (AlbumTable.schema ++ SongTable.schema).create

  val populateFirstTime =
    for {
      albumId <- insertAlbum += Album("Keyboard", "Hit it Bro")
      count <- SongTable ++= Seq(
        Song("Hit me one more time", albumId),
        Song("Press them all", albumId)
      )
    } yield count

  val populateSecondTime =
    for {
      albumId <- insertAlbum += Album("Mouse", "Click it Bro")
      count <- SongTable ++= Seq(
        Song("Jumping around", albumId),
        Song("Sneaking around", albumId)
      )
    } yield count

  val join = for {
    song <- SongTable
    album <- song.album
  } yield (album.artist, song.name)

  // Database -----------------------------------
  val db = Database.forConfig("scalaxdb")

  // Let's go! ----------------------------------
  def exec[T](action: DBIO[T]): T =
  Await.result(db.run(action), 2 seconds)

  def main(args: Array[String]): Unit = {

    exec(createTables) //create tables
    println("start")
    exec(populateFirstTime) //populate table with data
    exec(populateSecondTime)
    println("finish init")
    println("\nResult of foreign key join:")
    println(exec(join.result)) // join artist and song title

    exec(SongTable.result).foreach(println) //print out songs on SongTable
    exec(AlbumTable.result).foreach(println) //print out albums on AlbumTable

    val deleteHitItBroAlbum = for {
      mouseAlbumId <- AlbumTable.filter(_.title === "Hit it Bro").map(_.id).result.headOption
      rowsAffected <- SongTable.filter(_.albumId === mouseAlbumId).delete
    } yield rowsAffected

    exec(deleteHitItBroAlbum) //Delete a album// this action will delete songs associated with that album

    println("after delete")
    exec(SongTable.result).foreach(println)
    exec(AlbumTable.result).foreach(println)
  }
}