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

A context, likes a compiler, provides all the types, methods, and encoder/decoders for writing so-called quotations. The context creates and runs quotations to generate target language. Different contexts have different types and methods. Why Quill ?

  • Compile-time query generation
  • Quoted DSL allows easy composition that makes code more readable.
  • compile-time query normalization for better performance
  • Compile-time validation by query probing
  • More active contributors and recent commits

The first thing in Quill is to create a context. The SqlMirrorContext is used to generate SQL statements. Scastie is a great tool to try out Quill.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
mport io.getquill._

val ctx = new SqlMirrorContext(PostgresDialect, SnakeCase)
import ctx._

case class Person(name: String, age: Int)

val m = ctx.run(query[Person].filter(p => p.name == "John"))
println(m.string)

// output: SELECT p.name, p.age FROM person p WHERE p.name = 'John'

1.1 Quotation

Quill creates, composes, and manages quotations.

  • A quotation can be a simple value.
  • A quotation can be used in another quotations.
  • A quotation can define functions and inline values.

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    
    val pi = quote(3.14159) // simple value
    
    case class Circle(radius: Float)
    val areas1 = quote {
    query[Circle].map(c => pi * c.radius * c.radius)  // use a quoation in another quoation
    }
    
    val area = quote {
    (c: Circle) => {  // high-order function
    val r2 = c.radius * c.radius  // inline value
    pi * r2
    }
    }
    
    val areas2 = quote {
    query[Circle].map(c => area(c))
    }
    
    // both versions of areas will be normalized as
    val areas = quote {
    query[Circle].map(c => 3.14159 * c.radius * c.radius)
    }

The query[Circle] method has a type of Query[Circle]. It represents the actual table/view being selected. Scala doesn’t support high-order functions with type parameters, use a method type paramter. For example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
def existsAny[T] = quote {
  (xs: Query[T]) => (p: T => Boolean) =>
        xs.filter(p(_)).nonEmpty
}

val q = quote {
  query[Circle].filter { c1 =>
    existsAny(query[Circle])(c2 => c2.radius > c1.radius)
  }
}

Quotations are both compile-time and runtime values. Quill uses a type refinement to store the quotation’s AST as an annotation avilable at compile-time and the uses q.ast at runtime.Avoid giving explicit types to quotation when possible to make the type refinement work. 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)).

1.2 Bindings

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

  • 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).

The lifted queries have two main usages:

  • contains: liftedQuery.contains is mapped to IN clause in SQL
  • batch action: liftedQuery.foreach. In insert, it is mapped to INSERT INTO Circle (radius) VALUES (?)

1.3 Schema

Quill uses case classes to represent db schema. Class names and field names are db identifers. The table names and columns can be customized as quote { querySchema[Circle]("circle_table", _.radius -> "radius_column") }. The Quill method signature is def querySchema[T](entity: String, columns: (T => (Any, String))*): EntityQuery[T].

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")
  }
}

Use .returningGenerated(_.id) to return a generated value. Use .returningGenerated(r => (r.id, r.sku)) to return a tuple of generated values from an insert query. returningGenerated excludes specified columns from the insertion. MySql only supports single value return.

For non-generated value, use .returning. The returning and returningGenerated methods also support arithmetic operations and UDF.

If use .returning to return generated columns, specify column values in insertion. for example: query[Product].insert(_.description -> "My Product", _.sku -> 1011L))).returning(r => (r.id, r.description)). Another method is to use insertMeta to exclude columns in insert. For example, 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
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
// INSERT INTO Product (description, sku) VALUES (?, ?) RETURNING myUdf(id)
case class Supplier(id: Int, clientSku: Long)

val q = quote {
  query[Product].insert(_.description -> "My Product", _.sku -> 1011L)
}

val udf = quote { (i: Long) => infix"myUdf($i)".as[Int] }
ctx.run(q.returning(r => udf(id))) //: List[Int]

// INSERT INTO Product (description,sku) VALUES ('My Product', 1011)
// RETURNING (SELECT MAX(s.id) FROM Supplier s WHERE s.sku = clientSku)
ctx.run {
  q.returning(r => query[Supplier].filter(s => s.sku == r.sku).map(_.id).max)
} //: List[Option[Long]]

// INSERT INTO Product (description,sku) VALUES ('My Product', 1011)
// RETURNING id + 100, myUdf(id), (SELECT MAX(s.id) FROM Supplier s WHERE s.sku = sku)
ctx.run {
  q.returning(r =>
    (r.id + 100, udf(r.id), query[Supplier].filter(s => s.sku == r.sku).map(_.id).max)
  )
}

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

1
2
3
4
5
case class Contact(phone: String, address: String) extends Embedded
case class Person(id: Int, name: String, contact: Contact)

// SELECT x.id, x.name, x.phone, x.address FROM Person x
ctx.run(query[Person])

2 Queries

Quill abstracts db tables as in-memory collections. Use for-comprehensions for table join. Quill nomralizes the quotation and translates the monadic joins (flatMap) to application joins (def product[A, B](fa: F[A], fb: F[B]): F[(A, B)]). The normalization avoids the nested queries.

2.1 Query Methods

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
case class Person(id: Int, name: String, age: Int)
case class Contact(personId: Int, phone: String)

val q = quote {
  for {
    p <- query[Person] if (p.id == 99)
    q <- query[Contact] if (c.personId == p.id)
  } yield {
    (p.name, c.phone)
  }
}
// SELECT p.name, c.phone FROM Person p, Contact c
// WHERE (p.id = 999) AND (c.personId = p.id)
ctx.run(q)

// same as
val q = quote {
  query[Person].filter(p => p.id == 99).flatMap(
    p => query[Contact].filter(c => c.personId == p.id).map(c => (p.name, c.phone)))
}

The query methods are: filter, map, flatMap, concatMap (use traversalbe instead of Query, generated UNNEST for the traversable values), sortBy (with Ord.asc, Ord.desc, Ord.descNullslast etc.), drop, take, groupBy, union (only distinct values), unionAll or ++ (allow duplicates), aggretations (min, max, avg, sum, size), isEmpty, nonEmpty, contains, distinct, nested, join (applicative, implicit, flat),,,.

2.2 Joins

An important concept is that inner join returns a tuple of both tables where left/right join return a table and an option table.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
case class A(id: Int)
case class B(fk: Int)

// Applicative Join: common for two tables
// inner join
val q = quote { query[A].join(query[B]).on(_.id == _.fk) }

// Left outer Join
val q = quote {
  query[Person].leftJoin(query[Address]).on((p, a) => p.id == a.fk)
}

// Right outer Join
val q = quote {
  query[Person].rightJoin(query[Address]).on((p, a) => p.id == a.fk)
}

// Full outer Join
val q = quote {
  query[Person].fullJoin(query[Address]).on((p, a) => p.id == a.fk)
}

// All is well for two tables but for three or more, the nesting mess begins:
val q = quote {
  query[Person]
    .join(query[Address]).on({case (p, a) => p.id == a.fk}) // Let's use `case` here to stay consistent
    .join(query[Company]).on({case ((p, a), c) => a.zip == c.zip})
}

// Implicity join: monadic syntax
quote {
  for {
    a <- query[A]
    b <- query[B] if (a.id == b.fk)
  } yield (a, b)
}

//adding more joins without having to do any pesky nesting.
val q = quote {
  for {
    p <- query[Person]
    a <- query[Address] if (p.id == a.fk)
    c <- query[Address] if (c.zip == a.zip)
  } yield (p, a, c)
}


// Flat join, supports inner, right and left joins without nesting
quote {
  for {
    a <- query[A]
    b <- query[B].join(_.fk == a.id)
  }
}

val q = quote {
  for {
    p <- query[Person]
    a <- query[Address].join(a => a.fk == p.id)
    c <- query[Company].leftJoin(c => c.zip == a.zip)
  } yield (p,a,c)
}

2.3 Optional Values

Nullable columns in tables are Option types in Scala. Quill relies on the null-fallthrough behavior of ANSI standard. use isDefined or isEmpty to check null values. Use exists in a join condition. To accept null in join, use forall in join. Use map for optional values in leftJion or rightJoin. For example:

1
2
3
4
5
6
7
val q = quote {
  query[Company].leftJoin(query[Address])
    .on((c, a) => c.zip == a.zip)
    .map {case(c,a) =>                          // Row type is (Company, Option[Address])
      (c.name, a.map(_.street), a.map(_.zip))   // Use `Option.map` to get `street` and `zip` fields
    }
}

For [Option[Option[_]]], use flatMap or flatten.

Use [Option[T].orNull method to convert an option-enclosed row back into a regular row. For primitive types, use getOrNull.

The ==, != and .equals follow the scala-idomatic way. To use the SQL-idiomatic way, import ctx.extras._.

2.4 Ad-hoc case class

Case classes can be used as output values.

2.5 Query Probing

Query probing validates queriies against the database at compile time, failing the compilation if it is not valid. The query validation does not alter the database.

To enable it, mix QueryProbing trait to the database configuration. The context must be created in a separate compilation unit in order to be loaded at compile time.

3 Dynamic Queries

If quiery’s structures are only determined at runtime, Quill uses 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

4 Extending Quill

4.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. Don’t use it in multiple map and filter. Use .pure to give Quil hints for flatten.

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 a regular Scala tuple. The query 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

Quill uses Encoder to encode query inputs and Decodere to read values returned by queriries. Use mapped encoding and raw encoding to extend it.

  • 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

The meta DSL customizes how Quill handles the expansion and execution of quotations through implicit meta instance.

  • schemaMeta[T]: create an instance of SchemaMeta for query[T].
  • insertMeta[T]: create an instnace of InsertMeta for query[T].insert(t) to exclude columns.
  • updateMeta[T]: exclude columns in update.
  • queryMeta: custom expansion of query types and extraction of the final value.

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 Others

9.1 Logging

To log generated SQL statements, use <logger name="io.getquill.context.jdbc.JdbcContext" level="DEBUG"/> in logback.xml.

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

9.2 Drivers

Async pg support is in Quill NDBC Postgres – it is new and may not have strong support in the long run.

9.3 Print Queries

The ctx.translate(q) converts a query into a string.