Create, init and run a postgres database for your development environment

dev_db.sh

#!/bin/bash

# Copyright 2021 Michael Hazell
#
# Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:
#
# The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.
#
# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

########################
# Config
########################

dbUser="client"
dbPass="password"
dbName="database-name"
folderBase="./data"
# schemaName="public"  -- not needed for public

# Brew Configure which postgres should be used
export PATH="/opt/homebrew/opt/postgresql@11/bin:$PATH"



########################
# Parse options
########################

POSITIONAL=()
while [[ $# -gt 0 ]]; do
  key="$1"

  case $key in
    -r|--run)
      run="y"
      shift # past argument
      ;;
    -h|--help)
      showhelp="y"
      shift # past argument
      ;;
    -s|--show)
      showDatabases="y"
      shift
      ;;
    -l|--latest)
      latest="y"
      shift
      ;;
    -c|--create)
      create="y"
      shift
      ;;
    --delete)
      delete="y"
      shift # past argument
      ;;
    --restore)
      restore="y"
      shift
      ;;
    *)    # unknown option
      POSITIONAL+=("$1") # save it in an array for later
      shift # past argument
      ;;
  esac
done

set -- "${POSITIONAL[@]}" # restore positional parameters

########################
# Database path
########################

# Figure out which file we're working with here...
today=$(date "+%Y%m%d")
latestDBName=$(ls -1 $folderBase | tail -1)
newDBName="$folderBase/$today"
if [ ! -z "$1" ]; then
  # FIXME: this can get weird if we are also restoring ...
  newDBName="$folderBase/$today-$1"
fi

# Are we creating a new database?
# Are we running the latest DB?
# Did the user give the full path for an existing database?

if [ "$create" = "y" ]; then
  # new DB + add the suffix
  dbPath="$newDBName"

  # If it already exists, increment
  i=0
  while [ -d "$dbPath" ]
  do
    echo "\"$dbPath\" already exists"
    ((i=i+1))
    dbPath="$newDBName-$i"
  done
elif [ "$latest" = "y" ]; then
  dbPath="$folderBase/$latestDBName"
elif [ -d "$1" ]; then
  dbPath=$1
fi

########################
# Other Variables
########################

restorePath=$2

pgurl="postgres://${dbUser}@127.0.0.1"

## Color reference
# Black        0;30     Dark Gray     1;30
# Red          0;31     Light Red     1;31
# Green        0;32     Light Green   1;32
# Brown/Orange 0;33     Yellow        1;33
# Blue         0;34     Light Blue    1;34
# Purple       0;35     Light Purple  1;35
# Cyan         0;36     Light Cyan    1;36
# Light Gray   0;37     White         1;37

C_MSG='\033[0;35m'
C_TRACE='\033[1;30m'
C_CMD='\033[1;36m'
C_DCMD='\033[1;30m'
C_PATH='\033[0;36m'
C_ERR='\033[0;31m'
C_='\033[0m' # No Color

########################
# Functions
########################

__initialize() {
  printf "${C_TRACE}Initialising the database ${C_PATH}$dbPath${C_}\n"
  initdb "$dbPath" 1> /dev/null
}

