sharper.ly/scripts/setup-db.sh

#!/usr/bin/env bash
# ============================================================================
# SHARP-ER - create and verify the CMS database (manual setup, PATH A)
# ----------------------------------------------------------------------------
# Idempotent: running it twice is a no-op. It never drops anything and never
# touches data - it only creates the database if it is missing, then proves it
# can be connected to with the credentials Strapi will use.
#
#   ./scripts/setup-db.sh                  # use .env / the defaults below
#   ./scripts/setup-db.sh --name other_db  # override one value
#   ./scripts/setup-db.sh --help
#
# Docker users do not need this script: the postgres service creates the
# database from POSTGRES_DB on first boot.
# ============================================================================
set -euo pipefail

REPO_ROOT="$(cd -- "$(dirname -- "${BASH_SOURCE[0]}")/.." && pwd)"

# --- Defaults, overridden by .env, overridden by flags ----------------------
DB_NAME="sharper_cms"
DB_USER="postgres"
DB_PASSWORD=""
DB_HOST="127.0.0.1"
DB_PORT="5432"

# --- Output helpers --------------------------------------------------------
if [ -t 1 ] && [ -z "${NO_COLOR:-}" ]; then
  C_OK=$'\033[32m'; C_WARN=$'\033[33m'; C_ERR=$'\033[31m'; C_DIM=$'\033[2m'; C_OFF=$'\033[0m'
else
  C_OK=""; C_WARN=""; C_ERR=""; C_DIM=""; C_OFF=""
fi
ok()   { printf '%s  ok  %s %s\n' "$C_OK" "$C_OFF" "$*"; }
info() { printf '%s ..   %s %s\n' "$C_DIM" "$C_OFF" "$*"; }
warn() { printf '%s warn %s %s\n' "$C_WARN" "$C_OFF" "$*" >&2; }
die()  { printf '%s fail %s %s\n' "$C_ERR" "$C_OFF" "$*" >&2; exit 1; }

usage() {
  cat <<'USAGE'
setup-db.sh - create and verify the SHARP-ER CMS database (manual setup).

Idempotent: it never drops anything and never touches data. It creates the
database only if it is missing, then proves it can be connected to with the
credentials Strapi will use.

  ./scripts/setup-db.sh                       use .env / the defaults
  ./scripts/setup-db.sh --name other_db       override one value
  ./scripts/setup-db.sh --host db.internal --port 5433

Options:
  --name      database name          (default sharper_cms)
  --user      database role          (default postgres)
  --password  role password          (default: DATABASE_PASSWORD from .env)
  --host      server host            (default 127.0.0.1)
  --port      server port            (default 5432)
  -h, --help  show this and exit

Values come from ./.env, then apps/cms/.env, then these flags - last wins.
Docker users do not need this script: the postgres service creates the
database from POSTGRES_DB on first boot.
USAGE
  exit 0
}

# --help must work before anything else touches the filesystem or the server.
for arg in "$@"; do
  case "$arg" in -h|--help) usage ;; esac
done

# --- .env (root) ------------------------------------------------------------
# Read DATABASE_* only, so a stray line in .env cannot redefine anything else.
read_env_file() {
  local file="$1" line key value
  [ -f "$file" ] || return 0
  while IFS= read -r line || [ -n "$line" ]; do
    case "$line" in
      DATABASE_NAME=*|DATABASE_USERNAME=*|DATABASE_PASSWORD=*|DATABASE_HOST=*|DATABASE_PORT=*) ;;
      *) continue ;;
    esac
    key="${line%%=*}"
    value="${line#*=}"
    value="${value%\"}"; value="${value#\"}"
    value="${value%\'}"; value="${value#\'}"
    [ -n "$value" ] || continue
    case "$key" in
      DATABASE_NAME)     DB_NAME="$value" ;;
      DATABASE_USERNAME) DB_USER="$value" ;;
      DATABASE_PASSWORD) DB_PASSWORD="$value" ;;
      DATABASE_HOST)     DB_HOST="$value" ;;
      DATABASE_PORT)     DB_PORT="$value" ;;
    esac
  done < "$file"
  info "read defaults from ${file#"$REPO_ROOT"/}"
}

read_env_file "$REPO_ROOT/.env"
read_env_file "$REPO_ROOT/apps/cms/.env"

