Skip to main content

How to Write Joins and Inspect the Generated SQL

In this guide, we query two related tables, author and book, with the sql module's SqlQuery builder. By the end, you will have inner and left joins that return each book with its first author and each author with their books, filtered and sorted, plus the exact SQL each join sends to the database, printed at run time and written to .sql files at compile time.

We describe the link between the tables once, as a Rel, and reuse it in every query:

  • Define two tables that reference each other
  • Join each book to its first author (many-to-one)
  • List each author's books (one-to-many)
  • Keep authors who have no books yet (left join)
  • Filter and sort a join
  • Print the generated SQL for SQLite and PostgreSQL
  • Write the SQL to files at compile time for review

The Problem​

A join returns columns from two tables in one row, and your code has to split that row back into two values. Without a query builder, you write the column list, the ON condition and the row mapping by hand, and keep all three in sync with the case classes:

import java.sql.DriverManager

case class Author(authorId: Int, name: String, born: Option[Int])
case class Book(bookId: Int, title: String, year: Int, firstAuthorId: Int)

val connection = DriverManager.getConnection("jdbc:sqlite:library.db")
val statement = connection.prepareStatement(
  """SELECT b.book_id, b.title, b.year, b.first_author_id, a.author_id, a.name, a.born
    |FROM book b JOIN author a ON b.first_author_id = a.author_id""".stripMargin
)
val rs = statement.executeQuery()
val rows = Iterator
  .continually(rs.next())
  .takeWhile(identity)
  .map { _ =>
    val book   = Book(rs.getInt(1), rs.getString(2), rs.getInt(3), rs.getInt(4))
    val born   = rs.getInt(7)
    val author = Author(rs.getInt(5), rs.getString(6), if (rs.wasNull()) None else Some(born))
    (book, author)
  }
  .toList
connection.close()

Every field added to Book or Author means editing the SELECT list and shifting every column index after it. A wrong index still compiles and silently puts the wrong value in the wrong field. And the SQL is a string, so the only way to review the joins a program runs is to read every string in the code base.

In this guide, we let SqlQuery write the column list and the ON condition from the tables' schemas, decode each row into a tuple of case classes, and print the SQL it generates so you can check it.

Prerequisites​

Add the sql module and the SQLite JDBC driver to your build.sbt:

// Scala 3.3 to 3.8
libraryDependencies += "dev.zio" %% "zio-blocks-sql" % "0.0.56"

// Scala 3.9 and newer
libraryDependencies += "dev.zio" %% "zio-blocks-next-sql" % "0.0.56"
scalacOptions += "-experimental"

// The SQLite JDBC driver, for every Scala version
libraryDependencies += "org.xerial" % "sqlite-jdbc" % "3.53.4.0"

The sql module is published for Scala 3 only. Running queries over JDBC needs the JVM and JDK 21 or newer.

We assume you have connected to a database and run a query with the sql"..." interpolator before, as in Build Your First Database Repository on SQLite. The same queries run on PostgreSQL; How to Connect to PostgreSQL with a Connection Pool shows how to build the JdbcTransactor for it.

note

SqlQuery in zio.blocks.sql.query is not the query DSL from the Query DSL with Reified Optics guides. Those guides build their own SQL generator from SchemaExpr optics; this guide uses the join builder that ships with the sql module.

Everything in this guide comes from three imports:

import zio.blocks.schema.Schema
import zio.blocks.sql.query.*
import zio.blocks.sql.*

The Library Tables​

Our library has authors and books. Each book stores the ID of its first author, so many books can point at one author:

case class Author(authorId: Int, name: String, born: Option[Int])
object Author {
  given Schema[Author] = Schema.derived
}

case class Book(bookId: Int, title: String, year: Int, firstAuthorId: Int)
object Book {
  given Schema[Book] = Schema.derived
}

val authors = Table.derived[Author]
val books   = Table.derived[Book]

Table.derived maps each case class to a table named after the type, author and book, with one snake-case column per field:

println(s"${authors.name}: ${authors.columns.mkString(", ")}")
// author: author_id, name, born
println(s"${books.name}: ${books.columns.mkString(", ")}")
// book: book_id, title, year, first_author_id
warning

Give every column a name that no other table in the join uses, as author_id and book_id do here. Rows decode into tuples by column label, and when two joined tables both have an id column, every id in the tuple is read from the first one. With Author(id, ...) and Book(id, ...), each book comes back paired with an Author that carries the book's ID. The SQL itself is correct; only the decoding is wrong.

