Roll back the latest migration group

[vadim-shilov]

Will Prisma Migrate support to roll back the latest migration group?
I couldn't find anything like this http://knexjs.org/#Migrations-rollback in docs.

For example:
A new version of an app includes some migrations.
It was deployed (migrations were applied) but after was made a decision to roll it back to the previous version.
What the way to roll back the last batch of migrations?

[albertoperdomo]

Traditional migration systems rely on up and down migrations to move up and down version of the DB schema. While this system is prevalent, we've learned from speaking with many developers that most of the time down migrations are not used.

In a production environment, rollbacks can lead to data loss. For example: dropping a column or table that was added in a migration, and which has been exposed to users for storing data, will lead to losing this data from users. It may sound counterintuitive, but often the way to resolve this kind of situation is not by undoing the changes from the migrations but rather to fix the underlying issue, or disable the corresponding feature.

Our current recommendation is to resolve issues by rolling forward: create a new migration with the changes you want.

Note all of this is relevant when the migrations have been applied in other environments, i.e. production. If the migrations you want to "undo" are in development, you can always delete the migration directory, and the next time you run prisma migrate dev, it will prompt to reset your development database.

Removing database migration directories is of course not something you should do in production or a similar environment, i.e. staging.

We are open to feedback about rollbacks (and of course about everything else): If you have strong opinions on why we should implement rollbacks please do share your thoughts with us.

[formigarafa]

Traditional migration systems rely on up and down migrations to move up and down version of the DB schema.
...

I bet those traditional systems rely on up and down based on arguments, not just because of opinions.
I can imagine two simple scenarios where being able to rollback a migration would be at least helpful:

• Developing multiple features in separated branches; and first and foremost
• Contingency plan: (expect for the unexpected).

And doesn't matter how much you test, prepare, check and review, one day mistakes will be made and whoever is using this tool they will hope they can rollback the migration as quick as possible to avoid build up of more of whatever problem they will be having form their production environment.

I don't think people are asking for autogenerated down migrations, which even the autogenerated up migration can be misleading. Some people will understand what I mean when they are adding a new non nullable column with a default value in a table with millions of records. Even though there is a single command to do that if you want this to happen with zero-downtime you will need to do this change in steps. Likely, in multiple transactions.

But maybe if there is, at least, a clear path on how to achieve this optionally, then this discussion wouldn't be necessary.
Something like a command that will execute the down migration to the latest migration executed if the file exists.
That would probably be enough.

[tylerslater-io]

While this system is prevalent, we've learned from speaking with many developers that most of the time down migrations are not used.

Rollbacks are uncommon but are a requirement in any SDLC. Fixing forward leads to more issues and an increase in downtime. For quick prototyping and no users that's fine, but for a production system this is a dealbreaker to us implementing Prisma as our ORM. While many can argue that reverting a commit in git is less used than simply committing new code, it still plays an integral part in the use of the system.

It may sound counterintuitive, but often the way to resolve this kind of situation is not by undoing the changes from the migrations but rather to fix the underlying issue, or disable the corresponding feature.

This definitely is counterintuitive as encouraging fixes against production leads to more downtime than a rollback where you can address the issue with more precision and intention.

Prisma seems to be growing in adoption and I would love to see it mature with the necessary tooling needed for a stable production system.

[schuhegger]

@albertoperdomo for us this feature is essential to be able to use Prisma not just for prototyping but also as stable production ORM. So far we love Prisma but unfortunately it would be a dealbreaker for us if we cannot roll back the database. Are there any news on this topic and is Prisma planning to integrate this feature soon?

[albertoperdomo]

@schuhegger Sorry, I no longer work at Prisma. cc @janpio

[janpio]

Here is our documentation on the current approach on down migrations and rollbacks: https://www.prisma.io/docs/guides/migrate/developing-with-prisma-migrate/generating-down-migrations

[orcioly]

Bom dia pessoal,

Verificando os comentários, seria bom se o Prisma V2 ou posterior quando surgir, ter a opção para reverter às última migrações. No modo de desenvolvimento se deletarmos o arquivo de migração e rodar o migrate dev resolveria o problema, porém, ficaríamos sem o histórico/versionamente dessa reversão.

Foi apenas meu ponto de vista e peço desculpas se eu estiver errado.

Grato,

[janpio]

Translation:

Good morning people,

Checking the comments, it would be nice if Prisma V2 or later when it comes out has the option to revert to the last migrations. In development mode if we delete the migration file and run migrate dev it would solve the problem, however, we would be without the history/version of this rollback.

It was just my point of view and I apologize if I'm wrong.

[RohitRox]

Any new news on this one?

[orcioly]

Good night,

Not. It's complicated because I've been enjoying Prisma, but I realize that I'm getting limited with some things. The difficulty of clearly documenting libraries to work with Prisma is precarious and unclear. I'm honestly thinking of removing Prisma from the project and implementing TypeORM. I have no choice because I have a deadline to meet.

[eddienubes]

It's definitely something you should consider to implement!

Hope you didn't forget about this thread.

[gabimor]

run into a similar issue with a "bad" migration that already tried to run on staging. I couldn't add a new migration to solve it cause it never got there (failing on the original bad migration).
also, deleting the migration from prisma/migrations didn't do the trick as the old one still tried to run when deploying!
this is what i did:

• turns out that failed migrations leave a record in dbmigration table. I deleted that record. (just one record for the one bad migration. you will see the error stored there as well)
• delete the migration file and folder from the prisma/migrations folder
• generate a new migration with @default() (this will require you to reset the development db, meaning running all the migrations again).

after a commit and a push (which runs the CI/CD and the migrations), it worked.
the "bad" migration was gone and the new one run adding the field with a default value

@josephJaoNasser

[josephJaoNasser]

I've been doing this as well. The problem with this is that, as you said, it resets the development DB and it deletes the data that's been stored locally.

Fortunately for me, I've been playing around with a staging DB deployed online as opposed to locally stored database files, so whenever I have a bad migration, my local DB is empty and I'd just reset it. I only use the prisma migrate dev to update my @prisma/client in the front end since I'm using typescript.

Other than that, it's a lot of extra steps, and lost testing data to fix a bad migration. Compare that to running a command in the cli, something like "prisma rollback dev <migration name>" //example only in case somebody decides to copy this thinking it's legit

[erikhuck]

+1 for being able to rollback all migrations in a single command similar to the db:migrate:undo:all in the sequelize package. I think prisma is overall better than sequelize but the ability to revert all migrations in a single command is very useful. This is what we'd like to do with our test database so we can use the same test database for multiple apps.

[erikhuck]

I've been able to accomplish this by executing psql -c 'DROP SCHEMA public CASCADE'. That has the same effect as reverting all migrations because it pretty much deletes all the tables. It would have been nice if prisma simply could revert all the migrations but this solution works well enough.

[khaelys]

So basically you would like to have a migrate reset that doesn't execute the migrations?

[abdulhanan001]

In my case I have delete a pervious migration and then run a command npx prisma migrate dev after that I

? We need to reset the "public" schema at "0.0.0.0:5432"
Do you want to continue? All data will be lost. › (y/N)```

Its really feeling worse to again restore whole  database dump just to rollback or delete one migration :( 

Any update!

[codePassion-dot]

it is not just about feeling clever using a library but being able to ship code quickly, hope this would be implemented in the future 😞

[sagardspeed2]

Same problem here,
Just revert one column name change, I need to re-dump very large data of the database it not making sense for this small changes.

This rollback feature needs to there for this work.

Eagerly waiting for this feature to introduced in prisma.

[dukejones]

It's been a mainstay in Ruby on Rails for many years, and one of the reasons that ActiveRecord is still the best ORM out there. I'd really like to get full end-to-end Typescript, but until Prisma is up to snuff in its change management, I can't really depend on it for managing large datasets in production.

[uki1014]

I often have to modify the migration content, e.g. by reviewing Pull Requests.
I have been using Ruby on Rails for about 4 years now, and in a development environment there are always cases where you have to go back and forth between multiple branches and run different migrations for each one.
The bin/rails db:rollback command has been a great tool in those cases.

Is there any reason why Prisma does not (or is difficult to) create a rollback command?

[suhjohn]

Seems like https://github.com/sequelize/sequelize supports migrate down, along with any other serious projects like RoR, Django, Go's sql-migrate https://sequelize.org/docs/v6/other-topics/migrations/#undoing-migrations
Note to anyone who's in the TS ecosystem looking for an actual ORM to use Sequelize. I shouldn't have touched Prisma listening to script kiddies on Reddit.

[suhjohn]

For those who use Postgres, I've created the following scripts that effectively automates the annoying parts of the library:

generate-migration.ts: https://gist.github.com/suhjohn/861367eeb0a50cbaba94becaadf4bf09
migrate-down.ts: https://gist.github.com/suhjohn/db1ffba13841dbd775fae83402d645d6

1. Auto-generation of down.sql migration along with the difference from the current database as suggested in this doc (https://www.prisma.io/docs/guides/migrate/developing-with-prisma-migrate/generating-down-migrations)
2. Execution of down migration and cleanup on _prisma_migrations.

This assumes the following folder structure:

Root
  -- prisma 
    -- migrations
    schema.prisma
  -- scripts
    -- generate-migration.ts
    -- migrate-down.ts
  package.json

Hope this is somewhat useful for anyone who find this thread

[formigarafa]

That looks nice. I will remember to give it a try if I need to go back to last Prism project I worked with.

[azwar]

Down migration is easy when we do with other library, so at this time, we use other library to manage db migration. Prisma is used only for query and schema definition. We hope Prisma team has attention about this. Thank you Prisma team.

[tony-engineering]

Hello,
I would like to create a generic job that automates database rollbacks. Ideally I would be able to take any point in the code history, and it would rollback the database schema to this state.

Ended up here after reading
documentation about down migrations.
and production troubleshooting

My conclusion is that Prisma doesn't handle the use case where we want to rollback a migration that did not fail.

Only workaround I can imagine, would be:

1. Force rollback using migrate db push from an older state of the code, or create a rollback.sql script with prisma migrate diff --from-schema-datasource prisma/schema.prisma --to-schema-datamodel prisma/schema.prisma --script > rollback.sql then apply it
2. After the "force rollback", if you check status prisma migrate status it will always tell you "Database schema is up to date !"
3. To fix that you then need to query the migrations list SELECT * FROM public._prisma_migrations ORDER BY migration_name ASC
4. Identify which migrations you rolled back with the rollback.sql script, then delete the corresponding migrations from the table

Alternative to steps 3. and 4. could be prisma migrate reset, but this would lead into a data loss.

At this point Im still unsure how I will implement that.

[jvolonda42]

Hello,

One of the most frustrating thing in this is that the documentation state that we should delete all created migration and create a new one, but this wipe the local database.

So when stating this:
Squashing migrations can be useful when developing with a branch-based workflow. During a large local development effort on a feature branch you might generate multiple migrations using migrate dev. After the feature is finished, the migration history might contain unnecessary intermediate steps that are unwanted in the final migration history that will be pushed to the main branch.
For prisma team every complicated feature should be done with a reset of local database?

This needs to be on the top priority.

Thanks for your efforts Prisma team. Your tool is great it just need some missing feature used on every production ORM.

[foloinfo]

I wanted something like rake db:rollback so I wrote a script. https://gist.github.com/foloinfo/942b8f2c59cb45c355529f68eb63521b
The idea is the same as the ts script from comment, but I'm using basic bash commands and only npx prisma. Now I can freely generate, migrate, rollback, rinse and repeat.

I've never used rollback on production (make a new migration to drop table or change column). So I agree with this comment.

...speaking with many developers that most of the time down migrations are not used.
In a production environment, rollbacks can lead to data loss.

However, I always use rollback in the development environment, maybe I want to tweak the name, made wrong table or something very trivial. Those are not failing migration, but I want to re-do it. It's pretty annoying if I need to reset the development db often.

Maybe this feature is not so important for production use cases, but I think this kind of feature is very important for improving the developer experience, which RoR (ActiveRecord) is very good at.

[jvolonda42]

thats what I ended up doing but I really think this should be integrated in prisma and not be done manually by the community.