Using HikariCP to connect to Heroku Postgres(ql!) in Kotlin

Are you trying to connect to a Heroku Postgres instance using HikariCP?

This has been way too hard because I couldn’t find much documentation out there 😦

We only need to do a few simple code changes and the DATABASE_URL env var is sufficient for our needs. What these simple changes are though, is not obvious.

The Prod code is at the bottom of this post but the following emphasises the difference between DATABASE_URL and what HikariCP wants.

class Database {

    val hikariConfig: HikariConfig = HikariConfig()

    init {
        hikariConfig.jdbcUrl = "jdbc:postgresql://HOST:PORT/DATABASE?sslmode=require"
        hikariConfig.driverClassName = "org.postgresql.Driver"
        hikariConfig.username = "username"
        hikariConfig.password = "password"
    }

    private val dataSource = HikariDataSource(hikariConfig)

}

VERY Important:

  • Heroku gives you your database url connection string as an env var DATABASE_URL in the following format: postgres://username:password@host:port/databaseName but HikariCP expects this to be jdbc:postgresql://host:port/databaseName?sslmode=require
    • note: jdbc: is prepended
    • postgres should be postgresql (lol)
    • sslmode=require to authenticate via ssl
    • username and password must be pulled out of connection string and set as config properties: hikariConfig.username and hikariConfig.password
  • hikariConfig.driverClassName = "org.postgresql.Driver" is absolutely required, the driver class cannot be inferred.
  • I initially used hikariConfig.dataSourceClassName = "org.postgresql.ds.PGSimpleDataSource" but this ignored jdbcUrl and attempted to connect to localhost:5432 which is Captain Badtimes if not using local postgres.

Why?

I want to share a database between applications on Heroku. This isn’t terribly well documented. Initially I thought this was only possible on Heroku Postgres plans above Hobby (ie. plan > $50/month) because the command heroku pg:attach is only available above Hobby. In reality, the only shortfall of not attaching the postgres this way is that Heroku can change the DATABASE_URL any time, which will only update apps that are “attached” via the command.

THE END 😥

To use the DATABASE_URL env var generically:

class Database {

    private val hikariConfig = HikariConfig()

    private val DATABASE_URL = System.getenv("DATABASE_URL")

    init {
        val credentialsAndConnectionString = DATABASE_URL.split("@")
        val credentials = credentialsAndConnectionString[0].split("postgres://")[1].split(":")
        val connectionString = credentialsAndConnectionString[1]
        hikariConfig.jdbcUrl = "jdbc:postgresql://$connectionString?sslmode=require"
        hikariConfig.driverClassName = "org.postgresql.Driver"
        hikariConfig.username = credentials[0]
        hikariConfig.password = credentials[1]
    }

    private val ds = HikariDataSource(hikariConfig)

//do some queries.

This will blow up if the env var is missing, which you may or may not desire.

1 thought on “Using HikariCP to connect to Heroku Postgres(ql!) in Kotlin”

  1. > postgres should be postgresql (lol)

    You saved me so much of a headache with this one. I really appreciate you pointing this particular thing out.

    Liked by 1 person

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s