Library

Slick

Overview

Slick (Scala Language-Integrated Connection Kit) is "a functional relational mapping (FRM) library for Scala" that provides a type-safe, functional programming paradigm-based database access layer. Unlike traditional ORMs, it allows writing SQL-like queries directly within Scala code and ensures query correctness through compile-time type checking. With Reactive Streams support, asynchronous processing, and query construction through function composition, it achieves high performance and maintainability in modern Scala application development.

Details

Slick 2025 edition fully supports Scala 3 and Scala 2.13, embodying best practices for functional database access. It handles enterprise-level requirements through SQL DDL generation, schema migration, and support for multiple database engines (PostgreSQL, MySQL, SQLite, H2, SQL Server, etc.). Optimized for reactive application development with Future-based asynchronous APIs, Akka Streams integration, and support for Effect types (cats-effect, ZIO, etc.). Mixed usage with Plain SQL is also possible, providing flexibility for complex query requirements.

Key Features

• Type-Safe Queries: Compile-time query validation leveraging Scala's type system
• Functional Approach: Query construction and reuse through function composition
• Multi-DB Support: Support for major RDBMS systems
• Reactive Streams: Asynchronous streaming query support
• DDL Generation: Automatic DDL generation from schema definitions
• Plain SQL Integration: Raw SQL execution capability for complex requirements

Pros and Cons

Pros

• Powerful type safety maximizing Scala's type system capabilities
• Natural integration with functional programming paradigms
• High performance through asynchronous processing and Reactive Streams
• Quality improvement through compile-time query error detection
• Unified access layer for multiple databases
• Strong affinity with Scala ecosystem (Akka, Play Framework, etc.)

Cons

• Learning curve specific to Scala and functional programming mastery required
• Lack of ORM-like features (automatic relationship resolution, etc.)
• Complex queries may be easier to write in raw SQL
• Lower accessibility for Java developers
• Limited documentation and resources in Japanese
• May be overkill for small-scale projects

Reference Pages

• Slick Official Documentation
• Slick GitHub Repository
• Slick API Documentation

Code Examples

Setup

// build.sbt
val slickVersion = "3.5.0"

libraryDependencies ++= Seq(
  "com.typesafe.slick" %% "slick" % slickVersion,
  "com.typesafe.slick" %% "slick-hikaricp" % slickVersion,
  "com.typesafe.slick" %% "slick-codegen" % slickVersion,
  "org.postgresql" % "postgresql" % "42.7.1",
  "com.h2database" % "h2" % "2.2.224",
  "org.slf4j" % "slf4j-simple" % "2.0.9"
)

// Database configuration
import slick.jdbc.PostgresProfile.api._
import slick.basic.DatabaseConfig
import slick.jdbc.JdbcProfile
import scala.concurrent.ExecutionContext.Implicits.global

// Database configuration
val dbConfig = DatabaseConfig.forConfig[JdbcProfile]("mydb")
val db = dbConfig.db

// application.conf
/*
mydb {
  profile = "slick.jdbc.PostgresProfile$"
  db {
    driver = "org.postgresql.Driver"
    url = "jdbc:postgresql://localhost/mydb"
    user = "postgres"
    password = "password"
    numThreads = 10
    maxConnections = 10
  }
}
*/

Basic Usage

import slick.jdbc.PostgresProfile.api._
import scala.concurrent.Future
import java.time.LocalDateTime

// Table definition
case class User(id: Option[Long], name: String, email: String, age: Int, createdAt: LocalDateTime)

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")
  def age = column[Int]("age")
  def createdAt = column[LocalDateTime]("created_at")
  
  // Unique constraint
  def emailIdx = index("idx_email", email, unique = true)
  
  // Projection (mapping between table and case class)
  def * = (id.?, name, email, age, createdAt).mapTo[User]
}

// Table query
val users = TableQuery[Users]

// Posts table
case class Post(id: Option[Long], title: String, content: String, userId: Long, published: Boolean, createdAt: LocalDateTime)

class Posts(tag: Tag) extends Table[Post](tag, "posts") {
  def id = column[Long]("id", O.PrimaryKey, O.AutoInc)
  def title = column[String]("title")
  def content = column[String]("content", O.SqlType("TEXT"))
  def userId = column[Long]("user_id")
  def published = column[Boolean]("published")
  def createdAt = column[LocalDateTime]("created_at")
  
  // Foreign key constraint
  def user = foreignKey("fk_posts_user", userId, users)(_.id.get)
  
