Want to take your software engineering career to the next level? Join the mailing list for career tips & advice Click here

quill

Compile-time Language Integrated Queries for Scala

Star full 4f7b624809470f25b6493d5a7b30d9b9cb905931146e785d67c86ef0c205a402Star full 4f7b624809470f25b6493d5a7b30d9b9cb905931146e785d67c86ef0c205a402Star full 4f7b624809470f25b6493d5a7b30d9b9cb905931146e785d67c86ef0c205a402Star blank 374f33e4d622a2930833db3cbea26b5d03dc44961a6ecab0b9e13276d97d6682Star blank 374f33e4d622a2930833db3cbea26b5d03dc44961a6ecab0b9e13276d97d6682 (1 ratings)
Rated 3.0 out of 5
Subscribe to updates I use quill


Statistics on quill

Number of watchers on Github 1698
Number of open issues 253
Average time to close an issue 13 days
Main language Scala
Average time to merge a PR 1 day
Open pull requests 76+
Closed pull requests 36+
Last commit 4 months ago
Repo Created over 5 years ago
Repo Last Updated 3 months ago
Size 8.83 MB
Homepage https://getquill.io
Organization / Authorgetquill
Contributors6
Page Updated
Do you use quill? Leave a review!
View open issues (253)
View quill activity
View on github
Book a Mock Interview With Me (Silicon Valley Engineering Leader, 100s of interviews conducted)
Software engineers: It's time to get promoted. Starting NOW! Subscribe to my mailing list and I will equip you with tools, tips and actionable advice to grow in your career.
Evaluating quill for your project? Score Explanation
Commits Score (?)
Issues & PR Score (?)
What people are saying about quill Leave a review
I can't get it to work with case classes used type "with multiple parameters" in a Cassandra table

IMPORTANT: This is the documentation for the latest SNAPSHOT version. Please refer to the website at http://getquill.io for the latest release's documentation.

quill

Compile-time Language Integrated Query for Scala

Build Status Codacy Badge codecov.io Join the chat at https://gitter.im/getquill/quill Maven Central Javadocs

Quill provides a Quoted Domain Specific Language (QDSL) to express queries in Scala and execute them in a target language. The library's core is designed to support multiple target languages, currently featuring specializations for Structured Query Language (SQL) and Cassandra Query Language (CQL).

example

  1. Boilerplate-free mapping: The database schema is mapped using simple case classes.
  2. Quoted DSL: Queries are defined inside a quote block. Quill parses each quoted block of code (quotation) at compile time and translates them to an internal Abstract Syntax Tree (AST)
  3. Compile-time query generation: The ctx.run call reads the quotation's AST and translates it to the target language at compile time, emitting the query string as a compilation message. As the query string is known at compile time, the runtime overhead is very low and similar to using the database driver directly.
  4. Compile-time query validation: If configured, the query is verified against the database at compile time and the compilation fails if it is not valid. The query validation does not alter the database state.

Note: The GIF example uses Eclipse, which shows compilation messages to the user.

Quotation

Introduction

The QDSL allows the user to write plain Scala code, leveraging Scala's syntax and type system. Quotations are created using the quote method and can contain any excerpt of code that uses supported operations. To create quotations, first create a context instance. Please see the context section for more details on the different context available.

For this documentation, a special type of context that acts as a mirror is used:

import io.getquill._

val ctx = new SqlMirrorContext(MirrorSqlDialect, Literal)

Note: Scastie is a great tool to try out Quill without having to prepare a local environment. It works with mirror contexts, see this snippet as an example.

The context instance provides all the types, methods, and encoders/decoders needed for quotations:

import ctx._

A quotation can be a simple value:

val pi = quote(3.14159)

And be used within another quotation:

case class Circle(radius: Float)

val areas = quote {
  query[Circle].map(c => pi * c.radius * c.radius)
}

Quotations can also contain high-order functions and inline values:

val area = quote {
  (c: Circle) => {
    val r2 = c.radius * c.radius
    pi * r2
  }
}
val areas = quote {
  query[Circle].map(c => area(c))
}

Quill's normalization engine applies reduction steps before translating the quotation to the target language. The correspondent normalized quotation for both versions of the areas query is:

val areas = quote {
  query[Circle].map(c => 3.14159 * c.radius * c.radius)
}

Scala doesn't have support for high-order functions with type parameters. It's possible to use a method type parameter for this purpose:

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

Compile-time quotations

Quotations are both compile-time and runtime values. Quill uses a type refinement to store the quotation's AST as an annotation available at compile-time and the q.ast method exposes the AST as runtime value.

It is important to avoid giving explicit types to quotations when possible. For instance, this quotation can't be read at compile-time as the type refinement is lost:

// Avoid type widening (Quoted[Query[Circle]]), or else the quotation will be dynamic.
val q: Quoted[Query[Circle]] = quote {
  query[Circle].filter(c => c.radius > 10)
}

ctx.run(q) // Dynamic query

Quill falls back to runtime normalization and query generation if the quotation's AST can't be read at compile-time. Please refer to dynamic queries for more information.

Inline queries

Quoting is implicit when writing a query in a run statement.

ctx.run(query[Circle].map(_.radius))
// SELECT r.radius FROM Circle r

Bindings

Quotations are designed to be self-contained, without references to runtime values outside their scope. There are two mechanisms to explicitly bind runtime values to a quotation execution.

Lifted values

A runtime value can be lifted to a quotation through the method lift:

def biggerThan(i: Float) = quote {
  query[Circle].filter(r => r.radius > lift(i))
}
ctx.run(biggerThan(10)) // SELECT r.radius FROM Circle r WHERE r.radius > ?

Lifted queries

A Iterable instance can be lifted as a Query. There are two main usages for lifted queries:

contains

def find(radiusList: List[Float]) = quote {
  query[Circle].filter(r => liftQuery(radiusList).contains(r.radius))
}
ctx.run(find(List(1.1F, 1.2F))) 
// SELECT r.radius FROM Circle r WHERE r.radius IN (?)

batch action

def insert(circles: List[Circle]) = quote {
  liftQuery(circles).foreach(c => query[Circle].insert(c))
}
ctx.run(insert(List(Circle(1.1F), Circle(1.2F)))) 
// INSERT INTO Circle (radius) VALUES (?)

Schema

The database schema is represented by case classes. By default, quill uses the class and field names as the database identifiers:

case class Circle(radius: Float)

val q = quote {
  query[Circle].filter(c => c.radius > 1)
}

ctx.run(q) // SELECT c.radius FROM Circle c WHERE c.radius > 1

Schema customization

Alternatively, the identifiers can be customized:

val circles = quote {
  querySchema[Circle]("circle_table", _.radius -> "radius_column")
}

val q = quote {
  circles.filter(c => c.radius > 1)
}

ctx.run(q)
// SELECT c.radius_column FROM circle_table c WHERE c.radius_column > 1

If multiple tables require custom identifiers, it is good practice to define a schema object with all table queries to be reused across multiple queries:

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

Database-generated values

returningGenerated

Database generated values can be returned from an insert query by using .returningGenerated. These properties will also be excluded from the insertion since they are database generated.

case class Product(id: Int, description: String, sku: Long)

val q = quote {
  query[Product].insert(lift(Product(0, "My Product", 1011L))).returningGenerated(_.id)
}

val returnedIds = ctx.run(q) //: List[Int]
// INSERT INTO Product (description,sku) VALUES (?, ?) -- NOTE that 'id' is not being inserted.

Multiple properties can be returned in a Tuple or Case Class and all of them will be excluded from insertion.

NOTE: Using multiple properties is currently supported by Postgres, Oracle and SQL Server

// Assuming sku is generated by the database.
val q = quote {
  query[Product].insert(lift(Product(0, "My Product", 1011L))).returningGenerated(r => (id, sku))
}

val returnedIds = ctx.run(q) //: List[(Int, Long)]
// INSERT INTO Product (description) VALUES (?) RETURNING id, sku -- NOTE that 'id' and 'sku' are not being inserted.

returning

In certain situations, we might want to return fields that are not auto generated as well. In this case we do not want the fields to be automatically excluded from the insertion. The returning method is used for that.

val q = quote {
  query[Product].insert(lift(Product(0, "My Product", 1011L))).returning(r => (id, description))
}

val returnedIds = ctx.run(q) //: List[(Int, String)]
// INSERT INTO Product (id, description, sku) VALUES (?, ?, ?) RETURNING id, description

Wait a second! Why did we just insert id into the database? That is because returning does not exclude values from the insertion! We can fix this situation by manually specifying the columns to insert:

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