We create both tables in a temporary SQLite file, so each run starts empty. A file-backed database is needed because every connect opens a new connection, and an in-memory SQLite database (jdbc:sqlite::memory:) is empty again on every new connection. We write the CREATE TABLE statements by hand, because Table#createTable doesn't declare a PRIMARY KEY:

val dbFile     = java.nio.file.Files.createTempFile("library", ".db")
val transactor = JdbcTransactor.fromUrl(s"jdbc:sqlite:$dbFile", SqlDialect.SQLite)

transactor.connect {
  sql"""CREATE TABLE author (
    author_id INTEGER PRIMARY KEY,
    name      TEXT NOT NULL,
    born      INTEGER
  )""".update
  sql"""CREATE TABLE book (
    book_id         INTEGER PRIMARY KEY,
    title           TEXT NOT NULL,
    year            INTEGER NOT NULL,
    first_author_id INTEGER NOT NULL REFERENCES author (author_id)
  )""".update
}

Then we add four authors and four books. Li Haoyi has no book in our library yet, which matters for the left join later:

val authorRepo = Repo.derived[Author, Int]
val bookRepo   = Repo.derived[Book, Int]

transactor.connect {
  authorRepo.insert(Author(1, "Martin Odersky", Some(1958)))
  authorRepo.insert(Author(2, "John A. De Goes", None))
  authorRepo.insert(Author(3, "Paul Chiusano", None))
  authorRepo.insert(Author(4, "Li Haoyi", None))

  bookRepo.insert(Book(1, "Zionomicon", 2024, 2))
  bookRepo.insert(Book(2, "Programming in Scala", 2008, 1))
  bookRepo.insert(Book(3, "Functional Programming in Scala", 2014, 3))
  bookRepo.insert(Book(4, "Programming in Scala, Fifth Edition", 2021, 1))
}

Join Each Book to Its First Author​

A Rel describes how two tables are linked: a foreign-key column in one table equals a key column in the other. Rel.manyToOne takes the table holding the foreign key, a selector for that field, the referenced table and a selector for its key:

val bookAuthor = Rel.manyToOne(books, _.firstAuthorId, authors, _.authorId)

The selectors are checked at compile time, so _.firstAuthorID doesn't compile, and each field name is turned into its snake-case column name, first_author_id and author_id.

SqlQuery.from(books) starts a query on the book table, and join(bookAuthor) adds an inner join to author. toFrag renders the query to a Frag, which runs like any sql"..." fragment. A join selects every column of both tables, books first, so we decode each row as a (Book, Author) tuple:

val booksWithAuthors = SqlQuery.from(books).join(bookAuthor)
transactor.connect {
  booksWithAuthors.toFrag(SqlDialect.SQLite).query[(Book, Author)].foreach { case (book, author) =>
    println(s"${book.title} (${book.year}) by ${author.name}")
  }
}
// Zionomicon (2024) by John A. De Goes
// Programming in Scala (2008) by Martin Odersky
// Functional Programming in Scala (2014) by Paul Chiusano
// Programming in Scala, Fifth Edition (2021) by Martin Odersky

The tuple follows the join order: the table passed to SqlQuery.from comes first, then one element per join. A tuple whose types don't match that order reads the wrong columns, so keep the two in step.

List Each Author's Books​

The same link, read from the other side, is one-to-many: one author, many books. Rel.oneToMany takes the "one" table first:

val authorBooks = Rel.oneToMany(authors, _.authorId, books, _.firstAuthorId)

Rel.manyToOne and Rel.oneToMany build the same Rel: the ON condition is "the first table's column equals the second table's column", and each selector must name a field of the table next to it. The constructor's name only documents which way the link reads. In fact SqlQuery.from(authors).join(bookAuthor) produces the same rows as the query below.

A join still returns one row per matching pair, so an author with two books comes back twice. We start from authors and group the rows by author in Scala:

val authorsAndBooks = SqlQuery.from(authors).join(authorBooks)
transactor.connect {
  val rows = authorsAndBooks.toFrag(SqlDialect.SQLite).query[(Author, Book)]
  rows.groupMap(_._1)(_._2).toList.sortBy(_._1.name).foreach { case (author, written) =>
    println(s"${author.name}: ${written.sortBy(_.year).map(_.title).mkString(", ")}")
  }
}
// John A. De Goes: Zionomicon
// Martin Odersky: Programming in Scala, Programming in Scala, Fifth Edition
// Paul Chiusano: Functional Programming in Scala

Li Haoyi is missing: an inner join only keeps authors that have at least one matching book.

Keep Authors Who Have No Books​

To keep every author, use a left join. leftJoin and joinLeft are the same method under two names, and so are join and innerJoin. join(rel, JoinKind.Left) picks the kind from a value:

