How to configure spring-boot to use file based H2 database

I have successfully created a spring boot application that uses the H2 embedded database in-memory. I would now like to change this to a file based version that will persist.

I have tried just changing the spring.datasource.* properties in my application.properties file and they look something like this:

spring.datasource.url=jdbc:h2:file:~/test;DB_CLOSE_ON_EXIT=FALSE
spring.datasource.username=test
spring.datasource.password=test
spring.datasource.driverClassName=org.h2.Driver`  

It seems like spring boot just ignores these settings because it just starts as follows:

o.s.j.d.e.EmbeddedDatabaseFactory        : Starting embedded database: url='jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=false', username='sa'

My pom.xml contains the following dependencies that may be relevant to this post:

<parent>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-parent</artifactId>
  <version>1.3.5.RELEASE</version>
</parent>
....
<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-web</artifactId>
</dependency> 
<dependency>
	<groupId>com.h2database</groupId>
	<artifactId>h2</artifactId>
</dependency>
<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-devtools</artifactId>
</dependency>

My understanding from the documentation and a number of posts is that the configuration should just work but no luck for me. Just to prevent some of the basic errors I have tried and checked the following:

1. My application properties is in the classspath:
2. I have tried to exclude the auto configuration in annotation @EnableAutoConfiguration
3. I have tried to inject a dataSource bean with combinations of annotation @Primary, @ConfigurationProperties(prefix = "spring.datasource") and setting the properties programmatically with DataSourceBuilder. This causes other errors related to the type being null.

Seems like I am missing a key concept or something. Can anyone help.

UPDATE 1: Extract from my auto configuration report:

Positive matches:
-----------------

    DataSourceAutoConfiguration matched
  - @ConditionalOnClass classes found: javax.sql.DataSource,org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseType (OnClassCondition)

   DataSourceAutoConfiguration.DataSourceInitializerConfiguration matched
  - @ConditionalOnMissingBean (types: org.springframework.boot.autoconfigure.jdbc.DataSourceInitializer; SearchStrategy: all) found no beans (OnBeanCondition)

   DataSourceAutoConfiguration.EmbeddedConfiguration matched
  - embedded database H2 detected (DataSourceAutoConfiguration.EmbeddedDataSourceCondition)
  - @ConditionalOnMissingBean (types: javax.sql.DataSource,javax.sql.XADataSource; SearchStrategy: all) found no beans (OnBeanCondition)

   DataSourceAutoConfiguration.JdbcTemplateConfiguration matched
  - existing auto database detected (DataSourceAutoConfiguration.DataSourceAvailableCondition)

   DataSourceAutoConfiguration.JdbcTemplateConfiguration#jdbcTemplate matched
  - @ConditionalOnMissingBean (types: org.springframework.jdbc.core.JdbcOperations; SearchStrategy: all) found no beans (OnBeanCondition)

   DataSourceAutoConfiguration.JdbcTemplateConfiguration#namedParameterJdbcTemplate matched
  - @ConditionalOnMissingBean (types: org.springframework.jdbc.core.namedparam.NamedParameterJdbcOperations; SearchStrategy: all) found no beans (OnBeanCondition)

   DataSourceTransactionManagerAutoConfiguration matched
  - @ConditionalOnClass classes found: org.springframework.jdbc.core.JdbcTemplate,org.springframework.transaction.PlatformTransactionManager (OnClassCondition)

   DataSourceTransactionManagerAutoConfiguration.TransactionManagementConfiguration matched
  - @ConditionalOnMissingBean (types: org.springframework.transaction.annotation.AbstractTransactionManagementConfiguration; SearchStrategy: all) found no beans (OnBeanCondition)

    H2ConsoleAutoConfiguration matched
  - @ConditionalOnClass classes found: org.h2.server.web.WebServlet (OnClassCondition)
  - found web application StandardServletEnvironment (OnWebApplicationCondition)
  - matched (OnPropertyCondition)

   HibernateJpaAutoConfiguration matched
  - @ConditionalOnClass classes found: org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean,org.springframework.transaction.annotation.EnableTransactionManagement,javax.persistence.EntityManager (OnClassCondition)
  - found HibernateEntityManager class (HibernateJpaAutoConfiguration.HibernateEntityManagerCondition)

Negative matches:
-----------------

    DataSourceAutoConfiguration.NonEmbeddedConfiguration did not match
  - missing supported DataSource (DataSourceAutoConfiguration.NonEmbeddedDataSourceCondition)

`

UPDATE 2: added actuator and looked at endpoint /configprops. What is interesting here is that my config has been taken and the database exists but when the application runs it does not use this dataSource.

