Library

Quill

Overview

Quill is "a type-safe database access library for Scala" designed as a macro-based database access layer that generates SQL at compile time. Based on the concept of "Compile-time Language Integrated Queries," it leverages Scala's type system and macro capabilities to check query validity at compile time rather than runtime, generating type-safe SQL code. Supporting various databases (PostgreSQL, MySQL, SQLite, H2, Cassandra, MongoDB) and providing both synchronous and asynchronous execution models, it enables high-performance data access in modern Scala applications.

Details

Quill 2025 edition fully utilizes the latest features of Scala 3, providing a more intuitive and type-safe database programming experience. Compile-time SQL generation through macros completely eliminates runtime query construction overhead, achieving excellent performance. Integration with Akka Streams, ZIO, and cats-effect strongly supports reactive application development. Additionally, dependency injection using Context Functions and type class-based extensions through Given Instances (formerly Implicit) naturally integrate with functional programming best practices.

Key Features

• Compile-time SQL Generation: Zero runtime overhead queries through macros
• Type Safety: Safe queries maximizing Scala's type system
• Multi-Database: Unified API for relational and NoSQL databases
• Async Support: Complete integration with Future, ZIO, cats-effect
• DSL: Intuitive and expressive query description language
• Zero Runtime Dependencies: Generated code is pure JDBC/driver calls

Pros and Cons

Pros

• Best performance with zero runtime query generation overhead
• Complete type safety and compile-time error detection through Scala's type system
• Natural query description through intuitive, Scala-idiomatic DSL
• Ability to operate multiple database engines with the same API
• Complete integration with functional programming paradigms
• Lightweight with minimal runtime dependencies

Cons

• High learning curve requiring understanding of Scala macros
• Increased compilation time, potentially slowing builds in large projects
• Difficulty constructing dynamic queries with limited flexibility
• Limited IDE support making debugging difficult in some cases
• Potential macro expansion errors with complex queries
• Limited Japanese documentation and community resources

Reference Pages

• Quill Official Site
• Quill GitHub Repository
• Quill Documentation

Code Examples

Setup

// build.sbt
val quillVersion = "4.8.0"

libraryDependencies ++= Seq(
  "io.getquill" %% "quill-jdbc" % quillVersion,
  "io.getquill" %% "quill-jdbc-zio" % quillVersion,
  "io.getquill" %% "quill-cassandra" % quillVersion,
  "io.getquill" %% "quill-cassandra-zio" % quillVersion,
  
  // Database drivers
  "org.postgresql" % "postgresql" % "42.7.1",
  "mysql" % "mysql-connector-java" % "8.0.33",
  "com.h2database" % "h2" % "2.2.224",
  
  // Connection pool (optional)
  "com.zaxxer" % "HikariCP" % "5.1.0",
  
  // ZIO (for async processing)
  "dev.zio" %% "zio" % "2.0.21",
  "dev.zio" %% "zio-streams" % "2.0.21"
)

// application.conf
quill {
  dataSourceClassName = "org.postgresql.ds.PGSimpleDataSource"
  dataSource {
    user = "postgres"
    password = "password"
    databaseName = "mydb"
    portNumber = 5432
    serverName = "localhost"
  }
  connectionTimeout = 30000
  maximumPoolSize = 10
}

Basic Usage

import io.getquill._
import java.time.LocalDateTime

// Database context setup
lazy val ctx = new PostgresJdbcContext(SnakeCase, "quill")
import ctx._

// Case class definitions
case class User(
  id: Option[Long],
  name: String,
  email: String,
  age: Int,
  isActive: Boolean,
  createdAt: LocalDateTime
)

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

// Table definition and mapping
implicit val userSchemaMeta = schemaMeta[User]("users")
implicit val postSchemaMeta = schemaMeta[Post]("posts")

// Basic CRUD operations
object UserRepository {
  
