search

rubenv / sql-migrate

SQL schema migration tool for Go.
MIT License
3.16k stars 269 forks source link

[Proposal] Addition of 'reset' to sql-migrate command #248

Open hirokinakano opened 1 year ago

hirokinakano commented 1 year ago

TL;DR

I want to add a command that resets and redoes the application of all migration files.

For instance, it would be convenient to be able to revert all DB changes applied in the project with migration files just by executing the command like the following:

sql-migrate reset -env=development

Context

There are times when I want to reset all migration files in the development or local development environment. For example, in the following cases, there are about 100 migration files, but resetting them with the redo command by specifying 100 as an argument is cumbersome.

+-------------------------------------------------------------+-------------------------------+
|                          MIGRATION                          |            APPLIED            |
+-------------------------------------------------------------+-------------------------------+
| 001-***.sql                                                 | 2020-09-07 02:04:54 +0000 UTC |
| 002-***.sql                                                 | 2020-09-07 02:04:55 +0000 UTC |
| 003-***.sql                                                 | 2020-09-07 02:04:55 +0000 UTC |
| 004-***.sql                                                 | 2020-09-07 02:04:55 +0000 UTC |
| 005-***.sql                                                 | 2020-09-07 02:04:55 +0000 UTC |
| 006-***.sql                                                 | 2020-09-07 02:04:55 +0000 UTC |
| 007-***.sql                                                 | 2020-09-07 02:04:55 +0000 UTC |
| 008-***.sql                                                 | 2020-09-07 02:04:55 +0000 UTC |
| 009-***.sql                                                 | 2020-09-18 02:53:01 +0000 UTC |
| 010-***.sql                                                 | 2020-09-18 07:11:46 +0000 UTC |
| 011-***.sql                                                 | 2020-09-18 07:11:46 +0000 UTC |
| 012-***.sql                                                 | 2020-09-23 02:01:55 +0000 UTC |
| 013-***.sql                                                 | 2020-10-02 05:14:37 +0000 UTC |
| 014-***.sql                                                 | 2020-10-06 07:10:07 +0000 UTC |
| 015-***.sql                                                 | 2020-10-14 02:52:58 +0000 UTC |
| 016-***.sql                                                 | 2020-10-14 02:52:59 +0000 UTC |
| 017-***.sql                                                 | 2020-10-15 10:36:30 +0000 UTC |
| 018-***.sql                                                 | 2020-10-18 14:20:19 +0000 UTC |
| 019-***.sql                                                 | 2020-10-20 07:12:12 +0000 UTC |
| 020-***.sql                                                 | 2020-10-23 09:09:09 +0000 UTC |

and more...

Especially in the local development environment, the motivation to reset the migration, reapply the new migration, and rebuild the table occurs regularly. Therefore, it would be more efficient to proceed with development if it can be completed with a single operation of a command like "reset".

When executing the reset command, all tables will be dropped and all migration files under the project will be re-executed.

Caution

If you enable reset in the production environment, it is expected that accidents where all data in production is deleted could occur. Therefore, an error will be triggered when executing in the production environment. It will be necessary to add processing that allows the reset command to be executed in the production environment by passing flags or other parameters to the command.

rubenv commented 1 year ago

I'd accept a reset command that does what you describe.

The same caution applies to using down, can't really protect against operator error I'm afraid, so not sure if we need that.

hirokinakano commented 1 year ago

@rubenv Thanks for accepting my suggestion, I will create and send you an additional PR for the reset command.

The same caution applies to using down, can't really protect against operator error I'm afraid, so not sure if we need that

It is true that the same problem can occur in the down command and in the reset command, which I will add this time. Is it ok if I don't have to take that into account once in the reset command as well?

rubenv commented 1 year ago

Yup, people click past any warnings anyway and anything interactive will break scripting.