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. 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)
}
}