val returnedIds = ctx.run(q) //: List[(Int, String)]
// INSERT INTO Product (description, sku) VALUES (?, ?) RETURNING id, description

We can also fix this situation by using an insert-meta.

implicit val productInsertMeta = insertMeta[Product](_.id)
val q = quote {
  query[Product].insert(lift(Product(0L, "My Product", 1011L))).returning(r => (id, description))
}

val returnedIds = ctx.run(q) //: List[(Int, String)]
// INSERT INTO Product (description, sku) VALUES (?, ?) RETURNING id, description

returning can also be used after update:

val q = quote {
  query[Product].update(lift(Product(42, "Updated Product", 2022L))).returning(r => (r.id, r.description))
}

val updated = ctx.run(q) //: List[(Int, String)]
// UPDATE Product SET id = ?, description = ?, sku = ? RETURNING id, description

or even after delete:

val q = quote {
  query[Product].delete.returning(r => (r.id, r.description))
}

val deleted = ctx.run(q) //: List[(Int, String)]
// DELETE FROM Product RETURNING id, description

Customization

Postgres

The returning and returningGenerated methods also support arithmetic operations, SQL UDFs and even entire queries. These are inserted directly into the SQL RETURNING clause.

Assuming this basic query:

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

Add 100 to the value of id:

ctx.run(q.returning(r => id + 100)) //: List[Int]
// INSERT INTO Product (description, sku) VALUES (?, ?) RETURNING id + 100

Pass the value of id into a UDF:

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 (?, ?) RETURNING myUdf(id)

Use the return value of sku to issue a query:

case class Supplier(id: Int, clientSku: Long)
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 (SELECT MAX(s.id) FROM Supplier s WHERE s.sku = clientSku)

As is typically the case with Quill, you can use all of these features together.

ctx.run {
  q.returning(r => 
    (r.id + 100, udf(r.id), query[Supplier].filter(s => s.sku == r.sku).map(_.id).max)
  ) 
} // List[(Int, Int, 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)

NOTE: Queries used inside of return clauses can only return a single row per insert. Otherwise, Postgres will throw: ERROR: more than one row returned by a subquery used as an expression. This is why is it strongly recommended that you use aggregators such as max or mininside of quill returning-clause queries. In the case that this is impossible (e.g. when using Postgres booleans), you can use the .value method: q.returning(r => query[Supplier].filter(s => s.sku == r.sku).map(_.id).value).

SQL Server

The returning and returningGenerated methods are more restricted when using SQL Server; they only support arithmetic operations. These are inserted directly into the SQL OUTPUT INSERTED.* or OUTPUT DELETED.* clauses.

Assuming the query:

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

Add 100 to the value of id:

ctx.run(q.returning(r => id + 100)) //: List[Int]
// INSERT INTO Product (description, sku) OUTPUT INSERTED.id + 100 VALUES (?, ?)

Update returning:

val q = quote {
  query[Product].update(_.description -> "Updated Product", _.sku -> 2022L).returning(r => (r.id, r.description))
}

val updated = ctx.run(q)
// UPDATE Product SET description = 'Updated Product', sku = 2022 OUTPUT INSERTED.id, INSERTED.description

Delete returning:

val q = quote {
  query[Product].delete.returning(r => (r.id, r.description))
}

val updated = ctx.run(q)
// DELETE FROM Product OUTPUT DELETED.id, DELETED.description

Embedded case classes

Quill supports nested Embedded case classes:

case class Contact(phone: String, address: String) extends Embedded
case class Person(id: Int, name: String, contact: Contact)

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

Note that default naming behavior uses the name of the nested case class properties. It's possible to override this default behavior using a custom schema:

case class Contact(phone: String, address: String) extends Embedded
case class Person(id: Int, name: String, homeContact: Contact, workContact: Option[Contact])

val q = quote {
  querySchema[Person](
    "Person",
    _.homeContact.phone          -> "homePhone",
    _.homeContact.address        -> "homeAddress",
    _.workContact.map(_.phone)   -> "workPhone",
    _.workContact.map(_.address) -> "workAddress"
  )
}

ctx.run(q)
// SELECT x.id, x.name, x.homePhone, x.homeAddress, x.workPhone, x.workAddress FROM Person x

Queries

The overall abstraction of quill queries uses database tables as if they were in-memory collections. Scala for-comprehensions provide syntactic sugar to deal with these kinds of monadic operations:

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 == 999)
    c <- query[Contact] if(c.personId == p.id)
  } yield {
    (p.name, c.phone)
  }
}

ctx.run(q)
// SELECT p.name, c.phone FROM Person p, Contact c WHERE (p.id = 999) AND (c.personId = p.id)

Quill normalizes the quotation and translates the monadic joins to applicative joins, generating a database-friendly query that avoids nested queries.

Any of the following features can be used together with the others and/or within a for-comprehension:

filter

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

ctx.run(q)
// SELECT p.id, p.name, p.age FROM Person p WHERE p.age > 18

map

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

ctx.run(q)
// SELECT p.name FROM Person p

flatMap

val q = quote {
  query[Person].filter(p => p.age > 18).flatMap(p => query[Contact].filter(c => c.personId == p.id))
}

ctx.run(q)
// SELECT c.personId, c.phone FROM Person p, Contact c WHERE (p.age > 18) AND (c.personId = p.id)

concatMap

// similar to `flatMap` but for transformations that return a traversable instead of `Query`

val q = quote {
  query[Person].concatMap(p => p.name.split(" "))
}

ctx.run(q)
// SELECT UNNEST(SPLIT(p.name, " ")) FROM Person p

sortBy

val q1 = quote {
  query[Person].sortBy(p => p.age)
}

ctx.run(q1)
// SELECT p.id, p.name, p.age FROM Person p ORDER BY p.age ASC NULLS FIRST

val q2 = quote {
  query[Person].sortBy(p => p.age)(Ord.descNullsLast)
}

ctx.run(q2)
// SELECT p.id, p.name, p.age FROM Person p ORDER BY p.age DESC NULLS LAST

val q3 = quote {
  query[Person].sortBy(p => (p.name, p.age))(Ord(Ord.asc, Ord.desc))
}

ctx.run(q3)
// SELECT p.id, p.name, p.age FROM Person p ORDER BY p.name ASC, p.age DESC

drop/take

val q = quote {
  query[Person].drop(2).take(1)
}

ctx.run(q)
// SELECT x.id, x.name, x.age FROM Person x LIMIT 1 OFFSET 2

groupBy

val q = quote {
  query[Person].groupBy(p => p.age).map {
    case (age, people) =>
      (age, people.size)
  }
}

ctx.run(q)
// SELECT p.age, COUNT(*) FROM Person p GROUP BY p.age

union

val q = quote {
  query[Person].filter(p => p.age > 18).union(query[Person].filter(p => p.age > 60))
}

ctx.run(q)
// SELECT x.id, x.name, x.age FROM (SELECT id, name, age FROM Person p WHERE p.age > 18
// UNION SELECT id, name, age FROM Person p1 WHERE p1.age > 60) x

unionAll/++

val q = quote {
  query[Person].filter(p => p.age > 18).unionAll(query[Person].filter(p => p.age > 60))
}

ctx.run(q)
// SELECT x.id, x.name, x.age FROM (SELECT id, name, age FROM Person p WHERE p.age > 18
// UNION ALL SELECT id, name, age FROM Person p1 WHERE p1.age > 60) x

val q2 = quote {
  query[Person].filter(p => p.age > 18) ++ query[Person].filter(p => p.age > 60)
}

ctx.run(q2)
// SELECT x.id, x.name, x.age FROM (SELECT id, name, age FROM Person p WHERE p.age > 18
// UNION ALL SELECT id, name, age FROM Person p1 WHERE p1.age > 60) x

aggregation

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

ctx.run(r.min) // SELECT MIN(p.age) FROM Person p
ctx.run(r.max) // SELECT MAX(p.age) FROM Person p
ctx.run(r.avg) // SELECT AVG(p.age) FROM Person p
ctx.run(r.sum) // SELECT SUM(p.age) FROM Person p
ctx.run(r.size) // SELECT COUNT(p.age) FROM Person p

isEmpty/nonEmpty

val q = quote {
  query[Person].filter{ p1 =>
    query[Person].filter(p2 => p2.id != p1.id && p2.age == p1.age).isEmpty
  }
}

ctx.run(q)
// SELECT p1.id, p1.name, p1.age FROM Person p1 WHERE
// NOT EXISTS (SELECT * FROM Person p2 WHERE (p2.id <> p1.id) AND (p2.age = p1.age))

val q2 = quote {
  query[Person].filter{ p1 =>
    query[Person].filter(p2 => p2.id != p1.id && p2.age == p1.age).nonEmpty
  }
}

ctx.run(q2)
// SELECT p1.id, p1.name, p1.age FROM Person p1 WHERE
// EXISTS (SELECT * FROM Person p2 WHERE (p2.id <> p1.id) AND (p2.age = p1.age))

contains

val q = quote {
  query[Person].filter(p => liftQuery(Set(1, 2)).contains(p.id))
}

ctx.run(q)
// SELECT p.id, p.name, p.age FROM Person p WHERE p.id IN (?, ?)

val q1 = quote { (ids: Query[Int]) =>
  query[Person].filter(p => ids.contains(p.id))
}

ctx.run(q1(liftQuery(List(1, 2))))
// SELECT p.id, p.name, p.age FROM Person p WHERE p.id IN (?, ?)

val peopleWithContacts = quote {
  query[Person].filter(p => query[Contact].filter(c => c.personId == p.id).nonEmpty)
}
val q2 = quote {
  query[Person].filter(p => peopleWithContacts.contains(p.id))
}

ctx.run(q2)
// SELECT p.id, p.name, p.age FROM Person p WHERE p.id IN (SELECT p1.* FROM Person p1 WHERE EXISTS (SELECT c.* FROM Contact c WHERE c.personId = p1.id))

distinct

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

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

nested

val q = quote {
  query[Person].filter(p => p.name == "John").nested.map(p => p.age)
}

ctx.run(q)
// SELECT p.age FROM (SELECT p.age FROM Person p WHERE p.name = 'John') p

joins

Joins are arguably the largest source of complexity in most SQL queries. Quill offers a few different syntaxes so you can choose the right one for your use-case!

case class A(id: Int)
case class B(fk: Int)

// Applicative Joins:
quote {
  query[A].join(query[B]).on(_.id == _.fk)
}

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

// Flat Joins:
quote {
  for {
    a <- query[A]
    b <- query[B].join(_.fk == a.id)
  } yield (a, b)
}

Let's see them one by one assuming the following schema:

case class Person(id: Int, name: String)
case class Address(street: String, zip: Int, fk: Int)

(Note: If your use case involves lots and lots of joins, both inner and outer. Skip right to the flat-joins section!)

applicative joins

Applicative joins are useful for joining two tables together, they are straightforward to understand, and typically look good on one line. Quill supports inner, left-outer, right-outer, and full-outer (i.e. cross) applicative joins.

// Inner Join
val q = quote {
  query[Person].join(query[Address]).on(_.id == _.fk)
}

ctx.run(q) //: List[(Person, Address)]
// SELECT x1.id, x1.name, x2.street, x2.zip, x2.fk 
// FROM Person x1 INNER JOIN Address x2 ON x1.id = x2.fk

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

ctx.run(q) //: List[(Person, Option[Address])]
// Note that when you use named-variables in your comprehension, Quill does its best to honor them in the query.
// SELECT p.id, p.name, a.street, a.zip, a.fk 
// FROM Person p LEFT JOIN Address a ON p.id = a.fk

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

ctx.run(q) //: List[(Option[Person], Address)]
// SELECT p.id, p.name, a.street, a.zip, a.fk 
// FROM Person p RIGHT JOIN Address a ON p.id = a.fk

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

ctx.run(q) //: List[(Option[Person], Option[Address])]
// SELECT p.id, p.name, a.street, a.zip, a.fk 
// FROM Person p FULL JOIN Address a ON p.id = a.fk

What about joining more than two tables with the applicative syntax? Here's how to do that:

case class Company(zip: Int)

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

ctx.run(q) //: List[((Person, Address), Company)]
// (Unfortunately when you use `case` statements, Quill can't help you with the variables names either!)
// SELECT x01.id, x01.name, x11.street, x11.zip, x11.fk, x12.name, x12.zip 
// FROM Person x01 INNER JOIN Address x11 ON x01.id = x11.fk INNER JOIN Company x12 ON x11.zip = x12.zip

No worries though, implicit joins and flat joins have your other use-cases covered!

implicit joins

Quill's implicit joins use a monadic syntax making them pleasant to use for joining many tables together. They look a lot like Scala collections when used in for-comprehensions making them familiar to a typical Scala developer. What's the catch? They can only do inner-joins.

val q = quote {
  for {
    p <- query[Person]
    a <- query[Address] if (p.id == a.fk)
  } yield (p, a)
}

run(q) //: List[(Person, Address)]
// SELECT p.id, p.name, a.street, a.zip, a.fk 
// FROM Person p, Address a WHERE p.id = a.fk

Now, this is great because you can keep adding more and 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)
}

run(q) //: List[(Person, Address, Company)]
// SELECT p.id, p.name, a.street, a.zip, a.fk, c.name, c.zip 
// FROM Person p, Address a, Company c WHERE p.id = a.fk AND c.zip = a.zip

Well that looks nice but wait! What If I need to inner, and outer join lots of tables nicely? No worries, flat-joins are here to help!

flat joins

Flat Joins give you the best of both worlds! In the monadic syntax, you can use both inner joins, and left-outer joins together without any of that pesky nesting.

// Inner Join
val q = quote {
  for { 
    p <- query[Person]
    a <- query[Address].join(a => a.fk == p.id)
  } yield (p,a)
}

ctx.run(q) //: List[(Person, Address)]
// SELECT p.id, p.name, a.street, a.zip, a.fk
// FROM Person p INNER JOIN Address a ON a.fk = p.id

// Left (Outer) Join
val q = quote {
  for { 
    p <- query[Person]
    a <- query[Address].leftJoin(a => a.fk == p.id)
  } yield (p,a)
}

ctx.run(q) //: List[(Person, Option[Address])]
// SELECT p.id, p.name, a.street, a.zip, a.fk 
// FROM Person p LEFT JOIN Address a ON a.fk = p.id

Now you can keep adding both right and left joins without nesting!

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

ctx.run(q) //: List[(Person, Address, Option[Company])]
// SELECT p.id, p.name, a.street, a.zip, a.fk, c.name, c.zip 
// FROM Person p 
// INNER JOIN Address a ON a.fk = p.id 
// LEFT JOIN Company c ON c.zip = a.zip

Can't figure out what kind of join you want to use? Who says you have to choose?

With Quill the following multi-join queries are equivalent, use them according to preference:


case class Employer(id: Int, personId: Int, name: String)

val qFlat = quote {
  for{
    (p,e) <- query[Person].join(query[Employer]).on(_.id == _.personId)
       c  <- query[Contact].leftJoin(_.personId == p.id)
  } yield(p, e, c)
}

val qNested = quote {
  for{
    ((p,e),c) <-
      query[Person].join(query[Employer]).on(_.id == _.personId)
      .leftJoin(query[Contact]).on(
        _._1.id == _.personId
      )
  } yield(p, e, c)
}

ctx.run(qFlat)
ctx.run(qNested)
// SELECT p.id, p.name, p.age, e.id, e.personId, e.name, c.id, c.phone
// FROM Person p INNER JOIN Employer e ON p.id = e.personId LEFT JOIN Contact c ON c.personId = p.id

Note that in some cases implicit and flat joins cannot be used together, for example, the following query will fail.

val q = quote {
  for {
    p <- query[Person]
    p1 <- query[Person] if (p1.name == p.name)
    c <- query[Contact].leftJoin(_.personId == p.id)
  } yield (p, c)
}

// ctx.run(q)
// java.lang.IllegalArgumentException: requirement failed: Found an `ON` table reference of a table that is 
// not available: Set(p). The `ON` condition can only use tables defined through explicit joins.

This happens because an explicit join typically cannot be done after an implicit join in the same query.

A good guideline is in any query or subquery, choose one of the following:

  • Use flat-joins + applicative joins or
  • Use implicit joins

Also, note that not all Option operations are available on outer-joined tables (i.e. tables wrapped in an Option object), only a specific subset. This is mostly due to the inherent limitations of SQL itself. For more information, see the 'Optional Tables' section.

Optionals / Nullable Fields

Note that the behavior of Optionals has recently changed to include stricter null-checks. See the orNull / getOrNull section for more details.

Option objects are used to encode nullable fields. Say you have the following schema:

CREATE TABLE Person(
  id INT NOT NULL PRIMARY KEY,
  name VARCHAR(255) -- This is nullable!
);
CREATE TABLE Address(
  fk INT, -- This is nullable!
  street VARCHAR(255) NOT NULL,
  zip INT NOT NULL,
  CONSTRAINT a_to_p FOREIGN KEY (fk) REFERENCES Person(id)
);
CREATE TABLE Company(
  name VARCHAR(255) NOT NULL,
  zip INT NOT NULL
)

