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.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 22val 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:
|
|
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
Iterableinterface can be lifted as aQueryusingliftQuery(myIterable).
The lifted queries have two main usages:
- contains:
liftedQuery.containsis mapped toINclause in SQL - batch action:
liftedQuery.foreach. In insert, it is mapped toINSERT 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:
|
|
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.
|
|
Quill supports nested 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. 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
|
|
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.
|
|
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:
|
|
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:
|
|
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:
|
|
6.3 Raw SQL
Use infix to define raw SQL as the following:
|
|
The result is a regular Scala tuple. The query is nested because Quill doesn’t have a query tree.
6.4 Database Functions
|
|
6.5 Comparison Operators and Batch
|
|
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:
|
|
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 ofSchemaMetaforquery[T].insertMeta[T]: create an instnace ofInsertMetaforquery[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:
|
|
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.