What SQL access layer to use for simple reading in Play-Scala?

Question

I will be implementing a read-only web application in Play 2.1 (Scala). Since I'll only be doing reading and marshaling the data read to JSON I would like to avoid any other DSLs and mappings.

I've done similar projects in .NET/C# using dapper-dot-net and was very happy with the way things turned out. No fuss and not much boiler plate.

I am currently looking at:

• anorm (anormtyped looks very promising too, but is probably to early to adopt. Avoiding the manual mapping of variables to case class constructor parameters seems awesome.)
• prequel
• slick - because it is supposed to be the primary way of doing SQL in 2.1 and mainly the plain SQL API

Fairspoken

I probably should have been more explicit about looking into slick because it is supposed to be the preferred way of doing SQL in Play 2.1. Their documentation on the plain SQL kind of shows they prefer the other two APIs. – Fairspoken

Answer 1

Slick is very good. Make sure you check out this short book about it - it's very good at explaining some basics. Along with the docs it would get you forward quickly. Also, note that the docs in github are better - the latest haven't been published yet.

Using the plain sql option is very well supported. With plain sql queries you don't much in terms of type checking, though. Otherwise, using Scala 2.10 doing a plain query is as easy as:

sql"select * from coffees where name = $name".as[Coffee]

This will even protect you from sql injction, as $name is not actually in the query. See the docs for more information.

Albuminous

see my comment on the original question. Have you used the plain SQL API or just the others? – Fairspoken

I see now. And am beginning to take a liking to Slick. I still do have to write the GetResult though? – Fairspoken

Answer 2

I have a project on GitHub which is massively inspired by Dapper, called dbmapper

Advantages over Slick are:

• no DSL -- you already know a good data DSL, it is called SQL
• totally asynchronous
• very little boilerplate code

Here is an example:

// Scala class that maps to the book table, with columns to match the class members
case class Book(
  bookId: Int,
  title: String,
  retailPrice: BigDecimal,
  publishDate: LocalDate)

// mapping function from table row to Book class, 
// auto generated at compile time by Scala Macro  
implicit def rowToBook: RowData => Book = (r) => DbCodeGenerator.rowToClass[Book](r)

// query returning future list of books, 
// safe query interpolation, the maxPrice is converted to a query argument  
val maxPrice = 11.99
val allBooksFuture = DbAsync.exec[Book](q"select * from book where retail_price < $maxPrice")        

val oneBook: Future[Book] = DbAsync.execOne[Book](q"select * from book where book_id = 2")

// returns Future[Option[]]     
val maybeOneBook: Future[Option[Book]] = DbAsync.execOneOrNone[Book](q"select * from book where book_id = -123")

If you know dapper from the dotnet world then dbmapper will feel strangely familiar!

Catfall

Looks very promising! Unfortunately we have already moved on from Play/Scala because because of lack of support for Windows in production in Play at the time. – Fairspoken