Announcing the PlanetScale GitHub Actions

By Brian Morrison II | March 31, 2023

With our database branching and deploy request workflow, PlanetScale was built with DevOps pipelines in mind. However, anyone who's worked in DevOps long enough knows it is a very "choose your own adventure" practice. While guidelines exist, many companies build their pipelines very differently according to the needs of their products. Integrating PlanetScale into this flow is no exception. Today, we're lowering the barrier to entry by publishing the first wave of official PlanetScale GitHub Actions for you to use in your projects.

What are GitHub Actions?

Before we cover the available Actions, it's worth taking a moment to understand what GitHub Actions is. GitHub Actions allows you to automate processes directly within your repository by defining jobs in YAML that will perform operations based on the triggers you define. These YAML files are referred to as "workflows," whereas the individual operations within are called "steps." Developers are free to write their own steps using Bash or PowerShell manually, or they can search the GitHub Actions Marketplace for a pre-defined step that performs the operations they need without having to build the functionality themselves.

The `setup-pscale-action` GitHub Action

The planetscale/setup-pscale-action allows you to make pscale, the PlanetScale CLI tool, available within your GitHub Actions workflows.

Once installed, you are able to automate workflows via pscale on Linux, Windows, or Mac runners.

Such as:

Creating database branches
Creating branch passwords and connecting to your database
Opening deploy requests
Auto commenting schema diffs on pull requests
and more...

Let's look at some examples.

Create a new database branch when a GitHub branch is created

If your team opens a new database branch whenever a feature branch is created in your GitHub repository, you can use pscale to create a branch and a password. The branch credentials can then be used as environment variables in a preview or staging environment.

- name: Create branch
  env:
    PLANETSCALE_SERVICE_TOKEN_ID: ${{ secrets.PLANETSCALE_SERVICE_TOKEN_ID }}
    PLANETSCALE_SERVICE_TOKEN: ${{ secrets.PLANETSCALE_SERVICE_TOKEN }}
  run: |
    set +e
    pscale branch show ${{ secrets.PLANETSCALE_DATABASE_NAME }} ${{ env.PSCALE_BRANCH_NAME }}
    exit_code=$?
    set -e

    if [ $exit_code -eq 0 ]; then
      echo "Branch exists. Skipping branch creation."
    else
      echo "Branch does not exist. Creating."
      pscale branch create ${{ secrets.PLANETSCALE_DATABASE_NAME }} ${{ env.PSCALE_BRANCH_NAME }} --wait
    fi

Notice that we first check if the branch exists. If it does, we do nothing. Otherwise, we create it and pass the --wait flag.

This is useful when running in CI, as the workflow may run multiple times and you'll want the branch ready if you are running schema migrations immediately after creating the branch.

Create a password for the branch

You can use pscale password create to generate credentials for your database branch.

- name: Generate password for branch
  env:
    PLANETSCALE_SERVICE_TOKEN_ID: ${{ secrets.PLANETSCALE_SERVICE_TOKEN_ID }}
    PLANETSCALE_SERVICE_TOKEN: ${{ secrets.PLANETSCALE_SERVICE_TOKEN }}
  run: |
    response=$(pscale password create ${{ secrets.PLANETSCALE_DATABASE_NAME }} ${{ env.PSCALE_BRANCH_NAME }} -f json)

    id=$(echo "$response" | jq -r '.id')
    host=$(echo "$response" | jq -r '.access_host_url')
    username=$(echo "$response" | jq -r '.username')
    password=$(echo "$response" | jq -r '.plain_text')
    ssl_mode="verify_identity"  # Assuming a default value for ssl_mode
    ssl_ca="/etc/ssl/certs/ca-certificates.crt"  # Assuming a default value for ssl_ca

    # Set the password ID, allows us to later delete it if wanted.
    echo "PASSWORD_ID=$id" >> $GITHUB_ENV

    # Create the DATABASE_URL
    database_url="mysql://$username:$password@$host/${{ secrets.PLANETSCALE_DATABASE_NAME }}?sslmode=$ssl_mode&sslca=$ssl_ca"
    echo "DATABASE_URL=$database_url" >> $GITHUB_ENV
    echo "::add-mask::$DATABASE_URL"
- name: Use the DATABASE_URL in a subsequent step
  run: |
    echo "Using DATABASE_URL: $DATABASE_URL"

This example shows creating the password and getting back a response in JSON. The JSON is then parsed to create a DATABASE_URL which can be used in later steps, such as using the branch as the database for a preview environment or to connect and run migrations that were included in the GitHub pull request.

Open a deploy request

You can use pscale deploy-request create to open a new deploy request from GitHub Actions. This can be useful after running migrations against a branch.

- name: Open DR if migrations
  env:
    PLANETSCALE_SERVICE_TOKEN_ID: ${{ secrets.PLANETSCALE_SERVICE_TOKEN_ID }}
    PLANETSCALE_SERVICE_TOKEN: ${{ secrets.PLANETSCALE_SERVICE_TOKEN }}
  run: pscale deploy-request create ${{ secrets.PLANETSCALE_DATABASE_NAME }} ${{ env.PSCALE_BRANCH_NAME }}

Get deploy request diff and comment on pull request

We can use pscale deploy-request diff to see the full schema diff of a deploy request.

This example is useful when combined with opening a deploy request for a git branch. You can then automatically comment the diff back to the GitHub pull request.

- name: Comment on PR
  env:
    PLANETSCALE_SERVICE_TOKEN_ID: ${{ secrets.PLANETSCALE_SERVICE_TOKEN_ID }}
    PLANETSCALE_SERVICE_TOKEN: ${{ secrets.PLANETSCALE_SERVICE_TOKEN }}
  run: |
    echo "Deploy request opened: https://app.planetscale.com/${{ secrets.PLANETSCALE_ORG_NAME }}/${{ secrets.PLANETSCALE_DATABASE_NAME }}/deploy-requests/${{ env.DEPLOY_REQUEST_NUMBER }}" >> migration-message.txt
    echo "" >> migration-message.txt
    echo "\`\`\`diff" >> migration-message.txt
    pscale deploy-request diff ${{ secrets.PLANETSCALE_DATABASE_NAME }} ${{ env.DEPLOY_REQUEST_NUMBER }}  -f json | jq -r '.[].raw' >> migration-message.txt
    echo "\`\`\`" >> migration-message.txt
- name: Comment PR - db migrated
  uses: thollander/actions-comment-pull-request@v2
  with:
    filePath: migration-message.txt

This writes the diff to the migration-message.txt file and then creates a comment on the pull request that triggered the workflow.

How to use the PlanetScale GitHub Actions

To get started using PlanetScale + GitHub Actions, see our full guide and complete page of examples here.