How to connect to Postgres in GitHub Actions

Question

I am trying GitHub Actions for CI with a Ruby on Rails application.

My setup is with VM, not running the Ruby build in a container.

This is my workflow yml. It runs all the way without errors until the step "Setup Database".

name: Rails CI

on:
  push:
    branches:
    - master
  pull_request:
    branches:
    - master

jobs:
  build:

    runs-on: ubuntu-latest

    services:
      postgres:
        image: postgres:10.10
        env:
          POSTGRES_USER: postgres
          POSTGRES_PASSWORD: postgres
          POSTGRES_DB: db_test
        ports:
          - 5432/tcp
        options: --health-cmd pg_isready --health-interval 10s --health-timeout 5s --health-retries 5
      redis:
        image: redis:latest
        ports:
          - 6379/tcp

    steps:
    - uses: actions/checkout@v1

    - name: Set up ruby 2.5
      uses: actions/setup-ruby@v1
      with:
        ruby-version: 2.5.5

    - name: Set up node 8.14
      uses: actions/setup-node@v1
      with:
        node-version: '8.14'

    - name: Setup system dependencies
      run: sudo apt-get install libpq-dev

    - name: Setup App Dependencies
      run: |
        gem install bundler -v 1.17.3 --no-document
        bundle install --jobs 4 --retry 3
        npm install
        npm install -g yarn

    - name: Run rubocop
      run: bundle exec rubocop

    - name: Run brakeman
      run: bundle exec brakeman

    - name: Setup Database
      env:
        RAILS_ENV: test
        POSTGRES_HOST: localhost
        POSTGRES_USER: postgres
        POSTGRES_PASSWORD: postgres
        POSTGRES_PORT: ${{ job.services.postgres.ports[5432] }}
      run: |
        cp config/database.yml.ci config/database.yml
        bundle exec rails db:create
        bundle exec rails db:schema:load

    - name: Run rspec
      env:
        RAILS_ENV: test
        REDIS_HOST: redis
        REDIS_PORT: ${{ job.services.redis.ports[6379] }}
        POSTGRES_HOST: localhost
        POSTGRES_USER: postgres
        POSTGRES_PASSWORD: postgres
        POSTGRES_PORT: ${{ job.services.postgres.ports[5432] }}
      run: bundle exec rspec --tag ~type:system

I am able to install ruby, node, the images, Postgres as a service, etc, and run Rubocop and Brakeman. But when I try to set up the DB before running Rspec it says it cannot connect to the DB.

As far as I've been able to ascertain, the host is localhost when running the VM configuration as opposed to a container configuration.

This is the database.yml.ci that the "Setup Database" step copies to the database.yml to be used by Rails.

test:
  adapter: postgresql
  encoding: unicode
  database: db_test
  pool: 5
  username: <%= ENV['POSTGRES_USER'] %>
  password: <%= ENV['POSTGRES_PASSWORD'] %>
  host: <%= ENV['POSTGRES_HOST'] %>

I expected Postgres to be correctly set up and bundle exec rails db:create to create the database. However, it throws the following error:

rails aborted!
PG::ConnectionBad: could not connect to server: Connection refused
    Is the server running on host "localhost" (127.0.0.1) and accepting
    TCP/IP connections on port 5432?

I've tried all sorts of different configurations, but unfortunately, Actions is sort of knew and there doesn't seem to be a lot of material available online.

Any ideas on how to fix this?

===========================

EDIT:

So I was able to sort this out through trial and error. I ended up using a docker image with a ruby and node container. This is the working configuration:

on:
  push:
    branches:
    - master
  pull_request:
    branches:
    - master
    - development
    - release

jobs:
  build:

    runs-on: ubuntu-latest

    container:
      image: timbru31/ruby-node:latest

    services:
      postgres:
        image: postgres:11
        env:
          POSTGRES_USER: postgres
          POSTGRES_PASSWORD: postgres
          POSTGRES_DB: ci_db_test
        ports:
          - 5432:5432
        options: --health-cmd pg_isready --health-interval 10s --health-timeout 5s --health-retries 5

    chrome:
        image: selenium/standalone-chrome:latest
        ports:
          - 4444:4444

    steps:
    - uses: actions/checkout@v1

    - name: Setup app dependencies
      run: |
        gem install bundler -v 1.17.3 --no-document
        bundle install --jobs 4 --retry 3
        npm install
        npm install -g yarn

    - name: Run rubocop
      run: bundle exec rubocop

    - name: Run brakeman
      run: bundle exec brakeman

    - name: Setup database
      env:
        RAILS_ENV: test
        POSTGRES_HOST: postgres
        POSTGRES_USER: postgres
        POSTGRES_PASSWORD: postgres
        POSTGRES_DB: ci_db_test
        POSTGRES_PORT: ${{ job.services.postgres.ports[5432] }}
      run: |
        cp config/database.yml.ci config/database.yml
        bundle exec rails db:create
        bundle exec rails db:schema:load

    - name: Run rspec
      env:
        RAILS_ENV: test
        POSTGRES_HOST: postgres
        POSTGRES_USER: postgres
        POSTGRES_PASSWORD: postgres
        POSTGRES_DB: ci_db_test
        POSTGRES_PORT: ${{ job.services.postgres.ports[5432] }}
        SELENIUM_URL: 'http://chrome:4444/wd/hub'
      run: bundle exec rspec

