Handy scripts

[kentcdodds]

We have good instructions for backing up and restoring the database among other things. I think it would be really nice to package that up in a script.

I've been working on this a little bit already, but it's occurred to me that we should probably make this more formal and write it in JS instead of bash (because it's getting complicated). In particular, with multiple instances of the app you need to make sure each command you run against fly runs against the same instance and you probably want to run some of these against the primary instance as well.

Fly has the fly status --json command which should make it possible to determine the primary and then you can run things against that.

I'm posting this here in case anyone wants to continue from where I left off. Here's the bash script I've got so far. These don't work. I remembered a bit ago that these scripts would run locally, not on the machine so they're partway fixed from that mistake and they also assume some environment variables which should probably be inquirer inputs.

./other/scripts/backup-db.sh

#!/bin/sh

set -x

echo "➡️ Creating backup of sqlite.db"
BACKUP_NAME="/backups/backup-$(date +%Y-%m-%d_%H.%M.%S)-sqlite.db"
fly ssh console -C "mkdir -p /backups"
fly ssh console -C "litefs export -name sqlite.db \"$BACKUP_NAME\""

# Integrity check
echo "👀 Performing integrity check on the backup..."
INTEGRITY_CHECK=$(sqlite3 sqlite.db "PRAGMA integrity_check;")
if [ "$INTEGRITY_CHECK" != "ok" ]; then
  echo "Integrity check failed: $INTEGRITY_CHECK"
  exit 1
fi

echo "🗜️ Zipping backup file..."
gzip "$BACKUP_NAME"
GZ_BACKUP_NAME="${BACKUP_NAME}.gz"

echo "📥 downloading backup file"
fly ssh sftp get "$GZ_BACKUP_NAME"

./other/scripts/restore-db.sh

#!/bin/bash

# Check if the required environment variables are set
if [ -z "$FLY_APP_NAME" ] || [ -z "$DATABASE_FILENAME" ]; then
  echo "❌ Error: FLY_APP_NAME and DATABASE_FILENAME environment variables must be set."
  exit 1
fi

# Prompt the user with a warning
echo "⚠️ Warning - This overwrites your database for $FLY_APP_NAME. You can lose data. It is recommended you take a backup before proceeding."
read -p "Are you sure you want to proceed? (yes/no): " response

if [ "$response" != "yes" ]; then
  echo "Operation aborted by the user."
  exit 1
fi

# Prompt the user for the location of the backup file
read -p "Enter the location of the backup file: " BACKUP_FILE

if [ ! -f "$BACKUP_FILE" ]; then
  echo "❌ Error: Backup file not found at the specified location."
  exit 1
fi

# Establish an sftp session with the fly instance and upload the backup file to the server
echo "📤 Uploading backup file with SFTP..."
fly ssh sftp shell --app $FLY_APP_NAME <<EOF
put $BACKUP_FILE
EOF

# Extract the filename from the backup file path
BACKUP_FILENAME=$(basename $BACKUP_FILE)

echo "🔌 Connecting to the Fly instance via SSH..."
fly ssh console --app $FLY_APP_NAME <<EOF
echo "👀 Performing integrity check on the existing database..."
PRAGMA_CHECK_RESULT=\$(sqlite3 $DATABASE_FILENAME "PRAGMA integrity_check;")
if [ "\$PRAGMA_CHECK_RESULT" != "ok" ]; then
  echo "❌ Error: Existing database integrity check failed."
  exit 1
fi

# Check if the backup file is gzipped and unzip if it is
if [[ $BACKUP_FILENAME == *.gz ]]; then
  echo "🗜️ Unzipping backup file..."
  gunzip /$BACKUP_FILENAME
  BACKUP_FILENAME=\${BACKUP_FILENAME%.gz}
fi

echo "🔁 Restoring the database from the backup file..."
litefs import -name $DATABASE_FILENAME /$BACKUP_FILENAME

echo "👀 Performing integrity check on the restored database..."
PRAGMA_CHECK_RESULT=\$(sqlite3 $DATABASE_FILENAME "PRAGMA integrity_check;")
if [ "\$PRAGMA_CHECK_RESULT" != "ok" ]; then
  echo "❌ Error: Restored database integrity check failed."
  exit 1
fi

# Exit the ssh session
exit
EOF

if [ $? -ne 0 ]; then
  echo "❌ Error: An error occurred during the database restoration process."
  exit 1
fi

echo "✅ Database restoration complete."

Anyone who's interested in giving us a head start on this is welcome to do so!

[bradymwilliams]

Here's my functions that I use in a local zsh plugin that reliably pulls from primary if that helps anyone.

fly_backup_prod() {
  app=$1 # i have this hardcoded but could be read from fly.toml or something
  primary=$(fly status --app $app | grep primary | awk '{print $2}')

  echo "Connecting to production"
  fly ssh console --command "litefs export -name sqlite.db production-$(date +'%y-%m-%d').db" --app $app --machine $primary
  fly ssh sftp get production-$(date +'%y-%m-%d').db --app $app --machine $primary
}

fly_files_prod() {
  app=$1 # i have this hardcoded but could be read from fly.toml or something
  primary=$(fly status --app $app | grep primary | awk '{print $2}')
  fly ssh sftp shell --app $app --machine $primary
}

fly_ssh_prod() {
  app=$1 # i have this hardcoded but could be read from fly.toml or something
  primary=$(fly status --app $app | grep primary | awk '{print $2}')
  fly ssh console --app $app --machine $primary
}

fly_proxy_prod() {
  app=$1 # i have this hardcoded but could be read from fly.toml or something
  fly proxy 5556:5555 --app $app
}

fly_prisma_prod() {
  app=$1 # i have this hardcoded but could be read from fly.toml or something
  primary=$(fly status --app $app | grep primary | awk '{print $2}')
  fly ssh console -C "npm run prisma:studio" --app $app --machine $primary
}

[Cmosley]

Does anyone have a script that can dump the primary database into the staging app database? I'm essentially not even using the staging app atm because it has no data.