connect to an aiven postgres database with ease

avn-pg

#!/bin/bash
set -euo pipefail
######################################################
## REQUIREMENTS
## * proper configured and running avn cli: (https://github.com/aiven/aiven-client)
## * installed fzf (https://github.com/junegunn/fzf)
## * installed jq (https://github.com/stedolan/jq)
## * docker
######################################################
## CHANGE BELOW IF NECESSARY
AVN=avn
FZF=fzf
JQ=jq
# default PGCLIENT to use, if the environment variable PGCLIENT is set, avn-pg will use its contents
# to connect to the database
# example: $ PGCLIENT="psql \$PGDATABASE" avn-pg
DEFAULT_PGCLIENT="docker run --rm -ti --net host --env PGDATABASE --name pgcli$$ dbcliorg/pgcli:latest"
######################################################


if [ -z "${PGCLIENT+x}" ]; then
	PGCLIENT=$DEFAULT_PGCLIENT	
fi

if [ "$#" -lt 1 ]; then
	echo "Fetching projects..."
	PROJECT=$( \
		$AVN project list --json | \
		$JQ -r '.[] | .project_name' | \
		$FZF --prompt "project> "
	)
else
	PROJECT=$1
fi

if [ -z "${PROJECT+x}" ]; then
	exit 1
fi


echo "Fetching services for $PROJECT..."
DATA=$($AVN service list --project=$PROJECT --json)

if [ "$#" -lt 2 ]; then
	SERVICE=$( \
		echo -ne "$DATA" | \
		$JQ -r '.[] | select(.state == "RUNNING") | select(.service_type == "pg") | select(.connection_info.pg | length > 0) | .service_name' | \
		$FZF --prompt "service> "
	)
else
	SERVICE=$2
fi

if [ -z "${SERVICE+x}" ]; then
	exit 1
fi

RAW_CONNECTIONS=$(echo -ne "$DATA" | $JQ -r \
	".[] | select(.service_name == \"$SERVICE\") | .connection_info | (.pg | range(0;length) as \$i | {(\"master\" + (\$i | tostring)):.[\$i]}),{\"replica0\":.pg_replica_uri}" | \
	$JQ -s "add"
)


if [ -z "${RAW_CONNECTIONS+x}" ] || [ "$RAW_CONNECTIONS" == "null" ]; then
	echo "unable to get connections for $SERVICE"
	exit 1
fi

CONNECTIONS=$(echo -ne "$RAW_CONNECTIONS" | $JQ -r 'keys | .[]')

if [ -z "${CONNECTIONS+x}" ] || [ "$CONNECTIONS" == "null" ]; then
	echo "unable to get connections for $SERVICE"
	exit 1
fi

if [ "$#" -lt 3 ]; then
	CONNECTION=$(echo "$CONNECTIONS" | $FZF --prompt "database-server> ")
else
	CONNECTION=$3
fi

if [ -z "${CONNECTION+x}" ]; then
	exit 1
fi

if [ "$#" -lt 4 ]; then
	DATABASE=$(echo -ne "$DATA" | $JQ -r \
	".[] | select(.service_name == \"$SERVICE\") | .databases[]" |
		$FZF --prompt "database> "
	)
else
	DATABASE=$4
fi

if [ -z "${DATABASE+x}" ]; then
	exit 1
fi

PGDATABASE=$(echo -ne "$RAW_CONNECTIONS" | $JQ -r ".$CONNECTION")

export PGDATABASE=$(echo -ne "$PGDATABASE" | $JQ -R -r "sub(\"(?<conn>postgres://.*/)(?<db>[_\\\-a-zA-Z0-9]+)(?<params>\\\??.+)\"; \"\(.conn)$DATABASE\(.params)\")")

echo "Connecting to $PROJECT $SERVICE $CONNECTION $DATABASE"
eval $PGCLIENT