[Solved] Spring Boot H2 – Schema not found

Learn to solve the ‘JdbcSQLSyntaxErrorException: schema not found‘ error in a Spring boot application that uses the H2 database for data operations.

1. Problem

The error happens when we try to perform DDL or DML operations on the in-memory H2 database for which schema is expected to be created in runtime. The error looks like this:

org.springframework.dao.InvalidDataAccessResourceUsageException: could not prepare statement; SQL [insert into users.....
...
...
'Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Schema "USERS" not found; SQL statement:
insert into users.tbl_users (email, first_name, last_name, id) values (?, ?, ?, ?) [90079-214]

2. Solution

To fix this issue, we have two approaches:

2.1. Use INIT Statement in Connection String

This is the most straightforward solution which checks for the existing schema while making the connection. If the schema does not exist, it will create the specified schema.

spring.datasource.url=jdbc:h2:mem:USERS;DB_CLOSE_DELAY=-1;INIT=CREATE SCHEMA IF NOT EXISTS USERS;

2.2. Use an Existing Schema Stored in Filesystem

H2 database allows to create a persistent database/schema that we can specify using the connection string. Make sure that the specified schema is already created in past, by some other application, previous run of the application or manually.

spring.datasource.url = jdbc:h2:file:/data/sample

When we run the application with above said change, we will not see the schema not found error.

Happy Learning !!