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$$anon$5@1ef82bd
// )

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@1cc7febd

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@24529255

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@7e79e73e

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@1351d653

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@5f57d609

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@9ad04d2

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@5881274b

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$$anon$5@dd550ba
// )

// 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$3@778c9ae7

// 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$3@52bda39e

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$$anon$5@22078b7e
// )

val query1Sql =
  sql"SELECT $columns FROM $u LEFT 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 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@76833003

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