Scala for Backend Engineering & Functional Programming · Lesson

Database Integration with Slick/Doobie

Learn to integrate databases into your Play application using popular Scala ORMs/libraries like Slick or Doobie.

Database Integration with Slick/Doobie is a free Scala for Backend Engineering & Functional Programming lesson on CoddyKit — lesson 3 of 3. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the Scala for Backend Engineering & Functional Programming learning path, one of 3 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Why Integrate Databases?

Web applications often need to store and retrieve data persistently. This data could be user profiles, product information, or blog posts.

Persistence: Data remains available even after the application restarts.
Scalability: Handle large amounts of data and many concurrent users.
Reliability: Ensure data integrity and recoverability.

Integrating a database is crucial for any dynamic web application built with Play Framework.

Slick & Doobie: Scala's DB Tools

Scala offers powerful libraries for database interaction, with Slick and Doobie being two popular choices.

Slick: A Functional Relational Mapping (FRM) library. It allows you to work with databases in a type-safe, functional way, abstracting away SQL.
Doobie: A pure functional JDBC layer. It gives you direct control over SQL queries while integrating with functional programming paradigms like Cats Effect.

For this lesson, we'll focus on Slick due to its higher-level abstraction, which often simplifies initial integration with Play Framework.

Setting Up Slick in Play

First, add Slick and a database driver to your build.sbt. We'll use H2, an in-memory database, for easy setup.

Then, configure your database connection in conf/application.conf.

import play.sbt.PlayImport._

libraryDependencies ++= Seq(
  jdbc,
  ws,
  "com.typesafe.play" %% "play-slick" % "5.0.0",
  "com.typesafe.play" %% "play-slick-evolutions" % "5.0.0",
  "com.h2database" % "h2" % "2.2.224"
)

Database Configuration

Add these lines to your conf/application.conf to set up the H2 database and Slick profile. The slick.dbs.default.profile specifies the database type.

play.db.prototype.slick defines a template for database connections.

slick.dbs.default.profile="slick.jdbc.H2Profile$"

play.db { 
  prototype.slick = {
    db.driver="org.h2.Driver"
    db.url="jdbc:h2:mem:play;DB_CLOSE_DELAY=-1"
    db.username="sa"
    db.password=""
  }

  # Apply the prototype to our default DB
  default = ${play.db.prototype.slick}
}

Defining Your Model (Slick Table)

In Slick, you represent database tables as Scala classes that extend slick.jdbc.H2Profile.api.Table. Each column is mapped to a Scala type.

We define a TableQuery for each table, which is the entry point for querying.

import slick.jdbc.H2Profile.api._

// Define a case class for our data model
case class User(id: Option[Long], name: String, email: String)

// Define the table mapping
class Users(tag: Tag) extends Table[User](tag, "users") {
  def id = column[Option[Long]]("id", O.PrimaryKey, O.AutoInc)
  def name = column[String]("name")
  def email = column[String]("email")
  
  // Every table needs a * projection with the default mapping
  def * = (id, name, email) <> (User.tupled, User.unapply)
}

// TableQuery for accessing the Users table
val users = TableQuery[Users]

Code: Table & Schema Creation

This runnable example shows how to define a User table and then create its schema in an in-memory H2 database. The db.run method executes database actions asynchronously.

import slick.jdbc.H2Profile.api._
import scala.concurrent.ExecutionContext.Implicits.global
import scala.concurrent.Await
import scala.concurrent.duration._

case class User(id: Option[Long], name: String, email: String)

class Users(tag: Tag) extends Table[User](tag, "users") {
  def id = column[Option[Long]]("id", O.PrimaryKey, O.AutoInc)
  def name = column[String]("name")
  def email = column[String]("email")
  def * = (id, name, email) <> (User.tupled, User.unapply)
}

object SlickDemo {
  val users = TableQuery[Users]

  def main(args: Array[String]): Unit = {
    val db = Database.forURL("jdbc:h2:mem:test1;DB_CLOSE_DELAY=-1", driver = "org.h2.Driver")
    try {
      val setup = DBIO.seq(
        users.schema.create
      )
      val future = db.run(setup)
      Await.result(future, 2.seconds)
      println("Users table created successfully!")
    } finally {
      db.close()
    }
  }
}

DAO Pattern for Operations

The Data Access Object (DAO) pattern helps encapsulate database operations. It separates your application's business logic from its persistence logic.

A UserDAO would contain methods for inserting, querying, updating, and deleting User objects.

import slick.jdbc.H2Profile.api._
import scala.concurrent.Future

class UserDAO(db: Database) {
  import SlickDemo.users // Assuming users TableQuery is in SlickDemo object

  def insert(user: User): Future[User] = {
    db.run(users returning users.map(_.id) into ((user, id) => user.copy(id = id)) += user)
  }

  def findAll(): Future[Seq[User]] = {
    db.run(users.result)
  }

  def findById(id: Long): Future[Option[User]] = {
    db.run(users.filter(_.id === id).result.headOption)
  }
}

Code: Inserting & Querying Data

Here's how to insert a new user and then query all users from the database using the DAO methods we just defined.

import slick.jdbc.H2Profile.api._
import scala.concurrent.ExecutionContext.Implicits.global
import scala.concurrent.Await
import scala.concurrent.duration._

case class User(id: Option[Long], name: String, email: String)

class Users(tag: Tag) extends Table[User](tag, "users") {
  def id = column[Option[Long]]("id", O.PrimaryKey, O.AutoInc)
  def name = column[String]("name")
  def email = column[String]("email")
  def * = (id, name, email) <> (User.tupled, User.unapply)
}

class UserDAO(db: Database) {
  val users = TableQuery[Users]
  def insert(user: User): Future[User] = 
    db.run(users returning users.map(_.id) into ((u, id) => u.copy(id = id)) += user)
  def findAll(): Future[Seq[User]] = 
    db.run(users.result)
}

object SlickOperations {
  def main(args: Array[String]): Unit = {
    val db = Database.forURL("jdbc:h2:mem:test2;DB_CLOSE_DELAY=-1", driver = "org.h2.Driver")
    val userDAO = new UserDAO(db)
    try {
      Await.result(db.run(userDAO.users.schema.create), 2.seconds)
      println("Table created.")

      val future = for {
        user1 <- userDAO.insert(User(None, "Alice", "alice@example.com"))
        user2 <- userDAO.insert(User(None, "Bob", "bob@example.com"))
        allUsers <- userDAO.findAll()
      } yield allUsers

      val result = Await.result(future, 5.seconds)
      println(s"All users: $result")

    } finally {
      db.close()
    }
  }
}

Integrating DAO into Play

In a Play application, you typically inject your UserDAO into a controller using dependency injection. This allows your controller actions to interact with the database.

You'd configure Play's DI (e.g., Guice) to provide an instance of Database and UserDAO.

import play.api.mvc._
import play.api.libs.json._
import scala.concurrent.ExecutionContext
import javax.inject._

// Assuming User and UserDAO are defined as before

@Singleton
class UserController @Inject()(cc: ControllerComponents, userDAO: UserDAO)
                              (implicit ec: ExecutionContext) extends AbstractController(cc) {

  def listUsers = Action.async {
    userDAO.findAll().map { users =>
      Ok(Json.toJson(users))
    }
  }

  def createUser = Action.async(parse.json) {
    request =>
      request.body.validate[User].map { user =>
        userDAO.insert(user).map(u => Created(Json.toJson(u)))
      }.getOrElse(Future.successful(BadRequest("Invalid User Data")))
  }
}

Quick Check: Slick Table

Consider the following Scala case class and a partially defined Slick Table mapping:

case class Product(id: Option[Int], name: String, price: Double)

Which line correctly completes the * projection for the Product table, assuming id, name, and price columns are defined?

Recap: Database Integration

In this lesson, we explored how to integrate databases into your Play Framework applications using Slick.

We learned about Slick's role as a Functional Relational Mapping (FRM) library.
We covered setting up Slick dependencies and configuring the database in application.conf.
You saw how to define Scala case classes and map them to database tables using Slick's Table class.
We introduced the DAO pattern to organize database operations and provided examples of inserting and querying data.
Finally, we touched upon integrating these DAO operations into Play controllers for web API endpoints.

Mastering database integration is key to building robust and data-driven Play applications!

Frequently asked questions

Is the “Database Integration with Slick/Doobie” lesson free?

Yes — the full text of “Database Integration with Slick/Doobie” is free to read here on the web, and the Scala for Backend Engineering & Functional Programming course includes 3 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the Scala for Backend Engineering & Functional Programming course, upgrade to CoddyKit PRO.

What will I learn in “Database Integration with Slick/Doobie”?

Learn to integrate databases into your Play application using popular Scala ORMs/libraries like Slick or Doobie. You practise Scala for Backend Engineering & Functional Programming with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start Scala for Backend Engineering & Functional Programming?

No prior experience is required. Scala for Backend Engineering & Functional Programming on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 3, so you can start here or from the beginning and move at your own pace.

How long does the “Database Integration with Slick/Doobie” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this Scala for Backend Engineering & Functional Programming lesson?

Yes. Every Scala for Backend Engineering & Functional Programming lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

Play Framework Fundamentals
Building RESTful APIs with Play
Database Integration with Slick/Doobie

← Back to Scala for Backend Engineering & Functional Programming