tpolecat/doobie

Functional JDBC layer for Scala.

doobie's Issues

Add CatchSQL typeclass and bedazzlement

Catchable[M] implies CatchSQL[M] ... combinators for catching SQL state. Can dig up H2 SQLState table from the wastebin.

Consider unsafeRun and law of Capture

Adding an unsafeRun method on Capture would allow us to define testable laws. We could even subclass Catchable which would allow us to have testable laws for exception handling as well.

Macro for N-ary sql interpolator

See comments in #108. This will also open the possibility for specifying In/Out/InOut parameters to provide support for callable statement literals.

This has the potential to be terrible because I would like to continue supporting 2.10 and at least some aspects of macromé are incompatible. Hoping I will be able to use a compatible subset.

Alternative Capture[Task] that forks

This is easy, but we need to figure out a coherence policy. Selecting an alternative Capture instance will not affect the computed results so maybe it's not an issue. Or figure out how to tag higher-kinded types.

Support for query fragments

@wedens suggests concatenable query fragments that build up a set of parameters along with the sql string. Need HList support to make this convenient.

Single-element products should be treated as atoms

Right now you have to use xmap to create an atom for a 1-element product; we should be able to do this automatically.

contrib-h2

Need minimal stuff like Array for tut doc.

Benchmarks

Add benchmarking against raw JDBC and Slick. Setting the default fetch size and cursor type in Transactor will be helpful, but who knows.

Analysis fails for array/object types.

Figure out diagnostics abstraction for Update/Query.

There are two identical traits that probably don't need to exist at all. Does it make sense to abstract out a supertype for Update and Query?

Finish pending items in PG and H2 tests

String interpolator for CallableStatement

This will require some kind of tagging to distinguisg In/Out/InOut parameters. Maybe start with something simple with many In and one Out? Depends on #5.

Make HList work for params/rows

I expected to get this for free with ProductTypeclass but it doesn't seem like I do. Probably a simple fix.

Unclear requirements of sql string building

I have

case class Email(value:String)
case class PlayerId(value:String)
case class Player(id:PlayerId, email:Email)

I am trying to construct an sql string using sql

def insertPlayer(p: Player): ConnectionIO[Int] = {
    sql"""
          INSERT INTO players
          VALUES ($p.id.value, $p.email.value)
    """.update.run
  }

The above requires an implicit Atom[Player] in scope, which as i understand, is a contradiction, since a Player is not an atom but a product of PlayerId and Email.

Of course

def insertPlayer(p: Player): ConnectionIO[Int] = {
    val id = p.id.value
    val email = p.email.value
    sql"""
          INSERT INTO players
          VALUES ($id, $email)
    """.update.run
  }

runs fine.

Is my understanding fundamentally wrong? Or maybe i missed some documentation? Or might this jsut be a bit unclear for newbies on noobie?

Support for IN (...) clauses

Need a better solution than this:

def mkSql(names: List[String]): String =
  s"select id, name, age from person where name in (${names.map(_ => "?").mkString(", ")})"

def mkSet(names: List[String]): PreparedStatementIO[Unit] =
  names.zipWithIndex.foldRight(().point[PreparedStatementIO]) { case ((s, i), p) =>
    HPS.set(i + 1, s) *> p
  }

val names = List("Alice", "Bob")

scala> HC.process[(Int, String, Option[String])](mkSql(names), mkSet(names)).quick.run
  (1,Bob,None)
  (3,Alice,Some(42))

Support (or sugar? or nothing?) for PostgreSQL's composite types

PostgreSQL supports user-defined types.

Type creation is easily done with:

CREATE TYPE Point2D AS (x NUMERIC, y NUMERIC);

Value construction is done using 'ROW':

SELECT (ROW(10, 22.2) :: Point2D) AS point 

Value deconstruction is done using '.':

SELECT (ROW(10, 22.2) :: Point2D).x

Add optionT to Query/Query0

As suggested by @cencarnacion. Would it make sense to also have .listT and so on? I'm not convinced this is useful but it's easy and doesn't affect the lower-level APIs at all, so I'm fine giving it a try.

Change H2 tests to do write+read like PG tests do.

Duplicate close vals in generated code

Running sbt compile generates the following error for me:

[error]<blah>/doobie/core/target/scala-2.10/src_managed/main/doobie/free/connection.scala:307: close is already defined as value close
[error]   val close: ConnectionIO[Unit] =
[error]       ^

The generated code has this:

  /** 
   * @group Constructors (Primitives)
   */
  val close: CallableStatementIO[Unit] =
    F.liftFC(Close)

  /** 
   * @group Constructors (Primitives)
   */
  val close: CallableStatementIO[Unit] =
    F.liftFC(Close1)

Java info:

doobie master
❯ java -v
Unrecognized option: -v
Error: Could not create the Java Virtual Machine.
Error: A fatal exception has occurred. Program will exit.

doobie master  
❯ java --version
Unrecognized option: --version
Error: Could not create the Java Virtual Machine.
Error: A fatal exception has occurred. Program will exit.

doobie master  
❯ java -version
java version "1.7.0_21"
Java(TM) SE Runtime Environment (build 1.7.0_21-b12)
Java HotSpot(TM) 64-Bit Server VM (build 23.21-b01, mixed mode)

One of these days I'm going to get it right on the first try.

MySQL contrib package

Here's one that @adelbertc needed (no idea if it works).

implicit val BigIntMeta = 
  Meta.other[java.math.BigInteger]("BIGINT UNSIGNED").nxmap[BigInt](new BigInt(_), _.bigInteger)

Reference here.

Update to scalaz 7.1.1

tut is going to get in the way here. Need to remove the scalaz dependency.

SqlStateOps instance not inferrable for non-aliased free monad

The SqlStateOps syntax in the PostgreSQL add-on doesn't work when the unaliased Free type is inferred. Unclear how to make this work.

scala> 1.point[ConnectionIO].onStringDataRightTruncationClass01(null)
res1: doobie.imports.ConnectionIO[Int] = Gosub()

scala> 1.point[ConnectionIO].map(_ + 1).onStringDataRightTruncationClass01(null)
<console>:21: error: value onStringDataRightTruncationClass01 is not a member of scalaz.Free[[x]scalaz.Coyoneda[doobie.free.connection.ConnectionOp,x],Int]
              1.point[ConnectionIO].map(_ + 1).onStringDataRightTruncationClass01(null)
                                               ^

scala> (1.point[ConnectionIO].map(_ + 1) : ConnectionIO[Int]).onStringDataRightTruncationClass01(null)
res2: doobie.imports.ConnectionIO[Int] = Gosub()

Upgrade to scalaz-stream 0.6a

Add error handling and resource cleanup to FreeUsage

well duh

Add HTTP4s example

This is working on a WIP branch of HTTP4s; just need to wait for a release with scalaz 7.1 and streams 0.5a

Split Atom

There is no reason in principle why every readable type needs to be writable; split Atom into read/write pairs (like Argonaut does).

Batch update combinator

Add something like this to HPS.

/** Add many sets of parameters and execute as a batch update, returning total rows updated. */
def updateMany[F[_]: Foldable, A: Composite](fa: F[A]): PreparedStatementIO[Int] =
  fa.foldRight(FPS.executeBatch)((a, b) => HPS.set(a) *> FPS.addBatch *> b).map(_.sum)

Meta constructor for struct types

I think the path from here to there is:

• On Meta we need toObject and fromObject that perform a cast at the lowest level and then apply any accumulated invariant mappings. Propagate this to Atom.
• On Composite we then implement toObjectArray and fromObjectArray inductively, and from there trivially we can implement Meta.fromComposite.

Add PostGIS support

Should be easy, just need to add the dependency and mappings in pgtypes.scala.

/cc @aloiscochard

book of doobie errata

In chapter 04 I believe there is an example which failed to be updated:

scala> (sql"select code, name, population, gnp from country"
     |    .query[(Code, Country)] // Query0[(Code, Country)]
     |    .process.take(5)        // Process[ConnectionIO, (Code, Country)]
     |    .list                   // ConnectionIO[List[(Code, Country)]]
     |    .map(_.toMap)           // ConnectionIO[Map[Code, Country]]
     |    .quick.run)
  Map(Code(ANT) -> Country(Netherlands Antilles,217000,Some(1941.0)), Code(DZA) -> Country(Algeria,31471000,Some(49982.0)), Code(ALB) -> Country(Albania,3401200,Some(3205.0)), Code(NLD) -> Country(Netherlands,15864000,Some(371362.0)), Code(AFG) -> Country(Afghanistan,22720000,Some(5976.0)))

i.e. Code(ANT) should refer to the Antilles Country, not the "Netherlands Antilles" one.

Finish up initial book of doobie.

Nullability via Maybe (or other?)

HikariTransactor

Example of tagged types for alternative column mapping

Example with > 22 columns

@cencarnacion asks how to do this, and I have never tried it. So let's add an example with insert and select for a very wide table.

Add doc for connection pooling

Need doc for using HikiriConnectionPool and for implementing your own Transactor, as well as just doing liftK[F].run(conn) if you already happen to have a connection.

Finish pending items in core tests.

Add gitter+travis webhook

Per https://github.com/non/cats/pull/49

Transactor for existing DataSource

It will be useful when some pool other than hikari is used or DataSource already exists (e.g. when using doobie with plain jdbc).

Structured Logging

Add exception-proof structured logging that can produce usable traces like this. Need to have sliding windows or something to prevent unbounded trace growth. This will require messing with the Kleisli interpreters or adding additional ones.

Add callablestatement module in hi

Ensure YOLO and Specs2 handle all Query/Update constructions

Pretty sure YOLO doesn't handle Query or Update.

Example with params as list of values with witnesses

List[(A, Atom[A]) forSome { type A }] or maybe with Sigma[P[_]] {type I; val i: I; val fi: P[I]}.

Algebras and free monads thereof for Postgres driver extensions

Need free-level algebras for:

• PGConnecton
• CopyManager
• FastPath
• LargeObjectManager

And hi-level combinators where sensible.

Assuming this works, hacking up a Process[Task,PGNotification] would be a really cool demo.

N.B. also need a liftPGConnection : ConnectionIO[A] implemented in terms of unwrap.

Mention casting on the database

showing something like:

scala> sql"UPDATE core.roles SET permissions = $ps::core.permission[]".update.run.transact(xa).run 
res11: Int = 1 
scala> val q = sql"SELECT permissions FROM core.roles".query[List[Permission.Value]].unique.transact(xa).run 
q: List[msw.core.Permission.Value] = List(mswAdmin, teacher, student)

as a demonstration of how to handle postgres' asymetry for Out[String], In[PGObject]
having the cast inside the sql statement itself brings back the sanity of Out[String], In[String], even if it is an unsafe cast.

Multi-dimensional arrays for PostgreSQL

This should be derivable for arbitrary dimensions.

Add support for using Postgres enum types

This is simply Meta[String].nxmap(...). Add an example in the doc.

Recommended in #94

EDIT actually it's much more complicated than just invariant mapping of String; it's platform-specific and very twitchy.

Upgrade to PostgreSQL JDBC 9.4

Add 'option' combinator for Query

Need a way to get 0 or 1 rows, as suggested by @prange in #43.

Upgrade to shapeless 2.2.0