Play with Reactive Slick: A Simple CRUD application in Play! framework using Slick 3.0


Recently Typesafe released Slick 3.0.0-RC1, the first release candidate for “Reactive Slick”. Reactive Slick has many new features like Support for Reactive Streams API, a new API for composing and executing database actions and many more.

Before moving forward, we recommended you to go through the Slick 3.0.0-RC1 Upgrade guide. So, that you can get a hint of major changes in Slick 3.0.

In this post, we will see how to build a Reactive Play application with Reactive Slick. In order to build a simple CRUD application in Play framework using Reactive Slick (Slick-3.0.0-RC1) we need to follow these steps:

1) Run activator new play-reactive-slick play-scala to create a new Play application.

2) Add following dependencies in build.sbt file

"com.typesafe.slick" %% "slick" % "3.0.0-RC1"
"org.slf4j" % "slf4j-nop" % "1.6.4"
"postgresql" % "postgresql" % "9.1-901.jdbc4"

Here we are using PostgreSQL as database. So, if you have not installed it yet, you can download it from here.

3) Next, we need to add bidirectional mapping of our case class with the table, like this:

case class Employee(id: Option[Long], name: String, address: String, dob: Option[Date], joiningDate: Date, designation: Option[String])

class Employees(tag: Tag) extends Table[Employee](tag, "EMPLOYEE") {

 implicit val dateColumnType = MappedColumnType.base[Date, Long](d => d.getTime, d => new Date(d))

 def id = column[Long]("id", O.PrimaryKey, O.AutoInc)
 def name = column[String]("name")
 def address = column[String]("address")
 def dob = column[Date]("date_of_birth")
 def joiningDate = column[Date]("joining_date")
 def designation = column[String]("designation")

 def * = (id.?, name, address, dob.?, joiningDate, designation.?) <> (Employee.tupled, Employee.unapply _)

Important thing to note in above mapping is that we have omitted O.Nullable/O.NotNull. The reason behind it is that both O.Nullable/O.NotNull are deprecated in Slick-3.0.0-RC1. Instead, we have provided the mapping for optional fields in projection itself.

4) Then, we can write a Database Access Object (DAO) to perform CRUD operations:

object EmployeeDAO {

  val employees = TableQuery[Employees]

  def db: Database = Database.forDataSource(DB.getDataSource())

  def filterQuery(id: Long): Query[Employees, Employee, Seq] =
    employees.filter(_.id === id)

  def findById(id: Long): Future[Employee] =
    try db.run(filterQuery(id).result.head)
    finally db.close

  def insert(employee: Employee): Future[Int] =
    try db.run(employees += employee)
    finally db.close

  def update(id: Long, employee: Employee): Future[Int] =
    try db.run(filterQuery(id).update(employee))
    finally db.close

  def delete(id: Long): Future[Int] =
    try db.run(filterQuery(id).delete)
    finally db.close
}

As we can see in above code that now Slick returns result in Future. Also, now the database instance db have an associated connection pool and thread pool, so, it is important to call db.close when we are done using it.

5) At last, we need to provide Asynchronous Action response in controller. Example:

def delete(id: Long): Action[AnyContent] = Action.async { implicit request =>
 val futureEmpDel = EmployeeDAO.delete(id)
 futureEmpDel.map { result => Home.flashing("success" -> "Employee has been deleted") }.recover {
   case ex: TimeoutException =>
     Logger.error("Problem found in employee delete process")
     InternalServerError(ex.getMessage)
   }
 }

Here we are using Action.async to handle Future response from database.

In this way we can build a simple CRUD application in Play framework using Reactive Slick. For more information on Reactive Slick click here.

To download a demo Application click here.

This entry was posted in Agile, Future, Non-Blocking, Play Framework, Reactive, Scala, Slick, Web and tagged , , , , . Bookmark the permalink.

18 Responses to Play with Reactive Slick: A Simple CRUD application in Play! framework using Slick 3.0

  1. Reblogged this on Play!ng with Scala and commented:

    Recently Typesafe released Slick 3.0.0-RC1

  2. Pingback: Play with Reactive Slick: A Simple CRUD application in Play! framework using Slick 3.0 | Rishi Khandelwal

  3. Agdra says:

    Thank you 😀

  4. guillegr123 says:

    If you are using JDBC, I guess that you are not using the reactive behavior provided by this reactive slick version.

  5. Niklas Klein says:

    Thank you for the article, looks really interesting. I took a look at the source to recreate the project and was wondering where your evolutions are coming from. They appear to be auto generated but that is not happening for me, even though I specified the slick.default property in the config file. Is there a way to trigger the generation manually or how is it working for you?

  6. tldeti says:

    /** Free all resources allocated by Slick for this Database object. In particular, the
    * [[slick.util.AsyncExecutor]] with the thread pool for asynchronous execution is shut
    * down. If this object represents a connection pool managed directly by Slick, it is also
    * closed. */
    def close: Unit = try executor.close() finally source.close()

  7. tldeti says:

    I vote down this post because some newbie I know wrote “finally { db.close()} ” in model class after reading this.

  8. Vu Ngoc Hung says:

    I tried to change build.sbt, application.conf and then created correspondent table (Employee) on MySQL database. But I had the error message: “[MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘”date_of_birth” as x6, x9.”address” as x5, x9.”designation” as x8, x9.”id” as x3’ at line 1]”

    I tried to debug but couldn’t find the cause.
    Could you have a help?
    Thanks so much.

  9. Taig says:

    In your DAO object, when you call db.run(), db.close() you are calling the method db. This might give you an other database object which flaws the run/close flow, doesn’t it?

  10. Teolha says:

    I wonder why you don’t get an exception like: “[PSQLException: Bad value for type long : 2015-04-06 22:36:17]”

  11. Ivan says:

    Do you have any advice concerning mapped vs. non-mapped tables? Your are using mapped table, but
    tuple seems to be the “default” way since it appears in almost all the examples in the documentation and mapped tables only in a dedicated section. Where is the advantage of using tuples, the access is index based and not really type safe. Maybe I’m missing something.

  12. Documentation for Play asks you NOT to explicitly perform `db.close`:

    https://www.playframework.com/documentation/2.4.0/PlaySlick#Usage

  13. The documentation for Play explicitly says NOT to perform `db.close` in your DAO.

    https://www.playframework.com/documentation/2.4.0/PlaySlick#Usage

  14. Pingback: Close DB Connection in Slick 3.0 | news

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s