• Wraps postgresql-async to provide a simple asynchronous access to Postgres
• SQL Queries with interpolation
• Macro generated mapping layer from Scala type to Database Table
• Provides a Table Data Gateway model P of EAA
• If you are familiar with Dapper then you will be familiar with dbmapper.
• Fast

• No new Domain Specific Language to access data. You know a pretty good database access DSL, it is called SQL. This is not Typesafe's Slick
• No ORM. No Hibernate magic.

I use this code personally in some rather high transaction websites, it works well for me and I trust it.

However, there are advantages in 'going with the crowd', and maybe you should consider using Slick. Slick now supports asynchronous queries, but it is quite an over engineered beast.

  
  // 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, converting maxPrice 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")
      

case class SuperHero(
  superHeroId: Int = 0,
  name: String = "",
  wearsTights: Boolean = false,
  partner: Option[String] = None
)

class SuperHeroAccess extends TableAccess[SuperHero](
  () => DbCodeGenerator.codeToSql[SuperHero](),
  (r) => DbCodeGenerator.rowToClass[SuperHero](r),
  (u) => DbCodeGenerator.updateSql[SuperHero](u),
  (i) => DbCodeGenerator.insertSql[SuperHero](i),
  (i) => DbCodeGenerator.identityInsertSql[SuperHero](i),
  (t, i) => t.copy(superHeroId = i)) {}

// get SuperHero with id 2, all results are Future[] 
val hero = superHeroAccess.get(2)

// update Batman
val batman = await( superHeroAccess.load(1) )
superHeroAccess.update(batman.copy(partner = Some("Robin")))

// insert Catwoman
val catwoman = SuperHero(0, "Catwoman")
superHeroAccess.insert(catwoman)

// delete Batman
superHeroAccess.delete(1)

Executable Code Samples

The default connection string is for the samples is:

jdbc:postgresql://localhost/dbmappersamples?user=postgres&password=secret

Either change the connection string for your environment, or create a database named dbmappersamples, accessible by the postgres user with that password.

ScalaDocs on GitHub

Best Starting Point in the docs

The artifacts are hosted in JCenter on Bintray.

If you are using SBT >= 0.13.5 then the Bintray resolver is already known, just add the following to your libDependencies in build.sbt:

"com.github.njeuk" %% "dbmapper" % "3.3"

and a resolver to the JCenter Bintray Repository

resolvers += Resolver.jcenterRepo

If you are using SBT < 0.13.5, then you need to add a resolver for BinTray, see here: https://github.com/softprops/bintray-sbt

1. Place the dependency in you libDependencies of build.sbt

    libraryDependencies ++= Seq(
      ...
      "org.scalatestplus.play" % "scalatestplus-play_2.12" % "3.1.1" % "test",      
      "com.github.njeuk" %% "dbmapper" % "3.3"      
    )

1a. Depending on your SBT version, you will need to add a resolver to BinTray, most likely you need to add:

resolvers += Resolver.jcenterRepo

2. Create a Case Class to represent the table in the database. For example, assume there is a table called books:

  create table book(
    book_id serial, 
    title text not null, 
    retail_price numeric(10,2) not null, 
    publish_date date not null)

The corresponding Case Class would look like:

 case class Book(
   bookId: Int,
   title: String,
   retailPrice: BigDecimal,
   publishDate: LocalDate)

What happens if you don't like the name mapping conventions between from Scala and the DB?

You can override them with @attributes, see this sample

3. Ensure that the implicit object DbAsyncConfig is in scope.

You can setup the config explicitly like this:

implicit val dbAsyncConfig = DbAsyncConfig(URLParser.parse("jdbc:postgresql://localhost/dbmappersamples?user=postgres&password=secret"), Duration("500 ms"))

Or more simply just import:

import com.github.njeuk.dbmapper.Implicits._

which will create a config that pulls PlayFramework style database settings from your application.conf details. We only look at username/password/url.

4. Ensure there is an implicit function in scope to map a the database rowData to the Scala object.

implicit def rowToBook: RowData => Book = (r) => DbCodeGenerator.rowToClass[Book](r)

This one will generate the code based on the names in the Book class.

5. Write your queries:

async {
  val allBooks = await( DbAsync.exec[Book]("select * from book") )
  val titles = allBooks.map(b => b.title)
  println(s"Title where $titles")
}

dbmapper is asynchronous. We have used this codebase to implement completely asynchronous PlayFramework websites. We find using the Scala Async/Await mechanism the most straight forward to deal with Future results.

For example:

import scala.async.Async._
import scala.concurrent.ExecutionContext.Implicits.global
import com.github.njeuk.dbmapper.Implicits._

...

async {
  val batmanFuture = superHeroAccess.load(1)
  
  val batman = await(batmanFuture)
  // code here continues after batmanFuture succeeds
    
  // now we have found batman, update his partner
  await(superHeroAccess.update(batman.copy(partner = Some("Robin"))))
  
  // load updated Batman,  note await in the line above stalls this code until the update has completed 
  val batmanWithRobin = superHeroAccess.load(1)
  batmanWithRobin.partner should be (Some("Robin"))
}

...

dbmapper needs to map from the scala identifier to the database identifier. To do this it makes certain assumptions about the identifier names. These assumptions can be changed via attributes in your code, see the example CrudSqlWithCustomNameMapping

The default assumptions are:

Scala identifiers are in camelCase or PascalCase, these are represented in the database by identifiers in lowercase snake_case.

The table name is not pluralised, but is a straight conversion from the class name.

Each table has an Integer surrogate key.
The surrogate key is named <class>Id (e.g. bookId) in Scala and <table>_id (e.g. book_id) in the database.

If a variable is defined as an Option[] then dbmapper will convert null column values to None. Otherwise null column values will cause an exception.

See ScalaToSqlNameConversion

dbmapper needs to pass the data base connection information on to postgresql-async to access the database.

All the of the public dbmapper functions take an implicit arguments of type DbAsyncConfig.
This contains the connection information and details about what logging is wanted.

So prior to calling into dbmapper there needs to be a DbAsyncConfig in scope.

You have a number of ways to do this.

The easiest method is to: import com.github.njeuk.dbmapper.Implicits._

This uses a DbAsyncConfig which gets the connection information from config file settings / jvm properties using com.typesafe.config. DbAsyncConfig expects PlayFramework style db configurations. Read their documents for the details of locations looked, some like this in application.conf works:

db.default.url="jdbc:postgresql://some-domain-name-here/some-db-name-here"
db.default.user=your-db-user-here
db.default.password=your-password-here

You can also construct a DbAsyncConfig directly:

implicit val dbAsyncConfig = DbAsyncConfig(URLParser.parse("jdbc:postgresql://localhost/dbmappersamples?user=postgres&password=secret"), Duration("500 ms"))

dbmapper uses Scala Macros to build code to map between the database row data and the scala class.

The Macros run at compile time, of course, and exist in the project dbmapper-macros. The project is split to help with testing of dbmapper. The macros project is a dbmapper dependency, and thus automatically obtained from JCenter, you don't need to be aware of it.

If you have specific requirements, you could handcraft the code the macros generate.

See DbCodeGenerator

To make SQL queries simpler dbmapper provide query interpolation.

This is like string interpolation, but the embed variable references are converted to argument for the database call.

This provides a clean coding experience which is also protected from SQL Injection. The string is prefixed with a 'q' to signal Query Interpolation.

For example:

val name = "Bruce"
val bruce = DbAsync.exec[Person](q"select * from person where name = $name")

Lists and direct string interpolation are also supported, see the scaladocs for more examples.

Using the Macro generated code and the table mappings, you can easily build a Data Access object. The full example of this is shown in CrudSql.scala

By default, dbmapper will log any query that takes longer than 500ms. This is controlled via a setting in DbAsyncConfig. Setting this to zero will cause dbmapper to log all SQL that it executes with the execution lapsed time.

A common compile error with dbmapper is:

not found: type CodeToSql () => DbCodeGenerator.codeToSql[T]()

you need: import com.github.njeuk.dbmapper.macros.CodeToSql

strange compile error

Seeing the following compile error:

value get is not a member of com.github.mauricio.async.db.RowData [error] implicit def rowToBook: RowData => Contact = (r) => DbCodeGenerator.rowToClass[Contact](r)

You need to import

import com.github.njeuk.dbmapper.RowDataExtension._

The macro generates code that relies on that import, unfortunately the intellij IDE doesn't know about the macro generated code and will constantly try and 'tidy up' your imports and remove that import!

We could change the macro to generate code that doesn't depend on the RowDataExtension in the future, avoiding the import.

5 second delay on first connect when using OSX

There is an issue with the interplay between the Java DNS resolver and the Mac, see mauricio/postgresql-async#222 for the resolution.

Extending to MySql would be simple, but not implemented because I have no need for it. (You should probably think very carefully about implementing a new project in MySql anyway ;-) )

Sql Server and Oracle would be a lot harder, you need a replacement for postgresql-async, not impossible, just not a straightforward job to do.

The code generation assumes that you use camelCase in Code and snake_case in the database. It also assumes that table names are not pluralized unlike Rails ActiveRecord.

You can override theses assumptions on a case by case basis using @attributes. But if you do this a lot your code is going to look pretty horrendous.
Maybe we could pass the algorithm in to the code generators and evaluate them using Twitters Eval. Not sure how bad this will impact compile speeds.

Haven't built or tested for other older Scala versions. It probably will work, but no idea, I don't use those version any more.

As of version 3.3 we only compile for Scala 2.12.

Joda dates are no longer used, the standard Java date handling is better.

This project is freely available under the Apache 2 licence, have fun....