Tips and tricks using Hibernate and SQLite with Kotlin
Tags: kotlin, sql, sqlite, hibernate
Published: 2022-02-11 14:31:07

---
title: Tips and tricks using Hibernate and SQLite with Kotlin
published: true
date: 2022-02-09 23:26:02 UTC
tags: kotlin,sql,sqlite,hibernate
canonical_url: https://lengrand.fr/tips-and-tricks-using-hibernate-and-sqlite-with-kotlin/
---

 ![Tips and tricks using Hibernate and SQLite with Kotlin](https://images.unsplash.com/photo-1633412802994-5c058f151b66?crop=entropy&cs=tinysrgb&fit=max&fm=jpg&ixid=MnwxMTc3M3wwfDF8c2VhcmNofDJ8fHNxbHxlbnwwfHx8fDE2NDQ0NDkxMDQ&ixlib=rb-1.2.1&q=80&w=2000)

Last month, with [Compose Multiplatform going 1.0](https://blog.jetbrains.com/kotlin/2021/12/compose-multiplatform-1-0-is-going-live/), 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](https://dev.to/jlengrand/some-fun-with-kotlin-exposed-and-mysql-1p5a) 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.](https://github.com/jlengrand/hibernate-kotlin/blob/main/src/main/kotlin/Main.kt)

## 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](https://github.com/jlengrand/hibernate-kotlin) and running it.

In short, you probably have to use the `jpa`, `noarg` and / or `allopen` gradle plugins for various reasons described [here](https://kotlinlang.org/docs/all-open-plugin.html) 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](https://www.sqlite.org/omitted.html)!**

I created [a Stack Overflow question](https://stackoverflow.com/questions/70933212/hibernate-and-sqlite-set-unique-constraint-on-creation) 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](https://flywaydb.org/) 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](https://github.com/jlengrand/hibernate-kotlin).

Hope this helps someone somewhere someday!