SQL DSL

The Typo SQL DSL, an optional yet strongly recommended SQL DSL, can be an invaluable addition to your Scala toolbox, offering you a lot of convenience:

Simplicity: It's simple. Both for the developer, and for the compiler!

Neither aggregation nor projection is allowed — you always work on whole rows. On the other hand, you get predicates, joins, ordering, pagination.

It's designed to cover the "I just need to fetch/update this data" scenario, without requiring you to break flow by creating an sql file. Whenever you need more flexibility, you should reach for SQL files

Type Safety: With the Typo DSL, you benefit from Scala's robust type system, ensuring that your queries are syntactically correct at compile time. Say goodbye to runtime errors caused by mismatched column names or data types.

Readability and Maintainability: The Typo DSL promotes clean, expressive code, making it easier to understand and maintain your database queries. No more wrestling with long, convoluted SQL strings scattered throughout your codebase.

Tooling Integration: Leverage the power of IDEs and code editors for autocomplete, refactoring, and error checking, as the Typo DSL seamlessly integrates with IDEs. This significantly boosts developer productivity.

Select DSL

Some features

• Type-safe
• Generates not-too-terrible SQL
• Arbitrarily deep joins
• Order by
• Expresses a rich set of predicates (sql operators, functions, string functions, comparisons, etc)
• Some functions defined for string and array types
• Set of operators and functions are extendable by user
• Works both when backed by in-memory stubs and by PostgreSQL

Update DSL

Can express batch updates, where you set arbitrary number of columns with an arbitrary number of (implicitly ANDed) predicates.

Column values can be computed from the original value in the column or from the entire row, as seen below

Delete DSL

There is also a delete DSL, similar to select and update. It has no video yet, unfortunately.

Further reading

• Getting started for some information about how to set up the DSL.
• Limitations for a caveat on how PostgreSQL infers nullability.
• Customize sql files for how to override parameter/column names, types and nullability
• Dynamic queries for how to introduce some amount of dynamism in your queries

Example usage:

productRepo.select
  .where(_.`class` === "H ")
  .where(x => x.daystomanufacture > 25 or x.daystomanufacture <= 0)
  .where(x => x.productline === "foo")
  .join(unitmeasureRepo.select.where(_.name.like("name%")))
  .on { case (p, um) => p.sizeunitmeasurecode === um.unitmeasurecode }
  .join(projectModelRepo.select)
  .leftOn { case ((product, _), productModel) => product.productmodelid === productModel.productmodelid }
  .where { case ((product, _), productModel) => product.productmodelid === productModel(_.productmodelid) }
  .orderBy { case ((product, _), _) => product.productmodelid.asc }
  .orderBy { case ((_, _), productModel) => productModel(_.name).desc.withNullsFirst }
  .toList
// res1: List[Tuple2[Tuple2[ProductRow, UnitmeasureRow], Option[ProductmodelRow]]] = List(
// )