Norm

Table of Contents

• Concept
• Design
  • codegen
  • runtime
• Getting Started

Concept:

Norm(No-ORM), as the name suggests, is a library whose idealogy is opposite to that of how standard ORMs work. Norm is designed to avoid two things:

1. Having to write entity, query and result/response classes by hand
2. Writing queries/commands which would throw syntax or semantic errors at runtime

Design:

It is a library for Kotlin and postgres which enables generating query/command classes at compile time if the query is syntactically and semantically right, and provides methods to execute them on runtime.

Hence, Norm has two packages:

1. codegen

   Given a postgres sql query or command in the form of a .sql file, this package generates a query class, params class, paramSetter class and resultMapper class in Kotlin.

   How it works:

   a. It needs a postgres connection

   b. Analyzes the sql file by creating a PreparedStatement

   c. If no database errors, creates a SqlModel which acts as an input to CodeGenerator

   d. Kotlin file with classes of Query or Command, ParamSetter and RowMapper are generated from SqlModel

2. runtime

   What it does:

   a. Provides interfaces which are super types for all query, command, params, row mapper classes.

   b. Provides multiple functions on these interfaces to be able to execute query/command, map result to a list, execute batch commands and queries etc.

   These methods run against a Connection or on a ResultSet or on a PreparedStatement.

Getting Started:

1. Setup

   • Start postgres server locally
   • Add norm dependencies

         configurations {
             norm
         }
         dependencies {
             implementation "org.postgresql:postgresql:$postgresVersion"
             norm 'com.github.medly.norm:codegen:$normVersion'
             norm 'com.github.medly.norm:runtime:$normVersion'
         } 
     
     

   Pre-requisites: kotlin dependencies

2. Schema and table creation

   • Create schemas and tables needed for your application or repository.

   • Lets take example of a person table and create it in the default public schema of default postgres database

          psql -p 5432 -d postgres
          postgres=# create table persons(
                          id SERIAL PRIMARY KEY,
                          name VARCHAR,
                          age INT, 
                          occupation VARCHAR,
                          address VARCHAR
                      );
     

   • All or any table creations and migrations need to be run before using norm to generate classes

3. Writing query/command and generating classes using norm

   • norm needs paths to two folders defined:

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

   • add a task in gradle which would execute norm's code generation

         task compileNorm(type: JavaExec) {
             classpath = configurations.norm  
             main = "norm.MainKt"
             args "${rootProject.rootDir}/sql"   //input dir
             args "${rootProject.rootDir}/gen"   //output dir
             args "jdbc:postgresql://localhost/postgres"      //postgres connection string with db name
             args "postgres"                                  //db username
             args ""                                          //db password (optional for local) 
         }
     

   • write a query eg that fetches all persons whose age is greater than some number

     • add a folder in sql folder, say eg person

     • add a sql file in this folder with name, say eg get-all-persons-above-given-age

       SELECT * FROM persons WHERE AGE > :age;

     • run ./gradlew compileNorm. It will generate a folder within gen (output dir) with the same name as folder inside sql(input dir), person.

       The generated classes would be within a file named - GetAllPersonsAboveGivenAge (title case name of sql file)

       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()
             }
       
       

4. Executing queries/commands using norm

   • 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 = ""
                 }
     

   • Execute the query:

         val result = dataSource.connection.use { connection -> 
             GetAllPersonsAboveGivenAgeQuery().query(connection, GetAllPersonsAboveGivenAgeParams(20))
         }
         result.map { res ->
             println(res.id)
             println(res.name)
             println(res.age)
             println(res.occupation)
             println(res.address)
         }
     

Command Line Interface

Norm CLI can be used to generate Kotlin files corresponding to SQL files.

we can provide multiple of files using -f some/path/a.sql -f some/path/b.sql. This will generate Kotlin files at some.path.A.kt & some.path.A.kt. If we want to exclude some from package name then we must use -b option with the base dir -f some/path/a.sql -f some/path/b.sql -b some/. Now the kotlin files will be generated in package path.A.kt & path.A.kt inside the output-dir.

If option --in-dir is used, all the *.sql files will be used for code generation.

$ norm-codegen --help

Usage: norm-codegen [OPTIONS] [SQLFILES]...

  Generates Kotlin Source files for given SQL files using the Postgres
  database connection

Options:
  -j, --jdbc-url TEXT        JDBC connection URL (can use env var PG_JDBC_URL)
  -u, --username TEXT        Username (can use env var PG_USERNAME)
  -p, --password TEXT        Password (can use env var PG_PASSWORD)
  -b, --base-path DIRECTORY  relative path from this dir will be used to infer
                             package name
  -f, --file FILE            [Multiple] SQL files, the file path relative to
                             base path (-b) will be used to infer package name
  -d, --in-dir DIRECTORY     Dir containing .sql files, relative path from
                             this dir will be used to infer package name
  -o, --out-dir DIRECTORY    Output dir where source should be generated
  -h, --help                 Show this message and exit