Tranquil

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)

takezoe / tranquil 1.0.2

Tranquil

Usage

Code generator

Statistics

2 Dependencies

No Dependent