13. 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-contrib-postgresql" % "0.2.1"

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

Setting Up

The following examples require a few imports.

import doobie.imports._, scalaz._, Scalaz._, scalaz.concurrent.Task

val xa = DriverManagerTransactor[Task](
  "org.postgresql.Driver", "jdbc:postgresql:world", "postgres", ""
)

import xa.yolo._

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

import doobie.contrib.postgresql.pgtypes._

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')

NOTE that because it seems to be impossible to write a NULL value to an enum column or parameter, doobie cannot support Option mappings for enum types.

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 MyEnumAtom = pgEnum(MyEnum, "myenum")

scala> sql"select 'foo'::myenum".query[MyEnum.Value].unique.quick.run
  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 MyJavaEnumAtom = 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 FoobarAtom: Atom[FooBar] = 
  pgEnumString("myenum", FooBar.unsafeFromEnum, FooBar.toEnum)

scala> sql"select 'foo'::myenum".query[FooBar].unique.quick.run
  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 nxmap as described in Custom Mappings.

PostGIS Types

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

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.contrib.postgresql.sqlstate module, and recovery combinators for each of these (onUniqueViolation for example) are provided in doobie.contrib.postgresql.syntax.