  // Create user
  def createUser(name: String, email: String, age: Int): Long = {
    val user = User(None, name, email, age, isActive = true, LocalDateTime.now())
    ctx.run(quote {
      query[User].insertValue(lift(user)).returningGenerated(_.id)
    }).get
  }
  
  // Get user
  def getUserById(id: Long): Option[User] = {
    ctx.run(quote {
      query[User].filter(_.id.contains(lift(id)))
    }).headOption
  }
  
  // Get all users
  def getAllUsers: List[User] = {
    ctx.run(quote {
      query[User].sortBy(_.createdAt)(Ord.desc)
    })
  }
  
  // Search by name
  def searchUsersByName(namePattern: String): List[User] = {
    ctx.run(quote {
      query[User].filter(_.name.like(lift(s"%$namePattern%")))
    })
  }
  
  // Filter by age range
  def getUsersByAgeRange(minAge: Int, maxAge: Int): List[User] = {
    ctx.run(quote {
      query[User].filter(u => u.age >= lift(minAge) && u.age <= lift(maxAge))
    })
  }
  
  // Update user
  def updateUser(id: Long, name: String, email: String, age: Int): Long = {
    ctx.run(quote {
      query[User]
        .filter(_.id.contains(lift(id)))
        .update(u => u.name -> lift(name), u => u.email -> lift(email), u => u.age -> lift(age))
    })
  }
  
  // Delete user
  def deleteUser(id: Long): Long = {
    ctx.run(quote {
      query[User].filter(_.id.contains(lift(id))).delete
    })
  }
  
  // Get active users
  def getActiveUsers: List[User] = {
    ctx.run(quote {
      query[User].filter(_.isActive)
    })
  }
  
  // Count users
  def getUserCount: Long = {
    ctx.run(quote {
      query[User].size
    })
  }
  
  // Age statistics
  def getAgeStatistics: (Option[Int], Option[Int], Option[Double]) = {
    val stats = ctx.run(quote {
      query[User].map(u => (u.age, u.age, u.age))
        .aggregate((min(_._1), max(_._2), avg(_._3)))
    })
    stats
  }
}

// Usage example
def demonstrateBasicOperations(): Unit = {
  // Create users
  val userId1 = UserRepository.createUser("Alice Johnson", "[email protected]", 28)
  val userId2 = UserRepository.createUser("Bob Smith", "[email protected]", 32)
  val userId3 = UserRepository.createUser("Charlie Brown", "[email protected]", 25)
  
  println(s"Created users with IDs: $userId1, $userId2, $userId3")
  
  // Get user
  val user = UserRepository.getUserById(userId1)
  println(s"Retrieved user: $user")
  
  // Search by name
  val aliceUsers = UserRepository.searchUsersByName("Alice")
  println(s"Users with 'Alice' in name: $aliceUsers")
  
  // Age range filter
  val youngUsers = UserRepository.getUsersByAgeRange(25, 30)
  println(s"Users aged 25-30: $youngUsers")
  
  // Get statistics
  val (minAge, maxAge, avgAge) = UserRepository.getAgeStatistics
  println(s"Age stats - Min: $minAge, Max: $maxAge, Avg: $avgAge")
}

Complex Queries and JOINs

object PostRepository {
  
  // Create post
  def createPost(title: String, content: String, userId: Long, published: Boolean = false): Long = {
    val post = Post(None, title, content, userId, published, LocalDateTime.now())
    ctx.run(quote {
      query[Post].insertValue(lift(post)).returningGenerated(_.id)
    }).get
  }
  
  // JOIN users and posts
  def getPostsWithAuthors: List[(Post, User)] = {
    ctx.run(quote {
      for {
        post <- query[Post]
        user <- query[User] if user.id.contains(post.userId)
      } yield (post, user)
    })
  }
  
  // Get published posts only
  def getPublishedPosts: List[Post] = {
    ctx.run(quote {
      query[Post].filter(_.published)
    })
  }
  
  // User post counts
  def getUserPostCounts: List[(String, Long)] = {
    ctx.run(quote {
      (for {
        post <- query[Post]
        user <- query[User] if user.id.contains(post.userId)
      } yield (user.name, post.id))
        .groupBy(_._1)
        .map { case (userName, posts) => (userName, posts.size) }
    })
  }
  
  // Popular users (by post count)
  def getPopularUsers(limit: Int): List[(String, Long)] = {
    ctx.run(quote {
      (for {
        post <- query[Post]
        user <- query[User] if user.id.contains(post.userId)
      } yield (user.name, post.id))
        .groupBy(_._1)
        .map { case (userName, posts) => (userName, posts.size) }
        .sortBy(_._2)(Ord.desc)
        .take(lift(limit))
    })
  }
  
  // Conditional search
  def searchPosts(
    titlePattern: Option[String] = None,
    userId: Option[Long] = None,
    publishedOnly: Boolean = false,
    limit: Int = 20
  ): List[Post] = {
    ctx.run(quote {
      query[Post]
        .filter(p => 
          lift(titlePattern).forall(pattern => p.title.like(s"%$pattern%")) &&
          lift(userId).forall(uid => p.userId == uid) &&
          (!lift(publishedOnly) || p.published)
        )
        .sortBy(_.createdAt)(Ord.desc)
        .take(lift(limit))
    })
  }
  
  // Recent post statistics
  def getRecentPostStats(days: Int): (Long, Long, Long) = {
    val cutoffDate = LocalDateTime.now().minusDays(days)
    ctx.run(quote {
      val recentPosts = query[Post].filter(_.createdAt > lift(cutoffDate))
      val totalPosts = recentPosts.size
      val publishedPosts = recentPosts.filter(_.published).size
      val draftPosts = recentPosts.filter(!_.published).size
      (totalPosts, publishedPosts, draftPosts)
    })
  }
  
  // Latest post by user
  def getLatestPostByUser(userId: Long): Option[Post] = {
    ctx.run(quote {
      query[Post]
        .filter(_.userId == lift(userId))
        .sortBy(_.createdAt)(Ord.desc)
    }).headOption
  }
}

// Complex query usage example
def demonstrateComplexQueries(): Unit = {
  // Create posts
  val postId1 = PostRepository.createPost("First Post", "This is my first post!", 1L, published = true)
  val postId2 = PostRepository.createPost("Second Post", "Another interesting post", 1L, published = false)
  val postId3 = PostRepository.createPost("Published Post", "This is published", 2L, published = true)
  
  println(s"Created posts with IDs: $postId1, $postId2, $postId3")
  
  // JOIN query
  val postsWithAuthors = PostRepository.getPostsWithAuthors
  println(s"Posts with authors: $postsWithAuthors")
  
  // User post counts
  val userPostCounts = PostRepository.getUserPostCounts
  println(s"User post counts: $userPostCounts")
  
  // Popular users
  val popularUsers = PostRepository.getPopularUsers(5)
  println(s"Popular users: $popularUsers")
  
  // Conditional search
  val searchResults = PostRepository.searchPosts(
    titlePattern = Some("Post"),
    publishedOnly = true,
    limit = 10
  )
  println(s"Search results: $searchResults")
  
  // Recent post statistics
  val (total, published, drafts) = PostRepository.getRecentPostStats(7)
  println(s"Recent posts (7 days) - Total: $total, Published: $published, Drafts: $drafts")
}

ZIO Integration (Async Processing)

import zio._
import io.getquill.context.ZioJdbc._

// ZIO-compatible context
lazy val zioCtx = new PostgresZioJdbcContext(SnakeCase)
import zioCtx._

object ZioUserService {
  
  // Async user creation
  def createUserAsync(name: String, email: String, age: Int): ZIO[Any, Throwable, Long] = {
    val user = User(None, name, email, age, isActive = true, LocalDateTime.now())
    zioCtx.run(quote {
      query[User].insertValue(lift(user)).returningGenerated(_.id)
    }).map(_.get)
  }
  
