Rollback ("down") Migrations

[itsmingjie]

Hi all! I've been using Hasura for my company's codebase but am interested in learning more about how Supabase works.

I'm trying to set up migrations, and after following the guide included in the Supabase CLI blog post, I was able to get a forward migration working. But I'm curious whether there is a way perform a migration "rollback"? Use case: developer A and developer B are working on two sibling branches, so when merging their branches it might be helpful to roll back applied migrations to resolve potential conflicts.

Hasura generates an up.sql and down.sql for each migration, and you can run hasura migrate apply --down 2 or hasura migrate apply --up 1 to move your database schema forward or backward in time. Is there a similar implementation that I'm missing for Supabase?

Hasura doc for rolling back migrations: https://hasura.io/docs/latest/migrations-metadata-seeds/manage-migrations/#roll-back-migrations

[oscyp]

Any update here?

[aripo99]

this is pretty important, how do others handle wrong migrations?

[otang]

Agreed. Would also like this.

[sjunepark]

This would be nice

[hnykda]

I would also love to have a nice solution to this. Currently, we do something like this, which is not ideal and sometimes breaks:

git checkout commit-to-which-to-revert

supabase db reset

# generate diff - this "drops" the changes
supabase db diff -f revert-latests.sql

# manually execute the revert-latests.sql 

# get the migrations table
supabase migration list

# mark the migrations you don't want to be applied as reverted
supabase migration repair --status reverted ...

[Rajat-Nipane]

Are you doing this manually or you wrote all this steps in CI/Cd pipeline. I am trying to do similar thing.

[jankalfus]

[hnykda]

I disagree with you. Your advice might be good for a mid-size+ org with many users, valuable data, etc. A small start-up following this advice with a couple of design partners with a couple of devs will get slowed down totally unnecessarily (simply because losing data since the last migration is totally acceptable, compared to always having to dump everything, validating ad-hoc backups, paying for PITR, ...).

Case in point, e.g. django does have this feature for years, many teams use that extensively without any problems.

It's totally valid supabase doesn't have this, there might not care about these customers/not worth it, I respect that. But I am wary about saying "this is how it's supposed to be done in this case, therefore it applies in general, therefore you should not ask for/use this feature". Let me handle my risk analysis on my own, feel free not to use the feature, but don't tell me I shouldn't need it based on generalization from an example that is not everyone 😁 .

[aripo99]

totally disagree with this point. doing a database backup before executing each migration is unnecessary work. most migrations are easily reversible, and there's a reason why so many frameworks support rollback. also in terms of developer experience being able to rollback is super nice (right now my solution in supabase is to reset the db, which is a pain)

[jankalfus]

To clarify, I was just saying what’s considered best practice (i.e. what’s a safe default), since the original question was how do you perform a migration rollback. And I was guessing that this might be the reason, or rather one of the reasons, supabase doesn’t support it.

Nobody says you need to do it that way, that’s a misunderstanding, do it whatever way you want to, especially if you’re fine with losing data.

[epicdragon44]

Bumping this -- automatic down migrations would be incredibly helpful within many developer workflows.

[epicdragon44]

Especially now, with Supabase branching and migrations becoming an essential part of the new Supabase experience. Otherwise, as it stands, it seems there's no good way to "undo" Supabase branch merges either.

[johnconnorquigley]

bumping this ^

[guilhemheinrich]

bumping this ^

[kallebysantos]

Hi, I'd create a simple shell script to handle it for me.
It's pretty simple to use.

Adding new migration

./supabase-migration new "documents entity"
This command will create a new migration files, both up and drop.

Applying migration

./supabase-migration up
This command will do the samething as the default supabase-cli migration up

Rollback migration

./supabase-migration rollback
This will look into migration history, then it tries to execute the drop script of the last migration. You could customize the $limit var to rollback recursively.

Code

The script will tries to look into you .env files to get a connection string.
So you may need to include it: PG_CONN_URL=postgresql://postgres:$POSTGRES_PASSWORD@$DEFAULT_HOST:$POSTGRES_PORT/$POSTGRES_DB

NOTE: I'd use Fuzzy Finder to help find the correctly drop script

#!/bin/bash

set -a # automatically export all variables
. <(cat .env supabase/.env)
set +a

# Function to display help information
help() {
	echo "Usage: $0 [command] [options]"
	echo
	echo "Commands:"
	echo "  new NAME   Create a new migration item"
	echo "  up         Push the current state"
	echo "  rollback   Rollback to the previous state"
	echo "  help       Display this help message"
	echo
	echo "Options for 'new' command:"
	echo "  -n NAME    Specify the name of the new migration"
}

new() {
	local migration_name=$1
	shift
	local migrations_folder="./supabase/migrations"
	local migration_timestamp="$(date +%Y%m%d%H%M%S)"

	mkdir -p $migrations_folder

	echo "-- $migration_name | Up commands" >"$migrations_folder/${migration_timestamp}_$migration_name.sql"
	echo "-- $migration_name | Drop commands" >"$migrations_folder/${migration_timestamp}|_$migration_name.drop.sql"

	echo "Created $migration_name migration files"
}

rollback() {
	read -p "Attention the Supabase DB will be rollback to the previous migration. Are you sure? (y/N) " -n 1 -r
	echo
	if [[ ! $REPLY =~ ^[Yy]$ ]]; then
		echo "Operation aborted"
		exit 0
	fi

	local rollback_limit=1

	read -red '' -a versions <<<$(psql $PG_CONN_URL -t -A -c \
		"select version from supabase_migrations.schema_migrations order by version DESC LIMIT $rollback_limit;")

	for version in "${versions[@]}"; do
		local drop_file=$(fzf --exit-0 --select-1 -q "$version|_.drop.sql")
		if [[ -z $drop_file ]]; then
			echo "No drop script found for $version migration."
			exit 1
		fi

		echo "Rollback migration: $version with '$drop_file'"
		local drop_result=$(
			psql $PG_CONN_URL -t -A --single-transaction -f \
				<(cat <(echo "delete from supabase_migrations.schema_migrations where version='$version';") $drop_file)
		)

		if [[ $drop_result == *"ERROR"* ]]; then
			echo "==="
			echo "Error while executing script: '$drop_file'"
			exit 1
		fi

		echo "Rollback migration: Successfully executed '$drop_file'"
	done
}

main() {
	local command=$1
	shift

	case $command in
	new) new "$@" ;;
	up) ./supabase-cli migration up --self-hosted $@ ;; # Here you can put the default supabase-cli
	rollback) rollback ;;
	-h | --help) help ;;
	*)
		echo "Invalid option: '$command'"
		help
		exit 1
		;;
	esac
}

main "$@"

There is another alias script supabase-cli that I made to simplify the --db-url command for self-hosted projects.

#! /bin/bash

set -a # automatically export all variables
. <(cat .env supabase/.env)
set +a

ARGS=$*

# '--self-host' : Use Self host
if [[ $* == *"--self-host"* ]]; then
	ARGS="$@ --db-url $PG_CONN_URL"
fi

# echo $ARGS

pnpm dlx supabase $(echo ${ARGS/--self-host/})