println(SqlQuery.from(authors).leftJoin(authorBooks) == SqlQuery.from(authors).joinLeft(authorBooks))
// true
println(SqlQuery.from(authors).leftJoin(authorBooks) == SqlQuery.from(authors).join(authorBooks, JoinKind.Left))
// true

For an author with no books, the database returns SQL NULL in every book column. Decoding those NULLs as a plain Book doesn't fail: SQL NULL decodes to null for a String and to 0 for an Int, so Li Haoyi gets a book that doesn't exist:

val everyAuthor = SqlQuery.from(authors).leftJoin(authorBooks)
transactor.connect {
  everyAuthor.toFrag(SqlDialect.SQLite).query[(Author, Book)].foreach(println)
}
// (Author(1,Martin Odersky,Some(1958)),Book(2,Programming in Scala,2008,1))
// (Author(1,Martin Odersky,Some(1958)),Book(4,Programming in Scala, Fifth Edition,2021,1))
// (Author(2,John A. De Goes,None),Book(1,Zionomicon,2024,2))
// (Author(3,Paul Chiusano,None),Book(3,Functional Programming in Scala,2014,3))
// (Author(4,Li Haoyi,None),Book(0,null,0,0))

Option[Book] isn't an option either, because Option is only supported for single-column types. Instead, give the optional side a row type whose fields are all Options, and turn it into an Option[Book] in Scala:

case class BookColumns(bookId: Option[Int], title: Option[String], year: Option[Int], firstAuthorId: Option[Int]) {
  def toBook: Option[Book] =
    for {
      id     <- bookId
      t      <- title
      y      <- year
      author <- firstAuthorId
    } yield Book(id, t, y, author)
}
object BookColumns {
  given Schema[BookColumns] = Schema.derived
}

BookColumns has the same fields in the same order as Book, so it reads the same columns. Now the missing book is None:

transactor.connect {
  val rows = everyAuthor.toFrag(SqlDialect.SQLite).query[(Author, BookColumns)]
  rows.groupMap(_._1)(_._2.toBook).toList.sortBy(_._1.name).foreach { case (author, written) =>
    val titles = written.flatten.map(_.title)
    println(s"${author.name}: ${if (titles.isEmpty) "no books yet" else titles.mkString(", ")}")
  }
}
// John A. De Goes: Zionomicon
// Li Haoyi: no books yet
// Martin Odersky: Programming in Scala, Programming in Scala, Fifth Edition
// Paul Chiusano: Functional Programming in Scala

Filter and Sort a Join​

where adds a condition to the query, written as a sql"..." fragment; filter is the same method. Values you interpolate become bound parameters, as in any sql"..." fragment. Inside the fragment, refer to columns through the table aliases the query uses: t0 is the table passed to SqlQuery.from, and t1, t2 and so on are the joined tables in the order you added them. Quote column names with double quotes, as the generated SQL does.

orderBy sorts by a column of the t0 table, ascending unless you pass SortOrder.Desc. Here are the books published after 2010, newest first, with their first authors:

val publishedAfter = 2010

val recentBooks = SqlQuery
  .from(books)
  .join(bookAuthor)
  .where(sql"""t0."year" > $publishedAfter""")
  .orderBy("year", SortOrder.Desc)
transactor.connect {
  recentBooks.toFrag(SqlDialect.SQLite).query[(Book, Author)].foreach { case (book, author) =>
    println(s"${book.year}: ${book.title} by ${author.name}")
  }
}
// 2024: Zionomicon by John A. De Goes
// 2021: Programming in Scala, Fifth Edition by Martin Odersky
// 2014: Functional Programming in Scala by Paul Chiusano

A condition on the joined table uses its alias. Each where call adds a condition, and the conditions are combined with AND:

val author = "Martin Odersky"

val booksByOdersky = SqlQuery
  .from(books)
  .join(bookAuthor)
  .where(sql"""t1."name" = $author""")
  .where(sql"""t0."year" < ${2020}""")
transactor.connect {
  booksByOdersky.toFrag(SqlDialect.SQLite).query[(Book, Author)].foreach { case (book, _) =>
    println(book.title)
  }
}
// Programming in Scala
caution

orderBy and groupBy only reach the t0 table: they always render t0."<column>", and a name like t1.name is rejected with an IllegalArgumentException ("Invalid SQL column identifier"). To sort by a column of a joined table, start the query from that table, or sort the decoded rows in Scala. The alias names t0, t1 in a where fragment are not checked at all, so a typo there only fails when the database runs the query.

