H2 URL with PostgreSQL mode and jOOQ

Just a quick code snippet to show my future self how I was able to finally get H2 working with a jOOQ query that in production hits a PostgreSQL database. I spent hours trying to get this to work. H2 kept complaining about not being able to find the public schema when our jOOQ query was attempting to execut a query like select * from "public"."customer". It's wasn't the schema it was having trouble with but the quotes. I figured this out by pulling up the H2 console and trying the query with and without quotes and without the quotes it worked. So then I figured out that I needed to somehow tell H2 this was for PostgreSQL. H2 has a MODE option that you can set.

The following is the code snippet that finally worked. I have not optimized it. The url value contains all my attempts to get this thing to work. I think it can definitely be trimmed down but that's out of scope for this blog. I'm just trying to save others some time including my future self.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

import org.springframework.boot.jdbc.DataSourceBuilder;   DataSourceBuilder.create()     .driverClassName("org.h2.Driver")     .url("jdbc:h2:~/.h2/testdb"         + ";TRACE_LEVEL_SYSTEM_OUT=3"         + ";INIT=create schema if not exists public AUTHORIZATION sa\\"         + ";RUNSCRIPT FROM 'classpath:schema.sql'"         + ";DB_CLOSE_ON_EXIT=FALSE"         + ";SCHEMA=public"         + ";MODE=PostgreSQL"         + ";DATABASE_TO_LOWER=TRUE")     .username("sa")     .password("")     .build();