Programatically accessing Heroku PostgreSQL from GitHub Actions

My db-to-sqlite tool can connect to a PostgreSQL database, export all of the content and write it to a SQLite database file on disk.

I wanted to use it in a GitHub Action - but that meant I needed code running in the action workflow to be able to access my Heroku PostgreSQL database directly.

It turns out the DATABASE_URL environment variable in Heroku has everything you need to connect to that database from elsewhere.

If you have the heroku CLU tool installed and authenticated the following one-liner does the job:

db-to-sqlite $(heroku config:get DATABASE_URL -a simonwillisonblog) simonwillisonblog.db

To configure heroku in a GitHub Action you need to set a HEROKU_API_KEY environment variable.

You can create an OAuth token on your laptop like this:

% heroku authorizations:create --scope=read-protected 
Creating OAuth Authorization... done
Client:      <none>
ID:          4dd42e6c-5c89-4389-a9b1-4a5388b88517
Description: Long-lived user authorization
Scope:       read-protected
Token:       xxx copy and paste this bit xxx

Then you can paste the token into a GitHub repository secret called HEROKU_API_KEY.

Here's a fragment of my action workflow that creates the SQLite database pulling data from Heroku:

    - name: Import Heroku DB into SQLite
      env:
        HEROKU_API_KEY: ${{ secrets.HEROKU_API_KEY }}
      run: |-
        db-to-sqlite $(heroku config:get DATABASE_URL -a simonwillisonblog) simonwillisonblog.db \
          --table auth_permission \
          --table auth_user \
          --table blog_blogmark \
          --table blog_blogmark_tags \
          --table blog_entry \
          --table blog_entry_tags \
          --table blog_quotation \
          --table blog_quotation_tags \
          --table blog_tag \
          --table django_content_type \
          --table redirects_redirect

Related

• github-actions Running tests against PostgreSQL in a service container - 2021-02-23
• heroku Using heroku pg:pull to restore a backup to a macOS laptop - 2020-07-10
• github-actions Installing different PostgreSQL server versions in GitHub Actions - 2021-07-05
• github-actions Conditionally running a second job in a GitHub Actions workflow - 2022-07-11
• github-actions Storing files in an S3 bucket between GitHub Actions runs - 2021-12-07
• heroku Upgrading a Heroku PostgreSQL database with pg:copy - 2020-07-20
• github-actions Talking to a PostgreSQL service container from inside a Docker container - 2020-09-18
• sqlite Copy tables between SQLite databases - 2023-04-03
• github-actions Deploying a live Datasette demo when the tests pass - 2022-03-27
• fly Configuring Django SQL Dashboard for Fly PostgreSQL - 2023-08-21

Created 2020-08-18T15:46:33-07:00 · Edit