albertprz / logograph

Minimalistic typesafe SQL DSL for Scala

Logograph

Summary

This library provides a typesafe Scala DSL for generating SQL queries and statements
These statements can then be executed against a Database using the Logograph Context,
which uses JDBC for connecting to the Database, or any other Database connection middleware
provided in the client application

By using Scala macros in order to generate the SQL statetements at compile-time,
SQL generation provides a useful abstraction while having no performance
overhead at runtime compared to running a raw SQL statement.

These SQL generation macros also perform validation on the SQL statements,
so the library user can detect and fix several kinds of errors without the need to execute
the statement against the Database. This also means that most SQL syntax errors are guaranteed
to never happen in production code.

The generated SQL statements can embed runtime values and are fully parameterised,
so there is no risk of SQL Injection attacks.

The DSL works in a similar fashion to some other SQL compile-time DSLs available for Scala,
for instance Quill, but it aims to provide a streamlined API, with a focus on simplicity
from the user standpoint, leaving some advanced functionality aside, in order to adapt better
to the most common business use cases, following a convention over configuration approac.

There is some in-project customization around both global naming conventions as well as explicit table and column names via the use of an optional logograph.conf file in the project. You can see an example here

For some examples of statements serialisation and execution (such as complex nested queries or query unions & intersections), you can check the unit or integration test folders.

Usage

In order to use this library, you would need to add it to the dependencies in build.sbt:
All the releases are cross-compiled and therefore available for both scala 3 & scala 2.13.
All the tests also run for both Scala versions, since the macro implementations differ between the language versions.

libraryDependencies += "io.github.albertprz" % "logograph" %% "0.1.0")

Logograph DSL aim is to reflect as closely as possible the underlying SQL representation,
so the API is very SQL like:

import com.albertprz.logograph._

val qry = from[(Person, Address, Telephone)].select {
  case (p, a, t) ⇒ Query(
    Select          (Result (p.name, p.age, a.street, t.number))
    Where           (a.street like "%Baker St%",
                      p.name in names,
                      coalesce (p.isEmployer, false)),
    OrderBy         (desc (p.age)),
    LeftJoin (a)    (a.id === p.addressId),
    LeftJoin (t)    (t.id === p.telephoneId))
  }

The DSL is very concise and uses the same operator and functions that the SQL equivalent.
The SQL output for this query would be the following:

SELECT      p.[name], p.[age], a.[street], t.[number]
FROM        [Person] AS p
LEFT JOIN   [Address] AS a ON a.[id] = p.[addressId]
LEFT JOIN   [Telephone] AS t ON t.[id] = p.[telephoneId]
WHERE       (a.[street] like '%Baker St%') AND
            (p.[name] in (?, ?, ?)) AND
            (coalesce (p.[isEmployer], 0))
ORDER BY    p.[age] desc

And the parameters that were used in this query, for the runtime values are:

{@Application.names -> [John, Mark, Thomas]}

The query is generated in a fully typesafe manner. The query input tables must be specified by
case classes that extend the DbTable trait and the query result type must be a case class
that extends either the DbTable or the DbResult traits.
The qry value will be an object of type SelectStatement[Result], in this case.

// Database Table Models
case class Person (name: String, age: Int, isEmployer: Boolean, addressId: Int, telephoneId: Int)
                    extends DbTable
case class Address (id: Int, street: String) extends DbTable
case class Telephone (id: Int, number: String) extends DbTable


// Query Result Model
case class Result (name: String, age: Int, street: String, telephoneNumber: String) extends DbResult

Additionally the SQL Statement API methods ending in Debug, can be used in order to generate
a compile time error that will expose the SQL statement at compile time as well as the internal AST,
that was used to generate the SQL:

QueryClause (
  SelectClause ([Field (p, name), Field (p, age), Field (a, street), Field (t, number)]),
  FromClause ({p -> Person}), [
  LeftJoinClause (Address, a, [
    Operation (===, [Field (a, id), Field (p, addressId)])]),
  LeftJoinClause (Telephone, t, [
    Operation (===, [Field (t, id), Field (p, telephoneId)])])],
  WhereClause ([
    Operation (like, [Field (a, street),
      LiteralVal ("%Baker St%")]),
    Operation (in, [Field (p, name), Identity (?)]),
    Operation (coalesce, [Field (p, isEmployer), LiteralVal (0)])]),
  OrderByClause ([
    Operation (desc, [Field (p, age)])]))

The API also exposes Insert, Update & Delete statements, which have a common trait (SQLStatefulStament):

val stmts = Seq(insert(john),

                insert(johnAddress),

                update[Person] (p => (Map(p.name -> "Mark",
                                          p.age  -> 50),
                                      Where(p.age >= 10))),

                delete[Address] (a => Where(a.street <> "Baker Street"))

These statements will generate the following SQL output:

INSERT INTO [Person] ([name], [age], [isEmployer], [addressId], [telephoneId])
VALUES      (?, ?, ?, ?, ?)

INSERT INTO [Address] ([id], [street])
VALUES      (?, ?)

UPDATE      [Person]
SET         [name] = 'Mark',
            [age] = 50
WHERE       [age] >= 10

DELETE FROM [Address]
WHERE       [street] <> 'Baker Street'

The raw SQL and runtime params from any statement can be obtained at runtime
by accessing the sql and params fields:

(qry.sql, qry.params)

At last, the statements can be run against a Database by using implicitly / explicitly
a in-scope LogographContext instance, using the appropiate JDBC connection object

val conn: Connection
val qry: SelectStatement[Person]
val stmt: UpdateStatement[Address]

implicit val context = new LogographContext[IO](conn)
val resultsIO: IO[Seq[Person]] = qry.run()
val operationIO: IO[Unit] = stmt.run()

or

val conn: Connection
val qry: SelectStatement[Person]
val stmts: List[SQLStatefulStatements]

val context = new LogographContext[IO](conn)
val resultsIO: IO[Seq[Person]] = context.run(qry)
val operationsIO: IO[Unit] = context.run(stmts:_*)