Spring SQLite unique column throws exception

Question

I'm uing spring boot with a SQLite DB and spring keeps trying to alter the column AFTER it created the table, however SQLite alter statements only support renaming a table and such. I thought it was obvious enough that the SQLiteDialect would support this by declaring the column as unique during the table's creation but I guess i was wrong. My entity looks like this:

@Entity
@Table(name = "my_entities")
public class MyEntity {
    // usual Long id and stuff

    @Column(nullable = false, unique = true)
    private String word;

    // other fields
}

Here's the log output that shows the exception:

Error executing DDL "alter table my_entities add constraint UKegg5ipack6d4292p1uvwopxl6 unique (word)" via JDBC Statement

I considered many options:

• actually modifying SQLiteDialect myself since it's a community dialect anyways, but after looking into it a bit more I realized I would need to do a bit more than just modify a string somewhere 😅
• creating some kind of import.sql that has a drop and create statement in it, but it would defy the whole point of having my DB managed for me:

DROP TABLE IF EXISTS my_entities;
CREATE TABLE my_entities(
    ...
    word varchar(255) UNIQUE,
    ...
);

Eventually, I don't understand why SQLiteDialect can't just create the table from the start with the unique modifier. Does anyone have a solution that makes spring or the dialect do that ?

Answer 1

I suppose you are using Hibernate with hbm2ddl.auto=update. Spring does not manage your DDL. It is up to your JPA provider. It also has nothing to do with SQLiteDialect.

Iirc the Hibernate auto DDL parser has the following semantic (probably to fix some other bugs): if the database is already created, Hibernate won't include the CREATE TABLE with UNIQUE. Instead, it will issue ALTER CONSTRAINT. Drop the database yourself (in the case of SQLite, delete the file). Use hbm2ddl.auto=update to create everything back.

If you are not using production code, personally I do not recommend SQLite. SQLite has a lot of performance concerns which are mostly unnecessary for education and testing, thus limiting a lot of use cases. You can use the fully functional H2 (supports file backing like SQLite) instead. It should be as easy as changing your JDBC URL/Datasource configuration.

On the other hand, ideally you should provide your own DDL (hand written by the DBA) or use a Database migration tool like Flyway or Liquibase for automation.