See the SQL Before It Runs​

SqlQuery#explain(dialect) returns the SQL on one line, with numbered ?1, ?2 placeholders, followed by a -- params: comment that lists each parameter's position and type, never its value. We render the filtered query for both dialects:

println(recentBooks.explain(SqlDialect.SQLite))
// SELECT t0."book_id", t0."title", t0."year", t0."first_author_id", t1."author_id", t1."name", t1."born" FROM "book" AS t0 INNER JOIN "author" AS t1 ON t0."first_author_id" = t1."author_id" WHERE t0."year" > ?1 ORDER BY t0."year" DESC
// -- params: 1:Int
println(recentBooks.explain(SqlDialect.PostgreSQL))
// SELECT t0."book_id", t0."title", t0."year", t0."first_author_id", t1."author_id", t1."name", t1."born" FROM "book" AS t0 INNER JOIN "author" AS t1 ON t0."first_author_id" = t1."author_id" WHERE t0."year" > ?1 ORDER BY t0."year" DESC
// -- params: 1:Int

The two are identical: both dialects use ? placeholders and the same quoting, so a SqlQuery renders the same SQL for SQLite and PostgreSQL today. What differs between the dialects is the column types in CREATE TABLE statements, not the queries.

explain is meant for logs and code review. SqlQuery#sql(dialect) returns the exact string that toFrag sends to the JDBC driver, with plain ? placeholders:

println(recentBooks.sql(SqlDialect.SQLite))
// SELECT t0."book_id", t0."title", t0."year", t0."first_author_id", t1."author_id", t1."name", t1."born" FROM "book" AS t0 INNER JOIN "author" AS t1 ON t0."first_author_id" = t1."author_id" WHERE t0."year" > ? ORDER BY t0."year" DESC
println(everyAuthor.sql(SqlDialect.SQLite))
// SELECT t0."author_id", t0."name", t0."born", t1."book_id", t1."title", t1."year", t1."first_author_id" FROM "author" AS t0 LEFT JOIN "book" AS t1 ON t0."author_id" = t1."first_author_id"

The output shows the rules every join follows: the columns of each table in schema order, every identifier double-quoted, the FROM table aliased as t0, each JOIN kind spelled out, and each ON condition built from the Rel.

Dump the SQL to Files at Compile Time​

explain needs the program to run. To review every join a code base contains, for example in a pull request or by a DBA who doesn't read Scala, Dump.dumpQuery writes a query's SQL to a .sql file while the code compiles. The macro only writes when the JVM system property zib.sql.dumpDir is set in the JVM that runs the compiler; otherwise each call compiles to nothing.

Dumping happens at compile time, so the code in this section lives in the companion example JoinsDumpExample.scala rather than on this page. Two rules decide what the macro can see:

  • The query must be written out where Dump.dumpQuery can read it: an inline def, or the SqlQuery.from(...) chain itself. A query stored in a plain val or built at run time can't be dumped; the macro warns and writes nothing.
  • Likewise, the Rel must be built inside the chain, not referenced from a val.
object LibraryQueries {
  inline def booksWithFirstAuthors: SqlQuery[Book] =
    SqlQuery.from(Library.books).join(Rel.manyToOne(Library.books, _.firstAuthorId, Library.authors, _.authorId))
}

object JoinsDumpExample {
  // The val's name becomes the file name
  val booksWithFirstAuthors = Dump.dumpQuery(LibraryQueries.booksWithFirstAuthors)
}

Set the property when you start sbt. A running sbt server (sbt --client, or the one an IDE starts) keeps the property it was started with, so shut it down first. clean makes sure every file is compiled, and so every dump is written:

sbt -Dzib.sql.dumpDir=target/sql-dumps clean compile

SBT_OPTS="-Dzib.sql.dumpDir=target/sql-dumps" works too. Passing the property through scalacOptions or javaOptions does nothing, because those reach the compiler as flags or the forked program at run time, not as properties of the compiler's JVM.

Each Dump.dumpQuery call writes one file per dialect, named <name>_query-<dialect>.sql, where <name> is the val the call is assigned to. For the query above, target/sql-dumps/booksWithFirstAuthors_query-sqlite.sql contains:

SELECT t0."book_id", t0."title", t0."year", t0."first_author_id", t1."author_id", t1."name", t1."born" FROM "book" AS t0 INNER JOIN "author" AS t1 ON t0."first_author_id" = t1."author_id"

A file is only rewritten when its content changes, so the files can be committed and a pull request that changes a join shows the SQL diff next to the Scala diff. Dump.dumpTable(table) does the same for a table's CREATE TABLE statement, written to <table>-<dialect>.sql.

warning

Check the dump files before you rely on them:

  • A where(sql"...") condition loses its parameter in the dump: t0."year" > $publishedAfter is written as WHERE t0."year" > with no ?. Use explain for filtered queries.
  • A Dump.dumpQuery call that isn't assigned to a val is named after the enclosing object, as _local_<Object>__query-<dialect>.sql, and a second such call in the same object overwrites the first one's file.
  • Every sql"..." fragment also writes its own file, named after its enclosing method or object, while the property is set.

Putting It Together​

This program builds the library, runs the many-to-one join, the one-to-many and left joins, the filtered query, and prints the generated SQL:

sql-examples/src/main/scala/writejoinsandinspectgeneratedsql/CompleteExample.scala
package writejoinsandinspectgeneratedsql

import zio.blocks.sql.query.*
import zio.blocks.sql.*

/**
 * How to Write Joins and Inspect the Generated SQL: Complete Example
 *
 * Builds the library, runs the many-to-one join, the one-to-many and left
 * joins, the filtered query, and prints the generated SQL for both dialects.
 *
 * Run with: cd sql-examples && sbt "runMain
 * writejoinsandinspectgeneratedsql.CompleteExample"
 */
object CompleteExample {
  def main(args: Array[String]): Unit = {
    val transactor = Library.open()
    import Library.*

    // Many-to-one: each book with its first author
    val booksWithAuthors = SqlQuery.from(books).join(bookAuthor)

    transactor.connect {
      booksWithAuthors.toFrag(SqlDialect.SQLite).query[(Book, Author)].foreach { case (book, author) =>
        println(s"${book.title} (${book.year}) by ${author.name}")
      }
    }

    // One-to-many: each author's books, grouped in Scala
    val authorsAndBooks = SqlQuery.from(authors).join(authorBooks)

    transactor.connect {
      val rows = authorsAndBooks.toFrag(SqlDialect.SQLite).query[(Author, Book)]
      rows.groupMap(_._1)(_._2).toList.sortBy(_._1.name).foreach { case (author, written) =>
        println(s"${author.name}: ${written.sortBy(_.year).map(_.title).mkString(", ")}")
      }
    }

    // Left join: keep authors without books, decoding the optional side as BookColumns
    val everyAuthor = SqlQuery.from(authors).leftJoin(authorBooks)

    transactor.connect {
      val rows = everyAuthor.toFrag(SqlDialect.SQLite).query[(Author, BookColumns)]
      rows.groupMap(_._1)(_._2.toBook).toList.sortBy(_._1.name).foreach { case (author, written) =>
        val titles = written.flatten.map(_.title)
        println(s"${author.name}: ${if (titles.isEmpty) "no books yet" else titles.mkString(", ")}")
      }
    }

    // Filter and sort
    val publishedAfter = 2010

    val recentBooks = SqlQuery
      .from(books)
      .join(bookAuthor)
      .where(sql"""t0."year" > $publishedAfter""")
      .orderBy("year", SortOrder.Desc)

    transactor.connect {
      recentBooks.toFrag(SqlDialect.SQLite).query[(Book, Author)].foreach { case (book, author) =>
        println(s"${book.year}: ${book.title} by ${author.name}")
      }
    }

    // The generated SQL
    println(recentBooks.explain(SqlDialect.SQLite))
    println(recentBooks.explain(SqlDialect.PostgreSQL))
    println(everyAuthor.sql(SqlDialect.SQLite))
  }
}

Running the Examples​

All the code in this guide has runnable companions in the sql-examples project. To get them, clone the repository and change into the examples project:

git clone https://github.com/zio/zio-blocks.git
cd zio-blocks/sql-examples

Each example creates its own temporary SQLite file, so you can run them in any order. To compile all the examples without running them:

sbt compile

The Library Tables​

The domain, the tables, the Rel and the code that creates and fills the database are shared by every example:

sql-examples/src/main/scala/writejoinsandinspectgeneratedsql/Library.scala
sql-examples/src/main/scala/writejoinsandinspectgeneratedsql/Library.scala
package writejoinsandinspectgeneratedsql

import zio.blocks.schema.Schema
import zio.blocks.sql.query.*
import zio.blocks.sql.*

case class Author(authorId: Int, name: String, born: Option[Int])
object Author {
  given Schema[Author] = Schema.derived
}

case class Book(bookId: Int, title: String, year: Int, firstAuthorId: Int)
object Book {
  given Schema[Book] = Schema.derived
}

