squery

Simple SQL queries in Scala 3.

No DSLs, no fuss, just plain SQL.

Supports any JDBC driver.
Additional support for Postgres, MySql, MariaDb, Oracle, H2, and SQLite.

Scastie example: https://scastie.scala-lang.org/JArud6GGSLOmYyxCNsNdNw

See also https://github.com/sake92/mill-squery for generating boilerplate models and DAOs automatically from db.

Hello world:

// table rows
case class Customer(id: Int, name: String) derives SqlReadRow
case class Phone(id: Int, number: String) derives SqlReadRow

// joined row
case class CustomerWithPhone(c: Customer, p: Phone) derives SqlReadRow

val ds = JdbcDataSource()
ds.setURL("jdbc:h2:mem:")

val ctx = SqueryContext(ds)

ctx.run {
  val res: Seq[CustomerWithPhone] = sql"""
    SELECT c.id, c.name,
           p.id, p.number
    FROM customers c
    JOIN phones p ON p.customer_id = c.id
  """.readRows[CustomerWithPhone]()
}

---

Generator

You can generate boilerplate code for Rows and DAOs.

Mill plugin

See https://github.com/sake92/mill-squery

CLI

You can use squery-cli with Coursier launcher to generate your sources:

cs launch ba.sake:squery-cli_2.13:0.10.0 -M ba.sake.squery.cli.SqueryMain -- \
  --jdbcUrl jdbc:h2:... \
  --baseFolder src \
  --schemaMappings public:com.example.public \
  --schemaMappings myschema:com.example.myschema \
  --colNameIdentifierMapper camelcase \
  --typeNameMapper camelcase \
  --rowTypeSuffix Row \
  --daoTypeSuffix Dao \
  --typeMappingRule '.*_id|UUID|java.util.UUID' \
  --includeTables 'main\\.(users|orders)' \
  --excludeTables 'main\\.audit_.*'

--schemaMappings, --typeMappingRule, --includeTables, and --excludeTables are repeatable. Table patterns match schema.table; exclusions take precedence.

Type mapping rules use column-name-regex|declared-type-regex|Scala-type. Rules are evaluated in order, and the first matching rule overrides the database's built-in mapping. Both regexes must match the complete column name and the JDBC driver's declared type, so use .* when a partial match is intended. Use a fully-qualified Scala type in CLI rules, for example .*_id|UUID|java.util.UUID.

The CLI does not bundle JDBC drivers. Add the appropriate driver as another cs launch dependency. For SQLite:

cs launch org.xerial:sqlite-jdbc:3.46.1.0 \
  ba.sake:squery-cli_2.13:0.10.0 \
  -M ba.sake.squery.cli.SqueryMain -- \
  --jdbcUrl jdbc:sqlite:database.db \
  --schemaMappings main:com.example

Code

You can use squery-generator library to generate code directly.
This is handy when using Ammonite to explore a database structure and its contents.
It can also be used to generate source code manually in scala-cli or in your project (if you dont like CLI or mill plugin).

SQLite code generation reads only the main schema. It maps INTEGER, REAL, TEXT, and BLOB to Long, Double, String, and Array[Byte]; ambiguous declarations such as NUMERIC remain unknown. is_*, has_*, and can_* integer columns map to Boolean, *_at text columns to Instant, and *_date text columns to LocalDate. Ordered TypeMappingRules override the built-in mappings and require both column-name and declared-type regex matches. Generated RETURNING SQL requires SQLite 3.35 or newer; if using SQLite STRICT tables, use SQLite 3.37 or newer.