ScalaSql Reference Library

April 21, 2026 · View on GitHub

ScalaSql Reference Library

This page contains example queries for the ScalaSql, taken from the ScalaSql test suite. You can use this as a reference to see what kinds of operations ScalaSql supports and how these operations are translated into raw SQL to be sent to the database for execution.

If browsing this on Github, you can open the Outline pane on the right to quickly browse through the headers, or use Cmd-F to search for specific SQL keywords to see how to generate them from Scala (e.g. try searching for LEFT JOIN or WHEN)

Note that ScalaSql may generate different SQL in certain cases for different databases, due to differences in how each database parses SQL. These differences are typically minor, and as long as you use the right Dialect for your database ScalaSql should do the right thing for you.

A note for users of SimpleTable: The examples in this document assume usage of Table, with a higher kinded type parameter on a case class. If you are using SimpleTable, then the same code snippets should work by dropping [Sc].

DbApi

Basic usage of db.* operations such as db.run

DbApi.renderSql

You can use .renderSql on the DbApi or DbClient to see the SQL that is generated without actually running it

dbClient.renderSql(Buyer.select) ==>
  "SELECT buyer0.id AS id, buyer0.name AS name, buyer0.date_of_birth AS date_of_birth FROM buyer buyer0"

DbApi.run

Most common usage of dbClient.transaction/db.run to run a simple query within a transaction

dbClient.transaction { db =>
  db.run(Buyer.select) ==> List(
    Buyer[Sc](1, "James Bond", LocalDate.parse("2001-02-03")),
    Buyer[Sc](2, "叉烧包", LocalDate.parse("1923-11-12")),
    Buyer[Sc](3, "Li Haoyi", LocalDate.parse("1965-08-09"))
  )
}

DbApi.runSql

db.runSql can be used to run sql"..." strings, while providing a specified type that the query results will be deserialized as the specified type. db.runSql supports the all the same data types as db.run: primitives, date and time types, tuples, Foo[Sc] case classs, and any combination of these.

The sql"..." string interpolator automatically converts interpolated values into prepared statement variables, avoidin SQL injection vulnerabilities. You can also interpolate other sql"..." strings, or finally use SqlStr.raw for the rare cases where you want to interpolate a trusted java.lang.String into the sql"..." query without escaping.

dbClient.transaction { db =>
  val filterId = 2
  val output = db.runSql[String](
    sql"SELECT name FROM buyer WHERE id = $filterId"
  )

  assert(output == Seq("叉烧包"))

  val output2 = db.runSql[(String, LocalDate)](
    sql"SELECT name, date_of_birth FROM buyer WHERE id = $filterId"
  )
  assert(
    output2 ==
      Seq(("叉烧包", LocalDate.parse("1923-11-12")))
  )

  val output3 = db.runSql[(String, LocalDate, Buyer[Sc])](
    sql"SELECT name, date_of_birth, * FROM buyer WHERE id = $filterId"
  )
  assert(
    output3 ==
      Seq(
        (
          "叉烧包",
          LocalDate.parse("1923-11-12"),
          Buyer[Sc](
            id = 2,
            name = "叉烧包",
            dateOfBirth = LocalDate.parse("1923-11-12")
          )
        )
      )
  )
}

DbApi.updateSql

Similar to db.runQuery, db.runUpdate allows you to pass in a SqlStr, but runs an update rather than a query and expects to receive a single number back from the database indicating the number of rows inserted or updated

dbClient.transaction { db =>
  val newName = "Moo Moo Cow"
  val newDateOfBirth = LocalDate.parse("2000-01-01")
  val count = db
    .updateSql(
      sql"INSERT INTO buyer (name, date_of_birth) VALUES($newName, $newDateOfBirth)"
    )
  assert(count == 1)

  db.run(Buyer.select) ==> List(
    Buyer[Sc](1, "James Bond", LocalDate.parse("2001-02-03")),
    Buyer[Sc](2, "叉烧包", LocalDate.parse("1923-11-12")),
    Buyer[Sc](3, "Li Haoyi", LocalDate.parse("1965-08-09")),
    Buyer[Sc](4, "Moo Moo Cow", LocalDate.parse("2000-01-01"))
  )
}

DbApi.updateGetGeneratedKeysSql

Allows you to fetch the primary keys that were auto-generated for an INSERT defined as a SqlStr. Note: not supported by Sqlite https://github.com/xerial/sqlite-jdbc/issues/980

dbClient.transaction { db =>
  val newName = "Moo Moo Cow"
  val newDateOfBirth = LocalDate.parse("2000-01-01")
  val generatedIds = db
    .updateGetGeneratedKeysSql[Int](
      sql"INSERT INTO buyer (name, date_of_birth) VALUES ($newName, $newDateOfBirth), ($newName, $newDateOfBirth)"
    )

  assert(generatedIds == Seq(4, 5))

  db.run(Buyer.select) ==> List(
    Buyer[Sc](1, "James Bond", LocalDate.parse("2001-02-03")),
    Buyer[Sc](2, "叉烧包", LocalDate.parse("1923-11-12")),
    Buyer[Sc](3, "Li Haoyi", LocalDate.parse("1965-08-09")),
    Buyer[Sc](4, "Moo Moo Cow", LocalDate.parse("2000-01-01")),
    Buyer[Sc](5, "Moo Moo Cow", LocalDate.parse("2000-01-01"))
  )
}

DbApi.runRaw

runRawQuery is similar to runQuery but allows you to pass in the SQL strings "raw", along with ? placeholders and interpolated variables passed separately.

dbClient.transaction { db =>
  val output = db.runRaw[String]("SELECT name FROM buyer WHERE id = ?", Seq(2))
  assert(output == Seq("叉烧包"))
}

DbApi.updateRaw

runRawUpdate is similar to runRawQuery, but for update queries that return a single number

dbClient.transaction { db =>
  val count = db.updateRaw(
    "INSERT INTO buyer (name, date_of_birth) VALUES(?, ?)",
    Seq("Moo Moo Cow", LocalDate.parse("2000-01-01"))
  )
  assert(count == 1)

  db.run(Buyer.select) ==> List(
    Buyer[Sc](1, "James Bond", LocalDate.parse("2001-02-03")),
    Buyer[Sc](2, "叉烧包", LocalDate.parse("1923-11-12")),
    Buyer[Sc](3, "Li Haoyi", LocalDate.parse("1965-08-09")),
    Buyer[Sc](4, "Moo Moo Cow", LocalDate.parse("2000-01-01"))
  )
}

DbApi.updateGetGeneratedKeysRaw

Allows you to fetch the primary keys that were auto-generated for an INSERT defined using a raw java.lang.String and variables. Note: not supported by Sqlite https://github.com/xerial/sqlite-jdbc/issues/980

dbClient.transaction { db =>
  val generatedKeys = db.updateGetGeneratedKeysRaw[Int](
    "INSERT INTO buyer (name, date_of_birth) VALUES (?, ?), (?, ?)",
    Seq(
      "Moo Moo Cow",
      LocalDate.parse("2000-01-01"),
      "Moo Moo Cow",
      LocalDate.parse("2000-01-01")
    )
  )
  if (!this.isInstanceOf[MsSqlSuite])
    assert(generatedKeys == Seq(4, 5))
  else
    assert(generatedKeys == Seq(5))

  db.run(Buyer.select) ==> List(
    Buyer[Sc](1, "James Bond", LocalDate.parse("2001-02-03")),
    Buyer[Sc](2, "叉烧包", LocalDate.parse("1923-11-12")),
    Buyer[Sc](3, "Li Haoyi", LocalDate.parse("1965-08-09")),
    Buyer[Sc](4, "Moo Moo Cow", LocalDate.parse("2000-01-01")),
    Buyer[Sc](5, "Moo Moo Cow", LocalDate.parse("2000-01-01"))
  )
}

DbApi.stream

db.stream can be run on queries that return Seq[T]s, and makes them return geny.Generator[T]s instead. This allows you to deserialize and process the returned database rows incrementally without buffering the entire Seq[T] in memory. Not that the underlying JDBC driver and the underlying database may each perform their own buffering depending on their implementation

dbClient.transaction { db =>
  val output = collection.mutable.Buffer.empty[String]

  db.stream(Buyer.select).generate { buyer =>
    output.append(buyer.name)
    if (buyer.id >= 2) Generator.End else Generator.Continue
  }

  output ==> List("James Bond", "叉烧包")
}

DbApi.streamSql

.streamSql provides a lower level interface to .stream, allowing you to pass in a SqlStr of the form sql"...", while allowing you to process the returned rows in a streaming fashion without.

dbClient.transaction { db =>
  val excluded = "James Bond"
  val output = db
    .streamSql[Buyer[Sc]](sql"SELECT * FROM buyer where name != $excluded")
    .takeWhile(_.id <= 2)
    .map(_.name)
    .toList

  output ==> List("叉烧包")
}

DbApi.streamRaw

.streamRaw provides a lowest level interface to .stream, allowing you to pass in a java.lang.String and a Seq[Any] representing the interpolated prepared statement variables

dbClient.transaction { db =>
  val excluded = "James Bond"
  val output = db
    .streamRaw[Buyer[Sc]]("SELECT * FROM buyer WHERE buyer.name <> ?", Seq(excluded))
    .takeWhile(_.id <= 2)
    .map(_.name)
    .toList

  output ==> List("叉烧包")
}

Transaction

Usage of transactions, rollbacks, and savepoints

Transaction.simple.commit

Common workflow to create a transaction and run a delete inside of it. The effect of the delete is visible both inside the transaction and outside after the transaction completes successfully and commits

dbClient.transaction { implicit db =>
  db.run(Purchase.select.size) ==> 7

  db.run(Purchase.delete(_ => true)) ==> 7

  db.run(Purchase.select.size) ==> 0
}

dbClient.transaction(_.run(Purchase.select.size)) ==> 0

Transaction.simple.isolation

You can use .updateRaw to perform SET TRANSACTION ISOLATION LEVEL commands, allowing you to configure the isolation and performance characteristics of concurrent transactions in your database

dbClient.transaction { implicit db =>
  db.updateRaw("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE")

  db.run(Purchase.select.size) ==> 7

  db.run(Purchase.delete(_ => true)) ==> 7

  db.run(Purchase.select.size) ==> 0
}

dbClient.transaction(_.run(Purchase.select.size)) ==> 0

Transaction.simple.rollback

Example of explicitly rolling back a transaction using the db.rollback() method. After rollback, the effects of the delete query are undone, and subsequent select queries can see the previously-deleted entries both inside and outside the transaction

dbClient.transaction { implicit db =>
  db.run(Purchase.select.size) ==> 7

  db.run(Purchase.delete(_ => true)) ==> 7

  db.run(Purchase.select.size) ==> 0

  db.rollback()

  db.run(Purchase.select.size) ==> 7
}

dbClient.transaction(_.run(Purchase.select.size)) ==> 7

Transaction.simple.throw

Transactions are also rolled back if they terminate with an uncaught exception

try {
  dbClient.transaction { implicit db =>
    db.run(Purchase.select.size) ==> 7

    db.run(Purchase.delete(_ => true)) ==> 7

    db.run(Purchase.select.size) ==> 0

    throw new FooException
  }
} catch {
  case e: FooException => /*donothing*/
}

dbClient.transaction(_.run(Purchase.select.size)) ==> 7

Transaction.savepoint.commit

Savepoints are like "sub" transactions: they let you declare a savepoint and roll back any changes to the savepoint later. If a savepoint block completes successfully, the savepoint changes are committed ("released") and remain visible later in the transaction and outside of it

dbClient.transaction { implicit db =>
  db.run(Purchase.select.size) ==> 7

  db.run(Purchase.delete(_.id <= 3)) ==> 3
  db.run(Purchase.select.size) ==> 4

  db.savepoint { _ =>
    db.run(Purchase.delete(_ => true)) ==> 4
    db.run(Purchase.select.size) ==> 0
  }

  db.run(Purchase.select.size) ==> 0
}

dbClient.transaction(_.run(Purchase.select.size)) ==> 0

Transaction.savepoint.rollback

Like transactions, savepoints support the .rollback() method, to undo any changes since the start of the savepoint block.

dbClient.transaction { implicit db =>
  db.run(Purchase.select.size) ==> 7

  db.run(Purchase.delete(_.id <= 3)) ==> 3
  db.run(Purchase.select.size) ==> 4

  db.savepoint { sp =>
    db.run(Purchase.delete(_ => true)) ==> 4
    db.run(Purchase.select.size) ==> 0
    sp.rollback()
    db.run(Purchase.select.size) ==> 4
  }

  db.run(Purchase.select.size) ==> 4
}

dbClient.transaction(_.run(Purchase.select.size)) ==> 4

Transaction.savepoint.throw

Savepoints also roll back their enclosed changes automatically if they terminate with an uncaught exception

dbClient.transaction { implicit db =>
  db.run(Purchase.select.size) ==> 7

  db.run(Purchase.delete(_.id <= 3)) ==> 3
  db.run(Purchase.select.size) ==> 4

  try {
    db.savepoint { _ =>
      db.run(Purchase.delete(_ => true)) ==> 4
      db.run(Purchase.select.size) ==> 0
      throw new FooException
    }
  } catch {
    case e: FooException => /*donothing*/
  }

  db.run(Purchase.select.size) ==> 4
}

dbClient.transaction(_.run(Purchase.select.size)) ==> 4

Transaction.doubleSavepoint.commit

Only one transaction can be present at a time, but savepoints can be arbitrarily nested. Uncaught exceptions or explicit .rollback() calls would roll back changes done during the inner savepoint/transaction blocks, while leaving changes applied during outer savepoint/transaction blocks in-place

dbClient.transaction { implicit db =>
  db.run(Purchase.select.size) ==> 7

  db.run(Purchase.delete(_.id <= 2)) ==> 2
  db.run(Purchase.select.size) ==> 5

  db.savepoint { _ =>
    db.run(Purchase.delete(_.id <= 4)) ==> 2
    db.run(Purchase.select.size) ==> 3

    db.savepoint { _ =>
      db.run(Purchase.delete(_.id <= 6)) ==> 2
      db.run(Purchase.select.size) ==> 1
    }

    db.run(Purchase.select.size) ==> 1
  }

  db.run(Purchase.select.size) ==> 1
}

dbClient.transaction(_.run(Purchase.select.size)) ==> 1

Transaction.useBlock

Both transaction and savepoint accessors are actually returning a special UseBlock[...] types. When you call transaction(use) it desugars into transaction.apply(use) that provides a default resource-style management - it creates a transaction (or savepoint), runs use block immediately, then releases (commits or rolls back) the transaction.

If you need more control over the lifecycle, you may use transaction.allocate() method that returns (resource, releaseFunction) pair. This is especially useful when delaying side-effects with FP libraries like cats-effect or ZIO. Important: allocate() is impure and must be delayed in that case also. The dbClient.transaction expression itself does not perform any side-effects, it just creates closures.

dbClient.transaction

Select

Basic SELECT operations: map, filter, join, etc.

Select.constant

The most simple thing you can query in the database is an Expr. These do not need to be related to any database tables, and translate into raw SELECT calls without FROM.

Expr(1) + Expr(2)

```
SELECT (? + ?) AS res
```
```
3
```

Select.table

You can list the contents of a table via the query Table.select. It returns a Seq[CaseClass[Sc]] with the entire contents of the table. Note that listing entire tables can be prohibitively expensive on real-world databases, and you should generally use filters as shown below

Buyer.select

SELECT buyer0.id AS id, buyer0.name AS name, buyer0.date_of_birth AS date_of_birth
FROM buyer buyer0

Seq(
  Buyer[Sc](id = 1, name = "James Bond", dateOfBirth = LocalDate.parse("2001-02-03")),
  Buyer[Sc](id = 2, name = "叉烧包", dateOfBirth = LocalDate.parse("1923-11-12")),
  Buyer[Sc](id = 3, name = "Li Haoyi", dateOfBirth = LocalDate.parse("1965-08-09"))
)

Select.filter.single

ScalaSql's .filter translates to SQL WHERE, in this case we are searching for rows with a particular buyerId

ShippingInfo.select.filter(_.buyerId `=` 2)

SELECT
    shipping_info0.id AS id,
    shipping_info0.buyer_id AS buyer_id,
    shipping_info0.shipping_date AS shipping_date
  FROM shipping_info shipping_info0
  WHERE (shipping_info0.buyer_id = ?)

Seq(
  ShippingInfo[Sc](1, 2, LocalDate.parse("2010-02-03")),
  ShippingInfo[Sc](3, 2, LocalDate.parse("2012-05-06"))
)

Select.filter.multiple

You can stack multiple .filters on a query.

ShippingInfo.select
  .filter(_.buyerId `=` 2)
  .filter(_.shippingDate `=` LocalDate.parse("2012-05-06"))

SELECT
  shipping_info0.id AS id,
  shipping_info0.buyer_id AS buyer_id,
  shipping_info0.shipping_date AS shipping_date
FROM shipping_info shipping_info0
WHERE (shipping_info0.buyer_id = ?) AND (shipping_info0.shipping_date = ?)

Seq(ShippingInfo[Sc](id = 3, buyerId = 2, shippingDate = LocalDate.parse("2012-05-06")))

Select.filter.dotSingle.pass

Queries that you expect to return a single row can be annotated with .single. This changes the return type of the .select from Seq[T] to just T, and throws an exception if zero or multiple rows were returned

ShippingInfo.select
  .filter(_.buyerId `=` 2)
  .filter(_.shippingDate `=` LocalDate.parse("2012-05-06"))
  .single

SELECT
  shipping_info0.id AS id,
  shipping_info0.buyer_id AS buyer_id,
  shipping_info0.shipping_date AS shipping_date
FROM shipping_info shipping_info0
WHERE (shipping_info0.buyer_id = ?) AND (shipping_info0.shipping_date = ?)

ShippingInfo[Sc](id = 3, buyerId = 2, shippingDate = LocalDate.parse("2012-05-06"))

Select.filter.combined

You can perform multiple checks in a single filter using &&

ShippingInfo.select
  .filter(p => p.buyerId `=` 2 && p.shippingDate `=` LocalDate.parse("2012-05-06"))

SELECT
  shipping_info0.id AS id,
  shipping_info0.buyer_id AS buyer_id,
  shipping_info0.shipping_date AS shipping_date
FROM shipping_info shipping_info0
WHERE ((shipping_info0.buyer_id = ?) AND (shipping_info0.shipping_date = ?))

Seq(ShippingInfo[Sc](3, 2, LocalDate.parse("2012-05-06")))

Select.filterIf.filter not added

ShippingInfo.select.filterIf(false)(_.buyerId `=` 2)

SELECT
    shipping_info0.id AS id,
    shipping_info0.buyer_id AS buyer_id,
    shipping_info0.shipping_date AS shipping_date
  FROM shipping_info shipping_info0

Seq(
  ShippingInfo[Sc](1, 2, LocalDate.parse("2010-02-03")),
  ShippingInfo[Sc](2, 1, LocalDate.parse("2012-04-05")),
  ShippingInfo[Sc](3, 2, LocalDate.parse("2012-05-06"))
)

Select.filterIf.filter added

ShippingInfo.select.filterIf(true)(_.buyerId `=` 2)

SELECT
    shipping_info0.id AS id,
    shipping_info0.buyer_id AS buyer_id,
    shipping_info0.shipping_date AS shipping_date
  FROM shipping_info shipping_info0
  WHERE (shipping_info0.buyer_id = ?)

Seq(
  ShippingInfo[Sc](1, 2, LocalDate.parse("2010-02-03")),
  ShippingInfo[Sc](3, 2, LocalDate.parse("2012-05-06"))
)

Select.filterOpt.filter not added

ShippingInfo.select.filterOpt[Int](None)((table, value) => table.buyerId `=` value)

SELECT
    shipping_info0.id AS id,
    shipping_info0.buyer_id AS buyer_id,
    shipping_info0.shipping_date AS shipping_date
  FROM shipping_info shipping_info0

Seq(
  ShippingInfo[Sc](1, 2, LocalDate.parse("2010-02-03")),
  ShippingInfo[Sc](2, 1, LocalDate.parse("2012-04-05")),
  ShippingInfo[Sc](3, 2, LocalDate.parse("2012-05-06"))
)

Select.filterOpt.filter added

ShippingInfo.select.filterOpt(Some(2))((table, value) => table.buyerId `=` value)

SELECT
    shipping_info0.id AS id,
    shipping_info0.buyer_id AS buyer_id,
    shipping_info0.shipping_date AS shipping_date
  FROM shipping_info shipping_info0
  WHERE (shipping_info0.buyer_id = ?)

Seq(
  ShippingInfo[Sc](1, 2, LocalDate.parse("2010-02-03")),
  ShippingInfo[Sc](3, 2, LocalDate.parse("2012-05-06"))
)

Select.map.single

.map allows you to select exactly what you want to return from a query, rather than returning the entire row. Here, we return only the names of the Buyers

Buyer.select.map(_.name)

SELECT buyer0.name AS res FROM buyer buyer0

Seq("James Bond", "叉烧包", "Li Haoyi")

Select.map.filterMap

The common use case of SELECT FROM WHERE can be achieved via .select.filter.map in ScalaSql

Product.select.filter(_.price < 100).map(_.name)

SELECT product0.name AS res FROM product product0 WHERE (product0.price < ?)

```
Seq("Face Mask", "Socks", "Cookie")
```

Select.map.tuple2

You can return multiple values from your .map by returning a tuple in your query, which translates into a Seq[Tuple] being returned when the query is run

Buyer.select.map(c => (c.name, c.id))

SELECT buyer0.name AS res_0, buyer0.id AS res_1 FROM buyer buyer0

Seq(("James Bond", 1), ("叉烧包", 2), ("Li Haoyi", 3))

Select.map.tuple3

Buyer.select.map(c => (c.name, c.id, c.dateOfBirth))

SELECT
  buyer0.name AS res_0,
  buyer0.id AS res_1,
  buyer0.date_of_birth AS res_2
FROM buyer buyer0

Seq(
  ("James Bond", 1, LocalDate.parse("2001-02-03")),
  ("叉烧包", 2, LocalDate.parse("1923-11-12")),
  ("Li Haoyi", 3, LocalDate.parse("1965-08-09"))
)

Select.map.interpolateInMap

You can perform operations inside the .map to change what you return

Product.select.map(_.price * 2)

SELECT (product0.price * ?) AS res FROM product product0

Seq(17.76, 600, 6.28, 246.9, 2000.0, 0.2)

Select.map.heterogenousTuple

.map can return any combination of tuples, case classes, and primitives, arbitrarily nested. here we return a tuple of (Int, Buyer[Sc])

Buyer.select.map(c => (c.id, c))

SELECT
  buyer0.id AS res_0,
  buyer0.id AS res_1_id,
  buyer0.name AS res_1_name,
  buyer0.date_of_birth AS res_1_date_of_birth
FROM buyer buyer0

Seq(
  (1, Buyer[Sc](1, "James Bond", LocalDate.parse("2001-02-03"))),
  (2, Buyer[Sc](2, "叉烧包", LocalDate.parse("1923-11-12"))),
  (3, Buyer[Sc](3, "Li Haoyi", LocalDate.parse("1965-08-09")))
)

Select.toExpr

SELECT queries that return a single row and column can be used as SQL expressions in standard SQL databases. In ScalaSql, this is done by the .toExpr method, which turns a Select[T] into an Expr[T]. Note that if the Select returns more than one row or column, the database may select a row arbitrarily or will throw an exception at runtime (depend on implenmentation)

Product.select.map(p =>
  (
    p.name,
    Purchase.select
      .filter(_.productId === p.id)
      .sortBy(_.total)
      .desc
      .take(1)
      .map(_.total)
      .toExpr
  )
)

SELECT
  product0.name AS res_0,
  (SELECT purchase1.total AS res
    FROM purchase purchase1
    WHERE (purchase1.product_id = product0.id)
    ORDER BY res DESC
    LIMIT ?) AS res_1
FROM product product0

Seq(
  ("Face Mask", 888.0),
  ("Guitar", 900.0),
  ("Socks", 15.7),
  ("Skate Board", 493.8),
  ("Camera", 10000.0),
  ("Cookie", 1.3)
)

Select.subquery

ScalaSql generally combines operations like .map and .filter to minimize the number of subqueries to keep the generated SQL readable. If you explicitly want a subquery for some reason (e.g. to influence the database query planner), you can use the .subquery to force a query to be translated into a standalone subquery

Buyer.select.subquery.map(_.name)

SELECT subquery0.name AS res
FROM (SELECT buyer0.name AS name FROM buyer buyer0) subquery0

Seq("James Bond", "叉烧包", "Li Haoyi")

Select.aggregate.single

You can use methods like .sumBy or .sum on your queries to generate SQL SUM(...) aggregates

Purchase.select.sumBy(_.total)

SELECT SUM(purchase0.total) AS res FROM purchase purchase0

```
12343.2
```

Select.aggregate.multiple

If you want to perform multiple aggregates at once, you can use the .aggregate method which takes a function allowing you to call multiple aggregates inside of it

Purchase.select.aggregate(q => (q.sumBy(_.total), q.maxBy(_.total)))

SELECT SUM(purchase0.total) AS res_0, MAX(purchase0.total) AS res_1 FROM purchase purchase0

```
(12343.2, 10000.0)
```

Select.groupBy.simple

ScalaSql's .groupBy method translates into a SQL GROUP BY. Unlike the normal .groupBy provided by scala.Seq, ScalaSql's .groupBy requires you to pass an aggregate as a second parameter, mirroring the SQL requirement that any column not part of the GROUP BY clause has to be in an aggregate.

Purchase.select.groupBy(_.productId)(_.sumBy(_.total))

SELECT purchase0.product_id AS res_0, SUM(purchase0.total) AS res_1
FROM purchase purchase0
GROUP BY purchase0.product_id

Seq((1, 932.4), (2, 900.0), (3, 15.7), (4, 493.8), (5, 10000.0), (6, 1.30))

Select.groupBy.having

.filter calls following a .groupBy are automatically translated to SQL HAVING clauses

Purchase.select.groupBy(_.productId)(_.sumBy(_.total)).filter(_._2 > 100).filter(_._1 > 1)

SELECT purchase0.product_id AS res_0, SUM(purchase0.total) AS res_1
FROM purchase purchase0
GROUP BY purchase0.product_id
HAVING (SUM(purchase0.total) > ?) AND (purchase0.product_id > ?)

Seq((2, 900.0), (4, 493.8), (5, 10000.0))

Select.groupBy.filterHaving

