takezoe / tranquil

Type-safe SQL builder for Scala

GitHub

Tranquil Build Status Maven Central

Tranquil an experiment of type-safe SQL builder for Scala.

libraryDependencies += "com.github.takezoe" %% "tranquil" % "1.0.2"

Usage

First, ready table definitions like following:

import com.github.takezoe.tranquil._
import java.sql.ResultSet

case class User(userId: String, userName: String, companyId: Option[Int])

class Users extends TableDef[User]("USERS") {
  val userId    = new Column[String](this, "USER_ID")
  val userName  = new Column[String](this, "USER_NAME")
  val companyId = new OptionalColumn[Int](this, "COMPANY_ID")

  override def toModel(rs: ResultSet): User = {
    User(userId.get(rs), userName.get(rs), companyId.get(rs))
  }
}

case class Company(companyId: Int, companyName: String)

class Companies extends TableDef[Company]("COMPANIES") {
  val companyId   = new Column[Int](this, "COMPANY_ID")
  val companyName = new Column[String](this, "COMPANY_NAME")

  override def toModel(rs: ResultSet): Company = {
    Company(companyId.get(rs), companyName.get(rs))
  }
}

object Tables {
  val Users = new SingleTableAction[Users, User](new Users())
  val Companies = new SingleTableAction[Companies, Company](new Companies())
}

Then you can assemble SQL using type-safe DSL.

import com.github.takezoe.tranquil._
import com.github.takezoe.tranquil.Dialect.generic
import Tables._

val conn: java.sql.Connection = ...

// SELECT
val users: Seq[(User, Option[Company])] =
  Users
    .leftJoin(Companies){ case u ~ c => u.companyId eq c.companyId }
    .filter { case u ~ c => (u.userId eq "takezoe") || (u.userId eq "takezoen") }
    .sortBy { case u ~ c => u.userId asc }
    .list(conn)

Generated SQL is:

SELECT
  u.USER_ID      AS u_USER_ID,
  u.USER_NAME    AS u_USER_NAME,
  u.COMPANY_ID   AS u_COMPANY_ID,
  c.COMPANY_ID   AS c_COMPANY_ID,
  c.COMPANY_NAME AS c_COMPANY_NAME
FROM USERS u
LEFT JOIN COMPANIES c ON u.COMPANY_ID = c.COMPANY_ID
WHERE (u.USER_ID = ? OR u.USER_ID = ?)
ORDER BY u.USER_ID ASC

Grouping and aggregation are possible as follows:

val counts: Seq[(Option[Int], Long)] = 
  Users
    .groupBy { t => t.companyId ~ t.userId.count }
    .filter  { case companyId ~ count => count ge 10 }
    .list(conn)

Also you can assemble insert, update and delete SQL in the same way.

// INSERT
Users
  .insert { u => 
    (u.userId -> "takezoe") ~ 
    (u.userName -> "Naoki Takezoe")
  }
  .execute(conn)

// UPDATE
Users
  .update(_.userName -> "N. Takezoe")
  .filter(_.userId eq "takezoe")
  .execute(conn)

// DELETE
Users
  .delete()
  .filter(_.userId eq "takezoe")
  .execute(conn)

Code generator

Tranquil has a code generator to generate case classes and table definitions like explained above from database schema. You can run the code generator as follows:

import com.github.takezoe.tranquil.codegen._

CodeGenerator.generate(Settings(
  url       = "jdbc:h2:mem:test;TRACE_LEVEL_FILE=4",
  driver    = "org.h2.Driver",
  username  = "sa",
  password  = "sa"
))

Source files would be generated into /src/main/scala/models/Tables.scala in default.

In addition, you can configure the code generator via Settings which is passed to CodeGenerator. Settings has following properties:

property type description
driver String JDBC driver classname (required)
url String JDBC connection url (required)
username String JDBC connection username (required)
password String JDBC connection password (required)
catalog String catalog (default is "%")
schemaPattern String schema pattern (default is "%")
tablePattern String table pattern (default is "%")
includeTablePattern String regular expression which matches included tables (default is "")
excludeTablePattern String regular expression which matches excluded tables (default is "")
packageName String package name of generated source (default is "models")
targetDir File output directory of generated source (default is new File("src/main/scala"))
charset String chaarset of generated source (default is "UTF-8")
typeMappings Map[Int, String] mappings of SQL type to Scala type (default is DataTypes.defaultMappings)