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"
1 Like
3

Hi @Daniel_Terra,

Interesting question. I haven't seen another other users bring up this issue but I can definitely check internally to see if we have official recommendations for migrating data and schemas across Spaces.

Are you self hosting or on the cloud?

4

Hi @Daniel_Terra,

Looks like we have a feature request ticket to have DB data and schema migration as a built in native tool! I will attach your thread onto this and let you know about any updates on the thread from the eng team.

In the meantime, if your workaround is able to manually get the schema from the PG instance and move that, this is likely the best alternative.