Purchase.select
  .filter(_.count > 5)
  .groupBy(_.productId)(_.sumBy(_.total))
  .filter(_._2 > 100)

SELECT purchase0.product_id AS res_0, SUM(purchase0.total) AS res_1
FROM purchase purchase0
WHERE (purchase0.count > ?)
GROUP BY purchase0.product_id
HAVING (SUM(purchase0.total) > ?)

```
Seq((1, 888.0), (5, 10000.0))
```

Select.groupBy.multipleKeys

Purchase.select.groupBy(x => (x.shippingInfoId, x.productId))(_.sumBy(_.total))

SELECT
  purchase0.shipping_info_id AS res_0_0,
  purchase0.product_id AS res_0_1,
  SUM(purchase0.total) AS res_1
FROM 
  purchase purchase0
GROUP BY
  purchase0.shipping_info_id,
  purchase0.product_id

Seq(
  ((1, 1), 888.0),
  ((1, 2), 900.0),
  ((1, 3), 15.7),
  ((2, 4), 493.8),
  ((2, 5), 10000.0),
  ((3, 1), 44.4),
  ((3, 6), 1.3)
)

Select.groupBy.multipleKeysHaving

Purchase.select
  .groupBy(x => (x.shippingInfoId, x.productId))(_.sumBy(_.total))
  .filter(_._2 > 10)
  .filter(_._2 < 100)

SELECT
  purchase0.shipping_info_id AS res_0_0,
  purchase0.product_id AS res_0_1,
  SUM(purchase0.total) AS res_1
FROM 
  purchase purchase0
GROUP BY
  purchase0.shipping_info_id,
  purchase0.product_id
HAVING 
  (SUM(purchase0.total) > ?) AND (SUM(purchase0.total) < ?)

```
Seq(((1, 3), 15.7), ((3, 1), 44.4))
```

Select.distinct.nondistinct

