SQL DSL JOOQ

In this post I want to introduce the JOOQ sql domain specific language (DSL) for Kotlin and Java.

It is free for open source databases and we will use postgresql in this tutorial.

We will write a small app to manage todolists in Kotlin.

I will not explain how to setup a postgresql database but there are a lot of tutorials available for several operating systems.

Create the database

At first we need to connect to our postgres database, add a user and create a database.

In order to do this we need to execute the following statements:

create database jooq;
create user jooq with encrypted password 'jooq';
grant all privileges on database jooq to jooq;

Next we need some tables to contain our data.

Connect to the created jooq database and execute the following statements:

create table todo_list(id int primary key, name varchar(255));
create table todo_entry(id int primary key, todo_list_id int references todo_list(id), description varchar(255));

now we are set to start our todo list app

Starting up

We will create a new gradle project and add the jooq dependencies:

mkdir jooq_kotlin
cd kooq_kotlin
gradle init --dsl kotlin 

build.gradle.kts

plugins {
    kotlin("jvm") version "1.3.0"
    // to start our server directly from gradle
    application
}
dependencies {
    compile(kotlin("stdlib"))

    compile("org.jooq:jooq:3.11.11")
    compile("org.jooq:jooq-meta:3.11.11")
    compile("org.jooq:jooq-codegen:3.11.11")

    runtime("org.postgresql:postgresql:42.2.5")
}

repositories {
    mavenCentral()
}

application {
    mainClassName = "de.klg71.jooqtest.MainKt"
}

We should be able to execute it without any error:

gradle build