Tyql

A Scala3 SQL query generator

• based on named tuples and not macros nor higher-kinded types,
• checks query correctness at compile-time against selected backend,
• generates SQL at runtime,
• guides the user with nice error messages,
• is usable (feature coverage, speed).

TOC
Tyql - A Scala3 SQL query generator
How do i use it?
How do I configure it?
How fast is it in practice?
What about caching queries with changing inputs?
What about transactions and other driver-specific functionality?
Limits of compile-time correctness checking

How do i use it?

First, import a dialect (postgres, mysql, mariadb, duckdb, sqlite, h2) like this

import tyql.Dialect.postgres.given

Then define your tables as case classes or named tuples

case class Person(id: Long, name: String)
val persons = Table[Person]()
val orders = Table[(orderid: Long, person: Long, notes: Option[String])]("order")

Compose queries like this

val q = for (p <- persons ; if p.id > 10L ; o <- orders.joinOn(o => p.id == o.person))
  yield (name = p.name, orderNumber = o.orderid, specialNote = notes.getOrElse("NONE"))

You will sometimes have to wrap literals in lit, especially booleans: lit(true).

You can then examine the SQL

println(q.toSQLString())

or run it against the database and receive Scala-native data structures back

val conn = java.sql.DriverManager.getConnection("jdbc:postgresql://localhost:5433/testdb", "testuser", "testpass")
val db = tyql.DB(conn)
db.run(q)
/* List(
*   (name = "Adam", orderNumber = 12L, specialNote = "NONE"),
*   (name = "Eva", orderNumber = 3L, specialNote = "2nd floor")
* )
*/

How do I configure it?

given tyql.Config = new tyql.Config(tyql.CaseConvention.Underscores, tyql.ParameterStyle.EscapedInline) {}

For case convention (how will the Scala identifiers be translated into SQL) you can pick

enum CaseConvention:
  case Exact
  case Underscores // three_letter_word
  case PascalCase // ThreeLetterWord
  case CamelCase // threeLetterWord
  case CapitalUnderscores // THREE_LETTER_WORD
  case Joined // threeletterword
  case JoinedCapital // THREELETTERWORD

For parameter style you can pick EscapedInline (literals will be pasted inside the SQL) or DriverParametrized (the SQL will be ?-parametrized and the JDBC will be provided with the values).

How fast is it in practice?

We benchmarked against Quill (a prominent macro-based query generator) on a local MySQL instance. Quill computes and renders the query entirely at compile-time, we therefore compare to Tyql with caching enabled. We therefore are comparing the performance of the driver and fetching from cache. In our tests

• time it takes to fetch 100k rows is almost identical (87-89µs),
• time it takes for a round trip of a small query is almost identical (95-97µs).

Tyql generates queries usually in between 7µs and 25µs (depending on query complexity).

What about caching queries with changing inputs?

You can use Var(thunk: => T). If you're using ?-parametrization, the value will be fetched only inside DB.run, that is, when the parameters need to be passed to JDBC.

val q = persons.filter(p => p.age >= Var(getAge()))
db.run(q)

The query will be computed and rendered only once, no matter how many times it is run with different parameters.

What about transactions and other driver-specific functionality?

We do not replace JDBC, only wrap its Connection with our DB.

try {
  conn.setAutoCommit(false)
  val got = db.run(q1)
  // ...
  conn.commit()
} catch { case e: Exception =>
  conn.rollback()
} finally {
  conn.setAutoCommit(true)
  conn.close()
}

Limits of compile-time correctness checking

So far known things that we do not check at compile-time:

• correctness of casts (like person.name.asDouble) (design decision),
• that selected expressions in GROUP BY queries are either aggregations or groupings (bug, work in progress),
• out-of-bound index access, e.g. lit(List(1,2,3))(10) (which compiles to ARRAY[1,2,3][11]) (unsure if this should be supported for literals),
• existence of tables/relations, as well as permissions (design decision).