End-to-end setup script for deploying a PostgreSQL database and optional droplet (for API layer) to Digital Ocean using `doctl` in Bash

setup_digital_ocean_db.sh

#!/bin/bash

# Exit on error
set -e

# Load environment variables if .env exists
if [ -f .env ]; then
    export $(cat .env | grep -v '^#' | xargs)
fi

# Default values (override these with environment variables or command line arguments)
PROJECT_NAME=${PROJECT_NAME:-"my-project"}
PROJECT_PURPOSE=${PROJECT_PURPOSE:-"Database and application hosting"}
DB_NAME=${DB_NAME:-"${PROJECT_NAME}-db"}
DB_SIZE=${DB_SIZE:-"db-s-1vcpu-1gb"}
DB_REGION=${DB_REGION:-"nyc3"}
DB_VERSION=${DB_VERSION:-"17"}
CREATE_DROPLET=${CREATE_DROPLET:-"false"}
DROPLET_NAME=${DROPLET_NAME:-"${PROJECT_NAME}-app"}
DROPLET_SIZE=${DROPLET_SIZE:-"s-1vcpu-1gb"}
APP_USER=${APP_USER:-"app_user"}

# Colors for output
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
NC='\033[0m' # No Color

# Helper functions
log_info() {
    echo -e "${GREEN}[INFO]${NC} $1"
}

log_warn() {
    echo -e "${YELLOW}[WARN]${NC} $1"
}

log_error() {
    echo -e "${RED}[ERROR]${NC} $1"
}

generate_secure_password() {
    openssl rand -base64 32 | tr -d '/+=' | cut -c1-24
}

check_prerequisites() {
    log_info "Checking prerequisites..."
    
    # Check if doctl is installed
    if ! command -v doctl &> /dev/null; then
        log_error "doctl is not installed. Please install it first."
        exit 1
    fi

    # Check if psql is installed
    if ! command -v psql &> /dev/null; then
        log_error "psql is not installed. Please install it first."
        exit 1
    }

    # Check doctl authentication
    if ! doctl account get &> /dev/null; then
        log_error "doctl is not authenticated. Please run 'doctl auth init' first."
        exit 1
    }

    log_info "Prerequisites check passed."
}

create_project() {
    log_info "Creating Digital Ocean project: $PROJECT_NAME"
    
    # Check if project already exists
    if doctl projects list --format Name | grep -q "^${PROJECT_NAME}$"; then
        log_warn "Project '$PROJECT_NAME' already exists."
        PROJECT_ID=$(doctl projects list --format ID,Name | grep "${PROJECT_NAME}" | awk '{print $1}')
    else
        PROJECT_ID=$(doctl projects create --name "$PROJECT_NAME" \
            --purpose "$PROJECT_PURPOSE" \
            --environment "Production" \
            --format ID --no-header)
        log_info "Project created with ID: $PROJECT_ID"
    fi
    
    echo "PROJECT_ID=$PROJECT_ID" >> .env
}

create_database() {
    log_info "Creating PostgreSQL database: $DB_NAME"
    
    # Create the database
    DB_OUTPUT=$(doctl databases create "$DB_NAME" \
        --engine pg \
        --version "$DB_VERSION" \
        --region "$DB_REGION" \
        --size "$DB_SIZE" \
        --num-nodes 1 \
        --wait \
        --format ID,Name,Status --no-header)
    
    DATABASE_ID=$(echo "$DB_OUTPUT" | awk '{print $1}')
    
    # Wait for database to be ready
    log_info "Waiting for database to be ready..."
    while true; do
        STATUS=$(doctl databases get "$DATABASE_ID" --format Status --no-header)
        if [ "$STATUS" = "online" ]; then
            break
        fi
        sleep 10
    done
    
    # Get connection details
    CONNECTION_INFO=$(doctl databases connection "$DATABASE_ID" \
        --format Host,Port,User,Password,Database)
    
    # Parse connection details
    DB_HOST=$(echo "$CONNECTION_INFO" | awk '{print $1}')
    DB_PORT=$(echo "$CONNECTION_INFO" | awk '{print $2}')
    DB_USER=$(echo "$CONNECTION_INFO" | awk '{print $3}')
    DB_PASS=$(echo "$CONNECTION_INFO" | awk '{print $4}')
    DB_NAME=$(echo "$CONNECTION_INFO" | awk '{print $5}')
    
    # Save to .env file
    {
        echo "DB_HOST=$DB_HOST"
        echo "DB_PORT=$DB_PORT"
        echo "DB_USER=$DB_USER"
        echo "DB_PASS=$DB_PASS"
        echo "DB_NAME=$DB_NAME"
        echo "DB_URI=postgres://$DB_USER:$DB_PASS@$DB_HOST:$DB_PORT/$DB_NAME?sslmode=require"
        echo "DO_DATABASE_ID=$DATABASE_ID"
    } >> .env
    
    log_info "Database created and credentials saved to .env"
}

