Issue with connecting to specific Postgres databases

💬 Self Hosted Retool
1

Hi everyone,

I've been struggling with this issue for two days and could really use some help as I'm blocked.

Context

I have a local Postgres instance running with four databases:

  • production
  • sandbox
  • hammerhead_production
  • postgres

Docker Setup

I've set up two services in my docker-compose.yml file as follows:

services:
  db:
    image: postgres:13
    env_file: ./docker.env
    networks:
      - backend-network

  db2:
    image: postgres:13
    env_file: ./db2.env
    networks:
      - backend-network

I can connect to these Docker containerized Postgres instances using a database client (Navicat) without any issues.

I can get Docker to properly spin up Retool by pointing the docker compose file at the db service. However, when I try to create a resource in Retool to connect to db, it automatically connects to the hammerhead_production database and only shows those tables. Similarly, when I try to create a resource to connect to db2, it defaults to the postgres database (even though I have specified the sandbox database).

I cannot select the database I want to connect to in Retool. No matter which database I specify in the docker-compose.yml file or in the Retool resource configuration, it always defaults to hammerhead_production (for db) or postgres (for db2). I need to connect to the sandbox database.**

What I've tried

  1. Ensured that the docker.env and db2.env files correctly specify the desired database:
POSTGRES_DB=sandbox
POSTGRES_USER=my_user
POSTGRES_PASSWORD=my_password
POSTGRES_HOST=db (or db2)
  1. Explicitly set the POSTGRES_DB environment variable in the docker-compose.yml file:
services:
  db:
    image: postgres:13
    env_file: ./docker.env
    environment:
      POSTGRES_DB: sandbox
    networks:
      - backend-network

  db2:
    image: postgres:13
    env_file: ./db2.env
    environment:
      POSTGRES_DB: sandbox
    networks:
      - backend-network
  1. Restarted the Docker containers to apply changes:
docker-compose down
docker-compose up -d

Halp

Has anyone encountered a similar issue or have any suggestions on how to get Retool to connect to the correct database? Any advice would be greatly appreciated.

Thank you!

2

Hi @retoolnewb, welcome to the forum! :wave:

Before we touch on how to we could implement this, let's take a step back. Why is that we need all these different databases to spin up the instance?

We only really need one, that holds on all of your apps, resources, configs, etc. On a standard self-hosted setup, we would only have another one if we would like to use RetoolDB, as it provides a user friendly UI to work with your data without having to write much code.

Once we deploy our instance, we can connect to as many databases as we would like by creating new Resources, and that can be done through the UI.