CockroachDB – an Alternative to PostgreSQL

The project CockroachDB offers a completely new kind of database. The CockroachDB is a distributed database optimized for container based environments like Kubernetes. The database is Open Source and hosted on Github. CoackroachDB implements the standard PostgreSQL API so it should work with Java Persistence API (JPA). But the CockroachDB does not fully support transaction API with the same isolation level as a PostgreSQL DB. Transactions are important for Java Enterprise applications in combination with JPA – so it may work but it is not as easy as it should be.

Anyway, CockroachDB has a build in replica mechanism. This allows to replicate the data over several nodes in your cluster. With is Automated-Repair feature the database detects data inconsistency on read and write and automatically and fixes faulty data.

So it seems worth to me to test it in combination with Imixs-Workflow which we typically run with PostgreSQL. In the following I will show how to setup the database with docker-compose and run it together with the Imixs-Process-Manager.

Docker-Compose

To run a test environment with Imixs-Workflow I use docker-compose to setup 3 database nodes and one instance of a Wildfly Application server running the Imixs-Process-Manager.

version: "3.6"
services:

  db-management:
    # this instance exposes the management UI and other instances use it to join the cluster
    image: cockroachdb/cockroach:v20.1.0
    command: start --insecure --advertise-addr=db-management
    volumes:
      - /cockroach/cockroach-data
    expose:
      - "8080"
      - "26257"
    ports:
      - "26257:26257"
      - "8180:8080"
    healthcheck:
      test: ["CMD", "/cockroach/cockroach", "node", "status", "--insecure"]
      interval: 5s
      timeout: 5s
      retries: 5

  db-node-1:
    image: cockroachdb/cockroach:v20.1.0
    command: start --insecure --join=db-management --advertise-addr=db-node-1
    volumes:
      - /cockroach/cockroach-data
    depends_on:
      - db-management

  db-node-2:
    image: cockroachdb/cockroach:v20.1.0
    command: start --insecure --join=db-management --advertise-addr=db-node-2
    volumes:
      - /cockroach/cockroach-data
    depends_on:
      - db-management

  db-init:
    image: cockroachdb/cockroach:v20.1.0
    volumes:
      - ./scripts/init-cockroachdb.sh:/init.sh
    entrypoint: "/bin/bash"
    command: "/init.sh"
    depends_on:
      - db-management

  imixs-app:
    image: imixs/imixs-process-manager
    environment:
      TZ: "CET" 
      LANG: "en_US.UTF-8"  
      JAVA_OPTS: "-Dnashorn.args=--no-deprecation-warning"
      POSTGRES_CONNECTION: "jdbc:postgresql://db-management:26257/workflow-db"
      POSTGRES_USER: "root"
      POSTGRES_PASSWORD: "dummypassword"
    ports:
      - "8080:8080"
      - "8787:8787"

You can start the environment with

$ docker-compose up

The root user is created by default for each cluster which is running in the ‘insecure’ mode. The root user is assigned to the admin role and has all privileges across the cluster. To connect to the database using the PostgreSQL JDBC driver, the user root with a dummy password can be provided. Note: this is for test and development only. For production mode you need to start the cluster is the ‘secure mode’. See details here.

The Web UI

CockroachDB comes with a impressive Web UI which I expose on port 8180. So you can access the Web UI form your browser:

http://localhost:8180/

Create a Database

The Web UI has no interface to create users or databases. So we need to do this using the PostgreSQL command line syntax. For that open a bash in one of the 3 database nodes

$ docker exec -it imixsprocessmanager_db-management_1 bash

Within the bash you can enter the SQL shell with:

$ cockroach sql --insecure
#
# Welcome to the CockroachDB SQL shell.
# All statements must be terminated by a semicolon.
# To exit, type: \q.
#
# Server version: CockroachDB CCL v20.1.0 (x86_64-unknown-linux-gnu, built 2020/05/05 00:07:18, go1.13.9) (same version as client)
# Cluster ID: 90ece5f6-2bb7-40c6-9c1d-d758cc954509
#
# Enter \? for a brief introduction.
#
root@:26257/defaultdb>

now you can create an empty database for the Imixs-Workflow system:

> CREATE DATABASE "workflow-db";

That’s it! When you restart you deployment, the Imixs-Workflow engine successfully connects to CoackroachDB using the PSQL JDBC Driver. In the future I will provide some additional posts about running CockRoach in a Kubernetes Cluster based on the Open Source environment Imixs-Cloud.

NOTE: Further testing shows that the weak isolation level support of ACID transactions in CockroachDB makes it risky to run it in more complex situation. For that reason the Imixs-Workflow project does NOT recommend the usage of CockroachDB. See also the discussion here.