SQLDelight in Kotlin JVM

In simple words, SQLDelight is a tool that takes your slightly modified SQL code and converts it to easy to use Kotlin code.

What you write

Example of the generated code use

Note that I use SQLDelight for PostgreSQL and JVM in this article. Though this guide is partially applicable to the other variations of SQLDelight, you should refer to the official documentation if required.

Installation

The hardest part of using SQLDelight is installation. The “Getting Started” section in the official documentation doesn’t do a good job, so let’s make it a little easier with this guide.

Final project structure. Refer to it if needed.

1. Install and run PostgreSQL. You can see the information on how to get Postgres for your OS here. I will use pgAdmin in this article later, so be sure to keep this option checked during installation. The following steps of PostgreSQL installation will not be covered here since it is out of scope of this article. Also, make sure to remember your password!

2. Install the Gradle plugin. This part depends on the language of your Gradle build file.

Groovy

Add the following code to your build.gradle file. Order is important!

Kotlin

Add the following code to your build.gradle.kts file:

You also need to modify settings.gradle.kts:

To test that everything is correct, open your Gradle tab and check your Tasks. You should see a group of tasks named sqldelight.

3. Add several dependencies to build.gradle:

• PostgreSQL JDBC driver will allow Hikari to interact with the database.
• In this article, we will be using Hikari, which requires SLF4J to run. We will set it up later. I’m using slf4j-reload4j here but you can try using slf4j-jdk14, slf4j-simple or some other variation of SLF4J. See the documentation to make the right choice.
• Hikari connection pool provides us with javax.sql.DataSource.
• SQLDelight JDBC driver converts DataSource to com.squareup.sqldelight.db.SqlDriver.

4. If you choose to use slf4j-reload4j, you will need to set up Log4j upon which this variation of SLF4J runs. If you chose any other option, you will most likely have to skip this part.

Add log4j.properties to your /src/main/resources directory. Here is a way to configure Log4j:

You might want to try it out:

If it works, congratulations! Otherwise, I don’t envy you. Now we can write some actual code.

Example

The official documentation has this hockey example. I don’t have a problem with hockey unless I have to play it, so I’ll use that in my example.

1. Create a new database and name it “Hockey”. You can use pgAdmin, which you should have installed along with PostgreSQL. On Windows, it’s located in INSTALLATION_DIR\PostgreSQL\14\pgAdmin 4\bin\pgAdmin4.exe. The rest is trivial, I promise.

2. Create a new directory named sqldelight in /src/main. It should be on the same level as the kotlin and resources directories.

3. Create a file named v0__hockey.sqm. This will be our first migration a.k.a. creation of the HockeyPlayer table. Let’s also populate it with the first player in our team.

In Kotlin, SQLDelight keeps the names of tables and variables exactly as you created them in your .sq* file but it translates them to lowercase in PostgreSQL. So, in this example a HockeyPlayer class will be created in Kotlin and a hockeyplayer table — in Postgres. I did not find a solution for this problem.

4. Create the following directories: /src/main/sqldelight/com/example/sqldelight/hockey. Add a file named HockeyPlayer.sq there. Let it be empty for now.

The /com/example/ part depends on what you assigned to packageName in your build.gradle file. Also, everything that goes after /com/example/ doesn’t matter but as you add more tables, you might find such an approach more convinient.

5. Build your project and look inside /build/generated/sqldelight/code. You should see something similar to this.

Generated code example

6. Create a JDBC driver. Here I suppose your DB runs on localhost and 5432 port.

7. We have two methods to create a database:

• fun create(driver: SqlDriver)
• fun migrate(driver: SqlDriver, oldVersion: Int, newVersion: Int)

Let’s go for the second one because the first one won’t insert our first player. Run the migration.

Notice that you should always close connection of SqlDriver when you are done with your database. Here, it’s done with T.use(block: (T) -> R): R function.

8. Add some functions to HockeyPlayer.sq:

9. Try it!

You can find the full example here.