PlanetScale GitHub Actions

See our tech talk on Databases + CI/CD to see pscale + GitHub Actions used in a real application. With GitHub Actions, you can automate the creation of branches and deploy requests all within your CI workflow.

Getting Started

Authentication

Convert GitHub branch name to PlanetScale branch name

Create a PlanetScale branch

Recreate the branch when the pull request changes

Create a password for a branch

Open a deploy request

Get deploy request by branch name

Get deploy request diff and comment on pull request

Check for dropped columns

Submit a deploy request by branch name

Getting started

The best way to use PlanetScale within GitHub Actions is via the pscale CLI. Use planetscale/setup-pscale-action to make pscale available within your GitHub Actions.

- name: Setup pscale
  uses: planetscale/setup-pscale-action@v1

The action works with Linux, Windows, and Mac runners. Once installed it will be added to your tool cache for subsequent runs.

Authentication

Authentication for pscale is via service token environment variables. You will need to create a service token. Make sure to give your service token the proper permissions to the database you’ll be using in your workflow. Add your PLANETSCALE_SERVICE_TOKEN_ID and PLANETSCALE_SERVICE_TOKEN to your Actions secrets. In your Actions workflow, you will need to make the secrets available as environment variables.

- name: Run pscale command
  env:
    PLANETSCALE_SERVICE_TOKEN_ID: ${{ secrets.PLANETSCALE_SERVICE_TOKEN_ID }}
    PLANETSCALE_SERVICE_TOKEN: ${{ secrets.PLANETSCALE_SERVICE_TOKEN }}
  run: pscale database list

Examples

The following examples show how to accomplish common Actions workflows with PlanetScale. In each example, notice that we use secrets for the service token, database and organization names. You will need to set them in your GitHub repository to target your own database.

PLANETSCALE_ORG_NAME
PLANETSCALE_DATABASE_NAME
PLANETSCALE_SERVICE_TOKEN_ID
PLANETSCALE_SERVICE_TOKEN

Convert GitHub branch name to PlanetScale branch name

PlanetScale branch names must be lowercase, alphanumeric characters and hyphens are allowed. Since git branch names allow more possibilities, you can use the following code to transform a git branch name into an acceptable PlanetScale branch name.

- name: Rename branch name
  run: |
    branch_name=$(printf '%s' "$HEAD_REF" | tr -cd '[:alnum:]-' | tr '[:upper:]' '[:lower:]')
    echo "PSCALE_BRANCH_NAME=$branch_name" >> "$GITHUB_ENV"
  env:
    HEAD_REF: ${{ github.head_ref }}

This makes ${{ env.PSCALE_BRANCH_NAME }} available for use in the rest of the workflow. This is useful to run in any scenario where you are creating a PlanetScale branch to correspond with a git branch.

Create a PlanetScale branch

You can use pscale branch create to create a branch that matches your GitHub branch name.

- 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 }} --org ${{ secrets.PLANETSCALE_ORG_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 --org ${{ secrets.PLANETSCALE_ORG_NAME }}
    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.

Recreate the branch when the pull request changes

Skipping creation when the branch exists reuses whatever schema that branch already has. If a migration in the pull request is edited after the first workflow run, the branch and any open deploy request go stale and no longer match the code under review. Deleting and recreating the branch on every run guarantees it reflects the current pull request. Before deleting, close any open deploy requests for the branch and leave a comment on them pointing back to the pull request.

- name: Recreate 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 }} --org ${{ secrets.PLANETSCALE_ORG_NAME }}
    exists=$?
    set -e

    if [ $exists -eq 0 ]; then
      echo "Branch exists. Closing any open deploy requests and recreating."
      pr_url="https://github.com/${{ github.repository }}/pull/${{ github.event.pull_request.number }}"
      stale_numbers=$(pscale deploy-request list ${{ secrets.PLANETSCALE_DATABASE_NAME }} --org ${{ secrets.PLANETSCALE_ORG_NAME }} -f json \
        | jq -r --arg branch "${{ env.PSCALE_BRANCH_NAME }}" '.[] | select(.state == "open" and .branch == $branch) | .number')

      for number in $stale_numbers; do
        echo "Commenting on and closing deploy request $number"
        jq -n --arg body "This deploy request is stale. The schema in ${pr_url} changed, so this DR is being closed and a new one will be opened." '{body: $body}' \
          | pscale api organizations/${{ secrets.PLANETSCALE_ORG_NAME }}/databases/${{ secrets.PLANETSCALE_DATABASE_NAME }}/deploy-requests/"$number"/comments --org ${{ secrets.PLANETSCALE_ORG_NAME }} --input - \
          || echo "Warning: could not comment on deploy request $number"
        pscale deploy-request close ${{ secrets.PLANETSCALE_DATABASE_NAME }} "$number" --org ${{ secrets.PLANETSCALE_ORG_NAME }}
      done

      if [ -n "$stale_numbers" ]; then
        echo "STALE_DR_NUMBERS=$(echo $stale_numbers | tr '\n' ' ')" >> "$GITHUB_ENV"
      fi

      pscale branch delete ${{ secrets.PLANETSCALE_DATABASE_NAME }} ${{ env.PSCALE_BRANCH_NAME }} --org ${{ secrets.PLANETSCALE_ORG_NAME }} --force
    fi