And the CI DB configuration database.yml.ci

default: &default
  adapter: postgresql
  encoding: unicode
  username: <%= ENV['POSTGRES_USER'] %>
  password: <%= ENV['POSTGRES_PASSWORD'] %>
  host: <%= ENV['POSTGRES_HOST'] %>
  pool: 5
  database: <%= ENV['POSTGRES_DB'] %>

test:
  <<: *default

Whilst

Answer 1

I have a slightly different setup but this was the most relevant question when I encountered the same error so wanted to post here in case it can help. The two things that were critical for me were: 1) Set the DB_HOST=localhost 2) Set the --network="host" argument when you start the docker container with your rails app

name: Master Build

on: [push]

env:
  registry: my_registry_name
  # Not sure these are actually being passed down to rails, set them as the default in database.yml
  DB_HOST: localhost
  DB_USERNAME: postgres
  DB_PASSWORD: postgres

jobs:
  my_image_test:
    runs-on: ubuntu-latest

    services:
      postgres:
        image: postgres:latest
        env:
          POSTGRES_DB: postgres        
          POSTGRES_PASSWORD: postgres
          POSTGRES_USER: postgres
        ports:
          - 5432:5432
        # Set health checks to wait until postgres has started
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5

    steps:
      - name: Check out repository
        uses: actions/checkout@v2
      - name: Build my_image docker image
        uses: whoan/docker-build-with-cache-action@v5
        with:
          username: "${{secrets.aws_ecr_access_key_id}}"
          password: "${{secrets.aws_ecr_secret_access_key}}"
          registry: "${{env.registry}}"
          image_name: my_image
          context: my_image
      - name: Lint rubocop
        working-directory: ./my_image
        run: docker run $registry/my_image bundle exec rubocop
      - name: Run rails tests
        working-directory: ./my_image
        run: docker run --network="host" $registry/my_image bash -c "RAILS_ENV=test rails db:create && RAILS_ENV=test rails db:migrate && rails test"

Unsocial

env: POSTGRES_DB: postgres POSTGRES_PASSWORD: postgres POSTGRES_USER: postgres your pg passwords are exposed inside yaml, is there a better way to do this? – Mccay

@Mccay that database is run once and thrown away. The username and passwords aren't particularly useful because it's just a database that is setup in CI/CD and then thrown away. If you really wanted to extract those values you could put them in Github Environment Variables though. – Swear

Answer 2

Your problem appears to be that Postgres is not exposed on port 5432. Try to replace the port number with ${{ job.services.postgres.ports[5432] }}.

There are examples here: https://github.com/actions/example-services/blob/master/.github/workflows/postgres-service.yml

Ellington

I already have Postgres configured for that port. I've tried both setups in the examples, running locally on the VM and in a container – Whilst

Oops, sorry, I was mislead by the error message saying it couldn't connect on port 5432. It looks like you need to add the port number to the test database configuration maybe? – Lamellirostral

I ended up going running a docker image for the Rails app. I've edited my question with the working configuration. – Whilst

This link was helpful for me: github.com/actions/example-services/blob/master/.github/…. I'm set POSTGRES_HOST: postgres and POSTGRES_PORT: ${{ job.services.postgres.ports[5432] }}. That fixes the connection problem. – Moody

Answer 3

I had this challenge when trying to set up GitHub actions for a Rails Application.

Here's what worked for me:

name: Ruby

on:
  push:
    branches:
    - main
  pull_request:
    branches:
    - main

jobs:
  test:

    runs-on: ubuntu-latest
    strategy:
      matrix:
        ruby-version:
        - '2.7.2'
        node-version:
        - '12.22'
        database-name:
        - my-app
        database-password:
        - postgres
        database-user:
        - postgres
        database-host:
        - 127.0.0.1
        database-port:
        - 5432

    services:
      postgres:
        image: postgres:latest
        env:
          POSTGRES_DB: ${{ matrix.database-name }}
          POSTGRES_USER: ${{ matrix.database-user }}
          POSTGRES_PASSWORD: ${{ matrix.database-password }}
        ports:
          - 5432:5432
        # Set health checks to wait until postgres has started
        options:
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5

    steps:
    - name: Check out Git Repository
      uses: actions/checkout@v2

    - name: Set up Ruby, Bundler and Rails
      uses: ruby/setup-ruby@v1
      with:
        ruby-version: ${{ matrix.ruby-version }}
        bundler-cache: true # runs 'bundle install' and caches installed gems automatically

    - name: Set up Node
      uses: actions/setup-node@v1
      with:
        node-version: ${{ matrix.node-version }}

    - name: Install packages
      run: |
        yarn install --check-files

    - name: Setup test database
      env:
        RAILS_ENV: test
        DATABASE_NAME_TEST: ${{ matrix.database-name }}
        DATABASE_USER: ${{ matrix.database-user }}
        DATABASE_PASSWORD: ${{ matrix.database-password }}
        DATABASE_HOST: ${{ matrix.database-host }}
        DATABASE_PORT: ${{ matrix.database-port }}
        POSTGRES_DB: ${{ matrix.database-name }}
      run: |
        bundle exec rails db:migrate
        bundle exec rails db:seed

Note:

1. Replace my-app with the name of your app.
2. You can leave the database-password and database-user as postgres

That's all.

I hope this helps

Manned

You should not use the matrix to pass database data. – Emptor