# --- Flags ------------------------------------------------------------------
while [ $# -gt 0 ]; do
  case "$1" in
    --name)     DB_NAME="${2:?--name needs a value}"; shift 2 ;;
    --user)     DB_USER="${2:?--user needs a value}"; shift 2 ;;
    --password) DB_PASSWORD="${2:?--password needs a value}"; shift 2 ;;
    --host)     DB_HOST="${2:?--host needs a value}"; shift 2 ;;
    --port)     DB_PORT="${2:?--port needs a value}"; shift 2 ;;
    -h|--help)  usage ;;
    *) die "unknown option: $1 (try --help)" ;;
  esac
done

[ -n "$DB_PASSWORD" ] && export PGPASSWORD="$DB_PASSWORD"

PSQL=(psql --host "$DB_HOST" --port "$DB_PORT" --username "$DB_USER" --no-password --quiet --tuples-only --no-align)

# --- 1. Client tools --------------------------------------------------------
command -v psql     >/dev/null 2>&1 || die "psql not found. Install the PostgreSQL 16 client (Debian/Ubuntu: sudo apt install postgresql-client-16)."
command -v createdb >/dev/null 2>&1 || die "createdb not found. It ships with the PostgreSQL client package."
ok "client tools found ($(psql --version))"

# --- 2. Server reachable ----------------------------------------------------
if command -v pg_isready >/dev/null 2>&1; then
  pg_isready --host "$DB_HOST" --port "$DB_PORT" --timeout 5 >/dev/null 2>&1 \
    || die "no PostgreSQL server answering on $DB_HOST:$DB_PORT. Start it (sudo systemctl start postgresql) or point --host/--port somewhere else."
fi
if ! "${PSQL[@]}" --dbname postgres --command "SELECT 1" >/dev/null 2>&1; then
  die "cannot authenticate as \"$DB_USER\" on $DB_HOST:$DB_PORT. Check DATABASE_USERNAME / DATABASE_PASSWORD in .env, or pass --user / --password."
fi
ok "connected to $DB_HOST:$DB_PORT as \"$DB_USER\""

# --- 3. Database ------------------------------------------------------------
db_exists() {
  local found
  found="$("${PSQL[@]}" --dbname postgres --command \
    "SELECT 1 FROM pg_database WHERE datname = '${DB_NAME//\'/\'\'}'" 2>/dev/null || true)"
  [ "$(printf '%s' "$found" | tr -d '[:space:]')" = "1" ]
}

if db_exists; then
  ok "database \"$DB_NAME\" already exists - nothing to create"
else
  info "creating database \"$DB_NAME\" ..."
  createdb --host "$DB_HOST" --port "$DB_PORT" --username "$DB_USER" --no-password \
    --encoding UTF8 --owner "$DB_USER" "$DB_NAME" \
    || die "createdb failed. Does \"$DB_USER\" have CREATEDB rights?"
  ok "database \"$DB_NAME\" created (UTF8, owner \"$DB_USER\")"
fi

# --- 4. Verify --------------------------------------------------------------
SERVER_VERSION="$("${PSQL[@]}" --dbname "$DB_NAME" --command "SHOW server_version" | awk 'NR==1 {print $1}')"
ENCODING="$("${PSQL[@]}" --dbname "$DB_NAME" --command \
  "SELECT pg_encoding_to_char(encoding) FROM pg_database WHERE datname = current_database()" | tr -d '[:space:]')"
CAN_CREATE="$("${PSQL[@]}" --dbname "$DB_NAME" --command \
  "SELECT has_database_privilege(current_user, current_database(), 'CREATE')" | tr -d '[:space:]')"

[ "$CAN_CREATE" = "t" ] || warn "\"$DB_USER\" cannot CREATE in \"$DB_NAME\"; Strapi will not be able to build its tables."
case "$SERVER_VERSION" in
  16*|17*|18*) ;;
  *) warn "server is PostgreSQL $SERVER_VERSION; this project is developed against 16." ;;
esac

ok "verified: $DB_NAME on PostgreSQL $SERVER_VERSION, encoding $ENCODING"
printf '\n'
printf 'Put these in apps/cms/.env (cp apps/cms/.env.example apps/cms/.env first):\n'
printf '  DATABASE_CLIENT=postgres\n'
printf '  DATABASE_HOST=%s\n  DATABASE_PORT=%s\n  DATABASE_NAME=%s\n  DATABASE_USERNAME=%s\n  DATABASE_PASSWORD=%s\n' \
  "$DB_HOST" "$DB_PORT" "$DB_NAME" "$DB_USER" "${DB_PASSWORD:-<your password>}"
printf '\nNext: ./scripts/generate-secrets.sh --write apps/cms/.env && pnpm dev\n'