Skip to content

Instantly share code, notes, and snippets.

@osaxma

osaxma/hasura_postgres_docker_notes.md

Last active December 4, 2021 20:59
Show Gist options
  • Save osaxma/7d633a9b97551a27d68d649cf05e346d to your computer and use it in GitHub Desktop.
Save osaxma/7d633a9b97551a27d68d649cf05e346d to your computer and use it in GitHub Desktop.
Download ZIP
Notes on working with Hasura, Postgres and Docker in local development
Raw
hasura_postgres_docker_notes.md

Notes on working with Hasura & Postgres docker containers in a local mac environment.

Table of content:

Hasura

Download the hasura docker-compose file (docker-compose.yml). From Hasura's QuickStart with Docker Guide:

# in a new directory run
wget https://raw.githubusercontent.com/hasura/graphql-engine/stable/install-manifests/docker-compose/docker-compose.yaml
# or run
curl https://raw.githubusercontent.com/hasura/graphql-engine/stable/install-manifests/docker-compose/docker-compose.yaml -o docker-compose.yml

Before running the containers, add the following to the yaml file:

postgres:
  # (optional) add the following to expose the database endpoint if you want to access it locally (e.g. for SchemaSpy):
  # 5432 is the default port of postgres container
  ports:
    - "5432:5432" 
graphql-engine:
  environment:
    # to avoid some error that may appear where the console gets stuck at 'loading ...' -- see reference [1]
    HASURA_GRAPHQL_CONSOLE_ASSETS_DIR: /srv/console-assets

to run the containers together, use the following command in the same directory where the `docker-compose.yml` is: 
[~]$ docker-compose up -d

Now the containers are running. Reruning the command above again (after the containers has stopped) will run the same containers. The containers will not be recreated unless the yaml file has changes. Even then only the container with changes will be recreated. It's worth noting that the compose file mounts the data to a volume called db_data which is part of Docker (see Mounting Postgre volume locally below if you prefer to have the volume outside of Docker).

To stop the containers, you first need to get the containers ids:

# to see the running containers
[~]$ docker ps 

# to stop the containers 
[~] docker stop <container_id> 

# to stop multiple containers
[~] docker stop <container_id> <container_id> 

# to stop all runing containers (-q = --quiet that prints containter-ids only)
[~]$ docker stop $(docker ps -q)

to stop the containers + remove them + remove the networks, volumes, and images that were created by docker-compose, run the following command:

# warning: this will delete the volumes
[~] docker-compose down

Other useful commands for viewing the logs

# view the logs
[~]$ docker logs <container_id>

# to redirect the logs to a file 
# no need to append (i.e, >> ) since the command export the entire log anyway
[~]$ docker logs <container_id> > <file_name>.log 

# to keep the log running in the terminal (-f = --follow)
[~]$ docker logs -f <container_id> 

# to redirect the logs to a file as they are come 
# postgres containger won't redirect to file when with `follow` for some reason
[~]$ docker logs -f <container_id> > output.log

Mounting Postgres volume locally

If you like the to mount the database volume outside the container (local directory), here's an example of how it can be done:

## under postgres container, add the following:
  postgres:
    image: postgres:12
    volumes:
    - db_data:/var/lib/postgresql/data

Then, add the following at volumes:

volumes:
  db_data:
    driver: local
    driver_opts:
      type: 'none'
      o: 'bind'
      device: '~/User/Projects/hasura_project/db_data' # example path in MacOS

Note: there could be an issue with mounting the volume locally as this warning will appear in the logs of postgres container:

WARNING:  could not open statistics file "pg_stat_tmp/global.stat": Operation not permitted

Here's a good explaination to what global.stat is: https://stackoverflow.com/a/32193782/10976714

I still haven't figured out how a fix as it's not causing an issue for local development (if you see this and know how to fix, please comment below).

Adding top level function

(update: I believe after Hasura 2.0, this is done automatically tho I will leave it here for reference)

When adding a top level function (to root query), and you need to access the hasura-session, add the following configuration in order for the query to work:

"functions": [
    {
      "function": {
        "schema": "public",
        "name": "get_session_role" // see example below
      },
      "configuration": {
          "session_argument": "hasura_session" // see details below
      }
    },
  ],

example: Example function

details : details about configruation

references:

[1] github issue: Generic error when modifying database schema

[2] how to redirect docker logs to a single file



Postgres

Query with psql

1- enter the postgres container terminal

[~] docker exec -it <container_id> bin/bash 

# note: hasura container doesn't have bash, so you need to use shell instead
[~] docker exec -it <hasura_container_id> sh

2- To enter the postgres interactive terminal (i.e., psql):

first we need to log in. The username and password are inside the docker-compose.yml file (i.e., postgres & postgrespassword respectively).

# the username and password are inside the docker-compose.yml 
# default: username=postgres & password=postgrespassword
root@<container_id>: psql -U username -W 
# you'll be promted to enter the password  
password: ******
# now you'll be inside the postgres interactive terminal 
postgres$

3 - interact with the terminal and here's a list of useful commands:

# most commands starts with "\" 
# for help menu:
postgres$ \? 

# list database 
postgres$ \l

# list tables
postgres$ \dt

# to query 
postgres$ SELECT * FROM table_name LIMIT 1;

# list a specific table (e.g. `users` table)
postgres$ \d users 

# to exit 
postgres$ \q  # or simply `exit`

# to exit the container 
root@<container_id>: exit

Finding Triggers and Functions

  • using psql terminal
# to turn on/off expanding display
postgres$ \x 

# find the name of the function
postgres$ \df 

# find the name of the function/trigger based on a schema
postgres$ \df public.* 


# print the function definition (the plus sign will print extra information like the source code)
postgres$ \df+ name_of_function
  • using sql
select proname,prosrc from pg_proc where proname= your_function_name;

Explaining Queries

Explain only:

EXPLAIN SELECT count(*) FROM pg_attribute;

output:

 Aggregate  (cost=119.66..119.67 rows=1 width=8)
 ->  Seq Scan on pg_attribute  (cost=0.00..110.13 rows=3813 width=0)

Explain and Analyze query

EXPLAIN (ANALYZE, buffers, timing) SELECT count(*) FROM pg_attribute;

output:

Aggregate  (cost=119.66..119.67 rows=1 width=8) (actual time=31.324..31.339 rows=1 loops=1)
 Buffers: shared hit=72
 ->  Seq Scan on pg_attribute  (cost=0.00..110.13 rows=3813 width=0) (actual time=0.008..15.608 rows=3813 loops=1)
 Buffers: shared hit=72
Planning Time: 0.129 ms
Execution Time: 31.387 ms

Further reading:



PostgrSQL Data or Schema Dump

Inside the postgres container, there's a client application called: pg_dump that can be used to dump the schema or the data.

# to dump the database:
# don't run this command (see below to dump on a file)
[~]$  docker exec <container_id> pg_dump --dbname=<database_name> --username=<user_name> 
# for example
[~]$  docker exec 233a263da1fc pg_dump --dbname=postgres --username=postgres 
# for some reason the password isn't required

# to export schema only to a file
[~]$ docker exec <container_id> pg_dump -s -d postgres -U postgres > pg_dump_schema_only.psql 

# to export a specific schema and its data to a file
[~]$ docker exec <container_id> pg_dump --schema=public -d postgres -U postgres > pg_dump_public_schema_and_data.psql 
 
# to export a specific schema only (-s means schema only and -n schema name)
[~]$ docker exec <container_id> pg_dump -s -n public -d postgres -U postgres > pg_dump_public_schema_only.psql 


# for more options run
[~]$ docker exec <container_id> pg_dump --help

Here are other: PostgreSQL Client Applications

SchemaSpy

SchemaSpy generates the database to HTML documentation, including Entity Relationship diagrams.

1- create a config file called config/schemaspy.properties and add the following:

# note: make sure there is no spaces after properties 
# `localhost` or `127.0.0.1` doesn't work on mac, use `host.docker.internal`
schemaspy.t=pgsql
schemaspy.host=host.docker.internal
schemaspy.port=5432
schemaspy.db=postgres
schemaspy.u=postgres
schemaspy.p=postgrespassword
schemaspy.schemas=public

2- to generate SchemaSpy html file, run the following:
docker will get the container for you if you don't have it

[~]$ docker run -v $PWD/output:/output -v $PWD/config/:/config/ schemaspy/schemaspy:latest -configFile /config/schemaspy.properties

# $PWD/output is where the output will be on the your machine

3- open the index.html file inside $PWD/output to view the SchemaSpy


GraphQL

GraphQL schema export

get-graphql-schema is an npm package to get the graphql schema from a graphql endpoint. For hasura, the endpoint is shown on the the graphql tab. When running as a local host, the url is http://localhost:8080/v1/graphql:

# install the package with npm:
[~]$ npm install -g get-graphql-schema

# example: generate the schema to a file
[~]$ get-graphql-schema http://localhost:8080/v1/graphql > hasura_graphql_schema.graphql

# if the endpoints are secured, you need to include the admin secret (-h = --header):
[~]$ get-graphql-schema -h 'x-hasura-admin-secret=adminsecret' http://localhost:8080/v1/graphql > hasura_graphql_schema_alllow_list_enabled.graphql

If you like to get the schema for a specific role (e.g. user), it can be done as follow by using multiple headers:

get-graphql-schema -h 'x-hasura-admin-secret=adminsecret' -h 'x-hasura-role=user' http://localhost:8080/v1/graphql > hasura/hasura_graphql_schema_for_user_role.graphql

GraphQL introspection

This is a very useful article about GraphQL introspection:

an example:

GraphQL Output
{
  __schema {
    queryType {
      name
    }
  }
}
 
{
  "data": {
    "__schema": {
      "queryType": {
        "name": "Query"
      }
    }
  }
}

This is useful in Hasura when you like to extract the schema for a specific authenticated type (user, anonymous). By setting the x-hasura-role in the header, the result will only include the schema where the user has permission. Unlike, downloading the entire schema, this can download a user-specific schema.

GraphQL pagination

See this:



Useful VS code extensions

PostgreSQL - run queries and view table from VS code!

[PostgreSQL extention] is a query tool for PostgreSQL databases. While there is a database explorer it is NOT meant for creating/dropping databases or tables. The explorer is a visual aid for helping to craft your queries.

link: https://marketplace.visualstudio.com/items?itemName=ckolkman.vscode-postgres

GraphQL - useful for formatting and highlighting .graphql extension.

GraphQL extension for VSCode adds syntax highlighting, validation, and language features like go to definition, hover information and autocompletion for graphql projects. This extension also works with queries annotated with gql tag.

link: https://marketplace.visualstudio.com/items?itemName=GraphQL.vscode-graphql

pgFormatter helpful for formatting query files (.pgsql, .psql, and other extentions).

A VS Code extension that formats PostgreSQL SQL, using the Perl based pgFormatter tool developed by Gilles Darold.

link: https://marketplace.visualstudio.com/items?itemName=bradymholt.pgformatter

Other Notes

The best reference for PostgreSQL is their documentation. Note that Hasura, at the time of writing this, uses PostgreSQL version 12.

For Hasura & GraphQL, the best referene their documentation & their learn section.

Youtube Channels:

This gist was uploaded and updated with ease using: https://github.com/defunkt/gist

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment