Querying

Main benefits of this library are:

You can not mix up the order of the columns in the query and their types.
Certain operations, like equality, set membership, comparisons can be done in type-safe way.

All the following examples have this code in common:

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"),
//   read = doobie.util.Read$Transform@666d2b9c
// )

Equality

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$3@9fc3fbb

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$3@7a91edfe

Set Membership

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$3@1d7c2efc

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$3@36c54207

Nullability

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$3@724e3df2

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$3@21e22d34

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$3@5f30baaf

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")
  
  case class Address(street: String, city: String)
  object Address extends WithSQLDefinition[Address](Composite((
    street.sqlDef, city.sqlDef
  ))(Address.apply)(Tuple.fromProductTyped))
}

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"),
//   read = doobie.util.Read$Transform@51ddf85d
// )

// You can join the tables using the `===` operator as well.
val querySql =
  sql"SELECT $columns FROM $u INNER JOIN $a ON ${u(_.id) === a(_.userId)} WHERE ${u(_.id) === id}"
// querySql: 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 query = querySql.queryOf(columns)
// query: Query0[Tuple4[String, String, String, String]] = doobie.util.query$Query$$anon$3@13ea1320

Left/Right Joins

When performing a left/right join one side, which was previously non-nullable, becomes nullable.

To deal with that, use .option when specifying your columns or SQLDefinitions.

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)
// )

val columns = Columns((u(_.firstName), u(_.lastName), a(_.street.option)))
// columns: Columns[Tuple3[String, String, Option[String]]] = Columns(
//   sql = Fragment("u.first_name, u.last_name, a.street"),
//   read = doobie.util.Read$Transform@2c67f180
// )

val query1Sql =
  sql"SELECT $columns FROM $u LEFT JOIN $a ON ${u(_.id) === a(_.userId)} WHERE ${u(_.id) === id}"
// query1Sql: Fragment = Fragment("SELECT u.first_name, u.last_name, a.street FROM users AS u LEFT JOIN addresses AS a ON u.id = a.user_id WHERE u.id = ?")
val query1 = query1Sql.queryOf(columns)
// query1: Query0[Tuple3[String, String, Option[String]]] = doobie.util.query$Query$$anon$3@6a90ffc9

You can use the doobieroll assembler to turn relational data back to object hierarchies.