Prisma rollback shell script

generate_migration.sh

#!/bin/bash

# prisma does not generate rollback (down script by default)
# this script will generate the down script for each migration

name=$1
dir=$(pwd)

if [ -z "$name" ]
then
  echo "Please provide a name for the migration"
  exit 1
fi

echo "Generating migration $name"

echo "Generating down.sql"
# generate the down.sql first before the migration
dotenv -e .env.development -- npx prisma migrate diff --from-schema-datamodel prisma/schema.prisma --to-schema-datasource prisma/schema.prisma --script > /tmp/down.sql

echo "run yarn db:migrate"
# generate the migration file using prisma
npx dotenv -e .env.development -- npx prisma migrate dev --create-only --name $name

# find the latest migration
migration_path="${dir}/prisma/migrations"
latest_path=$(ls -td -- "${migration_path}"/*/ | head -n 1)
migration_name=$(basename "${latest_path%/}")

# adding the rollback for migration table
echo "-- remove the last migration from the _prisma_migrations table" >> /tmp/down.sql
echo "DELETE FROM _prisma_migrations WHERE migration_name = '${migration_name}';" >> /tmp/down.sql
mv /tmp/down.sql ${dir}/prisma/migrations/${migration_name}/

echo "Done"

rollback.sh

#!/bin/bash

# rollback the latest migration (only apply to the db)
dir=$(pwd)

echo "Rollback the latest migration"

# confirm with the user
read -p "Are you sure you want to rollback the latest migration? (y/n) " -n 1 -r
if [[ ! $REPLY =~ ^[Yy]$ ]]
then
  echo "Aborted"
  exit 1
fi

# find the latest migration
migration_path="${dir}/prisma/migrations"
latest_path=$(ls -td -- "${migration_path}"/*/ | head -n 1)
migration_name=$(basename "${latest_path%/}")

echo "Executing: $dir/prisma/migrations/${migration_name}/down.sql"
npx dotenv -e .env.development -- npx prisma db execute --file ${dir}/prisma/migrations/${migration_name}/down.sql

echo "Done"

I have placed the files into ${project_root}/bin, from where I run bin/generate_migration and bin/rollback.
I only use these commands in the development environment, aiming for a rake db:rollback-like behaviour, which I mostly utilize for rollbacks.

The above script should be added to package.json => scripts section and dotenv-cli installed (npm install dotenv-cli --save-dev). Otherwise you will get an error dotenv: command not found.
Alternatively, to run directly, you can prefix with npx, e.g. npx dotenv.

Additionally, the script assumes a tmp folder exists in your npm project. so either create that or adjust to root /tmp/down.sql instead.

@silverham Thanks for the feedback! I agree with your suggestion and have updated the gist.