[FIXED] Hibernate and SQLite : Set unique constraint on creation

Issue

I'm using Hibernate to create SQLite tables.

I have a table as such

@Entity 
class Person(

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

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

I see that when the database is created, the unique constraint is added later on via an ALTER request

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

Except that SQLite does not seem to support ALTER requests modifying constraints on tables.

So my question is : Is there a way to literally indicate Hibernate to set that uniqueness constraint on table creation? What would be the best way to do it?

I can ensure uniqueness easily later on via code, but I'd rather use the power of the database if I can.

I should add that this is for a personal small application so so far I'm using the update setting for hibernate.hbm2ddl.auto so Hibernate generates the SQL itself. I'm open to other methods but I'd rather avoid them if I can to reduce maintenance.

Solution

Gonna answer my own question given that it's not getting much traction :).

SQLite indeed does not support ALTER with constraints, and Hibernate does not (to my knowledge) offer a clean way to use custom SQL. On top of this, it is not recommended to use Hibernate: hbm2ddl.auto=update in production.

For those reasons, I decided to turn myself to Flyway and write my own SQL. The good news is that adding Flyway provides my database migrations. The bad news is that it's one more dependency to maintain.

What I've done:

Added the flyway dependency in my build.gradle.kts

    implementation("org.flywaydb:flyway-core:8.4.3")

Instantiated flyway before hibernate in my code, pointing to the same database:

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

Added a handwritten SQL migration file in resources/V1__Create_person_and_entries_table.sql

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

Et voilà!

I wrote a blog post over here with more details and some more reasons to use something like Flyway.

Answered By - jlengrand
Answer Checked By - Pedro (JavaFixing Volunteer)