Custom Jooq Types

This is the second part of the two-part series on writing a custom Scala class to represent the tstzrange column type in Postgres. Find the first part here.

Here I’m placing this binding class in the same file as the tstzrange case class:

import java.sql.{SQLFeatureNotSupportedException, Types}
import java.time.{Instant, ZonedDateTime}
import java.util.Objects
 
import org.jooq._
import org.jooq.impl.DSL
 
 
class PostgresTimestampTimezoneRangeBinding extends Binding[Object, TsTzRange] {
  // This regex captures start and end bounds (as either closed or open) and the two values inside
  // We use a lazy quantifier to avoid capturing beyond the next match
  private val rangeRegex = raw"([\[\(])(.*?),(.*?)([\]\)])".r
  override def converter: Converter[Object, TsTzRange] = new Converter[Object, TsTzRange]() {
    override def from(t: Object): TsTzRange =
      if (t == null) null
      else {
        t.toString match {
          case rangeRegex(startBound, start, end, endBound) =>
            val startInstant: Option[Instant] =
              if (start == "") None
              else Some(ZonedDateTime.parse(start.replace("\"", "").replace(" ", "T") + ":00").toInstant)
            val endInstant: Option[Instant] =
              if (end == "") None
              else Some(ZonedDateTime.parse(end.replace("\"", "").replace(" ", "T") + ":00").toInstant)
            TsTzRange(startInstant, endInstant, startBound == "[", endBound == "]")
        }
      }
 
    override def to(u: TsTzRange): Object =
      if (u == null) null
      else {
        val startBound = if (u.startInclusive) "[" else "("
        val endBound = if (u.endInclusive) "]" else ")"
        val start = u.start.map(_.toString).getOrElse("")
        val end = u.end.map(_.toString).getOrElse("")
        s"$startBound$start,$end$endBound"
      }
 
    override def fromType: Class[Object] =
      classOf[Object]
 
    override def toType: Class[TsTzRange] =
      classOf[TsTzRange]
  }
 
  override def sql(ctx: BindingSQLContext[TsTzRange]): Unit =
    ctx.render.visit(DSL.`val`(ctx.convert[Object](converter).value)).sql("::tstzrange")
 
  override def register(ctx: BindingRegisterContext[TsTzRange]): Unit =
    ctx.statement.registerOutParameter(ctx.index, Types.OTHER)
 
  override def set(ctx: BindingSetStatementContext[TsTzRange]): Unit =
    ctx.statement.setString(ctx.index, Objects.toString(ctx.convert[Object](converter).value, null))
 
  override def get(ctx: BindingGetResultSetContext[TsTzRange]): Unit =
    ctx.convert(converter).value(ctx.resultSet.getString(ctx.index))
 
  override def get(ctx: BindingGetStatementContext[TsTzRange]): Unit =
    ctx.convert(converter).value(ctx.statement.getString(ctx.index))
 
  override def set(ctx: BindingSetSQLOutputContext[TsTzRange]): Unit =
    throw new SQLFeatureNotSupportedException
 
  override def get(ctx: BindingGetSQLInputContext[TsTzRange]): Unit =
    throw new SQLFeatureNotSupportedException
}

A note about the parts of the implementation highlighted above.

First, an explanation of the regex. The Postgres string representation of tstzrange looks something like this: ["2020-08-21 00:00:00+00","2020-09-15 00:00:00+00"). So what we want to do is to capture the opening and closing brackets, as well as whatever is between the bracket and the comma separator. Note that we don’t match on the literal double quotes, since single-unbounded ranges don’t have them, e.g. ["2020-08-21 00:00:00+00",) I’m sure there is a way to do it with pure regex, but it’s more straightforward to just remove them with code.

In the next 2 highlighted lines, we do what seems like a brittle string munging code to turn the timestamp string into a ISO-8601 string that Java likes. The reason I chose to do this is because I want to rely on Java’s built-in parsers. It’s not easy to define a custom format with SimpleDateFormat or DateTimeFormatter because Postgres timestamps have variable precision.

See Section 8.5.2 Date/Time Output in the Postgres documentation and this Stack Overflow answer for more background on whether replacing the T separator with a space is still considered ISO-8601/RFC3339. Regardless, Java parsers only like timestamp strings with the T separator.

ISO 8601 specifies the use of uppercase letter T to separate the date and time. PostgreSQL accepts that format on input, but on output it uses a space rather than T, as shown above. This is for readability and for consistency with RFC 3339 as well as some other database systems.

See also the Java documentation here. Java only accepts timezone offsets with hh:mm, not just hh. In my opinion, we don’t have to worry about double appending the :00 portion, since Postgres normalizes all timestamps to UTC on write.

This is an example of how the transformation works:

"2020-01-01 00:00:00.000+08"
2020-01-01 00:00:00.000+08
2020-01-01T00:00:00.000+08:00

And finally, in your build.scala, add the highlighted lines to your existing Jooq generation config:

object Settings {
  lazy val jooqGen: Seq[Def.Setting[_]] = Seq(
    libraryDependencies ++= Seq(
      "org.postgresql" % "postgresql" % Versions.postgres % "jooq"
    ),
    jooqVersion := Versions.jooq,
    jooqCodegenConfig :=
      <configuration>
        <jdbc>
          <driver>org.postgresql.Driver</driver>
          <url>{
        System.getenv().getOrDefault("JDBC_DATABASE_URL", s"jdbc:postgresql://localhost:5432/mydb?user=postgres")
      }</url>
        </jdbc>
        <generator>
          <name>org.jooq.util.JooqGenerator</name>
          <strategy>
            <name>misc.JooqStrategy</name>
          </strategy>
          <database>
            <name>org.jooq.util.postgres.PostgresDatabase</name>
            <schemata>
              <schema>
                <inputSchema>public</inputSchema>
                <outputSchema>public</outputSchema>
              </schema>
            </schemata>
            <customTypes>
              <customType>
                <name>TimestampTimezoneRange</name>
                <type>misc.TsTzRange</type>
                <binding>misc.PostgresTimestampTimezoneRangeBinding</binding>
              </customType>
             <forcedTypes>
              <forcedType>
                <name>TimestampTimezoneRange</name>
                <types>tstzrange</types>
              </forcedType>
            </forcedTypes>
           </customTypes>
          </database>
          <generate>
            <deprecated>false</deprecated>
            <records>true</records>
            <immutablePojos>true</immutablePojos>
            <fluentSetters>true</fluentSetters>
            <daos>true</daos>
            <validationAnnotations>true</validationAnnotations>
            <routines>false</routines>
          </generate>
          <target>
            <packageName>model.jooq</packageName>
            <directory>{jooqCodegenTargetDirectory.value}</directory>
          </target>
        </generator>
    </configuration>,
      sources in doc in Compile := Seq.empty
  ) ++
    Defaults.packageTaskSettings(packageSrc, jooqCodegenTargetDirectory.map(Path.allSubpaths(_).toSeq)) ++ common
}

Finally, this can be used like so:

val results = dsl
  .select()
  .from(APPOINTMENT)
  .fetch$
  .map(_.into(APPOINTMENT))
 
println(results.head.appointmentTime) // TsTzRange(Some(2020-08-19T08:12:56.320Z),true,Some(2021-08-19T00:14:36.320Z),true)
 
dsl
  .insertInto(
    APPOINTMENT,
    APPOINTMENT.APPOINTMENT_TIME
  )
  .values(
    TsTzRange(Some(Instant.now), true, Some(Instant.now.plusSeconds(1000)), true)
  )
  .execute()