Snippets

Snippets are functions that you can use to construct common SQL fragments.

Import these function in scope with:

import doobieroll.snippets._

Let’s define some TableColumns first and see how we can create common SQL snippets from them:

import doobieroll.TableColumns
case class DbCompany(
  id: UUID,
  name: String,
  phoneNumber: String,
)

object DbCompany {
  val columns: TableColumns[DbCompany]  = TableColumns.deriveSnakeCaseTableColumns(tableName = "company")
}

case class DbEmployee(
  id: UUID,
  companyId: UUID,
  firstName: String,
  lastName: String,
)

object DbEmployee {
  val columns: TableColumns[DbEmployee]  = TableColumns.deriveSnakeCaseTableColumns(tableName = "employee")
}

Select columns from single table - selectColumnsFrom

selectColumnsFrom(DbCompany.columns)
// res0: doobie.util.fragment.Fragment = Fragment("SELECT id,name,phone_number FROM company ")

Select columns from multiple tables - selectColumns

selectColumns(
  DbCompany.columns.prefixed("c"),
  DbEmployee.columns.prefixed("e")
) ++ fr"FROM company c LEFT JOIN employee e ON company.id = employee.company_id"
// res1: doobie.util.fragment.Fragment = Fragment("SELECT c.id,c.name,c.phone_number ,e.id,e.company_id,e.first_name,e.last_name FROM company c LEFT JOIN employee e ON company.id = employee.company_id ")