This would encode to the following:

case class Person(id:Int, name:Option[String])
case class Address(fk:Option[Int], street:String, zip:Int)
case class Company(name:String, zip:Int)

Some important notes regarding Optionals and nullable fields.

In many cases, Quill tries to rely on the null-fallthrough behavior that is ANSI standard:

  • null == null := false
  • null == [true | false] := false

This allows the generated SQL for most optional operations to be simple. For example, the expression Option[String].map(v => v + "foo") can be expressed as the SQL v || 'foo' as opposed to CASE IF (v is not null) v || 'foo' ELSE null END so long as the concatenation operator || falls-through and returns null when the input is null. This is not true of all databases (e.g. Oracle), forcing Quill to return the longer expression with explicit null-checking. Also, if there are conditionals inside of an Option operation (e.g. o.map(v => if (v == "x") "y" else "z")) this creates SQL with case statements, which will never fall-through when the input value is null. This forces Quill to explicitly null-check such statements in every SQL dialect.

Let's go through the typical operations of optionals.

isDefined / isEmpty

The isDefined method is generally a good way to null-check a nullable field:

val q = quote {
  query[Address].filter(a => a.fk.isDefined)
}
ctx.run(q)
// SELECT a.fk, a.street, a.zip FROM Address a WHERE a.fk IS NOT NULL

The isEmpty method works the same way:

val q = quote {
  query[Address].filter(a => a.fk.isEmpty)
}
ctx.run(q)
// SELECT a.fk, a.street, a.zip FROM Address a WHERE a.fk IS NULL

exists

This method is typically used for inspecting nullable fields inside of boolean conditions, most notably joining!

val q = quote {
  query[Person].join(query[Address]).on((p, a)=> a.fk.exists(_ == p.id))
}
ctx.run(q)
// SELECT p.id, p.name, a.fk, a.street, a.zip FROM Person p INNER JOIN Address a ON a.fk = p.id

Note that in the example above, the exists method does not cause the generated SQL to do an explicit null-check in order to express the False case. This is because Quill relies on the typical database behavior of immediately falsifying a statement that has null on one side of the equation.

forall

Use this method in boolean conditions that should succeed in the null case.

val q = quote {
  query[Person].join(query[Address]).on((p, a) => a.fk.forall(_ == p.id))
}
ctx.run(q)
// SELECT p.id, p.name, a.fk, a.street, a.zip FROM Person p INNER JOIN Address a ON a.fk IS NULL OR a.fk = p.id

Typically this is useful when doing negative conditions, e.g. when a field is not some specified value (e.g. "Joe"). Being null in this case is typically a matching result.

val q = quote {
  query[Person].filter(p => p.name.forall(_ != "Joe"))
}

ctx.run(q)
// SELECT p.id, p.name FROM Person p WHERE p.name IS NULL OR p.name <> 'Joe'

map

As in regular Scala code, performing any operation on an optional value typically requires using the map function.

val q = quote {
 for {
    p <- query[Person]
  } yield (p.id, p.name.map("Dear " + _))
}

ctx.run(q)
// SELECT p.id, 'Dear ' || p.name FROM Person p
// * In Dialects where `||` does not fall-through for nulls (e.g. Oracle):
// * SELECT p.id, CASE WHEN p.name IS NOT NULL THEN 'Dear ' || p.name ELSE null END FROM Person p

Additionally, this method is useful when you want to get a non-optional field out of an outer-joined table (i.e. a table wrapped in an Option object).

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

run(q)
// SELECT c.name, a.street, a.zip FROM Company c LEFT JOIN Address a ON c.zip = a.zip

For more details about this operation (and some caveats), see the 'Optional Tables' section.

flatMap and flatten

Use these when the Option.map functionality is not sufficient. This typically happens when you need to manipulate multiple nullable fields in a way which would otherwise result in Option[Option[T]].

val q = quote {
  for {
    a <- query[Person]
    b <- query[Person] if (a.id > b.id)
  } yield (
    // If this was `a.name.map`, resulting record type would be Option[Option[String]]
    a.name.flatMap(an =>
      b.name.map(bn => 
        an+" comes after "+bn)))
}

ctx.run(q) //: List[Option[String]]
// SELECT (a.name || ' comes after ') || b.name FROM Person a, Person b WHERE a.id > b.id
// * In Dialects where `||` does not fall-through for nulls (e.g. Oracle):
// * SELECT CASE WHEN a.name IS NOT NULL AND b.name IS NOT NULL THEN (a.name || ' comes after ') || b.name ELSE null END FROM Person a, Person b WHERE a.id > b.id

// Alternatively, you can use `flatten`
val q = quote {
  for {
    a <- query[Person]
    b <- query[Person] if (a.id > b.id)
  } yield (
    a.name.map(an => 
      b.name.map(bn => 
        an + " comes after " + bn)).flatten)
}

ctx.run(q) //: List[Option[String]]
// SELECT (a.name || ' comes after ') || b.name FROM Person a, Person b WHERE a.id > b.id

This is also very useful when selecting from outer-joined tables i.e. where the entire table is inside of an Option object. Note how below we get the fk field from Option[Address].

val q = quote {
  query[Person].leftJoin(query[Address])
    .on((p, a) => a.fk.exists(_ == p.id))
    .map {case (p /*Person*/, a /*Option[Address]*/) => (p.name, a.flatMap(_.fk))}
}

ctx.run(q) //: List[(Option[String], Option[Int])]
// SELECT p.name, a.fk FROM Person p LEFT JOIN Address a ON a.fk = p.id

orNull / getOrNull

The orNull method can be used to convert an Option-enclosed row back into a regular row. Since Option[T].orNull does not work for primitive types (e.g. Int, Double, etc...), you can use the getOrNull method inside of quoted blocks to do the same thing.

Note that since the presence of null columns can cause queries to break in some data sources (e.g. Spark), so use this operation very carefully.

val q = quote {
  query[Person].join(query[Address])
    .on((p, a) => a.fk.exists(_ == p.id))
    .filter {case (p /*Person*/, a /*Option[Address]*/) => 
      a.fk.getOrNull != 123 } // Exclude a particular value from the query.
                              // Since we already did an inner-join on this value, we know it is not null.
}

ctx.run(q) //: List[(Address, Person)]
// SELECT p.id, p.name, a.fk, a.street, a.zip FROM Person p INNER JOIN Address a ON a.fk IS NOT NULL AND a.fk = p.id WHERE a.fk <> 123

In certain situations, you may wish to pretend that a nullable-field is not actually nullable and perform regular operations (e.g. arithmetic, concatenation, etc...) on the field. You can use a combination of Option.apply and orNull (or getOrNull where needed) in order to do this.

val q = quote {
  query[Person].map(p => Option(p.name.orNull + " suffix"))
}

ctx.run(q)
// SELECT p.name || ' suffix' FROM Person p 
// i.e. same as the previous behavior

In all other situations, since Quill strictly checks nullable values, and case.. if conditionals will work correctly in all Optional constructs. However, since they may introduce behavior changes in your codebase, the following warning has been introduced:

Conditionals inside of Option.[map | flatMap | exists | forall] will create a CASE statement in order to properly null-check the sub-query (...)

val q = quote {
  query[Person].map(p => p.name.map(n => if (n == "Joe") "foo" else "bar").getOrElse("baz"))
}
// Information:(16, 15) Conditionals inside of Option.map will create a `CASE` statement in order to properly null-check the sub-query: `p.name.map((n) => if(n == "Joe") "foo" else "bar")`. 
// Expressions like Option(if (v == "foo") else "bar").getOrElse("baz") will now work correctly, but expressions that relied on the broken behavior (where "bar" would be returned instead) need to be modified  (see the "orNull / getOrNull" section of the documentation of more detail).

ctx.run(a)
// Used to be this:
// SELECT CASE WHEN CASE WHEN p.name = 'Joe' THEN 'foo' ELSE 'bar' END IS NOT NULL THEN CASE WHEN p.name = 'Joe' THEN 'foo' ELSE 'bar' END ELSE 'baz' END FROM Person p
// Now is this:
// SELECT CASE WHEN p.name IS NOT NULL AND CASE WHEN p.name = 'Joe' THEN 'foo' ELSE 'bar' END IS NOT NULL THEN CASE WHEN p.name = 'Joe' THEN 'foo' ELSE 'bar' END ELSE 'baz' END FROM Person p

equals

The ==, !=, and .equals methods can be used to compare regular types as well Option types in a scala-idiomatic way. That is to say, either T == T or Option[T] == Option[T] is supported and the following truth-table is observed:

