Schema awareness

Background

I am working on a side project called Troy, which is a driver for Cassandra. Unlike Doobie, Troy is a very thin layer around Cassandra's Java driver, it doesn't try to provide any principled database access, nor any runtime classes. Instead it focuses on validating your CQL statements against the schema, inferring the type signature of the query, and generating the boilerplate to execute the query and parse the rows.

The approach I took to validate the CQL is rather interesting. I managed to define bunch of typeclasses to model Cassandra's type system to allow the compiler to understand and type-check your queries (with minimal use of macros). I recently gave a talk about my work at Scala.world conf, please have a look on the slides here

Proposal

Build something like Troy, but for Relational databases wrapping Doobie, possibly called Scoobie as suggested by @fommil :).

Users will write something like

import scoobie.query

case class Country(code: String, name: String, population: Long)
case class FindQuery(n: String)

val listByName = query[FindQuery, Country]("select code, name, population from country where name = ?")
val find = listByName.map(_.option)

ant then ...

scala> find(FindQuery("France")).transact(xa).unsafeRunSync
res3: Option[Country] = Some(Country(FRA,France,59225700))

Implementation

Troy has at least two drawbacks

• The implementation is mind-twisting and requires lots of boilerplate
• Some techniques like Aux are going to be simplified by Dotty. Which means Troy will need to be rewritten.

As a solution, I decided to write a codegen tool to generate the typeclasses boilerplate from some definition file. I wrote a POC for the syntax here, you can view and comment on it here

What I need from Doobie

Collaboration.. if anyone is interested to work on the codegen tool. My SQL knowledge is not as deep as Cassandra, so help is much appreciated.

Additionally, exposing some low level API (if not already exposed), that allows Scoobie to specify the database type of the selected columns and bind markers.

For example, in order to be able to decode the row returned by executing a query like select foo from mytable you need two piece of information:

1. the type of column "foo" at the database (which Doobie now only knows at runtime, after executing the query)
2. the JVM type provided by User as typeparam (which is statically known at compile time)

However, since Scoobie knows the database type at compile time, it could pass it to Doobie as a type param, allowing Doobie to pick the correct encoders/decoders at compile time.

Start by reviewing the linked Schematic codegen-tool POC and Doobie's existing handling of database column types and bind markers. Trace how row decoding and encoders/decoders currently learn types at runtime. A concrete scope and acceptance criteria are still needed; done would require an agreed low-level API and a working schema-aware query path.