Stream: hapi
Topic: CockroachDB
Joseph Shook (Feb 03 2022 at 19:59):
I started experimenting with the HAPI JPA server. My goal is to use it with CockroachDB.
So, for my first approach, I ran it against PostgreSQL and that works great. I loaded the existing Synthea data, one can download for testing.
For CockroachDB, updated my url in application.yaml. It is still prefixed with jdbc:postgresql. And the connection works just fine. The Schema is created. driverClassName remains equal to org.postgresql.Driver and no dialect was selected for jpa:properties:hibernate:dialect. It started and the schema was generated. But I couldn’t do things like just GET queries for Patient on an empty data set let alone insert. OK, so I proceeded to set the dialect like so:
jpa:
properties:
hibernate.format_sql: false
hibernate.show_sql: false
hibernate:
dialect: org.hibernate.dialect.CockroachDB201Dialect
That allowed me to query Patient without errors.
I am not a Java Programmer although I can easily read it and write it as it is very much like C#. But just remember any answers and I would appreciate as much elaboration as possible. My friction with Java isn’t Java it is just understanding the inferred knowledge around things like Maven and how all that orchestration works. I am sure I will use the wrong terminology so pardon me and correct me at will.
That is just background as I get to my questions:
So, during insert I get an error concerning mapping a byte[] to the OID type. The error is “The fastpath function lo_creat is unknown”
The table being updated is HFJ_RES_VER and the column failing is RES_TEXT, type of oid.
I journeyed into the hapi-fhir source. I knew there was some SQL schema files included from the referenced packages I somehow discovered while looking at my hapi-fhir-jpaserver-starter source code. BTW I am running my JPA server at the moment with mvn spring-boot:run -Pboot. And using IntelliJ as my IDE. I found the hapi-fhir-jpaserver-base package where I expected the .sql files to exist but they were not there. I guess this is generated and that would make sense. I learned that these are created via a hibernate53-ddl-vaven-plugin. And I can create it with this command. mvn install -DskipTests=true . Nice! I won’t tell you all the discovery I went through to figure that out.
My question concerns the choice of OID vs BYTEA specifically for PostgreSQL.
What is the benefit of choosing OID?
This is a space that is tough to Google and understand the motivation exactly. Search GitHub didn’t shed any light.
I did notice the hibernate54-ddl-maven-plugin plugin has a custom dialect called ca.uhn.fhir.jpa.model.dialect.HapiFhirPostgres94Dialect rather than the default dialects used for the other 6 database technologies. I started looking at it and it has a very long inheritance chain. I don’t know the exact mechanics of this but I am sure it all integrates with the database migration strategy.
Anyway, for a time I had this incompatibility friction between PostgreSQL and CockRoachDB specifically for byte[] to OID mapping. I even created a ticket with CockroachDB submitting them a very simple Java Unit Test with that distilled problem.
As time passed, I began thinking I should generate a CockroachDB schema and just go rebuild my DB schema with that. So, I did. After all, at some point CockroachDB created a dialect.
How We Built a CockroachDB Dialect for Hibernate (cockroachlabs.com)
Install a Driver or ORM Framework | CockroachDB Docs (cockroachlabs.com)
I am using CockroachDB V21.2.4 on Windows. Just running a insecure, single node (local cluster) on my dev box, so I picked the CockroachDB201Dialect dialect.
So just added this line in the top level pom.xml file next to the custom HapiFhirPostgres94Dialect dialect.
<customDialect>org.hibernate.dialect.CockroachDB201Dialect</customDialect>
So yes, it generated a schema file for called cockroachdb201.sql in the path, …. \hapi-fhir-jpaserver-base\target\classes\ca\uhn\hapi\fhir\jpa\docs\database\cockroachdb201.sql .
I dropped the database, recreated, and applied the cockroachdb201.sql schema. Started my FHIR JPA server configured to talk to CockroachDB. Yes, this started to work. About 93% of the first 144 transaction bundles succeeded. So, I have something new to look at now and can start a new thread on anything I find. If all goes well, I would be willing to create a GitHub issue and create a PR, but I have a bit more to learn about migrations and how I add that framing.
I guess my second question is, am I the only one trying to use CockroachDB? And has anyone thought about this more and willing to share their thoughts. It is a three node minimum cluster technology, build for replication using a line protocol compatible with PostgreSQL and dialect that is very similar and for the most par the same as PostgreSQL.
If you read this far then good for you. :blush:
Patrick Werner (Feb 04 2022 at 09:46):
to my knowledge you are the first one trying to get hapi to run with CockroachDB
Jens Villadsen (Feb 04 2022 at 13:57):
Interesting - (and yes, I read it all). @James Agnew maybe this would be interesting for you too.
Last updated: Apr 12 2022 at 19:14 UTC
