Lately I've built a few apps that have relational data. Instead of trying to shoehorn that data into a NoSQL model I decided to use the awesome [Heroku Postgres][1] service but I didn't want to lose out on the [Reactiveness][2] that most of the NoSQL data stores support. I discovered [ScalikeJDBC-Async][3] which uses [postgresql-async][4], a Reactive (non-blocking), JDBC-ish, Postgres driver. With those libraries I was able to keep my data relational and my app Reactive all the way down. Lets walk through how to do it in a [Play Framework][5] app. (TL;DR: Jump to the [the full source][6].)

If you want to start from scratch, create a new Play app from the [Play Scala Seed][7].

The minimum dependencies needed in the `build.sbt` file are:

```scala
libraryDependencies ++= Seq(
  "org.postgresql"       %  "postgresql"                    % "9.3-1102-jdbc41",
  "com.github.tototoshi" %% "play-flyway"                   % "1.2.0",

  "com.github.mauricio"  %% "postgresql-async"              % "0.2.16",
  "org.scalikejdbc"      %% "scalikejdbc-async"             % "0.5.5",
  "org.scalikejdbc"      %% "scalikejdbc-async-play-plugin" % "0.5.5"
)
```

The [play-flyway][8] library handles schema evolutions using [Flyway][9]. It is a great alternative to Play's JDBC module because it just does evolutions and does one-way evolutions (i.e. no downs). But because `play-flyway` doesn't use the `postgresql-async` driver, it needs the standard `postgresql` JDBC driver as well.

The `scalikejdbc-async-play-plugin` library manages the lifecycle of the connection pool used by `scalikejdbc-async` in a Play app.

To use `play-flyway` and `scalikejdbc-async-play-plugin` a `conf/play.plugins` file must tell Play about the plugins:

```
776:com.github.tototoshi.play2.flyway.Plugin
777:scalikejdbc.async.PlayPlugin
```

A first evolution script in `conf/db/migration/default/V1__create_tables.sql` will create a table named `bar` that will hold a list of bars for our little sample app:

```sql
DROP TABLE IF EXISTS bar;
CREATE TABLE bar (
  id SERIAL PRIMARY KEY,
  name VARCHAR NOT NULL
);
```

You will of course need a Postgres database to proceed. You can either install one locally or create a free one on the [Heroku Postres][1] cloud service. Then update the `conf/application.conf` file to point to the database:

```properties
db.default.driver="org.postgresql.Driver"
db.default.url="postgres://admin:admin@localhost:5432/test"
db.default.url=${?DATABASE_URL}
```

The last line above overrides the database connection url if there is a `DATABASE_URL` environment variable set (which is the case if your app is running on Heroku).

To run this app locally you can start the Play app by starting the Activator UI or from the command line with:

```bash
activator ~run
```

When you first open your app in the browser, the `play-flyway` plugin should detect that evolutions needs to be applied and ask you to apply them. Once applied you will be ready to create a simple database object and a few reactive request handlers.

Here is a `Bar` database object named `app/models/Bar.scala` that uses `scalikejdbc-async` for reactive creation and querying of Bars:

```scala
package models

import play.api.libs.json.Json
import scalikejdbc.WrappedResultSet
import scalikejdbc._
import scalikejdbc.async._
import scalikejdbc.async.FutureImplicits._

import scala.concurrent.Future
import scala.concurrent.ExecutionContext.Implicits.global

case class Bar(id: Long, name: String)

object Bar extends SQLSyntaxSupport[Bar] {

  implicit val jsonFormat = Json.format[Bar]

  override val columnNames = Seq("id", "name")

  lazy val b = Bar.syntax

  def db(b: SyntaxProvider[Bar])(rs: WrappedResultSet): Bar = db(b.resultName)(rs)

  def db(b: ResultName[Bar])(rs: WrappedResultSet): Bar = Bar(
    rs.long(b.id),
    rs.string(b.name)
  )

  def create(name: String)(implicit session: AsyncDBSession = AsyncDB.sharedSession): Future[Bar] = {
    val sql = withSQL(insert.into(Bar).namedValues(column.name -> name).returningId)
    sql.updateAndReturnGeneratedKey().map(id => Bar(id, name))
  }

  def findAll(implicit session: AsyncDBSession = AsyncDB.sharedSession): Future[List[Bar]] = {
    withSQL(select.from[Bar](Bar as b)).map(Bar.db(b))
  }

}
```

The `db` functions perform the mapping from SQL results to the `Bar` case class.

The `create` function takes a Bar name and returns a `Future[Bar]` by doing a non-blocking insert using the [ScalikeJDBC Query DSL][10]. When the insert has completed the primary key is returned and a new `Bar` instance is created and returned.

The `findAll` method uses the ScalikeJDBC Query DSL to select all of the Bars from the database, returning a `Future[List[Bar]]]`.

Now that we have a reactive database object, lets expose these through reactive request handlers. First setup the routes in the `conf/routes` file:

```routes
GET        /bars                   controllers.Application.getBars
POST       /bars                   controllers.Application.createBar
```

Define the controller functions in the `app/controllers/Application.scala` file:

```scala
def getBars = Action.async {
  Bar.findAll.map { bars =>
    Ok(Json.toJson(bars))
  }
}

def createBar = Action.async(parse.urlFormEncoded) { request =>
  Bar.create(request.body("name").head).map { bar =>
    Redirect(routes.Application.index())
  }
}
```

Both functions use `Action.async` which holds a function that takes a request and returns a response (`Result`) in the future. By returning a `Future[Result]` Play is able to make requests to the controller function non-blocking. The `getBars` controller function calls the `Bar.findAll` and then transforms the `Future[List[Bar]]` into a `Future[Result]`, the 200 response containing the JSON serialized list of bars. The `createBar` controller function parses the request, creates the Bar, and then transforms the `Future[Bar]` into a `Future[Result]` once the Bar has been created.

From the non-blocking perspective, here is what a request to the `getBars` controller function looks like:

  1. Web request made to `/bars`
  2. Thread allocated to web request
  3. Database request made for the SQL select
  4. Thread allocated to the database request
  5. Web request thread is deallocated (but the connection remains open)
  6. Database request thread is deallocated (but the connection remains open)
  7. Database response handler reallocates a thread
  8. SQL result is transformed to `List[Bar]`
  9. Database response thread is deallocated
 10. Web response handler reallocates a thread
 11. Web response is created from the list of bars
 12. Web response thread is deallocated

So everything is now reactive all the way down because there is a moment where the web request is waiting on the database to respond but no threads are allocated to the request.

Try it yourself with curl:

```bash
$ curl -X POST -d "name=foo" http://localhost:9000/bars
$ curl http://localhost:9000/bars
[{"id":1,"name":"foo"}]
```

Grab the [the full source][6] and let me know if you have any questions. Thanks!

 [1]: http://postgres.heroku.com
 [2]: http://reactivemanifesto.org
 [3]: https://github.com/scalikejdbc/scalikejdbc-async
 [4]: https://github.com/mauricio/postgresql-async
 [5]: http://playframework.org
 [6]: https://github.com/jamesward/play2bars/tree/scalikejdbc-async
 [7]: https://typesafe.com/activator/template/play-scala
 [8]: https://github.com/tototoshi/play-flyway
 [9]: http://flywaydb.org/
 [10]: http://scalikejdbc.org/documentation/query-dsl.html