How Norm works

Norm requires us to have an active database connection to precompile queries for typesafe data access. Codegen module uses this connection to generate the code.

Codegen

• Norm opens a connection to development database.
• It reads the SQL file and analyzes query by creating a PreparedStatement
• If query is syntactically correct, it creates a SqlModel which is an intermidiary data model that acts as an input to CodeGenerator
• Finally, generates Kotlin files with classes of Query or Command, ParamSetter and RowMapper

Runtime

• Provides generic extension functions and interfaces which can be used without code-gen. These simplify usage of Connection or ResultSet or PreparedStatement classes.
• Adds extentions on these interfaces to be able to execute query/command, map result to a list, execute batch commands and queries etc.

Putting it to use

This example requires to have a PostgreSQL server running (preferably locally)

1. Set up the schema

Create the schema and tables needed for your application or repository.

It is highly recommended to manage the database schema migrations (DDL statements) with a tool like Liquibase or Flyway.

For example, let’s create a person table in the default public schema of default postgres database.

create table persons(
    id SERIAL PRIMARY KEY,
    name VARCHAR,
    age INT, 
    occupation VARCHAR,
    address VARCHAR
);

we can use the psql -p 5432 -d postgres from command line to run the above

All the migrations need to be run before using norm codegen to generate classes.

2. Write the sql queries

Norm needs two directories defined in the project:

1. input directory - contains all sql files
2. output directory - contains all files generated by norm

Write our queries and commands in input dir. Continuing our example, lets write a query that fetches all persons whose age is greater than some number. add a sql file in this sql’s source dir with path, say eg sql/person/get-all-persons-above-given-age.sql

SELECT * FROM persons WHERE AGE > :age;

3. Generate the code

./gradlew normCodegen

4. Take a look at the generated code

The content of generated file would look like:

package person

import java.sql.PreparedStatement
import java.sql.ResultSet
import kotlin.Int
import kotlin.String
import norm.ParamSetter
import norm.Query
import norm.RowMapper

data class GetAllPersonsAboveGivenAgeParams(
  val age: Int?
)

class GetAllPersonsAboveGivenAgeParamSetter : ParamSetter<GetAllPersonsAboveGivenAgeParams> {
  override fun map(ps: PreparedStatement, params: GetAllPersonsAboveGivenAgeParams) {
    ps.setObject(1, params.age)
  }
}

data class GetAllPersonsAboveGivenAgeResult(
  val id: Int,
  val name: String?,
  val age: Int?,
  val occupation: String?,
  val address: String?
)

class GetAllPersonsAboveGivenAgeRowMapper : RowMapper<GetAllPersonsAboveGivenAgeResult> {
  override fun map(rs: ResultSet): GetAllPersonsAboveGivenAgeResult =
      GetAllPersonsAboveGivenAgeResult(
  id = rs.getObject("id") as kotlin.Int,
    name = rs.getObject("name") as kotlin.String?,
    age = rs.getObject("age") as kotlin.Int?,
    occupation = rs.getObject("occupation") as kotlin.String?,
    address = rs.getObject("address") as kotlin.String?)
}

class GetAllPersonsAboveGivenAgeQuery : Query<GetAllPersonsAboveGivenAgeParams,
    GetAllPersonsAboveGivenAgeResult> {
  override val sql: String = """
      |SELECT * FROM persons WHERE AGE > ?;
      |""".trimMargin()

  override val mapper: RowMapper<GetAllPersonsAboveGivenAgeResult> =
      GetAllPersonsAboveGivenAgeRowMapper()

  override val paramSetter: ParamSetter<GetAllPersonsAboveGivenAgeParams> =
      GetAllPersonsAboveGivenAgeParamSetter()
}

DO NOT alter the content of this file as it will get overwritten in next codegen.

5. Using the generated code

To run any query/command, a DataSource connection of postgres is required.

Create an instance of DataSource using the postgresql driver(already added in dependency) methods

  val dataSource = PGSimpleDataSource().also {
          it.setUrl("jdbc:postgresql://localhost/postgres")
          it.user = "postgres"
          it.password = ""
  }

Finally we can execute the query

  val result = dataSource.connection.use { connection -> 
      GetAllPersonsAboveGivenAgeQuery().query(connection, GetAllPersonsAboveGivenAgeParams(20))
  }
  result.forEach { println(it.toString()) }

And Have fun :)