Left Right Equality Result
a b == a == b
Some[T](a) Some[T](b) == a == b
Some[T](a) None == false
None Some[T](b) == false
None None == true
Some[T] Some[R] == Exception thrown.
a b != a != b
Some[T](a) Some[T](b) != a != b
Some[T](a) None != true
None Some[T](b) != true
Some[T] Some[R] != Exception thrown.
None None != false
case class Node(id:Int, status:Option[String], otherStatus:Option[String])

val q = quote { query[Node].filter(n => n.id == 123) }
ctx.run(q)
// SELECT n.id, n.status, n.otherStatus FROM Node n WHERE p.id = 123

val q = quote { query[Node].filter(r => r.status == r.otherStatus) }
ctx.run(q)
// SELECT r.id, r.status, r.otherStatus FROM Node r WHERE r.status IS NULL AND r.otherStatus IS NULL OR r.status = r.otherStatus

val q = quote { query[Node].filter(n => n.status == Option("RUNNING")) }
ctx.run(q)
// SELECT n.id, n.status, n.otherStatus FROM node n WHERE n.status IS NOT NULL AND n.status = 'RUNNING'

val q = quote { query[Node].filter(n => n.status != Option("RUNNING")) }
ctx.run(q)
// SELECT n.id, n.status, n.otherStatus FROM node n WHERE n.status IS NULL OR n.status <> 'RUNNING'

If you would like to use an equality operator that follows that ansi-idiomatic approach, failing the comparison if either side is null as well as the principle that null = null := false, you can import === (and =!=) from Context.extras. These operators work across T and Option[T] allowing comparisons like T === Option[T], Option[T] == T etc... to be made. You can use also === directly in Scala code and it will have the same behavior, returning false when other the left-hand or right-hand side is None. This is particularity useful in paradigms like Spark where you will typically transition inside and outside of Quill code.

When using a === b or a =!= b sometimes you will see the extra a IS NOT NULL AND b IS NOT NULL comparisons and sometimes you will not. This depends on equalityBehavior in SqlIdiom which determines whether the given SQL dialect already does ansi-idiomatic comparison to a, and b when an = operator is used, this allows us to omit the extra a IS NOT NULL AND b IS NOT NULL.

import ctx.extras._

// === works the same way inside of a quotation
val q = run( query[Node].filter(n => n.status === "RUNNING") )
// SELECT n.id, n.status FROM node n WHERE n.status IS NOT NULL AND n.status = 'RUNNING'

// as well as outside
(nodes:List[Node]).filter(n => n.status === "RUNNING")

Optional Tables

As we have seen in the examples above, only the map and flatMap methods are available on outer-joined tables (i.e. tables wrapped in an Option object).

Since you cannot use Option[Table].isDefined, if you want to null-check a whole table (e.g. if a left-join was not matched), you have to map to a specific field on which you can do the null-check.

val q = quote {
  query[Company].leftJoin(query[Address])
    .on((c, a) => c.zip == a.zip)         // Row type is (Company, Option[Address])
    .filter({case(c,a) => a.isDefined})   // You cannot null-check a whole table!
}

Instead, map the row-variable to a specific field and then check that field.

val q = quote {
  query[Company].leftJoin(query[Address])
    .on((c, a) => c.zip == a.zip)                     // Row type is (Company, Option[Address])
    .filter({case(c,a) => a.map(_.street).isDefined}) // Null-check a non-nullable field instead
}
ctx.run(q)
// SELECT c.name, c.zip, a.fk, a.street, a.zip 
// FROM Company c 
// LEFT JOIN Address a ON c.zip = a.zip 
// WHERE a.street IS NOT NULL

Finally, it is worth noting that a whole table can be wrapped into an Option object. This is particularly useful when doing a union on table-sets that are both right-joined and left-joined together.

val aCompanies = quote {
  for {
    c <- query[Company] if (c.name like "A%")
    a <- query[Address].join(_.zip == c.zip)
  } yield (c, Option(a))  // change (Company, Address) to (Company, Option[Address]) 
}
val bCompanies = quote {
  for {
    c <- query[Company] if (c.name like "A%")
    a <- query[Address].leftJoin(_.zip == c.zip)
  } yield (c, a) // (Company, Option[Address])
}
val union = quote {
  aCompanies union bCompanies
}
ctx.run(union)
// SELECT x.name, x.zip, x.fk, x.street, x.zip FROM (
// (SELECT c.name name, c.zip zip, x1.zip zip, x1.fk fk, x1.street street 
// FROM Company c INNER JOIN Address x1 ON x1.zip = c.zip WHERE c.name like 'A%') 
// UNION 
// (SELECT c1.name name, c1.zip zip, x2.zip zip, x2.fk fk, x2.street street 
// FROM Company c1 LEFT JOIN Address x2 ON x2.zip = c1.zip WHERE c1.name like 'A%')
// ) x

Ad-Hoc Case Classes

Case Classes can also be used inside quotations as output values:

case class Person(id: Int, name: String, age: Int)
case class Contact(personId: Int, phone: String)
case class ReachablePerson(name:String, phone: String)

val q = quote {
  for {
    p <- query[Person] if(p.id == 999)
    c <- query[Contact] if(c.personId == p.id)
  } yield {
    ReachablePerson(p.name, c.phone)
  }
}

ctx.run(q)
// SELECT p.name, c.phone FROM Person p, Contact c WHERE (p.id = 999) AND (c.personId = p.id)

As well as in general:

case class IdFilter(id:Int)

val q = quote {
  val idFilter = new IdFilter(999)
  for {
    p <- query[Person] if(p.id == idFilter.id)
    c <- query[Contact] if(c.personId == p.id)
  } yield {
    ReachablePerson(p.name, c.phone)
  }
}

ctx.run(q)
// SELECT p.name, c.phone FROM Person p, Contact c WHERE (p.id = 999) AND (c.personId = p.id)

Note however that this functionality has the following restrictions:

  1. The Ad-Hoc Case Class can only have one constructor with one set of parameters.
  2. The Ad-Hoc Case Class must be constructed inside the quotation using one of the following methods:
    1. Using the new keyword: new Person("Joe", "Bloggs")
    2. Using a companion object's apply method: Person("Joe", "Bloggs")
    3. Using a companion object's apply method explicitly: Person.apply("Joe", "Bloggs")
  3. Any custom logic in a constructor/apply-method of an Ad-Hoc case class will not be invoked when it is 'constructed' inside a quotation. To construct an Ad-Hoc case class with custom logic inside a quotation, you can use a quoted method.

Query probing

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

This feature is disabled by default. To enable it, mix the QueryProbing trait to the database configuration:

object myContext extends YourContextType with QueryProbing

The context must be created in a separate compilation unit in order to be loaded at compile time. Please use this guide that explains how to create a separate compilation unit for macros, that also serves to the purpose of defining a query-probing-capable context. context could be used instead of macros as the name of the separate compilation unit.

The configurations correspondent to the config key must be available at compile time. You can achieve it by adding this line to your project settings:

unmanagedClasspath in Compile += baseDirectory.value / "src" / "main" / "resources"

If your project doesn't have a standard layout, e.g. a play project, you should configure the path to point to the folder that contains your config file.

Actions

Database actions are defined using quotations as well. These actions don't have a collection-like API but rather a custom DSL to express inserts, deletes, and updates.

insert

val a = quote(query[Contact].insert(lift(Contact(999, "+1510488988"))))

ctx.run(a) // = 1 if the row was inserted 0 otherwise
// INSERT INTO Contact (personId,phone) VALUES (?, ?)

It is also possible to insert specific columns:

val a = quote {
  query[Contact].insert(_.personId -> lift(999), _.phone -> lift("+1510488988"))
}

ctx.run(a)
// INSERT INTO Contact (personId,phone) VALUES (?, ?)

batch insert

val a = quote {
  liftQuery(List(Person(0, "John", 31),Person(2, "name2", 32))).foreach(e => query[Person].insert(e))
}

ctx.run(a) //: List[Long] size = 2. Contains 1 @ positions, where row was inserted E.g List(1,1)
// INSERT INTO Person (id,name,age) VALUES (?, ?, ?)

update

val a = quote {
  query[Person].filter(_.id == 999).update(lift(Person(999, "John", 22)))
}

ctx.run(a) // = Long number of rows updated
// UPDATE Person SET id = ?, name = ?, age = ? WHERE id = 999

Using specific columns:

val a = quote {
  query[Person].filter(p => p.id == lift(999)).update(_.age -> lift(18))
}

ctx.run(a)
// UPDATE Person SET age = ? WHERE id = ?

Using columns as part of the update:

