Retool Database Migration from one Space to another

Queries and Resources
1

Goal: Replicate the Retool Database schema from one space to another

Hi everyone,

I'm using Spaces to manage Production and Staging environments, along with Source Control to handle deployments to Production.

The issue I'm facing is that I can't "protect" the Retool Database schema or version schema changes effectively. While there is a migration tool available, it only works for environments within the same space.

Has anyone encountered this issue before? If so, what strategies have you used to manage database schema changes between spaces? Any insights or best practices would be greatly appreciated!

2

This is the solution i came up soo far with chatgpt help;

Install dependencies

brew install apgdiff
brew install postgresql@15
brew link --force postgresql@15

Create a bash script; migration.sh

#!/bin/bash

# Connection variables
DEV_HOST=""
DEV_DB="retool"
DEV_USER="retool"
DEV_PASSWORD=""

PROD_HOST=""
PROD_DB="retool"
PROD_USER="retool"
PROD_PASSWORD=""

# Temp files automatically generated
DEV_SCHEMA="dev_schema.sql"
PROD_SCHEMA="prod_schema.sql"
UPDATE_SCRIPT="update_schema.sql"

# Export schema from dev database
echo "Export schema from dev database..."
PGPASSWORD="$DEV_PASSWORD" pg_dump -h "$DEV_HOST" -U "$DEV_USER" -d "$DEV_DB" -s -f "$DEV_SCHEMA"

# Export schema from production database
echo "Export schema from production database..."
PGPASSWORD="$PROD_PASSWORD" pg_dump -h "$PROD_HOST" -U "$PROD_USER" -d "$PROD_DB" -s -f "$PROD_SCHEMA"

# Compare schemas and generate a migration"
apgdiff "$PROD_SCHEMA" "$DEV_SCHEMA" > "$UPDATE_SCRIPT"

# Check changes
if [ -s "$UPDATE_SCRIPT" ]; then
    echo "Applying changes to production..."

    PGPASSWORD="$PROD_PASSWORD" psql -h "$PROD_HOST" -U "$PROD_USER" -d "$PROD_DB" -f "$UPDATE_SCRIPT"

    echo "Done!"
else
    echo "No changes found"
fi

# Remove temp files
rm -f "$DEV_SCHEMA" "$PROD_SCHEMA" "$UPDATE_SCRIPT"