  def * = (id.?, title, content, userId, published, createdAt).mapTo[Post]
}

val posts = TableQuery[Posts]

// Schema creation
val schema = users.schema ++ posts.schema

// DDL generation and schema creation
val setupAction = DBIO.seq(
  schema.createIfNotExists,
  
  // Initial data insertion
  users += User(None, "Alice", "[email protected]", 25, LocalDateTime.now()),
  users += User(None, "Bob", "[email protected]", 30, LocalDateTime.now()),
  users += User(None, "Charlie", "[email protected]", 35, LocalDateTime.now())
)

val setupFuture: Future[Unit] = db.run(setupAction)

Query Execution

import scala.concurrent.{Future, Await}
import scala.concurrent.duration._
import scala.util.{Success, Failure}

object UserService {
  
  // Get all users
  def getAllUsers: Future[Seq[User]] = {
    val query = users.result
    db.run(query)
  }
  
  // Get user by ID
  def getUserById(id: Long): Future[Option[User]] = {
    val query = users.filter(_.id === id).result.headOption
    db.run(query)
  }
  
  // Search by name
  def searchUsersByName(namePattern: String): Future[Seq[User]] = {
    val query = users.filter(_.name.like(s"%$namePattern%")).result
    db.run(query)
  }
  
  // Filter by age range
  def getUsersByAgeRange(minAge: Int, maxAge: Int): Future[Seq[User]] = {
    val query = users.filter(u => u.age >= minAge && u.age <= maxAge).result
    db.run(query)
  }
  
  // Create user
  def createUser(name: String, email: String, age: Int): Future[User] = {
    val insertAction = (users returning users.map(_.id)
      into ((user, id) => user.copy(id = Some(id)))
    ) += User(None, name, email, age, LocalDateTime.now())
    
    db.run(insertAction)
  }
  
  // Update user
  def updateUser(id: Long, name: String, email: String, age: Int): Future[Int] = {
    val updateAction = users.filter(_.id === id)
      .map(u => (u.name, u.email, u.age))
      .update((name, email, age))
    
    db.run(updateAction)
  }
  
  // Delete user
  def deleteUser(id: Long): Future[Int] = {
    val deleteAction = users.filter(_.id === id).delete
    db.run(deleteAction)
  }
  
  // Complex query example: Active user statistics
  def getUserStatistics: Future[(Int, Double, Int, Int)] = {
    val query = for {
      totalUsers <- users.length.result
      avgAge <- users.map(_.age).avg.result
      usersWithPosts <- users.filter(_.id.in(posts.map(_.userId).distinct)).length.result
      usersWithoutPosts <- users.filterNot(_.id.in(posts.map(_.userId).distinct)).length.result
    } yield (totalUsers, avgAge.getOrElse(0.0), usersWithPosts, usersWithoutPosts)
    
    db.run(query)
  }
}

// Usage example
def demonstrateBasicQueries(): Unit = {
  // Create user
  val createUserFuture = UserService.createUser("Dave", "[email protected]", 28)
  
  createUserFuture.onComplete {
    case Success(user) => 
      println(s"Created user: $user")
      
      // Get all users
      UserService.getAllUsers.foreach { users =>
        println(s"All users: $users")
      }
      
    case Failure(exception) => 
      println(s"Failed to create user: $exception")
  }
  
  // Search
  UserService.searchUsersByName("A").foreach { users =>
    println(s"Users with 'A' in name: $users")
  }
  
  // Age range filter
  UserService.getUsersByAgeRange(25, 35).foreach { users =>
    println(s"Users aged 25-35: $users")
  }
}

Relationships and Joins

// Join queries and relationship handling
object PostService {
  
  // Join users and posts
  def getPostsWithAuthors: Future[Seq[(Post, User)]] = {
    val query = posts.join(users).on(_.userId === _.id).result
    db.run(query)
  }
  
  // Left outer join: All users with optional posts
  def getUsersWithOptionalPosts: Future[Seq[(User, Option[Post])]] = {
    val query = users.joinLeft(posts).on(_.id === _.userId).result
    db.run(query)
  }
  
  // Complex join query: Post count per user
  def getUserPostCounts: Future[Seq[(User, Int)]] = {
    val query = users.joinLeft(posts).on(_.id === _.userId)
      .groupBy(_._1.id)
      .map { case (userId, group) => 
        (group.map(_._1).max, group.map(_._2).length)
      }
      .result
      
    db.run(query).map(_.collect {
      case (Some(user), count) => (user, count)
    })
  }
  
