Code generator

Squery has a code generator that can generate code for various databases:
Postgres, MySQL, MariaDB, Oracle and H2.

It generates models for table rows and handy DAO code with various utility methods:

  • countAll, countWhere
  • findAll, findWhere, findWhereOpt, findAllWhere, findById, findByIdOpt, findByIds
  • insert, updateById
  • deleteWhere, deleteById, deleteIds

Squery codegene is a bit special since it is using Regenesca library.
When you add a new column for example, it will refactor the *Row and *Dao code in place!
This means you can add your own methods/vals to the generated code, without fear that the codegen will remove it.
Of course, it is best to use scalafmt after codegen so that the diff is minimal.

Standalone generator

You can use Ammonite to test the generator:

import $ivy.`ba.sake:squery-generator_2.13:0.6.4`
import $ivy.`ba.sake::squery:0.6.4`
// if using Postgres JSONB
// import $ivy.`ba.sake::squery-postgres-jawn:0.6.4`
import $ivy.`org.postgresql:postgresql:42.7.4`
import $ivy.`com.zaxxer:HikariCP:5.1.0`

import ba.sake.squery.generator.*
import com.zaxxer.hikari.HikariDataSource

val dataSource = HikariDataSource()
dataSource.setJdbcUrl("jdbc:postgresql://localhost:5432/mydb")
dataSource.setUsername("username")
dataSource.setPassword("password")

val generator = SqueryGenerator(dataSource)
val generatedCode = generator.generateString(Seq("myschema"))
repl.load(generatedCode)

// now you can use the generated code
val ctx = SqueryContext(dataSource)
ctx.run {
  MyTableDao.findAll()
}

Mill plugin

Squery provides a Mill plugin:

import $ivy.`ba.sake::mill-squery-generator_mill0.11:0.6.4`
import ba.sake.squery.generator._
import ba.sake.squery.generator.mill.SqueryGeneratorModule

object app extends ScalaModule  with SqueryGeneratorModule {
  // use T.input(T.ctx.env("MY_ENV_VAR")) to set sensitive variables like password etc
  def squeryJdbcUrl = "jdbc:..."
  def squeryUsername = ".."
  def squeryPassword = ".."
  def squerySchemas = Seq("myschema" -> "com.mypackage.db")

  // override to tweak codegen settings
  def squeryGeneratorConfig: T[SqueryGeneratorConfig] = ...

Then you can call generator like this:

./mill root.squeryGenerate

and it will generate source code inside com.mypackage.db, based on myschema schema.