Skip to content

FAQ

Jump to bottom
Alexander Ioffe edited this page Dec 30, 2021 · 4 revisions

Responses to various questions.

Over the years I've realized we need a place to compile repsonsa to various interesting questions and Gists are wholly insufficient for this. I will try to create a space for that here.

How can I make a read-only connection.

This depends on your DB vendor and connection pool. If you are using Postgres with Hikari you should ideally only need to set this setting on the Hikari-layer but because of some configuration bugs you need to set it on the Datasource too. If you are using an application.conf add the following settings.

# Application.conf
testPostgresDB.readOnly=true
testPostgresDB.dataSource.readOnly=true
testPostgresDB.dataSource.readOnlyMode=always

Some relevant resources:

How do you write/query a jsonb column in postgres.

You will need to implement a custom encoder/decoder for this.

import io.circe.parser
import io.circe.Json
import io.getquill.{PostgresZioJdbcContext, SnakeCase}

object PostgresQuillContext extends PostgresZioJdbcContext(SnakeCase) {

    implicit val jsonEncoder: Encoder[Json] = encoder(
      java.sql.Types.OTHER,
      (index, json, row) => {
        val pgobj = new org.postgresql.util.PGobject()
        pgobj.setType("jsonb")
        pgobj.setValue(json.noSpaces)
        row.setObject(index, pgobj)
      }
    )

    implicit val jsonDecoder: Decoder[Json] =
      decoder((index, row, session) => {
        parser
          .parse(
            row.getObject(index, classOf[org.postgresql.util.PGobject]).getValue
          )
          .getOrElse(Json.Null)
      })
  }

Also, check out quill-pg by Matthew de Detrich that has many example implementations of this!

How do you get a min and max of a column on a single table with Quill

Normally you can do this with a query like this:

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

run { 
  query[Person].groupBy(p => p.name).map { 
    case (_, names) => 
      (names.map(_.age).min, names.map(_.age).max) 
  }  
}

If you want to use infixes, in order to do something like this:

SELECT MIN(id), MAX(id) FROM foo;

and trying to represent it with Quill as follows:

infix"""SELECT MIN(id), MAX(id) FROM foo""".as[Query[(Int, Int)]]

but Quill generates this SQL which is invalid

SELECT x._1, x._2 FROM (SELECT MIN(id), MAX(id) FROM foo) AS x

So you need to work around that and do something like this:

infix"""SELECT MIN(id) AS _1, MAX(id) AS _2 FROM foo""".as[Query[(Int, Int)]]

Is there a way to escape the ? in a Query?

For example, if you want to use a regex matcher in an infix query builder?

val searchString = "joe"
val pattern = s"""(?qi)$searchString"""
query[User]
  .filter { u =>
    infix"${u.firstName} ~ ${lift(pattern)}".pure.as[Boolean] ||
    infix"${u.lastName} ~ ${lift(pattern)}".pure.as[Boolean] ||
    infix"${u.email} ~ ${lift(pattern)}".pure.as[Boolean]
  }

To work around this, avoid including '?' in the infix definition.

infix"""regexp_match(${u.firstName}, ${lift(searchString)}, 'qi') is not null""".pure.as[Boolean]
Clone this wiki locally