Usage

Table Definitions

You want to start by defining your SQL tables.

The following defines a users table with id, first_name, last_name and nickname columns:

import doobie.*

object Users extends TableDefinition("users") {
  val id: Column[Int] = Column("id")
  val firstName: Column[String] = Column("first_name")
  val lastName: Column[String] = Column("last_name")
  val nickname: Column[Option[String]] = Column("nickname")
}

Querying

import doobie.*
import doobie.implicits.*

import Users as t
val columns = Columns((t.firstName, t.lastName))
// columns: Columns[Tuple2[String, String]] = Columns(
//   sql = Fragment("first_name, last_name")
// )

val querySql = sql"SELECT $columns FROM $t WHERE ${t.id === 42}"
// querySql: Fragment = Fragment("SELECT first_name, last_name FROM users WHERE id = ?")
val query = querySql.queryOf(columns)
// query: Query0[Tuple2[String, String]] = doobie.util.query$Query$$anon$5@2f8f5380

val notEqualQuerySql = sql"SELECT $columns FROM $t WHERE ${t.id !== 42}"
// notEqualQuerySql: Fragment = Fragment("SELECT first_name, last_name FROM users WHERE id <> ?")
val notEqualQuery = querySql.queryOf(columns)
// notEqualQuery: Query0[Tuple2[String, String]] = doobie.util.query$Query$$anon$5@35d20548

val multiQuerySql = sql"SELECT $columns FROM $t WHERE ${t.id in Vector(1, 2, 3)}"
// multiQuerySql: Fragment = Fragment("SELECT first_name, last_name FROM users WHERE id IN (?,?,?)")
val multiQuery = multiQuerySql.queryOf(columns)
// multiQuery: Query0[Tuple2[String, String]] = doobie.util.query$Query$$anon$5@5505ce89

val notInMultiQuerySql = sql"SELECT $columns FROM $t WHERE ${t.id notIn Vector(1, 2, 3)}"
// notInMultiQuerySql: Fragment = Fragment("SELECT first_name, last_name FROM users WHERE id NOT IN (?,?,?)")
val notInMultiQuery = multiQuerySql.queryOf(columns)
// notInMultiQuery: Query0[Tuple2[String, String]] = doobie.util.query$Query$$anon$5@6f688b4a

val hasNoNicknameQuerySql = sql"SELECT $columns FROM $t WHERE ${t.nickname === None}"
// hasNoNicknameQuerySql: Fragment = Fragment("SELECT first_name, last_name FROM users WHERE nickname IS NULL")
val hasNoNicknameQuery = hasNoNicknameQuerySql.queryOf(columns)
// hasNoNicknameQuery: Query0[Tuple2[String, String]] = doobie.util.query$Query$$anon$5@3a2ed674

val hasNicknameQuerySql = sql"SELECT $columns FROM $t WHERE ${t.nickname !== None}"
// hasNicknameQuerySql: Fragment = Fragment("SELECT first_name, last_name FROM users WHERE nickname IS NOT NULL")
val hasNicknameQuery = hasNicknameQuerySql.queryOf(columns)
// hasNicknameQuery: Query0[Tuple2[String, String]] = doobie.util.query$Query$$anon$5@1f737c3c

val hasSpecificNicknameQuerySql = sql"SELECT $columns FROM $t WHERE ${t.nickname === Some("Johnny")}"
// hasSpecificNicknameQuerySql: Fragment = Fragment("SELECT first_name, last_name FROM users WHERE nickname = ?")
val hasSpecificNicknameQuery = hasSpecificNicknameQuerySql.queryOf(columns)
// hasSpecificNicknameQuery: Query0[Tuple2[String, String]] = doobie.util.query$Query$$anon$5@64f988dd

Take note that:

• Column types are inferred from the column definitions.
• The returned query type is inferred from the columns used in the query.
• The operators are used to compare columns to values and they are typesafe.

Performing Table Joins

Given that we have the following addresses table:

import doobie.*

object Addresses extends TableDefinition("addresses") {
  val id: Column[Int] = Column("id")
  val userId: Column[Int] = Column("user_id")
  val street: Column[String] = Column("street")
  val city: Column[String] = Column("city")
}

You can perform a table join as follows:

import doobie.*
import doobie.implicits.*

val id = 42
// id: Int = 42

// Construct the prefixed versions of the tables. They will be known as `u` and `a` in the query.
val u = Users as "u"
// u: AliasedTableDefinition[Users] = AliasedTableDefinition(
//   name = Fragment("users AS u"),
//   alias = "u",
//   original = TableDefinition(users)
// )
val a = Addresses as "a"
// a: AliasedTableDefinition[Addresses] = AliasedTableDefinition(
//   name = Fragment("addresses AS a"),
//   alias = "a",
//   original = TableDefinition(addresses)
// )

// Construct the prefixed versions of the columns to prevent name clashes between the tables.
// They will be known as `u.first_name`, `u.last_name`, `a.street` and `a.city` in the query.
val columns = Columns((u(_.firstName), u(_.lastName), a(_.street), a(_.city)))
// columns: Columns[Tuple4[String, String, String, String]] = Columns(
//   sql = Fragment("u.first_name, u.last_name, a.street, a.city")
// )

// You can join the tables using the `===` operator as well. You have to use the `c` method to access the columns.
val query1Sql =
  sql"SELECT $columns FROM $u INNER JOIN $a ON ${u.c(_.id) === a.c(_.userId)} WHERE ${u.c(_.id) === id}"
// query1Sql: Fragment = Fragment("SELECT u.first_name, u.last_name, a.street, a.city FROM users AS u INNER JOIN addresses AS a ON u.id = a.user_id WHERE u.id = ?")
val query1 = query1Sql.queryOf(columns)
// query1: Query0[Tuple4[String, String, String, String]] = doobie.util.query$Query$$anon$5@281f0684

// Alternatively, instead of writing `u.c(_.id)` you can use `u(_.id)`, however that is less typesafe, as `apply` 
// accepts any `SQLDefinition`, not just a `Column`.
val query2Sql =
  sql"SELECT $columns FROM $u INNER JOIN $a ON ${u(_.id)} = ${a(_.userId)} WHERE ${u.c(_.id) === id}"
// query2Sql: Fragment = Fragment("SELECT u.first_name, u.last_name, a.street, a.city FROM users AS u INNER JOIN addresses AS a ON u.id = a.user_id WHERE u.id = ?")
val query2 = query2Sql.queryOf(columns)
// query2: Query0[Tuple4[String, String, String, String]] = doobie.util.query$Query$$anon$5@252f0043

You probably want to check out the doobieroll assembler to help you out with the joins as well.

Composite Columns

You will often need to bundle several columns in the table as a Scala data structure. For example, you might want to return a Names case class instead of a tuple of Strings.

import doobie.*
import doobie.implicits.*

case class Names(firstName: String, lastName: String, nickname: Option[String])

lazy val names: SQLDefinition[Names] = Composite((
  Users.firstName.sqlDef, Users.lastName.sqlDef, Users.nickname.sqlDef
))(Names.apply)(Tuple.fromProductTyped)

Then we can query the composite column as follows:

import doobie.*
import doobie.implicits.*

val id = 42
// id: Int = 42
import Users as t
val columns = names
// columns: SQLDefinition[Names] = Composite(columns: first_name, last_name, nickname)
val querySql = sql"SELECT $columns FROM $t WHERE ${t.id === id}"
// querySql: Fragment = Fragment("SELECT first_name, last_name, nickname FROM users WHERE id = ?")
val query = querySql.queryOf(columns)
// query: Query0[Names] = doobie.util.query$Query$$anon$5@1d177177

You can also use the SQLDefinition as another column in Columns:

import doobie.*
import doobie.implicits.*

val id = 42
// id: Int = 42
import Users as t
val columns = Columns((t.id, names))
// columns: Columns[Tuple2[Int, Names]] = Columns(
//   sql = Fragment("id, first_name, last_name, nickname")
// )
val querySql = sql"SELECT $columns FROM $t WHERE ${t.id === id}"
// querySql: Fragment = Fragment("SELECT id, first_name, last_name, nickname FROM users WHERE id = ?")
val query = querySql.queryOf(columns)
// query: Query0[Tuple2[Int, Names]] = doobie.util.query$Query$$anon$5@4d6c6f77

Often you will want to define the case class in the TableDefinition object itself. There is a helper for that use case:

import doobie.*
import doobie.implicits.*

object Users extends TableDefinition("users") {
  val id: Column[Int] = Column("id")
  val firstName: Column[String] = Column("first_name")
  val lastName: Column[String] = Column("last_name")
  val nickname: Column[Option[String]] = Column("nickname")
  
  case class Names(firstName: String, lastName: String, nickname: Option[String])
  object Names extends WithSQLDefinition[Names](Composite((
    firstName.sqlDef, lastName.sqlDef, nickname.sqlDef
  ))(Names.apply)(Tuple.fromProductTyped))
}

val id = 42
// id: Int = 42
import Users as t
val columns = Columns((t.id, t.Names))
// columns: Columns[Tuple2[Int, Names]] = Columns(
//   sql = Fragment("id, first_name, last_name, nickname")
// )
val querySql = sql"SELECT $columns FROM $t WHERE ${t.id === id}"
// querySql: Fragment = Fragment("SELECT id, first_name, last_name, nickname FROM users WHERE id = ?")
val query = querySql.queryOf(columns)
// query: Query0[Tuple2[Int, Names]] = doobie.util.query$Query$$anon$5@101c4381

Ultimately, you will probably want to use a case class for the entire table row. You can do that as well:

import doobie.*
import doobie.implicits.*

object Users extends TableDefinition("users") {
  val id: Column[Int] = Column("id")
  val firstName: Column[String] = Column("first_name")
  val lastName: Column[String] = Column("last_name")
  val nickname: Column[Option[String]] = Column("nickname")

  case class Names(firstName: String, lastName: String, nickname: Option[String])
  object Names extends WithSQLDefinition[Names](Composite((
    firstName.sqlDef, lastName.sqlDef, nickname.sqlDef
  ))(Names.apply)(Tuple.fromProductTyped))
  
  case class Row(id: Int, names: Names)
  object Row extends WithSQLDefinition[Row](Composite((
    id.sqlDef, Names.sqlDef
  ))(Row.apply)(Tuple.fromProductTyped)) with TableDefinition.RowHelpers[Row](this)
}

val id = 42
// id: Int = 42
import Users as t
val columns = t.Row
// columns: Row = repl.MdocSession$MdocApp1$Users$Row$@534a4e37
val querySql = sql"SELECT $columns FROM $t WHERE ${t.id === id}"
// querySql: Fragment = Fragment("SELECT id, first_name, last_name, nickname FROM users WHERE id = ?")
val query = querySql.queryOf(columns)
// query: Query0[Row] = doobie.util.query$Query$$anon$5@75f001e0

The TableDefinition.RowHelpers trait provides a couple of helpers, such as:

Users.Row.insert
// res2: Update[Row] = doobie.util.update$Update$$anon$6@5b49dffe
Users.Row.insertOnConflictDoNothing(Columns(Users.Names))
// res3: Update[Row] = doobie.util.update$Update$$anon$6@293b9f5d
Users.Row.insertOnConflictDoNothing0
// res4: Update[Row] = doobie.util.update$Update$$anon$6@288157bb
Users.Row.selectAll
// res5: Query0[Row] = doobie.util.query$Query$$anon$5@5d0bbc4a

Check their source code for the documentation.

Inserting

You can insert rows into the table by specifying the individual columns:

import cats.data.NonEmptyVector

insertInto(Users, NonEmptyVector.of(
  Users.id --> 42,
  Users.firstName --> "John",
  Users.lastName --> "Doe",
  Users.nickname --> None,
))
// res6: Fragment = Fragment("INSERT INTO users (id, first_name, last_name, nickname) VALUES (?, ?, ?, ?)")

You can use the composite columns as well:

import cats.data.NonEmptyVector

insertInto(Users, NonEmptyVector.of(
  Users.id --> 42,
) ++: (Users.Names ==> Users.Names("John", "Doe", Some("Johnny"))))
// res7: Fragment = Fragment("INSERT INTO users (id, first_name, last_name, nickname) VALUES (?, ?, ?, ?)")

Or you can use the Row case class:

// Insert single row.
insertInto(Users, Users.Row ==> Users.Row(42, Users.Names("John", "Doe", Some("Johnny"))))
// res8: Fragment = Fragment("INSERT INTO users (id, first_name, last_name, nickname) VALUES (?, ?, ?, ?)")
Users.Row.insert.toUpdate0(Users.Row(42, Users.Names("John", "Doe", Some("Johnny"))))
// res9: Update0 = doobie.util.update$Update$$anon$4@48a83e3a

// Batch insert.
Users.Row.insert.updateMany(NonEmptyVector.of(
  Users.Row(42, Users.Names("John", "Doe", Some("Johnny"))),
  Users.Row(43, Users.Names("Jane", "Doe", None))
))
// res10: Free[[A >: Nothing <: Any] => ConnectionOp[A], Int] = Suspend(
//   a = Uncancelable(
//     body = cats.effect.kernel.MonadCancel$$Lambda$12430/0x0000000103209840@3d9c9105
//   )
// )

Updating

Update is very similar to insert:

import cats.data.NonEmptyVector
import doobie.*
import doobie.implicits.*


sql"""
${updateTable(Users,
  Users.firstName --> "John",
  Users.lastName --> "Doe"
)} WHERE ${Users.id === 42}
"""
// res11: Fragment = Fragment("
//       UPDATE users SET first_name = ?, last_name = ? WHERE id = ?
//       ")

// Or using a collection:
sql"""
${updateTable(Users, NonEmptyVector.of(
  Users.firstName --> "John",
  Users.lastName --> "Doe"
))} WHERE ${Users.id === 42}
"""
// res12: Fragment = Fragment("
//       UPDATE users SET first_name = ?, last_name = ? WHERE id = ?
//       ")

You can use the composite columns as well:

import cats.data.NonEmptyVector
import doobie.*
import doobie.implicits.*

sql"""
${updateTable(Users, Users.Names ==> Users.Names("John", "Doe", Some("Fast Johnny")))}
WHERE ${Users.id === 42}
"""
// res13: Fragment = Fragment("
//       UPDATE users SET first_name = ?, last_name = ?, nickname = ?
//       WHERE id = ?
//       ")

Final Notes

• Extra effort has been made to document each type and function in the library, so make sure to check out the source code.
• The library is still in early stages of development, so breaking changes can occur.