Troubleshooting H2 Database in Spring Boot

H2 is a handy thing to have in your Spring Boot toolbox. It is most known as an in-memory database. That is, it will not persist changes to disk.  Generally, you create your tables during the start-up of your application, handle some data inside the DB and when stopping the application, everything goes away.
This is a powerful concept and allows you to handle a lot of use-cases(e.g: testing).

But H2 can also persist changes when configured to do so. Perhaps you need to store some small amount of data which is only used locally by the application and you don’t need the burden of a full-blown database.
In Spring Boot this is easy to do. You only need to specify the file where the DB should be located with:
spring.datasource.url=jdbc:h2:file:/home/bogdan/my_app/h2db

In one of the applications that I’m working on, I have the need to handle some kind of queue of jobs. I need to store locally pending jobs. Basic CRUD operations are enough for this purpose.
However, in conjunction with JPA (specifically, Hibernate) there was an annoying issue which I had to solve.
While the solution is trivial, the path leading to it have made me hit a few roadblocks.
I document them below.

The Problem

I’ve noticed that while I had added a table on startup and added also some information into that table, the information was gone the next time I’d started the application.
At first, this was a bit baffling since I had used H2 with file persistence in the past, using JdbcTemplate in SpringBoot and it worked flawless.

Accessing H2 console in browser

First thing I had to do was to somehow view the data in the table to see what’s happening.
Spring Boot has a handy console for viewing this information in the browser, on the same host:port as the application where it is embedded.
You can configure by adding the following entries into application.properties:
spring.h2.console.enabled=true
spring.h2.console.path=/h2-console

Then you can easily access the application via localhost:8080/h2-console
For development I use a VM. But I’ve configured the VM to be able to access the application from the host machine.

When trying to access from outside the VM, I got the following message:
“Sorry, remote connections (‘webAllowOthers’) are disabled on this server.”
I did not get this message when accessing from inside the VM.
After a bit of googline I found the solution. Simply add the following entry into application.properties:
spring.h2.console.settings.web-allow-others=true

But then I had another issue. Nothing was visible in the page. Opening the F12 console showed the following output:

Seems that the implementation of H2 console tries to embed content.
X-Frame-Options is a HTTP response header which instructs the browser to not allow embedding of that page via iframe, frame, embed or object.
By default, Spring Boot is very strict about this by setting this directive to “deny”. This is to mitigate clickjacking attacks.

However, at least for the debugging purposes, we need to be able to see this. We can relax a bit the directive by setting it to “sameorigin”.
In Spring Boot you can do that by something like this:

@Configuration
public class WebSecurityConfig extends WebSecurityConfigurerAdapter {
  @Override
  protected void configure(HttpSecurity http) throws Exception {
    headers().frameOptions().sameOrigin()
  }
}

After this change I could see tables in the database.
The entries were indeed in the table.

Offline reading of the DB file

After making sure that the data was inserted correctly into the table I needed to see what happens after the application is closed.
For this I’ve used RazorSQL tool (https://www.razorsql.com/) .
You can connect to the DB on disk by using the same URL used in the application.properties.
Connecting with RazorSQL, I could see the tables with the proper content.

Hibernate DDL operations

At the following step, while closing the application, I did notice an error message in the console.

What caught my eye was the line of logs containing “org.hibernate.tool.hbm2ddl.SchemaExport.drop”.
This suggested that tables are being dropped by Hibernate framework. Probably because it considers this to be in-memory database and tries a cleanup on exit.
I did some investigation ( aka googling ) and found that indeed this is the case. This is referenced here:
https://github.com/spring-projects/spring-boot/issues/1374

The solution for my problem is also there:

spring.jpa.properties.hibernate.hbm2ddl.auto=none

So that’s it. After this, it all went smooth.