Update Metabase DB credentials from Heroku (for PostgreSQL)

update-metabase-db-from-heroku.sh

#!/bin/bash
#
# Update database connections in Metabase from Heroku
#
# Tailor the following variables to your situation, set credentials in ~/.netrc (with: machine, login, password)
#
# Requirements:
# - curl
# - jq
# - heroku CLI
# - awk (usually installed by default)
# - sed (usually installed by default)
#
HEROKU_APP=my-app-name
METABASE_DB_ID=1
METABASE_DOMAIN=metabase.example.com
METABASE_URL="https://${METABASE_DOMAIN}"

netrc_get() {
  # https://stackoverflow.com/a/30031402/2866660
  awk -v "machine=${1}" -v "field=${2}" '
      $1 == "machine" {
          if (m)
              # we have already seen the requested domain but did not find the field
              exit 1
          if ($2 == machine) m=1
      }
      m && $1 == field {print $2; exit}
  ' ~/.netrc
}

METABASE_USER=`netrc_get "${METABASE_DOMAIN}" login`
METABASE_PASS=`netrc_get "${METABASE_DOMAIN}" password`

if [ ! "$METABASE_USER" -o ! "$METABASE_PASS" ]; then
  echo "Please Metabase login and password for ${METABASE_DOMAIN} in ~/.netrc" 1>&2
  exit 1
fi

mb_login() {
  curl -s -X POST \
    -H 'Content-Type: application/json' \
    -d '{"username": "'"${METABASE_USER}"'", "password": "'"${METABASE_PASS}"'"}' \
    "${METABASE_URL}/api/session"
}

heroku_dburl1() {
  # args: heroku_app
  # returns primary database URL
  heroku config:get DATABASE_URL -a "$1"
}

heroku_dburl2() {
  # args: heroku_app
  # returns non-primary database URL
  PRIMARY=`heroku_dburl1 "$1"`
  heroku config -a "$1" | sed 's/^HEROKU_POSTGRESQL_.*_URL: *\(.*\)$/\1/p;d' | grep -F "$PRIMARY" | head -n 1
}

mb_updatedb() {
  # args: token db_url metabase_db_id
  DB_URL="$2"

  if [ ! "$DB_URL" ]; then
    echo "$2: could not retrieve database URL." 1>&2
    return 1
  fi

  DB_USER=`echo "$DB_URL" | sed 's/^postgres:\/\/\([^:@/]\+\):\([^:@/]\+\)@\(.*\):\([0-9]\+\)\/\(.*\)$/\1/p;d'`
  DB_PASS=`echo "$DB_URL" | sed 's/^postgres:\/\/\([^:@/]\+\):\([^:@/]\+\)@\(.*\):\([0-9]\+\)\/\(.*\)$/\2/p;d'`
  DB_HOST=`echo "$DB_URL" | sed 's/^postgres:\/\/\([^:@/]\+\):\([^:@/]\+\)@\(.*\):\([0-9]\+\)\/\(.*\)$/\3/p;d'`
  DB_PORT=`echo "$DB_URL" | sed 's/^postgres:\/\/\([^:@/]\+\):\([^:@/]\+\)@\(.*\):\([0-9]\+\)\/\(.*\)$/\4/p;d'`
  DB_NAME=`echo "$DB_URL" | sed 's/^postgres:\/\/\([^:@/]\+\):\([^:@/]\+\)@\(.*\):\([0-9]\+\)\/\(.*\)$/\5/p;d'`

  BODY='{"engine": "postgres", "details": {"dbname":"'"${DB_NAME}"'", "host":"'"${DB_HOST}"'", "password":"'"${DB_PASS}"'", "port":'"${DB_PORT}"' ,"user":"'"${DB_USER}"'", "ssl":true, "ssl-mode":"require", "ssl-use-client-auth":false, "tunnel-enabled":false}}'

  # One could verify if the database connection actually works before updating
  # it in Metabase. Couldn't get this to work yet. One would need to parse the
  # JSON output (using jq) and see if there are any errors or not.
  #
  #    curl -s -X POST \
  #      -H "Content-Type: application/json" \
  #      -H "X-Metabase-Session: $token" \
  #      "${METABASE_URL}/api/database/validate" \
  #      -d "$BODY"

  curl -s -X PUT \
    -H "Content-Type: application/json" \
    -H "X-Metabase-Session: $token" \
    "${METABASE_URL}/api/database/$3" \
    -d "$BODY" >/dev/null

  if [ $? -ne 0 ]; then
    echo "$2: updating database details failed." 1>&2
    return 1
  fi
}

token=`mb_login | jq -r .id`

if [ ! "$token" ]; then
  echo "Could not login with Metabase." 2>&1
  exit 1
fi

mb_updatedb "$token" `heroku_dburl1 "$HEROKU_APP"` "$METABASE_DB_ID"
# If you want to use a follower database on the same app, use this form:
#   mb_updatedb "$token" `heroku_dburl2 "$HEROKU_APP"` "$METABASE_DB_ID"
# And if you have multiple databases, you can repeat the mb_updatedb line with other arguments.