Doobie

Imports

import doobie.imports._
import doobie.util.iolite.IOLite
import doobie.util.transactor.Transactor
import java.util.UUID
import scalaz.Scalaz._

Connecting to the database

val xa: Transactor[IOLite] =
  DriverManagerTransactor[IOLite](
      "org.h2.Driver"
    , "jdbc:h2:mem:db"
    , ""
    , ""
    )

DDL

val prepareDb: ConnectionIO[Int] =
  sql"""create table if not exists people (
            id char(36) not null unique
          , name varchar(512) not null
          , email varchar(512) not null unique
          , primary key(id)
          )
     """.update.run

A person to be created

This is an "incomplete" person record, as it lacks an ID.

case class NewPerson(name: String, email: String)

Custom types

We'll need to tell Doobie/Shapeless how to serialize and deserialize a Java UUID into a database string for our table row IDs.

implicit val UUIDMeta: Meta[UUID] =
  Meta[String].nxmap(UUID.fromString, _.toString)

An INSERT query

def addPerson(x: NewPerson): ConnectionIO[Int] =
  sql"""insert into people (id, name, email)
        values (
            ${UUID.randomUUID}
          , ${x.name}
          , ${x.email}
          )
     """.update.run

A complete person record

case class Person(id: UUID, name: String, email: String)

A SELECT query

def getPeople: ConnectionIO[List[Person]] =
  (sql"""select id, name, email
         from people
      """.query[Person]
  ).list

Composing queries

val composed: ConnectionIO[Unit] =
  prepareDb *>
  addPerson(NewPerson("James Earl Douglas", "james@earldouglas.com")) *>
  addPerson(NewPerson("Johnny McDoe", "johnny@mcdoe")) *>
  getPeople map { xs =>
    println("# People")
    xs map {
      case Person(id, name, email) =>
        println()
        println(s"## ${name}")
        println()
        println(s"* ID: ${id.toString}")
        println(s"* Email: ${email}")
    }
  }

composed.transact(xa).unsafePerformIO

Demo

/***
libraryDependencies += "org.tpolecat" %% "doobie-core" % "0.4.4"
libraryDependencies += "com.h2database" % "h2" % "1.4.200"
*/

This file is literate Scala, and can be run using Codedown:

$ curl https://earldouglas.com/posts/scala/doobie.md |
  codedown scala > script.scala
$ sbt -Dsbt.main.class=sbt.ScriptMain script.scala

# People

## James Earl Douglas

* ID: 71b4dc58-6031-44a8-a8e8-9c63ea2b7514
* Email: james@earldouglas.com

## Johnny McDoe

* ID: e0f7b4c5-aca8-4025-8998-e7653d834509
* Email: johnny@mcdoe