ScalaSql

In ScalaSQL, columns that can be NULL are modeled using Option[T] in your Scala case classes and Expr[Option[T]] in your queries. When you run a query, these fields are returned as Option[V] values (Some or None).

Filtering Nullable Columns

• Use .isDefined on an Expr[Option[V]] to generate a SQL IS NOT NULL check.
• Use .isEmpty on an Expr[Option[V]] to generate a SQL IS NULL check.

Equality Semantics

ScalaSQL provides two ways to compare nullable columns, which behave differently regarding SQL NULL semantics:

1. SQL Equality (=): Using the backticked = operator follows standard SQL semantics. In SQL, NULL = NULL is not true. Therefore, None = None returns false.
2. Scala Equality (=== / !==): Using === or !== translates to IS NOT DISTINCT FROM or IS DISTINCT FROM in SQL. This follows Scala == semantics, where None === None returns true.

// SQL Equality (returns false for None = None)
OptCols.select.filter(_.myInt `=` 1)

// Scala Equality (returns true for None === None)
OptCols.select.filter(_.myInt === Option.empty[Int])

// Inequality (IS DISTINCT FROM)
OptCols.select.filter(_.myInt !== Option(1))

The MySqlDialect provides operators tailored for MySQL, including row locking, string manipulation, and conflict handling:

• .forUpdate: Translates to SELECT ... FOR UPDATE.
• String manipulation: .reverse, .lpad(length, padString), and .rpad(length, padString).
• Concatenation: db.concat(...) and db.concatWs(separator, ...).
• Randomness: db.rand.
• Conflict Handling (Upsert): Use .onConflictUpdate during an insert to handle duplicate keys.
  • .onConflictUpdate(mapping): Updates specific columns.
  • .onConflictUpdate(v => v.column := v.column.expression): Performs computed updates (e.g., UPPER).

// FOR UPDATE
Buyer.select.filter(_.id === 1).forUpdate

// Conflict Handling (Upsert)
Buyer.insert
  .columns(
    _.name := "test buyer",
    _.id := 1
  )
  .onConflictUpdate(_.name := "TEST BUYER CONFLICT")

// Computed Conflict Update
Buyer.insert
  .columns(_.id := 1, _.name := "test")
  .onConflictUpdate(v => v.name := v.name.toUpperCase)

// Concatenation
db.concat("i ", "am", " cow", 1337)

ScalaSql automatically translates complex Scala semantics into SQL subqueries when a single SQL query cannot express the requested operation. This occurs in several common scenarios:

• Joins with limited results: Using .join on a .select that has .take(n) or .sortBy results in a JOIN on a subquery.
• Operations after LIMIT: Performing a .join after a .take (e.g., Table.select.take(n).join(...)) requires wrapping the limited set in a subquery in the FROM clause because SQL does not allow JOINs after LIMITs.
• Multiple Sorts/Limits: Chaining multiple .sortBy and .take calls (e.g., .take(4).sortBy(...).take(2)) results in nested subqueries to preserve the order and limit at each step.
• Group By and Joins: Using .groupBy followed by a .join results in a subquery in the FROM clause containing the grouped results.
• Subqueries as Expressions: Using a .select inside a .filter or .map (e.g., .filter(c => OtherTable.select.filter(...).size == 0)) translates to a subquery expression. For this to be valid SQL, the subquery must return exactly one row and one column, often achieved using aggregate functions like .size or .sumBy.
• Set Operations: Using .unionAll or .except on queries that include .take or .map transformations often requires subqueries to maintain the correct semantics of the set operation.
• Deep Nesting: ScalaSql supports arbitrarily deep nesting of .select calls, which translates directly into nested SQL subqueries.

// Example: Join with a limited subquery
Purchase.select
  .join(Product.select.sortBy(_.price).desc.take(1))(_.productId `=` _.id)
  .map { case (purchase, product) => purchase.total }

For databases that support it, LATERAL JOIN allows a subquery on the right-hand side of a join to reference expressions defined in the table on the left-hand side. This is useful for complex correlated subqueries.

Available Methods

• .crossJoinLateral(f): Performs a CROSS JOIN LATERAL.
• .joinLateral(f)((left, right) => condition): Performs a standard JOIN LATERAL with an ON condition.
• .leftJoinLateral(f)((left, right) => condition): Performs a LEFT JOIN LATERAL.
• .rightJoinLateral(...) and .outerJoinLateral(...) are also supported.

For-Comprehension Syntax

You can use the ...Lateral() variants within a for comprehension to make the syntax cleaner.

Note: When using leftJoinLateral, the right-hand side result will be wrapped in an Option (e.g., Some(record) or None) to handle cases where no match is found.

// Using crossJoinLateral
Buyer.select
  .crossJoinLateral(b => ShippingInfo.select.filter { s => b.id `=` s.buyerId })
  .map { case (b, s) => (b.name, s.shippingDate) }

// Using leftJoinLateral in a for-comprehension
for {
  b <- Buyer.select
  s <- ShippingInfo.select.filter(b.id `=` _.buyerId).leftJoinLateral(_ => Expr(true))
} yield (b, s)

The PostgresDialect provides several operators that translate to specific PostgreSQL SQL clauses:

• .distinctOn(column): Translates to DISTINCT ON (column). Often used with .sortBy.
• .forUpdate: Translates to SELECT ... FOR UPDATE for row locking.
• String manipulation: .ltrim(char), .rtrim(char), .reverse, .lpad(length, padString), and .rpad(length, padString).
• Concatenation: db.concat(...) and db.concatWs(separator, ...).
• Formatting: db.format(formatString, args...).
• Randomness: db.random.

// DISTINCT ON
Purchase.select.distinctOn(_.shippingInfoId).sortBy(_.shippingInfoId).desc

// FOR UPDATE
Invoice.select.filter(_.id === 1).forUpdate

// String manipulation
Expr("xxHellox").ltrim("x")
Expr("Hello").lpad(10, "xy")

// Concatenation and Formatting
db.concat("i ", "am", " cow", 1337)
db.concatWs(" ", "i", "am", "cow", 1337)
db.format("i am cow %s hear me moo %s", 1337, 31337)

// Random
db.random

To reduce boilerplate (DRY), you can nest case classes within a table's definition. ScalaSql will automatically flatten these nested classes into the single SQL table structure. The SQL level will see a flat set of columns, even though your Scala code uses a nested hierarchy.

Using standard Table

When using standard Table definitions, simply nest the case classes.

Using SimpleTable

Important: When using nested case classes with SimpleTable, the nested class must extend SimpleTable.Nested.

// For standard Table
case class Nested[T[_]](fooId: T[Int], myBoolean: T[Boolean])
case class Enclosing[T[_]](barId: T[Int], myString: T[String], foo: Nested[T])
object Enclosing extends Table[Enclosing]

// For SimpleTable
case class Nested(fooId: Int, myBoolean: Boolean) extends SimpleTable.Nested
case class Enclosing(barId: Int, myString: String, foo: Nested) extends SimpleTable[Enclosing]

// Usage remains the same: access via dot notation
val insertColumns = Enclosing.insert.columns(
  _.barId := 1,
  _.foo.fooId := 2
)