Java Hibernate, Liquibase supports cross databases and SQLite?

Question

I'm using Spring Boot 1.5.2 with Hibernate 5 and try to support as many databases as possible (i.e: Hibernate will create all the tables in SQLite 3, then I use Liquibase as an abstract layer to generate the XML change-log files for all kind of supported databases which Liquibase claimed: supported databases).

so I added the dependency for Liquibase in pom.xml (Maven).

    <!-- Database schema versions migration -->


    <dependency>
        <groupId>org.liquibase</groupId>
         <artifactId>liquibase-core</artifactId>
          <version>3.5.3</version>
    </dependency>
    <dependency>
        <groupId>org.liquibase</groupId>
        <artifactId>liquibase-maven-plugin</artifactId>
        <version>3.4.1</version>
    </dependency>

and a plugin to generate the changelog XML file from created database of Hibernate

<!-- Database schema versions migration -->  

 <plugin>
        <groupId>org.liquibase</groupId>
        <artifactId>liquibase-maven-plugin</artifactId>
        <version>3.5.3</version>
        <configuration>                  
            <propertyFile>src/main/resources/liquibase.properties</propertyFile>
        </configuration>                
 </plugin>

I have some configuration in liquibase.properties to connect to SQLite3 datab file, then I can run this command to create a changelog file.

mvn liquibase:generateChangeLog

The output changelog I cannot recreate in another different SQLite 3 db file, due to the addPrimaryKey element:

<changeSet author="rasdaman (generated)" id="1497363976895-86">
     <addPrimaryKey columnNames="address_id" tableName="address"/>
</changeSet>

and the error in Java Spring Boot when it starts:

addPrimaryKey is not supported on sqlite, 
classpath:/database_versions/db.changelog-master.xml::1497366115846-62::rasdaman (generated)

    at liquibase.changelog.DatabaseChangeLog.validate(DatabaseChangeLog.java:266)
    at liquibase.Liquibase.update(Liquibase.java:210)
    at liquibase.Liquibase.update(Liquibase.java:192)
    at liquibase.integration.spring.SpringLiquibase.performUpdate(SpringLiquibase.java:431)
    at liquibase.integration.spring.SpringLiquibase.afterPropertiesSet(SpringLiquibase.java:388)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1687)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1624)

If I use the generated output file from SQLite by Liquibase and allow Spring Boot to starts with Postgresql datasource, I got another error:

org.springframework.beans.factory.BeanCreationException: Error creating 
bean with name 'liquibase' defined in class path resource     


[org/springframework/boot/autoconfigure/liquibase/LiquibaseAutoConfiguration$LiquibaseConfiguration.class]: Invocation of init method failed; nested exception is liquibase.exception.MigrationFailedException: Migration failed for change set classpath:/database_versions/db.changelog-master.xml::1497366115846-1:: (generated):
         Reason: liquibase.exception.DatabaseException: ERROR: syntax error at or near "("
      Position: 72 [Failed SQL: CREATE TABLE public."HT_abstract_coverage" (abstract_coverage_id BIGINT(2000000000, 10) NOT NULL, hib_sess_id CHAR(36))]
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1628)

So it seems I don't have another tool to generate an abstract data file like XML automatically which can be imported to any common databases without problems? If you have any suggestion, please advise!

Thanks,

Bằng Rikimaru Bằng Rikimaru · Accepted Answer · 2017-06-14T06:17:02

The problem is Liquibase does not support SQLite very well http://www.liquibase.org/documentation/changes/add_primary_key.html , also Hibernate which needs a third party SQLite dialect and the performance for write is not good, so I think Liquibase could work well with another databases like: postgresql, mysql, hyperSQL,...

Java Hibernate, Liquibase supports cross databases and SQLite?

1 Answers