  // Async user retrieval
  def getUserByIdAsync(id: Long): ZIO[Any, Throwable, Option[User]] = {
    zioCtx.run(quote {
      query[User].filter(_.id.contains(lift(id)))
    }).map(_.headOption)
  }
  
  // Batch processing
  def batchCreateUsers(users: List[(String, String, Int)]): ZIO[Any, Throwable, List[Long]] = {
    ZIO.foreach(users) { case (name, email, age) =>
      createUserAsync(name, email, age)
    }
  }
  
  // Transaction processing
  def transferDataBetweenUsers(fromId: Long, toId: Long): ZIO[Any, Throwable, Unit] = {
    zioCtx.transaction {
      for {
        fromUser <- zioCtx.run(quote {
          query[User].filter(_.id.contains(lift(fromId)))
        }).map(_.headOption)
        toUser <- zioCtx.run(quote {
          query[User].filter(_.id.contains(lift(toId)))
        }).map(_.headOption)
        _ <- ZIO.when(fromUser.isEmpty || toUser.isEmpty)(
          ZIO.fail(new RuntimeException("One or both users not found"))
        )
        _ <- zioCtx.run(quote {
          query[Post].filter(_.userId == lift(fromId)).update(_.userId -> lift(toId))
        })
      } yield ()
    }
  }
  
  // Streaming processing
  def streamAllUsers: ZStream[Any, Throwable, User] = {
    ZStream.fromZIO(zioCtx.run(quote {
      query[User].sortBy(_.id)
    })).flatMap(ZStream.fromIterable)
  }
  
  // Complex statistics processing
  def getUserEngagementStats: ZIO[Any, Throwable, UserEngagementStats] = {
    for {
      totalUsers <- zioCtx.run(quote { query[User].size })
      totalPosts <- zioCtx.run(quote { query[Post].size })
      avgPostsPerUser <- zioCtx.run(quote {
        (for {
          post <- query[Post]
          user <- query[User] if user.id.contains(post.userId)
        } yield post.id)
          .groupBy(_ => 1)
          .map { case (_, posts) => posts.size }
          .avg
      }).map(_.getOrElse(0.0))
      topUserWithPostCount <- zioCtx.run(quote {
        (for {
          post <- query[Post]
          user <- query[User] if user.id.contains(post.userId)
        } yield (user.name, post.id))
          .groupBy(_._1)
          .map { case (userName, posts) => (userName, posts.size) }
          .sortBy(_._2)(Ord.desc)
      }).map(_.headOption)
    } yield UserEngagementStats(
      totalUsers = totalUsers,
      totalPosts = totalPosts,
      avgPostsPerUser = avgPostsPerUser,
      topUser = topUserWithPostCount.map(_._1),
      topUserPostCount = topUserWithPostCount.map(_._2).getOrElse(0L)
    )
  }
}

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

// ZIO application example
object QuillZioApp extends ZIOAppDefault {
  
  def run: ZIO[ZIOAppArgs, Any, Any] = {
    val program = for {
      // Batch user creation
      userIds <- ZioUserService.batchCreateUsers(List(
        ("Emma Wilson", "[email protected]", 26),
        ("David Chen", "[email protected]", 31),
        ("Sarah Johnson", "[email protected]", 29)
      ))
      _ <- Console.printLine(s"Created users with IDs: $userIds")
      
      // User retrieval
      user <- ZioUserService.getUserByIdAsync(userIds.head)
      _ <- Console.printLine(s"Retrieved user: $user")
      
      // Statistics retrieval
      stats <- ZioUserService.getUserEngagementStats
      _ <- Console.printLine(s"Engagement stats: $stats")
      
      // User streaming
      _ <- ZioUserService.streamAllUsers
        .take(5)
        .foreach(user => Console.printLine(s"Streamed user: ${user.name}"))
      
    } yield ()
    
    program.catchAll { error =>
      Console.printLineError(s"Application error: ${error.getMessage}")
    }
  }
}

Error Handling

import scala.util.{Try, Success, Failure}
import zio._

// Custom exception classes
sealed trait QuillAppError extends Throwable
case class UserNotFoundError(userId: Long) extends QuillAppError {
  override def getMessage: String = s"User not found: $userId"
}
case class DuplicateEmailError(email: String) extends QuillAppError {
  override def getMessage: String = s"Email already exists: $email"
}
case class ValidationError(message: String) extends QuillAppError {
  override def getMessage: String = s"Validation error: $message"
}
case class DatabaseError(cause: Throwable) extends QuillAppError {
  override def getMessage: String = s"Database error: ${cause.getMessage}"
  override def getCause: Throwable = cause
}

object SafeUserService {
  
  // Safe user creation (sync version)
  def createUserSafely(name: String, email: String, age: Int): Either[QuillAppError, User] = {
    Try {
      // Validation
      if (name.trim.isEmpty) {
        return Left(ValidationError("Name cannot be empty"))
      }
      if (!email.contains("@")) {
        return Left(ValidationError("Invalid email format"))
      }
      if (age < 0 || age > 150) {
        return Left(ValidationError("Age must be between 0 and 150"))
      }
      
      // Duplicate check
      val existingUser = ctx.run(quote {
        query[User].filter(_.email == lift(email))
      }).headOption
      
      if (existingUser.isDefined) {
        return Left(DuplicateEmailError(email))
      }
      
      // Create user
      val user = User(None, name, email, age, isActive = true, LocalDateTime.now())
      val userId = ctx.run(quote {
        query[User].insertValue(lift(user)).returningGenerated(_.id)
      }).get
      
      user.copy(id = Some(userId))
    } match {
      case Success(user) => Right(user)
      case Failure(exception) => Left(DatabaseError(exception))
    }
  }
  
  // Safe user retrieval (sync version)
  def getUserSafely(id: Long): Either[QuillAppError, User] = {
    Try {
      ctx.run(quote {
        query[User].filter(_.id.contains(lift(id)))
      }).headOption
    } match {
      case Success(Some(user)) => Right(user)
      case Success(None) => Left(UserNotFoundError(id))
      case Failure(exception) => Left(DatabaseError(exception))
    }
  }
  
  // Safe user update (sync version)
  def updateUserSafely(id: Long, name: String, email: String, age: Int): Either[QuillAppError, User] = {
    Try {
      // Validation
      if (name.trim.isEmpty) {
        return Left(ValidationError("Name cannot be empty"))
      }
      if (!email.contains("@")) {
        return Left(ValidationError("Invalid email format"))
      }
      if (age < 0 || age > 150) {
        return Left(ValidationError("Invalid age"))
      }
      
      // User existence check
      val existingUser = ctx.run(quote {
        query[User].filter(_.id.contains(lift(id)))
      }).headOption
      
      if (existingUser.isEmpty) {
        return Left(UserNotFoundError(id))
      }
      
      // Email duplicate check (excluding self)
      val emailConflict = ctx.run(quote {
        query[User].filter(u => u.email == lift(email) && !u.id.contains(lift(id)))
      }).headOption
      
      if (emailConflict.isDefined) {
        return Left(DuplicateEmailError(email))
      }
      
      // Execute update
      ctx.run(quote {
        query[User]
          .filter(_.id.contains(lift(id)))
          .update(u => u.name -> lift(name), u => u.email -> lift(email), u => u.age -> lift(age))
      })
      
      // Get updated user
      ctx.run(quote {
        query[User].filter(_.id.contains(lift(id)))
      }).head
      
    } match {
      case Success(user) => Right(user)
      case Failure(exception) => Left(DatabaseError(exception))
    }
  }
}

// ZIO version error handling
object ZioSafeUserService {
  
