Database Programming: JDBC, Slick, and Doobie

Database programming is a fundamental aspect of most Scala applications. Scala offers several excellent libraries for database interaction, each with different approaches and trade-offs. In this lesson, we'll explore three main approaches: JDBC for direct SQL access, Slick for functional relational mapping, and Doobie for purely functional database programming.

Understanding Database Programming Approaches

JDBC (Java Database Connectivity)

• Direct SQL execution
• Low-level, imperative style
• Maximum flexibility and control
• Requires manual resource management

Slick (Scala Language-Integrated Connection Kit)

• Type-safe database queries
• Functional relational mapping (FRM)
• Scala collections-like API
• Compile-time query validation

Doobie

• Purely functional database programming
• Composable queries
• Effect-typed operations
• Excellent integration with Cats Effect

JDBC: Direct Database Access

JDBC provides the foundation for database programming in the JVM ecosystem. While low-level, it offers maximum control and flexibility.

Setting Up JDBC

// build.sbt
libraryDependencies ++= Seq(
  "com.h2database" % "h2" % "2.2.224",           // H2 database for testing
  "org.postgresql" % "postgresql" % "42.6.0",    // PostgreSQL driver
  "com.mysql" % "mysql-connector-j" % "8.1.0",   // MySQL driver
  "com.zaxxer" % "HikariCP" % "5.0.1"            // Connection pooling
)

Basic JDBC Operations

import java.sql.{Connection, DriverManager, PreparedStatement, ResultSet}
import scala.util.{Try, Using}

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

class UserRepository {
  private val url = "jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1"
  private val username = "sa"
  private val password = ""

  // Initialize database
  def initDatabase(): Unit = {
    Using(DriverManager.getConnection(url, username, password)) { conn =>
      val stmt = conn.createStatement()
      stmt.execute("""
        CREATE TABLE IF NOT EXISTS users (
          id BIGINT PRIMARY KEY AUTO_INCREMENT,
          name VARCHAR(255) NOT NULL,
          email VARCHAR(255) NOT NULL UNIQUE,
          age INT NOT NULL
        )
      """)
    }.get
  }

  def createUser(user: User): Try[Long] = {
    Using(DriverManager.getConnection(url, username, password)) { conn =>
      val sql = "INSERT INTO users (name, email, age) VALUES (?, ?, ?)"
      val stmt = conn.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS)
      stmt.setString(1, user.name)
      stmt.setString(2, user.email)
      stmt.setInt(3, user.age)

      stmt.executeUpdate()
      val keys = stmt.getGeneratedKeys
      if (keys.next()) keys.getLong(1)
      else throw new RuntimeException("Failed to get generated ID")
    }
  }

  def findUserById(id: Long): Try[Option[User]] = {
    Using(DriverManager.getConnection(url, username, password)) { conn =>
      val sql = "SELECT id, name, email, age FROM users WHERE id = ?"
      val stmt = conn.prepareStatement(sql)
      stmt.setLong(1, id)

      val rs = stmt.executeQuery()
      if (rs.next()) {
        Some(User(
          id = rs.getLong("id"),
          name = rs.getString("name"),
          email = rs.getString("email"),
          age = rs.getInt("age")
        ))
      } else None
    }
  }
}

Connection Pooling with HikariCP

import com.zaxxer.hikari.{HikariConfig, HikariDataSource}
import java.sql.Connection
import scala.util.Using

class PooledUserRepository {
  private val config = new HikariConfig()
  config.setJdbcUrl("jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1")
  config.setUsername("sa")
  config.setPassword("")
  config.setMaximumPoolSize(10)
  config.setMinimumIdle(2)
  config.setConnectionTimeout(30000)
  config.setIdleTimeout(600000)
  config.setMaxLifetime(1800000)

  private val dataSource = new HikariDataSource(config)

  def getConnection: Connection = dataSource.getConnection

  def createUser(user: User): Try[Long] = {
    Using(getConnection) { conn =>
      val sql = "INSERT INTO users (name, email, age) VALUES (?, ?, ?)"
      val stmt = conn.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS)
      stmt.setString(1, user.name)
      stmt.setString(2, user.email)
      stmt.setInt(3, user.age)

      stmt.executeUpdate()
      val keys = stmt.getGeneratedKeys
      if (keys.next()) keys.getLong(1)
      else throw new RuntimeException("Failed to get generated ID")
    }
  }

  def close(): Unit = dataSource.close()
}

Slick: Functional Relational Mapping

Slick provides a type-safe, functional approach to database programming with a Scala collections-like API.

Setting Up Slick

