The State of JDBC

[JDBC (Java DataBase Connectivity)](https://download.oracle.com/otn-pub/jcp/jdbc-4_3-mrel3-eval-spec/jdbc4.3-fr-spec.pdf ) is a specification on how clients implemented in Java can work with (relational) databases.

Implementing a JDBC driver from the ground up is not a trivial task. The JDBC specification is detailed and covers a large API surface.

Many open-source and proprietary databases implement a JDBC driver.

# Postgres compatibility

At an early stage, CrateDB decided to be Postgres compatible. The advantage of this approach includes

* The burden of the implementing drivers for popular programming languages and runtimes is significant lower as it is possible to leverage stock Postgres drivers
* Tools, integrations, and applications which support Postgres, can be used in many cases. As the market (both open-source and proprietary) for Postgres compatible software is large, a large software catalogue is available with little effort.

As a database is complex software, Postgres compatibility is multi-faceted. While not an exhaustive list, this includes

* The implementation of the Postgres Wire Protocol to allow stock Postgres drivers to seamlessly connect.
* The SQL variant/dialect and data types which Postgres implements. The SQL standard does not specify geospatial data format and nested data structures.
* Internal tables within Postgres to represent metadata.
* Built-in functions and operators.

# Stock Postgres driver

The stock Postgres JDBC driver2 is used by many tools, integrations, and applications. It is developed as an open-source project by a small and dedicated team.

While the stock Postgres driver works generally well with CrateDB, we experience issues from time to time.

The source of the issues is typically related to the fact that CrateDB is not fully compatible. In particular, the driver relies on internal tables to provide metadata for the applications. For example, the driver will be able – as required by the JDBC specification – to query for primary keys. If the internal tables are not mapped 1-to-1, an application might fail.

Using the testing in the [crate/crate-qa](https://github.com/crate/crate-qa) repository, we can validate new versions of the stock driver to discover any breaking changes early. Currently, a few tests are disabled due to incompatibility issues (GitHub issues exist for these cases).

It is possible for us to send patches to the stock Postgres JDBC driver. When submitting a patch to an open-source project, there is a risk that it will not be accepted in a timely manner. A long-term strategy is to work with the stock driver team to gain their trust so future patches will be reviewed and merged swiftly.

# CrateDB driver

We have a [custom JDBC driver](https://github.com/crate/crate-jdbc) to handle the incompatibilities between CrateDB and the stock JDBC driver. The custom driver is built on top of the stock driver, and it consists of a number of changes to cover corner cases in compatibility.

As it has not been maintained actively for a long period, it has been falling behind the stock driver. Unfortunately, the stock driver has changed significantly. This implies that updating the custom driver is not a trivial task.

# Going forward

For Java developers – and tools, integrations, and applications implemented in Java – CrateDB offers two drivers: the stock JDBC driver and the custom JDBC driver.

To provide the best developer experience for Java developers, we need to ensure that one of the two drivers can be used. In case of incompatibility, we should consider where to fix the issue.

* If the incompatibility can be fixed in the server, all drivers, tools and integrations will benefit immensely. This is not always possible without a major effort.
* Patching the stock driver should be the second option. As we use the stock driver as base for our custom driver, the patch will benefit the custom driver too.
* The last resort is to fix the incompatibility in the custom driver.

Start by reading the linked crate/crate-qa testing repository and comparing the stock PostgreSQL JDBC driver with the custom crate-jdbc driver. The issue names compatibility concerns but no source files, tests, entry point, or acceptance criteria; a concrete scope and definition of done are needed before implementation can begin.

Written by the indexing model from the issue text.