Provide some tips on how to get access to the data from web apps

[frol]

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
• Hasura

PostgREST

I played around with PostgREST, and it is as simple as running this Docker Compose file:

version: '3'
 services:
 server:
 image: postgrest/postgrest
 ports:

*   "3000:3000"
     environment:
     PGRST_DB_URI: postgres://public_readonly:[email protected]/mainnet_explorer
     PGRST_DB_SCHEMA: public
     PGRST_DB_ANON_ROLE: public_readonly
     PGRST_SERVER_PROXY_URI: "http://127.0.0.1:3000"
     swagger:
     image: swaggerapi/swagger-ui
     ports:
*   "8080:8080"
     expose:
*   "8080"
     environment:
     API_URL: [http://localhost:3000/](http://localhost:3000/)

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/7029~~

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:[email protected]/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