For my blog, I decided to use SQLite as the database. So far, it’s proven to be a lightweight and reliable choice. I chose SQLite for several reasons:

  • Lightweight: It’s a file-based database that requires no installation or configuration.
  • Relational: It offers a relational structure, allowing for easy migration to a more advanced RDBMS in the future if needed.

In this post, I’ll walk through how I integrated SQLite into my Spring Boot application. You can find the full code in demo-sqlite repository in my github account.

Setting Up Dependencies

When initializing a new Spring Boot project via Spring Initializr, you can usually find the database driver dependencies you need. However, in this case, SQLite’s driver was not available by default. Furthermore, although hibernate-core (comes with spring-boot-starter-data-jpa) includes dialects for many databases, SQLite is not one of them. I had to manually add these dependencies. Additionally, I included spring-boot-starter-test for testing and lombok to simplify development.

Here’s the pom.xml setup for the dependencies:

<dependencies>
  <dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
  </dependency>
  <dependency>
    <groupId>org.hibernate.orm</groupId>
    <artifactId>hibernate-community-dialects</artifactId>
  </dependency>
  <dependency>
    <groupId>org.xerial</groupId>
    <artifactId>sqlite-jdbc</artifactId>
    <version>3.46.1.0</version>
  </dependency>
  <dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.34</version>
    <scope>provided</scope>
  </dependency>
  <!-- TEST -->
  <dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-test</artifactId>
    <scope>test</scope>
  </dependency>
</dependencies>

Configuring application.properties

The configuration for SQLite was similar to other databases for my case, with just a few differences;

  1. Password: You can set up a password for SQLite, but I skipped this step since my application does not store sensitive data.
  2. Date Handling: I wanted to use LocalDateTime in my application, so I needed to configure SQLite to store dates as text.

Here’s my application.properties setup:

spring.datasource.url=jdbc:sqlite:mydatabase.db?date_class=TEXT
spring.datasource.driver-class-name=org.sqlite.JDBC
spring.jpa.show-sql=true
spring.jpa.database-platform=org.hibernate.community.dialect.SQLiteDialect
spring.jpa.hibernate.ddl-auto=update

In my projects, I usually use Flyway for managing database migration, but for this example, I let Hibernate handle schema generation (ddl-auto=update).

Testing the Integration

To confirm the setup, I created a simple Post entity and a corresponding repository interface.

Post.java

@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
@Entity(name = "post")
public class Post {
  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private Long id;
  private String title;
  private String content;
  @Column(name = "created_at")
  private LocalDateTime createdAt;
}

PostRepository.java

@Repository
public interface PostRepository extends JpaRepository<Post, Long> {
}

PostRepositoryTest.java

@SpringBootTest
class PostRepositoryTest {

  @Autowired
  private PostRepository postRepository;

  @Test
  void testSavePost() {
    LocalDateTime postCreatedAt = LocalDateTime.now();
    postRepository.save(Post.builder()
        .title("Post Title")
        .content("Content")
        .createdAt(postCreatedAt)
        .build());
  }
}

When running the test, Hibernate successfully inserted a new post record into the database:

Hibernate: insert into post (content, created_at, title) values (?, ?, ?)
Hibernate: select last_insert_rowid()
[INFO] Tests run: 1, Failures: 0, Errors: 0, Skipped: 0, Time elapsed: 2.922 s -- in com.cb.demo.sqlite.repository.PostRepositoryTest

Conclusion

Setting up SQLite with Spring Boot was relatively straightforward, aside from manually adding the dependencies and configuring the connection string for LocalDateTime storage. For small applications or development environments, I would recommend SQLite as a lightweight and easy to setup database solution.

If your application doesn’t require advanced databases or handles only minimal data, SQLite can be an excellent choice. However, if your needs grow or become more demanding, the relational nature of SQLite allows for easy migration to a more advanced RDBMS down the road.