Using SQLDelight 2.0 with PostgreSQL for JVM

Recently I wanted to do some experiments with saving JSON data into a database and found out that PostgreSQL supports JSON as a data type.

I also wanted to use the fantastic SQLDelight library that creates type-safe models and queries for any database (it’s also multi-platform supported).

While trying to follow the recently released SQLDelight 2.0 guide, I stumbled into missing pieces to make the PostgreSQL work with it.

In this post, I will try to fill in the gaps and build a sample project showcasing both working together ✌️

PostgreSQL and SQLDelight. Source: https://kinsta.com/

A few of the missing pieces that I had to figure out were:

1. Create DataSource (“hikari” or other connection managers were mentioned in the guide)
2. Using HikariCP as data source to connect to PostgreSQL
3. Write all the glue pieces to use the SQLDelight code to perform CRUD operations on the PostgreSQL Database

Use HikariCP to create PostgreSQL DataSource

First import the hikari and PostgreSQL library into your JVM project

// https://github.com/brettwooldridge/HikariCP#artifacts
implementation("com.zaxxer:HikariCP:5.0.1")

// This is needed for the PostgreSQL driver
// https://mvnrepository.com/artifact/org.postgresql/postgresql
implementation("org.postgresql:postgresql:42.6.0")

Then you need to build the HikariConfig with the right data set to connect to the database and then create the HikariDataSource which is what is needed for the SQLDelight

Here is a snippet taken from the sample project

/**
 * Creates a [DataSource] using [HikariDataSource].
 */
private fun getDataSource(): DataSource {
    // https://jdbc.postgresql.org/documentation/use/
    val config = HikariConfig().apply {
        jdbcUrl = "jdbc:postgresql://localhost:5432/dbname"
        driverClassName = "org.postgresql.Driver"
        username = "dbusername"
        password = "dbpassword"
        maximumPoolSize = 3
        isAutoCommit = false
        transactionIsolation = "TRANSACTION_REPEATABLE_READ"
        validate()
    }
    return HikariDataSource(config)
}

That’s it, now you can follow the official SQLDelight guide on creating databse to create tables and do CRUD operations.

For example, here is a simplified snippet to give the whole picture

val dataSource: DataSource = getDataSource(appConfig)

val driver: SqlDriver = dataSource.asJdbcDriver()

// NOTE: The `SportsDatabase` and `PlayerQueries` are from SQLDelight
val database = SportsDatabase(driver)
val playerQueries: PlayerQueries = database.playerQueries

val hockeyPlayers = playerQueries.selectAll().executeAsList()
println("Existing ${hockeyPlayers.size} records: $hockeyPlayers")
// Prints following 👇
// - - - - - - - - - -
// Existing 15 records: 
// [HockeyPlayer(player_number=10, full_name=Corey Perry), ...]

The full snippet is available here.

See the GitHub project for a complete example with gradle dependencies and SQLDelight configuration needed to make it work.

GitHub - hossain-khan/SQLDelight-PostgreSQL-JVM-sample: A sample project exercising PostgreSQL with SQLDelight 2.0

Happy to hear feedback or any corrections to do this in a better way.

EDIT: After I wrote the article, I found similar article about it (which could have saved me ton of time), so do take a look at it too 😊

SQLDelight for PostgreSQL on Kotlin JVM