Generic Top

Finally announcing a new course. The Early Bird Price of the upcoming “Learn Spring” course will permanently increase by $50 on Friday:


1. Overview

In this quick tutorial, we’ll go through steps to use an SQLite database in a JPA-enabled Spring Boot application.

Spring Boot supports a few well known in-memory databases out of the box, but SQLite requires a bit more from us.

Let’s have a look at what it takes.

2. Project Setup

For our illustration, we’ll start with a Spring Data Rest app we’ve used in past tutorials.

In the pom, we need to add the sqllite-jdbc dependency:


This dependency gives us what we need to use JDBC to communicate with SQLite. But, if we are going to use an ORM, it’s not enough.

3. SQLite Dialect

See, Hibernate doesn’t ship with a Dialect for SQLite. We need to create one ourselves.

3.1. Extending Dialect

Our first step is to extend org.hibernate.dialect.Dialect class to register the data types provided by SQLite:

public class SQLiteDialect extends Dialect {

    public SQLiteDialect() {
        registerColumnType(Types.BIT, "integer");
        registerColumnType(Types.TINYINT, "tinyint");
        registerColumnType(Types.SMALLINT, "smallint");
        registerColumnType(Types.INTEGER, "integer");
        // other data types

There are several, so definitely check out the sample code for the rest.

Next, we’ll need to override some default Dialect behaviors.

3.2. Identity Column Support

For example, we need to tell Hibernate how SQLite handles @Id columns, which we can do with a custom IdentityColumnSupport implementation:

public class SQLiteIdentityColumnSupport extends IdentityColumnSupportImpl {

    public boolean supportsIdentityColumns() {
        return true;

    public String getIdentitySelectString(String table, String column, int type) 
      throws MappingException {
        return "select last_insert_rowid()";

    public String getIdentityColumnString(int type) throws MappingException {
        return "integer";

To keep things simple here, let’s keep the identity column type to Integer only. And to get the next available identity value, we’ll specify the appropriate mechanism.

Then, we simply override the corresponding method in our growing SQLiteDialect class:

public IdentityColumnSupport getIdentityColumnSupport() {
    return new SQLiteIdentityColumnSupport();

3.3. Disable Constraints Handling

And, SQLite doesn’t have support for the database constraints, so we’ll need to disable those by again overriding the appropriate methods for both primary and foreign keys:

public boolean hasAlterTable() {
    return false;

public boolean dropConstraints() {
    return false;

public String getDropForeignKeyString() {
    return "";

public String getAddForeignKeyConstraintString(String cn, 
  String[] fk, String t, String[] pk, boolean rpk) {
    return "";

public String getAddPrimaryKeyConstraintString(String constraintName) {
    return "";

And, in just a moment, we’ll be able to reference this new dialect in our Spring Boot configuration.

4. DataSource Configuration

Also, since Spring Boot doesn’t provide configuration support for SQLite database out of the box, we also need to expose our own DataSource bean:

@Autowired Environment env;

public DataSource dataSource() {
    final DriverManagerDataSource dataSource = new DriverManagerDataSource();
    return dataSource;

And finally, we’ll configure the following properties in our file:


Note that we need to keep the cache as shared in order to keep the database updates visible across multiple database connections.

So, with the above configurations, the app will start and will launch an in-memory database called myDb, which the remaining Spring Data Rest configuration can take up.

5. Conclusion

In this article, we took a sample Spring Data Rest application and pointed it at an SQLite database. However, to do so, we had to create a custom Hibernate dialect.

Make sure to check out the application over on Github. Just run with mvn spring-boot:run and browse to http://localhost:8080.

Generic bottom

Finally announcing a new course. The Early Bird Price of the upcoming “Learn Spring” course will permanently increase by $50 on Friday: