Thursday, October 8, 2020

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.
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();