val a = quote {
  query[Person].filter(p => p.id == lift(999)).update(p => p.age -> (p.age + 1))
}

ctx.run(a)
// UPDATE Person SET age = (age + 1) WHERE id = ?

batch update

val a = quote {
  liftQuery(List(Person(1, "name", 31),Person(2, "name2", 32))).foreach { person =>
     query[Person].filter(_.id == person.id).update(_.name -> person.name, _.age -> person.age)
  }
}

ctx.run(a) // : List[Long] size = 2. Contains 1 @ positions, where row was inserted E.g List(1,0)
// UPDATE Person SET name = ?, age = ? WHERE id = ?

delete

val a = quote {
  query[Person].filter(p => p.name == "").delete
}

ctx.run(a) // = Long the number of rows deleted
// DELETE FROM Person WHERE name = ''

insert or update (upsert, conflict)

Upsert is supported by Postgres, SQLite, MySQL and H2 onConflictIgnore only (since v1.4.200 in PostgreSQL compatibility mode)

Postgres and SQLite

Ignore conflict
val a = quote {
  query[Product].insert(_.id -> 1, _.sku -> 10).onConflictIgnore
}

// INSERT INTO Product AS t (id,sku) VALUES (1, 10) ON CONFLICT DO NOTHING

Ignore conflict by explicitly setting conflict target

val a = quote {
  query[Product].insert(_.id -> 1, _.sku -> 10).onConflictIgnore(_.id)
}

// INSERT INTO Product AS t (id,sku) VALUES (1, 10) ON CONFLICT (id) DO NOTHING

Multiple properties can be used as well.

val a = quote {
  query[Product].insert(_.id -> 1, _.sku -> 10).onConflictIgnore(_.id, _.description)
}

// INSERT INTO Product (id,sku) VALUES (1, 10) ON CONFLICT (id,description) DO NOTHING
Update on Conflict

Resolve conflict by updating existing row if needed. In onConflictUpdate(target)((t, e) => assignment): target refers to conflict target, t - to existing row and e - to excluded, e.g. row proposed for insert.

val a = quote {
  query[Product]
    .insert(_.id -> 1, _.sku -> 10)
    .onConflictUpdate(_.id)((t, e) => t.sku -> (t.sku + e.sku))
}

// INSERT INTO Product AS t (id,sku) VALUES (1, 10) ON CONFLICT (id) DO UPDATE SET sku = (t.sku + EXCLUDED.sku)

Multiple properties can be used with onConflictUpdate as well.

val a = quote {
  query[Product]
    .insert(_.id -> 1, _.sku -> 10)
    .onConflictUpdate(_.id, _.description)((t, e) => t.sku -> (t.sku + e.sku))
}

INSERT INTO Product AS t (id,sku) VALUES (1, 10) ON CONFLICT (id,description) DO UPDATE SET sku = (t.sku + EXCLUDED.sku)

MySQL

Ignore any conflict, e.g. insert ignore

val a = quote {
  query[Product].insert(_.id -> 1, _.sku -> 10).onConflictIgnore
}

// INSERT IGNORE INTO Product (id,sku) VALUES (1, 10)

Ignore duplicate key conflict by explicitly setting it

val a = quote {
  query[Product].insert(_.id -> 1, _.sku -> 10).onConflictIgnore(_.id)
}

// INSERT INTO Product (id,sku) VALUES (1, 10) ON DUPLICATE KEY UPDATE id=id

Resolve duplicate key by updating existing row if needed. In onConflictUpdate((t, e) => assignment): t refers to existing row and e - to values, e.g. values proposed for insert.

val a = quote {
  query[Product]
    .insert(_.id -> 1, _.sku -> 10)
    .onConflictUpdate((t, e) => t.sku -> (t.sku + e.sku))
}

// INSERT INTO Product (id,sku) VALUES (1, 10) ON DUPLICATE KEY UPDATE sku = (sku + VALUES(sku))

Printing Queries

The translate method is used to convert a Quill query into a string which can then be printed.

val str = ctx.translate(query[Person])
println(str)
// SELECT x.id, x.name, x.age FROM Person x

Insert queries can also be printed:

val str = ctx.translate(query[Person].insert(lift(Person(0, "Joe", 45))))
println(str)
// INSERT INTO Person (id,name,age) VALUES (0, 'Joe', 45)

As well as batch insertions:

val q = quote {
  liftQuery(List(Person(0, "Joe",44), Person(1, "Jack",45)))
    .foreach(e => query[Person].insert(e))
}
val strs: List[String] = ctx.translate(q)
strs.map(println)
// INSERT INTO Person (id, name,age) VALUES (0, 'Joe', 44)
// INSERT INTO Person (id, name,age) VALUES (1, 'Jack', 45)

The translate method is available in every Quill context as well as the Cassandra and OrientDB contexts, the latter two, however, do not support Insert and Batch Insert query printing.

IO Monad

Quill provides an IO monad that allows the user to express multiple computations and execute them separately. This mechanism is also known as a free monad, which provides a way of expressing computations as referentially-transparent values and isolates the unsafe IO operations into a single operation. For instance:

// this code using Future

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

val p = Person(0, "John", 22)
ctx.run(query[Person].insert(lift(p))).flatMap { _ =>
  ctx.run(query[Person])
}

// isn't referentially transparent because if you refactor the second database 
// interaction into a value, the result will be different:

val allPeople = ctx.run(query[Person])
ctx.run(query[Person].insert(lift(p))).flatMap { _ =>
  allPeople
}

// this happens because `ctx.run` executes the side-effect (database IO) immediately
// The IO monad doesn't perform IO immediately, so both computations:

val p = Person(0, "John", 22)

val a =
  ctx.runIO(query[Person].insert(lift(p))).flatMap { _ =>
    ctx.runIO(query[Person])
  }


val allPeople = ctx.runIO(query[Person])

val b =
  ctx.runIO(query[Person].insert(lift(p))).flatMap { _ =>
    allPeople
  }

// produce the same result when executed

performIO(a) == performIO(b)

The IO monad has an interface similar to Future; please refer to the class for more information regarding the available operations.

The return type of performIO varies according to the context. For instance, async contexts return Futures while JDBC returns values synchronously.

NOTE: Avoid using the variable name io since it conflicts with Quill's package io.getquill, otherwise you will get the following error.

recursive value io needs type

IO Monad and transactions

IO also provides the transactional method that delimits a transaction:

val a =
  ctx.runIO(query[Person].insert(lift(p))).flatMap { _ =>
    ctx.runIO(query[Person])
  }

performIO(a.transactional) // note: transactional can be used outside of `performIO`

Getting a ResultSet

Quill JDBC Contexts allow you to use prepare in order to get a low-level ResultSet that is useful for interacting with legacy APIs. This function returns a f: (Connection) => (PreparedStatement) closure as opposed to a PreparedStatement in order to guarantee that JDBC Exceptions are not thrown until you can wrap them into the appropriate Exception-handling mechanism (e.g. try/catch, Try etc...).

val q = quote {
  query[Product].filter(_.id == 1)
}
val preparer: (Connection) => (PreparedStatement)  = ctx.prepare(q)
// SELECT x1.id, x1.description, x1.sku FROM Product x1 WHERE x1.id = 1

// Use ugly stateful code, bracketed effects, or try-with-resources here:
var preparedStatement: PreparedStatement = _
var resultSet: ResultSet = _

try {
  preparedStatement = preparer(myCustomDataSource.getConnection)
  resultSet = preparedStatement.executeQuery()
} catch {
  case e: Exception =>
    // Close the preparedStatement and catch possible exceptions
    // Close the resultSet and catch possible exceptions
}

The prepare function can also be used with insert, and update queries.

val q = quote {
  query[Product].insert(lift(Product(1, "Desc", 123))
}
val preparer: (Connection) => (PreparedStatement)  = ctx.prepare(q)
// INSERT INTO Product (id,description,sku) VALUES (?, ?, ?)

As well as with batch queries.

Make sure to first quote your batch query and then pass the result into the prepare function (as is done in the example below) or the Scala compiler may not type the output correctly #1518.

val q = quote {
  liftQuery(products).foreach(e => query[Product].insert(e))
}
val preparers: Connection => List[PreparedStatement] = ctx.prepare(q)
val preparedStatement: List[PreparedStatement] = preparers(jdbcConf.dataSource.getConnection)

Effect tracking

The IO monad tracks the effects that a computation performs in its second type parameter:

val a: IO[ctx.RunQueryResult[Person], Effect.Write with Effect.Read] =
  ctx.runIO(query[Person].insert(lift(p))).flatMap { _ =>
    ctx.runIO(query[Person])
  }

This mechanism is useful to limit the kind of operations that can be performed. See this blog post as an example.

Implicit query

Quill provides implicit conversions from case class companion objects to query[T] through an additional trait:

val ctx = new SqlMirrorContext(MirrorSqlDialect, Literal) with ImplicitQuery

import ctx._

val q = quote {
  for {
    p <- Person if(p.id == 999)
    c <- Contact if(c.personId == p.id)
  } yield {
    (p.name, c.phone)
  }
}

ctx.run(q)
// SELECT p.name, c.phone FROM Person p, Contact c WHERE (p.id = 999) AND (c.personId = p.id)

Note the usage of Person and Contact instead of query[Person] and query[Contact].

SQL-specific operations

Some operations are SQL-specific and not provided with the generic quotation mechanism. The SQL contexts provide implicit classes for this kind of operation:

val ctx = new SqlMirrorContext(MirrorSqlDialect, Literal)
import ctx._

like

val q = quote {
  query[Person].filter(p => p.name like "%John%")
}
ctx.run(q)
// SELECT p.id, p.name, p.age FROM Person p WHERE p.name like '%John%'

SQL-specific encoding

Arrays

Quill provides SQL Arrays support. In Scala we represent them as any collection that implements Seq:

import java.util.Date

case class Book(id: Int, notes: List[String], pages: Vector[Int], history: Seq[Date])

ctx.run(query[Book])
// SELECT x.id, x.notes, x.pages, x.history FROM Book x

Note that not all drivers/databases provides such feature hence only PostgresJdbcContext and PostgresAsyncContext support SQL Arrays.

Cassandra-specific encoding

val ctx = new CassandraMirrorContext(Literal)
import ctx._

Collections

The Cassandra context provides List, Set, and Map encoding:


case class Book(id: Int, notes: Set[String], pages: List[Int], history: Map[Int, Boolean])

ctx.run(query[Book])
// SELECT id, notes, pages, history FROM Book

User-Defined Types

The cassandra context provides encoding of UDT (user-defined types).

import io.getquill.context.cassandra.Udt

case class Name(firstName: String, lastName: String) extends Udt

To encode the UDT and bind it into the query (insert/update queries), the context needs to retrieve UDT metadata from the cluster object. By default, the context looks for UDT metadata within the currently logged keyspace, but it's also possible to specify a concrete keyspace with udtMeta:

implicit val nameMeta = udtMeta[Name]("keyspace2.my_name")

When a keyspace is not set in udtMeta then the currently logged one is used.

Since it's possible to create a context without specifying a keyspace, (e.g. the keyspace parameter is null and the session is not bound to any keyspace), the UDT metadata will be resolved throughout the entire cluster.

It is also possible to rename UDT columns with udtMeta:

implicit val nameMeta = udtMeta[Name]("name", _.firstName -> "first", _.lastName -> "last")

Cassandra-specific operations

The cassandra context also provides a few additional operations:

allowFiltering

val q = quote {
  query[Person].filter(p => p.age > 10).allowFiltering
}
ctx.run(q)
// SELECT id, name, age FROM Person WHERE age > 10 ALLOW FILTERING

ifNotExists

val q = quote {
  query[Person].insert(_.age -> 10, _.name -> "John").ifNotExists
}
ctx.run(q)
// INSERT INTO Person (age,name) VALUES (10, 'John') IF NOT EXISTS

ifExists

val q = quote {
  query[Person].filter(p => p.name == "John").delete.ifExists
}
ctx.run(q)
// DELETE FROM Person WHERE name = 'John' IF EXISTS

usingTimestamp

val q1 = quote {
  query[Person].insert(_.age -> 10, _.name -> "John").usingTimestamp(99)
}
ctx.run(q1)
// INSERT INTO Person (age,name) VALUES (10, 'John') USING TIMESTAMP 99

val q2 = quote {
  query[Person].usingTimestamp(99).update(_.age -> 10)
}
ctx.run(q2)
// UPDATE Person USING TIMESTAMP 99 SET age = 10

usingTtl

val q1 = quote {
  query[Person].insert(_.age -> 10, _.name -> "John").usingTtl(11)
}
ctx.run(q1)
// INSERT INTO Person (age,name) VALUES (10, 'John') USING TTL 11

val q2 = quote {
  query[Person].usingTtl(11).update(_.age -> 10)
}
ctx.run(q2)
// UPDATE Person USING TTL 11 SET age = 10

val q3 = quote {
  query[Person].usingTtl(11).filter(_.name == "John").delete
}
ctx.run(q3)  
// DELETE FROM Person USING TTL 11 WHERE name = 'John'

using

val q1 = quote {
  query[Person].insert(_.age -> 10, _.name -> "John").using(ts = 99, ttl = 11)
}
ctx.run(q1)
// INSERT INTO Person (age,name) VALUES (10, 'John') USING TIMESTAMP 99 AND TTL 11

val q2 = quote {
  query[Person].using(ts = 99, ttl = 11).update(_.age -> 10)
}
ctx.run(q2)
// UPDATE Person USING TIMESTAMP 99 AND TTL 11 SET age = 10

val q3 = quote {
  query[Person].using(ts = 99, ttl = 11).filter(_.name == "John").delete
}
ctx.run(q3)
// DELETE FROM Person USING TIMESTAMP 99 AND TTL 11 WHERE name = 'John'

ifCond

val q1 = quote {
  query[Person].update(_.age -> 10).ifCond(_.name == "John")
}
ctx.run(q1)
// UPDATE Person SET age = 10 IF name = 'John'

val q2 = quote {
  query[Person].filter(_.name == "John").delete.ifCond(_.age == 10)
}
ctx.run(q2)
// DELETE FROM Person WHERE name = 'John' IF age = 10

delete column

val q = quote {
  query[Person].map(p => p.age).delete
}
ctx.run(q)
// DELETE p.age FROM Person

list.contains / set.contains

requires allowFiltering

val q = quote {
  query[Book].filter(p => p.pages.contains(25)).allowFiltering
}
ctx.run(q)
// SELECT id, notes, pages, history FROM Book WHERE pages CONTAINS 25 ALLOW FILTERING

map.contains

requires allowFiltering

val q = quote {
  query[Book].filter(p => p.history.contains(12)).allowFiltering
}
ctx.run(q)
// SELECT id, notes, pages, history FROM book WHERE history CONTAINS 12 ALLOW FILTERING

map.containsValue

requires allowFiltering

val q = quote {
  query[Book].filter(p => p.history.containsValue(true)).allowFiltering
}
ctx.run(q)
// SELECT id, notes, pages, history FROM book WHERE history CONTAINS true ALLOW FILTERING

Dynamic queries

Quill's default operation mode is compile-time, but there are queries that have their structure defined only at runtime. Quill automatically falls back to runtime normalization and query generation if the query's structure is not static. Example:

val ctx = new SqlMirrorContext(MirrorSqlDialect, Literal)

import ctx._

sealed trait QueryType
case object Minor extends QueryType
case object Senior extends QueryType

def people(t: QueryType): Quoted[Query[Person]] =
  t match {
    case Minor => quote {
      query[Person].filter(p => p.age < 18)
    }
    case Senior => quote {
      query[Person].filter(p => p.age > 65)
    }
  }

ctx.run(people(Minor))
// SELECT p.id, p.name, p.age FROM Person p WHERE p.age < 18

ctx.run(people(Senior))
// SELECT p.id, p.name, p.age FROM Person p WHERE p.age > 65

Dynamic query API

Additionally, Quill provides a separate query API to facilitate the creation of dynamic queries. This API allows users to easily manipulate quoted values instead of working only with quoted transformations.

Important: A few of the dynamic query methods accept runtime string values. It's important to keep in mind that these methods could be a vector for SQL injection.

Let's use the filter transformation as an example. In the regular API, this method has no implementation since it's an abstract member of a trait:

def filter(f: T => Boolean): EntityQuery[T]

In the dynamic API, filter is has a different signature and a body that is executed at runtime:

def filter(f: Quoted[T] => Quoted[Boolean]): DynamicQuery[T] =
  transform(f, Filter)

It takes a Quoted[T] as input and produces a Quoted[Boolean]. The user is free to use regular scala code within the transformation:

def people(onlyMinors: Boolean) =
  dynamicQuery[Person].filter(p => if(onlyMinors) quote(p.age < 18) else quote(true))

In order to create a dynamic query, use one of the following methods:

dynamicQuery[Person]
dynamicQuerySchema[Person]("people", alias(_.name, "pname"))

It's also possible to transform a Quoted into a dynamic query:

val q = quote {
  query[Person]
}
q.dynamic.filter(p => quote(p.name == "John"))

The dynamic query API is very similar to the regular API but has a few differences:

Queries

// schema queries use `alias` instead of tuples
dynamicQuerySchema[Person]("people", alias(_.name, "pname"))

// this allows users to use a dynamic list of aliases
val aliases = List(alias[Person](_.name, "pname"), alias[Person](_.age, "page"))
dynamicQuerySchema[Person]("people", aliases:_*)

// a few methods have an overload with the `Opt` suffix,
// which apply the transformation only if the option is defined:

def people(minAge: Option[Int]) =
  dynamicQuery[Person].filterOpt(minAge)((person, minAge) => quote(person.age >= minAge))

def people(maxRecords: Option[Int]) =
  dynamicQuery[Person].takeOpt(maxRecords)

def people(dropFirst: Option[Int]) =
  dynamicQuery[Person].dropOpt(dropFirst)

// method with `If` suffix, for better chaining  
def people(userIds: Seq[Int]) =
  dynamicQuery[Person].filterIf(userIds.nonEmpty)(person => quote(liftQuery(userIds).contains(person.id)))

Actions

// actions use `set` 
dynamicQuery[Person].filter(_.id == 1).update(set(_.name, quote("John")))

// or `setValue` if the value is not quoted
dynamicQuery[Person].insert(setValue(_.name, "John"))

// or `setOpt` that will be applied only the option is defined
dynamicQuery[Person].insert(setOpt(_.name, Some(
quill open issues Ask a question     (View All Issues)
  • almost 4 years Join with Option column should be possible using `== Some(other.column)`
  • almost 4 years Multi column contains doesn't work
  • almost 4 years Case class with private field requires queryMeta implicit to run a query
  • almost 4 years JOIN query nesting on DISTINCT clause
  • about 4 years Empty set generates incorrect type of IN clause for char columns
  • about 4 years join or leftJoin with nested query that also has join or nestedJoin results in invalid SQL
  • about 4 years leftJoin using Option column
  • about 4 years UUID encoder in quill-async not compatible with mysql
  • about 4 years Meta allow updates for case classes that are different than the query case class
  • about 4 years executeAction throws exception instead of failing the Future
  • about 4 years Can't tokenize a non-scalar lifting while lifting Option[String]
  • about 4 years Naming strategy overwrites customized columns identifiers
  • about 4 years To be able to define contactPoints with ports in the configuration.
  • about 4 years Common Table Expression support
  • about 4 years Batch actions are slow (and sequential)
  • about 4 years UPDATE ... RETURNING *
  • about 4 years Collection interface broken for batch actions
  • about 4 years Guarding against the wrong `lift`
  • about 4 years `lift(...).forall(...)` should compile to `? == ... OR ? IS NULL`
  • about 4 years Add a "currently using" section to README
  • about 4 years (Infix) / Custom - Filter/SortBy Query
  • about 4 years query update on selective fields
  • about 4 years Add 'update mysql/postgresql-async to 0.2.20' to CHANGELOG for 0.10.0 due to upstream Netty upgrade
  • about 4 years Failing db decoder should list column name that can't be decoded
  • about 4 years Is there a way to silence output from macro execution?
  • about 4 years Release script doesn't update website
  • about 4 years Create a StackOverflow tag
  • about 4 years IndexOutOfBoundsException when joining nested query with another join and filter
  • about 4 years How to paginate cassandra ?
  • about 4 years Port to scalameta
quill open pull requests (View All Pulls)
  • First H2 Iteration
  • [WIP] allow free variable bindings within `db.run`
  • [WIP] Return type improve
  • [WIP] Allow Free Variables anywhere
  • [WIP] Change transaction methods to provide similar signatures
  • [WIP] new site
  • [WIP] embedded case classes
  • transform ConfiguredEntity
  • [wip] make quotations dependent on the source
  • Refactor generated to returning keyword in order to return the correct type
  • [wip] Refactoring action apply
  • new encoding, macros refactoring, and additional fixes
  • Longer example and quote usage
  • [WIP] Encode SQL types for all SQL Encoder's/Decoder's
  • [WIP] Upserts
  • Initial commit for Scala 2.12 support
  • Encoders and decoders refactoring
  • [WIP] migrate to finagle-postgres 0.4.0
  • Update dependencies
  • Update tut plugin version from 0.4.2 to 0.4.7
  • [WIP] - Support for Cassandra collections
  • Update version of scalatest from 3.0.0-RC2 to 3.0.1
  • Update Monix version to 2.1.1
  • elimiate nest sql query like `SELECT x.* FROM (xxx) x`
  • hides debug/info/warn messages behind a flag
  • [WIP] Support Option.contains
  • [WIP] io free monad
  • [WIP] Accept Seq for batch action.
  • [WIP] materializeQueryMeta for tuple with Option[Custom] and Option[Option[Date]]
  • [WIP] Dependency updates
  • [WIP] Upgrade version finagle-postgres to 0.4.2
  • Collections support
  • Support for MS SQL
  • Fixing comparison with phantom
  • [WIP] ISSUE-697: Add tests.
  • [WIP] Upgrade version finagle-postgres to 0.4.1
  • Update sqlite-jdbc driver
  • Fix building sql-server in travis matrix
  • Fix query compilation of nested of single select query
  • Prevent generating invalid nested queries with take/drop after for comprehension
  • [WIP] Add support of dynamic table names
  • [WIP] Add support of upsert for Postgres and MySQL
  • [WIP] Ndbc module
  • [WIP] Splice query
  • trigger 2.3.3 release
  • Fix renaming aggregated properties in groupBy with custom querySchema
  • [WIP] Adding Quill-Application-Types (Quats) to AST
  • Update sqlite-jdbc to 3.32.3.1
  • Add support for scala 2.13 to quill-cassandra-lagom
  • Update mysql-connector-java to 8.0.21
  • Update orientdb-graphdb to 3.1.1
  • Update monix-eval, monix-reactive to 3.2.2
  • Add default implementations so Query can be more easily inherited from Dotty
  • Update sbt-scalajs, scalajs-compiler to 0.6.33
  • quill-finagle-mysql: add connection init sql support
  • Update cassandra-driver-core to 3.9.0
  • Update sbt-scalajs, scalajs-compiler to 1.0.1
  • Update sbt-scalajs-crossproject to 1.0.0
  • Update sbt-scalajs, scalajs-compiler to 1.0.0
  • Update scalajs-compiler to 0.6.32
  • Add ilike to SqlDsl
  • Monix streaming with NDBC
  • Make samples simpler allowing to focus on the relevant part
  • Revert "Update sbt-scalajs, scalajs-compiler to 0.6.31"
  • Update Monix version
  • [WIP] Add Beta Reductions for Trivial Conditions
  • Dynamic query DSL updated
  • [WIP] Add DSL for datetime/duration
  • [WIP] Fix if (liftQuery(set).isEmpty)
  • [WIP] Char encoders for all possible contexts
  • pluralize with evo-inflector
  • [WIP] Context for BigQuery
  • [WIP] create time logger
  • [WIP][Help needed] Scala Native Support Fixed.
  • [WIP] Scala Native - Updated
  • quill-cassandra-lagom: expose public method to retrieve bound statements
quill questions on Stackoverflow (View All Questions)
  • Rails - Quill does not load text while calling edit action
  • Can I add a class to paragraphs within the Quill editor?
  • Add custom button actions to Quill
  • Share Quill toolbar across multiple editors
  • How to add image in Quill JS?
  • Integrating Quill Rich Text Editor toolbar in SAPUI5 (Javascript) project
  • Quill js font size issue
  • Controls for image in quill editor
  • Quill JS - unsure how to properly set initial toolbar options
  • Trying to initialize Quill with specific formats and getting an error
  • ng-quill output. remove html tag but retain its value
  • Meteor and Quill editor Integration
  • Quill: author-advanced class does not fire on "keyup" event
  • Quill WYSIWYG editor - adding attributes to buttons
  • add a click handler to a quill object
  • Change tags in quill editor?
  • quill : drop content at the position under the pointer
  • quill : keep span id and class?
  • How to ensure programmatically selected text is visible in Quill when scrollbars present
  • ng-quill text editor only updates once
  • Including styles in Quill setHTML
  • How can I add/remove a class from the .editor-container in a quill instance
  • How can I get the Quill editor authorship module to turn highlighting on and off?
  • Quill Editor: Quill editor removes inline style of margin-left
  • How can I disable the Quill editor
  • How to combine Quill Rich Text Editor and socket.io to exchange Deltas
quill list of languages used
Other projects in Scala
Powered by Autocode - Instant Webhooks, Scripts and APIs
Autocode logo wordmark