- name: Create branch
  env:
    PLANETSCALE_SERVICE_TOKEN_ID: ${{ secrets.PLANETSCALE_SERVICE_TOKEN_ID }}
    PLANETSCALE_SERVICE_TOKEN: ${{ secrets.PLANETSCALE_SERVICE_TOKEN }}
  run: pscale branch create ${{ secrets.PLANETSCALE_DATABASE_NAME }} ${{ env.PSCALE_BRANCH_NAME }} --org ${{ secrets.PLANETSCALE_ORG_NAME }} --wait

The numbers of any closed deploy requests are saved to ${{ env.STALE_DR_NUMBERS }}, so a later step can mention which deploy request was replaced when commenting on the pull request. Pair this with a concurrency group keyed on the git branch so two pushes in quick succession don’t delete and create the same PlanetScale branch at the same time.

concurrency:
  group: ps-migrations-${{ github.head_ref }}
  cancel-in-progress: true

Create a password for a 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 --org ${{ secrets.PLANETSCALE_ORG_NAME }})

    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.

pscale password create can also accept a ttl flag which lets you limit the number of minutes the password is valid for.

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 }} \
      --org ${{ secrets.PLANETSCALE_ORG_NAME }} \
      --auto-delete-branch \
      --notes "https://github.com/${{ github.repository }}/pull/${{ github.event.pull_request.number }}"

--auto-delete-branch deletes the PlanetScale branch once the deploy request completes, so CI branches don’t accumulate. --notes links the deploy request back to the pull request that created it.

Get deploy request by branch name

You can use pscale deploy-requests show to grab the latest deploy request by branch name. This can be useful when deploying your application. You can first check if there are any deploy requests open for the branch being deployed. If there are, you can trigger the deploy request to run before you deploy your application.

- name: Get Deploy Requests
  env:
    PLANETSCALE_SERVICE_TOKEN_ID: ${{ secrets.PLANETSCALE_SERVICE_TOKEN_ID }}
    PLANETSCALE_SERVICE_TOKEN: ${{ secrets.PLANETSCALE_SERVICE_TOKEN }}
  run: |
    deploy_request_number=$(pscale deploy-request show ${{ secrets.PLANETSCALE_DATABASE_NAME }} ${{ env.PSCALE_BRANCH_NAME }} --org ${{ secrets.PLANETSCALE_ORG_NAME }} -f json | jq -r '.number')
    echo "DEPLOY_REQUEST_NUMBER=$deploy_request_number" >> $GITHUB_ENV

This example also makes the deploy request number available as an env var so that it can be used in later steps.

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 }} --org ${{ secrets.PLANETSCALE_ORG_NAME }} -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.

Check for dropped columns

PlanetScale sets a can_drop_data boolean for any schema change that drop a column or table. We can make use of this to emit a warning into our pull requests. In this example, we first wait for the deployment check to be ready. During this time, PlanetScale is examining the schema change, verifying that it is safe and generating the DDL statements to make the change. Once it’s done, we then use this information to put a comment on the deploy request with tips on how to deploy it safely.