Normal queries can allow duplicates in the returned row values, as seen below. You can use the .distinct operator (translates to SQl's SELECT DISTINCT) to eliminate those duplicates

Purchase.select.map(_.shippingInfoId)

SELECT purchase0.shipping_info_id AS res FROM purchase purchase0

```
Seq(1, 1, 1, 2, 2, 3, 3)
```

Select.distinct.distinct

Purchase.select.map(_.shippingInfoId).distinct

SELECT DISTINCT purchase0.shipping_info_id AS res FROM purchase purchase0

```
Seq(1, 2, 3)
```

Select.distinct.subquery

Columns inside nested subqueries cannot be elided when SELECT DISTINCT is used

ShippingInfo.select.distinct.subquery.map(_.buyerId)

SELECT subquery0.buyer_id AS res
FROM (SELECT DISTINCT
    shipping_info0.id AS id,
    shipping_info0.buyer_id AS buyer_id,
    shipping_info0.shipping_date AS shipping_date
  FROM shipping_info shipping_info0) subquery0

```
Seq(1, 2, 2)
```

Select.contains

ScalaSql's .contains method translates into SQL's IN syntax, e.g. here checking if a subquery contains a column as part of a WHERE clause

Buyer.select.filter(b => ShippingInfo.select.map(_.buyerId).contains(b.id))

SELECT buyer0.id AS id, buyer0.name AS name, buyer0.date_of_birth AS date_of_birth
FROM buyer buyer0
WHERE (buyer0.id IN (SELECT shipping_info1.buyer_id AS res FROM shipping_info shipping_info1))

Seq(
  Buyer[Sc](1, "James Bond", LocalDate.parse("2001-02-03")),
  Buyer[Sc](2, "叉烧包", LocalDate.parse("1923-11-12"))
)

Select.containsMultiple

ScalaSql's .contains can take a compound Scala value, which translates into SQL's IN syntax on a tuple with multiple columns. e.g. this query uses that ability to find the Buyer which has a shipment on a specific date, as an alternative to doing a JOIN.

Buyer.select.filter(b =>
  ShippingInfo.select
    .map(s => (s.buyerId, s.shippingDate))
    .contains((b.id, LocalDate.parse("2010-02-03")))
)

SELECT buyer0.id AS id, buyer0.name AS name, buyer0.date_of_birth AS date_of_birth
FROM buyer buyer0
WHERE ((buyer0.id, ?) IN (SELECT
    shipping_info1.buyer_id AS res_0,
    shipping_info1.shipping_date AS res_1
  FROM shipping_info shipping_info1))

Seq(
  Buyer[Sc](2, "叉烧包", LocalDate.parse("1923-11-12"))
)

Select.nonEmpty

ScalaSql's .nonEmpty and .isEmpty translates to SQL's EXISTS and NOT EXISTS syntax

Buyer.select
  .map(b => (b.name, ShippingInfo.select.filter(_.buyerId `=` b.id).map(_.id).nonEmpty))

SELECT
  buyer0.name AS res_0,
  (EXISTS (SELECT
    shipping_info1.id AS res
    FROM shipping_info shipping_info1
    WHERE (shipping_info1.buyer_id = buyer0.id))) AS res_1
FROM buyer buyer0

Seq(("James Bond", true), ("叉烧包", true), ("Li Haoyi", false))

Select.isEmpty

Buyer.select
  .map(b => (b.name, ShippingInfo.select.filter(_.buyerId `=` b.id).map(_.id).isEmpty))

SELECT
  buyer0.name AS res_0,
  (NOT EXISTS (SELECT
    shipping_info1.id AS res
    FROM shipping_info shipping_info1
    WHERE (shipping_info1.buyer_id = buyer0.id))) AS res_1
FROM buyer buyer0

Seq(("James Bond", false), ("叉烧包", false), ("Li Haoyi", true))

Select.nestedTuples

Queries can output arbitrarily nested tuples of Expr[T] and case class instances of Foo[Expr], which will be de-serialized into nested tuples of T and Foo[Sc]s. The AS aliases assigned to each column will contain the path of indices and field names used to populate the final returned values

Buyer.select
  .join(ShippingInfo)(_.id === _.buyerId)
  .sortBy(_._1.id)
  .map { case (b, s) => (b.id, (b, (s.id, s))) }

SELECT
  buyer0.id AS res_0,
  buyer0.id AS res_1_0_id,
  buyer0.name AS res_1_0_name,
  buyer0.date_of_birth AS res_1_0_date_of_birth,
  shipping_info1.id AS res_1_1_0,
  shipping_info1.id AS res_1_1_1_id,
  shipping_info1.buyer_id AS res_1_1_1_buyer_id,
  shipping_info1.shipping_date AS res_1_1_1_shipping_date
FROM buyer buyer0
JOIN shipping_info shipping_info1 ON (buyer0.id = shipping_info1.buyer_id)
ORDER BY res_1_0_id

Seq[(Int, (Buyer[Sc], (Int, ShippingInfo[Sc])))](
  (
    1,
    (
      Buyer[Sc](1, "James Bond", LocalDate.parse("2001-02-03")),
      (2, ShippingInfo[Sc](2, 1, LocalDate.parse("2012-04-05")))
    )
  ),
  (
    2,
    (
      Buyer[Sc](2, "叉烧包", LocalDate.parse("1923-11-12")),
      (1, ShippingInfo[Sc](1, 2, LocalDate.parse("2010-02-03")))
    )
  ),
  (
    2,
    (
      Buyer[Sc](2, "叉烧包", LocalDate.parse("1923-11-12")),
      (3, ShippingInfo[Sc](3, 2, LocalDate.parse("2012-05-06")))
    )
  )
)

Select.case.when

ScalaSql's caseWhen method translates into SQL's CASE/WHEN/ELSE/END syntax, allowing you to perform basic conditionals as part of your SQL query

Product.select.map(p =>
  db.caseWhen(
    (p.price > 200) -> (p.name + " EXPENSIVE"),
    (p.price > 5) -> (p.name + " NORMAL"),
    (p.price <= 5) -> (p.name + " CHEAP")
  )
)

SELECT
  CASE
    WHEN (product0.price > ?) THEN (product0.name || ?)
    WHEN (product0.price > ?) THEN (product0.name || ?)
    WHEN (product0.price <= ?) THEN (product0.name || ?)
  END AS res
FROM product product0

Seq(
  "Face Mask NORMAL",
  "Guitar EXPENSIVE",
  "Socks CHEAP",
  "Skate Board NORMAL",
  "Camera EXPENSIVE",
  "Cookie CHEAP"
)

Select.case.else

Product.select.map(p =>
  db.caseWhen(
    (p.price > 200) -> (p.name + " EXPENSIVE"),
    (p.price > 5) -> (p.name + " NORMAL")
  ).`else` { p.name + " UNKNOWN" }
)

SELECT
  CASE
    WHEN (product0.price > ?) THEN (product0.name || ?)
    WHEN (product0.price > ?) THEN (product0.name || ?)
    ELSE (product0.name || ?)
  END AS res
FROM product product0

Seq(
  "Face Mask NORMAL",
  "Guitar EXPENSIVE",
  "Socks UNKNOWN",
  "Skate Board NORMAL",
  "Camera EXPENSIVE",
  "Cookie UNKNOWN"
)

Join

inner JOINs, JOIN ONs, self-joins, LEFT/RIGHT/OUTER JOINs

Join.joinFilter

ScalaSql's .join or .join methods correspond to SQL JOIN and JOIN ... ON .... These perform an inner join between two tables, with an optional ON predicate. You can also .filter and .map the results of the join, making use of the columns joined from the two tables

Buyer.select.join(ShippingInfo)(_.id `=` _.buyerId).filter(_._1.name `=` "叉烧包")

SELECT
  buyer0.id AS res_0_id,
  buyer0.name AS res_0_name,
  buyer0.date_of_birth AS res_0_date_of_birth,
  shipping_info1.id AS res_1_id,
  shipping_info1.buyer_id AS res_1_buyer_id,
  shipping_info1.shipping_date AS res_1_shipping_date
FROM buyer buyer0
JOIN shipping_info shipping_info1 ON (buyer0.id = shipping_info1.buyer_id)
WHERE (buyer0.name = ?)

Seq(
  (
    Buyer[Sc](2, "叉烧包", LocalDate.parse("1923-11-12")),
    ShippingInfo[Sc](1, 2, LocalDate.parse("2010-02-03"))
  ),
  (
    Buyer[Sc](2, "叉烧包", LocalDate.parse("1923-11-12")),
    ShippingInfo[Sc](3, 2, LocalDate.parse("2012-05-06"))
  )
)

Join.joinFilterMap

Buyer.select
  .join(ShippingInfo)(_.id `=` _.buyerId)
  .filter(_._1.name `=` "James Bond")
  .map(_._2.shippingDate)

SELECT shipping_info1.shipping_date AS res
FROM buyer buyer0
JOIN shipping_info shipping_info1 ON (buyer0.id = shipping_info1.buyer_id)
WHERE (buyer0.name = ?)

```
Seq(LocalDate.parse("2012-04-05"))
```

Join.selfJoin

ScalaSql supports a "self join", where a table is joined with itself. This is done by simply having the same table be on the left-hand-side and right-hand-side of your .join or .join method. The two example self-joins below are trivial, but illustrate how to do it in case you want to do a self-join in a more realistic setting.

Buyer.select.join(Buyer)(_.id `=` _.id)

SELECT
  buyer0.id AS res_0_id,
  buyer0.name AS res_0_name,
  buyer0.date_of_birth AS res_0_date_of_birth,
  buyer1.id AS res_1_id,
  buyer1.name AS res_1_name,
  buyer1.date_of_birth AS res_1_date_of_birth
FROM buyer buyer0
JOIN buyer buyer1 ON (buyer0.id = buyer1.id)

Seq(
  (
    Buyer[Sc](1, "James Bond", LocalDate.parse("2001-02-03")),
    Buyer[Sc](1, "James Bond", LocalDate.parse("2001-02-03"))
  ),
  (
    Buyer[Sc](2, "叉烧包", LocalDate.parse("1923-11-12")),
    Buyer[Sc](2, "叉烧包", LocalDate.parse("1923-11-12"))
  ),
  (
    Buyer[Sc](3, "Li Haoyi", LocalDate.parse("1965-08-09")),
    Buyer[Sc](3, "Li Haoyi", LocalDate.parse("1965-08-09"))
  )
)

Join.selfJoin2

Buyer.select.join(Buyer)(_.id <> _.id)

SELECT
  buyer0.id AS res_0_id,
  buyer0.name AS res_0_name,
  buyer0.date_of_birth AS res_0_date_of_birth,
  buyer1.id AS res_1_id,
  buyer1.name AS res_1_name,
  buyer1.date_of_birth AS res_1_date_of_birth
FROM buyer buyer0
JOIN buyer buyer1 ON (buyer0.id <> buyer1.id)

Seq(
  (
    Buyer[Sc](1, "James Bond", LocalDate.parse("2001-02-03")),
    Buyer[Sc](2, "叉烧包", LocalDate.parse("1923-11-12"))
  ),
  (
    Buyer[Sc](1, "James Bond", LocalDate.parse("2001-02-03")),
    Buyer[Sc](3, "Li Haoyi", LocalDate.parse("1965-08-09"))
  ),
  (
    Buyer[Sc](2, "叉烧包", LocalDate.parse("1923-11-12")),
    Buyer[Sc](1, "James Bond", LocalDate.parse("2001-02-03"))
  ),
  (
    Buyer[Sc](2, "叉烧包", LocalDate.parse("1923-11-12")),
    Buyer[Sc](3, "Li Haoyi", LocalDate.parse("1965-08-09"))
  ),
  (
    Buyer[Sc](3, "Li Haoyi", LocalDate.parse("1965-08-09")),
    Buyer[Sc](1, "James Bond", LocalDate.parse("2001-02-03"))
  ),
  (
    Buyer[Sc](3, "Li Haoyi", LocalDate.parse("1965-08-09")),
    Buyer[Sc](2, "叉烧包", LocalDate.parse("1923-11-12"))
  )
)

Join.mapForGroupBy

Using non-trivial queries in the for-comprehension may result in subqueries being generated

for ((name, dateOfBirth) <- Buyer.select.groupBy(_.name)(_.minBy(_.dateOfBirth)))
  yield (name, dateOfBirth)

SELECT buyer0.name AS res_0, MIN(buyer0.date_of_birth) AS res_1
FROM buyer buyer0
GROUP BY buyer0.name

Seq(
  ("James Bond", LocalDate.parse("2001-02-03")),
  ("Li Haoyi", LocalDate.parse("1965-08-09")),
  ("叉烧包", LocalDate.parse("1923-11-12"))
)

Join.leftJoin

ScalaSql supports LEFT JOINs, RIGHT JOINs and OUTER JOINs via the .leftJoin/.rightJoin/.outerJoin methods

Buyer.select.leftJoin(ShippingInfo)(_.id `=` _.buyerId)

SELECT
  buyer0.id AS res_0_id,
  buyer0.name AS res_0_name,
  buyer0.date_of_birth AS res_0_date_of_birth,
  shipping_info1.id AS res_1_id,
  shipping_info1.buyer_id AS res_1_buyer_id,
  shipping_info1.shipping_date AS res_1_shipping_date
FROM buyer buyer0
LEFT JOIN shipping_info shipping_info1 ON (buyer0.id = shipping_info1.buyer_id)

Seq(
  (
    Buyer[Sc](1, "James Bond", LocalDate.parse("2001-02-03")),
    Some(ShippingInfo[Sc](2, 1, LocalDate.parse("2012-04-05")))
  ),
  (
    Buyer[Sc](2, "叉烧包", LocalDate.parse("1923-11-12")),
    Some(ShippingInfo[Sc](1, 2, LocalDate.parse("2010-02-03")))
  ),
  (
    Buyer[Sc](2, "叉烧包", LocalDate.parse("1923-11-12")),
    Some(ShippingInfo[Sc](3, 2, LocalDate.parse("2012-05-06")))
  ),
  (Buyer[Sc](3, "Li Haoyi", LocalDate.parse("1965-08-09")), None)
)

Join.leftJoinMap

.leftJoins return a JoinNullable[Q] for the right hand entry. This is similar to Option[Q] in Scala, supports a similar set of operations (e.g. .map), and becomes an Option[Q] after the query is executed

Buyer.select
  .leftJoin(ShippingInfo)(_.id `=` _.buyerId)
  .map { case (b, si) => (b.name, si.map(_.shippingDate)) }

SELECT buyer0.name AS res_0, shipping_info1.shipping_date AS res_1
FROM buyer buyer0
LEFT JOIN shipping_info shipping_info1 ON (buyer0.id = shipping_info1.buyer_id)

Seq(
  ("James Bond", Some(LocalDate.parse("2012-04-05"))),
  ("Li Haoyi", None),
  ("叉烧包", Some(LocalDate.parse("2010-02-03"))),
  ("叉烧包", Some(LocalDate.parse("2012-05-06")))
)

Join.leftJoinMap2

Buyer.select
  .leftJoin(ShippingInfo)(_.id `=` _.buyerId)
  .map { case (b, si) => (b.name, si.map(s => (s.id, s.shippingDate))) }

SELECT
  buyer0.name AS res_0,
  shipping_info1.id AS res_1_0,
  shipping_info1.shipping_date AS res_1_1
FROM buyer buyer0
LEFT JOIN shipping_info shipping_info1 ON (buyer0.id = shipping_info1.buyer_id)

Seq(
  ("James Bond", Some((2, LocalDate.parse("2012-04-05")))),
  ("Li Haoyi", None),
  ("叉烧包", Some((1, LocalDate.parse("2010-02-03")))),
  ("叉烧包", Some((3, LocalDate.parse("2012-05-06"))))
)

Join.leftJoinExpr

JoinNullable[Expr[T]]s can be implicitly used as Expr[Option[T]]s. This allows them to participate in any database query logic than any other Expr[Option[T]]s can participate in, such as being used as sort key or in computing return values (below).

Buyer.select
  .leftJoin(ShippingInfo)(_.id `=` _.buyerId)
  .map { case (b, si) => (b.name, si.map(_.shippingDate)) }
  .sortBy(_._2)

SELECT buyer0.name AS res_0, shipping_info1.shipping_date AS res_1
FROM buyer buyer0
LEFT JOIN shipping_info shipping_info1 ON (buyer0.id = shipping_info1.buyer_id)
ORDER BY res_1

Seq[(String, Option[LocalDate])](
  ("Li Haoyi", None),
  ("叉烧包", Some(LocalDate.parse("2010-02-03"))),
  ("James Bond", Some(LocalDate.parse("2012-04-05"))),
  ("叉烧包", Some(LocalDate.parse("2012-05-06")))
)

Join.leftJoinIsEmpty

You can use the .isEmpty method on JoinNullable[T] to check whether a joined table is NULL, by specifying a specific non-nullable column to test against.

Buyer.select
  .leftJoin(ShippingInfo)(_.id `=` _.buyerId)
  .map { case (b, si) => (b.name, si.nonEmpty(_.id)) }
  .distinct
  .sortBy(_._1)

SELECT DISTINCT buyer0.name AS res_0, (shipping_info1.id IS NOT NULL) AS res_1
FROM buyer buyer0
LEFT JOIN shipping_info shipping_info1 ON (buyer0.id = shipping_info1.buyer_id)
ORDER BY res_0

Seq(
  ("James Bond", true),
  ("Li Haoyi", false),
  ("叉烧包", true)
)

Join.leftJoinExpr2

Buyer.select
  .leftJoin(ShippingInfo)(_.id `=` _.buyerId)
  .map { case (b, si) => (b.name, si.map(_.shippingDate) > b.dateOfBirth) }

SELECT
  buyer0.name AS res_0,
  (shipping_info1.shipping_date > buyer0.date_of_birth) AS res_1
FROM buyer buyer0
LEFT JOIN shipping_info shipping_info1 ON (buyer0.id = shipping_info1.buyer_id)

Seq(
  ("James Bond", true),
  ("Li Haoyi", false),
  ("叉烧包", true),
  ("叉烧包", true)
)

Join.leftJoinExprExplicit

The conversion from JoinNullable[T] to Expr[Option[T]] can also be performed explicitly via JoinNullable.toExpr(...)

Buyer.select
  .leftJoin(ShippingInfo)(_.id `=` _.buyerId)
  .map { case (b, si) =>
    (b.name, JoinNullable.toExpr(si.map(_.shippingDate)) > b.dateOfBirth)
  }

SELECT
  buyer0.name AS res_0,
  (shipping_info1.shipping_date > buyer0.date_of_birth) AS res_1
FROM buyer buyer0
LEFT JOIN shipping_info shipping_info1 ON (buyer0.id = shipping_info1.buyer_id)

Seq(
  ("James Bond", true),
  ("Li Haoyi", false),
  ("叉烧包", true),
  ("叉烧包", true)
)

Join.rightJoin

ShippingInfo.select.rightJoin(Buyer)(_.buyerId `=` _.id)

SELECT
  shipping_info0.id AS res_0_id,
  shipping_info0.buyer_id AS res_0_buyer_id,
  shipping_info0.shipping_date AS res_0_shipping_date,
  buyer1.id AS res_1_id,
  buyer1.name AS res_1_name,
  buyer1.date_of_birth AS res_1_date_of_birth
FROM shipping_info shipping_info0
RIGHT JOIN buyer buyer1 ON (shipping_info0.buyer_id = buyer1.id)

Seq(
  (
    Some(ShippingInfo[Sc](2, 1, LocalDate.parse("2012-04-05"))),
    Buyer[Sc](1, "James Bond", LocalDate.parse("2001-02-03"))
  ),
  (
    Some(ShippingInfo[Sc](1, 2, LocalDate.parse("2010-02-03"))),
    Buyer[Sc](2, "叉烧包", LocalDate.parse("1923-11-12"))
  ),
  (
    Some(ShippingInfo[Sc](3, 2, LocalDate.parse("2012-05-06"))),
    Buyer[Sc](2, "叉烧包", LocalDate.parse("1923-11-12"))
  ),
  (None, Buyer[Sc](3, "Li Haoyi", LocalDate.parse("1965-08-09")))
)

Join.outerJoin

ShippingInfo.select.outerJoin(Buyer)(_.buyerId `=` _.id)

SELECT
  shipping_info0.id AS res_0_id,
  shipping_info0.buyer_id AS res_0_buyer_id,
  shipping_info0.shipping_date AS res_0_shipping_date,
  buyer1.id AS res_1_id,
  buyer1.name AS res_1_name,
  buyer1.date_of_birth AS res_1_date_of_birth
FROM shipping_info shipping_info0
FULL OUTER JOIN buyer buyer1 ON (shipping_info0.buyer_id = buyer1.id)

Seq(
  (
    Option(ShippingInfo[Sc](2, 1, LocalDate.parse("2012-04-05"))),
    Option(Buyer[Sc](1, "James Bond", LocalDate.parse("2001-02-03")))
  ),
  (
    Option(ShippingInfo[Sc](1, 2, LocalDate.parse("2010-02-03"))),
    Option(Buyer[Sc](2, "叉烧包", LocalDate.parse("1923-11-12")))
  ),
  (
    Option(ShippingInfo[Sc](3, 2, LocalDate.parse("2012-05-06"))),
    Option(Buyer[Sc](2, "叉烧包", LocalDate.parse("1923-11-12")))
  ),
  (Option.empty, Option(Buyer[Sc](3, "Li Haoyi", LocalDate.parse("1965-08-09"))))
)

Join.crossJoin

.crossJoin can be used to generate a SQL CROSS JOIN, which allows you to perform a JOIN with an ON clause in a consistent way across databases

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

SELECT buyer0.name AS res_0, shipping_info1.shipping_date AS res_1
FROM buyer buyer0
CROSS JOIN shipping_info shipping_info1
WHERE (buyer0.id = shipping_info1.buyer_id)

Seq(
  ("James Bond", LocalDate.parse("2012-04-05")),
  ("叉烧包", LocalDate.parse("2010-02-03")),
  ("叉烧包", LocalDate.parse("2012-05-06"))
)

FlatJoin

inner JOINs, JOIN ONs, self-joins, LEFT/RIGHT/OUTER JOINs

FlatJoin.join

"flat" joins using for-comprehensions are allowed. These allow you to "flatten out" the nested tuples you get from normal .join clauses, letting you write natural looking queries without deeply nested tuples.

for {
  b <- Buyer.select
  si <- ShippingInfo.join(_.buyerId `=` b.id)
} yield (b.name, si.shippingDate)

SELECT buyer0.name AS res_0, shipping_info1.shipping_date AS res_1
FROM buyer buyer0
JOIN shipping_info shipping_info1 ON (shipping_info1.buyer_id = buyer0.id)

Seq(
  ("James Bond", LocalDate.parse("2012-04-05")),
  ("叉烧包", LocalDate.parse("2010-02-03")),
  ("叉烧包", LocalDate.parse("2012-05-06"))
)

FlatJoin.join3

"flat" joins using for-comprehensions can have multiple .join clauses that translate to SQL JOIN ONs, as well as if clauses that translate to SQL WHERE clauses. This example uses multiple flat .joins together with if clauses to query the products purchased by the user "Li Haoyi" that have a price more than 1.0 dollars

for {
  b <- Buyer.select
  if b.name === "Li Haoyi"
  si <- ShippingInfo.join(_.id `=` b.id)
  pu <- Purchase.join(_.shippingInfoId `=` si.id)
  pr <- Product.join(_.id `=` pu.productId)
  if pr.price > 1.0
} yield (b.name, pr.name, pr.price)

SELECT buyer0.name AS res_0, product3.name AS res_1, product3.price AS res_2
FROM buyer buyer0
JOIN shipping_info shipping_info1 ON (shipping_info1.id = buyer0.id)
JOIN purchase purchase2 ON (purchase2.shipping_info_id = shipping_info1.id)
JOIN product product3 ON (product3.id = purchase2.product_id)
WHERE (buyer0.name = ?) AND (product3.price > ?)

Seq(
  ("Li Haoyi", "Face Mask", 8.88)
)

FlatJoin.leftJoin

Flat joins can also support .leftJoins, where the table being joined is given to you as a JoinNullable[T]

for {
  b <- Buyer.select
  si <- ShippingInfo.leftJoin(_.buyerId `=` b.id)
} yield (b.name, si.map(_.shippingDate))

SELECT buyer0.name AS res_0, shipping_info1.shipping_date AS res_1
FROM buyer buyer0
LEFT JOIN shipping_info shipping_info1 ON (shipping_info1.buyer_id = buyer0.id)

Seq(
  ("James Bond", Some(LocalDate.parse("2012-04-05"))),
  ("Li Haoyi", None),
  ("叉烧包", Some(LocalDate.parse("2010-02-03"))),
  ("叉烧包", Some(LocalDate.parse("2012-05-06")))
)

FlatJoin.flatMap

You can also perform inner joins via flatMap, either by directly calling .flatMap or via for-comprehensions as below. This can help reduce the boilerplate when dealing with lots of joins.

Buyer.select
  .flatMap(b => ShippingInfo.crossJoin().map((b, _)))
  .filter { case (b, s) => b.id `=` s.buyerId && b.name `=` "James Bond" }
  .map(_._2.shippingDate)

SELECT shipping_info1.shipping_date AS res
FROM buyer buyer0
CROSS JOIN shipping_info shipping_info1
WHERE ((buyer0.id = shipping_info1.buyer_id) AND (buyer0.name = ?))

```
Seq(LocalDate.parse("2012-04-05"))
```

FlatJoin.flatMapFor

You can also perform inner joins via `flatMap

for {
  b <- Buyer.select
  s <- ShippingInfo.crossJoin()
  if b.id `=` s.buyerId && b.name `=` "James Bond"
} yield s.shippingDate

SELECT shipping_info1.shipping_date AS res
FROM buyer buyer0
CROSS JOIN shipping_info shipping_info1
WHERE ((buyer0.id = shipping_info1.buyer_id) AND (buyer0.name = ?))

```
Seq(LocalDate.parse("2012-04-05"))
```

FlatJoin.flatMapForFilter

for {
  b <- Buyer.select.filter(_.name `=` "James Bond")
  s <- ShippingInfo.crossJoin().filter(b.id `=` _.buyerId)
} yield s.shippingDate

SELECT shipping_info1.shipping_date AS res
FROM buyer buyer0
CROSS JOIN shipping_info shipping_info1
WHERE (buyer0.name = ?) AND (buyer0.id = shipping_info1.buyer_id)

```
Seq(LocalDate.parse("2012-04-05"))
```

FlatJoin.flatMapForJoin

Using queries with joins in a for-comprehension is supported, with the generated JOINs being added to the FROM clause generated by the .flatMap.

for {
  (b, si) <- Buyer.select.join(ShippingInfo)(_.id `=` _.buyerId)
  (pu, pr) <- Purchase.select.join(Product)(_.productId `=` _.id).crossJoin()
  if si.id `=` pu.shippingInfoId
} yield (b.name, pr.name)

SELECT buyer0.name AS res_0, subquery2.res_1_name AS res_1
FROM buyer buyer0
JOIN shipping_info shipping_info1 ON (buyer0.id = shipping_info1.buyer_id)
CROSS JOIN (SELECT
    purchase2.shipping_info_id AS res_0_shipping_info_id,
    product3.name AS res_1_name
  FROM purchase purchase2
  JOIN product product3 ON (purchase2.product_id = product3.id)) subquery2
WHERE (shipping_info1.id = subquery2.res_0_shipping_info_id)

Seq(
  ("James Bond", "Camera"),
  ("James Bond", "Skate Board"),
  ("叉烧包", "Cookie"),
  ("叉烧包", "Face Mask"),
  ("叉烧包", "Face Mask"),
  ("叉烧包", "Guitar"),
  ("叉烧包", "Socks")
)

FlatJoin.flatMapForGroupBy

Using non-trivial queries in the for-comprehension may result in subqueries being generated

for {
  (name, dateOfBirth) <- Buyer.select.groupBy(_.name)(_.minBy(_.dateOfBirth))
  shippingInfo <- ShippingInfo.crossJoin()
} yield (name, dateOfBirth, shippingInfo.id, shippingInfo.shippingDate)

SELECT
  subquery0.res_0 AS res_0,
  subquery0.res_1 AS res_1,
  shipping_info1.id AS res_2,
  shipping_info1.shipping_date AS res_3
FROM (SELECT buyer0.name AS res_0, MIN(buyer0.date_of_birth) AS res_1
  FROM buyer buyer0
  GROUP BY buyer0.name) subquery0
CROSS JOIN shipping_info shipping_info1

Seq(
  ("James Bond", LocalDate.parse("2001-02-03"), 1, LocalDate.parse("2010-02-03")),
  ("James Bond", LocalDate.parse("2001-02-03"), 2, LocalDate.parse("2012-04-05")),
  ("James Bond", LocalDate.parse("2001-02-03"), 3, LocalDate.parse("2012-05-06")),
  ("Li Haoyi", LocalDate.parse("1965-08-09"), 1, LocalDate.parse("2010-02-03")),
  ("Li Haoyi", LocalDate.parse("1965-08-09"), 2, LocalDate.parse("2012-04-05")),
  ("Li Haoyi", LocalDate.parse("1965-08-09"), 3, LocalDate.parse("2012-05-06")),
  ("叉烧包", LocalDate.parse("1923-11-12"), 1, LocalDate.parse("2010-02-03")),
  ("叉烧包", LocalDate.parse("1923-11-12"), 2, LocalDate.parse("2012-04-05")),
  ("叉烧包", LocalDate.parse("1923-11-12"), 3, LocalDate.parse("2012-05-06"))
)

FlatJoin.flatMapForGroupBy2

Using non-trivial queries in the for-comprehension may result in subqueries being generated

for {
  (name, dateOfBirth) <- Buyer.select.groupBy(_.name)(_.minBy(_.dateOfBirth))
  (shippingInfoId, shippingDate) <- ShippingInfo.select
    .groupBy(_.id)(_.minBy(_.shippingDate))
    .crossJoin()
} yield (name, dateOfBirth, shippingInfoId, shippingDate)

SELECT
  subquery0.res_0 AS res_0,
  subquery0.res_1 AS res_1,
  subquery1.res_0 AS res_2,
  subquery1.res_1 AS res_3
FROM (SELECT
    buyer0.name AS res_0,
    MIN(buyer0.date_of_birth) AS res_1
  FROM buyer buyer0
  GROUP BY buyer0.name) subquery0
CROSS JOIN (SELECT
    shipping_info1.id AS res_0,
    MIN(shipping_info1.shipping_date) AS res_1
  FROM shipping_info shipping_info1
  GROUP BY shipping_info1.id) subquery1

Seq(
  ("James Bond", LocalDate.parse("2001-02-03"), 1, LocalDate.parse("2010-02-03")),
  ("James Bond", LocalDate.parse("2001-02-03"), 2, LocalDate.parse("2012-04-05")),
  ("James Bond", LocalDate.parse("2001-02-03"), 3, LocalDate.parse("2012-05-06")),
  ("Li Haoyi", LocalDate.parse("1965-08-09"), 1, LocalDate.parse("2010-02-03")),
  ("Li Haoyi", LocalDate.parse("1965-08-09"), 2, LocalDate.parse("2012-04-05")),
  ("Li Haoyi", LocalDate.parse("1965-08-09"), 3, LocalDate.parse("2012-05-06")),
  ("叉烧包", LocalDate.parse("1923-11-12"), 1, LocalDate.parse("2010-02-03")),
  ("叉烧包", LocalDate.parse("1923-11-12"), 2, LocalDate.parse("2012-04-05")),
  ("叉烧包", LocalDate.parse("1923-11-12"), 3, LocalDate.parse("2012-05-06"))
)

FlatJoin.flatMapForCompound

Using non-trivial queries in the for-comprehension may result in subqueries being generated

for {
  b <- Buyer.select.sortBy(_.id).asc.take(1)
  si <- ShippingInfo.select.sortBy(_.id).asc.take(1).crossJoin()
} yield (b.name, si.shippingDate)

SELECT
  subquery0.name AS res_0,
  subquery1.shipping_date AS res_1
FROM
  (SELECT buyer0.id AS id, buyer0.name AS name
  FROM buyer buyer0
  ORDER BY id ASC
  LIMIT ?) subquery0
CROSS JOIN (SELECT
    shipping_info1.id AS id,
    shipping_info1.shipping_date AS shipping_date
  FROM shipping_info shipping_info1
  ORDER BY id ASC
  LIMIT ?) subquery1

Seq(
  ("James Bond", LocalDate.parse("2010-02-03"))
)

Insert

Basic INSERT operations

Insert.single.values

Table.insert.values with a single value inserts a single row into the given table

Buyer.insert.values(
  Buyer[Sc](4, "test buyer", LocalDate.parse("2023-09-09"))
)

INSERT INTO buyer (id, name, date_of_birth) VALUES (?, ?, ?)

```
1
```

Buyer.select.filter(_.name `=` "test buyer")

Seq(Buyer[Sc](4, "test buyer", LocalDate.parse("2023-09-09")))

Insert.single.skipped

You can pass in one or more columns to .skipColumns to avoid inserting them. This is useful for columns where you want to rely on the value being auto-generated by the database.

Buyer.insert
  .values(
    Buyer[Sc](-1, "test buyer", LocalDate.parse("2023-09-09"))
  )
  .skipColumns(_.id)

INSERT INTO buyer (name, date_of_birth) VALUES (?, ?)

```
1
```

Buyer.select.filter(_.name `=` "test buyer")

Seq(Buyer[Sc](4, "test buyer", LocalDate.parse("2023-09-09")))

Insert.single.columns

Table.insert.columns inserts a single row into the given table, with the specified columns assigned to the given values, and any non-specified columns left NULL or assigned to their default values

Buyer.insert.columns(
  _.name := "test buyer",
  _.dateOfBirth := LocalDate.parse("2023-09-09"),
  _.id := 4
)

INSERT INTO buyer (name, date_of_birth, id) VALUES (?, ?, ?)

```
1
```

Buyer.select.filter(_.name `=` "test buyer")

Seq(Buyer[Sc](4, "test buyer", LocalDate.parse("2023-09-09")))

Insert.single.partial

Buyer.insert
  .columns(_.name := "test buyer", _.dateOfBirth := LocalDate.parse("2023-09-09"))

INSERT INTO buyer (name, date_of_birth) VALUES (?, ?)

```
1
```

Buyer.select.filter(_.name `=` "test buyer")

Seq(Buyer[Sc](4, "test buyer", LocalDate.parse("2023-09-09")))

Insert.batch.values

You can insert multiple rows at once by passing them to Buyer.insert.values

Buyer.insert.values(
  Buyer[Sc](4, "test buyer A", LocalDate.parse("2001-04-07")),
  Buyer[Sc](5, "test buyer B", LocalDate.parse("2002-05-08")),
  Buyer[Sc](6, "test buyer C", LocalDate.parse("2003-06-09"))
)

INSERT INTO buyer (id, name, date_of_birth)
VALUES (?, ?, ?), (?, ?, ?), (?, ?, ?)

```
3
```

Buyer.select

Seq(
  Buyer[Sc](1, "James Bond", LocalDate.parse("2001-02-03")),
  Buyer[Sc](2, "叉烧包", LocalDate.parse("1923-11-12")),
  Buyer[Sc](3, "Li Haoyi", LocalDate.parse("1965-08-09")),
  // id=4,5,6 comes from auto increment
  Buyer[Sc](4, "test buyer A", LocalDate.parse("2001-04-07")),
  Buyer[Sc](5, "test buyer B", LocalDate.parse("2002-05-08")),
  Buyer[Sc](6, "test buyer C", LocalDate.parse("2003-06-09"))
)

Insert.batch.partial

Buyer.insert.batched(_.name, _.dateOfBirth)(
  ("test buyer A", LocalDate.parse("2001-04-07")),
  ("test buyer B", LocalDate.parse("2002-05-08")),
  ("test buyer C", LocalDate.parse("2003-06-09"))
)

INSERT INTO buyer (name, date_of_birth)
VALUES (?, ?), (?, ?), (?, ?)

```
3
```

Buyer.select

Seq(
  Buyer[Sc](1, "James Bond", LocalDate.parse("2001-02-03")),
  Buyer[Sc](2, "叉烧包", LocalDate.parse("1923-11-12")),
  Buyer[Sc](3, "Li Haoyi", LocalDate.parse("1965-08-09")),
  // id=4,5,6 comes from auto increment
  Buyer[Sc](4, "test buyer A", LocalDate.parse("2001-04-07")),
  Buyer[Sc](5, "test buyer B", LocalDate.parse("2002-05-08")),
  Buyer[Sc](6, "test buyer C", LocalDate.parse("2003-06-09"))
)

Insert.select.caseclass

Table.insert.select inserts rows into the given table based on the given Table.select clause, and translates directly into SQL's INSERT INTO ... SELECT syntax.

Buyer.insert.select(
  identity,
  Buyer.select
    .filter(_.name <> "Li Haoyi")
    .map(b => b.copy(id = b.id + Buyer.select.maxBy(_.id)))
)

INSERT INTO buyer (id, name, date_of_birth)
SELECT
  (buyer0.id + (SELECT MAX(buyer1.id) AS res FROM buyer buyer1)) AS id,
  buyer0.name AS name,
  buyer0.date_of_birth AS date_of_birth
FROM buyer buyer0
WHERE (buyer0.name <> ?)

```
2
```

Buyer.select

Seq(
  Buyer[Sc](1, "James Bond", LocalDate.parse("2001-02-03")),
  Buyer[Sc](2, "叉烧包", LocalDate.parse("1923-11-12")),
  Buyer[Sc](3, "Li Haoyi", LocalDate.parse("1965-08-09")),
  Buyer[Sc](4, "James Bond", LocalDate.parse("2001-02-03")),
  Buyer[Sc](5, "叉烧包", LocalDate.parse("1923-11-12"))
)

Insert.select.simple

Buyer.insert.select(
  x => (x.name, x.dateOfBirth),
  Buyer.select.map(x => (x.name, x.dateOfBirth)).filter(_._1 <> "Li Haoyi")
)

INSERT INTO buyer (name, date_of_birth)
SELECT buyer0.name AS res_0, buyer0.date_of_birth AS res_1
FROM buyer buyer0
WHERE (buyer0.name <> ?)

```
2
```

Buyer.select

Seq(
  Buyer[Sc](1, "James Bond", LocalDate.parse("2001-02-03")),
  Buyer[Sc](2, "叉烧包", LocalDate.parse("1923-11-12")),
  Buyer[Sc](3, "Li Haoyi", LocalDate.parse("1965-08-09")),
  // id=4,5 comes from auto increment, 6 is filtered out in the select
  Buyer[Sc](4, "James Bond", LocalDate.parse("2001-02-03")),
  Buyer[Sc](5, "叉烧包", LocalDate.parse("1923-11-12"))
)

Update

Basic UPDATE queries

Update.update

Table.update takes a predicate specifying the rows to update, and a .set clause that allows you to specify the values assigned to columns on those rows

Buyer
  .update(_.name `=` "James Bond")
  .set(_.dateOfBirth := LocalDate.parse("2019-04-07"))

UPDATE buyer SET date_of_birth = ? WHERE (buyer.name = ?)

```
1
```

Buyer.select.filter(_.name `=` "James Bond").map(_.dateOfBirth).single

```
LocalDate.parse("2019-04-07")
```

Buyer.select.filter(_.name `=` "Li Haoyi").map(_.dateOfBirth).single

LocalDate.parse("1965-08-09" /* not updated */ )

Update.bulk

The predicate to Table.update is mandatory, to avoid anyone forgetting to provide one and accidentally bulk-updating all rows in their table. If you really do want to update all rows in the table, you can provide the predicate _ => true

Buyer.update(_ => true).set(_.dateOfBirth := LocalDate.parse("2019-04-07"))

```
UPDATE buyer SET date_of_birth = ?
```
```
3
```

Buyer.select.filter(_.name `=` "James Bond").map(_.dateOfBirth).single

```
LocalDate.parse("2019-04-07")
```

Buyer.select.filter(_.name `=` "Li Haoyi").map(_.dateOfBirth).single

```
LocalDate.parse("2019-04-07")
```

Update.multiple

This example shows how to update multiple columns in a single Table.update call

Buyer
  .update(_.name `=` "James Bond")
  .set(_.dateOfBirth := LocalDate.parse("2019-04-07"), _.name := "John Dee")

UPDATE buyer SET date_of_birth = ?, name = ? WHERE (buyer.name = ?)

```
1
```

Buyer.select.filter(_.name `=` "James Bond").map(_.dateOfBirth)

Seq[LocalDate]( /* not found due to rename */ )

Buyer.select.filter(_.name `=` "John Dee").map(_.dateOfBirth)

```
Seq(LocalDate.parse("2019-04-07"))
```

Update.dynamic

The values assigned to columns in Table.update can also be computed Expr[T]s, not just literal Scala constants. This example shows how to to update the name of the row for James Bond with it's existing name in uppercase

Buyer.update(_.name `=` "James Bond").set(c => c.name := c.name.toUpperCase)

UPDATE buyer SET name = UPPER(buyer.name) WHERE (buyer.name = ?)

```
1
```

Buyer.select.filter(_.name `=` "James Bond").map(_.dateOfBirth)

Seq[LocalDate]( /* not found due to rename */ )

Buyer.select.filter(_.name `=` "JAMES BOND").map(_.dateOfBirth)

```
Seq(LocalDate.parse("2001-02-03"))
```

Delete

Basic DELETE operations

Delete.single

Table.delete takes a mandatory predicate specifying what rows you want to delete. The most common case is to specify the ID of the row you want to delete

Purchase.delete(_.id `=` 2)

DELETE FROM purchase WHERE (purchase.id = ?)

```
1
```

Purchase.select

Seq(
  Purchase[Sc](id = 1, shippingInfoId = 1, productId = 1, count = 100, total = 888.0),
  // id==2 got deleted
  Purchase[Sc](id = 3, shippingInfoId = 1, productId = 3, count = 5, total = 15.7),
  Purchase[Sc](id = 4, shippingInfoId = 2, productId = 4, count = 4, total = 493.8),
  Purchase[Sc](id = 5, shippingInfoId = 2, productId = 5, count = 10, total = 10000.0),
  Purchase[Sc](id = 6, shippingInfoId = 3, productId = 1, count = 5, total = 44.4),
  Purchase[Sc](id = 7, shippingInfoId = 3, productId = 6, count = 13, total = 1.3)
)

Delete.multiple

Although specifying a single ID to delete is the most common case, you can pass in arbitrary predicates, e.g. in this example deleting all rows except for the one with a particular ID

Purchase.delete(_.id <> 2)

DELETE FROM purchase WHERE (purchase.id <> ?)

```
6
```

Purchase.select

Seq(Purchase[Sc](id = 2, shippingInfoId = 1, productId = 2, count = 3, total = 900.0))

Delete.all

If you actually want to delete all rows in the table, you can explicitly pass in the predicate _ => true

Purchase.delete(_ => true)

```
DELETE FROM purchase
```
```
7
```

Purchase.select

```
Seq[Purchase[Sc]](
  // all Deleted
)
```

CompoundSelect

Compound SELECT operations: sort, take, drop, union, unionAll, etc.

CompoundSelect.sort.simple

ScalaSql's .sortBy method translates into SQL ORDER BY

Product.select.sortBy(_.price).map(_.name)

SELECT product0.name AS res FROM product product0 ORDER BY product0.price

Seq("Cookie", "Socks", "Face Mask", "Skate Board", "Guitar", "Camera")

CompoundSelect.sort.twice

If you want to sort by multiple columns, you can call .sortBy multiple times, each with its own call to .asc or .desc. Note that the rightmost call to .sortBy takes precedence, following the Scala collections .sortBy semantics, and so the right-most .sortBy in ScalaSql becomes the left-most entry in the SQL ORDER BY clause

Purchase.select.sortBy(_.productId).asc.sortBy(_.shippingInfoId).desc

SELECT
  purchase0.id AS id,
  purchase0.shipping_info_id AS shipping_info_id,
  purchase0.product_id AS product_id,
  purchase0.count AS count,
  purchase0.total AS total
FROM purchase purchase0
ORDER BY shipping_info_id DESC, product_id ASC

Seq(
  Purchase[Sc](6, 3, 1, 5, 44.4),
  Purchase[Sc](7, 3, 6, 13, 1.3),
  Purchase[Sc](4, 2, 4, 4, 493.8),
  Purchase[Sc](5, 2, 5, 10, 10000.0),
  Purchase[Sc](1, 1, 1, 100, 888.0),
  Purchase[Sc](2, 1, 2, 3, 900.0),
  Purchase[Sc](3, 1, 3, 5, 15.7)
)

CompoundSelect.sort.sortLimit

ScalaSql also supports various combinations of .take and .drop, translating to SQL LIMIT or OFFSET

Product.select.sortBy(_.price).map(_.name).take(2)

SELECT product0.name AS res FROM product product0 ORDER BY product0.price LIMIT ?

```
Seq("Cookie", "Socks")
```

CompoundSelect.sort.sortOffset

Product.select.sortBy(_.price).map(_.name).drop(2)

SELECT product0.name AS res FROM product product0 ORDER BY product0.price OFFSET ?

Seq("Face Mask", "Skate Board", "Guitar", "Camera")

CompoundSelect.sort.sortLimitTwiceHigher

Note that .drop and .take follow Scala collections' semantics, so calling e.g. .take multiple times takes the value of the smallest .take, while calling .drop multiple times accumulates the total amount dropped

Product.select.sortBy(_.price).map(_.name).take(2).take(3)

SELECT product0.name AS res FROM product product0 ORDER BY product0.price LIMIT ?

```
Seq("Cookie", "Socks")
```

CompoundSelect.sort.sortLimitTwiceLower

Product.select.sortBy(_.price).map(_.name).take(2).take(1)

SELECT product0.name AS res FROM product product0 ORDER BY product0.price LIMIT ?

```
Seq("Cookie")
```

CompoundSelect.sort.sortLimitOffset

Product.select.sortBy(_.price).map(_.name).drop(2).take(2)

SELECT product0.name AS res FROM product product0 ORDER BY product0.price LIMIT ? OFFSET ?

```
Seq("Face Mask", "Skate Board")
```

CompoundSelect.sort.sortLimitOffsetTwice

Product.select.sortBy(_.price).map(_.name).drop(2).drop(2).take(1)

SELECT product0.name AS res FROM product product0 ORDER BY product0.price LIMIT ? OFFSET ?

```
Seq("Guitar")
```

CompoundSelect.sort.sortOffsetLimit

Product.select.sortBy(_.price).map(_.name).drop(2).take(2)

SELECT product0.name AS res FROM product product0 ORDER BY product0.price LIMIT ? OFFSET ?

```
Seq("Face Mask", "Skate Board")
```

CompoundSelect.distinct

ScalaSql's .distinct translates to SQL's SELECT DISTINCT

Purchase.select.sortBy(_.total).desc.take(3).map(_.shippingInfoId).distinct

SELECT DISTINCT subquery0.res AS res
FROM (SELECT purchase0.shipping_info_id AS res
  FROM purchase purchase0
  ORDER BY purchase0.total DESC
  LIMIT ?) subquery0

```
Seq(1, 2)
```

CompoundSelect.flatMap

Many operations in SQL cannot be done in certain orders, unless you move part of the logic into a subquery. ScalaSql does this automatically for you, e.g. doing a flatMap, .sumBy, or .aggregate after a .sortBy/.take, the LHS .sortBy/.take is automatically extracted into a subquery

Purchase.select.sortBy(_.total).desc.take(3).flatMap { p =>
  Product.crossJoin().filter(_.id === p.productId).map(_.name)
}

SELECT product1.name AS res
FROM (SELECT purchase0.product_id AS product_id, purchase0.total AS total
  FROM purchase purchase0
  ORDER BY total DESC
  LIMIT ?) subquery0
CROSS JOIN product product1
WHERE (product1.id = subquery0.product_id)

```
Seq("Camera", "Face Mask", "Guitar")
```

CompoundSelect.sumBy

Purchase.select.sortBy(_.total).desc.take(3).sumBy(_.total)

SELECT SUM(subquery0.total) AS res
FROM (SELECT purchase0.total AS total
  FROM purchase purchase0
  ORDER BY total DESC
  LIMIT ?) subquery0

```
11788.0
```

CompoundSelect.aggregate

Purchase.select
  .sortBy(_.total)
  .desc
  .take(3)
  .aggregate(p => (p.sumBy(_.total), p.avgBy(_.total)))

SELECT SUM(subquery0.total) AS res_0, AVG(subquery0.total) AS res_1
FROM (SELECT purchase0.total AS total
  FROM purchase purchase0
  ORDER BY total DESC
  LIMIT ?) subquery0

```
(11788.0, 3929.0)
```

CompoundSelect.union

ScalaSql's .union/.unionAll/.intersect/.except translate into SQL's UNION/UNION ALL/INTERSECT/EXCEPT.

Product.select
  .map(_.name.toLowerCase)
  .union(Product.select.map(_.kebabCaseName.toLowerCase))

SELECT LOWER(product0.name) AS res
FROM product product0
UNION
SELECT LOWER(product0.kebab_case_name) AS res
FROM product product0

Seq(
  "camera",
  "cookie",
  "face mask",
  "face-mask",
  "guitar",
  "skate board",
  "skate-board",
  "socks"
)

CompoundSelect.unionAll

Product.select
  .map(_.name.toLowerCase)
  .unionAll(Product.select.map(_.kebabCaseName.toLowerCase))

SELECT LOWER(product0.name) AS res
FROM product product0
UNION ALL
SELECT LOWER(product0.kebab_case_name) AS res
FROM product product0

Seq(
  "face mask",
  "guitar",
  "socks",
  "skate board",
  "camera",
  "cookie",
  "face-mask",
  "guitar",
  "socks",
  "skate-board",
  "camera",
  "cookie"
)

CompoundSelect.intersect

Product.select
  .map(_.name.toLowerCase)
  .intersect(Product.select.map(_.kebabCaseName.toLowerCase))

SELECT LOWER(product0.name) AS res
FROM product product0
INTERSECT
SELECT LOWER(product0.kebab_case_name) AS res
FROM product product0

Seq("camera", "cookie", "guitar", "socks")

CompoundSelect.except

Product.select
  .map(_.name.toLowerCase)
  .except(Product.select.map(_.kebabCaseName.toLowerCase))

SELECT LOWER(product0.name) AS res
FROM product product0
EXCEPT
SELECT LOWER(product0.kebab_case_name) AS res
FROM product product0

```
Seq("face mask", "skate board")
```

CompoundSelect.unionAllUnionSort

Performing a .sortBy after .union or .unionAll applies the sort to both sides of the union/unionAll, behaving identically to Scala or SQL

Product.select
  .map(_.name.toLowerCase)
  .unionAll(Buyer.select.map(_.name.toLowerCase))
  .union(Product.select.map(_.kebabCaseName.toLowerCase))
  .sortBy(identity)

SELECT LOWER(product0.name) AS res
FROM product product0
UNION ALL
SELECT LOWER(buyer0.name) AS res
FROM buyer buyer0
UNION
SELECT LOWER(product0.kebab_case_name) AS res
FROM product product0
ORDER BY res

Seq(
  "camera",
  "cookie",
  "face mask",
  "face-mask",
  "guitar",
  "james bond",
  "li haoyi",
  "skate board",
  "skate-board",
  "socks",
  "叉烧包"
)

CompoundSelect.unionAllUnionSortLimit

Product.select
  .map(_.name.toLowerCase)
  .unionAll(Buyer.select.map(_.name.toLowerCase))
  .union(Product.select.map(_.kebabCaseName.toLowerCase))
  .sortBy(identity)
  .drop(4)
  .take(4)

SELECT LOWER(product0.name) AS res
FROM product product0
UNION ALL
SELECT LOWER(buyer0.name) AS res
FROM buyer buyer0
UNION
SELECT LOWER(product0.kebab_case_name) AS res
FROM product product0
ORDER BY res
LIMIT ?
OFFSET ?

Seq("guitar", "james bond", "li haoyi", "skate board")

UpdateJoin

Basic UPDATE queries

UpdateJoin.join

ScalaSql supports performing UPDATEs with FROM/JOIN clauses using the .update.join methods

Buyer
  .update(_.name `=` "James Bond")
  .join(ShippingInfo)(_.id `=` _.buyerId)
  .set(c => c._1.dateOfBirth := c._2.shippingDate)

UPDATE buyer
SET date_of_birth = shipping_info0.shipping_date
FROM shipping_info shipping_info0
WHERE (buyer.id = shipping_info0.buyer_id) AND (buyer.name = ?)

```
1
```

Buyer.select.filter(_.name `=` "James Bond").map(_.dateOfBirth)

```
Seq(LocalDate.parse("2012-04-05"))
```

UpdateJoin.multijoin

Multiple joins are supported, e.g. the below example where we join the Buyer table three times against ShippingInfo/Purchase/Product to determine what to update

Buyer
  .update(_.name `=` "James Bond")
  .join(ShippingInfo)(_.id `=` _.buyerId)
  .join(Purchase)(_._2.id `=` _.shippingInfoId)
  .join(Product)(_._3.productId `=` _.id)
  .filter(t => t._4.name.toLowerCase `=` t._4.kebabCaseName.toLowerCase)
  .set(c => c._1.name := c._4.name)

UPDATE buyer
SET name = product2.name
FROM shipping_info shipping_info0
JOIN purchase purchase1 ON (shipping_info0.id = purchase1.shipping_info_id)
JOIN product product2 ON (purchase1.product_id = product2.id)
WHERE (buyer.id = shipping_info0.buyer_id)
AND (buyer.name = ?)
AND (LOWER(product2.name) = LOWER(product2.kebab_case_name))

```
1
```

Buyer.select.filter(_.id `=` 1).map(_.name)

```
Seq("Camera")
```

UpdateJoin.joinSubquery

In addition to JOINing against another table, you can also perform JOINs against subqueries by passing in a .select query to .join

Buyer
  .update(_.name `=` "James Bond")
  .join(ShippingInfo.select.sortBy(_.id).asc.take(2))(_.id `=` _.buyerId)
  .set(c => c._1.dateOfBirth := c._2.shippingDate)

UPDATE buyer SET date_of_birth = subquery0.shipping_date
FROM (SELECT
    shipping_info0.id AS id,
    shipping_info0.buyer_id AS buyer_id,
    shipping_info0.shipping_date AS shipping_date
  FROM shipping_info shipping_info0
  ORDER BY id ASC
  LIMIT ?) subquery0
WHERE (buyer.id = subquery0.buyer_id) AND (buyer.name = ?)

```
1
```

Buyer.select.filter(_.name `=` "James Bond").map(_.dateOfBirth)

```
Seq(LocalDate.parse("2012-04-05"))
```

UpdateJoin.joinSubqueryEliminatedColumn

Buyer
  .update(_.name `=` "James Bond")
  // Make sure the `SELECT shipping_info0.shipping_info_id AS shipping_info_id`
  // column gets eliminated since it is not used outside the subquery
  .join(ShippingInfo.select.sortBy(_.id).asc.take(2))(_.id `=` _.buyerId)
  .set(c => c._1.dateOfBirth := LocalDate.parse("2000-01-01"))

UPDATE buyer SET date_of_birth = ?
FROM (SELECT
    shipping_info0.id AS id,
    shipping_info0.buyer_id AS buyer_id
  FROM shipping_info shipping_info0
  ORDER BY id ASC
  LIMIT ?) subquery0
WHERE (buyer.id = subquery0.buyer_id) AND (buyer.name = ?)

```
1
```

Buyer.select.filter(_.name `=` "James Bond").map(_.dateOfBirth)

```
Seq(LocalDate.parse("2000-01-01"))
```

UpdateSubQuery

UPDATE queries that use Subqueries

UpdateSubQuery.setSubquery

You can use subqueries to compute the values you want to update, using aggregates like .maxBy to convert the Select[T] into an Expr[T]

Product.update(_ => true).set(_.price := Product.select.maxBy(_.price))

UPDATE product
SET price = (SELECT MAX(product1.price) AS res FROM product product1)

```
6
```

Product.select.map(p => (p.id, p.name, p.price))

Seq(
  (1, "Face Mask", 1000.0),
  (2, "Guitar", 1000.0),
  (3, "Socks", 1000.0),
  (4, "Skate Board", 1000.0),
  (5, "Camera", 1000.0),
  (6, "Cookie", 1000.0)
)

UpdateSubQuery.whereSubquery

Subqueries and aggregates can also be used in the WHERE clause, defined by the predicate passed to `Table.update

Product.update(_.price `=` Product.select.maxBy(_.price)).set(_.price := 0)

UPDATE product
SET price = ?
WHERE (product.price = (SELECT MAX(product1.price) AS res FROM product product1))

```
1
```

Product.select.map(p => (p.id, p.name, p.price))

Seq(
  (1, "Face Mask", 8.88),
  (2, "Guitar", 300.0),
  (3, "Socks", 3.14),
  (4, "Skate Board", 123.45),
  (5, "Camera", 0.0),
  (6, "Cookie", 0.1)
)

Returning

Queries using INSERT or UPDATE with RETURNING

Returning.insert.single

ScalaSql's .returning clause translates to SQL's RETURNING syntax, letting you perform insertions or updates and return values from the query (rather than returning a single integer representing the rows affected). This is especially useful for retrieving the auto-generated table IDs that many databases support.

Note that .returning/RETURNING is not supported in MySql, H2 or HsqlDB

Buyer.insert
  .columns(_.name := "test buyer", _.dateOfBirth := LocalDate.parse("2023-09-09"))
  .returning(_.id)

INSERT INTO buyer (name, date_of_birth) VALUES (?, ?) RETURNING buyer.id AS res

```
Seq(4)
```

Buyer.select.filter(_.name `=` "test buyer")

Seq(Buyer[Sc](4, "test buyer", LocalDate.parse("2023-09-09")))

Returning.insert.dotSingle

If your .returning query is expected to be a single row, the .single method is supported to convert the returned Seq[T] into a single T. .single throws an exception if zero or multiple rows are returned.

Buyer.insert
  .columns(_.name := "test buyer", _.dateOfBirth := LocalDate.parse("2023-09-09"))
  .returning(_.id)
  .single

INSERT INTO buyer (name, date_of_birth) VALUES (?, ?) RETURNING buyer.id AS res

```
4
```

Buyer.select.filter(_.name `=` "test buyer")

Seq(Buyer[Sc](4, "test buyer", LocalDate.parse("2023-09-09")))

Returning.insert.multiple

Buyer.insert
  .batched(_.name, _.dateOfBirth)(
    ("test buyer A", LocalDate.parse("2001-04-07")),
    ("test buyer B", LocalDate.parse("2002-05-08")),
    ("test buyer C", LocalDate.parse("2003-06-09"))
  )
  .returning(_.id)

INSERT INTO buyer (name, date_of_birth)
VALUES
  (?, ?),
  (?, ?),
  (?, ?)
RETURNING buyer.id AS res

```
Seq(4, 5, 6)
```

Buyer.select

Seq(
  Buyer[Sc](1, "James Bond", LocalDate.parse("2001-02-03")),
  Buyer[Sc](2, "叉烧包", LocalDate.parse("1923-11-12")),
  Buyer[Sc](3, "Li Haoyi", LocalDate.parse("1965-08-09")),
  // id=4,5,6 comes from auto increment
  Buyer[Sc](4, "test buyer A", LocalDate.parse("2001-04-07")),
  Buyer[Sc](5, "test buyer B", LocalDate.parse("2002-05-08")),
  Buyer[Sc](6, "test buyer C", LocalDate.parse("2003-06-09"))
)

Returning.insert.select

All variants of .insert and .update support .returning, e.g. the example below applies to .insert.select, and the examples further down demonstrate its usage with .update and .delete

Buyer.insert
  .select(
    x => (x.name, x.dateOfBirth),
    Buyer.select.map(x => (x.name, x.dateOfBirth)).filter(_._1 <> "Li Haoyi")
  )
  .returning(_.id)

INSERT INTO buyer (name, date_of_birth)
SELECT
  buyer1.name AS res_0,
  buyer1.date_of_birth AS res_1
FROM buyer buyer1
WHERE (buyer1.name <> ?)
RETURNING buyer.id AS res

```
Seq(4, 5)
```

Buyer.select

Seq(
  Buyer[Sc](1, "James Bond", LocalDate.parse("2001-02-03")),
  Buyer[Sc](2, "叉烧包", LocalDate.parse("1923-11-12")),
  Buyer[Sc](3, "Li Haoyi", LocalDate.parse("1965-08-09")),
  // id=4,5 comes from auto increment, 6 is filtered out in the select
  Buyer[Sc](4, "James Bond", LocalDate.parse("2001-02-03")),
  Buyer[Sc](5, "叉烧包", LocalDate.parse("1923-11-12"))
)

Returning.update.single

Buyer
  .update(_.name `=` "James Bond")
  .set(_.dateOfBirth := LocalDate.parse("2019-04-07"))
  .returning(_.id)

UPDATE buyer SET date_of_birth = ? WHERE (buyer.name = ?) RETURNING buyer.id AS res

```
Seq(1)
```

Buyer.select.filter(_.name `=` "James Bond").map(_.dateOfBirth)

```
Seq(LocalDate.parse("2019-04-07"))
```

Returning.update.multiple

Buyer
  .update(_.name `=` "James Bond")
  .set(_.dateOfBirth := LocalDate.parse("2019-04-07"), _.name := "John Dee")
  .returning(c => (c.id, c.name, c.dateOfBirth))

UPDATE buyer
SET date_of_birth = ?, name = ? WHERE (buyer.name = ?)
RETURNING buyer.id AS res_0, buyer.name AS res_1, buyer.date_of_birth AS res_2

Seq((1, "John Dee", LocalDate.parse("2019-04-07")))

Returning.delete

Purchase.delete(_.shippingInfoId `=` 1).returning(_.total)

DELETE FROM purchase WHERE (purchase.shipping_info_id = ?) RETURNING purchase.total AS res

```
Seq(888.0, 900.0, 15.7)
```

Purchase.select

Seq(
  // id=1,2,3 had shippingInfoId=1 and thus got deleted
  Purchase[Sc](id = 4, shippingInfoId = 2, productId = 4, count = 4, total = 493.8),
  Purchase[Sc](id = 5, shippingInfoId = 2, productId = 5, count = 10, total = 10000.0),
  Purchase[Sc](id = 6, shippingInfoId = 3, productId = 1, count = 5, total = 44.4),
  Purchase[Sc](id = 7, shippingInfoId = 3, productId = 6, count = 13, total = 1.3)
)

OnConflict

Queries using ON CONFLICT DO UPDATE or ON CONFLICT DO NOTHING

OnConflict.ignore

ScalaSql's .onConflictIgnore translates into SQL's ON CONFLICT DO NOTHING

Note that H2 and HsqlExpr do not support onConflictIgnore and onConflictUpdate, while MySql only supports onConflictUpdate but not onConflictIgnore.

Buyer.insert
  .columns(
    _.name := "test buyer",
    _.dateOfBirth := LocalDate.parse("2023-09-09"),
    _.id := 1 // This should cause a primary key conflict
  )
  .onConflictIgnore(_.id)

INSERT INTO buyer (name, date_of_birth, id) VALUES (?, ?, ?) ON CONFLICT (id) DO NOTHING

```
0
```

with insert.values

Buyer.insert
  .values(
    Buyer[Sc](
      id = 1,
      name = "test buyer",
      dateOfBirth = LocalDate.parse("2023-09-09")
    )
  )
  .onConflictIgnore(_.id)

INSERT INTO buyer (id, name, date_of_birth) VALUES (?, ?, ?) ON CONFLICT (id) DO NOTHING

```
0
```

with insert.select

Buyer.insert
  .select(
    identity,
    Buyer.select
      .filter(_.id === 1)
      .map(b => b.copy(name = b.name + "."))
  )
  .onConflictIgnore(_.id)

INSERT INTO
            buyer (id, name, date_of_birth)
          SELECT
            buyer0.id AS id,
            (buyer0.name || ?) AS name,
            buyer0.date_of_birth AS date_of_birth
          FROM
            buyer buyer0
          WHERE
            (buyer0.id = ?) ON CONFLICT (id) DO NOTHING

```
0
```

OnConflict.ignore.returningEmpty

Buyer.insert
  .columns(
    _.name := "test buyer",
    _.dateOfBirth := LocalDate.parse("2023-09-09"),
    _.id := 1 // This should cause a primary key conflict
  )
  .onConflictIgnore(_.id)
  .returning(_.name)

INSERT INTO buyer (name, date_of_birth, id) VALUES (?, ?, ?)
ON CONFLICT (id) DO NOTHING
RETURNING buyer.name AS res

```
Seq.empty[String]
```

with insert.values

Buyer.insert
  .values(
    Buyer[Sc](
      id = 1,
      name = "test buyer",
      dateOfBirth = LocalDate.parse("2023-09-09")
    )
  )
  .onConflictIgnore(_.id)
  .returning(_.name)

INSERT INTO buyer (id, name, date_of_birth) VALUES (?, ?, ?)
ON CONFLICT (id) DO NOTHING
RETURNING buyer.name AS res

```
Seq.empty[String]
```

OnConflict.ignore.returningOne

Buyer.insert
  .columns(
    _.name := "test buyer",
    _.dateOfBirth := LocalDate.parse("2023-09-09"),
    _.id := 4
  )
  .onConflictIgnore(_.id)
  .returning(_.name)

INSERT INTO buyer (name, date_of_birth, id) VALUES (?, ?, ?)
ON CONFLICT (id) DO NOTHING
RETURNING buyer.name AS res

```
Seq("test buyer")
```

with insert.values

Buyer.insert
  .values(
    Buyer[Sc](
      id = 5,
      name = "test buyer",
      dateOfBirth = LocalDate.parse("2023-09-09")
    )
  )
  .onConflictIgnore(_.id)
  .returning(_.name)

INSERT INTO buyer (id, name, date_of_birth) VALUES (?, ?, ?)
ON CONFLICT (id) DO NOTHING
RETURNING buyer.name AS res

```
Seq("test buyer")
```

OnConflict.update

ScalaSql's .onConflictUpdate translates into SQL's ON CONFLICT DO UPDATE

Buyer.insert
  .columns(
    _.name := "test buyer",
    _.dateOfBirth := LocalDate.parse("2023-09-09"),
    _.id := 1 // This should cause a primary key conflict
  )
  .onConflictUpdate(_.id)(_.name := "TEST BUYER CONFLICT")

INSERT INTO buyer (name, date_of_birth, id) VALUES (?, ?, ?) ON CONFLICT (id) DO UPDATE SET name = ?

```
1
```

with insert.values

Buyer.insert
  .values(
    Buyer[Sc](
      id = 1,
      name = "test buyer",
      dateOfBirth = LocalDate.parse("2023-09-09")
    )
  )
  .onConflictUpdate(_.id)(_.dateOfBirth := LocalDate.parse("2023-10-10"))

INSERT INTO buyer (id, name, date_of_birth) VALUES (?, ?, ?) ON CONFLICT (id) DO UPDATE SET date_of_birth = ?

```
1
```

with insert.select

Buyer.insert
  .select(
    identity,
    Buyer.select
      .filter(_.id === 1)
      .map(b => b.copy(name = b.name + "."))
  )
  .onConflictUpdate(_.id)(_.dateOfBirth := LocalDate.parse("2023-10-09"))

INSERT INTO
            buyer (id, name, date_of_birth)
          SELECT
            buyer1.id AS id,
            (buyer1.name || ?) AS name,
            buyer1.date_of_birth AS date_of_birth
          FROM
            buyer buyer1 
          WHERE
            (buyer1.id = ?) ON CONFLICT (id) DO 
          UPDATE
          SET date_of_birth = ?

```
1
```

Buyer.select

Seq(
  Buyer[Sc](1, "TEST BUYER CONFLICT", LocalDate.parse("2023-10-09")),
  Buyer[Sc](2, "叉烧包", LocalDate.parse("1923-11-12")),
  Buyer[Sc](3, "Li Haoyi", LocalDate.parse("1965-08-09"))
)

OnConflict.computed

Buyer.insert
  .columns(
    _.name := "test buyer",
    _.dateOfBirth := LocalDate.parse("2023-09-09"),
    _.id := 1 // This should cause a primary key conflict
  )
  .onConflictUpdate(_.id)(v => v.name := v.name.toUpperCase)

INSERT INTO buyer (name, date_of_birth, id) VALUES (?, ?, ?) ON CONFLICT (id) DO UPDATE SET name = UPPER(buyer.name)

```
1
```

with insert.values

Buyer.insert
  .values(
    Buyer[Sc](
      id = 3,
      name = "test buyer",
      dateOfBirth = LocalDate.parse("2023-09-09")
    )
  )
  .onConflictUpdate(_.id)(v => v.name := v.name.toUpperCase)

INSERT INTO buyer (id, name, date_of_birth) VALUES (?, ?, ?) ON CONFLICT (id) DO UPDATE SET name = UPPER(buyer.name)

```
1
```

Buyer.select

Seq(
  Buyer[Sc](1, "JAMES BOND", LocalDate.parse("2001-02-03")),
  Buyer[Sc](2, "叉烧包", LocalDate.parse("1923-11-12")),
  Buyer[Sc](3, "LI HAOYI", LocalDate.parse("1965-08-09"))
)

OnConflict.returning

Buyer.insert
  .columns(
    _.name := "test buyer",
    _.dateOfBirth := LocalDate.parse("2023-09-09"),
    _.id := 1 // This should cause a primary key conflict
  )
  .onConflictUpdate(_.id)(v => v.name := v.name.toUpperCase)
  .returning(_.name)
  .single

INSERT INTO buyer (name, date_of_birth, id) VALUES (?, ?, ?)
ON CONFLICT (id) DO UPDATE
SET name = UPPER(buyer.name)
RETURNING buyer.name AS res

```
"JAMES BOND"
```

with insert.values

Buyer.insert
  .values(
    Buyer[Sc](
      id = 1,
      name = "test buyer",
      dateOfBirth = LocalDate.parse("2023-09-09")
    )
  )
  .onConflictUpdate(_.id)(v => v.name := v.name.toLowerCase)
  .returning(_.name)
  .single

INSERT INTO buyer (id, name, date_of_birth) VALUES (?, ?, ?)
ON CONFLICT (id) DO UPDATE
SET name = LOWER(buyer.name)
RETURNING buyer.name AS res

```
"james bond"
```

Values

Basic VALUES operations

Values.basic

You can use Values to generate a SQL VALUES clause

db.values(Seq(1, 2, 3))

```
VALUES (?), (?), (?)
```
```
Seq(1, 2, 3)
```

Values.contains

Values supports .contains

db.values(Seq(1, 2, 3)).contains(1)

SELECT (? IN (VALUES (?), (?), (?))) AS res

```
true
```

Values.max

Values supports aggregate functions like .max

db.values(Seq(1, 2, 3)).max

SELECT MAX(subquery0.column1) AS res FROM (VALUES (?), (?), (?)) subquery0

```
3
```

Values.map

Values supports most .select operators like .map, .filter, .crossJoin, and so on

db.values(Seq(1, 2, 3)).map(_ + 1)

SELECT (subquery0.column1 + ?) AS res FROM (VALUES (?), (?), (?)) subquery0

```
Seq(2, 3, 4)
```

Values.filter

db.values(Seq(1, 2, 3)).filter(_ > 2)

SELECT subquery0.column1 AS res FROM (VALUES (?), (?), (?)) subquery0 WHERE (subquery0.column1 > ?)

```
Seq(3)
```

Values.crossJoin

db.values(Seq(1, 2, 3)).crossJoin(db.values(Seq(4, 5, 6))).map {
  case (a, b) => (a * 10 + b)
}

SELECT ((subquery0.column1 * ?) + subquery1.column1) AS res
FROM (VALUES (?), (?), (?)) subquery0
CROSS JOIN (VALUES (?), (?), (?)) subquery1

Seq(14, 15, 16, 24, 25, 26, 34, 35, 36)

Values.joinValuesAndTable

You can also mix values calls and normal selects in the same query, e.g. with joins

for {
  name <- db.values(Seq("Socks", "Face Mask", "Camera"))
  product <- Product.join(_.name === name)
} yield (name, product.price)

SELECT subquery0.column1 AS res_0, product1.price AS res_1
FROM (VALUES (?), (?), (?)) subquery0
JOIN product product1 ON (product1.name = subquery0.column1)

Seq(("Socks", 3.14), ("Face Mask", 8.88), ("Camera", 1000.0))

Values.multiple.tuple

values supports tuples and other data structures as well

db.values(Seq((1, 2), (3, 4), (5, 6)))

```
VALUES (?, ?), (?, ?), (?, ?)
```
```
Seq((1, 2), (3, 4), (5, 6))
```

Values.multiple.caseClass

db.values(
  Seq(
    Buyer[Sc](1, "hello", LocalDate.parse("2001-02-03")),
    Buyer[Sc](2, "world", LocalDate.parse("2004-05-06"))
  )
)

```
VALUES (?, ?, ?), (?, ?, ?)
```

Seq(
  Buyer[Sc](1, "hello", LocalDate.parse("2001-02-03")),
  Buyer[Sc](2, "world", LocalDate.parse("2004-05-06"))
)

Values.multiple.map

values supports tuples and other data structures as well

db.values(Seq((1, 2), (3, 4), (5, 6))).map { case (a, b) => (a + 10, b + 100) }

SELECT (subquery0.column1 + ?) AS res_0, (subquery0.column2 + ?) AS res_1
FROM (VALUES (?, ?), (?, ?), (?, ?)) subquery0

```
Seq((11, 102), (13, 104), (15, 106))
```

Values.multiple.mapCaseClass

values supports tuples and other data structures as well

{
  val buyers = Seq(
    Buyer[Sc](1, "hello", LocalDate.parse("2001-02-03")),
    Buyer[Sc](2, "world", LocalDate.parse("2004-05-06"))
  )
  val query = db.values(buyers).map { b => (b.id + 100, b) }
  query
}

SELECT
  (subquery0.column1 + ?) AS res_0,
  subquery0.column1 AS res_1_id,
  subquery0.column2 AS res_1_name,
  subquery0.column3 AS res_1_date_of_birth
FROM (VALUES (?, ?, ?), (?, ?, ?)) subquery0

Seq(
  (101, Buyer[Sc](1, "hello", LocalDate.parse("2001-02-03"))),
  (102, Buyer[Sc](2, "world", LocalDate.parse("2004-05-06")))
)

Values.multiple.caseClassContains

You can use .contains on multi-column Scala values, which are translated to a SQL IN clause on a tuple.

{
  val buyers = Seq(
    Buyer[Sc](2, "叉烧包", LocalDate.parse("1923-11-12")),
    Buyer[Sc](3, "Li Haoyi", LocalDate.parse("1965-08-09"))
  )
  Buyer.select.filter(!db.values(buyers).contains(_))
}

SELECT
  buyer0.id AS id,
  buyer0.name AS name,
  buyer0.date_of_birth AS date_of_birth
FROM buyer buyer0
WHERE (NOT
  ((buyer0.id, buyer0.name, buyer0.date_of_birth) IN (VALUES (?, ?, ?), (?, ?, ?))))

Seq(
  Buyer[Sc](1, "James Bond", LocalDate.parse("2001-02-03"))
)

LateralJoin

`JOIN LATERAL`, for the databases that support it. This allows you to use the
expressions defined in tables on the left-hand-side of the join in a
subquery on the right-hand-side of the join, v.s. normal `JOIN`s which only
allow you to use left-hand-side expressions in the `ON` expression but not
in the `FROM` subquery.

LateralJoin.crossJoinLateral

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

SELECT buyer0.name AS res_0, subquery1.shipping_date AS res_1
FROM buyer buyer0
CROSS JOIN LATERAL (SELECT shipping_info1.shipping_date AS shipping_date
  FROM shipping_info shipping_info1
  WHERE (buyer0.id = shipping_info1.buyer_id)) subquery1

Seq(
  ("James Bond", LocalDate.parse("2012-04-05")),
  ("叉烧包", LocalDate.parse("2010-02-03")),
  ("叉烧包", LocalDate.parse("2012-05-06"))
)

LateralJoin.crossJoinLateralFor

for {
  b <- Buyer.select
  s <- ShippingInfo.select.filter { s => b.id `=` s.buyerId }.crossJoinLateral()
} yield (b.name, s.shippingDate)

SELECT buyer0.name AS res_0, subquery1.shipping_date AS res_1
FROM buyer buyer0
CROSS JOIN LATERAL (SELECT shipping_info1.shipping_date AS shipping_date
  FROM shipping_info shipping_info1
  WHERE (buyer0.id = shipping_info1.buyer_id)) subquery1

Seq(
  ("James Bond", LocalDate.parse("2012-04-05")),
  ("叉烧包", LocalDate.parse("2010-02-03")),
  ("叉烧包", LocalDate.parse("2012-05-06"))
)

LateralJoin.joinLateral

Buyer.select
  .joinLateral(b => ShippingInfo.select.filter { s => b.id `=` s.buyerId })((_, _) => true)
  .map { case (b, s) => (b.name, s.shippingDate) }

SELECT buyer0.name AS res_0, subquery1.shipping_date AS res_1
FROM buyer buyer0
JOIN LATERAL (SELECT shipping_info1.shipping_date AS shipping_date
  FROM shipping_info shipping_info1
  WHERE (buyer0.id = shipping_info1.buyer_id)) subquery1
  ON ?

Seq(
  ("James Bond", LocalDate.parse("2012-04-05")),
  ("叉烧包", LocalDate.parse("2010-02-03")),
  ("叉烧包", LocalDate.parse("2012-05-06"))
)

LateralJoin.joinLateralFor

for {
  b <- Buyer.select
  s <- ShippingInfo.select.filter { s => b.id `=` s.buyerId }.joinLateral(_ => Expr(true))
} yield (b.name, s.shippingDate)

SELECT buyer0.name AS res_0, subquery1.shipping_date AS res_1
FROM buyer buyer0
JOIN LATERAL (SELECT shipping_info1.shipping_date AS shipping_date
  FROM shipping_info shipping_info1
  WHERE (buyer0.id = shipping_info1.buyer_id)) subquery1
ON ?

Seq(
  ("James Bond", LocalDate.parse("2012-04-05")),
  ("叉烧包", LocalDate.parse("2010-02-03")),
  ("叉烧包", LocalDate.parse("2012-05-06"))
)

LateralJoin.leftJoin

ScalaSql supports LEFT JOINs, RIGHT JOINs and OUTER JOINs via the .leftJoin/.rightJoin/.outerJoin methods

Buyer.select.leftJoinLateral(b => ShippingInfo.select.filter(b.id `=` _.buyerId))((_, _) =>
  Expr(true)
)

SELECT
  buyer0.id AS res_0_id,
  buyer0.name AS res_0_name,
  buyer0.date_of_birth AS res_0_date_of_birth,
  subquery1.id AS res_1_id,
  subquery1.buyer_id AS res_1_buyer_id,
  subquery1.shipping_date AS res_1_shipping_date
FROM buyer buyer0
LEFT JOIN LATERAL (SELECT
    shipping_info1.id AS id,
    shipping_info1.buyer_id AS buyer_id,
    shipping_info1.shipping_date AS shipping_date
  FROM shipping_info shipping_info1
  WHERE (buyer0.id = shipping_info1.buyer_id)) subquery1 ON ?

Seq(
  (
    Buyer[Sc](1, "James Bond", LocalDate.parse("2001-02-03")),
    Some(ShippingInfo[Sc](2, 1, LocalDate.parse("2012-04-05")))
  ),
  (
    Buyer[Sc](2, "叉烧包", LocalDate.parse("1923-11-12")),
    Some(ShippingInfo[Sc](1, 2, LocalDate.parse("2010-02-03")))
  ),
  (
    Buyer[Sc](2, "叉烧包", LocalDate.parse("1923-11-12")),
    Some(ShippingInfo[Sc](3, 2, LocalDate.parse("2012-05-06")))
  ),
  (Buyer[Sc](3, "Li Haoyi", LocalDate.parse("1965-08-09")), None)
)

LateralJoin.leftJoinFor

ScalaSql supports LEFT JOINs, RIGHT JOINs and OUTER JOINs via the .leftJoin/.rightJoin/.outerJoin methods

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

SELECT
  buyer0.id AS res_0_id,
  buyer0.name AS res_0_name,
  buyer0.date_of_birth AS res_0_date_of_birth,
  subquery1.id AS res_1_id,
  subquery1.buyer_id AS res_1_buyer_id,
  subquery1.shipping_date AS res_1_shipping_date
FROM buyer buyer0
LEFT JOIN LATERAL (SELECT
    shipping_info1.id AS id,
    shipping_info1.buyer_id AS buyer_id,
    shipping_info1.shipping_date AS shipping_date
  FROM shipping_info shipping_info1
  WHERE (buyer0.id = shipping_info1.buyer_id)) subquery1 ON ?

Seq(
  (
    Buyer[Sc](1, "James Bond", LocalDate.parse("2001-02-03")),
    Some(ShippingInfo[Sc](2, 1, LocalDate.parse("2012-04-05")))
  ),
  (
    Buyer[Sc](2, "叉烧包", LocalDate.parse("1923-11-12")),
    Some(ShippingInfo[Sc](1, 2, LocalDate.parse("2010-02-03")))
  ),
  (
    Buyer[Sc](2, "叉烧包", LocalDate.parse("1923-11-12")),
    Some(ShippingInfo[Sc](3, 2, LocalDate.parse("2012-05-06")))
  ),
  (Buyer[Sc](3, "Li Haoyi", LocalDate.parse("1965-08-09")), None)
)

WindowFunction

Window functions using OVER

WindowFunction.simple.rank

Window functions like rank() are supported. You can use the .over, .partitionBy, and .sortBy

Purchase.select.map(p =>
  (
    p.shippingInfoId,
    p.total,
    db.rank().over.partitionBy(p.shippingInfoId).sortBy(p.total).asc
  )
)

SELECT
  purchase0.shipping_info_id AS res_0,
  purchase0.total AS res_1,
  RANK() OVER (PARTITION BY purchase0.shipping_info_id ORDER BY purchase0.total ASC) AS res_2
FROM purchase purchase0

Seq[(Int, Double, Int)](
  (1, 15.7, 1),
  (1, 888.0, 2),
  (1, 900.0, 3),
  (2, 493.8, 1),
  (2, 10000.0, 2),
  (3, 1.3, 1),
  (3, 44.4, 2)
)

WindowFunction.simple.rowNumber

Purchase.select.map(p =>
  (
    p.shippingInfoId,
    p.total,
    db.rowNumber().over.partitionBy(p.shippingInfoId).sortBy(p.total).asc
  )
)

SELECT
  purchase0.shipping_info_id AS res_0,
  purchase0.total AS res_1,
  ROW_NUMBER() OVER (PARTITION BY purchase0.shipping_info_id ORDER BY purchase0.total ASC) AS res_2
FROM purchase purchase0

Seq[(Int, Double, Int)](
  (1, 15.7, 1),
  (1, 888.0, 2),
  (1, 900.0, 3),
  (2, 493.8, 1),
  (2, 10000.0, 2),
  (3, 1.3, 1),
  (3, 44.4, 2)
)

WindowFunction.simple.denseRank

Purchase.select.map(p =>
  (
    p.shippingInfoId,
    p.total,
    db.denseRank().over.partitionBy(p.shippingInfoId).sortBy(p.total).asc
  )
)

SELECT
  purchase0.shipping_info_id AS res_0,
  purchase0.total AS res_1,
  DENSE_RANK() OVER (PARTITION BY purchase0.shipping_info_id ORDER BY purchase0.total ASC) AS res_2
FROM purchase purchase0

Seq[(Int, Double, Int)](
  (1, 15.7, 1),
  (1, 888.0, 2),
  (1, 900.0, 3),
  (2, 493.8, 1),
  (2, 10000.0, 2),
  (3, 1.3, 1),
  (3, 44.4, 2)
)

Purchase.select.map(p =>
  (
    p.shippingInfoId,
    p.total,
    db.denseRank().over.partitionBy(p.shippingInfoId).sortBy(p.total).asc
  )
)

SELECT
  purchase0.shipping_info_id AS res_0,
  purchase0.total AS res_1,
  DENSE_RANK() OVER (PARTITION BY purchase0.shipping_info_id ORDER BY purchase0.total ASC) AS res_2
FROM purchase purchase0

Seq[(Int, Double, Int)](
  (1, 15.7, 1),
  (1, 888.0, 2),
  (1, 900.0, 3),
  (2, 493.8, 1),
  (2, 10000.0, 2),
  (3, 1.3, 1),
  (3, 44.4, 2)
)

WindowFunction.simple.percentRank

Purchase.select.map(p =>
  (
    p.shippingInfoId,
    p.total,
    db.percentRank().over.partitionBy(p.shippingInfoId).sortBy(p.total).asc
  )
)

SELECT
  purchase0.shipping_info_id AS res_0,
  purchase0.total AS res_1,
  PERCENT_RANK() OVER (PARTITION BY purchase0.shipping_info_id ORDER BY purchase0.total ASC) AS res_2
FROM purchase purchase0

Seq[(Int, Double, Double)](
  (1, 15.7, 0.0),
  (1, 888.0, 0.5),
  (1, 900.0, 1.0),
  (2, 493.8, 0.0),
  (2, 10000.0, 1.0),
  (3, 1.3, 0.0),
  (3, 44.4, 1.0)
)

WindowFunction.simple.cumeDist

Purchase.select.map(p =>
  (
    p.shippingInfoId,
    p.total,
    db.cumeDist().over.partitionBy(p.shippingInfoId).sortBy(p.total).asc
  )
)

SELECT
  purchase0.shipping_info_id AS res_0,
  purchase0.total AS res_1,
  CUME_DIST() OVER (PARTITION BY purchase0.shipping_info_id ORDER BY purchase0.total ASC) AS res_2
FROM purchase purchase0

Seq[(Int, Double, Double)](
  (1, 15.7, 0.3333333333333333),
  (1, 888.0, 0.6666666666666666),
  (1, 900.0, 1.0),
  (2, 493.8, 0.5),
  (2, 10000.0, 1.0),
  (3, 1.3, 0.5),
  (3, 44.4, 1.0)
)

WindowFunction.simple.ntile

Purchase.select.map(p =>
  (
    p.shippingInfoId,
    p.total,
    db.ntile(3).over.partitionBy(p.shippingInfoId).sortBy(p.total).asc
  )
)

SELECT
  purchase0.shipping_info_id AS res_0,
  purchase0.total AS res_1,
  NTILE(?) OVER (PARTITION BY purchase0.shipping_info_id ORDER BY purchase0.total ASC) AS res_2
FROM purchase purchase0

Seq[(Int, Double, Int)](
  (1, 15.7, 1),
  (1, 888.0, 2),
  (1, 900.0, 3),
  (2, 493.8, 1),
  (2, 10000.0, 2),
  (3, 1.3, 1),
  (3, 44.4, 2)
)

WindowFunction.simple.lag

Purchase.select.map(p =>
  (
    p.shippingInfoId,
    p.total,
    db.lag(p.total, 1, -1.0).over.partitionBy(p.shippingInfoId).sortBy(p.total).asc
  )
)

SELECT
  purchase0.shipping_info_id AS res_0,
  purchase0.total AS res_1,
  LAG(purchase0.total, ?, ?) OVER (PARTITION BY purchase0.shipping_info_id ORDER BY purchase0.total ASC) AS res_2
FROM purchase purchase0

Seq[(Int, Double, Double)](
  (1, 15.7, -1.0),
  (1, 888.0, 15.7),
  (1, 900.0, 888.0),
  (2, 493.8, -1.0),
  (2, 10000.0, 493.8),
  (3, 1.3, -1.0),
  (3, 44.4, 1.3)
)

WindowFunction.simple.lead

Purchase.select.map(p =>
  (
    p.shippingInfoId,
    p.total,
    db.lead(p.total, 1, -1.0).over.partitionBy(p.shippingInfoId).sortBy(p.total).asc
  )
)

SELECT
  purchase0.shipping_info_id AS res_0,
  purchase0.total AS res_1,
  LEAD(purchase0.total, ?, ?) OVER (PARTITION BY purchase0.shipping_info_id ORDER BY purchase0.total ASC) AS res_2
FROM purchase purchase0

Seq[(Int, Double, Double)](
  (1, 15.7, 888.0),
  (1, 888.0, 900.0),
  (1, 900.0, -1.0),
  (2, 493.8, 10000.0),
  (2, 10000.0, -1.0),
  (3, 1.3, 44.4),
  (3, 44.4, -1.0)
)

WindowFunction.simple.firstValue

Purchase.select.map(p =>
  (
    p.shippingInfoId,
    p.total,
    db.firstValue(p.total).over.partitionBy(p.shippingInfoId).sortBy(p.total).asc
  )
)

SELECT
  purchase0.shipping_info_id AS res_0,
  purchase0.total AS res_1,
  FIRST_VALUE(purchase0.total) OVER (PARTITION BY purchase0.shipping_info_id ORDER BY purchase0.total ASC) AS res_2
FROM purchase purchase0

Seq[(Int, Double, Double)](
  (1, 15.7, 15.7),
  (1, 888.0, 15.7),
  (1, 900.0, 15.7),
  (2, 493.8, 493.8),
  (2, 10000.0, 493.8),
  (3, 1.3, 1.3),
  (3, 44.4, 1.3)
)

WindowFunction.simple.lastValue

Purchase.select.map(p =>
  (
    p.shippingInfoId,
    p.total,
    db.lastValue(p.total).over.partitionBy(p.shippingInfoId).sortBy(p.total).asc
  )
)

SELECT
  purchase0.shipping_info_id AS res_0,
  purchase0.total AS res_1,
  LAST_VALUE(purchase0.total) OVER (PARTITION BY purchase0.shipping_info_id ORDER BY purchase0.total ASC) AS res_2
FROM purchase purchase0

Seq[(Int, Double, Double)](
  (1, 15.7, 15.7),
  (1, 888.0, 888.0),
  (1, 900.0, 900.0),
  (2, 493.8, 493.8),
  (2, 10000.0, 10000.0),
  (3, 1.3, 1.3),
  (3, 44.4, 44.4)
)

WindowFunction.simple.nthValue

Purchase.select.map(p =>
  (
    p.shippingInfoId,
    p.total,
    db.nthValue(p.total, 2).over.partitionBy(p.shippingInfoId).sortBy(p.total).asc
  )
)

SELECT
  purchase0.shipping_info_id AS res_0,
  purchase0.total AS res_1,
  NTH_VALUE(purchase0.total, ?) OVER (PARTITION BY purchase0.shipping_info_id ORDER BY purchase0.total ASC) AS res_2
FROM purchase purchase0

Seq[(Int, Double, Double)](
  (1, 15.7, 0.0),
  (1, 888.0, 888.0),
  (1, 900.0, 888.0),
  (2, 493.8, 0.0),
  (2, 10000.0, 10000.0),
  (3, 1.3, 0.0),
  (3, 44.4, 44.4)
)

WindowFunction.aggregate.sumBy

You can use .mapAggregate to use aggregate functions as window function

Purchase.select.mapAggregate((p, ps) =>
  (
    p.shippingInfoId,
    p.total,
    ps.sumBy(_.total).over.partitionBy(p.shippingInfoId).sortBy(p.total).asc
  )
)

SELECT
  purchase0.shipping_info_id AS res_0,
  purchase0.total AS res_1,
  SUM(purchase0.total) OVER (PARTITION BY purchase0.shipping_info_id ORDER BY purchase0.total ASC) AS res_2
FROM purchase purchase0

Seq(
  (1, 15.7, 15.7),
  (1, 888.0, 903.7),
  (1, 900.0, 1803.7),
  (2, 493.8, 493.8),
  (2, 10000.0, 10493.8),
  (3, 1.3, 1.3),
  (3, 44.4, 45.699999999999996)
)

WindowFunction.aggregate.avgBy

Window functions like rank() are supported. You can use the .over, .partitionBy, and .sortBy

Purchase.select.mapAggregate((p, ps) =>
  (
    p.shippingInfoId,
    p.total,
    ps.avgBy(_.total).over.partitionBy(p.shippingInfoId).sortBy(p.total).asc
  )
)

SELECT
  purchase0.shipping_info_id AS res_0,
  purchase0.total AS res_1,
  AVG(purchase0.total) OVER (PARTITION BY purchase0.shipping_info_id ORDER BY purchase0.total ASC) AS res_2
FROM purchase purchase0

Seq(
  (1, 15.7, 15.7),
  (1, 888.0, 451.85),
  (1, 900.0, 601.2333333333333),
  (2, 493.8, 493.8),
  (2, 10000.0, 5246.9),
  (3, 1.3, 1.3),
  (3, 44.4, 22.849999999999998)
)

WindowFunction.frames

You can have further control over the window function call via .frameStart, .frameEnd, .exclude

Purchase.select.mapAggregate((p, ps) =>
  (
    p.shippingInfoId,
    p.total,
    ps.sumBy(_.total)
      .over
      .partitionBy(p.shippingInfoId)
      .sortBy(p.total)
      .asc
      .frameStart
      .preceding()
      .frameEnd
      .following()
      .exclude
      .currentRow
  )
)

SELECT
  purchase0.shipping_info_id AS res_0,
  purchase0.total AS res_1,
  SUM(purchase0.total)
  OVER (PARTITION BY purchase0.shipping_info_id
    ORDER BY purchase0.total ASC
    ROWS BETWEEN UNBOUNDED PRECEDING
    AND UNBOUNDED FOLLOWING EXCLUDE CURRENT ROW) AS res_2
FROM purchase purchase0

Seq[(Int, Double, Double)](
  (1, 15.7, 1788.0),
  (1, 888.0, 915.7),
  (1, 900.0, 903.7),
  (2, 493.8, 10000.0),
  (2, 10000.0, 493.8),
  (3, 1.3, 44.4),
  (3, 44.4, 1.3)
)

WindowFunction.filter

ScalaSql allows .filter to be used after over to add a SQL FILTER clause to your window function call, allowing you to exclude certain rows from the window.

Purchase.select.mapAggregate((p, ps) =>
  (
    p.shippingInfoId,
    p.total,
    ps.sumBy(_.total)
      .over
      .filter(p.total > 100)
      .partitionBy(p.shippingInfoId)
      .sortBy(p.total)
      .asc
  )
)

SELECT
  purchase0.shipping_info_id AS res_0,
  purchase0.total AS res_1,
  SUM(purchase0.total)
    FILTER (WHERE (purchase0.total > ?))
    OVER (PARTITION BY purchase0.shipping_info_id
      ORDER BY purchase0.total ASC) AS res_2
FROM purchase purchase0

Seq[(Int, Double, Double)](
  (1, 15.7, 0.0),
  (1, 888.0, 888.0),
  (1, 900.0, 1788.0),
  (2, 493.8, 493.8),
  (2, 10000.0, 10493.8),
  (3, 1.3, 0.0),
  (3, 44.4, 0.0)
)

GetGeneratedKeys

INSERT operations with .getGeneratedKeys. Not supported by Sqlite (see https://github.com/xerial/sqlite-jdbc/issues/980)

GetGeneratedKeys.single.values

getGeneratedKeys on an insert returns the primary key, even if it was provided explicitly.

Buyer.insert
  .values(
    Buyer[Sc](17, "test buyer", LocalDate.parse("2023-09-09"))
  )
  .getGeneratedKeys[Int]

INSERT INTO buyer (id, name, date_of_birth) VALUES (?, ?, ?)

```
Seq(17)
```

Buyer.select.filter(_.name `=` "test buyer")

Seq(Buyer[Sc](17, "test buyer", LocalDate.parse("2023-09-09")))

GetGeneratedKeys.single.columns

All styles of INSERT query support .getGeneratedKeys, with this example using insert.columns rather than insert.values. You can also retrieve the generated primary keys using any compatible type, here shown using Long rather than Int

Buyer.insert
  .columns(
    _.name := "test buyer",
    _.dateOfBirth := LocalDate.parse("2023-09-09"),
    _.id := 4
  )
  .getGeneratedKeys[Long]

INSERT INTO buyer (name, date_of_birth, id) VALUES (?, ?, ?)

```
Seq(4L)
```

Buyer.select.filter(_.name `=` "test buyer")

Seq(Buyer[Sc](4, "test buyer", LocalDate.parse("2023-09-09")))

GetGeneratedKeys.single.partial

If the primary key was not provided but was auto-generated by the database, getGeneratedKeys returns the generated value

Buyer.insert
  .columns(_.name := "test buyer", _.dateOfBirth := LocalDate.parse("2023-09-09"))
  .getGeneratedKeys[Int]

INSERT INTO buyer (name, date_of_birth) VALUES (?, ?)

```
Seq(4)
```

Buyer.select.filter(_.name `=` "test buyer")

Seq(Buyer[Sc](4, "test buyer", LocalDate.parse("2023-09-09")))

GetGeneratedKeys.batch.partial

getGeneratedKeys can return multiple generated primary key values for a batch insert statement

Buyer.insert
  .batched(_.name, _.dateOfBirth)(
    ("test buyer A", LocalDate.parse("2001-04-07")),
    ("test buyer B", LocalDate.parse("2002-05-08")),
    ("test buyer C", LocalDate.parse("2003-06-09"))
  )
  .getGeneratedKeys[Int]

INSERT INTO buyer (name, date_of_birth)
VALUES (?, ?), (?, ?), (?, ?)

```
Seq(4, 5, 6)
```

Buyer.select

Seq(
  Buyer[Sc](1, "James Bond", LocalDate.parse("2001-02-03")),
  Buyer[Sc](2, "叉烧包", LocalDate.parse("1923-11-12")),
  Buyer[Sc](3, "Li Haoyi", LocalDate.parse("1965-08-09")),
  // id=4,5,6 comes from auto increment
  Buyer[Sc](4, "test buyer A", LocalDate.parse("2001-04-07")),
  Buyer[Sc](5, "test buyer B", LocalDate.parse("2002-05-08")),
  Buyer[Sc](6, "test buyer C", LocalDate.parse("2003-06-09"))
)

GetGeneratedKeys.select.simple

getGeneratedKeys can return multiple generated primary key values for an insert based on a select

Buyer.insert
  .select(
    x => (x.name, x.dateOfBirth),
    Buyer.select.map(x => (x.name, x.dateOfBirth)).filter(_._1 <> "Li Haoyi")
  )
  .getGeneratedKeys[Int]

INSERT INTO buyer (name, date_of_birth)
SELECT buyer0.name AS res_0, buyer0.date_of_birth AS res_1
FROM buyer buyer0
WHERE (buyer0.name <> ?)

```
Seq(4, 5)
```

Buyer.select

Seq(
  Buyer[Sc](1, "James Bond", LocalDate.parse("2001-02-03")),
  Buyer[Sc](2, "叉烧包", LocalDate.parse("1923-11-12")),
  Buyer[Sc](3, "Li Haoyi", LocalDate.parse("1965-08-09")),
  // id=4,5 comes from auto increment, 6 is filtered out in the select
  Buyer[Sc](4, "James Bond", LocalDate.parse("2001-02-03")),
  Buyer[Sc](5, "叉烧包", LocalDate.parse("1923-11-12"))
)

Schema

If your table belongs to a schema other than the default schema of your database, you can specify this in your table definition with
`override def schemaName = "otherschema"`

Schema.schema.select

Invoice.select

SELECT invoice0.id AS id, invoice0.total AS total, invoice0.vendor_name AS vendor_name
FROM otherschema.invoice invoice0

Seq(
  Invoice[Sc](id = 1, total = 150.4, vendor_name = "Siemens"),
  Invoice[Sc](id = 2, total = 213.3, vendor_name = "Samsung"),
  Invoice[Sc](id = 3, total = 407.2, vendor_name = "Shell")
)

Schema.schema.insert.columns

Invoice.insert.columns(
  _.total := 200.3,
  _.vendor_name := "Huawei"
)

INSERT INTO otherschema.invoice (total, vendor_name) VALUES (?, ?)

```
1
```

Schema.schema.insert.values

Invoice.insert
  .values(
    Invoice[Sc](
      id = 0,
      total = 200.3,
      vendor_name = "Huawei"
    )
  )
  .skipColumns(_.id)

INSERT INTO otherschema.invoice (total, vendor_name) VALUES (?, ?)

```
1
```

Schema.schema.update

Invoice
  .update(_.id === 1)
  .set(
    _.total := 200.3,
    _.vendor_name := "Huawei"
  )

UPDATE otherschema.invoice
            SET
              total = ?,
              vendor_name = ?
            WHERE
              (invoice.id = ?)

```
1
```

Schema.schema.delete

Invoice.delete(_.id === 1)

DELETE FROM otherschema.invoice WHERE (invoice.id = ?)

```
1
```

Schema.schema.insert into

Invoice.insert.select(
  i => (i.total, i.vendor_name),
  Invoice.select.map(i => (i.total, i.vendor_name))
)

INSERT INTO
              otherschema.invoice (total, vendor_name)
            SELECT
              invoice0.total AS res_0,
              invoice0.vendor_name AS res_1
            FROM
              otherschema.invoice invoice0

```
4
```

Schema.schema.join

Invoice.select.join(Invoice)(_.id `=` _.id).map(_._1.id)

SELECT
              invoice0.id AS res
            FROM
              otherschema.invoice invoice0
            JOIN otherschema.invoice invoice1 ON (invoice0.id = invoice1.id)

```
Seq(2, 3, 4, 5, 6, 7, 8, 9)
```

EscapedTableName

If your table name is a reserved sql world, e.g. `order`, you can specify this in your table definition with
`override def escape = true`

EscapedTableName.escape table name.select

Select.select

SELECT select0.id AS id, select0.name AS name
FROM "select" select0

```
Seq.empty[Select[Sc]]
```

EscapedTableName.escape table name.select with filter

Select.select.filter(_.id `=` 1)

SELECT select0.id AS id, select0.name AS name
FROM "select" select0
WHERE (select0.id = ?)

```
Seq.empty[Select[Sc]]
```

EscapedTableName.escape table name.delete

Select.delete(_ => true)

```
DELETE FROM "select"
```
```
0
```

EscapedTableName.escape table name.join

Select.select.join(Select)(_.id `=` _.id)

SELECT
  select0.id AS res_0_id,
  select0.name AS res_0_name,
  select1.id AS res_1_id,
  select1.name AS res_1_name
FROM
  "select" select0
  JOIN "select" select1 ON (select0.id = select1.id)

```
Seq.empty[(Select[Sc], Select[Sc])]
```

EscapedTableName.escape table name.update

Select.update(_ => true).set(_.name := "hello")

```
UPDATE "select" SET name = ?
```
```
0
```

EscapedTableName.escape table name.update where

Select.update(_.id `=` 1).set(_.name := "hello")

UPDATE "select" SET name = ? WHERE ("select".id = ?)

```
0
```

EscapedTableName.escape table name.insert

Select.insert.values(
  Select[Sc](
    id = 0,
    name = "hello"
  )
)

INSERT INTO "select" (id, name) VALUES (?, ?)

```
1
```

EscapedTableNameWithReturning

If your table name is a reserved sql world, e.g. `order`, you can specify this in your table definition with
`override def escape = true`

EscapedTableNameWithReturning.insert with returning

Select.insert
  .values(
    Select[Sc](
      id = 0,
      name = "hello"
    )
  )
  .returning(_.id)

INSERT INTO "select" (id, name) VALUES (?, ?) RETURNING "select".id AS res

```
Seq(0)
```

SubQuery

Queries that explicitly use subqueries (e.g. for JOINs) or require subqueries to preserve the Scala semantics of the various operators

SubQuery.sortTakeJoin

A ScalaSql .join referencing a .select translates straightforwardly into a SQL JOIN on a subquery

Purchase.select
  .join(Product.select.sortBy(_.price).desc.take(1))(_.productId `=` _.id)
  .map { case (purchase, product) => purchase.total }

SELECT purchase0.total AS res
FROM purchase purchase0
JOIN (SELECT product1.id AS id, product1.price AS price
  FROM product product1
  ORDER BY price DESC
  LIMIT ?) subquery1
ON (purchase0.product_id = subquery1.id)

```
Seq(10000.0)
```

SubQuery.sortTakeFrom

Some sequences of operations cannot be expressed as a single SQL query, and thus translate into an outer query wrapping a subquery inside the FROM. An example of this is performing a .join after a .take: SQL does not allow you to put JOINs after LIMITs, and so the only way to write this in SQL is as a subquery.

Product.select.sortBy(_.price).desc.take(1).join(Purchase)(_.id `=` _.productId).map {
  case (product, purchase) => purchase.total
}

SELECT purchase1.total AS res
FROM (SELECT product0.id AS id, product0.price AS price
  FROM product product0
  ORDER BY price DESC
  LIMIT ?) subquery0
JOIN purchase purchase1 ON (subquery0.id = purchase1.product_id)

```
Seq(10000.0)
```

SubQuery.sortTakeFromAndJoin

This example shows a ScalaSql query that results in a subquery in both the FROM and the JOIN clause of the generated SQL query.

Product.select
  .sortBy(_.price)
  .desc
  .take(3)
  .join(Purchase.select.sortBy(_.count).desc.take(3))(_.id `=` _.productId)
  .map { case (product, purchase) => (product.name, purchase.count) }

SELECT
  subquery0.name AS res_0,
  subquery1.count AS res_1
FROM (SELECT
    product0.id AS id,
    product0.name AS name,
    product0.price AS price
  FROM product product0
  ORDER BY price DESC
  LIMIT ?) subquery0
JOIN (SELECT
    purchase1.product_id AS product_id,
    purchase1.count AS count
  FROM purchase purchase1
  ORDER BY count DESC
  LIMIT ?) subquery1
ON (subquery0.id = subquery1.product_id)

```
Seq(("Camera", 10))
```

SubQuery.sortLimitSortLimit

Performing multiple sorts with .takes in between is also something that requires subqueries, as a single query only allows a single LIMIT clause after the ORDER BY

Product.select.sortBy(_.price).desc.take(4).sortBy(_.price).asc.take(2).map(_.name)

SELECT subquery0.name AS res
FROM (SELECT
    product0.name AS name,
    product0.price AS price
  FROM product product0
  ORDER BY price DESC
  LIMIT ?) subquery0
ORDER BY subquery0.price ASC
LIMIT ?

```
Seq("Face Mask", "Skate Board")
```

SubQuery.sortGroupBy

Purchase.select.sortBy(_.count).take(5).groupBy(_.productId)(_.sumBy(_.total))

SELECT subquery0.product_id AS res_0, SUM(subquery0.total) AS res_1
FROM (SELECT
    purchase0.product_id AS product_id,
    purchase0.count AS count,
    purchase0.total AS total
  FROM purchase purchase0
  ORDER BY count
  LIMIT ?) subquery0
GROUP BY subquery0.product_id

Seq((1, 44.4), (2, 900.0), (3, 15.7), (4, 493.8), (5, 10000.0))

SubQuery.groupByJoin

Purchase.select.groupBy(_.productId)(_.sumBy(_.total)).join(Product)(_._1 `=` _.id).map {
  case (productId, total, product) => (product.name, total)
}

SELECT
  product1.name AS res_0,
  subquery0.res_1 AS res_1
FROM (SELECT
    purchase0.product_id AS res_0,
    SUM(purchase0.total) AS res_1
  FROM purchase purchase0
  GROUP BY purchase0.product_id) subquery0
JOIN product product1 ON (subquery0.res_0 = product1.id)

Seq(
  ("Camera", 10000.0),
  ("Cookie", 1.3),
  ("Face Mask", 932.4),
  ("Guitar", 900.0),
  ("Skate Board", 493.8),
  ("Socks", 15.7)
)

SubQuery.subqueryInFilter

You can use .selects and aggregate operations like .size anywhere an expression is expected; these translate into SQL subqueries as expressions. SQL subqueries-as-expressions require that the subquery returns exactly 1 row and 1 column, which is something the aggregate operation (in this case .sum/COUNT(1)) helps us ensure. Here, we do subquery in a .filter/WHERE.

Buyer.select.filter(c => ShippingInfo.select.filter(p => c.id `=` p.buyerId).size `=` 0)

SELECT
  buyer0.id AS id,
  buyer0.name AS name,
  buyer0.date_of_birth AS date_of_birth
FROM buyer buyer0
WHERE ((SELECT
    COUNT(1) AS res
    FROM shipping_info shipping_info1
    WHERE (buyer0.id = shipping_info1.buyer_id)) = ?)

Seq(Buyer[Sc](3, "Li Haoyi", LocalDate.parse("1965-08-09")))

SubQuery.subqueryInMap

Similar to the above example, but we do the subquery/aggregate in a .map instead of a .filter

Buyer.select.map(c => (c, ShippingInfo.select.filter(p => c.id `=` p.buyerId).size))

SELECT
  buyer0.id AS res_0_id,
  buyer0.name AS res_0_name,
  buyer0.date_of_birth AS res_0_date_of_birth,
  (SELECT COUNT(1) AS res
    FROM shipping_info shipping_info1
    WHERE (buyer0.id = shipping_info1.buyer_id)) AS res_1
FROM buyer buyer0

Seq(
  (Buyer[Sc](1, "James Bond", LocalDate.parse("2001-02-03")), 1),
  (Buyer[Sc](2, "叉烧包", LocalDate.parse("1923-11-12")), 2),
  (Buyer[Sc](3, "Li Haoyi", LocalDate.parse("1965-08-09")), 0)
)

SubQuery.subqueryInMapNested

Buyer.select.map(c => (c, ShippingInfo.select.filter(p => c.id `=` p.buyerId).size `=` 1))

SELECT
  buyer0.id AS res_0_id,
  buyer0.name AS res_0_name,
  buyer0.date_of_birth AS res_0_date_of_birth,
  ((SELECT
    COUNT(1) AS res
    FROM shipping_info shipping_info1
    WHERE (buyer0.id = shipping_info1.buyer_id)) = ?) AS res_1
FROM buyer buyer0

Seq(
  (Buyer[Sc](1, "James Bond", LocalDate.parse("2001-02-03")), true),
  (Buyer[Sc](2, "叉烧包", LocalDate.parse("1923-11-12")), false),
  (Buyer[Sc](3, "Li Haoyi", LocalDate.parse("1965-08-09")), false)
)

SubQuery.selectLimitUnionSelect

Buyer.select
  .map(_.name.toLowerCase)
  .take(2)
  .unionAll(Product.select.map(_.kebabCaseName.toLowerCase))

SELECT subquery0.res AS res
FROM (SELECT
    LOWER(buyer0.name) AS res
  FROM buyer buyer0
  LIMIT ?) subquery0
UNION ALL
SELECT LOWER(product0.kebab_case_name) AS res
FROM product product0

Seq("james bond", "叉烧包", "face-mask", "guitar", "socks", "skate-board", "camera", "cookie")

SubQuery.selectUnionSelectLimit

Buyer.select
  .map(_.name.toLowerCase)
  .unionAll(Product.select.map(_.kebabCaseName.toLowerCase).take(2))

SELECT LOWER(buyer0.name) AS res
FROM buyer buyer0
UNION ALL
SELECT subquery0.res AS res
FROM (SELECT
    LOWER(product0.kebab_case_name) AS res
  FROM product product0
  LIMIT ?) subquery0

Seq("james bond", "叉烧包", "li haoyi", "face-mask", "guitar")

SubQuery.exceptAggregate

Product.select
  .map(p => (p.name.toLowerCase, p.price))
  // `p.name.toLowerCase` and  `p.kebabCaseName.toLowerCase` are not eliminated, because
  // they are important to the semantics of EXCEPT (and other non-UNION-ALL operators)
  .except(Product.select.map(p => (p.kebabCaseName.toLowerCase, p.price)))
  .aggregate(ps => (ps.maxBy(_._2), ps.minBy(_._2)))

SELECT
  MAX(subquery0.res_1) AS res_0,
  MIN(subquery0.res_1) AS res_1
FROM (SELECT
    LOWER(product0.name) AS res_0,
    product0.price AS res_1
  FROM product product0
  EXCEPT
  SELECT
    LOWER(product0.kebab_case_name) AS res_0,
    product0.price AS res_1
  FROM product product0) subquery0

```
(123.45, 8.88)
```

SubQuery.unionAllAggregate

Product.select
  .map(p => (p.name.toLowerCase, p.price))
  // `p.name.toLowerCase` and  `p.kebabCaseName.toLowerCase` get eliminated,
  // as they are not selected by the enclosing query, and cannot affect the UNION ALL
  .unionAll(Product.select.map(p => (p.kebabCaseName.toLowerCase, p.price)))
  .aggregate(ps => (ps.maxBy(_._2), ps.minBy(_._2)))

SELECT
  MAX(subquery0.res_1) AS res_0,
  MIN(subquery0.res_1) AS res_1
FROM (SELECT product0.price AS res_1
  FROM product product0
  UNION ALL
  SELECT product0.price AS res_1
  FROM product product0) subquery0

```
(1000.0, 0.1)
```

SubQuery.deeplyNested

Subqueries can be arbitrarily nested. This example traverses four tables to find the price of the most expensive product bought by each Buyer, but instead of using JOINs it uses subqueries nested 4 layers deep. While this example is contrived, it demonstrates how nested ScalaSql .select calls translate directly into nested SQL subqueries.

To turn the ScalaSql Select[T] into an Expr[T], you can either use an aggregate method like .sumBy(...): Expr[Int] that generates a SUM(...) aggregate, or via the .toExpr method that leaves the subquery untouched. SQL requires that subqueries used as expressions must return a single row and single column, and if the query returns some other number of rows/columns most databases will throw an exception, though some like Sqlite will pick the first row/column arbitrarily.

Buyer.select.map { buyer =>
  buyer.name ->
    ShippingInfo.select
      .filter(_.buyerId === buyer.id)
      .map { shippingInfo =>
        Purchase.select
          .filter(_.shippingInfoId === shippingInfo.id)
          .map { purchase =>
            Product.select
              .filter(_.id === purchase.productId)
              .map(_.price)
              .sorted
              .desc
              .take(1)
              .toExpr
          }
          .sorted
          .desc
          .take(1)
          .toExpr
      }
      .sorted
      .desc
      .take(1)
      .toExpr
}

SELECT
  buyer0.name AS res_0,
  (SELECT
    (SELECT
      (SELECT product3.price AS res
      FROM product product3
      WHERE (product3.id = purchase2.product_id)
      ORDER BY res DESC
      LIMIT ?) AS res
    FROM purchase purchase2
    WHERE (purchase2.shipping_info_id = shipping_info1.id)
    ORDER BY res DESC
    LIMIT ?) AS res
  FROM shipping_info shipping_info1
  WHERE (shipping_info1.buyer_id = buyer0.id)
  ORDER BY res DESC
  LIMIT ?) AS res_1
FROM buyer buyer0

Seq(
  ("James Bond", 1000.0),
  ("叉烧包", 300.0),
  ("Li Haoyi", 0.0)
)

WithCte

Basic WITH/Common-Table-Expression operations

WithCte.simple

ScalaSql supports WITH-clauses, also known as "Common Table Expressions" (CTEs), via the .withCte syntax.

db.withCte(Buyer.select.map(_.name)) { bs =>
  bs.map(_ + "-suffix")
}

WITH cte0 (res) AS (SELECT buyer0.name AS res FROM buyer buyer0)
SELECT (cte0.res || ?) AS res
FROM cte0

Seq("James Bond-suffix", "叉烧包-suffix", "Li Haoyi-suffix")

WithCte.multiple

Multiple withCte blocks can be stacked, turning into chained WITH clauses in the generated SQL

db.withCte(Buyer.select) { bs =>
  db.withCte(ShippingInfo.select) { sis =>
    bs.join(sis)(_.id === _.buyerId)
      .map { case (b, s) => (b.name, s.shippingDate) }
  }
}

WITH
  cte0 (id, name) AS (SELECT
    buyer0.id AS id, buyer0.name AS name FROM buyer buyer0),
  cte1 (buyer_id, shipping_date) AS (SELECT
      shipping_info1.buyer_id AS buyer_id,
      shipping_info1.shipping_date AS shipping_date
    FROM shipping_info shipping_info1)
SELECT cte0.name AS res_0, cte1.shipping_date AS res_1
FROM cte0
JOIN cte1 ON (cte0.id = cte1.buyer_id)

Seq(
  ("叉烧包", LocalDate.parse("2010-02-03")),
  ("James Bond", LocalDate.parse("2012-04-05")),
  ("叉烧包", LocalDate.parse("2012-05-06"))
)

WithCte.eliminated

Only the necessary columns are exported from the WITH clause; columns that are un-used in the downstream SELECT clause are eliminated

db.withCte(Buyer.select) { bs =>
  bs.map(_.name + "-suffix")
}

WITH cte0 (name) AS (SELECT buyer0.name AS name FROM buyer buyer0)
SELECT (cte0.name || ?) AS res
FROM cte0

Seq("James Bond-suffix", "叉烧包-suffix", "Li Haoyi-suffix")

WithCte.subquery

ScalaSql's withCte can be used anywhere a .select operator can be used. The generated WITH clauses may be wrapped in sub-queries in scenarios where they cannot be easily combined into a single query

db.withCte(Buyer.select) { bs =>
  db.withCte(ShippingInfo.select) { sis =>
    bs.join(sis)(_.id === _.buyerId)
  }
}.join(
  db.withCte(Product.select) { prs =>
    Purchase.select.join(prs)(_.productId === _.id)
  }
)(_._2.id === _._1.shippingInfoId)
  .map { case (b, s, (pu, pr)) => (b.name, pr.name) }

SELECT subquery0.res_0_name AS res_0, subquery1.res_1_name AS res_1
FROM (WITH
    cte0 (id, name)
    AS (SELECT buyer0.id AS id, buyer0.name AS name FROM buyer buyer0),
    cte1 (id, buyer_id)
    AS (SELECT shipping_info1.id AS id, shipping_info1.buyer_id AS buyer_id
      FROM shipping_info shipping_info1)
  SELECT cte0.name AS res_0_name, cte1.id AS res_1_id
  FROM cte0
  JOIN cte1 ON (cte0.id = cte1.buyer_id)) subquery0
JOIN (WITH
    cte1 (id, name)
    AS (SELECT product1.id AS id, product1.name AS name FROM product product1)
  SELECT
    purchase2.shipping_info_id AS res_0_shipping_info_id,
    cte1.name AS res_1_name
  FROM purchase purchase2
  JOIN cte1 ON (purchase2.product_id = cte1.id)) subquery1
ON (subquery0.res_1_id = subquery1.res_0_shipping_info_id)

Seq[(String, String)](
  ("James Bond", "Camera"),
  ("James Bond", "Skate Board"),
  ("叉烧包", "Cookie"),
  ("叉烧包", "Face Mask"),
  ("叉烧包", "Face Mask"),
  ("叉烧包", "Guitar"),
  ("叉烧包", "Socks")
)

ExprOps

Operations that can be performed on Expr[T] for any T

ExprOps.numeric.greaterThan

Expr(6) > Expr(2)

```
SELECT (? > ?) AS res
```
```
true
```

ExprOps.numeric.lessThan

Expr(6) < Expr(2)

```
SELECT (? < ?) AS res
```
```
false
```

ExprOps.numeric.greaterThanOrEquals

Expr(6) >= Expr(2)

```
SELECT (? >= ?) AS res
```
```
true
```

ExprOps.numeric.lessThanOrEquals

Expr(6) <= Expr(2)

```
SELECT (? <= ?) AS res
```
```
false
```

ExprOps.string.greaterThan

Expr("A") > Expr("B")

```
SELECT (? > ?) AS res
```
```
false
```

ExprOps.string.lessThan

Expr("A") < Expr("B")

```
SELECT (? < ?) AS res
```
```
true
```

ExprOps.string.greaterThanOrEquals

Expr("A") >= Expr("B")

```
SELECT (? >= ?) AS res
```
```
false
```

ExprOps.string.lessThanOrEquals

Expr("A") <= Expr("B")

```
SELECT (? <= ?) AS res
```
```
true
```

ExprOps.boolean.greaterThan

Expr(true) > Expr(false)

```
SELECT (? > ?) AS res
```
```
true
```

ExprOps.boolean.lessThan

Expr(true) < Expr(true)

```
SELECT (? < ?) AS res
```
```
false
```

ExprOps.boolean.greaterThanOrEquals

Expr(true) >= Expr(true)

```
SELECT (? >= ?) AS res
```
```
true
```

ExprOps.boolean.lessThanOrEquals

Expr(true) <= Expr(true)

```
SELECT (? <= ?) AS res
```
```
true
```

ExprOps.cast.byte

Expr(45.12).cast[Byte]

```
SELECT CAST(? AS INTEGER) AS res
```
```
45: Byte
```

ExprOps.cast.short

Expr(1234.1234).cast[Short]

```
SELECT CAST(? AS SMALLINT) AS res
```
```
1234: Short
```

ExprOps.cast.int

Expr(1234.1234).cast[Int]

```
SELECT CAST(? AS INTEGER) AS res
```
```
1234
```

ExprOps.cast.long

Expr(1234.1234).cast[Long]

```
SELECT CAST(? AS BIGINT) AS res
```
```
1234L
```

ExprOps.cast.string

Expr(1234.5678).cast[String]

```
SELECT CAST(? AS VARCHAR) AS res
```
```
"1234.5678"
```

ExprOps.cast.localdate

Expr("2001-02-03").cast[java.time.LocalDate]

```
SELECT CAST(? AS DATE) AS res
```

java.time.LocalDate.parse("2001-02-03")

ExprOps.cast.localdatetime

Expr("2023-11-12 03:22:41").cast[java.time.LocalDateTime]

```
SELECT CAST(? AS TIMESTAMP) AS res
```

java.time.LocalDateTime.parse("2023-11-12T03:22:41")

ExprOps.cast.utildate

Expr("2023-11-12 03:22:41").cast[java.util.Date]

```
SELECT CAST(? AS TIMESTAMP) AS res
```

new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse("2023-11-12 03:22:41")

ExprOps.cast.instant

Expr("2007-12-03 10:15:30.00").cast[java.time.Instant]

```
SELECT CAST(? AS TIMESTAMP) AS res
```

java.time.Instant.parse("2007-12-03T02:15:30.00Z")

ExprOps.cast.castNamed

Expr(1234.5678).castNamed[String](sql"CHAR(3)")

```
SELECT CAST(? AS CHAR(3)) AS res
```
```
"123"
```

ExprBooleanOps

Operations that can be performed on Expr[Boolean]

ExprBooleanOps.and

Expr(true) && Expr(true)

```
SELECT (? AND ?) AS res
```
```
true
```

Expr(false) && Expr(true)

```
SELECT (? AND ?) AS res
```
```
false
```

ExprBooleanOps.or

Expr(false) || Expr(false)

```
SELECT (? OR ?) AS res
```
```
false
```

!Expr(false)

```
SELECT (NOT ?) AS res
```
```
true
```

ExprNumericOps

Operations that can be performed on Expr[T] when T is numeric

ExprNumericOps.plus

Expr(6) + Expr(2)

```
SELECT (? + ?) AS res
```
```
8
```

ExprNumericOps.minus

Expr(6) - Expr(2)

```
SELECT (? - ?) AS res
```
```
4
```

ExprNumericOps.times

Expr(6) * Expr(2)

```
SELECT (? * ?) AS res
```
```
12
```

ExprNumericOps.divide

Expr(6) / Expr(2)

```
SELECT (? / ?) AS res
```
```
3
```

ExprNumericOps.modulo

Expr(6) % Expr(2)

```
SELECT MOD(?, ?) AS res
```
```
0
```

ExprNumericOps.bitwiseAnd

Expr(6) & Expr(2)

```
SELECT (? & ?) AS res
```
```
2
```

ExprNumericOps.bitwiseOr

Expr(6) | Expr(3)

```
SELECT (? | ?) AS res
```
```
7
```

ExprNumericOps.between

Expr(4).between(Expr(2), Expr(6))

```
SELECT ? BETWEEN ? AND ? AS res
```
```
true
```

ExprNumericOps.unaryPlus

+Expr(-4)

```
SELECT +? AS res
```
```
-4
```

ExprNumericOps.unaryMinus

-Expr(-4)

```
SELECT -? AS res
```
```
4
```

ExprNumericOps.unaryTilde

~Expr(-4)

```
SELECT ~? AS res
```
```
3
```

ExprNumericOps.abs

Expr(-4).abs

```
SELECT ABS(?) AS res
```
```
4
```

ExprNumericOps.mod

Expr(8).mod(Expr(3))

```
SELECT MOD(?, ?) AS res
```
```
2
```

ExprNumericOps.ceil

Expr(4.3).ceil

```
SELECT CEIL(?) AS res
```
```
5.0
```

ExprNumericOps.floor

Expr(4.7).floor

```
SELECT FLOOR(?) AS res
```
```
4.0
```

ExprNumericOps.precedence

(Expr(2) + Expr(3)) * Expr(4)

```
SELECT ((? + ?) * ?) AS res
```
```
20
```

ExprNumericOps.sign

Expr(-100).sign

```
SELECT SIGN(?) AS res
```
```
-1
```

ExprSeqNumericOps

Operations that can be performed on Expr[Seq[T]] where T is numeric

ExprSeqNumericOps.sum

Purchase.select.map(_.count).sum

SELECT SUM(purchase0.count) AS res FROM purchase purchase0

```
140
```

ExprSeqNumericOps.min

Purchase.select.map(_.count).min

SELECT MIN(purchase0.count) AS res FROM purchase purchase0

```
3
```

ExprSeqNumericOps.max

Purchase.select.map(_.count).max

SELECT MAX(purchase0.count) AS res FROM purchase purchase0

```
100
```

ExprSeqNumericOps.avg

Purchase.select.map(_.count).avg

SELECT AVG(purchase0.count) AS res FROM purchase purchase0

```
20
```

ExprSeqOps

Operations that can be performed on Expr[Seq[_]]

ExprSeqOps.size

Purchase.select.size

SELECT COUNT(1) AS res FROM purchase purchase0

```
7
```

ExprSeqOps.sumBy.simple

Purchase.select.sumBy(_.count)

SELECT SUM(purchase0.count) AS res FROM purchase purchase0

```
140
```

ExprSeqOps.sumBy.some

Purchase.select.sumByOpt(_.count)

SELECT SUM(purchase0.count) AS res FROM purchase purchase0

```
Option(140)
```

ExprSeqOps.sumBy.none

Purchase.select.filter(_ => false).sumByOpt(_.count)

SELECT SUM(purchase0.count) AS res FROM purchase purchase0 WHERE ?

```
Option.empty[Int]
```

ExprSeqOps.minBy.simple

Purchase.select.minBy(_.count)

SELECT MIN(purchase0.count) AS res FROM purchase purchase0

```
3
```

ExprSeqOps.minBy.some

Purchase.select.minByOpt(_.count)

SELECT MIN(purchase0.count) AS res FROM purchase purchase0

```
Option(3)
```

ExprSeqOps.minBy.none

Purchase.select.filter(_ => false).minByOpt(_.count)

SELECT MIN(purchase0.count) AS res FROM purchase purchase0 WHERE ?

```
Option.empty[Int]
```

ExprSeqOps.maxBy.simple

Purchase.select.maxBy(_.count)

SELECT MAX(purchase0.count) AS res FROM purchase purchase0

```
100
```

ExprSeqOps.maxBy.some

Purchase.select.maxByOpt(_.count)

SELECT MAX(purchase0.count) AS res FROM purchase purchase0

```
Option(100)
```

ExprSeqOps.maxBy.none

Purchase.select.filter(_ => false).maxByOpt(_.count)

SELECT MAX(purchase0.count) AS res FROM purchase purchase0 WHERE ?

```
Option.empty[Int]
```

ExprSeqOps.avgBy.simple

Purchase.select.avgBy(_.count)

SELECT AVG(purchase0.count) AS res FROM purchase purchase0

```
20
```

ExprSeqOps.avgBy.some

Purchase.select.avgByOpt(_.count)

SELECT AVG(purchase0.count) AS res FROM purchase purchase0

```
Option(20)
```

ExprSeqOps.avgBy.none

Purchase.select.filter(_ => false).avgByOpt(_.count)

SELECT AVG(purchase0.count) AS res FROM purchase purchase0 WHERE ?

```
Option.empty[Int]
```

ExprSeqOps.mkString.simple

Buyer.select.map(_.name).mkString()

SELECT STRING_AGG(buyer0.name || '', '') AS res FROM buyer buyer0

```
"James Bond叉烧包Li Haoyi"
```

ExprSeqOps.mkString.sep

Buyer.select.map(_.name).mkString(", ")

SELECT STRING_AGG(buyer0.name || '', ?) AS res FROM buyer buyer0

```
"James Bond, 叉烧包, Li Haoyi"
```

ExprStringOps

Operations that can be performed on Expr[String]

ExprStringOps.plus

Expr("hello") + Expr("world")

```
SELECT (? || ?) AS res
```
```
"helloworld"
```

ExprStringOps.like

Expr("hello").like("he%")

```
SELECT (? LIKE ?) AS res
```
```
true
```

ExprStringOps.length

Expr("hello").length

```
SELECT LENGTH(?) AS res
```
```
5
```

ExprStringOps.octetLength

Expr("叉烧包").octetLength

```
SELECT OCTET_LENGTH(?) AS res
```
```
9
```

ExprStringOps.position

Expr("hello").indexOf("ll")

```
SELECT POSITION(? IN ?) AS res
```
```
3
```

ExprStringOps.toLowerCase

Expr("Hello").toLowerCase

```
SELECT LOWER(?) AS res
```
```
"hello"
```

ExprStringOps.trim

Expr("  Hello ").trim

```
SELECT TRIM(?) AS res
```
```
"Hello"
```

ExprStringOps.ltrim

Expr("  Hello ").ltrim

```
SELECT LTRIM(?) AS res
```
```
"Hello "
```

ExprStringOps.rtrim

Expr("  Hello ").rtrim

```
SELECT RTRIM(?) AS res
```
```
"  Hello"
```

ExprStringOps.substring

Expr("Hello").substring(2, 2)

```
SELECT SUBSTRING(?, ?, ?) AS res
```
```
"el"
```

ExprStringOps.startsWith

Expr("Hello").startsWith("Hel")

```
SELECT (? LIKE ? || '%') AS res
```
```
true
```

ExprStringOps.endsWith

Expr("Hello").endsWith("llo")

```
SELECT (? LIKE '%' || ?) AS res
```
```
true
```

ExprStringOps.contains

Expr("Hello").contains("ll")

```
SELECT (? LIKE '%' || ? || '%') AS res
```
```
true
```

ExprStringOps.replace

Expr("Hello").replace("ll", "rr")

```
SELECT REPLACE(?, ?, ?) AS res
```
```
"Herro"
```

ExprBlobOps

Operations that can be performed on Expr[Bytes]

ExprBlobOps.plus

Expr(Bytes("hello")) + Expr(Bytes("world"))

```
SELECT (? || ?) AS res
```
```
Bytes("helloworld")
```

ExprBlobOps.like

Expr(Bytes("hello")).like(Bytes("he%"))

```
SELECT (? LIKE ?) AS res
```
```
true
```

ExprBlobOps.length

Expr(Bytes("hello")).length

```
SELECT LENGTH(?) AS res
```
```
5
```

ExprBlobOps.octetLength

Expr(Bytes("叉烧包")).octetLength

```
SELECT OCTET_LENGTH(?) AS res
```
```
9
```

ExprBlobOps.position

Expr(Bytes("hello")).indexOf(Bytes("ll"))

```
SELECT POSITION(? IN ?) AS res
```
```
3
```

ExprBlobOps.substring

Expr(Bytes("Hello")).substring(2, 2)

```
SELECT SUBSTRING(?, ?, ?) AS res
```
```
Bytes("el")
```

ExprBlobOps.startsWith

Expr(Bytes("Hello")).startsWith(Bytes("Hel"))

```
SELECT (? LIKE ? || '%') AS res
```
```
true
```

ExprBlobOps.endsWith

Expr(Bytes("Hello")).endsWith(Bytes("llo"))

```
SELECT (? LIKE '%' || ?) AS res
```
```
true
```

ExprBlobOps.contains

Expr(Bytes("Hello")).contains(Bytes("ll"))

```
SELECT (? LIKE '%' || ? || '%') AS res
```
```
true
```

ExprMathOps

Math operations; supported by H2/Postgres/MySql/MsSql, not supported by Sqlite

ExprMathOps.power

db.power(10, 3)

```
SELECT POWER(?, ?) AS res
```
```
1000.0
```

ExprMathOps.sqrt

db.sqrt(9)

```
SELECT SQRT(?) AS res
```
```
3.0
```

ExprMathOps.ln

db.ln(16.0)

```
SELECT LN(?) AS res
```

ExprMathOps.log

db.log(2, 8)

```
SELECT LOG(?, ?) AS res
```

ExprMathOps.log10

db.log10(16.0)

```
SELECT LOG10(?) AS res
```

ExprMathOps.exp

db.exp(16.0)

```
SELECT EXP(?) AS res
```

ExprMathOps.sin

db.sin(16.0)

```
SELECT SIN(?) AS res
```

ExprMathOps.cos

db.cos(16.0)

```
SELECT COS(?) AS res
```

ExprMathOps.tan

db.tan(16.0)

```
SELECT TAN(?) AS res
```

ExprMathOps.asin

db.asin(1.0)

```
SELECT ASIN(?) AS res
```

ExprMathOps.acos

db.acos(1.0)

```
SELECT ACOS(?) AS res
```

ExprMathOps.atan

db.atan(1.0)

```
SELECT ATAN(?) AS res
```

ExprMathOps.atan2

db.atan2(16.0, 23.0)

```
SELECT ATAN2(?, ?) AS res
```

ExprMathOps.pi

db.pi

```
SELECT PI() AS res
```

ExprMathOps.degrees

db.degrees(180)

```
SELECT DEGREES(?) AS res
```

ExprMathOps.radians

db.radians(180)

```
SELECT RADIANS(?) AS res
```

DbCountOps

COUNT and COUNT(DISTINCT) aggregations

DbCountOps.countBy

Purchase.select.countBy(_.productId)

SELECT COUNT(purchase0.product_id) AS res FROM purchase purchase0

```
7
```

DbCountOps.countDistinctBy

Purchase.select.countDistinctBy(_.productId)

SELECT COUNT(DISTINCT purchase0.product_id) AS res FROM purchase purchase0

```
6
```

DbCountOps.countExpr

Purchase.select.map(_.productId).count

SELECT COUNT(purchase0.product_id) AS res FROM purchase purchase0

```
7
```

DbCountOps.countDistinctExpr

Purchase.select.map(_.productId).countDistinct

SELECT COUNT(DISTINCT purchase0.product_id) AS res FROM purchase purchase0

```
6
```

DbCountOps.countWithGroupBy

Purchase.select.groupBy(_.shippingInfoId)(agg => agg.countBy(_.productId))

SELECT purchase0.shipping_info_id AS res_0, COUNT(purchase0.product_id) AS res_1
              FROM purchase purchase0
              GROUP BY purchase0.shipping_info_id

```
Seq((1, 3), (2, 2), (3, 2))
```

DbCountOps.countDistinctWithGroupBy

Purchase.select.groupBy(_.shippingInfoId)(agg => agg.countDistinctBy(_.productId))

SELECT purchase0.shipping_info_id AS res_0, COUNT(DISTINCT purchase0.product_id) AS res_1
              FROM purchase purchase0
              GROUP BY purchase0.shipping_info_id

```
Seq((1, 3), (2, 2), (3, 2))
```

DbCountOps.countWithFilter

Purchase.select.filter(_.total > 100).countBy(_.productId)

SELECT COUNT(purchase0.product_id) AS res
              FROM purchase purchase0
              WHERE (purchase0.total > ?)

```
4
```

DbCountOps.countDistinctWithFilter

Purchase.select.filter(_.total > 100).countDistinctBy(_.productId)

SELECT COUNT(DISTINCT purchase0.product_id) AS res
              FROM purchase purchase0
              WHERE (purchase0.total > ?)

```
4
```

DbCountOps.multipleAggregatesWithCount

Purchase.select.aggregate(agg =>
  (agg.countBy(_.productId), agg.countDistinctBy(_.productId), agg.sumBy(_.total))
)

SELECT COUNT(purchase0.product_id) AS res_0, COUNT(DISTINCT purchase0.product_id) AS res_1, SUM(purchase0.total) AS res_2
              FROM purchase purchase0

```
(7, 6, 12343.2)
```

DbCountOps.countInJoin

(for {
  p <- Purchase.select
  pr <- Product.join(_.id === p.productId)
} yield pr).countBy(_.name)

SELECT COUNT(product1.name) AS res
              FROM purchase purchase0
              JOIN product product1 ON (product1.id = purchase0.product_id)

```
7
```

DbCountOps.countWithComplexExpressions.arithmetic

Purchase.select.map(_.total * 2).count

SELECT COUNT((purchase0.total * ?)) AS res
                FROM purchase purchase0

```
7
```

DbCountOps.countWithComplexExpressions.stringConcat

Product.select.map(p => p.name + " - " + p.kebabCaseName).count

SELECT COUNT(((product0.name || ?) || product0.kebab_case_name)) AS res
                FROM product product0

```
6
```

DbCountOps.countDistinctWithComplexExpressions.arithmetic

Purchase.select.map(p => p.productId + 100).countDistinct

SELECT COUNT(DISTINCT (purchase0.product_id + ?)) AS res
                FROM purchase purchase0

```
6
```

DbCountOpsOption

COUNT operations with Option types

DbCountOpsOption

OptCols.insert.batched(_.myInt, _.myInt2)(
  (None, None),
  (Some(1), Some(2)),
  (Some(3), None),
  (None, Some(4)),
  (Some(1), Some(5)),
  (Some(2), Some(2))
)

```
6
```

DbCountOpsOption.countOptionColumn.countBy

OptCols.select.countBy(_.myInt)

SELECT COUNT(opt_cols0.my_int) AS res FROM opt_cols opt_cols0

```
4
```

DbCountOpsOption.countOptionColumn.countDistinctBy

OptCols.select.countDistinctBy(_.myInt)

SELECT COUNT(DISTINCT opt_cols0.my_int) AS res FROM opt_cols opt_cols0

```
3
```

DbCountOpsOption.countExprOption.count

OptCols.select.map(_.myInt2).count

SELECT COUNT(opt_cols0.my_int2) AS res FROM opt_cols opt_cols0

```
4
```

DbCountOpsOption.countExprOption.countDistinct

OptCols.select.map(_.myInt2).countDistinct

SELECT COUNT(DISTINCT opt_cols0.my_int2) AS res FROM opt_cols opt_cols0

```
3
```

DbCountOpsOption.groupByWithOptionCount

OptCols.select
  .groupBy(_.myInt)(agg => agg.countBy(_.myInt2))

SELECT opt_cols0.my_int AS res_0, COUNT(opt_cols0.my_int2) AS res_1
              FROM opt_cols opt_cols0
              GROUP BY opt_cols0.my_int

Seq((None, 1), (Some(1), 2), (Some(2), 1), (Some(3), 0))

DbCountOpsAdvanced

Advanced COUNT operations with edge cases and expressions

DbCountOpsAdvanced.setup

OptCols.insert.batched(_.myInt, _.myInt2)(
  (Some(1), Some(1)),
  (Some(2), None),
  (Some(3), Some(3)),
  (None, Some(4)),
  (Some(5), Some(5))
)

```
5
```

DbCountOpsAdvanced.countWithNulls.nonNullCount

OptCols.select.countBy(_.myInt)

SELECT COUNT(opt_cols0.my_int) AS res FROM opt_cols opt_cols0

```
4
```

DbCountOpsAdvanced.countWithNulls.nonNullCountDistinct

OptCols.select.countDistinctBy(_.myInt)

SELECT COUNT(DISTINCT opt_cols0.my_int) AS res FROM opt_cols opt_cols0

```
4
```

DbCountOpsAdvanced.countWithNulls.secondColumnCount

OptCols.select.countBy(_.myInt2)

SELECT COUNT(opt_cols0.my_int2) AS res FROM opt_cols opt_cols0

```
4
```

DbCountOpsAdvanced.countWithNulls.secondColumnCountDistinct

OptCols.select.countDistinctBy(_.myInt2)

SELECT COUNT(DISTINCT opt_cols0.my_int2) AS res FROM opt_cols opt_cols0

```
4
```

DbCountOpsAdvanced.countWithExpressions.countArithmeticExpressions

Purchase.select.map(p => p.productId * 2).count

SELECT COUNT((purchase0.product_id * ?)) AS res FROM purchase purchase0

```
7
```

DbCountOpsAdvanced.countWithExpressions.countDistinctArithmeticExpressions

Purchase.select.map(p => p.productId + p.shippingInfoId).countDistinct

SELECT COUNT(DISTINCT (purchase0.product_id + purchase0.shipping_info_id)) AS res FROM purchase purchase0

```
6
```

DbCountOpsAdvanced.countWithModuloOperations.moduloCount

Purchase.select.map(p => p.productId % 2).countDistinct

SELECT COUNT(DISTINCT MOD(purchase0.product_id, ?)) AS res FROM purchase purchase0

```
2
```

DbCountOpsAdvanced.countWithModuloOperations.moduloWithFilter

Purchase.select.filter(_.productId > 2).map(p => p.productId % 3).countDistinct

SELECT COUNT(DISTINCT MOD(purchase0.product_id, ?)) AS res FROM purchase purchase0 WHERE (purchase0.product_id > ?)

```
3
```

DbCountOpsAdvanced.countWithGroupBy.groupByWithCount

Purchase.select.groupBy(_.shippingInfoId)(agg => agg.countBy(_.productId))

SELECT purchase0.shipping_info_id AS res_0, COUNT(purchase0.product_id) AS res_1
                FROM purchase purchase0
                GROUP BY purchase0.shipping_info_id

```
Seq((1, 3), (2, 2), (3, 2))
```

DbCountOpsAdvanced.countWithGroupBy.groupByWithCountDistinct

Purchase.select.groupBy(_.shippingInfoId)(agg => agg.countDistinctBy(_.productId))

SELECT purchase0.shipping_info_id AS res_0, COUNT(DISTINCT purchase0.product_id) AS res_1
                FROM purchase purchase0
                GROUP BY purchase0.shipping_info_id

```
Seq((1, 3), (2, 2), (3, 2))
```

DbCountOpsAdvanced.countWithComplexFilters.countWithRangeFilter

Purchase.select
  .filter(p => p.productId >= 2 && p.productId <= 4)
  .countBy(_.total)

SELECT COUNT(purchase0.total) AS res
                FROM purchase purchase0
                WHERE ((purchase0.product_id >= ?) AND (purchase0.product_id <= ?))

```
3
```

DbCountOpsAdvanced.countWithComplexFilters.countWithDecimalFilter

Purchase.select
  .filter(_.total > 100)
  .countDistinctBy(_.productId)

SELECT COUNT(DISTINCT purchase0.product_id) AS res
                FROM purchase purchase0
                WHERE (purchase0.total > ?)

```
4
```

DbCountOpsAdvanced.countWithAdvancedPredicates.countWithComplexFilter

Purchase.select
  .filter(p => p.productId > 1 && p.shippingInfoId <= 2)
  .countDistinctBy(_.productId)

SELECT COUNT(DISTINCT purchase0.product_id) AS res
                FROM purchase purchase0
                WHERE ((purchase0.product_id > ?) AND (purchase0.shipping_info_id <= ?))

```
4
```

DataTypes

Basic operations on all the data types that ScalaSql supports mapping between Database types and Scala types

DataTypes.constant

This example demonstrates a range of different data types being written and read back via ScalaSQL

object MyEnum extends Enumeration {
  val foo, bar, baz = Value

  implicit def make: String => Value = withName
}
case class DataTypes[T[_]](
    myTinyInt: T[Byte],
    mySmallInt: T[Short],
    myInt: T[Int],
    myBigInt: T[Long],
    myDouble: T[Double],
    myBoolean: T[Boolean],
    myLocalDate: T[LocalDate],
    myLocalTime: T[LocalTime],
    myLocalDateTime: T[LocalDateTime],
    myUtilDate: T[Date],
    myInstant: T[Instant],
    myVarBinary: T[geny.Bytes],
    myUUID: T[java.util.UUID],
    myEnum: T[MyEnum.Value]
)

object DataTypes extends Table[DataTypes]

val value = DataTypes[Sc](
  myTinyInt = 123.toByte,
  mySmallInt = 12345.toShort,
  myInt = 12345678,
  myBigInt = 12345678901L,
  myDouble = 3.14,
  myBoolean = false,
  myLocalDate = LocalDate.parse("2023-12-20"),
  myLocalTime = LocalTime.parse("10:15:30"),
  myLocalDateTime = LocalDateTime.parse("2011-12-03T10:15:30"),
  myUtilDate = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSS").parse("2011-12-03T10:15:30.000"),
  myInstant = Instant.parse("2011-12-03T10:15:30Z"),
  myVarBinary = new geny.Bytes(Array[Byte](1, 2, 3, 4, 5, 6, 7, 8)),
  myUUID = new java.util.UUID(1234567890L, 9876543210L),
  myEnum = MyEnum.bar
)

val value2 = DataTypes[Sc](
  67.toByte,
  mySmallInt = 32767.toShort,
  myInt = 12345678,
  myBigInt = 9876543210L,
  myDouble = 2.71,
  myBoolean = true,
  myLocalDate = LocalDate.parse("2020-02-22"),
  myLocalTime = LocalTime.parse("03:05:01"),
  myLocalDateTime = LocalDateTime.parse("2021-06-07T02:01:03"),
  myUtilDate = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSS").parse("2021-06-07T02:01:03.000"),
  myInstant = Instant.parse("2021-06-07T02:01:03Z"),
  myVarBinary = new geny.Bytes(Array[Byte](9, 8, 7, 6, 5, 4, 3, 2)),
  myUUID = new java.util.UUID(9876543210L, 1234567890L),
  myEnum = MyEnum.baz
)

db.run(
  DataTypes.insert.columns(
    _.myTinyInt := value.myTinyInt,
    _.mySmallInt := value.mySmallInt,
    _.myInt := value.myInt,
    _.myBigInt := value.myBigInt,
    _.myDouble := value.myDouble,
    _.myBoolean := value.myBoolean,
    _.myLocalDate := value.myLocalDate,
    _.myLocalTime := value.myLocalTime,
    _.myLocalDateTime := value.myLocalDateTime,
    _.myUtilDate := value.myUtilDate,
    _.myInstant := value.myInstant,
    _.myVarBinary := value.myVarBinary,
    _.myUUID := value.myUUID,
    _.myEnum := value.myEnum
  )
) ==> 1
db.run(
  DataTypes.insert.columns(
    _.myTinyInt := value2.myTinyInt,
    _.mySmallInt := value2.mySmallInt,
    _.myInt := value2.myInt,
    _.myBigInt := value2.myBigInt,
    _.myDouble := value2.myDouble,
    _.myBoolean := value2.myBoolean,
    _.myLocalDate := value2.myLocalDate,
    _.myLocalTime := value2.myLocalTime,
    _.myLocalDateTime := value2.myLocalDateTime,
    _.myUtilDate := value2.myUtilDate,
    _.myInstant := value2.myInstant,
    _.myVarBinary := value2.myVarBinary,
    _.myUUID := value2.myUUID,
    _.myEnum := value2.myEnum
  )
) ==> 1

db.run(DataTypes.select) ==> Seq(value, value2)

DataTypes.nonRoundTrip

In general, databases do not store timezones and offsets together with their timestamps: "TIMESTAMP WITH TIMEZONE" is a lie and it actually stores UTC and renders to whatever timezone the client queries it from. Thus values of type OffsetDateTime can preserve their instant, but cannot be round-tripped preserving the offset.

case class NonRoundTripTypes[T[_]](
    myZonedDateTime: T[ZonedDateTime],
    myOffsetDateTime: T[OffsetDateTime]
)

object NonRoundTripTypes extends Table[NonRoundTripTypes]

val value = NonRoundTripTypes[Sc](
  myZonedDateTime = ZonedDateTime.parse("2011-12-03T10:15:30+01:00[Europe/Paris]"),
  myOffsetDateTime = OffsetDateTime.parse("2011-12-03T10:15:30+00:00")
)

def normalize(v: NonRoundTripTypes[Sc]) = v.copy[Sc](
  myZonedDateTime = v.myZonedDateTime.withZoneSameInstant(ZoneId.systemDefault),
  myOffsetDateTime = v.myOffsetDateTime.withOffsetSameInstant(OffsetDateTime.now.getOffset)
)

db.run(
  NonRoundTripTypes.insert.columns(
    _.myOffsetDateTime := value.myOffsetDateTime,
    _.myZonedDateTime := value.myZonedDateTime
  )
) ==> 1

db.run(NonRoundTripTypes.select).map(normalize) ==> Seq(normalize(value))

DataTypes.enclosing

You can nest case classes in other case classes to DRY up common sets of table columns. These nested case classes have their columns flattened out into the enclosing case class's columns, such that at the SQL level it is all flattened out without nesting.

// case class Nested[T[_]](
//   fooId: T[Int],
//   myBoolean: T[Boolean],
// )
// object Nested extends Table[Nested]
//
// case class Enclosing[T[_]](
//     barId: T[Int],
//     myString: T[String],
//     foo: Nested[T]
// )
// object Enclosing extends Table[Enclosing]
val value1 = Enclosing[Sc](
  barId = 1337,
  myString = "hello",
  foo = Nested[Sc](
    fooId = 271828,
    myBoolean = true
  )
)
val value2 = Enclosing[Sc](
  barId = 31337,
  myString = "world",
  foo = Nested[Sc](
    fooId = 1618,
    myBoolean = false
  )
)

val insertColumns = Enclosing.insert.columns(
  _.barId := value1.barId,
  _.myString := value1.myString,
  _.foo.fooId := value1.foo.fooId,
  _.foo.myBoolean := value1.foo.myBoolean
)
db.renderSql(insertColumns) ==>
  "INSERT INTO enclosing (bar_id, my_string, foo_id, my_boolean) VALUES (?, ?, ?, ?)"

db.run(insertColumns) ==> 1

val insertValues = Enclosing.insert.values(value2)
db.renderSql(insertValues) ==>
  "INSERT INTO enclosing (bar_id, my_string, foo_id, my_boolean) VALUES (?, ?, ?, ?)"

db.run(insertValues) ==> 1

db.renderSql(Enclosing.select) ==> """
          SELECT
            enclosing0.bar_id AS bar_id,
            enclosing0.my_string AS my_string,
            enclosing0.foo_id AS foo_id,
            enclosing0.my_boolean AS my_boolean
          FROM enclosing enclosing0
        """

db.run(Enclosing.select) ==> Seq(value1, value2)

DataTypes.JoinNullable proper type mapping

case class A[T[_]](id: T[Int], bId: T[Option[Int]])
object A extends Table[A]

object Custom extends Enumeration {
  val Foo, Bar = Value

  implicit def make: String => Value = withName
}

case class B[T[_]](id: T[Int], custom: T[Custom.Value])
object B extends Table[B]
db.run(A.insert.columns(_.id := 1, _.bId := None))
val result = db.run(A.select.leftJoin(B)(_.id === _.id).single)
result._2 ==> None

DataTypes.enclosing - with SimpleTable

Important: When using nested case classes with SimpleTable, make sure to extend SimpleTable.Nested in the nested class.

// case class Nested(
//   fooId: Int,
//   myBoolean: Boolean,
// ) extends SimpleTable.Nested
// object Nested extends SimpleTable[Nested]
//
// case class Enclosing(
//     barId: Int,
//     myString: String,
//     foo: Nested
// )
// object Enclosing extends SimpleTable[Enclosing]
val value1 = Enclosing(
  barId = 1337,
  myString = "hello",
  foo = Nested(
    fooId = 271828,
    myBoolean = true
  )
)
val value2 = Enclosing(
  barId = 31337,
  myString = "world",
  foo = Nested(
    fooId = 1618,
    myBoolean = false
  )
)

val insertColumns = Enclosing.insert.columns(
  _.barId := value1.barId,
  _.myString := value1.myString,
  _.foo.fooId := value1.foo.fooId,
  _.foo.myBoolean := value1.foo.myBoolean
)
db.renderSql(insertColumns) ==>
  "INSERT INTO enclosing (bar_id, my_string, foo_id, my_boolean) VALUES (?, ?, ?, ?)"

db.run(insertColumns) ==> 1

val insertValues = Enclosing.insert.values(value2)
db.renderSql(insertValues) ==>
  "INSERT INTO enclosing (bar_id, my_string, foo_id, my_boolean) VALUES (?, ?, ?, ?)"

db.run(insertValues) ==> 1

db.renderSql(Enclosing.select) ==> """
          SELECT
            enclosing0.bar_id AS bar_id,
            enclosing0.my_string AS my_string,
            enclosing0.foo_id AS foo_id,
            enclosing0.my_boolean AS my_boolean
          FROM enclosing enclosing0
        """

db.run(Enclosing.select) ==> Seq(value1, value2)

Optional

Queries using columns that may be NULL, Expr[Option[T]] or Option[T] in Scala

Optional

OptCols.insert.batched(_.myInt, _.myInt2)(
  (None, None),
  (Some(1), Some(2)),
  (Some(3), None),
  (None, Some(4))
)

```
4
```

Optional.selectAll

Nullable columns are modelled as T[Option[V]] fields on your case class, and are returned to you as Option[V] values when you run a query. These can be Some or None

OptCols.select

SELECT
  opt_cols0.my_int AS my_int,
  opt_cols0.my_int2 AS my_int2
FROM opt_cols opt_cols0

Seq(
  OptCols[Sc](None, None),
  OptCols[Sc](Some(1), Some(2)),
  OptCols[Sc](Some(3), None),
  OptCols[Sc](None, Some(4))
)

Optional.groupByMaxGet

Some aggregates return Expr[Option[V]]s, et.c. .maxByOpt

OptCols.select.groupBy(_.myInt)(_.maxByOpt(_.myInt2.get))

SELECT opt_cols0.my_int AS res_0, MAX(opt_cols0.my_int2) AS res_1
FROM opt_cols opt_cols0
GROUP BY opt_cols0.my_int

Seq(None -> Some(4), Some(1) -> Some(2), Some(3) -> None)

Optional.isDefined

.isDefined on Expr[Option[V]] translates to a SQL IS NOT NULL check

OptCols.select.filter(_.myInt.isDefined)

SELECT
  opt_cols0.my_int AS my_int,
  opt_cols0.my_int2 AS my_int2
FROM opt_cols opt_cols0
WHERE (opt_cols0.my_int IS NOT NULL)

Seq(OptCols[Sc](Some(1), Some(2)), OptCols[Sc](Some(3), None))

Optional.isEmpty

.isEmpty on Expr[Option[V]] translates to a SQL IS NULL check

OptCols.select.filter(_.myInt.isEmpty)

SELECT
  opt_cols0.my_int AS my_int,
  opt_cols0.my_int2 AS my_int2
FROM opt_cols opt_cols0
WHERE (opt_cols0.my_int IS NULL)

Seq(OptCols[Sc](None, None), OptCols[Sc](None, Some(4)))

Optional.sqlEquals.nonOptionHit

Backticked = equality in ScalaSQL translates to a raw = in SQL. This follows SQL NULL semantics, meaning that None = None returns false rather than true

OptCols.select.filter(_.myInt `=` 1)

SELECT
  opt_cols0.my_int AS my_int,
  opt_cols0.my_int2 AS my_int2
FROM opt_cols opt_cols0
WHERE (opt_cols0.my_int = ?)

```
Seq(OptCols[Sc](Some(1), Some(2)))
```

Optional.sqlEquals.nonOptionMiss

OptCols.select.filter(_.myInt `=` 2)

SELECT
  opt_cols0.my_int AS my_int,
  opt_cols0.my_int2 AS my_int2
FROM opt_cols opt_cols0
WHERE (opt_cols0.my_int = ?)

```
Seq[OptCols[Sc]]()
```

Optional.sqlEquals.optionMiss

OptCols.select.filter(_.myInt `=` Option.empty[Int])

SELECT
  opt_cols0.my_int AS my_int,
  opt_cols0.my_int2 AS my_int2
FROM opt_cols opt_cols0
WHERE (opt_cols0.my_int = ?)

```
Seq[OptCols[Sc]]()
```

Optional.scalaEquals.someHit

=== equality in ScalaSQL translates to a IS NOT DISTINCT in SQL. This roughly follows Scala == semantics, meaning None === None returns true

OptCols.select.filter(_.myInt === Option(1))

SELECT
  opt_cols0.my_int AS my_int,
  opt_cols0.my_int2 AS my_int2
FROM opt_cols opt_cols0
WHERE (opt_cols0.my_int IS NOT DISTINCT FROM ?)

```
Seq(OptCols[Sc](Some(1), Some(2)))
```

Optional.scalaEquals.noneHit

OptCols.select.filter(_.myInt === Option.empty[Int])

SELECT
  opt_cols0.my_int AS my_int,
  opt_cols0.my_int2 AS my_int2
FROM opt_cols opt_cols0
WHERE (opt_cols0.my_int IS NOT DISTINCT FROM ?)

Seq(OptCols[Sc](None, None), OptCols[Sc](None, Some(4)))

Optional.scalaEquals.notEqualsSome

OptCols.select.filter(_.myInt !== Option(1))

SELECT
  opt_cols0.my_int AS my_int,
  opt_cols0.my_int2 AS my_int2
FROM opt_cols opt_cols0
WHERE (opt_cols0.my_int IS DISTINCT FROM ?)

Seq(
  OptCols[Sc](None, None),
  OptCols[Sc](Some(3), None),
  OptCols[Sc](None, Some(value = 4))
)

Optional.scalaEquals.notEqualsNone

OptCols.select.filter(_.myInt !== Option.empty[Int])

SELECT
  opt_cols0.my_int AS my_int,
  opt_cols0.my_int2 AS my_int2
FROM opt_cols opt_cols0
WHERE (opt_cols0.my_int IS DISTINCT FROM ?)

Seq(
  OptCols[Sc](Some(1), Some(2)),
  OptCols[Sc](Some(3), None)
)

Optional.map

You can use operators like .map and .flatMap to work with your Expr[Option[V]] values. These roughly follow the semantics that you would be familiar with from Scala.

OptCols.select.map(d => d.copy[Expr](myInt = d.myInt.map(_ + 10)))

SELECT
  (opt_cols0.my_int + ?) AS my_int,
  opt_cols0.my_int2 AS my_int2
FROM opt_cols opt_cols0

Seq(
  OptCols[Sc](None, None),
  OptCols[Sc](Some(11), Some(2)),
  OptCols[Sc](Some(13), None),
  OptCols[Sc](None, Some(4))
)

Optional.map2

OptCols.select.map(_.myInt.map(_ + 10))

SELECT (opt_cols0.my_int + ?) AS res FROM opt_cols opt_cols0

```
Seq(None, Some(11), Some(13), None)
```

Optional.flatMap

OptCols.select
  .map(d => d.copy[Expr](myInt = d.myInt.flatMap(v => d.myInt2.map(v2 => v + v2 + 10))))

SELECT
  ((opt_cols0.my_int + opt_cols0.my_int2) + ?) AS my_int,
  opt_cols0.my_int2 AS my_int2
FROM opt_cols opt_cols0

Seq(
  OptCols[Sc](None, None),
  OptCols[Sc](Some(13), Some(2)),
  // because my_int2 is added to my_int, and my_int2 is null, my_int becomes null too
  OptCols[Sc](None, None),
  OptCols[Sc](None, Some(4))
)

Optional.mapGet

You can use .get to turn an Expr[Option[V]] into an Expr[V]. This follows SQL semantics, such that NULLs anywhere in that selected column automatically will turn the whole column None (if it's an Expr[Option[V]] column) or null (if it's not an optional column)

OptCols.select.map(d => d.copy[Expr](myInt = d.myInt.map(_ + d.myInt2.get + 1)))

SELECT
  ((opt_cols0.my_int + opt_cols0.my_int2) + ?) AS my_int,
  opt_cols0.my_int2 AS my_int2
FROM opt_cols opt_cols0

Seq(
  OptCols[Sc](None, None),
  OptCols[Sc](Some(4), Some(2)),
  // because my_int2 is added to my_int, and my_int2 is null, my_int becomes null too
  OptCols[Sc](None, None),
  OptCols[Sc](None, Some(4))
)

Optional.rawGet

OptCols.select.map(d => d.copy[Expr](myInt = d.myInt.get + d.myInt2.get + 1))

SELECT
  ((opt_cols0.my_int + opt_cols0.my_int2) + ?) AS my_int,
  opt_cols0.my_int2 AS my_int2
FROM opt_cols opt_cols0

Seq(
  OptCols[Sc](None, None),
  OptCols[Sc](Some(4), Some(2)),
  // because my_int2 is added to my_int, and my_int2 is null, my_int becomes null too
  OptCols[Sc](None, None),
  OptCols[Sc](None, Some(4))
)

Optional.getOrElse

OptCols.select.map(d => d.copy[Expr](myInt = d.myInt.getOrElse(-1)))

SELECT
  COALESCE(opt_cols0.my_int, ?) AS my_int,
  opt_cols0.my_int2 AS my_int2
FROM opt_cols opt_cols0

Seq(
  OptCols[Sc](Some(-1), None),
  OptCols[Sc](Some(1), Some(2)),
  OptCols[Sc](Some(3), None),
  OptCols[Sc](Some(-1), Some(4))
)

Optional.orElse

OptCols.select.map(d => d.copy[Expr](myInt = d.myInt.orElse(d.myInt2)))

SELECT
  COALESCE(opt_cols0.my_int, opt_cols0.my_int2) AS my_int,
  opt_cols0.my_int2 AS my_int2
FROM opt_cols opt_cols0

Seq(
  OptCols[Sc](None, None),
  OptCols[Sc](Some(1), Some(2)),
  OptCols[Sc](Some(3), None),
  OptCols[Sc](Some(4), Some(4))
)

Optional.filter

.filter follows normal Scala semantics, and translates to a CASE/WHEN (foo)/ELSE NULL

OptCols.select.map(d => d.copy[Expr](myInt = d.myInt.filter(_ < 2)))

SELECT
  CASE
    WHEN (opt_cols0.my_int < ?) THEN opt_cols0.my_int
    ELSE NULL
  END AS my_int,
  opt_cols0.my_int2 AS my_int2
FROM opt_cols opt_cols0

Seq(
  OptCols[Sc](None, None),
  OptCols[Sc](Some(1), Some(2)),
  OptCols[Sc](None, None),
  OptCols[Sc](None, Some(4))
)

Optional.sorting.nullsLast

.nullsLast and .nullsFirst translate to SQL NULLS LAST and NULLS FIRST clauses

OptCols.select.sortBy(_.myInt).nullsLast

SELECT opt_cols0.my_int AS my_int, opt_cols0.my_int2 AS my_int2
FROM opt_cols opt_cols0
ORDER BY my_int NULLS LAST

Seq(
  OptCols[Sc](Some(1), Some(2)),
  OptCols[Sc](Some(3), None),
  OptCols[Sc](None, None),
  OptCols[Sc](None, Some(4))
)

Optional.sorting.nullsFirst

OptCols.select.sortBy(_.myInt).nullsFirst

SELECT opt_cols0.my_int AS my_int, opt_cols0.my_int2 AS my_int2
FROM opt_cols opt_cols0
ORDER BY my_int NULLS FIRST

Seq(
  OptCols[Sc](None, None),
  OptCols[Sc](None, Some(4)),
  OptCols[Sc](Some(1), Some(2)),
  OptCols[Sc](Some(3), None)
)

Optional.sorting.ascNullsLast

OptCols.select.sortBy(_.myInt).asc.nullsLast

SELECT opt_cols0.my_int AS my_int, opt_cols0.my_int2 AS my_int2
FROM opt_cols opt_cols0
ORDER BY my_int ASC NULLS LAST

Seq(
  OptCols[Sc](Some(1), Some(2)),
  OptCols[Sc](Some(3), None),
  OptCols[Sc](None, None),
  OptCols[Sc](None, Some(4))
)

Optional.sorting.ascNullsFirst

OptCols.select.sortBy(_.myInt).asc.nullsFirst

SELECT opt_cols0.my_int AS my_int, opt_cols0.my_int2 AS my_int2
FROM opt_cols opt_cols0
ORDER BY my_int ASC NULLS FIRST

Seq(
  OptCols[Sc](None, None),
  OptCols[Sc](None, Some(4)),
  OptCols[Sc](Some(1), Some(2)),
  OptCols[Sc](Some(3), None)
)

Optional.sorting.descNullsLast

OptCols.select.sortBy(_.myInt).desc.nullsLast

SELECT opt_cols0.my_int AS my_int, opt_cols0.my_int2 AS my_int2
FROM opt_cols opt_cols0
ORDER BY my_int DESC NULLS LAST

Seq(
  OptCols[Sc](Some(3), None),
  OptCols[Sc](Some(1), Some(2)),
  OptCols[Sc](None, None),
  OptCols[Sc](None, Some(4))
)

Optional.sorting.descNullsFirst

OptCols.select.sortBy(_.myInt).desc.nullsFirst

SELECT opt_cols0.my_int AS my_int, opt_cols0.my_int2 AS my_int2
FROM opt_cols opt_cols0
ORDER BY my_int DESC NULLS FIRST

Seq(
  OptCols[Sc](None, None),
  OptCols[Sc](None, Some(4)),
  OptCols[Sc](Some(3), None),
  OptCols[Sc](Some(1), Some(2))
)

Optional.sorting.roundTripOptionalValues

This example demonstrates a range of different data types being written as options, both with Some(v) and None values

object MyEnum extends Enumeration {
  val foo, bar, baz = Value

  implicit def make: String => Value = withName
}
case class OptDataTypes[T[_]](
    myTinyInt: T[Option[Byte]],
    mySmallInt: T[Option[Short]],
    myInt: T[Option[Int]],
    myBigInt: T[Option[Long]],
    myDouble: T[Option[Double]],
    myBoolean: T[Option[Boolean]],
    myLocalDate: T[Option[LocalDate]],
    myLocalTime: T[Option[LocalTime]],
    myLocalDateTime: T[Option[LocalDateTime]],
    myUtilDate: T[Option[Date]],
    myInstant: T[Option[Instant]],
    myVarBinary: T[Option[geny.Bytes]],
    myUUID: T[Option[java.util.UUID]],
    myEnum: T[Option[MyEnum.Value]]
)

object OptDataTypes extends Table[OptDataTypes] {
  override def tableName: String = "data_types"
}

val rowSome = OptDataTypes[Sc](
  myTinyInt = Some(123.toByte),
  mySmallInt = Some(12345.toShort),
  myInt = Some(12345678),
  myBigInt = Some(12345678901L),
  myDouble = Some(3.14),
  myBoolean = Some(true),
  myLocalDate = Some(LocalDate.parse("2023-12-20")),
  myLocalTime = Some(LocalTime.parse("10:15:30")),
  myLocalDateTime = Some(LocalDateTime.parse("2011-12-03T10:15:30")),
  myUtilDate = Some(
    new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSS").parse("2011-12-03T10:15:30.000")
  ),
  myInstant = Some(Instant.parse("2011-12-03T10:15:30Z")),
  myVarBinary = Some(new geny.Bytes(Array[Byte](1, 2, 3, 4, 5, 6, 7, 8))),
  myUUID = Some(new java.util.UUID(1234567890L, 9876543210L)),
  myEnum = Some(MyEnum.bar)
)
val rowSome2 = OptDataTypes[Sc](
  myTinyInt = Some(67.toByte),
  mySmallInt = Some(32767.toShort),
  myInt = Some(23456789),
  myBigInt = Some(9876543210L),
  myDouble = Some(2.71),
  myBoolean = Some(false),
  myLocalDate = Some(LocalDate.parse("2020-02-22")),
  myLocalTime = Some(LocalTime.parse("03:05:01")),
  myLocalDateTime = Some(LocalDateTime.parse("2021-06-07T02:01:03")),
  myUtilDate = Some(
    new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSS").parse("2021-06-07T02:01:03.000")
  ),
  myInstant = Some(Instant.parse("2021-06-07T02:01:03Z")),
  myVarBinary = Some(new geny.Bytes(Array[Byte](9, 8, 7, 6, 5, 4, 3, 2))),
  myUUID = Some(new java.util.UUID(9876543210L, 1234567890L)),
  myEnum = Some(MyEnum.baz)
)

val rowNone = OptDataTypes[Sc](
  myTinyInt = None,
  mySmallInt = None,
  myInt = None,
  myBigInt = None,
  myDouble = None,
  myBoolean = None,
  myLocalDate = None,
  myLocalTime = None,
  myLocalDateTime = None,
  myUtilDate = None,
  myInstant = None,
  myVarBinary = None,
  myUUID = None,
  myEnum = None
)
db.run(
  OptDataTypes.insert.values(rowSome, rowSome2, rowNone)
) ==> 3

db.run(OptDataTypes.select) ==> Seq(rowSome, rowSome2, rowNone)

Optional.filter - with SimpleTable

.filter follows normal Scala semantics, and translates to a CASE/WHEN (foo)/ELSE NULL

OptCols.select.map(d => d.updates(_.myInt(_.filter(_ < 2))))

SELECT
  CASE
    WHEN (opt_cols0.my_int < ?) THEN opt_cols0.my_int
    ELSE NULL
  END AS my_int,
  opt_cols0.my_int2 AS my_int2
FROM opt_cols opt_cols0

Seq(
  OptCols(None, None),
  OptCols(Some(1), Some(2)),
  OptCols(None, None),
  OptCols(None, Some(4))
)

Optional.getOrElse - with SimpleTable

OptCols.select.map(d => d.updates(_.myInt(_.getOrElse(-1))))

SELECT
  COALESCE(opt_cols0.my_int, ?) AS my_int,
  opt_cols0.my_int2 AS my_int2
FROM opt_cols opt_cols0

Seq(
  OptCols(Some(-1), None),
  OptCols(Some(1), Some(2)),
  OptCols(Some(3), None),
  OptCols(Some(-1), Some(4))
)

Optional.rawGet - with SimpleTable

OptCols.select.map(d => d.updates(_.myInt := d.myInt.get + d.myInt2.get + 1))

SELECT
  ((opt_cols0.my_int + opt_cols0.my_int2) + ?) AS my_int,
  opt_cols0.my_int2 AS my_int2
FROM opt_cols opt_cols0

Seq(
  OptCols(None, None),
  OptCols(Some(4), Some(2)),
  // because my_int2 is added to my_int, and my_int2 is null, my_int becomes null too
  OptCols(None, None),
  OptCols(None, Some(4))
)

Optional.orElse - with SimpleTable

OptCols.select.map(d => d.updates(_.myInt(_.orElse(d.myInt2))))

SELECT
  COALESCE(opt_cols0.my_int, opt_cols0.my_int2) AS my_int,
  opt_cols0.my_int2 AS my_int2
FROM opt_cols opt_cols0

Seq(
  OptCols(None, None),
  OptCols(Some(1), Some(2)),
  OptCols(Some(3), None),
  OptCols(Some(4), Some(4))
)

Optional.flatMap - with SimpleTable

OptCols.select
  .map(d => d.updates(_.myInt(_.flatMap(v => d.myInt2.map(v2 => v + v2 + 10)))))

SELECT
  ((opt_cols0.my_int + opt_cols0.my_int2) + ?) AS my_int,
  opt_cols0.my_int2 AS my_int2
FROM opt_cols opt_cols0

Seq(
  OptCols(None, None),
  OptCols(Some(13), Some(2)),
  // because my_int2 is added to my_int, and my_int2 is null, my_int becomes null too
  OptCols(None, None),
  OptCols(None, Some(4))
)

Optional.map - with SimpleTable

You can use operators like .map and .flatMap to work with your Expr[Option[V]] values. These roughly follow the semantics that you would be familiar with from Scala.

OptCols.select.map(d => d.updates(_.myInt(_.map(_ + 10))))

SELECT
  (opt_cols0.my_int + ?) AS my_int,
  opt_cols0.my_int2 AS my_int2
FROM opt_cols opt_cols0

Seq(
  OptCols(None, None),
  OptCols(Some(11), Some(2)),
  OptCols(Some(13), None),
  OptCols(None, Some(4))
)

PostgresDialect

Operations specific to working with Postgres Databases

PostgresDialect.distinctOn

ScalaSql's Postgres dialect provides the .distinctOn operator, which translates into a SQL DISTINCT ON clause

Purchase.select.distinctOn(_.shippingInfoId).sortBy(_.shippingInfoId).desc

SELECT
  DISTINCT ON (purchase0.shipping_info_id) purchase0.id AS id,
  purchase0.shipping_info_id AS shipping_info_id,
  purchase0.product_id AS product_id,
  purchase0.count AS count,
  purchase0.total AS total
FROM purchase purchase0
ORDER BY shipping_info_id DESC

Seq(
  Purchase[Sc](6, 3, 1, 5, 44.4),
  Purchase[Sc](4, 2, 4, 4, 493.8),
  Purchase[Sc](2, 1, 2, 3, 900.0)
)

PostgresDialect.forUpdate

ScalaSql's Postgres dialect provides the .forUpdate operator, which translates into a SQL SELECT ... FOR UPDATE clause

Invoice.select.filter(_.id === 1).forUpdate

SELECT
  invoice0.id AS id,
  invoice0.total AS total,
  invoice0.vendor_name AS vendor_name
FROM otherschema.invoice invoice0
WHERE (invoice0.id = ?)
FOR UPDATE

Seq(
  Invoice[Sc](1, 150.4, "Siemens")
)

PostgresDialect.ltrim2

Expr("xxHellox").ltrim("x")

```
SELECT LTRIM(?, ?) AS res
```
```
"Hellox"
```

PostgresDialect.rtrim2

Expr("xxHellox").rtrim("x")

```
SELECT RTRIM(?, ?) AS res
```
```
"xxHello"
```

PostgresDialect.reverse

Expr("Hello").reverse

```
SELECT REVERSE(?) AS res
```
```
"olleH"
```

PostgresDialect.lpad

Expr("Hello").lpad(10, "xy")

```
SELECT LPAD(?, ?, ?) AS res
```
```
"xyxyxHello"
```

PostgresDialect.rpad

Expr("Hello").rpad(10, "xy")

```
SELECT RPAD(?, ?, ?) AS res
```
```
"Helloxyxyx"
```

PostgresDialect.concat

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

```
SELECT CONCAT(?, ?, ?, ?) AS res
```
```
"i am cow1337"
```

PostgresDialect.concatWs

db.concatWs(" ", "i", "am", "cow", 1337)

```
SELECT CONCAT_WS(?, ?, ?, ?, ?) AS res
```
```
"i am cow 1337"
```

PostgresDialect.format

db.format("i am cow %s hear me moo %s", 1337, 31337)

```
SELECT FORMAT(?, ?, ?) AS res
```
```
"i am cow 1337 hear me moo 31337"
```

PostgresDialect.random

db.random

```
SELECT RANDOM() AS res
```

MySqlDialect

Operations specific to working with MySql Databases

MySqlDialect.forUpdate

ScalaSql's MySql dialect provides the .forUpdate operator, which translates into a SQL SELECT ... FOR UPDATE clause

Buyer.select.filter(_.id === 1).forUpdate

SELECT
  buyer0.id AS id,
  buyer0.name AS name,
  buyer0.date_of_birth AS date_of_birth
FROM buyer buyer0
WHERE (buyer0.id = ?)
FOR UPDATE

Seq(
  Buyer[Sc](1, "James Bond", LocalDate.parse("2001-02-03"))
)

MySqlDialect.reverse

Expr("Hello").reverse

```
SELECT REVERSE(?) AS res
```
```
"olleH"
```

MySqlDialect.lpad

Expr("Hello").lpad(10, "xy")

```
SELECT LPAD(?, ?, ?) AS res
```
```
"xyxyxHello"
```

MySqlDialect.rpad

Expr("Hello").rpad(10, "xy")

```
SELECT RPAD(?, ?, ?) AS res
```
```
"Helloxyxyx"
```

MySqlDialect.conflict.ignore

Buyer.insert
  .columns(
    _.name := "test buyer",
    _.dateOfBirth := LocalDate.parse("2023-09-09"),
    _.id := 1 // This should cause a primary key conflict
  )
  .onConflictUpdate(x => x.id := x.id)

INSERT INTO buyer (name, date_of_birth, id) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE id = buyer.id

```
1
```

MySqlDialect.conflict.update

Buyer.insert
  .columns(
    _.name := "test buyer",
    _.dateOfBirth := LocalDate.parse("2023-09-09"),
    _.id := 1 // This should cause a primary key conflict
  )
  .onConflictUpdate(_.name := "TEST BUYER CONFLICT")

INSERT INTO buyer (name, date_of_birth, id) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE name = ?

```
2
```

Buyer.select

Seq(
  Buyer[Sc](1, "TEST BUYER CONFLICT", LocalDate.parse("2001-02-03")),
  Buyer[Sc](2, "叉烧包", LocalDate.parse("1923-11-12")),
  Buyer[Sc](3, "Li Haoyi", LocalDate.parse("1965-08-09"))
)

MySqlDialect.conflict.updateComputed

Buyer.insert
  .columns(
    _.name := "test buyer",
    _.dateOfBirth := LocalDate.parse("2023-09-09"),
    _.id := 1 // This should cause a primary key conflict
  )
  .onConflictUpdate(v => v.name := v.name.toUpperCase)

INSERT INTO buyer (name, date_of_birth, id) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE name = UPPER(buyer.name)

```
2
```

Buyer.select

Seq(
  Buyer[Sc](1, "JAMES BOND", LocalDate.parse("2001-02-03")),
  Buyer[Sc](2, "叉烧包", LocalDate.parse("1923-11-12")),
  Buyer[Sc](3, "Li Haoyi", LocalDate.parse("1965-08-09"))
)

MySqlDialect.concat

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

```
SELECT CONCAT(?, ?, ?, ?) AS res
```
```
"i am cow1337"
```

MySqlDialect.concatWs

db.concatWs(" ", "i", "am", "cow", 1337)

```
SELECT CONCAT_WS(?, ?, ?, ?, ?) AS res
```
```
"i am cow 1337"
```

MySqlDialect.rand

db.rand

```
SELECT RAND() AS res
```

SqliteDialect

Operations specific to working with Sqlite Databases

SqliteDialect.ltrim2

Expr("xxHellox").ltrim("x")

```
SELECT LTRIM(?, ?) AS res
```
```
"Hellox"
```

SqliteDialect.rtrim2

Expr("xxHellox").rtrim("x")

```
SELECT RTRIM(?, ?) AS res
```
```
"xxHello"
```

SqliteDialect.glob

Expr("*cop*").glob("roflcopter")

```
SELECT GLOB(?, ?) AS res
```
```
true
```

SqliteDialect.changes

db.changes

```
SELECT CHANGES() AS res
```

SqliteDialect.totalChanges

db.totalChanges

```
SELECT TOTAL_CHANGES() AS res
```

SqliteDialect.typeOf

db.typeOf(123)

```
SELECT TYPEOF(?) AS res
```
```
"integer"
```

SqliteDialect.lastInsertRowId

db.lastInsertRowId

```
SELECT LAST_INSERT_ROWID() AS res
```

SqliteDialect.char

db.char(108, 111, 108)

```
SELECT CHAR(?, ?, ?) AS res
```
```
"lol"
```

SqliteDialect.format

db.format("i am cow %s hear me moo %s", 1337, 31337)

```
SELECT FORMAT(?, ?, ?) AS res
```
```
"i am cow 1337 hear me moo 31337"
```

SqliteDialect.hex

db.hex(new geny.Bytes(Array(1, 10, 100, -127)))

```
SELECT HEX(?) AS res
```
```
"010A6481"
```

SqliteDialect.unhex

db.unhex("010A6481")

```
SELECT UNHEX(?) AS res
```

new geny.Bytes(Array(1, 10, 100, -127))

SqliteDialect.zeroBlob

db.zeroBlob(16)

```
SELECT ZEROBLOB(?) AS res
```
```
new geny.Bytes(new Array[Byte](16))
```

H2Dialect

Operations specific to working with H2 Databases

H2Dialect.ltrim2

Expr("xxHellox").ltrim("x")

```
SELECT LTRIM(?, ?) AS res
```
```
"Hellox"
```

H2Dialect.rtrim2

Expr("xxHellox").rtrim("x")

```
SELECT RTRIM(?, ?) AS res
```
```
"xxHello"
```

H2Dialect.lpad

Expr("Hello").lpad(10, "xy")

```
SELECT LPAD(?, ?, ?) AS res
```
```
"xxxxxHello"
```

H2Dialect.rpad

Expr("Hello").rpad(10, "xy")

```
SELECT RPAD(?, ?, ?) AS res
```
```
"Helloxxxxx"
```

H2Dialect.concat

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

```
SELECT CONCAT(?, ?, ?, ?) AS res
```
```
"i am cow1337"
```

H2Dialect.concatWs

db.concatWs(" ", "i", "am", "cow", 1337)

```
SELECT CONCAT_WS(?, ?, ?, ?, ?) AS res
```
```
"i am cow 1337"
```

MsSqlDialect

Operations specific to working with Microsoft SQL Databases

MsSqlDialect.top

For ScalaSql's Microsoft SQL dialect provides, the .take(n) operator translates into a SQL TOP(n) clause

Buyer.select.take(0)

SELECT TOP(?) buyer0.id AS id, buyer0.name AS name, buyer0.date_of_birth AS date_of_birth
FROM buyer buyer0

```
Seq[Buyer[Sc]]()
```

MsSqlDialect.bool vs bit

Insert rows with BIT values

db.run(
  BoolTypes.insert.columns(
    _.nullable := value.nullable,
    _.nonNullable := value.nonNullable,
    _.a := value.a,
    _.b := value.b,
    _.comment := value.comment
  )
) ==> 1
db.run(
  BoolTypes.insert.columns(
    _.nullable := value2.nullable,
    _.nonNullable := value2.nonNullable,
    _.a := value2.a,
    _.b := value2.b,
    _.comment := value2.comment
  )
) ==> 1

MsSqlDialect.uodate BIT

BoolTypes
  .update(_.a `=` 1)
  .set(_.nonNullable := true)

UPDATE bool_types SET non_nullable = ? WHERE (bool_types.a = ?)