This uses the shared access credentials to the database, and you get http://127.0.0.1:3000 REST API server along with http://127.0.0.1:8080 Swagger UI to play with the APIs interactively:
(you cannot really insert, update, or delete from Indexer for Explorer database using read-only access, but PostgREST does not realize that for some reason and still generates unnecessary endpoints)
Just as an example, here is the type of query you can run:
(there are some custom query selectors that you can use and that will be translated into SQL)
UPD: it is resolved, and here is the docker-compole.yml file I used to get it up and running:
version: '3.6'
services:
postgres:
image: postgres:12
restart: always
#volumes:
1. * db_data:/var/lib/postgresql/data
environment:
POSTGRES_PASSWORD: postgrespassword
graphql-engine:
image: hasura/graphql-engine:v2.0.0
ports:
* "8080:8080"
depends_on:
* "postgres"
#restart: always
environment:
HASURA_GRAPHQL_DATABASE_URL: postgres://public_readonly:nearprotocol@35.184.214.98/testnet_explorer
HASURA_GRAPHQL_METADATA_DATABASE_URL: postgres://postgres:postgrespassword@postgres:5432/postgres
1. enable the console served by server
HASURA_GRAPHQL_ENABLE_CONSOLE: "true" # set to "false" to disable console
2. enable debugging mode. It is recommended to disable this in production
HASURA_GRAPHQL_DEV_MODE: "true"
HASURA_GRAPHQL_ENABLED_LOG_TYPES: startup, http-log, webhook-log, websocket-log, query-log
3. uncomment next line to set an admin secret
1. HASURA_GRAPHQL_ADMIN_SECRET: myadminsecretkey
Story
It will be useful for those who are not very familiar with the backends / databases to be able to get access to the data right from the web apps.
That would be a stretch if we extend our shared public access to that, but we may at least document and recommend people using it.
Solutions
There are a few solutions that can turn PostgreSQL database into API:
PostgREST
I played around with PostgREST, and it is as simple as running this Docker Compose file:
This uses the shared access credentials to the database, and you get http://127.0.0.1:3000 REST API server along with http://127.0.0.1:8080 Swagger UI to play with the APIs interactively:
(you cannot really insert, update, or delete from Indexer for Explorer database using read-only access, but PostgREST does not realize that for some reason and still generates unnecessary endpoints)
Just as an example, here is the type of query you can run:
(there are some custom query selectors that you can use and that will be translated into SQL)
There are also instructions on how to run it on Heroku: https://postgrest.org/en/stable/install.html#deploying-to-heroku
Hasura
I have not tried it yet, but I assume it is a similar experience with GraphQL and OpenAPI support.
P.S. Unfortunately, Hasura does not work with read-only access: https://github.com/hasura/graphql-engine/issues/7029UPD: it is resolved, and here is the docker-compole.yml file I used to get it up and running: