This is a study note of Quill: a query language DSL, named Quoted Domain Specific Lnaguage (QDSL), that supports SQL and Cassandra Query Language(CQL). It supports simple schema mapping by case classes and provides compile time query generation and validation.

1 Introduction

Quotations are created using the ctx.quote method and can contain any code that uses supported operations. First create a context instance before create any quotations.

  • A quotation can be a simple value: val pi = quote(3.14).
  • A quoation can be used in another quotations: val area = quote { query[Circle].map(c => pi * c.radius * c.radius)}.
  • A quotation can contain high-order functions: val area = quote { query[Circle].map(c => myQuotedMethod(c))}.
  • Scala doesn’t support high-order functions with type parameters, use a metho type paramter.
  • Quotations can be both compile-time and runtime values. Avoide explicit types to let Quill use compile time value. Quill falls back to runtime vaule if the quotation’s AST can’t be read at compile-time.
  • Quoting is implicit when writing a query in a run statment: ctx.run(query[Circle].map(_.radius)).

Async pg support is in Quill NDBC Postgres.

1.1 Lifted Values

Quotations are self-contained, without references to runtime value outside their scope. There are two ways to bind runtime values to a quotation exection:

  • Lifted Values: use lift(val) to make a runtime value usable in a quotation.
  • Lifted queries: an Iterable interface can be lifted as a Query using liftQuery(myIterable). It can be used in liftedQuery.contains or liftedQuery.foreach.

1.2 Schema

Quill use case class and filed names as the database identifers. The table names and columns can be customized as querySchema[Circle]("circle_table", _.radius -> "radius_column").

If multiple tables require custom identifiers, it is recommended to define a schema object. Following is an example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
case class Circle(radius: Int)
case class Rectangle(length: Int, width: Int)
object schema {
  val circles = quote {
    querySchema[Circle](
        "circle_table",
        _.radius -> "radius_column")
  }
  val rectangles = quote {
    querySchema[Rectangle](
        "rectangle_table",
        _.length -> "length_column",
        _.width -> "width_column")
  }
}

1.3 Db-Generated Values

Use .returningGenerated(r => (id, sku)) to return a tuple of generated values from an insert query. MySql only supports single value return. For non-generated value, use .returning with manual specification of insert columns or a insert-meta such as implicit val productInsertMeta = insertMeta[Product](_.id).

In pg, the returning and returningGenerated support arithmetic operations, SQL UDF and even entire quires in the returning. Queries used inside of return clauses only return a single row per insert - use aggregators such as max or min to avoid errors.

1.4 Embedded Case Classes

Quill supports embedded case classes. The embeded case class extends Embedded. The shema can be customized.

2 Queries

Quill abstracts db tables as in-memory collections. Use for-comprehensions for table join. Qulill normalizes the quotation as SQL join to avoid nested query. The query methods are: filter, map, flatMap, concatMap (similar as flatMap for traversable result), sortBy (with Ord.asc, Ord.desc, Ord.descNullslast etc.), drop, take, groupBy, union, unionAll, aggretations (min, max, avg, sum, size), isEmpty, nonEmpty, contains, distinct, join (applicative, implicit, flat),,,

  • optional values
  • ad-hoc case class
  • query probing

3 Actions

4 IO Monad

5 Dynamic Queries

If quieres structure are only determined at runtime, Quill use runtime query generation. Quill porvides a set of dynamic query APIs for dynamic queries. There are three methods to create dynamic queries:

  • dynamicQuery[T]
  • dynamicQuerySchema[T]
  • quote.dynamic

6 Extending Quill

6.1 Infix

Inflix provides a mechanism to use non-supported features. For example:

1
2
3
4
5
6
7
8
9
implicit class ForUpdate[T](q: Query[T]) {
  def forUpdate = quote(infix"$q FOR UPDATE".as[Query[T]])
}

val a = quote {
  query[Person].filter(p => p.age < 18).forUpdate
}

ctx.run(a)

Quill doesn’t flat the infix query.

6.2 Dynamic Infix

Dynamic infix is defined via #$, for example:

1
2
def test(functionName: String) =
  ctx.run(query[Person].map(p => infix"#$functionName(${p.name})".as[Int]))

6.3 Raw SQL

Use infix to define raw SQL as the following:

1
2
3
4
5
val rawQuery = quote {
  (id: Int) => infix"""SELECT id, name FROM my_entity WHERE id = $id""".as[Query[(Int, String)]]
}
ctx.run(rawQuery(1))
//SELECT x._1, x._2 FROM (SELECT id AS "_1", name AS "_2" FROM my_entity WHERE id = 1) x

The result is nested because Quill doesn’t have a query tree.

6.4 Database Functions

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
val myFunction = quote {
  (i: Int) => infix"MY_FUNCTION($i)".as[Int]
}

val q = quote {
  query[Person].map(p => myFunction(p.age))
}

ctx.run(q)
// SELECT MY_FUNCTION(p.age) FROM Person p

6.5 Comparison Operators and Batch

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
// compare date
import java.util.Date

implicit class DateQuotes(left: Date) {
  def >(right: Date) = quote(infix"$left > $right".as[Boolean])

  def <(right: Date) = quote(infix"$left < $right".as[Boolean])
}

// batch insert
implicit class OnDuplicateKeyIgnore[T](q: Insert[T]) {
  def ignoreDuplicate = quote(infix"$q ON DUPLICATE KEY UPDATE id=id".as[Insert[T]])
}

ctx.run(
  liftQuery(List(
    Person(1, "Test1", 30),
    Person(2, "Test2", 31)
  )).foreach(row => query[Person].insert(row).ignoreDuplicate)
)

6.6 Custom Encoding

  • Mapped encoding: for db-supported types
  • Raw encoding: for non-db-supported types

Quill automatically encodes AnyVal value classes. For example:

1
2
3
4
5
6
7
8
case class UserId(value: Int) extends AnyVal
case class User(id: UserId, name: String)

val q = quote {
  for {
    u <- query[User] if u.id == lift(UserId(1))
  } yield u
}

4.6 Meta DSL

  • schema meta
  • insert meta
  • update meta
  • query meta

7 Contexts

8 Code Generation

The Code Generator Doc.

Import lib: libraryDependencies += "io.getquill" %% "quill-codegen-jdbc" % "3.1.1-SNAPSHOT".

Code sample:

1
2
3
4
val gen = new SimpleJdbcCodegen(snakecaseConfig, "com.my.project") {
    override def nameParser = SnakeCaseNames
}
gen.writeFiles("src/main/scala/com/my/project")

9 Logging

Compile time: -Dquill.macro.log=false, -Dquill.macro.log.pretty=true Runtime: Dquill.binds.log=true -jar myapp.jar