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

config.sh

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

dbUser="postgres"
dbPass="postgres"
dbName='app-name'
folderBase="./data"
dumpFolder="./dump"
# schemaName="public" # -- not needed for public

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

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

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

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.


########################
# Load shared config
########################

source ./config.sh

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

run="y"
latest="y"

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

  case $key in
    -h|--help)
      showhelp="y"
      run="n"
      shift # past argument
      ;;
    -l|--list)
      listDatabases="y"
      run="n"
      shift
      ;;
    -c|--create)
      create="y"
      latest="n"
      shift
      ;;
    --delete)
      delete="y"
      run="n"
      latest="n"
      shift # past argument
      ;;
    --dump)
      dump="y"
      run="n"
      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)

# 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

  # If the first param is not empty
  if [ ! -z "$1" ]; then
    newDBName="$today-$1"
  else
    newDBName="$today"
  fi

  # new DB + add the suffix
  dbPath="$folderBase/$newDBName"

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

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

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

__list_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

  # Theres a postgres user db by default
  if [ "$dbUser" != "postgres" ]; then
    createdb "$dbUser" 1> /dev/null
  fi
  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

}

__dump() {
  mkdir -p "$dumpFolder"
  dumpPath="$dumpFolder/$dbName-$today.pg.zst"
  printf "${C_TRACE}Dumping database to ${C_PATH}$dumpPath${C_}\n"
  pg_dump "$pgUrl/$dbName" --format=c -Z0 | zstd -11 > "$dumpPath"
}

__usage() {
  # FIXME: make --restore work and then document it
message="Usage: init_dev_db.sh [dbPath] [options]
Options:
  -h --help      Show documentation
  -c --create    Create a new database
  -s --show      Show summary of databases in \"$folderBase\"
  -r --run       Run the database
  --delete       Delete the existing database
  --dump         Create a dump of the database
"
  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_}

List all databases in the data folder.
${C_DCMD}> ./dev_db.sh --list${C_}

Delete an existing db.
${C_DCMD}> ./dev_db.sh ./db/my-feature --delete${C_}

Run a specific database.
${C_DCMD}> ./dev_db.sh ./db/my-feature --run${C_}

Run the most recent database.
${C_DCMD}> ./dev_db.sh${C_}


Run the latest database
> ./dev_db.sh

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 ./data/{database_name}

"
  printf "$help"
}

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

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

if [ "$listDatabases" = "y" ]; then
  __list_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 [ "$dump" = "y" ]; then
  __dump
  exit
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

# 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

restore_db.sh

#!/bin/bash

# This script is a companion to the dev_db.sh and is used to restore a
# PostgreSQL database from a zst compressed dump file.

# Load the shared configuration
source ./config.sh

# Usage: ./dev_restore.sh <dump_file.zst>

if [ "$#" -ne 1 ]; then
  echo "Usage: $0 <dump_file.zst>"
  exit 1
fi

## Check that the database is running
if ! psql -U "$dbUser" -d "$dbName" -c '\q' &>/dev/null; then
  printf "\n${C_ERR}Database ${C_PATH}$dbName${C_} is not running or not accessible.\n"
  printf "Please start the database server and try again.\n"
  exit 1
fi

# extract and restore the database


dumpFile="$1"
if [ ! -f "$dumpFile" ]; then
  printf "\n${C_ERR}Dump file ${C_PATH}$dumpFile${C_} does not exist.\n"
  exit 1
fi

# Drop the existing database
printf "${C_MSG}Dropping existing database ${C_PATH}$dbName${C_} if it exists...\n"
dropdb --if-exists --username=postgres "$dbName"
if [ $? -ne 0 ]; then
  printf "\n${C_ERR}Failed to drop the existing database ${C_PATH}$dbName${C_}.\n"
  exit 1
fi

# Create a new database
printf "${C_MSG}Creating new database ${C_PATH}$dbName${C_}...\n"
createdb --username=postgres "$dbName"
if [ $? -ne 0 ]; then
  printf "\n${C_ERR}Failed to create the database ${C_PATH}$dbName${C_}.\n"
  exit 1
fi

# Extract and restore the database
printf "${C_MSG}Extracting and restoring database from ${C_PATH}$dumpFile${C_}\n"
zstdcat "$dumpFile" | pg_restore --dbname "$pgUrl/$dbName" --no-owner --no-acl --clean --if-exists -U postgres

if [ $? -ne 0 ]; then
  printf "\n${C_ERR}Failed to restore the database from ${C_PATH}$dumpFile${C_}.\n"
  exit 1
fi

printf "\n${C_MSG}Database ${C_PATH}$dbName${C_} restored successfully from ${C_PATH}$dumpFile${C_}.\n"