Can we use PostgREST with CockroachDB

Question

PostgREST serves a fully RESTful API from any existing PostgreSQL database.

CockroachDB promises distributed SQL advantages.

CockroachDB is built to be largely compatible with PostgreSQL. Here is a detailed blog on this

If we can bring these two together that's a powerful combination. We could automatically generate RESTful API for CockroachDB using PostgREST.

Is PostgREST compatible with CockroachDB ?

Has anyone used this combination on a production application ?

Answer 1

Like Steve Chavez commented, PostgREST is not currently compatible with CockroachDB since CockroachDB doesn't support SET LOCAL. See https://github.com/cockroachdb/cockroach/issues/32562#issuecomment-662105805 for more details.

Answer 2

There is a project called universal-database-proxy-rest. It is very similar to PostgREST but its more simplistic and relies on the database metadata. It is both compatible with CockroachDB and PostgresDB.

Here's an example of how to use with Docker.

Create a docker-compose.yml with the following contents:

version: '3.9'

services:
  crdb:
    image: cockroachdb/cockroach:v22.1.0
    ports:
      - "26257:26257"
    command: start-single-node --insecure
    volumes:
      - ./init-cockroach.sql:/docker-entrypoint-initdb.d/init.sql
    healthcheck:
      test: cockroach sql --insecure
      interval: 1s
      timeout: 3s
      retries: 5
  db-rest-api:
    environment:
      CRB_PASSWORD:
    image: mcadecio/universal-database-proxy-rest:0.0.6
    depends_on:
      crdb:
        condition: service_healthy
    ports:
      - "8010:8010"
    volumes:
      - ./config.json:/usr/database-proxy/config.json

This docker-compose just has 2 services. The CockroachDB and the Rest API that will be generated by the tables found in the DB.

Create a file named init-cockroach.sql with following contents:

CREATE SCHEMA vehicles;

CREATE TABLE vehicles.cars
(
    car_id       bigint primary key not null
        constraint cars_car_id_uindex unique,
    manufacturer varchar(50)        not null,
    doors        bigint default 5
);

INSERT INTO vehicles.cars
VALUES (1, 'BMW', 5),
       (2, 'BMW', 3),
       (3, 'MERCEDES', 5),
       (4, 'MERCEDES', 3);

CREATE TABLE vehicles.wheel
(
    wheel_type varchar(50) primary key not null
);

We will use this to pre-seed our database with some data.

Next create a file name config.json with the following:

{
  "cockroachApi": {
    "enabled": true,
    "host": "0.0.0.0",
    "port": 8010,
    "openApiFilePath": "crbOpenApi.yaml",
    "database": {
      "host": "crdb",
      "port": 26257,
      "username": "root",
      "password": "CRB_PASSWORD",
      "databaseName": "defaultdb"
    }
  }
}

Here we are just setting the port we want the REST API to be exposed and the database connection details.

Now its all setup. Just do: docker compose up or docker-compose up (depending on your docker setup)

On your browser go to http://localhost:8010/docs you will see the Swagger UI and the endpoints available. You can try them out on the browser. Swagger Screenshot

If you want to know more about this project head to: https://github.com/mcadecio/universal-database-proxy-rest

Feel free to provide any feedback.

Answer 3

With the 22.2 release of CockroachDB and beyond, PostgreREST as of version 10.0.0 works with CockroachDB.

Docs pointing to support: https://www.cockroachlabs.com/docs/releases/v22.2#v22-2-0-database-operations