__show_databases() {
  echo "Showing folders in $folderBase"
  du -hsc $folderBase/*
}

__delete() {
  printf "${C_TRACE}Removing the exsting database at ${C_PATH}${dbPath}${C_}\n"
  rm -r "$dbPath";
}

__start() {
  printf "${C_TRACE}Starting postgres${C_}\n"
  postgres -D "$dbPath" -k /tmp &
  PG_PID=$!

  trap __stop EXIT

  # Wait for the database to start
  sleep 4
}

__stop() {
  printf "${C_TRACE}Stopping postgres${C_}\n"
  kill $PG_PID

  # Give it a sec for the db to stop so our messaging appears after postgres output
  sleep 1
  if [ $run = "y" ]; then
    printf "\n${C_MSG}FYI - You can start the database directly with:${C_}\n"
  else
    printf "\n${C_MSG}Start the database with:${C_}\n"
  fi
  printf "${C_CMD}postgres -D \"$dbPath\" -k /tmp${C_}\n\n"
}

__bootstrap() {
  printf "${C_TRACE}Bootstraping database${C_}\n"

  # Create the user and database
  createuser "$dbUser" --superuser 1> /dev/null
  createdb "$dbUser" 1> /dev/null
  createdb "$dbName" 1> /dev/null

  # Permissions
  psql "$pgurl/$dbUser" 1> /dev/null <<- SQL
  GRANT ALL ON DATABASE $dbName TO $dbUser;
SQL

  # Creat the schema if configured
  if [ "$schemaName" ]; then
    psql "$pgurl/$dbUser" 1> /dev/null <<- SQL
      \connect $dbName;
      CREATE SCHEMA $schemaName;
SQL
  fi

  # Other DB bootstrapping - This is going to be project specific
  # NOTE: It may make more sense to restore from dump
}

__restore() {
  if [ -f "$restorePath" ]; then
    printf "${C_TRACE}Restoring from: ${C_PATH}$restorePath${C_}\n"
    # <"$restorePath" psql "$pgurl/$dbName"
    # pg_restore 
    pg_restore --create --clean --no-privileges --no-owner --file /dev/stdout "${restorePath}" |\
    psql "$pgurl/$dbName"
    # psql --log-file="${LOG_PATH}" --quiet --echo-errors --output=/dev/null "$pgurl/$dbName"
  fi
}

__usage() {
message="Usage: init_dev_db.sh [dbPath] [restorePath] [options]
Options:
  -c --create    Create a new database
  -s --show      Show summary of databases in \"$folderBase\"
  -l --latest    Use the most recent database in \"$folderBase\"
  -d --delete    Delete the existing database
  -r --run       Run the database
  -h --help      Show documentation
"
  echo "$message"
}

__help() {
help="
${C_MSG}# Create and run postgres in your dev environment.${C_}

This script is designed to let you quickly spin up a fresh database
when working on a feature branch without messing up your ${C_DCMD}\"main\"${C_} dev database.

You will need to install postgres and disable the postgres service.
${C_DCMD}
> brew install postgres
> brew services stop postgres
${C_}

${C_MSG}# Examples:${C_}

Initialize a database and restore from a dump file.
${C_DCMD}> ./dev_db.sh ./db/my-feature ./dumps/database.sql${C_}

Passing ${C_DCMD}--delete${C_} will delete the db if it exists. Useful for testing migrations.
${C_DCMD}> ./dev_db.sh ./db/my-feature --delete${C_}

You can run a database with ${C_DCMD}--run${C_}.
${C_DCMD}> ./dev_db.sh ./db/my-feature --run${C_}

Replace, init and run in one line.
${C_DCMD}> ./dev_db.sh ./db/my-feature ./dumps/database.sql --delete --run${C_}


Run the latest database (alphabetically)
> ./dev_db.sh -l

Create a database in \"./data/$today\"
> ./dev_db.sh -c

Create a database in \"./data/$today-APP-955\"
> ./dev_db.sh -c APP-995

Run a specific database
> ./dev_db.sh -r ./data/20220407-APP-955-2

"
  printf "$help"
}

########################
# Input validation
########################

if [ "$showhelp" = "y" ]; then
  echo ""
  __usage
  __help
  exit
fi

if [ "$showDatabases" = "y" ]; then
  __show_databases
  exit
fi


# FIXME -- is this still needed?
if [ -z "$dbPath" ]; then
 printf "\n${C_ERR}Please specify a database path${C_}\n\n"
 __usage
 exit 1
fi

if [ ! -z "$restorePath" ] && [ ! -f "$restorePath" ]; then
  printf "\n${C_ERR}Cannot find restore file ${C_PATH}\"$restorePath\"${C_}\n\n"
  __usage
  exit 1
fi

########################
# RUN
########################

printf "${C_MSG}Initializing ${C_PATH}$dbPath\n"

# Delete exisiting db if it exists and we've been asked to
if [ "$delete" = "y" ] && [ -d "$dbPath" ]; then
  __delete
  exit
fi

# Initialize the database if it does not already exist and we've been asked to
if [ "$create" = "y" ] && [ ! -d "$dbPath" ]; then
  bootstrap="y" # we're going to need to bootstrap if we are initializing
  __initialize
  didSomething="y"
fi

# The Database should now exist. If not something is terribly wrong
if [ ! -d "$dbPath" ]; then
  printf "${C_ERR}Something is wrong. Could not find a database at ${C_PATH}$dbPath"
  exit 1;
fi

# We need to start postgres for everything that follows
__start

# Bootstrap the db
if [ "$bootstrap" = "y" ]; then
  __bootstrap
  didSomething="y"
fi

# Apply the restore file if provided
if [ "$restore" = "y" ] && [ "$restorePath" ]; then
  __restore
  didSomething="y"
fi

# All done, keep postgres running or exit with some messaging
if [ "$run" = "y" ]; then
  printf "\n${C_MSG}Database ready${C_} 🎉\n"
  printf "${C_CMD}ctrl-c${C_} to exit\n"
  # idle waiting for abort from user
  read -r -d '' _ </dev/tty
else
  if [ "$didSomething" = "y" ]; then
    printf "${C_MSG}All done${C_} 🎉\n"
  else
    printf "${C_MSG}Nothing to do here${C_}\n"
  fi
fi