  // More efficient user post counts
  def getUserPostCountsEfficient: Future[Seq[(String, Int)]] = {
    val query = users.joinLeft(posts).on(_.id === _.userId)
      .groupBy(_._1.name)
      .map { case (userName, group) => 
        (userName, group.length)
      }
      .result
      
    db.run(query)
  }
  
  // Conditional join: Published posts only
  def getPublishedPostsWithAuthors: Future[Seq[(Post, User)]] = {
    val query = posts.filter(_.published === true)
      .join(users).on(_.userId === _.id)
      .result
      
    db.run(query)
  }
  
  // Create post
  def createPost(title: String, content: String, userId: Long, published: Boolean = false): Future[Post] = {
    val insertAction = (posts returning posts.map(_.id)
      into ((post, id) => post.copy(id = Some(id)))
    ) += Post(None, title, content, userId, published, LocalDateTime.now())
    
    db.run(insertAction)
  }
  
  // Get posts by user
  def getPostsByUser(userId: Long): Future[Seq[Post]] = {
    val query = posts.filter(_.userId === userId).result
    db.run(query)
  }
  
  // Popular users (by post count)
  def getPopularUsers(limit: Int = 10): Future[Seq[(String, Int)]] = {
    val query = users.join(posts).on(_.id === _.userId)
      .groupBy(_._1.name)
      .map { case (userName, group) => (userName, group.length) }
      .sortBy(_._2.desc)
      .take(limit)
      .result
      
    db.run(query)
  }
}

// Usage example
def demonstrateJoins(): Unit = {
  // Create post
  for {
    user <- UserService.getUserById(1L)
    _ <- user match {
      case Some(u) => 
        PostService.createPost("First Post", "This is my first post!", u.id.get, published = true)
      case None => 
        Future.failed(new RuntimeException("User not found"))
    }
    
    // Join query for posts and users
    postsWithAuthors <- PostService.getPostsWithAuthors
    _ = println(s"Posts with authors: $postsWithAuthors")
    
    // User post counts
    userPostCounts <- PostService.getUserPostCountsEfficient
    _ = println(s"User post counts: $userPostCounts")
    
  } yield ()
}

Transactions and Advanced Operations

import slick.dbio.Effect
import slick.sql.FixedSqlAction

object AdvancedUserService {
  
  // Transaction handling
  def transferPostsBetweenUsers(fromUserId: Long, toUserId: Long): Future[Unit] = {
    val transaction = (for {
      fromUser <- users.filter(_.id === fromUserId).result.headOption
      toUser <- users.filter(_.id === toUserId).result.headOption
      result <- (fromUser, toUser) match {
        case (Some(_), Some(_)) =>
          posts.filter(_.userId === fromUserId)
            .map(_.userId)
            .update(toUserId)
            .map(_ => ())
        case _ =>
          DBIO.failed(new RuntimeException("One or both users not found"))
      }
    } yield result).transactionally
    
    db.run(transaction)
  }
  
  // Batch processing
  def batchCreateUsers(usersData: Seq[(String, String, Int)]): Future[Seq[User]] = {
    val insertActions = usersData.map { case (name, email, age) =>
      User(None, name, email, age, LocalDateTime.now())
    }
    
    val batchInsert = (users returning users.map(_.id)
      into ((user, id) => user.copy(id = Some(id)))
    ) ++= insertActions
    
    db.run(batchInsert)
  }
  
  // Complex update: Conditional bulk update
  def updateUsersAgeByRange(ageIncrease: Int, minAge: Int, maxAge: Int): Future[Int] = {
    val updateAction = users.filter(u => u.age >= minAge && u.age <= maxAge)
      .map(_.age)
      .update(minAge + ageIncrease) // This is simplified; actual implementation would be more complex
    
    db.run(updateAction)
  }
  
  // Custom SQL execution
  def executeCustomQuery(minPostCount: Int): Future[Seq[(String, Int)]] = {
    val query = sql"""
      SELECT u.name, COUNT(p.id) as post_count
      FROM users u
      LEFT JOIN posts p ON u.id = p.user_id
      GROUP BY u.id, u.name
      HAVING COUNT(p.id) >= $minPostCount
      ORDER BY post_count DESC
    """.as[(String, Int)]
    
    db.run(query)
  }
  
  // Stream processing (for large datasets)
  def streamAllUsers: DatabasePublisher[User] = {
    db.stream(users.result)
  }
  
  // Conditional deletion and cleanup
  def cleanupInactiveUsers(daysSinceLastPost: Int): Future[Int] = {
    val cutoffDate = LocalDateTime.now().minusDays(daysSinceLastPost)
    
    val deleteAction = for {
      // Get user IDs without recent posts
      inactiveUserIds <- users.filterNot(_.id.in(
        posts.filter(_.createdAt > cutoffDate).map(_.userId)
      )).map(_.id).result
      
      // Delete users without posts
      deletedCount <- users.filter(_.id.inSet(inactiveUserIds.flatten.toSet)).delete
    } yield deletedCount
    
    db.run(deleteAction.transactionally)
  }
  
  // Statistics gathering
  def getUserEngagementStats: Future[UserEngagementStats] = {
    val query = for {
      totalUsers <- users.length.result
      totalPosts <- posts.length.result
      avgPostsPerUser <- posts.groupBy(_.userId).map(_._2.length).avg.result
      topUser <- users.join(posts).on(_.id === _.userId)
        .groupBy(_._1.name)
        .map { case (name, group) => (name, group.length) }
        .sortBy(_._2.desc)
        .take(1)
        .result
        .headOption
    } yield UserEngagementStats(
      totalUsers = totalUsers,
      totalPosts = totalPosts,
      avgPostsPerUser = avgPostsPerUser.getOrElse(0.0),
      topUser = topUser.map(_._1),
      topUserPostCount = topUser.map(_._2).getOrElse(0)
    )
    
    db.run(query)
  }
}

// Case class for statistics data
case class UserEngagementStats(
  totalUsers: Int,
  totalPosts: Int,
  avgPostsPerUser: Double,
  topUser: Option[String],
  topUserPostCount: Int
)

// Usage example
def demonstrateAdvancedOperations(): Unit = {
  import scala.concurrent.duration._
  
  // Batch user creation
  val newUsers = Seq(
    ("Eve", "[email protected]", 29),
    ("Frank", "[email protected]", 31),
    ("Grace", "[email protected]", 27)
  )
  
  val batchCreateFuture = AdvancedUserService.batchCreateUsers(newUsers)
  
  batchCreateFuture.foreach { createdUsers =>
    println(s"Batch created users: $createdUsers")
    
    // Get statistics
    AdvancedUserService.getUserEngagementStats.foreach { stats =>
      println(s"Engagement stats: $stats")
    }
  }
  
  // Execute custom query
  AdvancedUserService.executeCustomQuery(1).foreach { results =>
    println(s"Users with at least 1 post: $results")
  }
}

Error Handling

import scala.concurrent.Future
import scala.util.{Success, Failure}
import slick.jdbc.SQLActionBuilder
import java.sql.SQLException

// Custom exception classes
case class UserNotFoundException(userId: Long) extends RuntimeException(s"User not found: $userId")
case class DuplicateEmailException(email: String) extends RuntimeException(s"Email already exists: $email")
case class DatabaseOperationException(message: String, cause: Throwable) extends RuntimeException(message, cause)

object SafeUserService {
  
  // Safe user creation
  def createUserSafely(name: String, email: String, age: Int): Future[Either[String, User]] = {
    // Validation
    if (name.trim.isEmpty) {
      return Future.successful(Left("Name cannot be empty"))
    }
    if (!email.contains("@")) {
      return Future.successful(Left("Invalid email format"))
    }
    if (age < 0 || age > 150) {
      return Future.successful(Left("Invalid age range"))
    }
    
    val action = for {
      // Duplicate check
      existingUser <- users.filter(_.email === email).result.headOption
      result <- existingUser match {
        case Some(_) => 
          DBIO.failed(DuplicateEmailException(email))
        case None =>
          (users returning users.map(_.id)
            into ((user, id) => user.copy(id = Some(id)))
          ) += User(None, name, email, age, LocalDateTime.now())
      }
    } yield result
    
    db.run(action.transactionally)
      .map(Right(_))
      .recover {
        case DuplicateEmailException(email) => Left(s"Email $email is already registered")
        case ex: SQLException => Left(s"Database error: ${ex.getMessage}")
        case ex: Exception => Left(s"Unexpected error: ${ex.getMessage}")
      }
  }
  
  // Safe user retrieval
  def getUserSafely(userId: Long): Future[Either[String, User]] = {
    val query = users.filter(_.id === userId).result.headOption
    
    db.run(query)
      .map {
        case Some(user) => Right(user)
        case None => Left(s"User with ID $userId not found")
      }
      .recover {
        case ex: SQLException => Left(s"Database error: ${ex.getMessage}")
        case ex: Exception => Left(s"Unexpected error: ${ex.getMessage}")
      }
  }
  
  // Safe user update
  def updateUserSafely(userId: Long, name: String, email: String, age: Int): Future[Either[String, User]] = {
    // Validation
    if (name.trim.isEmpty) {
      return Future.successful(Left("Name cannot be empty"))
    }
    if (!email.contains("@")) {
      return Future.successful(Left("Invalid email format"))
    }
    
    val action = for {
      // User existence check
      existingUser <- users.filter(_.id === userId).result.headOption
      result <- existingUser match {
        case None =>
          DBIO.failed(UserNotFoundException(userId))
        case Some(user) =>
          // Email duplicate check (excluding self)
          for {
            duplicateCheck <- users.filter(u => u.email === email && u.id =!= userId).result.headOption
            updateResult <- duplicateCheck match {
              case Some(_) => 
                DBIO.failed(DuplicateEmailException(email))
              case None =>
                users.filter(_.id === userId)
                  .map(u => (u.name, u.email, u.age))
                  .update((name, email, age))
                  .flatMap(_ => users.filter(_.id === userId).result.head)
            }
          } yield updateResult
      }
    } yield result
    
    db.run(action.transactionally)
      .map(Right(_))
      .recover {
        case UserNotFoundException(id) => Left(s"User with ID $id not found")
        case DuplicateEmailException(email) => Left(s"Email $email is already in use")
        case ex: SQLException => Left(s"Database error: ${ex.getMessage}")
        case ex: Exception => Left(s"Unexpected error: ${ex.getMessage}")
      }
  }
  
  // Safe deletion (with related data check)
  def deleteUserSafely(userId: Long): Future[Either[String, Boolean]] = {
    val action = for {
      // User existence check
      existingUser <- users.filter(_.id === userId).result.headOption
      result <- existingUser match {
        case None =>
          DBIO.successful(false)
        case Some(_) =>
          // Related posts check
          for {
            postCount <- posts.filter(_.userId === userId).length.result
            deleteResult <- if (postCount > 0) {
              // Delete related posts as well
              for {
                _ <- posts.filter(_.userId === userId).delete
                deletedUsers <- users.filter(_.id === userId).delete
              } yield deletedUsers > 0
            } else {
              users.filter(_.id === userId).delete.map(_ > 0)
            }
          } yield deleteResult
      }
    } yield result
    
    db.run(action.transactionally)
      .map(Right(_))
      .recover {
        case ex: SQLException => Left(s"Database error: ${ex.getMessage}")
        case ex: Exception => Left(s"Unexpected error: ${ex.getMessage}")
      }
  }
  
  // Safe batch operation execution
  def batchOperationSafely[T](operations: Seq[DBIO[T]]): Future[Either[String, Seq[T]]] = {
    val batchAction = DBIO.sequence(operations).transactionally
    
    db.run(batchAction)
      .map(Right(_))
      .recover {
        case ex: SQLException => Left(s"Batch operation failed: ${ex.getMessage}")
        case ex: Exception => Left(s"Unexpected error in batch operation: ${ex.getMessage}")
      }
  }
  
  // Database connection test
  def testConnection: Future[Either[String, String]] = {
    val testQuery = sql"SELECT 1".as[Int]
    
    db.run(testQuery)
      .map(_ => Right("Database connection successful"))
      .recover {
        case ex: SQLException => Left(s"Database connection failed: ${ex.getMessage}")
        case ex: Exception => Left(s"Connection test error: ${ex.getMessage}")
      }
  }
}

// Error handling usage example
def demonstrateErrorHandling(): Unit = {
  // Safe user creation
  SafeUserService.createUserSafely("John Doe", "[email protected]", 30).foreach {
    case Right(user) => 
      println(s"Successfully created user: $user")
      
      // Safe update
      SafeUserService.updateUserSafely(user.id.get, "John Smith", "[email protected]", 31).foreach {
        case Right(updatedUser) => println(s"Successfully updated user: $updatedUser")
        case Left(error) => println(s"Update failed: $error")
      }
      
    case Left(error) => 
      println(s"User creation failed: $error")
  }
  
  // Test retrieving non-existent user
  SafeUserService.getUserSafely(999L).foreach {
    case Right(user) => println(s"Found user: $user")
    case Left(error) => println(s"User not found: $error")
  }
  
  // Connection test
  SafeUserService.testConnection.foreach {
    case Right(message) => println(message)
    case Left(error) => println(s"Connection test failed: $error")
  }
}