Slick to doobie notes

SlickToDoobieNotes.md

• doobie uses ConnectionIO instead of DBIO in Slick. Both are composable using for-comprehension.
• In Slick, you write connection.run(someDBIO) while in doobie someConnectionIO.transact(connection)
  • Slick: conn.run(sqlu"INSERT INTO FileData (fileId, data) VALUES (${s.fileId}, ${s.data})"
  • doobie: sql"INSERT INTO FileData (fileId, data) VALUES (${s.fileId}, ${s.data})".update.run.transact(conn)
• In Slick, you have to explicitly say that the statements will run in the same transaction. In doobie, the transact method always runs all the statements in one transaction.
• To distinguish between queries and non-queries, you use sql and sqlu interpolators in Slick. In doobie, you always use sql interpolator but the suffix differs:
  • Query: sql"SELECT data FROM FileData WHERE fileId = ${fileId}".query[FileData].to[Seq].transact(conn)
  • Streaming query: sql"SELECT data FROM FileData WHERE fileId = ${fileId}".query[FileData].stream.transact(conn)
  • Non-query: sql"INSERT INTO FileData (fileId, data) VALUES (${s.fileId}, ${s.data})".update.run.transact(conn)
• In Slick, you can use raw-interpolation using #${rawSql} syntax. In doobie, you must compose the statement using fragments.
  • sql"INSERT INTO " ++ Fragment.const(myTableName) ++ fr"(data) VALUES (${s.data})".
  • Please note that fr interpolator works as sql interpolator, so s.data is sent to database as a SQL parameter.
• Doobie automatically deserializes primitive types (ints, strings, ...), and also case-classes composed from these primitives. So, often you don't have to write any deserializer.

Queries in sequence

import doobie._
import doobie.implicits._
import cats.implicits._ // for the `traverse` method

List( // has to be List, not just a Seq
  sql"truncate table " ++ Fragment.const(tableName),
  sql"insert into " ++ Fragment.const(tableName) ++ sql" values (${hash.bytes}, 2)"
).traverse(_.update.run).transact(conn)

Using Postgres-specific features

import doobie._
import doobie.implicits._
import doobie.postgres.implicits._

val query = sql"select bcFileId, bcResult, bcReason, bcLastScans from " ++
  Fragment.const(tableName) ++
  sql" where bcFileId = ${fileId.bytes}"

query
  .query[(Array[Byte], Int, Int, Map[String, String])] // Map[String, String] <-> hstore
  .stream
  .transact(shard.conn)

Using IN clause
Oficial docs

import doobie._
import doobie.implicits._

val query = sql"select bcFileId, bcResult, bcReason from " ++
  Fragment.const(tableName) ++
  sql" where " ++ Fragments.in(fr"bcFileId", NonEmptyList.fromListUnsafe(hashes.map(_.bytes).toList))

query
  .query[(Array[Byte], Int, Int)]
  .stream
  .transact(shard.conn)