Tips and tricks using Hibernate and SQLite with Kotlin

Last month, with Compose Multiplatform going 1.0, I've been wanting to build a simple application for myself. It's barely more than a personal note taking app.

And for once, I wanted to build a native application instead of a webapp. When you need some kind of SQL database interaction in Kotlin, exposed seems to be pretty much the norm. However, I've already written a bit about why I don't quite like it personally (It has a lot to do with code duplication). And because I wanted to learn a bit more about the Java Persistence API while staying minimal and local, I've decided to go for some good old hibernate and a SQLite setup.

This blog exposes some of the things I've found out while implementing my project. Maybe they're useful for someone else too :). Kotlin, Hibernate and SQLite doesn't seem to be a very present combination online as of now.

You can find a sample project applying the tips I'm describing over here.

Some early context

Some issues and integration challenges between Kotlin and Hibernate are rather well documented. I won't spend time on those here, you can get past them directly by cloning the sample and running it.

In short, you probably have to use the jpa, noarg and / or allopen gradle plugins for various reasons described here better than I would.

90% of the magic fits in those couple lines to be added in your build.gradle.kts:

plugins {
    id("org.jetbrains.kotlin.plugin.noarg") version "1.6.10"
    id("org.jetbrains.kotlin.plugin.jpa") version "1.6.10"
    id("org.jetbrains.kotlin.plugin.allopen") version "1.6.10"
    kotlin("jvm") version "1.6.10"
    application
}

allOpen {
    annotation("javax.persistence.Entity")
    annotation("javax.persistence.MappedSuperclass")
    annotation("javax.persistence.Embeddable")
}

Some extra plugins and setup to use hibernate with Kotlin easily

I would also recommend avoiding using data classes, because the way equals and hashcode messes with hibernate.

Avoiding XML at all cost

For some reason, when searching for Hibernate results that don't use the whole Spring framework, search results and documentation pages are full of XML configuration.

It's 2022, I really want to avoid XML everywhere I can.

Turns out you can also create a hibernate.properties file instead of the hibernate.cfg.xml you'll see everywhere, use the same XML values and it will work out just fine. Here is my properties file :

hibernate.dialect = org.sqlite.hibernate.dialect.SQLiteDialect
hibernate.connection.driver_class=org.sqlite.JDBC
hibernate.connection.url=jdbc:sqlite:test.db
hibernate.show_sql=true
hibernate.hbm2ddl.auto=

A sample properties file to avoid XML

It will be picked up automatically if placed in your resources folder in case you instantiate a default sessionFactory.

Last but not least, you can programatically insert your entities as part of your Configuration to avoid putting them in the properties file.

    val sessionFactory = Configuration()
        .addAnnotatedClass(Person::class.java)
        .addAnnotatedClass(Entry::class.java)
        .buildSessionFactory()

Generated Id and Null values

Let's start with something simple, but quite annoying. We have a simple Entity, with a required value and some id value.

@Entity
class Person(
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    val id: Int,

    @Column(nullable = false)
    val name: String,
)

A barebones Hibernate Entity

We want to create a person called Bob, and save him in the database. Well, something that simple is actually quite annoying to do in Kotlin, because id is not known yet, and yet we have to provide it. We can't just use a random id to avoid override existing persons. We could use a nullable Int? but then we still have to provide a null value every time, to create persons. For example val bob = Person(null, "bob"). Quite ugly though...

There is a simple solution that solves most of out problems though. We can simply place the Id at the end and forget about it. We can actually do the same for many more values that get set by the database during save, like timestamps.

@Entity
class Person(

    @Column(unique = true, nullable = false)
    var name: String,

    @Column(name = "created_at")
    val createdAt : Date? = null,

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    val id: Int? = null,
)

The same barebone entity, but benefitting from generated values on the DB side

That way, we can simple create people like this : val bob = Person("Bob"), while getting a completely filled object when it's being grabbed from the database.

Granted, you still have to handle nullable values in your software but you know they'll be null prior to saving, and won't any more once fetched from the database.

Working with unique constraints in SQLite

with hibernate, it's pretty simple to define unique constraints. In the case of our persons, we want only one person called Bob in our database. Surprisingly, the following Entity definition will not generate a database model that will respect that unique constraint when using SQLite :

@Entity 
class Person(

    @Column(unique = true, nullable = false)
    val name: String,

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    val id: Int? = null,
)

fun main(args: Array<String>) {

    val sessionFactory = Configuration()
        .addAnnotatedClass(Person::class.java)
        .buildSessionFactory()

    val session = sessionFactory.openSession()
    session.beginTransaction()

    session.save(Person("bob"))
    session.save(Person("bob"))
    session.save(Person("bob"))

    session.transaction.commit()
}

Setting unique constraints on an entity is not enough with SQLite

The piece of code above will save three different entries, each with an identical name, and different ids.

The reason for this is that to create to set that unique constraint, hibernate first creates the table, and THEN only alters it to add the constraint. Here is the resulting SQL, coming from the logs:

Hibernate: create table Person (id integer, name varchar(255) not null, primary key (id))
Hibernate: alter table Person add constraint UK_is4vd0f6kw9sw4dxyie5cy9fa unique (name)

Nothing wrong with this, except that SQLite does not support ALTER requests modifying constraints on tables!

I created a Stack Overflow question about it, which didn't meet much success :).

In the end, the easiest solution I found to cope with that issue is to use Flyway in my app and set the database schema myself. It does bring a bit more support but overall I am happy with the change because thanks to Flyway I have a standard way to handle database migrations in my app in the future.

You can look at the complete sample to see how Flyway is integrated in the app, but here is how the SQL looks like :

create table Person
(
    id integer primary key,
    name varchar(255) not null UNIQUE
);

An SQL representation of the Entity defined earlier

Essentially, we need to add the dependency and run Flyway early in our app before setting up Hibernate.

    val flyway = Flyway
        .configure()
        .dataSource("jdbc:sqlite:test.db", null, null).load()
    flyway.migrate()

Simpliest way to run Flyway

Nothing mind breaking :)

SQLite not setting TIMESTAMP values in the database

Right, with the two points above we'd expect to be sorted right? Not quite.

When adding the created_at value, it turns out we created a new problem for ourselves. Here is how the first version of my SQL looked like :

create table Person
(
    id integer primary key,
    name varchar(255) not null UNIQUE,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

SQL definition with a default, non null timestamp value

Well, we have a little issue with this :

• I don't want NULL values there. Except that's what I set as default in my kotlin Entity above, remember?
• I still do want a NON NULL constraint for that timestamp. Creation time is important
• Even if I relax the NULL constraint, I can't benefit from the DEFAULT CURRENT_TIMESTAMP part of my SQL because my entry will always come with the value set to NULL.

So what I really want is something like "set the value to the current timestamp when saving, and make sure it's not NULL".

I found a way of doing exactly this :

create table Person
(
    id integer primary key,
    name varchar(255) not null UNIQUE,
    created_at TIMESTAMP NOT NULL ON CONFLICT REPLACE DEFAULT current_timestamp
);

SQL definition setting the timestamp to the current time when receiving a null

Grant it, it looks a little convoluted, but it avoids me having to set the timestamp myself in software and instead rely on the database, which sounds like a better idea. Especially since I'm using Flyway anyways.

That's it! This is a list of random tips I found out when using this, I admit, uncommon setup those days. But hey, all in all it's harder to make it simpler in terms of dependencies than with SQLite and Hibernate.

Again, if you want to have a running setup with all of the small tips I listed here, you can go over to the sample.

Hope this helps someone somewhere someday!