In Spring application prevent H2 from transforming identifiers case to upper

Question

I'm using Spring 2.6.2 and have tried both:

testImplementation("com.h2database:h2:2.1.214")

and

testImplementation("com.h2database:h2:1.4.200")

I create database schema's using raw SQL in JDBC connections to db during app startup e.g.

CREATE SCHEMA IF NOT EXISTS "001"
CREATE SCHEMA IF NOT EXISTS "002schema"
CREATE SCHEMA IF NOT EXISTS "3_schema"

Then Liquibase performs database migration.

When run against PostgreSQL, this works fine and schemas are created using the literal String provided. Tables inside those schemas are all lowercase too, as specified in the Liquibase changelogs.

When run against H2 in unit tests, an exception is thrown when performing database migration for 002schema. It looks like Liquibase then expects all schemas to have been upper-cased causing failure.

Exception during application start-up:

Initializing Liquibase for schema 001
Creating database history table with name: "001".DATABASECHANGELOG
Liquibase ran for schema 001
..
Initializing Liquibase for schema 002schema
Exception encountered during context initialization - cancelling refresh attempt: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'liquibaseMultiTenant' defined in class path resource [com/company/app/data/liquibase/LiquibaseConfiguration.class]: Invocation of init method failed; nested exception is liquibase.exception.LockException: liquibase.exception.DatabaseException: Schema "002SCHEMA" not found; SQL statement:
CREATE TABLE "002SCHEMA".DATABASECHANGELOGLOCK (ID INT NOT NULL, LOCKED BOOLEAN NOT NULL, LOCKGRANTED TIMESTAMP, LOCKEDBY VARCHAR(255), CONSTRAINT PK_DATABASECHANGELOGLOCK PRIMARY KEY (ID)) [90079-214] [Failed SQL: (90079) CREATE TABLE "002SCHEMA".DATABASECHANGELOGLOCK (ID INT NOT NULL, LOCKED BOOLEAN NOT NULL, LOCKGRANTED TIMESTAMP, LOCKEDBY VARCHAR(255), CONSTRAINT PK_DATABASECHANGELOGLOCK PRIMARY KEY (ID))]

I've tried the following options in H2 URL to prevent everything being made uppercase but none are working.

Any ideas?

spring:
  datasource:
    platform: h2
    url: jdbc:h2:mem:test;MODE=PostgreSQL;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=false;DATABASE_TO_UPPER=false;
    driver-class-name: org.h2.Driver
    username: sa
    password:

Also tried:

spring:
  datasource:
    platform: h2
    url: jdbc:h2:mem:test;MODE=PostgreSQL;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=false;DATABASE_TO_LOWER=true;CASE_INSENSITIVE_IDENTIFIERS=true;
    driver-class-name: org.h2.Driver
    username: sa
    password:

Don't use identifiers that require quoting, and then the identifiers are no longer case-sensitive. Your problem is another good example why testing against a database that is different than production makes it a meaningless test. — , Oct 19 '22 at 21:43

score 0 · Answer 1 · answered Oct 19 '22 at 20:41

If the name of the schema is double quoted like "002SCHEMA" or "002schema" then it is case sensitive, it is not sensitive otherwise without the quotes:

Are PostgreSQL column names case-sensitive?

maybe you can solve the problem by changing this:

CREATE SCHEMA IF NOT EXISTS "002schema"

to this:

CREATE SCHEMA IF NOT EXISTS "002SCHEMA"

or somehow make liquibase not use quotes on schema name

In Spring application prevent H2 from transforming identifiers case to upper

1 Answers1