Why is my h2 database empty every time my Spring Boot application starts?

Question

I'm using spring boot in a maven project with the spring-boot-starter-data-jpa dependency.

In my application.yml file I have:

spring.datasource.url: jdbc:h2:./data;DB_CLOSE_ON_EXIT=FALSE

I also have a DataPopulation class with :

if (userRepository.findAll().iterator().hasNext()) {
  // Database already has users..
}

Where userRepository is a CrudRepository instance.

On every start up the userRepository returns no users, regardless of how many were added the last time the application was running.

I can see that the data.mv.db and data.trace.db files are created and not empty.

Why is my database always empty on start up? What am I missing?

Answer 1

The ./data path makes me think the database is getting deleted each time you build the application, as . means the current directory. So you could use a database file relative your your user home folder, indicated by ~: ~/data. You also may want to use :file in the JDBC URL:

spring.datasource.url: jdbc:h2:file:~/data;DB_CLOSE_ON_EXIT=FALSE

In case you need, check the H2 features documentation.

Answer 2

You need to declare the url in this form:

jdbc:h2:file:<dbpath>

example

jdbc:h2:file:/opt/db/mydb

to persist your data.

Answer 3

There was nothing wrong my jdbc string. Spring boot uses 'create-drop' as the default hibernate ddl-auto value.

I resolved the issue by adding:

spring.jpa.hibernate.ddl-auto=update

to my properties.

Answer 4

As per https://www.baeldung.com/spring-boot-h2-database, by default data.sql gets executeed ahead of Hibernate initialisation. Therefore, we have to defer datasource initialisation as given below. This ensures that the data is populated after the schema is generated. spring.jpa.defer-datasource-initialization=true

Following is my working application.properties

spring.h2.console.enabled=true
spring.sql.init.platform=h2
spring.datasource.url=jdbc:h2:mem:lee
spring.jpa.defer-datasource-initialization=true