Experiment with DBs and auto API generation

CockroachDB

We will use Docker to run everything. Because we need to run more than one docker image and this is a toy project we will use docker compose.

The first service is the database. We will use cockroach database.

I created the first version of docker-compose.yml based on two tutorials linked above. To test run docker-compose up and connect http://localhost:8080/.

External client

(optional)

Let's connect to the database with an external client.

Start compose

docker-compose up

Use DB client of your preference. I would use pgcli.

pgcli "postgresql://root@localhost:26257/defaultdb?sslmode=disable"
Server: PostgreSQL CCL
Version: 2.0.2
Chat: https://gitter.im/dbcli/pgcli
Mail: https://groups.google.com/forum/#!forum/pgcli
Home: http://pgcli.com
root@localhost:defaultdb> Exception in thread completion_refresh:
Traceback (most recent call last):
  File "python/Frameworks/Python.framework/Versions/3.7/lib/python3.7/threading.py", line 917, in _bootstrap_inner
    self.run()
  File "python/Frameworks/Python.framework/Versions/3.7/lib/python3.7/threading.py", line 865, in run
    self._target(*self._args, **self._kwargs)
  File "pgcli/2.0.2/libexec/lib/python3.7/site-packages/pgcli/completion_refresher.py", line 68, in _bg_refresh
    refresher(completer, executor)
  File "pgcli/2.0.2/libexec/lib/python3.7/site-packages/pgcli/completion_refresher.py", line 111, in refresh_tables
    completer.extend_foreignkeys(executor.foreignkeys())
  File "pgcli/2.0.2/libexec/lib/python3.7/site-packages/pgcli/pgcompleter.py", line 259, in extend_foreignkeys
    for fk in fk_data:
  File "pgcli/2.0.2/libexec/lib/python3.7/site-packages/pgcli/pgexecute.py", line 614, in foreignkeys
    cur.execute(query)
psycopg2.NotSupportedError: unimplemented at or near ")"
DETAIL:  source SQL:

                SELECT s_p.nspname AS parentschema,
                       t_p.relname AS parenttable,
                       unnest((
                        select
                            array_agg(attname ORDER BY i)cs-mode     Refreshing completions...
                                                        ^
HINT:  See: https://github.com/cockroachdb/cockroach/issues/23620

>

Sample database

Let's set up sample database.

There are a lot of them (but not all work out of the box):

I picked up opensourceshakespeare.

docker-compose up -d
  Starting db-experiment_db_1 ... done
docker exec -it db-experiment_db_1 ./cockroach sql --insecure
root@:26257/defaultdb> IMPORT PGDUMP 'https://raw.githubusercontent.com/stereobooster/opensourceshakespeare/master/shakespeare.sql';

Autogenerated API

Postgraphile

Let's add Postgraphile. To do this we can use official docker image. But first, let's try it locally

npx postgraphile -n 0.0.0.0 --connection "postgresql://root@localhost:26257/defaultdb?sslmode=disable" --schema defaultdb --watch

A fatal error occurred when building the initial schema, so the process will now exit. Error details:

error: syntax error at or near "operator"
    at Connection.parseE (/postgraphile/node_modules/pg/lib/connection.js:601:11)
    at Connection.parseMessage (/postgraphile/node_modules/pg/lib/connection.js:398:19)
    at Socket.<anonymous> (/postgraphile/node_modules/pg/lib/connection.js:120:22)
    at Socket.emit (events.js:182:13)
    at addChunk (_stream_readable.js:283:12)
    at readableAddChunk (_stream_readable.js:264:11)
    at Socket.Readable.push (_stream_readable.js:219:10)
    at TCP.onStreamRead [as onread] (internal/stream_base_commons.js:94:17)

Doesn't work :(. Need to try with PostgreSQL.

Hasura

Let's add Hasura section to the docker compose.

docker-compose up
...
hasura_1  | {"internal":{"statement":"SET LOCAL \"hasura.user\" = '{\"x-hasura-role\":\"admin\"}'","prepared":false,"error":{"exec_status":"FatalError","hint":"See: https://github.com/cockroachdb/cockroach/issues/32562","message":"unimplemented at or near \"hasura.user\"","status_code":"0A000","description":"source SQL:\nSET LOCAL \"hasura.user\" = '{\"x-hasura-role\":\"admin\"}'\n          ^"},"arguments":[]},"path":"$","error":"postgres query error","code":"postgres-error"}