"spring.datasource.CONFIGURATION_PROPERTIES":
    {"prefix":"spring.datasource",
     "properties":{
        "schema":null,
        "data":null,
        "xa":{"dataSourceClassName":null,
               "properties":{}
             },
        "type":null,
        "separator":";",
        "url":"jdbc:h2:file:~/test;DB_CLOSE_ON_EXIT=FALSE",
        "platform":"all",
        "continueOnError":false,
        "jndiName":null,               
        "sqlScriptEncoding":null,
        "password":"******",
        "name":"testdb",
        "driverClassName":"org.h2.Driver",
        "initialize":true,
        "username":"test"
        }
    }  

I am adding this answer to avoid confusion and further research.

Actually I have the same problem and none of the answer worked for me completely rather than the mix for some answers worked.

Here is the minimal configuration which is required to persist H2 db in spring boot.

application.properties

# H2
spring.h2.console.enabled=true
spring.h2.console.path=/h2
# Datasource
spring.datasource.url=jdbc:h2:file:~/spring-boot-h2-db
spring.datasource.username=sa
spring.datasource.password=
spring.datasource.driver-class-name=org.h2.Driver
spring.jpa.hibernate.ddl-auto=update

Here spring.jpa.hibernate.ddl-auto=update does the trick. Nothing else is required.

No need to add spring-boot-starter-jdbc in pom.xml

No need to add any parameter in jdbc url.

Refer to http://www.h2database.com/html/cheatSheet.html

I guess it might be problem with the jdbc.url, change it like this:

# from:
spring.datasource.url=jdbc:h2:file:~/test;DB_CLOSE_ON_EXIT=FALSE

# to:
spring.datasource.url=jdbc:h2:~/test;DB_CLOSE_ON_EXIT=FALSE

Using the following setting on application.properties, I manage to keep the data persisted even after shutting down and restarting SpringBoot, and even after restarting the computer.

spring.datasource.name=japodb
spring.datasource.initialize=false
spring.datasource.driverClassName=org.h2.Driver

spring.datasource.url=jdbc:h2:file:~/japodb;DB_CLOSE_ON_EXIT=FALSE;IFEXISTS=TRUE;DB_CLOSE_DELAY=-1;

Don't Close a Database when the VM Exits, yes, but also don’t make a new database if it’s already there.

jdbc:h2:<url>;IFEXISTS=TRUE

spring.jpa.hibernate.ddl-auto = update

Here is the configuration worked for me

#File based h2 DB
spring.datasource.url=jdbc:h2:file:C:/temp/test_db;DB_CLOSE_ON_EXIT=FALSE;AUTO_RECONNECT=TRUE;DB_CLOSE_DELAY=-1
#In memory
#spring.datasource.url=jdbc:h2:mem:testdb:security_permission;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE
spring.datasource.driverClassName=org.h2.Driver
spring.h2.console.enabled=true
spring.h2.console.path=/h2-console
spring.jpa.hibernate.ddl-auto=update
spring.datasource.username=user
spring.datasource.password=admin
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
#Use datasource.initialization-mode if you are configured to use file based h2 and data.sql
spring.datasource.initialization-mode=always
spring.jpa.defer-datasource-initialization=true

When I used data initialization with data.sql, I have to use the following option to make it work

spring.datasource.initialization-mode=always

After the data is initialized, it can be set to never

spring.datasource.initialization-mode=never

Just generated a brand new Spring Boot project with start.spring.io with a few dependencies h2, JPA, web, devtools, actuator. After adding a simple Entity and Spring Data repository, the database is indeed created in memory by default.

Adding the following to my application.properties definitely creates the database file in the right place:

spring.datasource.url=jdbc:h2:file:~/test;DB_CLOSE_ON_EXIT=FALSE
spring.datasource.username=test
spring.datasource.password=test
spring.datasource.driverClassName=org.h2.Driver

I can even connect to it with the H2 console when devtools is enabled http://localhost:8080/h2-console/.

The next logical step is to visit the http://localhost:8080/autoconfig endpoint and check the auto-configuration status.

In my case, the following is positiveMatches:

DataSourceAutoConfiguration.NonEmbeddedConfiguration: [
{
  condition: "DataSourceAutoConfiguration.NonEmbeddedDataSourceCondition",
  message: "supported DataSource class found"
},
{
  condition: "OnBeanCondition",
  message: "@ConditionalOnMissingBean (types: javax.sql.DataSource,javax.sql.XADataSource; SearchStrategy: all) found no beans"
}
],

and the following in negativeMatches:

DataSourceAutoConfiguration.EmbeddedConfiguration: [
{
  condition: "DataSourceAutoConfiguration.EmbeddedDataSourceCondition",
  message: "existing non-embedded database detected"
}
],

Could you try the following and check the auto-configuration report for those?

Create a file .h2.server.properties in your class path and put below things and try again. You can create this file in resources folder.

#H2 Server Properties
0=H2 File|org.h2.Driver|jdbc\:h2\:file\:~/test;DB_CLOSE_ON_EXIT=FALSE

# Enable if you want other applications to connect
#webAllowOthers=true
#webPort=8082
#webSSL=false