Support multiple databases via environment variables only, with no config file

[jrobison-op]

The problem

When you want to run pghero with one single database, you can pull the existing ankane/pghero Docker image and inject a DATABASE_URL variable by way of your cloud platform (Amazon ECS task definition, etc). This is super easy because you don't need a build pipeline to build your own image, you don't need a git repository, etc. You just pull the image and run it with an environment variable.

But if you use multiple databases, then there's no support for the above pattern. You have to resort to one of the below two patterns, neither of which are very appealing:

1. Build your own custom image with a custom config file, which usually means you need:

• a git repository to hold the config file and the custom Dockerfile
• a build pipeline to build the custom image
• an image repository (ECR, etc)
• multiple environment variables like DATABASE_URL_1, DATABASE_URL_2, etc, to provide the actual connection strings referenced in the config file

2. Use a janky workaround where you override the ankane/pghero command and build the config file on the fly, then execute puma -C /app/config/puma.rb to start the service, like this:

echo 'databases:'                             > /app/config/pghero.yml && \
echo '  main:'                               >> /app/config/pghero.yml && \
echo '    url: <%= ENV["DATABASE_URL_1"] %>' >> /app/config/pghero.yml && \
echo '  secondary:'                          >> /app/config/pghero.yml && \
echo '    url: <%= ENV["DATABASE_URL_2"] %>' >> /app/config/pghero.yml && \
puma -C /app/config/puma.rb

Proposed solution

The app should look for environment variables named DATABASE_URL_1, DATABASE_URL_2, DATABASE_URL_N and use those without requiring any config file.

Thanks.

[jrobison-op]

And in case anyone else stumbles across this with the same use case, here's how I implemented option 2 but without having to echo a bunch of YAML-indented strings:

locals {
  pghero_config = {
    databases = merge(
      {
        MainDatabase = {
          url                        = "<%= ENV['MAIN_DATABASE_URL'] %>"
          aws_db_instance_identifier = aws_db_instance.main.id
        }
        SecondDatabase = {
          url                        = "<%= ENV['SECOND_DATABASE_URL'] %>"
          aws_db_instance_identifier = aws_db_instance.second.id
        }
      },
    )
    time_zone = "UTC"
  }
}


resource "aws_ecs_task_definition" "pghero" {
  ...

  container_definitions = jsonencode(
    [
      {
        name       = "pghero"
        image      = "ankane/pghero:v3.8.0"
        entryPoint = ["/bin/sh", "-c"]
        command = [
          join("\n", [
            "cat > /app/config/pghero.yml <<'YAML'",
            yamlencode(local.pghero_config),
            "YAML",
            "exec bundle exec puma -C /app/config/puma.rb",
          ])
        ]
        ...
      },
    ]
  )
}