  // Safe user creation (ZIO version)
  def createUserSafely(name: String, email: String, age: Int): ZIO[Any, QuillAppError, User] = {
    for {
      _ <- ZIO.when(name.trim.isEmpty)(ZIO.fail(ValidationError("Name cannot be empty")))
      _ <- ZIO.when(!email.contains("@"))(ZIO.fail(ValidationError("Invalid email format")))
      _ <- ZIO.when(age < 0 || age > 150)(ZIO.fail(ValidationError("Age must be between 0 and 150")))
      
      existingUser <- zioCtx.run(quote {
        query[User].filter(_.email == lift(email))
      }).map(_.headOption).mapError(DatabaseError)
      
      _ <- ZIO.when(existingUser.isDefined)(ZIO.fail(DuplicateEmailError(email)))
      
      user = User(None, name, email, age, isActive = true, LocalDateTime.now())
      userId <- zioCtx.run(quote {
        query[User].insertValue(lift(user)).returningGenerated(_.id)
      }).map(_.get).mapError(DatabaseError)
      
    } yield user.copy(id = Some(userId))
  }
  
  // Safe user retrieval (ZIO version)
  def getUserSafely(id: Long): ZIO[Any, QuillAppError, User] = {
    zioCtx.run(quote {
      query[User].filter(_.id.contains(lift(id)))
    }).mapError(DatabaseError)
      .flatMap(users => 
        ZIO.fromOption(users.headOption).mapError(_ => UserNotFoundError(id))
      )
  }
  
  // Bulk operations (ZIO version)
  def bulkOperationSafely[T](operations: List[ZIO[Any, QuillAppError, T]]): ZIO[Any, QuillAppError, List[T]] = {
    ZIO.collectAll(operations)
  }
  
  // Database connection test
  def testConnection: ZIO[Any, QuillAppError, String] = {
    zioCtx.run(quote(sql"SELECT 1".as[Int]))
      .map(_ => "Database connection successful")
      .mapError(DatabaseError)
  }
}

// Error handling usage example
def demonstrateErrorHandling(): Unit = {
  // Sync version error handling
  SafeUserService.createUserSafely("Jane Doe", "[email protected]", 28) match {
    case Right(user) => 
      println(s"Successfully created user: $user")
      
      SafeUserService.updateUserSafely(user.id.get, "Jane Smith", "[email protected]", 29) match {
        case Right(updatedUser) => println(s"Successfully updated user: $updatedUser")
        case Left(error) => println(s"Update failed: ${error.getMessage}")
      }
      
    case Left(error) => 
      println(s"User creation failed: ${error.getMessage}")
  }
  
  // Test with invalid data
  SafeUserService.createUserSafely("", "invalid-email", -5) match {
    case Right(_) => println("This shouldn't happen")
    case Left(error) => println(s"Correctly caught validation error: ${error.getMessage}")
  }
  
  // Test retrieving non-existent user
  SafeUserService.getUserSafely(999L) match {
    case Right(user) => println(s"Found user: $user")
    case Left(error) => println(s"User not found error: ${error.getMessage}")
  }
}

// ZIO version error handling usage example
val zioErrorHandlingExample: ZIO[Any, Nothing, Unit] = {
  val program = for {
    user <- ZioSafeUserService.createUserSafely("John Doe", "[email protected]", 30)
    _ <- Console.printLine(s"Created user: $user")
    
    retrievedUser <- ZioSafeUserService.getUserSafely(user.id.get)
    _ <- Console.printLine(s"Retrieved user: $retrievedUser")
    
    connectionStatus <- ZioSafeUserService.testConnection
    _ <- Console.printLine(connectionStatus)
    
  } yield ()
  
  program.catchAll {
    case ValidationError(message) => Console.printLineError(s"Validation error: $message")
    case UserNotFoundError(userId) => Console.printLineError(s"User $userId not found")
    case DuplicateEmailError(email) => Console.printLineError(s"Email $email already exists")
    case DatabaseError(cause) => Console.printLineError(s"Database error: ${cause.getMessage}")
  }
}