Scala 数据库访问：Slick、Doobie 与 Quill 类型安全 SQL

import com.zaxxer.hikari.HikariConfig
import com.zaxxer.hikari.HikariDataSource

val cfg = new HikariConfig()
cfg.setJdbcUrl("jdbc:postgresql://localhost:5432/app")
cfg.setUsername("app")
cfg.setPassword("secret")
cfg.setMaximumPoolSize(10)          // 核心：并行查询量上限
cfg.setMinimumIdle(2)
cfg.setConnectionTimeout(5000)
cfg.setPoolName("main-pool")

val ds: HikariDataSource = new HikariDataSource(cfg)

-- db/migration/V1__create_users.sql
CREATE TABLE users (
  id        BIGSERIAL PRIMARY KEY,
  name      VARCHAR(100) NOT NULL,
  email     VARCHAR(255) UNIQUE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

import org.flywaydb.core.Flyway

Flyway.configure()
  .dataSource(ds)
  .locations("classpath:db/migration")
  .load()
  .migrate()

import slick.jdbc.PostgresProfile.api._

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 * = (id, name, email) <> (User.tupled, User.unapply)
}

val users = TableQuery[Users]   // 可组合的查询对象

// 全部用户
val all: Query[Users, User, Seq] = users

// 过滤 + 排序 + 分页
val adults: Query[Users, User, Seq] =
  users.filter(_.id > 0).sortBy(_.id.desc).take(20)

// 执行
val rows: Seq[User] = db.run(adults.result).futureValue

val tx = for {
  inserted <- (users returning users.map(_.id)) += User(0, "Alice", "a@x.com")
  _        <- users.filter(_.id === inserted).map(_.name).update("Alice2")
} yield inserted

val id: Long = db.run(tx.transactionally).futureValue

import doobie._
import doobie.implicits._
import cats.effect.IO

// 定义类型映射
case class User(id: Long, name: String, email: String)
implicit val userRead: Read[User] = Read[(Long, String, String)].map(User.tupled)

// 查询：字符串 + 返回类型
val find: ConnectionIO[Option[User]] =
  sql"select id, name, email from users where id = ${1L}"
    .query[User].option

// 执行
val program: IO[Option[User]] = transactor.trans.apply(find)

// 更复杂的查询
val search: ConnectionIO[List[User]] =
  sql"""select id, name, email from users
        where name ilike ${"%" + q + "%"} order by id"""
    .query[User].to[List]

val cond: Fragment = if (active) fr"and active = true" else Fragment.empty
val stmt: Fragment = fr"select id, name from users where 1=1" ++ cond

import io.getquill.{PostgresZioJdbcContext, Snakify}

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

object Ctx extends PostgresZioJdbcContext(Snakify)

// 编译期翻译成: select id, name, email from users order by id desc limit ?
val query: Quoted[Query[User]] =
  quote { query[User].filter(_.id > 0).sortBy(_.id).take(20) }

val rows: Task[List[User]] =
  Ctx.run(query).provideLayer(dbLayer)

val moneyTransfer: ConnectionIO[Unit] = for {
  _ <- sql"update accounts set balance = balance - 100 where id = 1".update.run
  _ <- sql"update accounts set balance = balance + 100 where id = 2".update.run
} yield ()

// transact 保证整个程序在一个事务里
moneyTransfer.transact(transactor)

// Slick
val page = users.filter(_.name.startsWith(prefix))
  .sortBy(_.id).drop(offset).take(limit)

// Doobie
val pageSql = sql"""select id, name from users
                    order by id limit $limit offset $offset"""

// Quill
quote { query[User].sortBy(_.id).drop(lift(offset)).take(lift(limit)) }

-- 用上次的 id 做游标
select id, name from users
where id > ${lastId} order by id limit ${pageSize};

// Doobie 统计
val counts = sql"select status, count(*) from users group by status"
  .query[(String, Int)].to[List]

// Slick 聚合（类型安全）
val avgAge = users.map(_.id).avg  // Query[Rep[Option[Double]], ...]

class Orders(tag: Tag) extends Table[Order](tag, "orders") {
  def id = column[Long]("id", O.PrimaryKey)
  def userId = column[Long]("user_id")
  def amount = column[BigDecimal]("amount")
}

val orders = TableQuery[Orders]

// inner join
val userOrders: Query[(Users, Orders), (User, Order), Seq] =
  users.join(orders).on(_.id === _.userId)

case class UserOrder(userId: Long, userName: String, amount: BigDecimal)

val joined: ConnectionIO[List[UserOrder]] =
  sql"""
    select u.id, u.name, o.amount
    from users u join orders o on o.user_id = u.id
    where u.id = ${1L}
  """.query[UserOrder].to[List]

// 坏：N 次查询
val bad: List[UserOrder] = userIds.map(id => queryOne(id))

// 好：一次 IN 查询
val good: List[UserOrder] =
  sql"select * from orders where user_id in (${userIds})".query[UserOrder].to[List]

// Doobie 批量插入
val batchInsert: ConnectionIO[Int] =
  Update[User]("insert into users(name, email) values(?, ?)")
    .updateMany(userList)     // 一条语句多组参数

// Slick 批量
val n: Int = db.run(users ++= List(user1, user2, user3)).futureValue