<commit_before>---
layout: book
number: 10
title: Custom Mappings
---

In this chapter we learn how to use custom `Meta` instances to map arbitrary data types as single-column values; and how to use custom `Composite` instances to map arbitrary types across multiple columns.

### Setting Up

The examples in this chapter require the `contrib-postgresql` add-on, as well as the [argonaut](http://argonaut.io/) JSON library, which you can add to your build thus:

```scala
#+scalaz
libraryDependencies += "io.argonaut" %% "argonaut" % "6.2-M1" // as of date of publication
#-scalaz
#+cats
val circeVersion = "0.4.1"

libraryDependencies ++= Seq(
  "io.circe" %% "circe-core",
  "io.circe" %% "circe-generic",
  "io.circe" %% "circe-parser"
).map(_ % circeVersion)
#-cats
```

In our REPL we have the same setup as before, plus a few extra imports.

```tut:silent
#+scalaz
import argonaut._, Argonaut._
#-scalaz
#+cats
import io.circe._, io.circe.jawn._, io.circe.syntax._
#-cats
import doobie.imports._
import java.awt.Point
import org.postgresql.util.PGobject
import scala.reflect.runtime.universe.TypeTag
import scala.util.Try
#+scalaz
import scalaz._, Scalaz._
#-scalaz
#+cats
import cats._, cats.implicits._
#-cats

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

import xa.yolo._
```

### Meta, Atom, and Composite

The `doobie.free` API provides constructors for JDBC actions like `setString(1, "foo")` and `getBoolean(4)`, which operate on single columns specified by name or offset. Query parameters are set and resulting rows are read by repeated applications of these low-level actions.

The `doobie.hi` API abstracts the construction of these composite operations via the `Composite` typeclass, which provides actions to get or set a heterogeneous **sequence** of column values. For example, the following programs are equivalent:

```tut:silent
// Using doobie.free
FPS.setString(1, "foo") >> FPS.setInt(2, 42)

// Using doobie.hi
HPS.set(1, ("foo", 42))

// Or leave the 1 out if you like, since we usually start there
HPS.set(("foo", 42))

// Which simply delegates to the Composite instance
Composite[(String,Int)].set(1, ("foo", 42))
```

**doobie** can derive `Composite` instances for primitive column types, plus tuples, `HList`s, shapeless records, and case classes whose elements have `Composite` instances. These primitive column types are identified by `Atom` instances, which describe `null`-safe column mappings. These `Atom` instances are almost always derived from lower-level `null`-unsafe mappings specified by the `Meta` typeclass.

So our strategy for mapping custom types is to construct a new `Meta` instance (given `Meta[A]` you get `Atom[A]` and `Atom[Option[A]]` for free); and our strategy for multi-column mappings is to construct a new `Composite` instance. We consider both cases below.

### Meta by Invariant Map

Let's say we have a structured value that's represented by a single string in a legacy database. We also have conversion methods to and from the legacy format. 

```tut:silent
case class PersonId(department: String, number: Int) {
  def toLegacy = department + ":" + number
}

object PersonId {

  def fromLegacy(s: String): Option[PersonId] =
    s.split(":") match {
      case Array(dept, num) => Try(num.toInt).toOption.map(new PersonId(dept, _))
      case _                => None
    }

  def unsafeFromLegacy(s: String): PersonId =
    fromLegacy(s).getOrElse(throw new RuntimeException("Invalid format: " + s))

}

val pid = PersonId.unsafeFromLegacy("sales:42")
```

Because `PersonId` is a case class of primitive column values, we can already map it across two columns. We can look at its `Composite` instance and see that its column span is two:

```tut:nofail
Composite[PersonId].length
```

However if we try to use this type for a *single* column value (i.e., as a query parameter, which requires an `Param` instance), it doesn't compile.

```tut:fail:plain
sql"select * from person where id = $pid"
```

According to the error message we need a `Param[PersonId :: HNil]` instance which requires a `Meta` instance for each member, which means we need a `Meta[PersonId]`. 

```tut:fail
Meta[PersonId]
```

... and we don't have one. So how do we get one? The simplest way is by basing it on an existing `Meta` instance, using `nxmap`, which is like the invariant functor `xmap` but ensures that `null` values are never observed. So we simply provide `String => PersonId` and vice-versa and we're good to go.

```tut:silent
implicit val PersonIdMeta: Meta[PersonId] = 
  Meta[String].nxmap(PersonId.unsafeFromLegacy, _.toLegacy)
```

Now it compiles as a column value and as a `Composite` that maps to a *single* column:

```tut
sql"select * from person where id = $pid"
Composite[PersonId].length
sql"select 'podiatry:123'".query[PersonId].quick.unsafePerformIO
```

Note that the `Composite` width is now a single column. The rule is: if there exists an instance `Meta[A]` in scope, it will take precedence over any automatic derivation of `Composite[A]`.

### Meta by Construction

Some modern databases support a `json` column type that can store structured data as a JSON document, along with various SQL extensions to allow querying and selecting arbitrary sub-structures. So an obvious thing we might want to do is provide a mapping from Scala model objects to JSON columns, via some kind of JSON serialization library.

We can construct a `Meta` instance for the argonaut `Json` type by using the `Meta.other` constructor, which constructs a direct object mapping via JDBC's `.getObject` and `.setObject`. In the case of PostgreSQL the JSON values are marshalled via the `PGObject` type, which encapsulates an uninspiring `(String, String)` pair representing the schema type and its string value. 

Here we go:

```tut:silent
implicit val JsonMeta: Meta[Json] = 
  Meta.other[PGobject]("json").nxmap[Json](
#+scalaz
    a => Parse.parse(a.getValue).leftMap[Json](sys.error).merge, // failure raises an exception
    a => new PGobject <| (_.setType("json")) <| (_.setValue(a.nospaces))
#-scalaz
#+cats<commit_msg>Updating an example to remove the deprecated <|<commit_after>---
layout: book
number: 10
title: Custom Mappings
---

In this chapter we learn how to use custom `Meta` instances to map arbitrary data types as single-column values; and how to use custom `Composite` instances to map arbitrary types across multiple columns.

### Setting Up

The examples in this chapter require the `contrib-postgresql` add-on, as well as the [argonaut](http://argonaut.io/) JSON library, which you can add to your build thus:

```scala
#+scalaz
libraryDependencies += "io.argonaut" %% "argonaut" % "6.2-M1" // as of date of publication
#-scalaz
#+cats
val circeVersion = "0.4.1"

libraryDependencies ++= Seq(
  "io.circe" %% "circe-core",
  "io.circe" %% "circe-generic",
  "io.circe" %% "circe-parser"
).map(_ % circeVersion)
#-cats
```

In our REPL we have the same setup as before, plus a few extra imports.

```tut:silent
#+scalaz
import argonaut._, Argonaut._
#-scalaz
#+cats
import io.circe._, io.circe.jawn._, io.circe.syntax._
#-cats
import doobie.imports._
import java.awt.Point
import org.postgresql.util.PGobject
import scala.reflect.runtime.universe.TypeTag
import scala.util.Try
#+scalaz
import scalaz._, Scalaz._
#-scalaz
#+cats
import cats._, cats.implicits._
#-cats

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

import xa.yolo._
```

### Meta, Atom, and Composite

The `doobie.free` API provides constructors for JDBC actions like `setString(1, "foo")` and `getBoolean(4)`, which operate on single columns specified by name or offset. Query parameters are set and resulting rows are read by repeated applications of these low-level actions.

The `doobie.hi` API abstracts the construction of these composite operations via the `Composite` typeclass, which provides actions to get or set a heterogeneous **sequence** of column values. For example, the following programs are equivalent:

```tut:silent
// Using doobie.free
FPS.setString(1, "foo") >> FPS.setInt(2, 42)

// Using doobie.hi
HPS.set(1, ("foo", 42))

// Or leave the 1 out if you like, since we usually start there
HPS.set(("foo", 42))

// Which simply delegates to the Composite instance
Composite[(String,Int)].set(1, ("foo", 42))
```

**doobie** can derive `Composite` instances for primitive column types, plus tuples, `HList`s, shapeless records, and case classes whose elements have `Composite` instances. These primitive column types are identified by `Atom` instances, which describe `null`-safe column mappings. These `Atom` instances are almost always derived from lower-level `null`-unsafe mappings specified by the `Meta` typeclass.

So our strategy for mapping custom types is to construct a new `Meta` instance (given `Meta[A]` you get `Atom[A]` and `Atom[Option[A]]` for free); and our strategy for multi-column mappings is to construct a new `Composite` instance. We consider both cases below.

### Meta by Invariant Map

Let's say we have a structured value that's represented by a single string in a legacy database. We also have conversion methods to and from the legacy format. 

```tut:silent
case class PersonId(department: String, number: Int) {
  def toLegacy = department + ":" + number
}

object PersonId {

  def fromLegacy(s: String): Option[PersonId] =
    s.split(":") match {
      case Array(dept, num) => Try(num.toInt).toOption.map(new PersonId(dept, _))
      case _                => None
    }

  def unsafeFromLegacy(s: String): PersonId =
    fromLegacy(s).getOrElse(throw new RuntimeException("Invalid format: " + s))

}

val pid = PersonId.unsafeFromLegacy("sales:42")
```

Because `PersonId` is a case class of primitive column values, we can already map it across two columns. We can look at its `Composite` instance and see that its column span is two:

```tut:nofail
Composite[PersonId].length
```

However if we try to use this type for a *single* column value (i.e., as a query parameter, which requires an `Param` instance), it doesn't compile.

```tut:fail:plain
sql"select * from person where id = $pid"
```

According to the error message we need a `Param[PersonId :: HNil]` instance which requires a `Meta` instance for each member, which means we need a `Meta[PersonId]`. 

```tut:fail
Meta[PersonId]
```

... and we don't have one. So how do we get one? The simplest way is by basing it on an existing `Meta` instance, using `nxmap`, which is like the invariant functor `xmap` but ensures that `null` values are never observed. So we simply provide `String => PersonId` and vice-versa and we're good to go.

```tut:silent
implicit val PersonIdMeta: Meta[PersonId] = 
  Meta[String].nxmap(PersonId.unsafeFromLegacy, _.toLegacy)
```

Now it compiles as a column value and as a `Composite` that maps to a *single* column:

```tut
sql"select * from person where id = $pid"
Composite[PersonId].length
sql"select 'podiatry:123'".query[PersonId].quick.unsafePerformIO
```

Note that the `Composite` width is now a single column. The rule is: if there exists an instance `Meta[A]` in scope, it will take precedence over any automatic derivation of `Composite[A]`.

### Meta by Construction

Some modern databases support a `json` column type that can store structured data as a JSON document, along with various SQL extensions to allow querying and selecting arbitrary sub-structures. So an obvious thing we might want to do is provide a mapping from Scala model objects to JSON columns, via some kind of JSON serialization library.

We can construct a `Meta` instance for the argonaut `Json` type by using the `Meta.other` constructor, which constructs a direct object mapping via JDBC's `.getObject` and `.setObject`. In the case of PostgreSQL the JSON values are marshalled via the `PGObject` type, which encapsulates an uninspiring `(String, String)` pair representing the schema type and its string value. 

Here we go:

```tut:silent
implicit val JsonMeta: Meta[Json] = 
  Meta.other[PGobject]("json").nxmap[Json](
#+scalaz
    a => Parse.parse(a.getValue).leftMap[Json](sys.error).merge, // failure raises an exception
    a => {
      val o = new PGobject 
      o.setType("json")
      o.setValue(a.noSpaces)
      o
    }
#-scalaz
#+cats
{'fraction_non_alphanumeric': 0.1083986743189718, 'fraction_numerical': 0.004526715706086816, 'mean_word_length': 4.515827017387427, 'pattern_counts': {'":': 4, '<': 6, '<?xml version=': 0, '>': 17, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '4032215', 'n_tokens_mistral': 3880, 'n_tokens_neox': 3684, 'n_words': 1563}