wvengen · January 23, 2023 11:20
diff --git a/update-metabase-db-from-heroku.sh b/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.
	#!/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.