Title

class: center, middle

# Pure-Functional Database Access in Scala
Rob Norris / `@tpolecat`

???

- name is rob
- going to talk about some work i have been doing with pure functional database access
- please ask questions as I go, it's likely i will lose some people since i haven't put a lot of time into this

---

# What is Functional Programming?

Key idea: *Referential Transparency*

- Expressions can be replaced with their values without changing the meaning of the program.

- More generally, equivalent expressions are interchangeable.

- When you can't do the above, we call it a *side effect*.
  - Printing to the console
  - Throwing an exception
  - Looking at the clock
  - Reading a file
  - Etc., etc.

Intuition: FP is about programming with *values*

???

- so RT is a syntactic property of programs that tells you what kinds of transformations you can do freely
- so what you get is equational reasoning; you can treat equal things as truly equal

---

# What is Functional IO?

Key idea: *programs* are values

```scala
sealed trait ConsoleAction
case class  Print(s: String) extends ConsoleAction
case object Newline extends ConsoleAction

type Program = List[ConsoleAction]
```

Here is a pure functional IO program:
```scala
val prog = List(Print("Hello"), Newline, Print("World!"))
```

---

# What is Functional IO?

...
```scala
val prog = List(Print("Hello"), Newline, Print("World!"))
```

Here is a possible *interpreter* for our program.

```scala
def stringInterpreter(p: Program): String = 
  p.foldLeft("") { (accum, a) =>
    a match {
      case Print(s) => accum + s
      case Newline  => accum + '\n'
    }
  }
```

And we can run it:

```scala
scala> stringInterpreter(prog)
res0: String = 
Hello
World!
```

---

# What is Functional IO?

... 
```scala
scala> stringInterpreter(prog)
res0: String = 
Hello
World!
```

Note:

- No side-effects anywhere.

- `prog` is just a value.

---

# What is Functional IO?

Another interpreter:

```scala
def unsafeInterpreter(p: Program): Unit = 
  p.foreach { 
    case Print(s) => Console.print(s)
    case Newline  => Console.println()
  }
```

And we can run it:

```scala
scala> unsafeInterpreter(prog)
Hello
World!
```

---

# What is Functional IO?

...
```scala
scala> unsafeInterpreter(prog)
Hello
World!
```

Note:

- No side-effects anywhere *until we run the unsafe interpreter*

- `prog` is *still* just a value.

Intuition:

- We treat the interpreter as *part of the runtime*.

- We can talk about programs as values right up until we run them.

???

- Ok so in real life you need something more sophisticated than a list of statements, but you actually don't need *much* more.
- Data structure called `Free` that provides this minimal structure, and that's what is really happning in the examples I'll be showing you.

---

# Problems with Existing DB Libs

- Not functional (effects are uncontrolled).

- Unprincipled resource management (implicit sessions).

- No proper abstractions.
  - Lots of new not-quite-right stuff to learn.
  - Can't build big programs from smaller ones.
  - Can't reuse existing library support like scalaz.

- Some people can cope with this. I can't.

???

- today there was a guy on `#scala` struggling to abstract over tables in Slick and he was really suffering.

---

# Introducing Doobie

Pure-functional library for writing JDBC programs.

- Programs are values.

- Monadic API (no nutty DSL) with familiar scalaz abstractions.

- *Interpreted* into a target monad of your choosing (`IO` and `Task` provided). API is open; you can write your own interpreter if you prefer.

- *Not* an ORM.

- *Not* a relational algebra or SQL generator, although such a thing could be built on top.

---

# Introducing Doobie

Out of the box:

- Low-level API allows direct translation of existing imperative JDBC code.
  - Supports full `java.sql` API (as of JDK 1.6)
  - Designed for library implementors.

- High-level API provides a resource-safe subset with higher-level features:

- Typeclass-based mapping of product types to columns.
  - Treat result sets as streams.
  - String interpolation and other syntax to make this easy.
  - Designed for end users.
  - *Good* support for String-based queries.

- Same types used in both APIs; drop down as needed.

