Using SQLite with Java Spring

Last updated: 2025-12-22

When I wanted to use SQLite with java spring, process was not as straightforward as when we use MySQL, MariaDB or other well supported databases. Hence I researched tutorials, posts from many devs faced similar problem and finally integrated SQLited db seamlessly. Here I’m documenting the exact steps for my own future reference and for anyone interested. If you are reading this, note about the last updated date as the information here might get outdated in future.

Steps to follow

1. Install the SQLite driver

2. Set up hibernate SQL dialect

3. Set up the data source and driver in application.properties (if applicable)

4. Write the schema

5. Write data type converters (if applicable)

6. Write the entity and repository files

1. Install the driver

Install the driver package using maven. Often, the popular driver is from Xerial. If using maven, adding the following dependency to pom.xml should do.

<dependency>  
    <groupId>org.xerial</groupId>  
    <artifactId>sqlite-jdbc</artifactId>  
    <version>3.51.0.0</version>  
</dependency>

2. Setting up the SQL dialect

Spring by default uses hibernate as the ORM. Hibernate includes SQL dialects for popular database engines such as MySQL/Mariadb or PostgreSQL. However, hibernate does not include dialects for SQLite. Therefore, it is required to either write your own dialect or to use a dialect written by someone else. For this purpose, most easier way is to use the SQLite community dialect.

There are two similar artifacts for this purpose in the maven central repositories under similar package names. One artifact’s version is tracked by Spring itself. Other is not. Hence, using the correct package is important. Refer the following maven dependency block.

<dependency>  
    <groupId>org.hibernate.orm</groupId>  
    <artifactId>hibernate-community-dialects</artifactId>  
</dependency>

Note the group id is org.hibernate.orm but not simply org.hibernate. The one mentioned above is tracked by Spring. Therefore we can omit the version tag to allow Spring manage the version. The org.hibernate version could also work but often causes version mismatches.

3. Set up the data source and driver (if applicable)

You need to anyway set up the data source and driver. However the process may depend on the practice you use in the code base. You might write special config class, i.e a DataSource bean, which allows you to do programmatic data source selection, like having multiple SQLite database files and switching between them depending on your need.

Otherwise, if you have no restrictions and simply want SQLite to work, you can follow the following approach. This is the easiest and just works method. That is, you have to edit your application.properties file to add the following configuration.

spring.datasource.driver-class-name=org.sqlite.JDBC  
spring.jpa.database-platform=org.hibernate.community.dialect.SQLiteDialect  
spring.datasource.url=jdbc:sqlite:acmestocks.db  
spring.sql.init.mode=always

Properties are self explanatory. The last property, spring.sql.init.mode says the when to run the database initialization (the schema.sql file). always means Spring will run that every time the application initializes.

4. Writing the schema

This is the step where you create tables and initial structure of your database. For this step, if your application is large scale, you can use a database migration strategy. However, in this guide I’m using the simple schema.sql method, which suits for most small scale projects as the complexity is less.

Create a file called schema.sql in the resources directory. Spring will run it to initialize the database as defined in the spring.sql.init.mode property in application.properties mentioned above.

-- SQLite schema for AcmeStocks application  
  
CREATE TABLE IF NOT EXISTS product (  
    id INTEGER PRIMARY KEY,  
    sku TEXT NOT NULL,  
    name TEXT NOT NULL,  
    default_price REAL NOT NULL,  
    life_time_days INTEGER NOT NULL,  
    low_stock_threshold INTEGER NOT NULL  
);  
  
-- Create unique index on SKU to ensure uniqueness  
CREATE UNIQUE INDEX IF NOT EXISTS idx_product_sku ON product(sku);

5. Write data type converters

Conversions between java data types and SQL data types are usually handled by the SQL dialect. However, I found that the SQLite community dialect do weird behavior when it comes to date time data types. Therefore, we can write the following converters. Spring will load them up and use them to do the conversions.

import jakarta.persistence.AttributeConverter;  
import jakarta.persistence.Converter;  
  
import java.time.LocalDate;  
  
@Converter(autoApply = true)  
public class LocalDateConvertor implements AttributeConverter<LocalDate, String> { 
    @Override  
    public String convertToDatabaseColumn(LocalDate attribute) {  
        return attribute != null ? attribute.toString() : null;  
    }  
    @Override  
    public LocalDate convertToEntityAttribute(String dbData) {  
        return dbData != null ? LocalDate.parse(dbData) : null;  
    }  
}

That one is for converting LocalDate datatype to String so SQLite actually save the date as a text in the db. Following one is similar, for java.time.Instant.

import jakarta.persistence.AttributeConverter;  
import jakarta.persistence.Converter;  
  
import java.time.Instant;  
  
@Converter(autoApply = true)  
public class InstantConvertor implements AttributeConverter<Instant, String> {  
    @Override  
    public String convertToDatabaseColumn(Instant attribute) {  
        return attribute != null ? attribute.toString() : null;  
    }  
    @Override  
    public Instant convertToEntityAttribute(String dbData) {  
        return dbData != null ? Instant.parse(dbData) : null;  
    }  
}

I prefer to place the converters in a sub-package preferably, persistence. With this, for timestamps, we need to use the java java.time.Instant data type and for dates, java.time.LocalDate and store them in the db as the data type TEXT.

6. Entity and repository files

At this point, SQLite specific configuration is complete. Now you can proceed to the normal flow of using databases with Spring framework and define entity and repository files, and carry on your project.