Templates for Slick Domain Classes mapping to DB Tables

Templates for Slick Domain Classes mapping to DB Tables

Last updated:

These are some recurring patterns that may arise when creating tables and classes for use with Slick 2.x.

Adding some of these to your IDE's templates (code snippets) could save you a lot of work.

Simplest possible DB Table

import play.api.db.slick.Config.driver.simple._
import play.api.Play.current
import scala.slick.lifted.Tag

class Users(tag: Tag) extends Table[(Long,String,String)](tag, "users") {
  //table attributes
  def id = column[Long]("id", O.PrimaryKey, O.AutoInc)
  def username = column[String]("username", O.NotNull)
  def password = column[String]("password", O.NotNull)

  //simple unique index
  def idx = index("unique_username", (username), unique = true)

  //define the "shape" of a single data record
  //what should be returned when we query
  def * = (id, username)
}

DB Table with case class

You can use case classes instead of tuples, so you get a nice object (of the case class) when you query this table. You can add methods to this class, easily use if for pattern matching, and so on.

import java.sql.Timestamp
import play.api.db.slick.Config.driver.simple._
import play.api.Play.current
import scala.slick.lifted.Tag

//The id has to be an Option type so you can create instances that do  
// not have an ID yet, before you insert them into the database.
case class User(
  id: Option[Long],
  username: String,
  email: Option[String],
  password: String,
  createdAt: Timestamp)

//this is a class that represents the actual database table
//it is based upon case class User, defined above
class Users(tag: Tag) extends Table[User](tag, "users") {

  def id = column[Long]("id", O.PrimaryKey, O.AutoInc)
  def username = column[String]("username", O.NotNull)
  def email = column[String]("email", O.Nullable)
  def password = column[String]("password", O.NotNull)
  def createdAt = column[Timestamp]("createdAt", O.NotNull)

  def idx = index("unique_username", (username), unique = true)

  //define the "shape" of a single data record
  //we're saying that an object of case class User should be returned
  def * = (id.?, username, email.?, password, createdAt) <> (User.tupled, User.unapply)

}

A DB Table, a case class and a companion object to help with queries

import java.sql.Timestamp
import play.api.db.slick.Config.driver.simple._
import play.api.Play.current
import scala.slick.lifted.Tag

case class User(
  id: Option[Long],
  username: String,
  email: Option[String],
  password: String,
  createdAt: Timestamp)

//this is a class that represents the actual database table
//it is based upon case class User, defined above
class Users(tag: Tag) extends Table[User](tag, "users") {

  def id = column[Long]("id", O.PrimaryKey, O.AutoInc)
  def username = column[String]("username", O.NotNull)
  def email = column[String]("email", O.Nullable)
  def password = column[String]("password", O.NotNull)
  def createdAt = column[Timestamp]("createdAt", O.NotNull)

  def idx = index("unique_username", (username), unique = true)

  //define the "shape" of a single data record
  //we're saying that an object of case class User should be returned
  def * = (id.?, name, description.?, createdAt) <> ((User.apply _).tupled, User.unapply)

}

//finder methods and things like that
object User {
  val models = TableQuery[Users]

  // this method needs an implicit DBSession to be in scope when they are called
  //the user with the given id may not exist, so we must return an Option[User]
  def findById(id: Long)(implicit s: Session): Option[User] =
    models.filter(_.id === id).list.headOption

}

Relationship table for many-to-many relationships

When you have a N-to-N relationship between two of your tables, you need a relationship table to manage this.

An example of such a situation is the connection between Students and Teachers. In a normal school or college, a Teacher can have many Students and a Student can, in turn, have many Teachers.

import play.api.db.slick.Config.driver.simple._
import play.api.Play.current
import scala.slick.lifted.Tag
//need to import the related classes
import models.Teachers
import models.Students

class TeachersXStudents(tag: Tag) extends Table[(Long, Long)](tag, "teachers_x_students") {
  //table attributes
  def teacherId = column[Long]("teacher_id")
  def studentId = column[Long]("student_id")

  //composite primary key
  def pk = primaryKey("pk_teachers_x_students", (teacherId, studentId))

  def fk1 = foreignKey("teacher_fk", teacherId, TableQuery[Teachers])(_.id, onUpdate = ForeignKeyAction.Cascade, onDelete = ForeignKeyAction.Cascade)
  def fk2 = foreignKey("student_fk", studentId, TableQuery[Students])(_.id, onUpdate = ForeignKeyAction.Cascade, onDelete = ForeignKeyAction.Cascade)

  //define the "shape" of a single data record
  //what should be returned when we query this table using slick
  def * = (teacherId, studentId)
}

slick

Archive