Run a Postgres Database Locally

Introduction

This guide will take you through setting up a local Postgres connector and a local Postgres database along with Adminer, a free database manager tool, using the DDN CLI and Docker Compose.

You can use any database management tool you prefer, we're using Adminer here. Some free and paid alternatives are: pgAdmin, dBeaver, psql, TablePlus, DataGrip, etc.

Step 1: Setup the Postgres data connector

Follow the quickstart and, during the ddn connector init -i step, choose the hasura/postgres connector. Select all the default options.

You'll see the following output:

HINT To access the local Postgres database:
- Run: docker compose -f app/connector/mypostgres/compose.postgres-adminer.yaml up -d
- Open Adminer in your browser at http://localhost:9822 and create tables
- To connect to the database using other clients use postgresql://user:[email protected]:8105/dev

This will initialize a connector directory for you that contains a compose.postgres-adminer.yaml file that is configured to run Postgres and Adminer locally.

The output of the ddn connector init -i command shows you the docker compose command that can be used to run the database and adminer, the URL at which the Postgres database will be available and the URL at which will be able to access Adminer.

If you already have a Postgres connector and just want to set up the local database, you can use the following docker compose template which is what the DDN CLI also initializes:

compose.postgres-adminer.yaml

configs:
  adminer-index.php:
    content: |
      <?php
        if(!count($$_GET)) {
          $$_POST['auth'] = [
            'server' => $$_ENV['ADMINER_DEFAULT_SERVER'],
            'username' => $$_ENV['ADMINER_DEFAULT_USERNAME'],
            'password' => $$_ENV['ADMINER_DEFAULT_PASSWORD'],
            'driver' => $$_ENV['ADMINER_DEFAULT_DRIVER'],
            'db'    => $$_ENV['ADMINER_DEFAULT_DB'],
          ];
        }
        include './adminer.php';
      ?>
services:
  adminer:
    configs:
      - source: adminer-index.php
        target: /var/www/html/index.php
    depends_on:
      - postgres
    environment:
      ADMINER_DEFAULT_DB: dev
      ADMINER_DEFAULT_DRIVER: pgsql
      ADMINER_DEFAULT_PASSWORD: password
      ADMINER_DEFAULT_SERVER: postgres
      ADMINER_DEFAULT_USERNAME: user
    image: adminer:latest
    ports:
      - 8081:8080 # Run Adminer on port 8081
    restart: unless-stopped
  postgres:
    environment:
      POSTGRES_DB: dev
      POSTGRES_PASSWORD: password
      POSTGRES_USER: user
    image: postgres:latest
    ports:
      - 5432:5432 # Run Postgres on port 5432
    restart: unless-stopped
    volumes:
      - db_data:/var/lib/postgresql/data
volumes:
  db_data: {}

Step 2: Start Postgres database and Adminer

To get Postgres and Adminer up and running, use the docker compose command shown in the output of ddn connector init -i which would be something like below:

docker compose -f app/connector/mypostgres/compose.postgres-adminer.yaml up -d

If you are creating your own docker compose, then change the path to the compose file accordingly.

Step 3: Manage your database via Adminer

To access Adminer, head to the Adminer URL shown in the output of ddn connector init -i.

If you are creating your own docker compose, head to the localhost endpoint opened by your Adminer container.

You should see the Adminer console as follows:

You can now create tables, add data, etc. on the Postgres database and iterate on your database schema.

Next Steps

Iterate on your database schema or connect the database to your supergraph

Now that you have a running Postgres database with the tables you created, you can iterate on it using the steps in the quickstart.

If you created your own docker compose, you can connect the Postgres database to your supergraph using the Postgres data connector by following the steps in this guide and providing the database connection string as the following:

Connection string to connect to local supergraph

postgresql://user:[email protected]:5432/dev

Connecting to your local database from the cloud

Note that as this is a locally running database you can connect to it directly from the data connector running locally, however, if you deploy your connector to Hasura DDN, the cloud-hosted version of your data connector won't be able to find your local database. You'll need to use a tool like ngrok to provide a tunnel to access your database from the cloud. This will expose the port, most likely 5432, on which the database is running and allow Hasura DDN to connect to it.

Connect to the database directly

You can also connect to the database directly using other tools, such as psql, using the database connection string:

To connect using psql, run:

psql postgresql://user:password@localhost:5432/dev