create_droplet() {
    if [ "$CREATE_DROPLET" != "true" ]; then
        log_info "Skipping droplet creation (CREATE_DROPLET != true)"
        return
    fi

    log_info "Creating droplet: $DROPLET_NAME"
    
    # Check SSH keys
    SSH_KEYS=$(doctl compute ssh-key list --format ID --no-header)
    if [ -z "$SSH_KEYS" ]; then
        log_warn "No SSH keys found. Creating a new one..."
        ssh-keygen -t rsa -b 4096 -f ~/.ssh/id_rsa -N ""
        doctl compute ssh-key import "default-key" --public-key-file ~/.ssh/id_rsa.pub
        SSH_KEYS=$(doctl compute ssh-key list --format ID --no-header)
    fi
    
    # Create droplet
    DROPLET_ID=$(doctl compute droplet create "$DROPLET_NAME" \
        --region "$DB_REGION" \
        --size "$DROPLET_SIZE" \
        --image ubuntu-22-04-x64 \
        --ssh-keys "$SSH_KEYS" \
        --wait \
        --format ID --no-header)
    
    # Get droplet IP
    DROPLET_IP=$(doctl compute droplet get "$DROPLET_ID" --format PublicIPv4 --no-header)
    
    # Save to .env file
    {
        echo "DROPLET_ID=$DROPLET_ID"
        echo "DROPLET_NAME=$DROPLET_NAME"
        echo "DROPLET_IP=$DROPLET_IP"
    } >> .env
    
    # Assign to project
    doctl projects resources assign "$PROJECT_ID" --resource="do:droplet:$DROPLET_ID"
    
    log_info "Droplet created and assigned to project"
}

setup_database_users() {
    log_info "Setting up database users and permissions..."
    
    # Generate a secure password for the application user
    APP_USER_PASSWORD=$(generate_secure_password)
    
    # Create temporary SQL file
    TMP_SQL=$(mktemp)
    
    # Write SQL commands
    cat > "$TMP_SQL" << EOF
-- Create application user
CREATE ROLE $APP_USER WITH LOGIN PASSWORD '$APP_USER_PASSWORD';

-- Create application schema
CREATE SCHEMA IF NOT EXISTS app;

-- Grant necessary permissions
GRANT USAGE ON SCHEMA app TO $APP_USER;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO $APP_USER;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA app TO $APP_USER;

-- Set default privileges for future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA app
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO $APP_USER;
ALTER DEFAULT PRIVILEGES IN SCHEMA app
    GRANT USAGE, SELECT ON SEQUENCES TO $APP_USER;
EOF
    
    # Execute SQL file
    PGPASSWORD=$DB_PASS psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -f "$TMP_SQL"
    
    # Clean up
    rm "$TMP_SQL"
    
    # Save app user credentials to .env
    {
        echo "APP_USER=$APP_USER"
        echo "APP_USER_PASSWORD=$APP_USER_PASSWORD"
    } >> .env
    
    log_info "Database users and permissions configured"
}

verify_setup() {
    log_info "Verifying setup..."
    
    # Check database
    if doctl databases list | grep -q "$DB_NAME"; then
        log_info "✓ Database is running"
    else
        log_error "Database verification failed"
        exit 1
    fi
    
    # Check droplet if created
    if [ "$CREATE_DROPLET" = "true" ]; then
        if doctl compute droplet list | grep -q "$DROPLET_NAME"; then
            log_info "✓ Droplet is running"
        else
            log_error "Droplet verification failed"
            exit 1
        fi
    fi
    
    # Test database connection
    if PGPASSWORD=$APP_USER_PASSWORD psql -h "$DB_HOST" -p "$DB_PORT" -U "$APP_USER" -d "$DB_NAME" \
        -c "SELECT current_user, current_database();" > /dev/null 2>&1; then
        log_info "✓ Database connection successful"
    else
        log_error "Database connection test failed"
        exit 1
    fi
    
    log_info "Setup verification completed successfully"
}

main() {
    log_info "Starting PostgreSQL setup on DigitalOcean..."
    
    check_prerequisites
    create_project
    create_database
    create_droplet
    setup_database_users
    verify_setup
    
    log_info "Setup completed successfully!"
    log_info "Connection details and credentials have been saved to .env"
}

# Run main function
main