Use Github Actions on Pull Requests to Automate Postgres on Crunchy Bridge

Automating pull requests to deploy staging applications is a game changer for large teams performing shipping quality products. Using Crunchy Bridge’s CLI or API, you can easily automate the entire process for these staging deployments. The simplest workflow would look something like the following:

In this example, during the “Create Postgres Cluster”, we’ll create a hobby-0 cluster for Postgres. Then, when the PR is closed the cluster will be torn down. We keep it simple for this example, but depending on your use case you can expand the capabilities.

For teams that like to have an anonymized dataset for staging, they use the Crunchy Bridge CLI to fork the production cluster, then run an anonymization process on the forked cluster. For teams that are running PRs often, they could have an anonymized cluster available to be forked as well. You can also create an empty database and add an automated process to load a seed file.

Naming convention

Since naming is among the two hardest things in computer science, let’s tackle it early. I like to keep my cloud tidy, so I give my Postgres clusters predictable names. Names can be added to the cluster at the time of creation. I like to name the automation clusters with something like:

{github repository name}-merge-{pull request id}

If you have a repository with name Wayne-Enterprises/Batmans_Code and a PR with id 7, it will end up with a cluster like this:

batmans-code-merge-7

Later, you’ll see this in the code we use to generate the name. Of course, you can change this to whatever you like.

Prepping your Crunchy Bridge account

Next, log into your Crunchy Bridge account, and go to My Account → API Keys. Add an API key for this account:

Since I like to be tidy, I also use production teams and developer teams on my cloud services. So, I create a developer team:

After creating this team, grab the team’s id from the URL, as you’ll need it in a moment. In the URL, you’ll see something that looks like https://crunchybridge.com/teams/gqa4owetwbdfvfacpdxhgf2qmu/dashboard. From it, grab the string that is between “teams/” and “/dashboard”. For the above URL, it would be gqa4owetwbdfvfacpdxhgf2qmu — but yours will be something different. This is your team’s id that we will use in a GitHub Secret in a moment.

Adding GitHub Actions Secrets

GitHub secrets allow you to add sensitive values to your GitHub Actions without revealing them to the world. When the GitHub Action runs, you can use the syntax ${{ secrets.CRUNCHY_BRIDGE_API_KEY }} to request the sensitive value at that time. Note: it’s important that your sections never print the secrets, else it can be viewed by anyone with access to the repository.

To add GitHub Secret, go to the repository, then click Settings → Secrets and Variables → Actions. From there, add the secrets for CRUNCHY_BRIDGE_API_KEY and CRUNCHY_BRIDGE_STAGING_TEAM_ID that were created in the previous section.

Once complete, you should see the following:

Adding the workflow file

Now, add a file to your repository at .github/workflows/crunchy-bridge-review-cluster.yml:

name: Crunchy Bridge Review Cluster

on:
  pull_request:
    types: [opened, reopened, closed]

permissions:
  contents: read

jobs:
  launch:
    if: ${{ github.event.pull_request.state == 'open' }}
    runs-on: ubuntu-latest
    name: Launch Crunchy Bridge Review Cluster

    steps:
      - name: Create Crunchy Bridge Review Cluster
        run: |
          export CRUNCHY_BRIDGE_CLUSTER_NAME=$(echo "$GITHUB_REPOSITORY-$GITHUB_REF_NAME" | sed 's/^[^\/]\+\///' | sed 's/[^0-9A-z\-]\+/-/g' )
          export CB_API_KEY=${{ secrets.CRUNCHY_BRIDGE_API_KEY }}

          wget https://github.com/CrunchyData/bridge-cli/releases/download/v3.4.0/cb-v3.4.0_linux_amd64.zip
          unzip cb-v3.4.0_linux_amd64.zip

          (./cb list | grep $CRUNCHY_BRIDGE_CLUSTER_NAME) || ./cb create --platform aws --region us-east-1 --plan hobby-0 --team ${{ secrets.CRUNCHY_BRIDGE_STAGING_TEAM_ID }} --storage 10 --name $CRUNCHY_BRIDGE_CLUSTER_NAME --version 16

          ./cb uri $CRUNCHY_BRIDGE_CLUSTER_NAME

          for i in $(seq 1 120)
          do
            (./cb info $CRUNCHY_BRIDGE_CLUSTER_NAME | grep 'state: ready') && exit 0
            echo -n '.'
            sleep 5
          done

          exit 1
  teardown:
    if: ${{ github.event.pull_request.state == 'closed' }}
    runs-on: ubuntu-latest
    name: Delete Crunchy Bridge Review Cluster

    steps:
      - name: Delete Crunchy Bridge Test Cluster
        run: |
          export CRUNCHY_BRIDGE_CLUSTER_NAME=$(echo "$GITHUB_REPOSITORY-$GITHUB_REF_NAME" | sed 's/^[^\/]\+\///' | sed 's/[^0-9A-z\-]\+/-/g' )
          export CB_API_KEY=${{ secrets.CRUNCHY_BRIDGE_API_KEY }}

          wget https://github.com/CrunchyData/bridge-cli/releases/download/v3.4.0/cb-v3.4.0_linux_amd64.zip
          unzip cb-v3.4.0_linux_amd64.zip

          (./cb list | grep $CRUNCHY_BRIDGE_CLUSTER_NAME) && ./cb destroy $CRUNCHY_BRIDGE_CLUSTER_NAME --confirm || exit 0

To trigger the workflow, commit the file, push to GitHub, and create a pull request. The file is fairly simple. It sets environmental variables for the name of the cluster to be created and the Crunchy Bridge API key. Then, it downloads the Crunchy Bridge CLI called cb. Then, it either creates the cluster or deletes the cluster depending on the state of the pull request.

Sending the connection URI to the application

To get the Postgres URI , do the following and it will return the full URI string for the cluster:

export CB_API_KEY=${{ secrets.CRUNCHY_BRIDGE_API_KEY }}
wget https://github.com/CrunchyData/bridge-cli/releases/download/v3.4.0/cb-v3.4.0_linux_amd64.zip
unzip cb-v3.4.0_linux_amd64.zip
./cb uri $CRUNCHY_BRIDGE_CLUSTER_NAME

If you were deploying to Heroku, the final line would look like:

heroku config:set DATABASE_URL=$(./cb uri $CRUNCHY_BRIDGE_CLUSTER_NAME)

If you were deploying with to your own stack, you could write to the database/config.yml to a file:

cat << EOF | tee config/database.yml
default: &default
  adapter: postgresql
  encoding: unicode
  pool: 5

staging:
  url: $(./cb uri $CRUNCHY_BRIDGE_CLUSTER_NAME)
EOF

Generally, use the $(./cb uri $CRUNCHY_BRIDGE_CLUSTER_NAME) command to retrieve the value to write to your environments connection of choice.

CLI v. API

In this tutorial, we chose to keep it simple with the CLI. Previously, when configured for another Ruby on Rails application, I had written this interaction with a Rake file. In that scenario, I had built a process highly specific to that application and Ruby on Rails. The CLI allowed me to use the tools at hand, and I did not have to mess with language libraries. Crunchy Bridge has an amazingly powerful API. If you need custom functionality and would like to orchestrate it with your language of choice, check out the API.

Protecting production

When automating review applications, I’m always watching to make sure I’m automating the correct database. For that reason, Crunchy Bridge has “Cluster Protection” to keep your production clusters from being a victim of an automation failure. To turn on, go to your Cluster, then go to Settings → General → Danger Zone → Cluster Protection.

Quick summary

• Automate your database creation with your GitHub actions and review apps! Seriously, they're amazing.
• Name your provisions predictably and save your API key in GitHub secrets.
• Create a custom workflow file that runs upon pull request to have a github action create your new database and wait until its ready. Also within that workflow file specify that upon removal of the pull request, the database will automatically be removed.
• Send the connection string to your review application and start testing.