- name: Check deployment state
    id: check-state
    env:
      PLANETSCALE_SERVICE_TOKEN_ID: ${{ secrets.PLANETSCALE_SERVICE_TOKEN_ID }}
      PLANETSCALE_SERVICE_TOKEN: ${{ secrets.PLANETSCALE_SERVICE_TOKEN }}
    run: |
      for i in {1..10}; do
        deployment_state=$(pscale deploy-request show ${{ secrets.PLANETSCALE_ORG_NAME }} ${{ env.PSCALE_BRANCH_NAME }} --org ${{ secrets.PLANETSCALE_ORG_NAME }} --format json | jq -r '.deployment_state')
        echo "Deployment State: $deployment_state"

        if [ "$deployment_state" = "ready" ]; then
          echo "Deployment state is ready. Continuing."
          break
        fi

        echo "Deployment state is not ready. Waiting 2 seconds before checking again."
        sleep 2
      done
  - name: Comment PR - db migrated
    if: ${{ env.DR_OPENED }}
    env:
      PLANETSCALE_SERVICE_TOKEN_ID: ${{ secrets.PLANETSCALE_SERVICE_TOKEN_ID }}
      PLANETSCALE_SERVICE_TOKEN: ${{ secrets.PLANETSCALE_SERVICE_TOKEN }}
    run: |
      deploy_data=$(pscale api organizations/${{ secrets.PLANETSCALE_ORG_NAME }}/databases/${{ secrets.PLANETSCALE_DATABASE_NAME }}/deploy-requests/${{ env.DEPLOY_REQUEST_NUMBER }}/deployment --org planetscale)
      can_drop_data=$(echo "$deploy_data" | jq -r '.deploy_operations[] | select(.can_drop_data == true) | .can_drop_data')

      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

      if [ "$can_drop_data" = "true" ]; then
        echo ":rotating_light: You are dropping a column. Before running the migration make sure to do the following:" >> migration-message.txt
        echo "" >> migration-message.txt

        echo "1. [ ] Deploy app changes to ensure the column is no longer being used." >> migration-message.txt
        echo "2. [ ] Once you've verified it's no used, run the deploy request." >> migration-message.txt
        echo "" >> migration-message.txt
      else
        echo "When adding to the schema, the Deploy Request must be run **before** the code is deployed." >> migration-message.txt
        echo "Please ensure your schema changes are compatible with the application code currently running in production." >> migration-message.txt
        echo "" >> migration-message.txt

        echo "1. [ ] Successfully run the Deploy Request" >> migration-message.txt
        echo "2. [ ] Deploy this PR" >> migration-message.txt
        echo "" >> migration-message.txt
      fi

      echo "\`\`\`diff" >> migration-message.txt
      pscale deploy-request diff ${{ secrets.PLANETSCALE_ORG_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
    if: ${{ env.DR_OPENED }}
    with:
      filePath: migration-message.txt

Submit a deploy request by branch name

To trigger a deploy, we can use pscale deploy-request deploy. This command will accept either the deploy request number, or the name of the branch that the deploy request was created from. When using with GitHub Actions, it’s often easier to use the branch name. The --wait flag will let the command run until the deployment is complete. This is important if you want your schema change to run before the next step in your workflow.

- name: Deploy schema migrations
  env:
    PLANETSCALE_SERVICE_TOKEN_ID: ${{ secrets.PLANETSCALE_SERVICE_TOKEN_ID }}
    PLANETSCALE_SERVICE_TOKEN: ${{ secrets.PLANETSCALE_SERVICE_TOKEN }}
  run: |
    pscale deploy-request deploy ${{ secrets.PLANETSCALE_DATABASE_NAME }} ${{ env.PSCALE_BRANCH_NAME }} --org ${{ secrets.PLANETSCALE_ORG_NAME }} --wait

Need help?

Get help from the PlanetScale Support team, or join our Discord community to see how others are using PlanetScale.

Pricing

Platform

Integrations

Guides

PlanetScale GitHub Actions

Getting Started

Authentication

Convert GitHub branch name to PlanetScale branch name

Create a PlanetScale branch

Recreate the branch when the pull request changes

Create a password for a branch

Open a deploy request

Get deploy request by branch name

Get deploy request diff and comment on pull request

Check for dropped columns

Submit a deploy request by branch name

Getting started

Authentication

Examples

Convert GitHub branch name to PlanetScale branch name

Create a PlanetScale branch

Recreate the branch when the pull request changes

Create a password for a branch

Open a deploy request

Get deploy request by branch name

Get deploy request diff and comment on pull request

Check for dropped columns

Submit a deploy request by branch name

Need help?

Getting Started

Authentication

Convert GitHub branch name to PlanetScale branch name

Create a PlanetScale branch

Recreate the branch when the pull request changes

Create a password for a branch

Open a deploy request

Get deploy request by branch name

Get deploy request diff and comment on pull request

Check for dropped columns

Submit a deploy request by branch name

​Getting started

​Authentication

​Examples

​Convert GitHub branch name to PlanetScale branch name

​Create a PlanetScale branch

​Recreate the branch when the pull request changes

​Create a password for a branch

​Open a deploy request

​Get deploy request by branch name

​Get deploy request diff and comment on pull request

​Check for dropped columns

​Submit a deploy request by branch name

​Need help?

Getting started

Authentication

Examples

Convert GitHub branch name to PlanetScale branch name

Create a PlanetScale branch

Recreate the branch when the pull request changes

Create a password for a branch

Open a deploy request

Get deploy request by branch name

Get deploy request diff and comment on pull request

Check for dropped columns

Submit a deploy request by branch name

Need help?