Cleanup Demo Database #77
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| name: Cleanup Demo Database | |
| on: | |
| schedule: | |
| - cron: "0 2 * * *" # Runs at 2 AM UTC every day | |
| workflow_dispatch: # Allows manual triggering | |
| jobs: | |
| cleanup: | |
| name: Cleanup Demo Database | |
| runs-on: ubuntu-latest | |
| steps: | |
| - name: Checkout | |
| uses: actions/checkout@v2 | |
| with: | |
| submodules: "true" | |
| - uses: actions/setup-node@v2 | |
| with: | |
| node-version: "18" | |
| - name: Install Dependencies | |
| run: npm ci | |
| - name: Configure AWS Credentials | |
| uses: aws-actions/configure-aws-credentials@v1 | |
| with: | |
| aws-access-key-id: ${{ secrets.STAGING_AWS_ACCESS_KEY_ID }} | |
| aws-secret-access-key: ${{ secrets.STAGING_AWS_SECRET_ACCESS_KEY }} | |
| aws-region: us-east-2 | |
| - name: Reset and Initialize Database | |
| run: | |
| | | |
| # Get connection string from Parameter Store | |
| CONNECTION_STRING=$(aws ssm get-parameter --name "/demo/contentApi/connectionString" --with-decryption --query "Parameter.Value" --output text) | |
| # Extract database name from connection string | |
| DB_NAME=$(echo $CONNECTION_STRING | sed -n 's/.*\/\/[^:]*:[^@]*@[^:]*:[0-9]*\/\([^?]*\).*/\1/p') | |
| # Extract host, user, and password from connection string | |
| DB_HOST=$(echo $CONNECTION_STRING | sed -n 's/.*@\([^:]*\):[0-9]*\/.*/\1/p') | |
| DB_USER=$(echo $CONNECTION_STRING | sed -n 's/.*:\/\/\([^:]*\):.*/\1/p') | |
| DB_PASS=$(echo $CONNECTION_STRING | sed -n 's/.*:\/\/[^:]*:\([^@]*\)@.*/\1/p') | |
| # Drop and recreate database | |
| mysql -h "$DB_HOST" -u "$DB_USER" -p"$DB_PASS" -e "DROP DATABASE IF EXISTS ${DB_NAME}; CREATE DATABASE ${DB_NAME};" | |
| # Create .env file for initdb script | |
| echo "APP_ENV=Demo" > .env | |
| echo "CONNECTION_STRING=$CONNECTION_STRING" >> .env | |
| # Run the initdb script to create tables and seed data | |
| npm run initdb | |
| # Run the demo.mysql file to populate demo data | |
| mysql -h "$DB_HOST" -u "$DB_USER" -p"$DB_PASS" "$DB_NAME" < tools/dbScripts/demo.mysql |