// build.sbt
libraryDependencies ++= Seq(
  "com.typesafe.slick" %% "slick" % "3.4.1",
  "com.typesafe.slick" %% "slick-hikaricp" % "3.4.1",
  "com.h2database" % "h2" % "2.2.224",
  "org.slf4j" % "slf4j-nop" % "1.7.36"
)

Basic Slick Setup

import slick.jdbc.H2Profile.api._
import slick.jdbc.meta.MTable
import scala.concurrent.{Future, Await}
import scala.concurrent.duration._
import scala.concurrent.ExecutionContext.Implicits.global

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

// Define the Users table
class Users(tag: Tag) extends Table[User](tag, "USERS") {
  def id = column[Long]("ID", O.PrimaryKey, O.AutoInc)
  def name = column[String]("NAME")
  def email = column[String]("EMAIL", O.Unique)
  def age = column[Int]("AGE")

  def * = (id, name, email, age) <> (User.tupled, User.unapply)
}

// Table query
val users = TableQuery[Users]

class SlickUserRepository {
  private val db = Database.forConfig("h2mem")

  def initDatabase(): Future[Unit] = {
    db.run(users.schema.createIfNotExists)
  }

  def createUser(name: String, email: String, age: Int): Future[Long] = {
    val insertQuery = users.map(u => (u.name, u.email, u.age)) returning users.map(_.id)
    db.run(insertQuery += (name, email, age))
  }

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

  def findUsersByAge(minAge: Int): Future[Seq[User]] = {
    db.run(users.filter(_.age >= minAge).sortBy(_.name).result)
  }

  def updateUser(user: User): Future[Int] = {
    db.run(users.filter(_.id === user.id).update(user))
  }

  def deleteUser(id: Long): Future[Int] = {
    db.run(users.filter(_.id === id).delete)
  }

  def close(): Unit = db.close()
}

Advanced Slick Queries

// Complex queries with joins
case class Post(id: Long, userId: Long, title: String, content: String)

class Posts(tag: Tag) extends Table[Post](tag, "POSTS") {
  def id = column[Long]("ID", O.PrimaryKey, O.AutoInc)
  def userId = column[Long]("USER_ID")
  def title = column[String]("TITLE")
  def content = column[String]("CONTENT")

  def * = (id, userId, title, content) <> (Post.tupled, Post.unapply)

  // Foreign key
  def user = foreignKey("USER_FK", userId, users)(_.id)
}

val posts = TableQuery[Posts]

class BlogRepository {
  private val db = Database.forURL(
    "jdbc:h2:mem:blogdb;DB_CLOSE_DELAY=-1",
    driver = "org.h2.Driver"
  )

  // Join query: get users with their posts
  def getUsersWithPosts(): Future[Seq[(User, Seq[Post])]] = {
    val query = for {
      user <- users
      post <- posts if post.userId === user.id
    } yield (user, post)

    db.run(query.result).map { results =>
      results.groupBy(_._1).map { case (user, userPosts) =>
        (user, userPosts.map(_._2))
      }.toSeq
    }
  }

  // Left join: get all users and their posts (including users without posts)
  def getAllUsersWithOptionalPosts(): Future[Seq[(User, Option[Post])]] = {
    val query = users.joinLeft(posts).on(_.id === _.userId)
    db.run(query.result)
  }

  // Aggregation query
  def getPostCountByUser(): Future[Seq[(String, Int)]] = {
    val query = users
      .join(posts).on(_.id === _.userId)
      .groupBy(_._1.name)
      .map { case (name, group) => (name, group.length) }

    db.run(query.result)
  }
}

Doobie: Purely Functional Database Programming

Doobie provides a purely functional approach to database programming with excellent composability and type safety.

Setting Up Doobie

// build.sbt
libraryDependencies ++= Seq(
  "org.tpolecat" %% "doobie-core" % "1.0.0-RC4",
  "org.tpolecat" %% "doobie-h2" % "1.0.0-RC4",
  "org.tpolecat" %% "doobie-hikari" % "1.0.0-RC4",
  "org.tpolecat" %% "doobie-scalatest" % "1.0.0-RC4" % Test,
  "org.typelevel" %% "cats-effect" % "3.5.2"
)

Basic Doobie Setup

import doobie._
import doobie.implicits._
import doobie.h2.H2Transactor
import cats.effect._
import cats.implicits._

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

class DoobieUserRepository[F[_]: Async] {

  // Create transactor
  val transactor: Resource[F, Transactor[F]] = 
    H2Transactor.newH2Transactor[F](
      "jdbc:h2:mem:doobiedb;DB_CLOSE_DELAY=-1",
      "sa", 
      "",
      ExecutionContexts.synchronous // Use appropriate EC in real apps
    )

  // SQL queries
  def initDatabase(): ConnectionIO[Unit] = {
    sql"""
      CREATE TABLE IF NOT EXISTS users (
        id BIGINT PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(255) NOT NULL,
        email VARCHAR(255) NOT NULL UNIQUE,
        age INT NOT NULL
      )
    """.update.run.void
  }

  def insertUser(name: String, email: String, age: Int): ConnectionIO[Long] = {
    sql"INSERT INTO users (name, email, age) VALUES ($name, $email, $age)"
      .update
      .withUniqueGeneratedKeys[Long]("id")
  }

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

  def findByEmail(email: String): ConnectionIO[Option[User]] = {
    sql"SELECT id, name, email, age FROM users WHERE email = $email"
      .query[User]
      .option
  }

  def findByAgeRange(minAge: Int, maxAge: Int): ConnectionIO[List[User]] = {
    sql"SELECT id, name, email, age FROM users WHERE age BETWEEN $minAge AND $maxAge ORDER BY name"
      .query[User]
      .to[List]
  }

  def updateUser(user: User): ConnectionIO[Int] = {
    sql"UPDATE users SET name = ${user.name}, email = ${user.email}, age = ${user.age} WHERE id = ${user.id}"
      .update
      .run
  }

  def deleteUser(id: Long): ConnectionIO[Int] = {
    sql"DELETE FROM users WHERE id = $id"
      .update
      .run
  }

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

  // Streaming large result sets
  def streamAllUsers(): fs2.Stream[ConnectionIO, User] = {
    sql"SELECT id, name, email, age FROM users"
      .query[User]
      .stream
  }
}

Advanced Doobie Features

Custom Data Types and Mappings

import java.time.LocalDateTime
import java.util.UUID

case class User(
  id: UUID,
  name: String, 
  email: String,
  createdAt: LocalDateTime,
  status: UserStatus
)

sealed trait UserStatus
case object Active extends UserStatus
case object Inactive extends UserStatus
case object Suspended extends UserStatus

// Custom Meta instance for UserStatus
implicit val userStatusMeta: Meta[UserStatus] = 
  Meta[String].imap {
    case "active" => Active
    case "inactive" => Inactive
    case "suspended" => Suspended
    case other => throw new RuntimeException(s"Unknown status: $other")
  } {
    case Active => "active"
    case Inactive => "inactive" 
    case Suspended => "suspended"
  }

class AdvancedDoobieRepository[F[_]: Async] {

  def createUser(name: String, email: String, status: UserStatus): ConnectionIO[UUID] = {
    val id = UUID.randomUUID()
    val now = LocalDateTime.now()

    sql"""
      INSERT INTO users (id, name, email, created_at, status) 
      VALUES ($id, $name, $email, $now, $status)
    """.update.run.as(id)
  }

  def findActiveUsers(): ConnectionIO[List[User]] = {
    sql"""
      SELECT id, name, email, created_at, status 
      FROM users 
      WHERE status = ${Active: UserStatus}
      ORDER BY created_at DESC
    """.query[User].to[List]
  }

  def updateUserStatus(id: UUID, status: UserStatus): ConnectionIO[Boolean] = {
    sql"UPDATE users SET status = $status WHERE id = $id"
      .update
      .run
      .map(_ > 0)
  }
}

Composable Queries and Fragments

object UserQueries {

  // Base query fragment
  private val baseSelect: Fragment = 
    sql"SELECT id, name, email, age FROM users"

  // Conditional fragments
  def byAge(age: Int): Fragment = sql"age = $age"
  def byAgeRange(min: Int, max: Int): Fragment = sql"age BETWEEN $min AND $max"
  def byNameLike(pattern: String): Fragment = sql"name LIKE $pattern"
  def byEmail(email: String): Fragment = sql"email = $email"

  // Composable query builder
  def findUsers(
    ageFilter: Option[Int] = None,
    ageRange: Option[(Int, Int)] = None,
    namePattern: Option[String] = None,
    emailFilter: Option[String] = None,
    limit: Option[Int] = None
  ): ConnectionIO[List[User]] = {

    val filters = List(
      ageFilter.map(byAge),
      ageRange.map { case (min, max) => byAgeRange(min, max) },
      namePattern.map(byNameLike),
      emailFilter.map(byEmail)
    ).flatten

    val whereClause = filters match {
      case Nil => Fragment.empty
      case head :: tail => tail.foldLeft(sql" WHERE " ++ head) { (acc, filter) =>
        acc ++ sql" AND " ++ filter
      }
    }

    val limitClause = limit.fold(Fragment.empty)(l => sql" LIMIT $l")
    val orderClause = sql" ORDER BY name"

    val query = baseSelect ++ whereClause ++ orderClause ++ limitClause

    query.query[User].to[List]
  }
}

Testing with Doobie

import doobie.scalatest._
import org.scalatest.flatspec.AnyFlatSpec
import org.scalatest.matchers.should.Matchers

class DoobieRepositorySpec extends AnyFlatSpec with Matchers with IOChecker {

  val transactor = H2Transactor.newH2Transactor[IO](
    "jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1",
    "sa",
    "",
    ExecutionContexts.synchronous
  ).allocated.unsafeRunSync()._1

  val repository = new DoobieUserRepository[IO]

  // Initialize test database
  repository.initDatabase().transact(transactor).unsafeRunSync()

  "UserRepository queries" should "be well-typed" in {
    check(repository.insertUser("test", "test@example.com", 25))
    check(repository.findById(1L))
    check(repository.findByEmail("test@example.com"))
    check(repository.findByAgeRange(20, 30))
    check(repository.updateUser(User(1L, "test", "test@example.com", 25)))
    check(repository.deleteUser(1L))
  }

  "insertUser" should "create a new user" in {
    val program = for {
      id <- repository.insertUser("John Doe", "john@example.com", 30)
      user <- repository.findById(id)
    } yield (id, user)

    val (id, user) = program.transact(transactor).unsafeRunSync()

    id should be > 0L
    user shouldBe defined
    user.get.name shouldBe "John Doe"
    user.get.email shouldBe "john@example.com"
    user.get.age shouldBe 30
  }
}

Database Migrations and Schema Management

Flyway Integration

// build.sbt
libraryDependencies += "org.flywaydb" % "flyway-core" % "9.22.3"

import org.flywaydb.core.Flyway

class DatabaseMigration {
  def migrate(jdbcUrl: String, username: String, password: String): Unit = {
    val flyway = Flyway.configure()
      .dataSource(jdbcUrl, username, password)
      .locations("classpath:db/migration")
      .load()

    flyway.migrate()
  }
}

// Migration files in src/main/resources/db/migration/
// V1__Create_users_table.sql
// V2__Add_posts_table.sql
// V3__Add_user_status_column.sql

Best Practices and Performance Tips

Connection Pooling

// Hikari configuration for production
val config = new HikariConfig()
config.setJdbcUrl("jdbc:postgresql://localhost:5432/mydb")
config.setUsername("user")
config.setPassword("password")
config.setMaximumPoolSize(20)
config.setMinimumIdle(5)
config.setConnectionTimeout(30000)
config.setIdleTimeout(600000)
config.setMaxLifetime(1800000)
config.setLeakDetectionThreshold(60000)

val dataSource = new HikariDataSource(config)

Prepared Statement Reuse

// Good: Use prepared statements
def findUsersByAge(age: Int): ConnectionIO[List[User]] = {
  sql"SELECT * FROM users WHERE age >= $age".query[User].to[List]
}

// Avoid: String concatenation (SQL injection risk)
def findUsersByAgeUnsafe(age: Int): ConnectionIO[List[User]] = {
  Fragment.const(s"SELECT * FROM users WHERE age >= $age").query[User].to[List]
}

Batch Operations

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

// Streaming for large datasets
def processLargeDataset(): fs2.Stream[ConnectionIO, Unit] = {
  sql"SELECT id, name, email, age FROM users"
    .query[User]
    .stream
    .chunkN(1000)
    .map(processChunk)
}

Error Handling

def findUserSafely(id: Long): ConnectionIO[Either[String, User]] = {
  findById(id)
    .map(_.toRight(s"User with id $id not found"))
    .attemptSql
    .map(_.leftMap(_.getMessage))
}

def transactionalOperation(): ConnectionIO[Unit] = {
  (for {
    userId <- insertUser("John", "john@example.com", 30)
    _ <- insertPost(userId, "My First Post", "Hello World!")
    _ <- updateUserLastLogin(userId)
  } yield ()).attemptSql.flatMap {
    case Right(result) => result.pure[ConnectionIO]
    case Left(error) => 
      // Log error and potentially retry
      MonadError[ConnectionIO, Throwable].raiseError(error)
  }
}

Conclusion

Database programming in Scala offers multiple approaches, each with its strengths:

• JDBC: Maximum control and flexibility, suitable for complex SQL and performance-critical applications
• Slick: Type-safe queries with a functional approach, excellent for domain-driven designs
• Doobie: Purely functional database programming with excellent composability and effect management

Choose the approach that best fits your project's needs:

• Use JDBC for maximum control and when working with existing SQL-heavy codebases
• Use Slick for type-safe, object-relational mapping with functional programming benefits
• Use Doobie for purely functional applications with Cats Effect and when composability is important

All three approaches can coexist in the same project, allowing you to use the best tool for each specific use case.