Library

sqlpp11

Overview

sqlpp11 is a type-safe SQL library porting C++11 template technology to Scala. It achieves safe database access through compile-time SQL verification and advanced type system, enabling natural SQL syntax writing while guaranteeing SQL query type safety.

Details

sqlpp11 2025 edition is adopted for special purposes in type safety priority projects. Limited use in financial and medical systems requiring advanced type checking, it significantly reduces runtime errors by detecting SQL errors at compile time. Through an innovative approach using template metaprogramming, it structurally prevents SQL injection attacks and guarantees consistency between database schema and code.

Key Features

• Compile-time SQL Verification: SQL syntax checking through type system
• Type Safety: Complete type inference and checking
• Natural SQL Syntax: Intuitive DSL close to SQL
• Zero Overhead: No runtime cost
• SQL Injection Protection: Structurally safe
• Database Independent: Multiple DB support

Pros and Cons

Pros

• Detects SQL errors at compile time
• High reliability through type safety
• Structurally prevents SQL injection attacks
• No performance overhead
• Natural and readable SQL syntax
• Easy to test (mockable)

Cons

• Very steep learning curve
• Increased compilation time
• Complex error messages
• Limited community
• Insufficient documentation
• Difficult to build dynamic queries

References

• sqlpp11 GitHub Repository
• sqlpp11 Wiki

Examples

Basic Setup

// build.sbt
libraryDependencies ++= Seq(
  "com.github.rbock" %% "sqlpp11-scala" % "0.60",
  "org.postgresql" % "postgresql" % "42.5.0"
)

// Table definition generation (auto-generated from DDL)
// CREATE TABLE users (
//   id SERIAL PRIMARY KEY,
//   name VARCHAR(100) NOT NULL,
//   email VARCHAR(255) UNIQUE NOT NULL,
//   age INTEGER,
//   created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
// );

// tables/Users.scala (auto-generated code)
object Users extends Table("users") {
  val id = column[Int]("id").primaryKey.autoIncrement
  val name = column[String]("name").notNull
  val email = column[String]("email").unique.notNull
  val age = column[Option[Int]]("age")
  val createdAt = column[Timestamp]("created_at").default(currentTimestamp)
  
  type Row = (Int, String, String, Option[Int], Timestamp)
}

// tables/Posts.scala
object Posts extends Table("posts") {
  val id = column[Int]("id").primaryKey.autoIncrement
  val title = column[String]("title").notNull
  val content = column[String]("content")
  val authorId = column[Int]("author_id").references(Users.id)
  val published = column[Boolean]("published").default(false)
  val viewCount = column[Int]("view_count").default(0)
  val createdAt = column[Timestamp]("created_at").default(currentTimestamp)
}

Basic CRUD Operations

import sqlpp11._
import java.sql.{Connection, DriverManager}

class UserRepository(implicit conn: Connection) {
  import Users._
  
  // CREATE - Create new records
  def createUser(name: String, email: String, age: Option[Int]): Int = {
    val query = insertInto(Users)
      .set(Users.name := name)
      .set(Users.email := email)
      .set(Users.age := age)
      .returning(Users.id)
    
    query.execute()
  }
  
  // Batch insert
  def batchCreateUsers(users: Seq[(String, String, Option[Int])]): Unit = {
    val insert = insertInto(Users)
      .columns(Users.name, Users.email, Users.age)
    
    users.foreach { case (name, email, age) =>
      insert.values(name, email, age)
    }
    
    insert.execute()
  }
  
  // READ - Read records
  def findById(userId: Int): Option[User] = {
    val query = select(all)
      .from(Users)
      .where(Users.id === userId)
    
    query.fetchOne().map(rowToUser)
  }
  
  def findByEmail(email: String): Option[User] = {
    val query = select(all)
      .from(Users)
      .where(Users.email === email)
    
    query.fetchOne().map(rowToUser)
  }
  
  def findAll(): Seq[User] = {
    val query = select(all)
      .from(Users)
      .orderBy(Users.name.asc)
    
    query.fetch().map(rowToUser)
  }
  
  // Conditional search
  def searchUsers(
    namePattern: Option[String] = None,
    minAge: Option[Int] = None,
    maxAge: Option[Int] = None
  ): Seq[User] = {
    var query = select(all).from(Users)
    
    namePattern.foreach { pattern =>
      query = query.where(Users.name.like(s"%$pattern%"))
    }
    
    minAge.foreach { age =>
      query = query.where(Users.age >= age)
    }
    
    maxAge.foreach { age =>
      query = query.where(Users.age <= age)
    }
    
    query.orderBy(Users.name.asc).fetch().map(rowToUser)
  }
  
  // UPDATE - Update records
  def updateUser(id: Int, name: String, age: Option[Int]): Boolean = {
    val query = update(Users)
      .set(Users.name := name)
      .set(Users.age := age)
      .where(Users.id === id)
    
    query.execute() > 0
  }
  
  // DELETE - Delete records
  def deleteUser(id: Int): Boolean = {
    val query = deleteFrom(Users)
      .where(Users.id === id)
    
    query.execute() > 0
  }
  
  // Helper method
  private def rowToUser(row: Users.Row): User = row match {
    case (id, name, email, age, createdAt) =>
      User(id, name, email, age, createdAt)
  }
}

case class User(
  id: Int,
  name: String,
  email: String,
  age: Option[Int],
  createdAt: Timestamp
)

Advanced Query Operations

class AdvancedQueries(implicit conn: Connection) {
  import Users._
  import Posts._
  
  // JOIN operations
  def getUsersWithPostCount(): Seq[(User, Int)] = {
    val query = select(
      Users.all,
      count(Posts.id).as("post_count")
    )
    .from(Users)
    .leftJoin(Posts).on(Users.id === Posts.authorId)
    .groupBy(Users.id)
    .orderBy(count(Posts.id).desc)
    
    query.fetch().map { row =>
      val user = User(
        row.get(Users.id),
        row.get(Users.name),
        row.get(Users.email),
        row.get(Users.age),
        row.get(Users.createdAt)
      )
      val postCount = row.get[Int]("post_count")
      (user, postCount)
    }
  }
  
  // Subqueries
  def getActiveUsers(): Seq[User] = {
    val recentPosts = select(Posts.authorId)
      .from(Posts)
      .where(Posts.createdAt > now() - interval(30, "days"))
      .groupBy(Posts.authorId)
      .having(count(Posts.id) >= 5)
    
    val query = select(all)
      .from(Users)
      .where(Users.id.in(recentPosts))
    
    query.fetch().map(rowToUser)
  }
  
  // UNION operations
  def getAllAuthors(): Seq[String] = {
    val postAuthors = select(Users.name)
      .from(Users)
      .innerJoin(Posts).on(Users.id === Posts.authorId)
      .where(Posts.published === true)
    
    val commentAuthors = select(Users.name)
      .from(Users)
      .innerJoin(Comments).on(Users.id === Comments.userId)
    
    val query = postAuthors.union(commentAuthors).distinct
    
    query.fetch().map(_.get[String](Users.name))
  }
  
  // Window functions
  def getUserRankings(): Seq[(User, Int)] = {
    val query = select(
      Users.all,
      rowNumber().over(
        orderBy(count(Posts.id).desc)
      ).as("rank")
    )
    .from(Users)
    .leftJoin(Posts).on(Users.id === Posts.authorId)
    .groupBy(Users.id)
    
    query.fetch().map { row =>
      val user = rowToUser((
        row.get(Users.id),
        row.get(Users.name),
        row.get(Users.email),
        row.get(Users.age),
        row.get(Users.createdAt)
      ))
      val rank = row.get[Int]("rank")
      (user, rank)
    }
  }
  
  // CTE (Common Table Expression)
  def getTopAuthorsWithStats(): Seq[AuthorStats] = {
    val authorStats = withCte("author_stats")
      .as(
        select(
          Posts.authorId,
          count(Posts.id).as("total_posts"),
          sum(Posts.viewCount).as("total_views"),
          avg(Posts.viewCount).as("avg_views")
        )
        .from(Posts)
        .where(Posts.published === true)
        .groupBy(Posts.authorId)
      )
    
    val query = select(
      Users.name,
      authorStats("total_posts"),
      authorStats("total_views"),
      authorStats("avg_views")
    )
    .from(Users)
    .innerJoin(authorStats).on(Users.id === authorStats("author_id"))
    .where(authorStats("total_posts") > 10)
    .orderBy(authorStats("total_views").desc)
    
    query.fetch().map { row =>
      AuthorStats(
        name = row.get[String](Users.name),
        totalPosts = row.get[Int]("total_posts"),
        totalViews = row.get[Int]("total_views"),
        avgViews = row.get[Double]("avg_views")
      )
    }
  }
  
  // Dynamic query building
  def buildDynamicQuery(filters: QueryFilters): Query[User] = {
    var conditions = Seq.empty[Condition]
    
    filters.name.foreach { name =>
      conditions :+= Users.name.like(s"%$name%")
    }
    
    filters.email.foreach { email =>
      conditions :+= Users.email === email
    }
    
    filters.ageRange.foreach { case (min, max) =>
      conditions :+= (Users.age >= min and Users.age <= max)
    }
    
    filters.createdAfter.foreach { date =>
      conditions :+= Users.createdAt > date
    }
    
    val whereClause = conditions.reduceOption(_ and _)
      .getOrElse(trueLiteral)
    
    select(all)
      .from(Users)
      .where(whereClause)
      .orderBy(Users.createdAt.desc)
  }
  
  private def rowToUser(row: Users.Row): User = row match {
    case (id, name, email, age, createdAt) =>
      User(id, name, email, age, createdAt)
  }
}

case class AuthorStats(
  name: String,
  totalPosts: Int,
  totalViews: Int,
  avgViews: Double
)

case class QueryFilters(
  name: Option[String] = None,
  email: Option[String] = None,
  ageRange: Option[(Int, Int)] = None,
  createdAfter: Option[Timestamp] = None
)

Transaction Processing

class TransactionService(implicit conn: Connection) {
  import Users._
  import Posts._
  
  def transferPosts(fromUserId: Int, toUserId: Int): Either[String, Int] = {
    conn.setAutoCommit(false)
    
    try {
      // Verify users exist
      val fromUser = select(Users.id)
        .from(Users)
        .where(Users.id === fromUserId)
        .fetchOne()
      
      val toUser = select(Users.id)
        .from(Users)
        .where(Users.id === toUserId)
        .fetchOne()
      
      if (fromUser.isEmpty) {
        Left("Source user not found")
      } else if (toUser.isEmpty) {
        Left("Target user not found")
      } else {
        // Transfer posts
        val updateCount = update(Posts)
          .set(Posts.authorId := toUserId)
          .where(Posts.authorId === fromUserId)
          .execute()
        
        conn.commit()
        Right(updateCount)
      }
    } catch {
      case e: Exception =>
        conn.rollback()
        Left(s"Transaction failed: ${e.getMessage}")
    } finally {
      conn.setAutoCommit(true)
    }
  }
}

Practical Example

// Play Framework integration
import play.api.mvc._
import play.api.libs.json._
import javax.inject._
import scala.concurrent.{ExecutionContext, Future}

@Singleton
class UserController @Inject()(
  cc: ControllerComponents,
  db: Database
)(implicit ec: ExecutionContext) extends AbstractController(cc) {
  
  implicit val userFormat: Format[User] = Json.format[User]
  
  def listUsers = Action.async { implicit request =>
    Future {
      db.withConnection { implicit conn =>
        val repository = new UserRepository()
        val users = repository.findAll()
        Ok(Json.toJson(users))
      }
    }
  }
  
  def getUser(id: Int) = Action.async { implicit request =>
    Future {
      db.withConnection { implicit conn =>
        val repository = new UserRepository()
        repository.findById(id) match {
          case Some(user) => Ok(Json.toJson(user))
          case None => NotFound(Json.obj("error" -> "User not found"))
        }
      }
    }
  }
  
  def createUser = Action.async(parse.json) { implicit request =>
    request.body.validate[CreateUserRequest] match {
      case JsSuccess(data, _) =>
        Future {
          db.withConnection { implicit conn =>
            val repository = new UserRepository()
            val userId = repository.createUser(data.name, data.email, data.age)
            Created(Json.obj("id" -> userId))
          }
        }
      case JsError(errors) =>
        Future.successful(BadRequest(Json.obj("errors" -> errors)))
    }
  }
  
  def searchUsers = Action.async { implicit request =>
    val name = request.getQueryString("name")
    val minAge = request.getQueryString("minAge").map(_.toInt)
    val maxAge = request.getQueryString("maxAge").map(_.toInt)
    
    Future {
      db.withConnection { implicit conn =>
        val repository = new UserRepository()
        val users = repository.searchUsers(name, minAge, maxAge)
        Ok(Json.toJson(users))
      }
    }
  }
}

case class CreateUserRequest(
  name: String,
  email: String,
  age: Option[Int]
)

// Type-safe query builder
class TypeSafeQueryBuilder {
  import Users._
  import Posts._
  
  // Detect type errors at compile time
  def compiletimeTypeCheck(): Unit = {
    // This is a compile error (type mismatch)
    // val invalid = select(Users.name)
    //   .from(Users)
    //   .where(Users.name === 123) // Cannot assign number to String type
    
    // This is correct
    val valid = select(Users.name)
      .from(Users)
      .where(Users.age > 18)
    
    // JOIN type safety
    val joinQuery = select(Users.name, Posts.title)
      .from(Users)
      .innerJoin(Posts).on(Users.id === Posts.authorId)
    // .on(Users.name === Posts.authorId) // Compile error
  }
  
  // Automatic prepared statement generation
  def preparedStatements(name: String, minAge: Int): Unit = {
    // SQL injection impossible
    val query = select(all)
      .from(Users)
      .where(Users.name === name)
      .where(Users.age >= minAge)
    
    // Generated SQL:
    // SELECT * FROM users WHERE name = ? AND age >= ?
    // Parameters are automatically bound
  }
}