Entity Generation

In ExoQuery, queries are synthesized from a DSL so all you need for a Schema-First (or Database-First) development workflow is the ability to generate entity-classes (i.e. Annotated Data-Classes) from your database schema. ExoQuery provides a way to generate entity-classes at compile-time and automatically adds them to the classpath so that you can use them to write queries queries right away.

Let’s say that you have a fairly consistent Postgres database schema that looks like this:

CREATE TABLE Person (id SERIAL PRIMARY KEY, first_name VARCHAR, last_name VARCHAR, age INT);
CREATE TABLE Address (id SERIAL PRIMARY KEY, owner_id INT REFERENCES Person(id), street VARCHAR, zip VARCHAR);

Firstly, add the JDBC driver to your ExoQuery plugin dependencies. For example:

exoQuery {
  codegenDrivers.add("org.postgresql:postgresql:42.7.3")
}

Then, add the following code to your source files:

// GeneratedEntitiesExample.kt
package my.example.app

fun myFunction() {
  sql.generate(
    Code.Entities(
      CodeVersion.Fixed("1.0.0"),
      DatabaseDriver.Postgres("jdbc:postgresql://<db-host>:<db-port>/<db-name>"),
      "my.example.app",   // root package for generated entities
      // Since database-schema is snake_case, use SnakeCase parser to convert table/column names to CamelCase class/property names
      nameParser = NameParser.SnakeCase
    )
  )
}

Then compile GeneratedEntitiesExample.kt the following files will be generated in your MyProject/entities/main/kotlin directory:

// MyProject/entities/main/kotlin/my/example/app/<db-schema>/Person.kt
package my.example.app.<db-schema>
...
@Serializable
data class Person(val id: Int, @SerialName("first_name") val firstName: String, @SerialName("last_name") val lastName: String, val age: Int)

and…

// MyProject/entities/main/kotlin/my/example/app/<db-schema>/Address.kt
package my.example.app.<db-schema>
...
@Serializable
data class Address(val id: Int, @SerialName("owner_id") val ownerId: Int, val street: String, val zip: String)

These files will automatically be added to your classpath so you can use them in your queries:

package my.example.app
...
fun myQuery() {
  val q = sql.select {
    val p = from(Table<Person>())
    val a = join(Table<Address>()) { a -> a.ownerId == p.id }
    Pair(p, a)
  }
  println(q.buildFor.Postgres().xr.show())
}

Typically you will want to store the database credentials in a .codegen.properties file in your project root.

# MyProject/.codegen.properties
user=postgres
password=postgres

(You can also tell the code-generator to look for credentials in the environment variables of your choice.)

NOTE: If you are using Postgres, it may be necessary to add the <db-schema> that you are using to the search path. You can do this by adding ?currentSchema=public,...,<db-schema> to the end of your JDBC URL.

For more details, have a look at the Code Generation sample: exoquery-sample-codegen.

Pay Attention to CodeVersion

Whenever you change the CodeVersion.Fixed("1.0.0") setting, the entities will be regenerated. This means that if you change your database schema, you can simply bump the version number! When you do that, be sure to actually recompile the file containing the capture.generate(...) call since that is what triggers the regeneration of the entities. If the file is not recompiled, the entities will not be regenerated.