case class BookColumns(bookId: Option[Int], title: Option[String], year: Option[Int], firstAuthorId: Option[Int]) {
  def toBook: Option[Book] =
    for {
      id     <- bookId
      t      <- title
      y      <- year
      author <- firstAuthorId
    } yield Book(id, t, y, author)
}
object BookColumns {
  given Schema[BookColumns] = Schema.derived
}

/**
 * How to Write Joins and Inspect the Generated SQL: the library tables
 *
 * Defines the `author` and `book` tables, the `Rel`s that link them, and a
 * `Library.open()` that creates and fills a temporary SQLite database. Shared
 * by every example in this package; it has no `main` of its own.
 */
object Library {
  val authors = Table.derived[Author]
  val books   = Table.derived[Book]

  val bookAuthor  = Rel.manyToOne(books, _.firstAuthorId, authors, _.authorId)
  val authorBooks = Rel.oneToMany(authors, _.authorId, books, _.firstAuthorId)

  /**
   * Creates a temporary SQLite file with both tables, inserts four authors and
   * four books, and returns its transactor.
   */
  def open(): JdbcTransactor = {
    val dbFile = java.nio.file.Files.createTempFile("library", ".db")
    dbFile.toFile.deleteOnExit()
    val transactor = JdbcTransactor.fromUrl(s"jdbc:sqlite:$dbFile", SqlDialect.SQLite)

    transactor.connect {
      sql"""CREATE TABLE author (
        author_id INTEGER PRIMARY KEY,
        name      TEXT NOT NULL,
        born      INTEGER
      )""".update
      sql"""CREATE TABLE book (
        book_id         INTEGER PRIMARY KEY,
        title           TEXT NOT NULL,
        year            INTEGER NOT NULL,
        first_author_id INTEGER NOT NULL REFERENCES author (author_id)
      )""".update
    }

    val authorRepo = Repo.derived[Author, Int]
    val bookRepo   = Repo.derived[Book, Int]

    transactor.connect {
      authorRepo.insert(Author(1, "Martin Odersky", Some(1958)))
      authorRepo.insert(Author(2, "John A. De Goes", None))
      authorRepo.insert(Author(3, "Paul Chiusano", None))
      authorRepo.insert(Author(4, "Li Haoyi", None))

      bookRepo.insert(Book(1, "Zionomicon", 2024, 2))
      bookRepo.insert(Book(2, "Programming in Scala", 2008, 1))
      bookRepo.insert(Book(3, "Functional Programming in Scala", 2014, 3))
      bookRepo.insert(Book(4, "Programming in Scala, Fifth Edition", 2021, 1))
    }

    transactor
  }
}

Join Each Book to Its First Author​

sql-examples/src/main/scala/writejoinsandinspectgeneratedsql/Step1ManyToOneExample.scala
sql-examples/src/main/scala/writejoinsandinspectgeneratedsql/Step1ManyToOneExample.scala
package writejoinsandinspectgeneratedsql

import zio.blocks.sql.query.*
import zio.blocks.sql.*

/**
 * How to Write Joins and Inspect the Generated SQL: Join Each Book to Its First
 * Author
 *
 * Joins each book to its first author with Rel.manyToOne and decodes rows as
 * (Book, Author) tuples.
 *
 * Run with: cd sql-examples && sbt "runMain
 * writejoinsandinspectgeneratedsql.Step1ManyToOneExample"
 */
object Step1ManyToOneExample {
  def main(args: Array[String]): Unit = {
    val transactor = Library.open()
    import Library.*

    val booksWithAuthors = SqlQuery.from(books).join(bookAuthor)

    transactor.connect {
      booksWithAuthors.toFrag(SqlDialect.SQLite).query[(Book, Author)].foreach { case (book, author) =>
        println(s"${book.title} (${book.year}) by ${author.name}")
      }
    }
  }
}

Run it with:

sbt "runMain writejoinsandinspectgeneratedsql.Step1ManyToOneExample"

List Each Author's Books​

sql-examples/src/main/scala/writejoinsandinspectgeneratedsql/Step2OneToManyExample.scala
sql-examples/src/main/scala/writejoinsandinspectgeneratedsql/Step2OneToManyExample.scala
package writejoinsandinspectgeneratedsql

import zio.blocks.sql.query.*
import zio.blocks.sql.*

/**
 * How to Write Joins and Inspect the Generated SQL: List Each Author's Books
 *
 * Starts from authors, joins with Rel.oneToMany and groups the rows by author
 * in Scala.
 *
 * Run with: cd sql-examples && sbt "runMain
 * writejoinsandinspectgeneratedsql.Step2OneToManyExample"
 */
