Eric Campbell 5 min read Scala

Scala - Database Access (Slick/Doobie)

Slick (Scala Language-Integrated Connection Kit) treats database queries as Scala collections, providing compile-time verification of queries against your schema.

Key Insights

  • Slick provides type-safe database queries using Scala’s type system and functional composition, while Doobie offers a pure functional approach with explicit effect management through cats-effect
  • Slick’s code generation from existing schemas eliminates boilerplate and ensures compile-time safety, but Doobie’s plain SQL approach gives more control and transparency for complex queries
  • Connection pooling, transaction management, and streaming results differ significantly between libraries—Slick uses DBIO actions while Doobie leverages fs2 streams for backpressure-aware data processing

Slick: Type-Safe Database Queries

Slick (Scala Language-Integrated Connection Kit) treats database queries as Scala collections, providing compile-time verification of queries against your schema.

// build.sbt
libraryDependencies ++= Seq(
  "com.typesafe.slick" %% "slick" % "3.5.0",
  "com.typesafe.slick" %% "slick-hikaricp" % "3.5.0",
  "org.postgresql" % "postgresql" % "42.6.0"
)

Define your schema using Slick’s table definitions:

import slick.jdbc.PostgresProfile.api._

case class User(id: Long, email: String, name: String, age: Int)

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

val users = TableQuery[Users]

Execute queries with type-safe composition:

import scala.concurrent.{Await, Future}
import scala.concurrent.duration._
import scala.concurrent.ExecutionContext.Implicits.global

val db = Database.forConfig("mydb")

// Insert
val insertAction = users += User(0L, "john@example.com", "John Doe", 30)
val insertFuture: Future[Int] = db.run(insertAction)

// Query with filters
val adultUsersQuery = users.filter(_.age >= 18).result
val adultUsers: Future[Seq[User]] = db.run(adultUsersQuery)

// Complex query with joins
case class Order(id: Long, userId: Long, total: BigDecimal)

class Orders(tag: Tag) extends Table[Order](tag, "orders") {
  def id = column[Long]("id", O.PrimaryKey, O.AutoInc)
  def userId = column[Long]("user_id")
  def total = column[BigDecimal]("total")
  
  def * = (id, userId, total) <> (Order.tupled, Order.unapply)
  def user = foreignKey("user_fk", userId, users)(_.id)
}

val orders = TableQuery[Orders]

val userOrdersQuery = for {
  (u, o) <- users join orders on (_.id === _.userId)
  if u.age >= 18
} yield (u.name, o.total)

val results: Future[Seq[(String, BigDecimal)]] = db.run(userOrdersQuery.result)

Slick Transaction Management

Slick uses DBIO actions that can be combined and executed transactionally:

def transferFunds(fromUser: Long, toUser: Long, amount: BigDecimal): DBIO[Boolean] = {
  val debitAction = sqlu"""
    UPDATE accounts SET balance = balance - $amount 
    WHERE user_id = $fromUser AND balance >= $amount
  """
  
  val creditAction = sqlu"""
    UPDATE accounts SET balance = balance + $amount 
    WHERE user_id = $toUser
  """
  
  (for {
    debitRows <- debitAction
    _ <- if (debitRows > 0) creditAction else DBIO.failed(new Exception("Insufficient funds"))
  } yield true).transactionally
}

// Execute transaction
val transferResult: Future[Boolean] = db.run(transferFunds(1L, 2L, 100.00))

Streaming results for large datasets:

import akka.stream.scaladsl._

val source: Source[User, NotUsed] = 
  Source.fromPublisher(db.stream(users.result))

source
  .filter(_.age >= 18)
  .map(user => s"${user.name}: ${user.email}")
  .runForeach(println)

Doobie: Pure Functional Database Access

Doobie provides a purely functional approach using cats-effect for effect management:

// build.sbt
libraryDependencies ++= Seq(
  "org.tpolecat" %% "doobie-core" % "1.0.0-RC4",
  "org.tpolecat" %% "doobie-postgres" % "1.0.0-RC4",
  "org.tpolecat" %% "doobie-hikari" % "1.0.0-RC4"
)

Basic setup and queries:

import doobie._
import doobie.implicits._
import cats.effect._
import cats.effect.unsafe.implicits.global

case class User(id: Long, email: String, name: String, age: Int)

val xa = Transactor.fromDriverManager[IO](
  driver = "org.postgresql.Driver",
  url = "jdbc:postgresql://localhost:5432/mydb",
  user = "postgres",
  password = "password",
  logHandler = None
)

// Simple query
def findUserById(id: Long): ConnectionIO[Option[User]] =
  sql"SELECT id, email, name, age FROM users WHERE id = $id"
    .query[User]
    .option

// Execute
val user: Option[User] = findUserById(1L).transact(xa).unsafeRunSync()

// Insert with returning
def insertUser(email: String, name: String, age: Int): ConnectionIO[User] =
  sql"""
    INSERT INTO users (email, name, age) 
    VALUES ($email, $name, $age)
  """.update
    .withUniqueGeneratedKeys[User]("id", "email", "name", "age")

val newUser: User = insertUser("jane@example.com", "Jane Smith", 28)
  .transact(xa)
  .unsafeRunSync()

Doobie Composable Queries

Build queries from fragments for dynamic SQL:

import doobie.implicits._
import doobie.util.fragments._

def findUsers(minAge: Option[Int], namePattern: Option[String]): ConnectionIO[List[User]] = {
  val baseQuery = fr"SELECT id, email, name, age FROM users"
  
  val ageFilter = minAge.map(age => fr"age >= $age")
  val nameFilter = namePattern.map(pattern => fr"name ILIKE $pattern")
  
  val filters = List(ageFilter, nameFilter).flatten
  
  val whereClause = filters match {
    case Nil => Fragment.empty
    case fs => fs.foldSmash(fr"WHERE", fr"AND", Fragment.empty)
  }
  
  (baseQuery ++ whereClause).query[User].to[List]
}

// Usage
val adults = findUsers(Some(18), None).transact(xa).unsafeRunSync()
val johns = findUsers(None, Some("%John%")).transact(xa).unsafeRunSync()

Batch operations for efficiency:

def insertUsers(users: List[(String, String, Int)]): ConnectionIO[Int] = {
  val sql = "INSERT INTO users (email, name, age) VALUES (?, ?, ?)"
  Update[(String, String, Int)](sql).updateMany(users)
}

val usersToInsert = List(
  ("user1@example.com", "User One", 25),
  ("user2@example.com", "User Two", 30),
  ("user3@example.com", "User Three", 35)
)

val inserted: Int = insertUsers(usersToInsert).transact(xa).unsafeRunSync()

Streaming with Doobie

Doobie integrates with fs2 for streaming large result sets:

import fs2.Stream

def streamAllUsers: Stream[ConnectionIO, User] =
  sql"SELECT id, email, name, age FROM users"
    .query[User]
    .stream

// Process stream with backpressure
def processUsers: IO[Unit] = 
  streamAllUsers
    .chunkN(100) // Process in chunks
    .evalMap { chunk =>
      IO.println(s"Processing batch of ${chunk.size} users") *>
      IO(chunk.toList.foreach(user => println(s"${user.name}: ${user.email}")))
    }
    .compile
    .drain
    .transact(xa)

processUsers.unsafeRunSync()

Transaction Management in Doobie

Explicit transaction control with error handling:

def transferWithValidation(
  fromUserId: Long, 
  toUserId: Long, 
  amount: BigDecimal
): ConnectionIO[Either[String, Unit]] = {
  for {
    fromBalance <- sql"SELECT balance FROM accounts WHERE user_id = $fromUserId"
      .query[BigDecimal]
      .unique
    result <- if (fromBalance >= amount) {
      for {
        _ <- sql"UPDATE accounts SET balance = balance - $amount WHERE user_id = $fromUserId".update.run
        _ <- sql"UPDATE accounts SET balance = balance + $amount WHERE user_id = $toUserId".update.run
      } yield Right(())
    } else {
      FC.pure(Left(s"Insufficient funds: $fromBalance"))
    }
  } yield result
}

// Execute with error handling
val transferResult: Either[String, Unit] = 
  transferWithValidation(1L, 2L, 50.00)
    .transact(xa)
    .unsafeRunSync()

Connection Pooling

Both libraries support HikariCP for production-grade connection pooling:

// Slick
import slick.jdbc.PostgresProfile.api._
import com.typesafe.config.ConfigFactory

val config = ConfigFactory.parseString("""
  mydb {
    dataSourceClass = "org.postgresql.ds.PGSimpleDataSource"
    properties = {
      serverName = "localhost"
      portNumber = "5432"
      databaseName = "mydb"
      user = "postgres"
      password = "password"
    }
    numThreads = 10
    maxConnections = 10
  }
""")

val db = Database.forConfig("mydb", config)

// Doobie with HikariCP
import doobie.hikari._

val transactor: Resource[IO, HikariTransactor[IO]] = for {
  xa <- HikariTransactor.newHikariTransactor[IO](
    driverClassName = "org.postgresql.Driver",
    url = "jdbc:postgresql://localhost:5432/mydb",
    user = "postgres",
    pass = "password",
    executionContext = scala.concurrent.ExecutionContext.global
  )
} yield xa

transactor.use { xa =>
  findUserById(1L).transact(xa)
}.unsafeRunSync()

Choosing Between Slick and Doobie

Slick excels when you need type-safe query composition and code generation from existing schemas. Its table definitions provide IDE support and refactoring safety. Use Slick for applications where database schema changes frequently or when working with teams that prefer Scala-native syntax over SQL.

Doobie shines in purely functional codebases using cats-effect. Its plain SQL approach makes it easier to optimize complex queries and understand exactly what’s being executed. Choose Doobie when you need explicit effect management, streaming capabilities with fs2, or when working with legacy databases where custom SQL is unavoidable.

Both libraries handle connection pooling, transactions, and production workloads effectively. The choice depends on your team’s functional programming maturity and whether you prefer type-safe DSLs or direct SQL control.

Liked this? There's more.

Every week: one practical technique, explained simply, with code you can use immediately.