Quill Integration

As of version 0.7 doobie now lets us use Quill quotes to construct ConnectionIO programs. Quill provides statement construction and type mapping, and doobie takes care of statement execution.

In order to use this feature we need to add the following dependency, which pulls in quill-jdbc 3.4.10 transitively.

libraryDependencies += "org.tpolecat" %% "doobie-quill" % "0.8.8"

The examples below require the following imports.

import io.getquill.{ idiom => _, _ }
import doobie.quill.DoobieContext

We can now construct a DoobieContext for our back-end database and import its members, as we would with a traditional Quill context. The options are H2, MySQL, Oracle, Postgres, SQLite, and SQLServer.

val dc = new DoobieContext.Postgres(Literal) // Literal naming scheme
import dc._

We will be using the country table from our test database, so we need a data type of that name, with fields whose names and types line up with the table definition.

case class Country(code: String, name: String, population: Int)

We’re now ready to construct doobie programs using Quill quotes.

Note: this is a demonstration of integration and isn’t intended to be a Quill tutorial. See the Quill documentation for information about statement construction.

Examples

Following are some examples. Note the return types from run, which are normal doobie types. You can freely mix Quill quotes into existing doobie programs.

Here is a query.

val q1 = quote { query[Country].filter(_.code == "GBR") }
// q1: AnyRef with Quoted[EntityQuery[Country]]{def quoted: io.getquill.ast.Filter;def ast: io.getquill.ast.Filter;def id50738446(): Unit;val liftings: Object} = querySchema("Country").filter(x2 => x2.code == "GBR")

// Select all at once
run(q1)
// res0: doobie.package.ConnectionIO[List[Country]] = Suspend(
//   BracketCase(
//     Suspend(
//       PrepareStatement(
//         "SELECT x2.code, x2.name, x2.population FROM Country x2 WHERE x2.code = 'GBR'"
//       )
//     ),
//     doobie.hi.connection$$$Lambda$8172/902966097@1117263f,
//     cats.effect.Bracket$$Lambda$8174/1752249240@6fd4d6c3
//   )
// )

// Stream in chunks of 16
stream(q1, 16)
// res1: fs2.Stream[doobie.package.ConnectionIO, Country] = Stream(..)

A simple update.

val u1 = quote { query[Country].filter(_.name like "U%").update(_.name -> "foo") }
// u1: AnyRef with Quoted[Update[Country]]{def quoted: io.getquill.ast.Update;def ast: io.getquill.ast.Update;def id1396135128(): Unit;val liftings: Object} = querySchema("Country").filter(x3 => infix"${x3.name} like ${"U%"}").update(x4 => x4.name -> "foo")

// Update yielding count of affected rows
run(u1)
// res2: doobie.package.ConnectionIO[Long] = Suspend(
//   BracketCase(
//     Suspend(
//       PrepareStatement("UPDATE Country SET name = 'foo' WHERE name like 'U%'")
//     ),
//     doobie.hi.connection$$$Lambda$8172/902966097@1713a04c,
//     cats.effect.Bracket$$Lambda$8174/1752249240@8502c3a
//   )
// )

A batch update.

val u2 = quote {
  liftQuery(List("U%", "A%")).foreach { pat =>
    query[Country].filter(_.name like pat).update(_.name -> "foo")
  }
}
// u2: AnyRef with Quoted[BatchAction[Update[Country]]]{def quoted: io.getquill.ast.Foreach;def ast: io.getquill.ast.Foreach;def id519296784(): Unit;val liftings: AnyRef{val scala.collection.immutable.List.apply[String]("U%", "A%"): io.getquill.quotation.ScalarValueLifting[List[String],String]}} = ?.foreach((pat) => querySchema("Country").filter(x5 => infix"${x5.name} like $pat").update(x6 => x6.name -> "foo"))

// Update yielding list of counts of affected rows
run(u2)
// res3: doobie.package.ConnectionIO[List[Long]] = FlatMapped(
//   FlatMapped(
//     Suspend(
//       BracketCase(
//         Suspend(
//           PrepareStatement("UPDATE Country SET name = 'foo' WHERE name like ?")
//         ),
//         doobie.hi.connection$$$Lambda$8172/902966097@66e96a2,
//         cats.effect.Bracket$$Lambda$8174/1752249240@48101194
//       )
//     ),
//     cats.FlatMap$$Lambda$8343/722281023@428bd91a
//   ),
//   cats.Monad$$Lambda$8144/773217938@4bb31c8d
// )

Now we will look at batch updates with generated keys. For this we will create a new table.

CREATE TABLE Foo (
  id    SERIAL,
  value VARCHAR(42)
)

And a related data type.

case class Foo(id: Int, value: String)

We can now write an update returning generated keys.

val u3 = quote {
  query[Foo].insert(lift(Foo(0, "Joe"))).returning(_.id)
}
// u3: AnyRef with Quoted[ActionReturning[Foo, Int]]{def quoted: io.getquill.ast.Returning;def ast: io.getquill.ast.Returning;def id1302982979(): Unit;val liftings: AnyRef{val App.this.Foo.apply(0, "Joe").id: io.getquill.quotation.ScalarValueLifting[Int,Int]; val App.this.Foo.apply(0, "Joe").value: io.getquill.quotation.ScalarValueLifting[String,String]}} = querySchema("Foo").insert(v => v.id -> ?, v => v.value -> ?).returning((x7) => x7.id)

// Update yielding a single id
run(u3)
// res4: doobie.package.ConnectionIO[Int] = Suspend(
//   BracketCase(
//     Suspend(
//       PrepareStatement3(
//         "INSERT INTO Foo (id,value) VALUES (?, ?) RETURNING id",
//         1
//       )
//     ),
//     doobie.hi.connection$$$Lambda$9198/581532532@399a48df,
//     cats.effect.Bracket$$Lambda$8174/1752249240@714e72c9
//   )
// )

And a batch update returning generated keys.

val u4 = quote {
  liftQuery(List(Foo(0, "Joe"), Foo(0, "Bob"))).foreach { a =>
    query[Foo].insert(a).returning(_.id)
  }
}
// u4: AnyRef with Quoted[BatchAction[ActionReturning[Foo, Int]]]{def quoted: io.getquill.ast.Foreach;def ast: io.getquill.ast.Foreach;def id1086843774(): Unit;val liftings: AnyRef{val scala.collection.immutable.List.apply[repl.Session.App.Foo](App.this.Foo.apply(0, "Joe"), App.this.Foo.apply(0, "Bob")): io.getquill.quotation.CaseClassValueLifting[List[repl.Session.App.Foo]]}} = ?.foreach((a) => querySchema("Foo").insert(v => v.id -> a.id, v => v.value -> a.value).returning((x8) => x8.id))

// Update yielding a list of ids
run(u4)
// res5: doobie.package.ConnectionIO[List[Int]] = FlatMapped(
//   FlatMapped(
//     Suspend(
//       BracketCase(
//         Suspend(
//           PrepareStatement3(
//             "INSERT INTO Foo (id,value) VALUES (?, ?) RETURNING id",
//             1
//           )
//         ),
//         doobie.hi.connection$$$Lambda$9198/581532532@6d1126f3,
//         cats.effect.Bracket$$Lambda$8174/1752249240@3e9518a8
//       )
//     ),
//     cats.FlatMap$$Lambda$8343/722281023@55227315
//   ),
//   cats.Monad$$Lambda$8144/773217938@57789b42
// )