Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Update Metabase DB credentials from Heroku (for PostgreSQL)
#!/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.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment