Create PostgreSQL database and role based on `DATABASE_URL` environment variable

createdb.sh

#!/usr/bin/env bash

exists () {
  type "$1" >/dev/null 2>/dev/null
}

if exists "psql"; then
    PSQL=psql
else
    for v in $(seq 17 14); do
        if exists "psql-${v}"; then
	    PSQL="psql-${v}"
	    break
	fi
    done
fi

if [ -z "${PSQL}" ]; then
    echo "Missing psql command" >&2
    exit 1
fi

if [ -n "${DATABASE_URL}" ]; then
    DB_URL="${DATABASE_URL}"
else
    ENV_FILE="$1"

    if [ -z "${ENV_FILE}" ]; then
      echo "Usage: $0 envfile" >&2
      exit 1
    fi

    DB_URL=$(sed -n '/DATABASE_URL=/s/DATABASE_URL=//p' "${ENV_FILE}")
fi

if echo | ${PSQL} "${DB_URL}" > /dev/null 2>&1; then
    echo "Database already exists. Skipping."
    exit 0
fi

USERNAME=$(echo "${DB_URL}" | cut -d: -f2 | sed 's,//,,' | tr '[:upper:]' '[:lower:]')
PASSWORD=$(echo "${DB_URL}" | cut -d: -f3 | sed 's,@.*,,')
HOSTNAME=$(echo "${DB_URL}" | cut -d@ -f2 | sed -E 's,[/:].*,,')
PORT=$(echo "${DB_URL}" | cut -d@ -f2 | sed -E 's,.*:([0-9]+)/.*$,\1,')
DATABASE=$(echo "${DB_URL}" | sed 's,.*/,,' | tr '[:upper:]' '[:lower:]')

psql="${PSQL} -h ${HOSTNAME} -p ${PORT} -d postgres"

db_exists=$($psql -tAc "SELECT 1 FROM pg_database WHERE datname='${DATABASE}'")
if [ -z "${db_exists}" ]; then
    $psql -c "CREATE DATABASE ${DATABASE} ENCODING='UTF8'"
fi

psql="${PSQL} -h ${HOSTNAME} -p ${PORT} -d ${DATABASE}"

user_exists=$($psql -tAc "SELECT 1 FROM pg_roles WHERE rolname='${USERNAME}'")
if [ -z "${user_exists}" ]; then
    $psql -c "CREATE ROLE ${USERNAME} WITH LOGIN ENCRYPTED PASSWORD '${PASSWORD}' CREATEDB"
fi

$psql -tAc "GRANT ALL PRIVILEGES ON DATABASE ${DATABASE} TO ${USERNAME}"
$psql -tAc "GRANT ALL PRIVILEGES ON SCHEMA public TO ${USERNAME}"