IMPORTANT: This is the documentation for the latest SNAPSHOT version. Please refer to the website at http://getquill.io for the lastest release's documentation.
Compile-time Language Integrated Query for Scala
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).
- Boilerplate-free mapping: The database schema is mapped using simple case classes.
- Quoted DSL: Queries are defined inside a
quoteblock. Quill parses each quoted block of code (quotation) at compile time and translates them to an internal Abstract Syntax Tree (AST) - Compile-time query generation: The
ctx.runcall 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. - 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.
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 types and methods to deal 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 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)
}
}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 queryQuill 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.
Quoting is implicit when writing a query in a run statement.
ctx.run(query[Circle].map(_.radius))
// SELECT r.radius FROM Circle rQuotations 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.
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 > ?A Traversable instance can be lifted as a Query. There are two main usages for lifted queries:
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 (?)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 (?)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 > 1Alternatively, 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 > 1If 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")
}
}It is possible to make a column that is a generated by the database to be ignored during insertions and returned as a returning value.
case class Product(id: Long, description: String, sku: Long)
val q = quote {
query[Product].insert(lift(Product(0L, "My Product", 1011L))).returning(_.id)
}
val returnedIds = ctx.run(q)
// INSERT INTO Product (description,sku) VALUES (?, ?)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 xNote 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 xThe 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 kind 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:
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 > 18val q = quote {
query[Person].map(p => p.name)
}
ctx.run(q)
// SELECT p.name FROM Person pval 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)// 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 pval 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 DESCval q = quote {
query[Person].drop(2).take(1)
}
ctx.run(q)
// SELECT x.id, x.name, x.age FROM Person x LIMIT 1 OFFSET 2val 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.ageval 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) xval 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) xval 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 pval 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))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))val q = quote {
query[Person].map(p => p.age).distinct
}
ctx.run(q)
// SELECT DISTINCT p.age FROM Person pval 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') pJoins 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 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.fkWhat about joining more then 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.zipNo worries though, implicit joins and flat joins have your other use-cases covered!
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.fkNow 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.zipWell 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 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.idNow 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.zipCan'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.idNote 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 a 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.
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.
Optionals cannot be compared via the
==operator due to differences in database handling. See .exists as to how to do that.
In many cases, Quill tries to rely on the null-fallthrough behavior that is ANSI standard:
null == null := falsenull == [true | false] := falseThis 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 SQLv || 'foo'as opposed toCASE IF (v is not null) v || 'foo' ELSE null ENDso long as the concatenation operator||"falls-through" and returnsnullwhen 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.
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 NULLThe 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 NULLThis 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.idIt is also useful for comparing two optional values:
val q = quote {
for {
pa <- query[Person]
pb <- query[Person] if (pa.name.exists(na => pb.name.exists(nb => na == nb)))
} yield (pa, pb)
}
ctx.run(q)
// SELECT pa.id, pa.name, pb.id, pb.name FROM Person pa, Person pb WHERE pa.name = pb.nameNote however that as you can see from the examples 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.
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.idTypically 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'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 pAdditionally, this this method is useful when you want to get a non-optional field out of a 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.zipFor more details about this operation (and some caveats), see the 'Optional Tables' section.
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.idThis 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.idThe 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 <> 123In certain situations, you may wish 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 behaviorIn 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
CASEstatement 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
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 a 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 NULLFinally, it is worth noting that a whole table can be wrapped into an Option object. This is particularily
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%')
// ) xCase 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:
- The Ad-Hoc Case Class can only have one constructor with one set of parameters.
- The Ad-Hoc Case Class must be constructed inside the quotation using one of the following methods:
- Using the
newkeyword:new Person("Joe", "Bloggs") - Using a companion object's apply method:
Person("Joe", "Bloggs") - Using a companion object's apply method explicitly:
Person.apply("Joe", "Bloggs")
- Using the
- Any custom logic in a constructor/apply-method of a 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 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.
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.
val a = quote(query[Contact].insert(lift(Contact(999, "+1510488988"))))
ctx.run(a)
// INSERT INTO Contact (personId,phone) VALUES (?, ?)val a = quote {
query[Contact].insert(_.personId -> lift(999), _.phone -> lift("+1510488988"))
}
ctx.run(a)
// INSERT INTO Contact (personId,phone) VALUES (?, ?)val a = quote {
liftQuery(List(Person(0, "John", 31))).foreach(e => query[Person].insert(e))
}
ctx.run(a)
// INSERT INTO Person (id,name,age) VALUES (?, ?, ?)val a = quote {
query[Person].filter(_.id == 999).update(lift(Person(999, "John", 22)))
}
ctx.run(a)
// UPDATE Person SET id = ?, name = ?, age = ? WHERE id = 999val a = quote {
query[Person].filter(p => p.id == lift(999)).update(_.age -> lift(18))
}
ctx.run(a)
// UPDATE Person SET age = ? WHERE id = ?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 = ?val a = quote {
liftQuery(List(Person(1, "name", 31))).foreach { person =>
query[Person].filter(_.id == person.id).update(_.name -> person.name, _.age -> person.age)
}
}
ctx.run(a)
// UPDATE Person SET name = ?, age = ? WHERE id = ?val a = quote {
query[Person].filter(p => p.name == "").delete
}
ctx.run(a)
// DELETE FROM Person WHERE name = ''Upsert is supported by Postgres, SQLite and MySQL
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 NOTHINGIgnore 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 NOTHINGResolve 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)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=idResolve 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))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 xInsert 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.
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 following error.
recursive value io needs type
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`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.
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].
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._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%'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 xNote that not all drivers/databases provides such feature hence only PostgresJdbcContext and
PostgresAsyncContext support SQL Arrays.
val ctx = new CassandraMirrorContext(Literal)
import ctx._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 BookThe cassandra context provides encoding of UDT (user-defined types).
import io.getquill.context.cassandra.Udt
case class Name(firstName: String, lastName: String) extends UdtTo encode UDT and bind it into the query (insert/update queries), context needs to retrieve UDT metadata from
cluster object. By default, context looks for UDT within currently logged keyspace, but it's also possible to specify
concrete keyspace with udtMeta:
implicit val nameMeta = udtMeta[Name]("keyspace2.my_name")When keyspace is not set in udtMeta then the currently logged is used.
Since it's possible to create context without specifying keyspace, e.g. keyspace parameter is null and session is not bound to any keyspace, UDT metadata is being resolved among all cluster.
It's also possible to rename UDT columns with udtMeta:
implicit val nameMeta = udtMeta[Name]("name", _.firstName -> "first", _.lastName -> "last")The cassandra context also provides a few additional operations:
val q = quote {
query[Person].filter(p => p.age > 10).allowFiltering
}
ctx.run(q)
// SELECT id, name, age FROM Person WHERE age > 10 ALLOW FILTERINGval q = quote {
query[Person].insert(_.age -> 10, _.name -> "John").ifNotExists
}
ctx.run(q)
// INSERT INTO Person (age,name) VALUES (10, 'John') IF NOT EXISTSval q = quote {
query[Person].filter(p => p.name == "John").delete.ifExists
}
ctx.run(q)
// DELETE FROM Person WHERE name = 'John' IF EXISTSval 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 = 10val 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'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'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 = 10val q = quote {
query[Person].map(p => p.age).delete
}
ctx.run(q)
// DELETE p.age FROM Personrequires 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 FILTERINGrequires 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 FILTERINGrequires 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 FILTERINGQuill'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 > 65Addionally, 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].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("John")))
// it's also possible to use a runtime string value as the column name
dynamicQuery[Person].update(set("name", quote("John")))
// to insert or update a case class instance, use `insertValue`/`updateValue`
val p = Person(0, "John", 21)
dynamicQuery[Person].insertValue(p)
dynamicQuery[Person].updateValue(p)Infix is a very flexible mechanism to use non-supported features without having to use plain queries in the target language. It allows insertion of arbitrary strings within quotations.
For instance, quill doesn't support the FOR UPDATE SQL feature. It can still be used through infix and implicit classes:
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)
// SELECT p.name, p.age FROM person p WHERE p.age < 18 FOR UPDATEThe forUpdate quotation can be reused for multiple queries.
Infix supprts runtime string values through the #$ prefix. Example:
def test(functionName: String) =
ctx.run(query[Person].map(p => infix"#$functionName(${p.name})".as[Int]))You can also use infix to port raw SQL queries to Quill and map it to regular scala tuples.
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) xNote that in this case the result query is nested.
It's required since quill is not aware of a query tree and cannot safely unnest it.
This is different to the example above because infix starts with the query infix"$q... where its tree is already compiled
A custom database function can also be used through infix:
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 pYou can implement comparison operators by defining implicit conversion and using infix.
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])
}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)
)Quill uses Encoders to encode query inputs and Decoders to read values returned by queries. The library provides a few built-in encodings and two mechanisms to define custom encodings: mapped encoding and raw encoding.
If the correspondent database type is already supported, use MappedEncoding. In this example, String is already supported by Quill and the UUID encoding from/to String is defined through mapped encoding:
import ctx._
import java.util.UUID
implicit val encodeUUID = MappedEncoding[UUID, String](_.toString)
implicit val decodeUUID = MappedEncoding[String, UUID](UUID.fromString(_))A mapped encoding also can be defined without a context instance by importing io.getquill.MappedEncoding:
import io.getquill.MappedEncoding
import java.util.UUID
implicit val encodeUUID = MappedEncoding[UUID, String](_.toString)
implicit val decodeUUID = MappedEncoding[String, UUID](UUID.fromString(_))Note that can it be also used to provide mapping for element types of collection (SQL Arrays or Cassandra Collections).
If the database type is not supported by Quill, it is possible to provide "raw" encoders and decoders:
trait UUIDEncodingExample {
val jdbcContext: PostgresJdbcContext[Literal] // your context should go here
import jdbcContext._
implicit val uuidDecoder: Decoder[UUID] =
decoder((index, row) =>
UUID.fromString(row.getObject(index).toString)) // database-specific implementation
implicit val uuidEncoder: Encoder[UUID] =
encoder(java.sql.Types.OTHER, (index, value, row) =>
row.setObject(index, value, java.sql.Types.OTHER)) // database-specific implementation
// Only for postgres
implicit def arrayUUIDEncoder[Col <: Seq[UUID]]: Encoder[Col] = arrayRawEncoder[UUID, Col]("uuid")
implicit def arrayUUIDDecoder[Col <: Seq[UUID]](implicit bf: CBF[UUID, Col]): Decoder[Col] =
arrayRawDecoder[UUID, Col]
}Quill automatically encodes AnyVals (value classes):
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
}
ctx.run(q)
// SELECT u.id, u.name FROM User u WHERE (u.id = 1)The meta DSL allows the user to customize how Quill handles the expansion and execution of quotations through implicit meta instances.
By default, quill expands query[Person] to querySchema[Person]("Person"). It's possible to customize this behavior using an implicit instance of SchemaMeta:
def example = {
implicit val personSchemaMeta = schemaMeta[Person]("people", _.id -> "person_id")
ctx.run(query[Person])
// SELECT x.person_id, x.name, x.age FROM people x
}InsertMeta customizes the expansion of case classes for insert actions (query[Person].insert(p)). By default, all columns are expanded and through an implicit InsertMeta, it's possible to exclude columns from the expansion:
implicit val personInsertMeta = insertMeta[Person](_.id)
ctx.run(query[Person].insert(lift(Person(-1, "John", 22))))
// INSERT INTO Person (name,age) VALUES (?, ?)Note that the parameter of insertMeta is called exclude, but it isn't possible to use named parameters for macro invocations.
UpdateMeta customizes the expansion of case classes for update actions (query[Person].update(p)). By default, all columns are expanded, and through an implicit UpdateMeta, it's possible to exclude columns from the expansion:
implicit val personUpdateMeta = updateMeta[Person](_.id)
ctx.run(query[Person].filter(_.id == 1).update(lift(Person(1, "John", 22))))
// UPDATE Person SET name = ?, age = ? WHERE id = 1Note that the parameter of updateMeta is called exclude, but it isn't possible to use named parameters for macro invocations.
This kind of meta instance customizes the expansion of query types and extraction of the final value. For instance, it's possible to use this feature to normalize values before reading them from the database:
implicit val personQueryMeta =
queryMeta(
(q: Query[Person]) =>
q.map(p => (p.id, infix"CONVERT(${p.name} USING utf8)".as[String], p.age))
) {
case (id, name, age) =>
Person(id, name, age)
}The query meta definition is open and allows the user to even join values from other tables before reading the final value. This kind of usage is not encouraged.
Contexts represent the database and provide an execution interface for queries.
Quill provides mirror context for test purposes. Instead of running the query, mirror context return a structure with the information that would be used to run the query. There are three mirror context instances:
io.getquill.MirrorContext: Mirrors the quotation ASTio.getquill.SqlMirrorContext: Mirrors the SQL queryio.getquill.CassandraMirrorContext: Mirrors the CQL query
The context instance provides all methods and types to interact with quotations and the database. Depending on how the context import happens, Scala won't be able to infer that the types are compatible.
For instance, this example will not compile:
class MyContext extends SqlMirrorContext(MirrorSqlDialect, Literal)
case class MySchema(c: MyContext) {
import c._
val people = quote {
querySchema[Person]("people")
}
}
case class MyDao(c: MyContext, schema: MySchema) {
def allPeople =
c.run(schema.people)
// ERROR: [T](quoted: MyDao.this.c.Quoted[MyDao.this.c.Query[T]])MyDao.this.c.QueryResult[T]
cannot be applied to (MyDao.this.schema.c.Quoted[MyDao.this.schema.c.EntityQuery[Person]]{def quoted: io.getquill.ast.ConfiguredEntity; def ast: io.getquill.ast.ConfiguredEntity; def id1854281249(): Unit; val bindings: Object})
}
One alternative to work with this kind of context import is use traits with abstract context values:
class MyContext extends SqlMirrorContext(MirrorSqlDialect, Literal)
trait MySchema {
val c: MyContext
import c._
val people = quote {
querySchema[Person]("people")
}
}
case class MyDao(c: MyContext) extends MySchema {
import c._
def allPeople =
c.run(people)
}Another simple way to modularize Quill code is by extending Context as a self-type and applying mixins. Using this strategy,
it is possible to create functionality that is fully portable across databases and even different types of databases
(e.g. creating common queries for both Postgres and Spark).
For example, create the following abstract context:
trait ModularContext[I <: Idiom, N <: NamingStrategy] { this: Context[I, N] =>
def peopleOlderThan = quote {
(age:Int, q:Query[Person]) => q.filter(p => p.age > age)
}
}Let's see how this can be used across different kinds of databases and Quill contexts.
// Note: In some cases need to explicitly specify [MirrorSqlDialect, Literal].
val ctx =
new SqlMirrorContext[MirrorSqlDialect, Literal](MirrorSqlDialect, Literal)
with ModularContext[MirrorSqlDialect, Literal]
import ctx._
println( run(peopleOlderThan(22, query[Person])).string )val ctx =
new PostgresJdbcContext[Literal](Literal, ds)
with ModularContext[PostgresDialect, Literal]
import ctx._
val results = run(peopleOlderThan(22, query[Person]))object CustomQuillSparkContext extends QuillSparkContext
with ModularContext[SparkDialect, Literal]
val results = run(peopleOlderThan(22, liftQuery(dataset)))Quill provides a fully type-safe way to use Spark's highly-optimized SQL engine. It's an alternative to Dataset's weakly-typed API.
libraryDependencies ++= Seq(
"io.getquill" %% "quill-spark" % "3.1.1-SNAPSHOT"
)
Differently from the other modules, the context is a companion object and doesn't depend on a spark session. To use it, add the following import:
import io.getquill.QuillSparkContext._The spark session must be provided by the user through an implicit value:
import org.apache.spark.sql.SparkSession
// Replace by your spark sql context creation
implicit lazy val sqlContext =
SparkSession.builder().master("local").appName("spark test").getOrCreate().sqlContextQuill decoders and meta instances are not used by the quill-spark module, Spark's Encoders are used instead. Add this import to have them in scope:
import sqlContext.implicits._The liftQuery method converts Datasets to Quill queries:
import org.apache.spark.sql.Dataset
def filter(myDataset: Dataset[Person], name: String): Dataset[Int] =
run {
liftQuery(myDataset).filter(_.name == lift(name)).map(_.age)
}
// SELECT x1.age _1 FROM (?) x1 WHERE x1.name = ?Note that the run method returns a Dataset transformed by the Quill query using the SQL engine.
Additionally, note that the queries printed from run(myQuery) during compile time escape question marks via a backslash them in order to
be able to substitute liftings properly. They are then returned back to their original form before running.
import org.apache.spark.sql.Dataset
def filter(myDataset: Dataset[Person]): Dataset[Int] =
run {
liftQuery(myDataset).filter(_.name == "?").map(_.age)
}
// This is generated during compile time:
// SELECT x1.age _1 FROM (?) x1 WHERE x1.name = '\?'
// It is reverted upon run-time:
// SELECT x1.age _1 FROM (ds1) x1 WHERE x1.name = '?'Important: Spark doesn't support transformations of inner classes. Use top-level classes.
Example:
lazy val ctx = new MysqlJdbcContext(SnakeCase, "ctx")The SQL dialect parameter defines the specific database dialect to be used. Some context types are specific to a database and thus not require it.
Quill has five built-in dialects:
io.getquill.H2Dialectio.getquill.MySQLDialectio.getquill.PostgresDialectio.getquill.SqliteDialectio.getquill.SQLServerDialectio.getquill.OracleDialect
The naming strategy parameter defines the behavior when translating identifiers (table and column names) to SQL.
| strategy | example |
|---|---|
io.getquill.naming.Literal |
some_ident -> some_ident |
io.getquill.naming.Escape |
some_ident -> "some_ident" |
io.getquill.naming.UpperCase |
some_ident -> SOME_IDENT |
io.getquill.naming.LowerCase |
SOME_IDENT -> some_ident |
io.getquill.naming.SnakeCase |
someIdent -> some_ident |
io.getquill.naming.CamelCase |
some_ident -> someIdent |
io.getquill.naming.MysqlEscape |
some_ident -> `some_ident` |
io.getquill.naming.PostgresEscape |
$some_ident -> $some_ident |
Multiple transformations can be defined using NamingStrategy(). For instance, the naming strategy
NamingStrategy(SnakeCase, UpperCase)
produces the following transformation:
someIdent -> SOME_IDENT
The transformations are applied from left to right.
The string passed to the context is used as the key to obtain configurations using the typesafe config library.
Additionally, the contexts provide multiple constructors. For instance, with JdbcContext it's possible to specify a DataSource directly, without using the configuration:
def createDataSource: javax.sql.DataSource with java.io.Closeable = ???
lazy val ctx = new MysqlJdbcContext(SnakeCase, createDataSource)The quill-jdbc module provides a simple blocking JDBC context for standard use-cases. For transactions, the JDBC connection is kept in a thread-local variable.
Quill uses HikariCP for connection pooling. Please refer to HikariCP's documentation for a detailed explanation of the available configurations.
Note that there are dataSource configurations, that go under dataSource, like user and password, but some pool settings may go under the root config, like connectionTimeout.
The JdbcContext provides thread-local transaction support:
ctx.transaction {
ctx.run(query[Person].delete)
// other transactional code
}
The body of transaction can contain calls to other methods and multiple run calls, since the transaction is propagated through a thread-local.
libraryDependencies ++= Seq(
"mysql" % "mysql-connector-java" % "8.0.15",
"io.getquill" %% "quill-jdbc" % "3.1.1-SNAPSHOT"
)
lazy val ctx = new MysqlJdbcContext(SnakeCase, "ctx")ctx.dataSourceClassName=com.mysql.cj.jdbc.MysqlDataSource
ctx.dataSource.url=jdbc:mysql://host/database
ctx.dataSource.user=root
ctx.dataSource.password=root
ctx.dataSource.cachePrepStmts=true
ctx.dataSource.prepStmtCacheSize=250
ctx.dataSource.prepStmtCacheSqlLimit=2048
ctx.connectionTimeout=30000
libraryDependencies ++= Seq(
"org.postgresql" % "postgresql" % "9.4.1208",
"io.getquill" %% "quill-jdbc" % "3.1.1-SNAPSHOT"
)
lazy val ctx = new PostgresJdbcContext(SnakeCase, "ctx")ctx.dataSourceClassName=org.postgresql.ds.PGSimpleDataSource
ctx.dataSource.user=root
ctx.dataSource.password=root
ctx.dataSource.databaseName=database
ctx.dataSource.portNumber=5432
ctx.dataSource.serverName=host
ctx.connectionTimeout=30000
libraryDependencies ++= Seq(
"org.xerial" % "sqlite-jdbc" % "3.18.0",
"io.getquill" %% "quill-jdbc" % "3.1.1-SNAPSHOT"
)
lazy val ctx = new SqliteJdbcContext(SnakeCase, "ctx")ctx.driverClassName=org.sqlite.JDBC
ctx.jdbcUrl=jdbc:sqlite:/path/to/db/file.db
libraryDependencies ++= Seq(
"com.h2database" % "h2" % "1.4.192",
"io.getquill" %% "quill-jdbc" % "3.1.1-SNAPSHOT"
)
lazy val ctx = new H2JdbcContext(SnakeCase, "ctx")ctx.dataSourceClassName=org.h2.jdbcx.JdbcDataSource
ctx.dataSource.url=jdbc:h2:mem:yourdbname
ctx.dataSource.user=sa
libraryDependencies ++= Seq(
"com.microsoft.sqlserver" % "mssql-jdbc" % "6.1.7.jre8-preview",
"io.getquill" %% "quill-jdbc" % "3.1.1-SNAPSHOT"
)
lazy val ctx = new SqlServerJdbcContext(SnakeCase, "ctx")Quill supports Oracle version 12c and up although due to licensing restrictions, version 18c XE is used for testing.
Note that the latest Oracle JDBC drivers are not publicly available. In order to get them, you will need to connect to Oracle's private maven repository as instructed here. Unfortunately, this procedure currently does not work for SBT. There are various workarounds available for this situation here.
libraryDependencies ++= Seq(
"com.oracle.jdbc" % "ojdbc8" % "18.3.0.0.0",
"io.getquill" %% "quill-jdbc" % "3.1.1-SNAPSHOT"
)
lazy val ctx = new OracleJdbcContext(SnakeCase, "ctx")ctx.dataSourceClassName=com.microsoft.sqlserver.jdbc.SQLServerDataSource
ctx.dataSource.user=user
ctx.dataSource.password=YourStrongPassword
ctx.dataSource.databaseName=database
ctx.dataSource.portNumber=1433
ctx.dataSource.serverName=host
The quill-jdbc-monix module integrates the Monix asynchronous programming framework with Quill,
supporting all of the database vendors of the quill-jdbc module.
The Quill Monix contexts encapsulate JDBC Queries and Actions into Monix Tasks
and also include support for streaming queries via Observable.
The MonixJdbcContext can stream using Monix Observables:
ctx.stream(query[Person]) // returns: Observable[Person]
.foreachL(println(_))
.runSyncUnsafe()
The MonixJdbcContext provides support for transactions by storing the connection into a Monix Local.
This process is designed to be completely transparent to the user. As with the other contexts,
if an exception is thrown anywhere inside a task or sub-task within a transaction block, the entire block
will be rolled back by the database.
Basic syntax:
val trans =
ctx.transaction {
for {
_ <- ctx.run(query[Person].delete)
_ <- ctx.run(query[Person].insert(Person("Joe", 123)))
p <- ctx.run(query[Person])
} yield p
} //returns: Task[List[Person]]
val result = trans.runSyncUnsafe() //returns: List[Person]
Streaming can also be done inside of transaction block so long as the result is converted to a task beforehand.
val trans =
ctx.transaction {
for {
_ <- ctx.run(query[Person].insert(Person("Joe", 123)))
ppl <- ctx
.stream(query[Person]) // Observable[Person]
.foldLeftL(List[Person]())({case (l, p) => p +: l}) // ... becomes Task[List[Person]]
} yield ppl
} //returns: Task[List[Person]]
val result = trans.runSyncUnsafe() //returns: List[Person]
Use a Runner object to create the different MonixJdbcContexts.
The Runner does the actual wrapping of JDBC calls into Monix Tasks.
import monix.execution.Scheduler
import io.getquill.context.monix.Runner
// You can use the default Runner when constructing a Monix jdbc contexts.
// The resulting tasks will be wrapped with whatever Scheduler is
// defined when you do task.syncRunUnsafe(), typically a global implicit.
lazy val ctx = new MysqlMonixJdbcContext(SnakeCase, "ctx", Runner.default)
// However...
// Monix strongly suggests that you use a separate thread pool for database IO
// operations. `Runner` provides a convenience method in order to do this.
lazy val ctx = new MysqlMonixJdbcContext(SnakeCase, "ctx", Runner.using(Scheduler.io()))libraryDependencies ++= Seq(
"mysql" % "mysql-connector-java" % "8.0.15",
"io.getquill" %% "quill-jdbc-monix" % "3.1.1-SNAPSHOT"
)
lazy val ctx = new MysqlMonixJdbcContext(SnakeCase, "ctx", Runner.default)ctx.dataSourceClassName=com.mysql.cj.jdbc.MysqlDataSource
ctx.dataSource.url=jdbc:mysql://host/database
ctx.dataSource.user=root
ctx.dataSource.password=root
ctx.dataSource.cachePrepStmts=true
ctx.dataSource.prepStmtCacheSize=250
ctx.dataSource.prepStmtCacheSqlLimit=2048
ctx.connectionTimeout=30000
libraryDependencies ++= Seq(
"org.postgresql" % "postgresql" % "9.4.1208",
"io.getquill" %% "quill-jdbc-monix" % "3.1.1-SNAPSHOT"
)
lazy val ctx = new PostgresMonixJdbcContext(SnakeCase, "ctx", Runner.default)ctx.dataSourceClassName=org.postgresql.ds.PGSimpleDataSource
ctx.dataSource.user=root
ctx.dataSource.password=root
ctx.dataSource.databaseName=database
ctx.dataSource.portNumber=5432
ctx.dataSource.serverName=host
ctx.connectionTimeout=30000
libraryDependencies ++= Seq(
"org.xerial" % "sqlite-jdbc" % "3.18.0",
"io.getquill" %% "quill-jdbc-monix" % "3.1.1-SNAPSHOT"
)
lazy val ctx = new SqliteMonixJdbcContext(SnakeCase, "ctx", Runner.default)ctx.driverClassName=org.sqlite.JDBC
ctx.jdbcUrl=jdbc:sqlite:/path/to/db/file.db
libraryDependencies ++= Seq(
"com.h2database" % "h2" % "1.4.192",
"io.getquill" %% "quill-jdbc-monix" % "3.1.1-SNAPSHOT"
)
lazy val ctx = new H2MonixJdbcContext(SnakeCase, "ctx", Runner.default)ctx.dataSourceClassName=org.h2.jdbcx.JdbcDataSource
ctx.dataSource.url=jdbc:h2:mem:yourdbname
ctx.dataSource.user=sa
libraryDependencies ++= Seq(
"com.microsoft.sqlserver" % "mssql-jdbc" % "6.1.7.jre8-preview",
"io.getquill" %% "quill-jdbc-monix" % "3.1.1-SNAPSHOT"
)
lazy val ctx = new SqlServerMonixJdbcContext(SnakeCase, "ctx", Runner.default)ctx.dataSourceClassName=com.microsoft.sqlserver.jdbc.SQLServerDataSource
ctx.dataSource.user=user
ctx.dataSource.password=YourStrongPassword
ctx.dataSource.databaseName=database
ctx.dataSource.portNumber=1433
ctx.dataSource.serverName=host
Quill supports Oracle version 12c and up although due to licensing restrictions, version 18c XE is used for testing.
Note that the latest Oracle JDBC drivers are not publicly available. In order to get them, you will need to connect to Oracle's private maven repository as instructed here. Unfortunately, this procedure currently does not work for SBT. There are various workarounds available for this situation here.
libraryDependencies ++= Seq(
"com.oracle.jdbc" % "ojdbc7" % "12.1.0.2",
"io.getquill" %% "quill-jdbc-monix" % "3.1.1-SNAPSHOT"
)
lazy val ctx = new OracleJdbcContext(SnakeCase, "ctx")ctx.dataSourceClassName=oracle.jdbc.xa.client.OracleXADataSource
ctx.dataSource.databaseName=xe
ctx.dataSource.user=database
ctx.dataSource.password=YourStrongPassword
ctx.dataSource.driverType=thin
ctx.dataSource.portNumber=1521
ctx.dataSource.serverName=host
The quill-async module provides simple async support for MySQL and Postgres databases.
The async module provides transaction support based on a custom implicit execution context:
ctx.transaction { implicit ec =>
ctx.run(query[Person].delete)
// other transactional code
}
The body of transaction can contain calls to other methods and multiple run calls, but the transactional code must be done using the provided implicit execution context. For instance:
def deletePerson(name: String)(implicit ec: ExecutionContext) =
ctx.run(query[Person].filter(_.name == lift(name)).delete)
ctx.transaction { implicit ec =>
deletePerson("John")
}
Depending on how the main execution context is imported, it is possible to produce an ambigous implicit resolution. A way to solve this problem is shadowing the multiple implicits by using the same name:
import scala.concurrent.ExecutionContext.Implicits.{ global => ec }
def deletePerson(name: String)(implicit ec: ExecutionContext) =
ctx.run(query[Person].filter(_.name == lift(name)).delete)
ctx.transaction { implicit ec =>
deletePerson("John")
}
Note that the global execution context is renamed to ec.
ctx.host=host
ctx.port=1234
ctx.user=root
ctx.password=root
ctx.database=database
or use connection URL with database-specific scheme (see below):
ctx.url=scheme://host:5432/database?user=root&password=root
ctx.poolMaxQueueSize=4
ctx.poolMaxObjects=4
ctx.poolMaxIdle=999999999
ctx.poolValidationInterval=10000
Also see PoolConfiguration documentation.
ctx.sslmode=disable # optional, one of [disable|prefer|require|verify-ca|verify-full]
ctx.sslrootcert=./path/to/cert/file # optional, required for sslmode=verify-ca or verify-full
ctx.charset=UTF-8
ctx.maximumMessageSize=16777216
ctx.connectTimeout=5s
ctx.testTimeout=5s
ctx.queryTimeout=10m
libraryDependencies ++= Seq(
"io.getquill" %% "quill-async-mysql" % "3.1.1-SNAPSHOT"
)
lazy val ctx = new MysqlAsyncContext(SnakeCase, "ctx")See above
For url property use mysql scheme:
ctx.url=mysql://host:3306/database?user=root&password=root
libraryDependencies ++= Seq(
"io.getquill" %% "quill-async-postgres" % "3.1.1-SNAPSHOT"
)
lazy val ctx = new PostgresAsyncContext(SnakeCase, "ctx")For url property use postgresql scheme:
ctx.url=postgresql://host:5432/database?user=root&password=root
Support for the Twitter Finagle library is available with MySQL and Postgres databases.
The finagle context provides transaction support through a Local value. See twitter util's scaladoc for more details.
ctx.transaction {
ctx.run(query[Person].delete)
// other transactional code
}
The body of transaction can contain calls to other methods and multiple run calls, since the transaction is automatically propagated through the Local value.
libraryDependencies ++= Seq(
"io.getquill" %% "quill-finagle-mysql" % "3.1.1-SNAPSHOT"
)
lazy val ctx = new FinagleMysqlContext(SnakeCase, "ctx")ctx.dest=localhost:3306
ctx.user=root
ctx.password=root
ctx.database=database
ctx.pool.watermark.low=0
ctx.pool.watermark.high=10
ctx.pool.idleTime=5 # seconds
ctx.pool.bufferSize=0
ctx.pool.maxWaiters=2147483647
The finagle context provides transaction support through a Local value. See twitter util's scaladoc for more details.
ctx.transaction {
ctx.run(query[Person].delete)
// other transactional code
}
The body of transaction can contain calls to other methods and multiple run calls, since the transaction is automatically propagated through the Local value.
libraryDependencies ++= Seq(
"io.getquill" %% "quill-finagle-postgres" % "3.1.1-SNAPSHOT"
)
lazy val ctx = new FinaglePostgresContext(SnakeCase, "ctx")ctx.host=localhost:3306
ctx.user=root
ctx.password=root
ctx.database=database
ctx.useSsl=false
ctx.hostConnectionLimit=1
ctx.numRetries=4
ctx.binaryResults=false
ctx.binaryParams=false
libraryDependencies ++= Seq(
"io.getquill" %% "quill-cassandra" % "3.1.1-SNAPSHOT"
)
lazy val ctx = new CassandraSyncContext(SnakeCase, "ctx")lazy val ctx = new CassandraAsyncContext(SnakeCase, "ctx")The configurations are set using runtime reflection on the Cluster.builder instance. It is possible to set nested structures like queryOptions.consistencyLevel, use enum values like LOCAL_QUORUM, and set multiple parameters like in credentials.
ctx.keyspace=quill_test
ctx.preparedStatementCacheSize=1000
ctx.session.contactPoint=127.0.0.1
ctx.session.withPort=9042
ctx.session.queryOptions.consistencyLevel=LOCAL_QUORUM
ctx.session.withoutMetrics=true
ctx.session.withoutJMXReporting=false
ctx.session.credentials.0=root
ctx.session.credentials.1=pass
ctx.session.maxSchemaAgreementWaitSeconds=1
ctx.session.addressTranslator=com.datastax.driver.core.policies.IdentityTranslator
libraryDependencies ++= Seq(
"io.getquill" %% "quill-cassandra-monix" % "3.1.1-SNAPSHOT"
)
lazy val ctx = new CassandraMonixContext(SnakeCase, "ctx")lazy val ctx = new CassandraStreamContext(SnakeCase, "ctx")libraryDependencies ++= Seq(
"io.getquill" %% "quill-orientdb" % "3.1.1-SNAPSHOT"
)
lazy val ctx = new OrientDBSyncContext(SnakeCase, "ctx")The configurations are set using OPartitionedDatabasePool which creates a pool of DB connections from which an instance of connection can be acquired. It is possible to set DB credentials using the parameter called username and password.
ctx.dbUrl=remote:127.0.0.1:2424/GratefulDeadConcerts
ctx.username=root
ctx.password=root
To disable logging of queries during compilation use quill.macro.log option:
sbt -Dquill.macro.log=false
Quill uses SLF4J for logging. Each context logs queries which are currently executed.
It also logs the list of parameters which are bound into prepared statement if any.
To enable that use quill.binds.log option:
java -Dquill.binds.log=true -jar myapp.jar
In order to quickly start with Quill, we have setup some template projects:
Please refer to SLICK.md for a detailed comparison between Quill and Slick.
Please refer to CASSANDRA.md for a detailed comparison between Quill and other main alternatives for interaction with Cassandra in Scala.
- scala-db-codegen - Code/boilerplate generator from db schema
- quill-cache - Caching layer for Quill
- quill-gen - a DAO generator for
quill-cache
ScalaDays Berlin 2016 - Scylla, Charybdis, and the mystery of Quill
quill-spark: A type-safe Scala API for Spark SQL Scalac.io blog - Compile-time Queries with Quill
Please note that this project is released with a Contributor Code of Conduct. By participating in this project you agree to abide by its terms. See CODE_OF_CONDUCT.md for details.
See the LICENSE file for details.
- @deusaquilus (lead maintainer)
- @fwbrasil (creator)
- @jilen
- @juliano
- @mentegy
- @mxl
- @gustavoamigo
- @godenji
- @lvicentesanchez
You can notify all current maintainers using the handle @getquill/maintainers.
The project was created having Philip Wadler's talk "A practical theory of language-integrated query" as its initial inspiration. The development was heavily influenced by the following papers: