When we initialised Mage in the previous blog we used a docker-compose command spun up two services: Mage & Postgres. This means that they will both communicate in a docker network. Now it's about using the Mage application to speak to Postgres.

• Mage and Postgres Composition
• Configuring Postgres in Mage
• Testing connection in Postres

Mage and Postgres Composition

Firstly, it's worth checking our docker-compose.yml file to see the containers' composition.

version: '3'
services:
  magic:
    image: mageai/mageai:latest
    command: mage start ${PROJECT_NAME}
    env_file:
      - .env
    build:
      context: .
      dockerfile: Dockerfile
    environment:
      USER_CODE_PATH: /home/src/${PROJECT_NAME}
      POSTGRES_DBNAME: ${POSTGRES_DBNAME}
      POSTGRES_SCHEMA: ${POSTGRES_SCHEMA}
      POSTGRES_USER: ${POSTGRES_USER}
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
      POSTGRES_HOST: ${POSTGRES_HOST}
      POSTGRES_PORT: ${POSTGRES_PORT}
    ports:
      - 6789:6789
    volumes:
      - .:/home/src/
      - ~/Documents/secrets/personal-gcp.json:/home/src/personal-gcp.json
    restart: on-failure:5
  postgres:
    image: postgres:14
    restart: on-failure
    container_name: ${PROJECT_NAME}-postgres
    env_file:
      - .env
    environment:
      POSTGRES_DB: ${POSTGRES_DBNAME}
      POSTGRES_USER: ${POSTGRES_USER}
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
    ports:
      - "${POSTGRES_PORT}:5432"

Noteworthy parts:

• There are environment variables being interpolated. These refer to the .env file we create: env_file: -.env. Here, the environment variables will be created in that container. It's also nice that this file .env is contained in the .gitignore so we won't be surfacing secrets in the repo!

  • We're pulling variables locally and injecting them into the docker containers. The passwords in the .env don't exist as code in any of our images, therefore. When working locally, this works perfect for managing secrets.

• Mounting this ensures project persistence. The GCP Service Account key doesn't need to be mounted, it's just simpler:

  volumes:
      - .:/home/src/
      - ~/Documents/secrets/personal-gcp.json:/home/src/personal-gcp.json
  

Configuring Postgres in Mage

We need to jump into the io_config.yaml file to manage our connections in Mage.

Above, we can see two important elements. The postgres connection template and the profile we're currently working in. For this project, for now, we will create a second profile called dev. In here, we will configure just the Postgres connection, like so:

Above, we you will notice that we've interpolated the values of the variables. These are taken from the environment variables. Again, keeping the secrets not in code. We need to pull in the environment variables in Mage is in **ginger templating**:

• "{{}}" with syntax as env_var('')
• All together: "{{env_var('')}}"

Testing connection in Postres

1. Create a Pipeline
2. Add a Block as a Data Loader, configure, and test.

It's worth noting that the ticking the Use raw SQL box in the Data Loader configuration will remove any templating. This helps for testing!

Nevertheless, a successful connection 🚀