15. Extensions for PostgreSQL

In this chapter we discuss the extended support that doobie offers for users of PostgreSQL. To use these extensions you must add an additional dependency to your project:

libraryDependencies += "org.tpolecat" %% "doobie-postgres" % doobieVersion

This library pulls in PostgreSQL JDBC Driver 9.4 as a transitive dependency.

Setting Up

The following examples require a few imports.

import doobie.imports._
import cats._, cats.data._, cats.implicits._
import fs2.interop.cats._
val xa = DriverManagerTransactor[IOLite](
  "org.postgresql.Driver", "jdbc:postgresql:world", "postgres", ""
)
val y = xa.yolo; import y._

doobie adds support for a large number of extended types that are not supported directly by JDBC. All mappings (except postgis) are provided in the pgtypes module.

import doobie.postgres.imports._

Array Types

doobie supports single-dimensional arrays of the following types:

In addition to Array you can also map to List and Vector. Note that arrays of advanced types and structs are not supported by the driver; arrays of Byte are represented as bytea; and arrays of int2 are incorrectly mapped by the driver as Array[Int] rather than Array[Short] and are not supported in doobie.

See the previous chapter on SQL Arrays for usage examples.

Enum Types

doobie supports mapping PostgreSQL enum types to Scala enumerated types, with the slight complication that Scala doesn’t really support enumerated types as a first-class notion. We will examine three ways to construct mappings for the following PostgreSQL type:

create type myenum as enum ('foo', 'bar')

The first option is to map myenum to an instance of the execrable scala.Enumeration class via the pgEnum constructor.

object MyEnum extends Enumeration {
  val foo, bar = Value
}

implicit val MyEnumMeta = pgEnum(MyEnum, "myenum")

scala> sql"select 'foo'::myenum".query[MyEnum.Value].unique.quick.unsafePerformIO
  foo

It works, but Enumeration is terrible so it’s unlikely you will want to do this. A better option, perhaps surprisingly, is to map myenum to a Java enum via the pgJavaEnum constructor.

// This is Java code
public enum MyJavaEnum { foo, bar; }

implicit val MyJavaEnumMeta = pgJavaEnum[MyJavaEnum]("myenum")

And the final, most general construction simply requires evidence that your taget type can be translated to and from String.

sealed trait FooBar

object FooBar {

  case object Foo extends FooBar
  case object Bar extends FooBar

  def toEnum(e: FooBar): String =
    e match {
      case Foo => "foo"
      case Bar => "bar"
    }

  def fromEnum(s: String): Option[FooBar] =
    Option(s) collect {
      case "foo" => Foo
      case "bar" => Bar
    }

  def unsafeFromEnum(s: String): FooBar =
    fromEnum(s).getOrElse(throw doobie.util.invariant.InvalidEnum[FooBar](s))

}

implicit val FoobarMeta: Meta[FooBar] =
  pgEnumString("myenum", FooBar.unsafeFromEnum, FooBar.toEnum)

scala> sql"select 'foo'::myenum".query[FooBar].unique.quick.unsafePerformIO
  Foo

Geometric Types

The following geometric types are supported, and map to driver-supplied types.

It is expected that these will be mapped to application-specific types via xmap as described in Custom Mappings.

PostGIS Types

doobie provides mappings for the top-level PostGIS geometric types provided by the org.postgis driver extension.

Mappings for postgis are provided in the pgistypes module. Doobie expects postgis dependency to be provided, so if you use this module you should add postgis as a dependency.

libraryDependencies += "org.postgis" % "postgis-jdbc" % "1.3.3"

// Not provided via doobie.postgres.imports._; you must import them explicitly.
import doobie.postgres.pgistypes._

In addition to the general types above, doobie provides mappings for the following abstract and concrete fine-grained types carried by PGgeometry:

Other Nonstandard Types

Extended Error Handling

A complete table of SQLSTATE values is provided in the doobie.postgres.sqlstate module. Recovery combinators for each of these states (onUniqueViolation for example) are provided in doobie.postgres.syntax.

import doobie.postgres.imports._

val p = sql"oops".query[String].unique // this won't work

Some of the recovery combinators demonstrated:

scala> p.attempt.quick.unsafePerformIO // attempt provided by Catchable instance
  Left(org.postgresql.util.PSQLException: ERROR: syntax error at or near "oops"
  Position: 1)

scala> p.attemptSqlState.quick.unsafePerformIO // this catches only SQL exceptions
  Left(SqlState(42601))

scala> p.attemptSomeSqlState { case SqlState("42601") => "caught!" } .quick.unsafePerformIO // catch it
  Left(caught!)

scala> p.attemptSomeSqlState { case sqlstate.class42.SYNTAX_ERROR => "caught!" } .quick.unsafePerformIO // same, w/constant
  Left(caught!)

scala> p.exceptSomeSqlState { case sqlstate.class42.SYNTAX_ERROR => "caught!".pure[ConnectionIO] } .quick.unsafePerformIO // recover
  caught!

scala> p.onSyntaxError("caught!".pure[ConnectionIO]).quick.unsafePerformIO // using recovery combinator
  caught!

Server-Side Statements

PostgreSQL supports server-side caching of prepared statements after a certain number of executions, which can have desirable performance consequences for statements that only need to be planned once. Note that this caching happens only for PreparedStatement instances that are re-used within a single connection lifetime. doobie supports programmatic configuration of the prepare threshold:

See the JDBC driver documentation for more information.

LISTEN and NOTIFY

PostgreSQL provides a simple transactional message queue that can be used to notify a connection that something interesting has happened. Such notifications can be tied to database triggers, which provides a way to notify clients that data has changed. Which is cool.

doobie provides ConnectionIO constructors for SQL LISTEN, UNLISTEN, and NOTIFY in the doobie.postgres.hi.connection module. New notifications are retrieved (synchronously, sadly, that’s all the driver provides) via pgGetNotifications. Note that all of the “listening” operations apply to the current connection, which must therefore be long-running and typically off to the side from normal transactional operations. Further note that you must setAutoCommit(false) on this connection or commit between each call in order to retrieve messages. The examples project includes a program that demonstrates how to present a channel as a Process[Task, PGNotification].

Large Objects

PostgreSQL provides a facility for storing very large objects (up to 4TB each) in a single uniform storage, identified by unique numeric ID and accessed via fast byte-block transfer. Note that “normal” large object columns types such as bytea and text can store values as large as 1GB each, so the large object API is rarely used. However there are cases where the size and/or efficiency of large objects justifies the use of this API.

doobie provides an algebra and free monads for the driver’s LargeObjectManager and LargeObject types in the doobie.postgres.free package. There is also [the beginnings of] a high-level API that includes constructors for creating large objects from files and vice-versa. The example project contains a brief usage example.

Please file an issue or ask questions on the Gitter channel if you need to use this API; it will evolve as use cases demand.

Copy Manager

The PostgreSQL JDBC driver’s CopyManager API provides a pass-through for the SQL COPY statement, allowing very fast data transfer via java.io streams. Here we construct a program that dumps a table to Console.out in CSV format, with quoted values.

import doobie.postgres.imports._

val q = """
  copy country (name, code, population)
  to stdout (
    encoding 'utf-8',
    force_quote *,
    format csv
  )
  """

val prog: ConnectionIO[Long] =
  PHC.pgGetCopyAPI(PFCM.copyOut(q, Console.out)) // return value is the row count

See the links above and sample code in the examples/ project in the doobie GitHub repo for more information on this specialized API.

Fastpath

doobie provides an algebra and free monad for constructing programs that use the FastPathAPI provided by the PostgreSQL JDBC driver, however this API is mostly deprecated in favor of server-side statements (see above). And in any case I can’t find an example of how you would use it from Java so I don’t have an example here. But if you’re using it let me know and we can figure it out.