object Step2OneToManyExample {
  def main(args: Array[String]): Unit = {
    val transactor = Library.open()
    import Library.*

    val authorsAndBooks = SqlQuery.from(authors).join(authorBooks)

    transactor.connect {
      val rows = authorsAndBooks.toFrag(SqlDialect.SQLite).query[(Author, Book)]
      rows.groupMap(_._1)(_._2).toList.sortBy(_._1.name).foreach { case (author, written) =>
        println(s"${author.name}: ${written.sortBy(_.year).map(_.title).mkString(", ")}")
      }
    }
  }
}

Run it with:

sbt "runMain writejoinsandinspectgeneratedsql.Step2OneToManyExample"

Keep Authors Who Have No Books​

sql-examples/src/main/scala/writejoinsandinspectgeneratedsql/Step3LeftJoinExample.scala
sql-examples/src/main/scala/writejoinsandinspectgeneratedsql/Step3LeftJoinExample.scala
package writejoinsandinspectgeneratedsql

import zio.blocks.sql.query.*
import zio.blocks.sql.*

/**
 * How to Write Joins and Inspect the Generated SQL: Keep Authors Who Have No
 * Books
 *
 * Left join: shows the bogus Book decoded from NULL columns, and the
 * BookColumns fix that yields Option[Book].
 *
 * Run with: cd sql-examples && sbt "runMain
 * writejoinsandinspectgeneratedsql.Step3LeftJoinExample"
 */
object Step3LeftJoinExample {
  def main(args: Array[String]): Unit = {
    val transactor = Library.open()
    import Library.*

    println(SqlQuery.from(authors).leftJoin(authorBooks) == SqlQuery.from(authors).joinLeft(authorBooks))
    println(SqlQuery.from(authors).leftJoin(authorBooks) == SqlQuery.from(authors).join(authorBooks, JoinKind.Left))

    val everyAuthor = SqlQuery.from(authors).leftJoin(authorBooks)

    transactor.connect {
      everyAuthor.toFrag(SqlDialect.SQLite).query[(Author, Book)].foreach(println)
    }

    transactor.connect {
      val rows = everyAuthor.toFrag(SqlDialect.SQLite).query[(Author, BookColumns)]
      rows.groupMap(_._1)(_._2.toBook).toList.sortBy(_._1.name).foreach { case (author, written) =>
        val titles = written.flatten.map(_.title)
        println(s"${author.name}: ${if (titles.isEmpty) "no books yet" else titles.mkString(", ")}")
      }
    }
  }
}

Run it with:

sbt "runMain writejoinsandinspectgeneratedsql.Step3LeftJoinExample"

Filter and Sort a Join​

sql-examples/src/main/scala/writejoinsandinspectgeneratedsql/Step4FilterSortExample.scala
sql-examples/src/main/scala/writejoinsandinspectgeneratedsql/Step4FilterSortExample.scala
package writejoinsandinspectgeneratedsql

import zio.blocks.sql.query.*
import zio.blocks.sql.*

/**
 * How to Write Joins and Inspect the Generated SQL: Filter and Sort a Join
 *
 * Adds where conditions using table aliases and sorts with orderBy.
 *
 * Run with: cd sql-examples && sbt "runMain
 * writejoinsandinspectgeneratedsql.Step4FilterSortExample"
 */
object Step4FilterSortExample {
  def main(args: Array[String]): Unit = {
    val transactor = Library.open()
    import Library.*

    val publishedAfter = 2010

    val recentBooks = SqlQuery
      .from(books)
      .join(bookAuthor)
      .where(sql"""t0."year" > $publishedAfter""")
      .orderBy("year", SortOrder.Desc)

    transactor.connect {
      recentBooks.toFrag(SqlDialect.SQLite).query[(Book, Author)].foreach { case (book, author) =>
        println(s"${book.year}: ${book.title} by ${author.name}")
      }
    }

    val author = "Martin Odersky"

    val booksByOdersky = SqlQuery
      .from(books)
      .join(bookAuthor)
      .where(sql"""t1."name" = $author""")
      .where(sql"""t0."year" < ${2020}""")

    transactor.connect {
      booksByOdersky.toFrag(SqlDialect.SQLite).query[(Book, Author)].foreach { case (book, _) =>
        println(book.title)
      }
    }
  }
}

Run it with:

sbt "runMain writejoinsandinspectgeneratedsql.Step4FilterSortExample"

See the SQL Before It Runs​

sql-examples/src/main/scala/writejoinsandinspectgeneratedsql/Step5ExplainExample.scala
sql-examples/src/main/scala/writejoinsandinspectgeneratedsql/Step5ExplainExample.scala
package writejoinsandinspectgeneratedsql

import zio.blocks.sql.query.*
import zio.blocks.sql.*

/**
 * How to Write Joins and Inspect the Generated SQL: See the SQL Before It Runs
 *
 * Prints explain for SQLite and PostgreSQL, and the exact SQL string toFrag
 * sends to the driver.
 *
 * Run with: cd sql-examples && sbt "runMain
 * writejoinsandinspectgeneratedsql.Step5ExplainExample"
 */
object Step5ExplainExample {
  def main(args: Array[String]): Unit = {
    val transactor = Library.open()
    import Library.*

    val publishedAfter = 2010

    val recentBooks = SqlQuery
      .from(books)
      .join(bookAuthor)
      .where(sql"""t0."year" > $publishedAfter""")
      .orderBy("year", SortOrder.Desc)

    println(recentBooks.explain(SqlDialect.SQLite))
    println(recentBooks.explain(SqlDialect.PostgreSQL))

    val everyAuthor = SqlQuery.from(authors).leftJoin(authorBooks)

    println(everyAuthor.sql(SqlDialect.SQLite))
  }
}

Run it with:

sbt "runMain writejoinsandinspectgeneratedsql.Step5ExplainExample"

Dump the SQL to Files at Compile Time​

sql-examples/src/main/scala/writejoinsandinspectgeneratedsql/JoinsDumpExample.scala
sql-examples/src/main/scala/writejoinsandinspectgeneratedsql/JoinsDumpExample.scala
package writejoinsandinspectgeneratedsql

import zio.blocks.sql.query.*
import zio.blocks.sql.*

object LibraryQueries {
  inline def booksWithFirstAuthors: SqlQuery[Book] =
    SqlQuery.from(Library.books).join(Rel.manyToOne(Library.books, _.firstAuthorId, Library.authors, _.authorId))
}

/**
 * How to Write Joins and Inspect the Generated SQL: Dump the SQL to Files at
 * Compile Time
 *
 * `Dump.dumpQuery` writes the query's SQL to a `.sql` file while this file
 * compiles, when the compiler's JVM has the `zib.sql.dumpDir` system property
 * set. `main` prints the files it produced.
 *
 * Run with: cd sql-examples && sbt -Dzib.sql.dumpDir=target/sql-dumps clean
 * compile "runMain writejoinsandinspectgeneratedsql.JoinsDumpExample"
 */
object JoinsDumpExample {
  // The val's name becomes the file name
  val booksWithFirstAuthors = Dump.dumpQuery(LibraryQueries.booksWithFirstAuthors)

  def main(args: Array[String]): Unit = {
    val dir   = java.nio.file.Paths.get("target/sql-dumps")
    val files =
      if (java.nio.file.Files.isDirectory(dir)) {
        val stream = java.nio.file.Files.list(dir)
        try
          stream.toArray.toList
            .map(_.asInstanceOf[java.nio.file.Path])
            .filter(p =>
              p.getFileName.toString.startsWith("booksWithFirstAuthors") && p.getFileName.toString.endsWith(".sql")
            )
            .sortBy(_.getFileName.toString)
        finally stream.close()
      } else Nil

    if (files.isEmpty)
      println("No dump files found. Recompile with: sbt -Dzib.sql.dumpDir=target/sql-dumps clean compile")
    else
      files.foreach { file =>
        println(s"== ${file.getFileName}")
        println(new String(java.nio.file.Files.readAllBytes(file), "UTF-8").trim)
      }
  }
}

Compile with the dump directory set, then run the example to print the files it produced:

sbt -Dzib.sql.dumpDir=target/sql-dumps clean compile "runMain writejoinsandinspectgeneratedsql.JoinsDumpExample"

The Complete Example​

sbt "runMain writejoinsandinspectgeneratedsql.CompleteExample"

Going Further​

  • Frag documents Frag#query, Frag#queryOne and the other ways to run the fragment SqlQuery#toFrag returns.
  • How to Stream Large Query Results reads a large join as a stream of row chunks: call Frag#queryStream or Frag#queryChunked on the fragment SqlQuery#toFrag returns, instead of Frag#query.
  • DbCodec explains how rows decode into case classes and tuples, and how SQL NULL is handled.
  • sql(table) Checked Interpolation checks table and column names in hand-written SQL at compile time, for joins that SqlQuery can't express.
  • How to Test Database Code Against SQLite gives every test its own database, which suits tests for join queries.
  • SqlLogger records the SQL of every query a transactor runs, including joins, with its duration and row count.