Fixing Spring Boot H2 Exception: “Schema not found”

Omozegie AziegbeSeptember 24th, 2024Last Updated: September 24th, 2024

0 268 3 minutes read

The H2 “Schema Not Found” exception is a common issue that occurs when using Spring Boot with an H2 in-memory database. This error is generally caused by a mismatch between the specified schema in the database and the schema defined in the entity class.

In this article, we’ll explore the cause of the error, the setup where it typically occurs, and multiple ways to resolve it.

1. The Error Explained

This error occurs when H2 tries to access a database schema that hasn’t been created or set up properly. Several factors can contribute to this issue:

Database Initialization Issues: If the database isn’t correctly initialized during application startup, H2 may fail to create the required tables or schemas.
Spring Boot Configuration: Misconfigurations in the application.properties or application.yml files can cause H2 to be unaware of the expected schemas, leading to the error.

When using H2 with Spring Boot, if a schema is defined in an entity using the @Table annotation, as shown in the example below, that schema must exist in the database. H2 will throw a Schema Not Found exception if the schema is missing.

@Entity
@Table(name = "employee", schema = "company")
public class Employee {
    // Fields, getters, setters, and annotations
}

In the above example, the schema is defined as company. If the H2 database does not automatically create this schema, we will encounter the Schema Not Found error.

1.1 Reproducing the “Schema Not Found” Error

To reproduce the “Schema not found” error, we will create a basic Spring Boot application with H2 and Hibernate.

Add Configuration in application.properties

# H2 Database configuration
spring.h2.console.enabled=true
spring.datasource.url=jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=password

# Disable automatic schema generation
spring.jpa.hibernate.ddl-auto=none
spring.jpa.show-sql=true

Create the User Entity

@Entity
@Table(name = "employee", schema = "company")
public class Employee {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;
    private String email;

    // Getters and Setters
}

Create the UserRepository

public interface EmployeeRepository extends JpaRepository<Employee, Long> {
}

@RestController
@RequestMapping("/employees")
public class EmployeeController {

    @Autowired
    private EmployeeRepository employeeRepository;

    @GetMapping
    public List<Employee> getAllEmployees() {
        return employeeRepository.findAll();
    }
}

When the application is started and http://localhost:8080/employees is accessed, the “Schema not found” error will appear.

spring boot h2 exception schema not found error

2. Proposed Solutions for Fixing the “Schema Not Found” Exception

2.1 Schema Creation via Database URL

The simplest solution to fix the “Schema Not Found” error is to create the schema directly in the H2 database URL itself. We can specify schema creation commands in the spring.datasource.url property, allowing it to execute when the database starts.

To apply this solution, modify the spring.datasource.url property to include the schema creation SQL command and allow Hibernate to automatically create the schema by updating the spring.jpa.hibernate.ddl-auto property. Setting this to update or create will allow Hibernate to manage schema creation.

spring.datasource.url=jdbc:h2:mem:company;INIT=CREATE SCHEMA IF NOT EXISTS company;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE
spring.jpa.hibernate.ddl-auto=create

INIT=CREATE SCHEMA IF NOT EXISTS company: This instructs H2 to create the schema company at startup if it doesn’t already exist.
By setting the spring.jpa.hibernate.ddl-auto to create, Spring Boot will automatically create the schema and tables based on the entity configuration. In this case, the company schema will be created automatically, resolving the error.

2.2 Manually Create the Schema Using SQL Scripts

If we want to keep spring.jpa.hibernate.ddl-auto=none, we can manually create the schema using an SQL script to avoid automatic schema creation.

First, Create an SQL script in the src/main/resources directory named schema.sql:

-- Create the schema
CREATE SCHEMA IF NOT EXISTS company;

-- Create the employee table inside the company schema
CREATE TABLE IF NOT EXISTS company.employee (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL
);

CREATE SCHEMA IF NOT EXISTS company; ensures that the company schema is created if it doesn’t already exist.
CREATE TABLE IF NOT EXISTS company.employee creates the employee table inside the company schema

Next, Enable schema.sql execution by adding the following property to application.properties:

spring.datasource.schema=classpath:schema.sql
spring.jpa.hibernate.ddl-auto=none

With this setup, Spring Boot will execute the schema.sql file at startup, which will create the company schema if it doesn’t already exist.

3. Conclusion

In this article, we examined the “Schema Not Found” exception in Spring Boot with H2, caused by a missing or improperly initialized schema. We discussed solutions like enabling automatic schema creation, using SQL scripts, and creating the schema via the H2 database URL. Implementing one of these methods ensures the schema is available at startup, resolving the issue.