simpledba - Simple Database Access for Scala

Build Status Coverage Status

TL;DR

Map case classes into columns for your DB, create typesafe queries that run with your favourite effect and streaming library.

Goals

  • Prevent you from writing queries which the database won’t allow you to run, using types.
  • Support as many column family style DB’s as possible. (DynamoDB, Cassandra, Google Cloud Datastore)
  • Support JDBC with full typed support for non joined queries. Joined queries can be created with un-typechecked SQL.
  • Facilitate writing DB agnostic query layers. Switching between AWS and Google Cloud? No Problem.
  • Be a library, not a framework. No magic, just principles.

Quickstart

val simpledbaVersion = "0.1.11-SNAPSHOT"

libraryDependencies ++= Seq(
  "io.doolse" %% "simpledba-jdbc",
  "io.doolse" %% "simpledba-dynamodb",
  "io.doolse" %% "simpledba-circe"
).map(_ % simpledbaVersion)

Define your domain model case classes and a database agnostic query trait. In this example we will be using ZIO with it’s stream library for the effects.

import java.util.UUID

import io.doolse.simpledba._
import zio._
import zio.stream._

case class User(userId: UUID, firstName: String, lastName: String, yearOfBirth: Int)

case class Car(id: UUID, make: String, model: String, ownerId: UUID)

// Support multiple DB types by parameterizing on the write operation type
trait Queries[W] {
  type S[A] = Stream[Throwable, A]
  type F[A] = Task[A]
  type Writes[A] = WriteQueries[S, F, W, A]
  def users: Writes[User]
  def cars: Writes[Car]
  def usersByFirstName: String => S[User]
  def carsForUser: UUID => S[Car]
  def flush: S[W] => F[Unit]
}

Write your app:

trait ExampleApp[W] {

  type S[A] = Stream[Throwable, A]
  type F[A] = Task[A]

  def queries: Queries[W]
  def initSchema: F[Unit]

  def initData: F[Unit] = {
    val magId = UUID.randomUUID()
    val mahId = UUID.randomUUID()
    queries.flush {
      queries.users.insertAll(
        Stream(User(magId, "Jolse", "Maginnis", 1980), User(mahId, "Jolse", "Mahinnis", 1999))) ++
        queries.cars.insertAll(
          Stream(
            Car(UUID.randomUUID(), "Honda", "Accord Euro", magId),
            Car(UUID.randomUUID(), "Honda", "Civic", mahId),
            Car(UUID.randomUUID(), "Ford", "Laser", magId),
            Car(UUID.randomUUID(), "Hyundai", "Accent", magId)
          ))
    }
  }

  import zio.console._

  def querySomeData: TaskR[Console, Unit] =
    (for {
      user <- queries.usersByFirstName("Jolse")
      _ <- ZStream.fromEffect {
        queries.carsForUser(user.userId).runCollect.tap { cars =>
          putStrLn(s"${user.firstName} ${user.lastName} owns ${cars}")
        }
      }
    } yield ()).runDrain

  def run() = {
    val runtime = new DefaultRuntime {}
    runtime.unsafeRun(initSchema *> initData *> querySomeData)
  }
}

Now we can create a DB specific implementation of the app, let’s start with JDBC (using embedded HSQL).

import io.doolse.simpledba.jdbc._
import io.doolse.simpledba.interop.zio._
import zio.interop.catz._

class JDBCApp(logger: JDBCLogger[Task]) extends ExampleApp[JDBCWriteOp] {

  import java.sql.DriverManager
  import io.doolse.simpledba.jdbc.hsql._

  // Use HSQL driver, single connection
  val mapper           = hsqldbMapper
  val singleConnection = DriverManager.getConnection("jdbc:hsqldb:mem:example")

  val jdbcQueries = mapper.queries(
    new JDBCEffect[S, F](SingleJDBCConnection(singleConnection), logger))

  val carTable  = mapper.mapped[Car].table("cars").key('id)
  val userTable = mapper.mapped[User].table("users").key('userId)

  import jdbcQueries._

  override val queries: Queries[JDBCWriteOp] = new Queries[JDBCWriteOp] {
    val users: Writes[User]                 = writes(userTable)
    val cars: Writes[Car]                   = writes(carTable)
    val usersByFirstName: String => S[User] = query(userTable).where('firstName, BinOp.EQ).build
    val carsForUser: UUID => S[Car]         = query(carTable).where('ownerId, BinOp.EQ).build
    val flush: S[JDBCWriteOp] => F[Unit]    = jdbcQueries.flush
  }
  override val initSchema: F[Unit] = jdbcQueries.flush {
    Stream(carTable, userTable).flatMap(dropAndCreate)
  }
}

Running this gives you the expected result:

new JDBCApp(NothingLogger()).run()

Jolse Maginnis owns List(Car(6140a600-7c6a-461d-b109-ec85e63ba1b9,Ford,Laser,0ec55f05-6460-4186-9d02-f4df32fbe14d), Car(9fdb71e5-19dc-43af-a5d6-c4ce49a240f1,Honda,Accord Euro,0ec55f05-6460-4186-9d02-f4df32fbe14d), Car(c946fba1-9bce-4bf3-9efb-87d682cd9321,Hyundai,Accent,0ec55f05-6460-4186-9d02-f4df32fbe14d))
Jolse Mahinnis owns List(Car(a4677328-3ede-4a3f-8629-b92be538f368,Honda,Civic,b405415a-1e5f-413c-844d-7ebfcb999b22))

To see what’s going on with SQL being executed, you can log the queries:

DROP TABLE cars IF EXISTS -- Values: 
CREATE TABLE cars (id UUID NOT NULL,make LONGVARCHAR NOT NULL,model LONGVARCHAR NOT NULL,"ownerId" UUID NOT NULL,PRIMARY KEY(id)) -- Values: 
DROP TABLE users IF EXISTS -- Values: 
CREATE TABLE users ("userId" UUID NOT NULL,"firstName" LONGVARCHAR NOT NULL,"lastName" LONGVARCHAR NOT NULL,"yearOfBirth" INTEGER NOT NULL,PRIMARY KEY("userId")) -- Values: 
INSERT INTO users ("userId","firstName","lastName","yearOfBirth") VALUES (?,?,?,?) -- Values: bdf4ac30-2965-4f3b-a4ef-b7b99c2c9923,Jolse,Maginnis,1980
INSERT INTO users ("userId","firstName","lastName","yearOfBirth") VALUES (?,?,?,?) -- Values: 7eb1a207-cfa2-4f94-9ecd-78ae1fec8cd9,Jolse,Mahinnis,1999
INSERT INTO cars (id,make,model,"ownerId") VALUES (?,?,?,?) -- Values: c75f2e63-921b-4761-bdcc-706560413150,Honda,Accord Euro,bdf4ac30-2965-4f3b-a4ef-b7b99c2c9923
INSERT INTO cars (id,make,model,"ownerId") VALUES (?,?,?,?) -- Values: f8be7135-8ab3-4ef1-afb5-57ef7833fef3,Honda,Civic,7eb1a207-cfa2-4f94-9ecd-78ae1fec8cd9
INSERT INTO cars (id,make,model,"ownerId") VALUES (?,?,?,?) -- Values: 97cd8954-6a78-4e83-a317-f855166ba15b,Ford,Laser,bdf4ac30-2965-4f3b-a4ef-b7b99c2c9923
INSERT INTO cars (id,make,model,"ownerId") VALUES (?,?,?,?) -- Values: de01e43c-2b9a-4ea9-bc4a-df37073cff13,Hyundai,Accent,bdf4ac30-2965-4f3b-a4ef-b7b99c2c9923
SELECT "userId","firstName","lastName","yearOfBirth" FROM users WHERE "firstName" = ?  -- Values: Jolse
SELECT id,make,model,"ownerId" FROM cars WHERE "ownerId" = ?  -- Values: 7eb1a207-cfa2-4f94-9ecd-78ae1fec8cd9
Jolse Mahinnis owns List(Car(f8be7135-8ab3-4ef1-afb5-57ef7833fef3,Honda,Civic,7eb1a207-cfa2-4f94-9ecd-78ae1fec8cd9))
SELECT id,make,model,"ownerId" FROM cars WHERE "ownerId" = ?  -- Values: bdf4ac30-2965-4f3b-a4ef-b7b99c2c9923
Jolse Maginnis owns List(Car(97cd8954-6a78-4e83-a317-f855166ba15b,Ford,Laser,bdf4ac30-2965-4f3b-a4ef-b7b99c2c9923), Car(c75f2e63-921b-4761-bdcc-706560413150,Honda,Accord Euro,bdf4ac30-2965-4f3b-a4ef-b7b99c2c9923), Car(de01e43c-2b9a-4ea9-bc4a-df37073cff13,Hyundai,Accent,bdf4ac30-2965-4f3b-a4ef-b7b99c2c9923))