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")
}

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$3@6f8dc710

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"),
//   read = doobie.util.Read$$anon$5@9c90534
// )
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$3@17416247

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"),
//   read = doobie.util.Read$$anon$5@3acd1b73
// )
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$3@1bdcecc2

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$@b8ee6fb
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$3@579b873e

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

Users.Row.insert
// res2: Update[Row] = doobie.util.update$Update$$anon$5@14b03c93
Users.Row.insertOnConflictDoNothing(Columns(Users.Names))
// res3: Update[Row] = doobie.util.update$Update$$anon$5@3fd9820
Users.Row.insertOnConflictDoNothing0
// res4: Update[Row] = doobie.util.update$Update$$anon$5@5c87b47b
Users.Row.selectAll
// res5: Query0[Row] = doobie.util.query$Query$$anon$3@19571bcc

Check their source code for the documentation.