---

# Low-Level API

Key idea: *nested* programs

- Each carrier type has its own monadic DSL, and all can be interpreted directly or lifted into another carrier type's DSL.

???

- So what this means is, you can use doobie just to process a resultset, and do everything else with your existing database code; all you need is a resultset.
- Or you can build your whole program by composing programs written in these little languages.

---

# Low-Level API

Example program. You are not expected to think this looks easy.

```scala
// Data type to read
case class CountryCode(code: String)

// DriverManager program
val tmain: DriverManagerIO[List[CountryCode]] = 
  for {
    c <- getConnection("jdbc:h2:mem:test;DB_CLOSE_DELAY=-1", "sa", "")
    a <- liftConnection(c, speakerQuery("English", 10))
  } yield a

// Connection Program
def speakerQuery(s: String, p: Double): ConnectionIO[List[CountryCode]] =
  for {
    ps <- prepareStatement("""SELECT COUNTRYCODE FROM COUNTRYLANGUAGE 
                              WHERE LANGUAGE = ? AND PERCENTAGE > ?""")
    l  <- liftPreparedStatement(ps, 
      // PreparedStatement Program
      for {
        _  <- setString(1, s)
        _  <- setDouble(2, p)
        rs <- executeQuery
        l  <- liftResultSet(rs, unroll(getString(1).map(CountryCode(_))))
      } yield l)
  } yield l
```

???

- Things to notice, we're just using monad comprehensions here to sequence our computations.
- We use lifting to embed smaller programs inside larger one.

---

# Low-Level API

Example program (cont'd)

```scala
// ResultSet Program
def unroll[A](action: ResultSetIO[A]): ResultSetIO[List[A]] = {
  def unroll0(as: List[A]): ResultSetIO[List[A]] = 
    next flatMap {
      case false => as.point[ResultSetIO]
      case true  => action flatMap { a => unroll0(a :: as) }
    }
  unroll0(Nil).map(_.reverse)
}

// Main Program  
def main(args: Array[String]): Unit = 
  tmain.trans[IO].traverseU(putStrLn).unsafePerformIO
```

Takeaway: 
- Powerful and very general; can write *any* JDBC program this way.
- Must be careful: raw JDBC objects, manual error handling.
- Also kind of verbose.

???

- But, we're using map and flatMap and traverse and all these operations that you get for free if you use the right abstractions.
- Ok but you don't want to write programs like this; it's too much work. So most of the time...

---

# High-Level API

- Same types as low-level API, but all operations are resource-safe.
- Implemented entirely in terms of low-level API. (Abstractions FTW)
- Syntax and helper classes.

---

# High-Level API

A much better program, in much less code:

```scala
// Data type to read
case class Country(name: String, continent: String, population: Int)

// Transactor knows how to create connection and manage transactions
val xa = DriverManagerTransactor[Task]("org.h2.Driver", "jdbc:...", "sa", "")

// A parameterized query, as a scalaz-stream Process
def q(e: Double) = 
  sql"""SELECT NAME, CONTINENT, POPULATION 
        FROM COUNTRY WHERE LIFEEXPECTANCY >= $e""".process[Country]

// Transact yields Process[Task,Country] ... stream top 5 results to stdout
val program = xa.transact(q(80)).take(5).map(_.toString).to(io.stdOutLines).run

// Main program
def main(args: Array[String]): Unit =
  program.run
```

???

- (Be sure to talk about desugaring of query)

---

class: center, middle

# Demos

---

# Status

- Currently a preview release (0.1) for the daring.

- Features in the works.
  - High-level API support for callable statements.
  - Integration with HTTP4s.
  - Structured logging ([example](https://raw.githubusercontent.com/tpolecat/doobie/master/log.png)).
  - Static typechecking of SQL literals.
  - Relational algebra (SQL generation).

---
class: center, middle

# Thanks! Questions?
Rob Norris / `@tpolecat`

<small>Code and examples at `github.com/tpolecat/doobie`.</small>

???

- feedback, complaints