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:

1
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:

1
% heroku authorizations:create --scope=read-protected
2
Creating OAuth Authorization... done
3
Client:      <none>
4
ID:          4dd42e6c-5c89-4389-a9b1-4a5388b88517
5
Description: Long-lived user authorization
6
Scope:       read-protected
7
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:

1
    - name: Import Heroku DB into SQLite
2
      env:
3
        HEROKU_API_KEY: ${{ secrets.HEROKU_API_KEY }}
4
      run: |-
5
        db-to-sqlite $(heroku config:get DATABASE_URL -a simonwillisonblog) simonwillisonblog.db \
6
          --table auth_permission \
7
          --table auth_user \
8
          --table blog_blogmark \
9
          --table blog_blogmark_tags \
10
          --table blog_entry \
11
          --table blog_entry_tags \
12
          --table blog_quotation \
13
          --table blog_quotation_tags \
14
          --table blog_tag \
15
          --table django_content_type \
16
          --table redirects_redirect