Doobie cheat sheet

October 25, 2017

sbt configuration

/***
scalaVersion := "2.11.11"
libraryDependencies += "org.tpolecat"   %% "doobie-core" % "0.4.1"
libraryDependencies += "com.h2database" %  "h2"          % "1.4.194"
*/

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"
                                 , ""
                                 , ""
                                 )

def run[A](query: ConnectionIO[A]): A =
  query.transact(xa).unsafePerformIO

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}")
    }
  }

run(composed)

Demo

This post is literate Scala. Try it out with sbt and codedown:

$ curl -s https://earldouglas.com/posts/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