0

I have a simple question (request?) which I have looked all over and really cannot find an answer to. As part of my routine workflow I will want to be able to test a migration on a staging server without committing it. That means running the migration within a transaction and doing everything that would be done normally exactly the same as on the PROD env except at the very end doing a ROLLBACK; instead of a COMMIT; (in Postgres terms).

I've only found more-or-less workaround recommendations to this. But I want/need a CLI option that basically allows me to switch between testing and committing without making file changes. Does this exist and I am just missing it?

One simple scenario that shows my use case (though I am very surprised that it isn't a basic feature): I make changes to schema/code on DEV (where there is not much data load), run tests and everything looks good. Then run the same migration on STAGING, run the same tests and find that the changes are NOT good because of performance issues that only appear on STAGING. So I need to go back to DEV and make more changes and repeat the process before actually committing my code to PROD. And at the end of all that, I would then validate my "undo" scripts.

If each time I had to rebuild my staging server (several TB) because the code was committed... that would be very silly. And if my only work around to not automatically committing migrations is this then that is frustrating because it requires me to make code changes to switch "testing" on and off. >>> There should be a CLI option.

Any feedback or help is greatly appreciated. I am trying to get my team moved over to Flyway!

Alexi Theodore
  • 1,177
  • 10
  • 16

1 Answers1

1

I'm not sure that this is a simple question.

As long as the RDBMS you use supports DDL transactions, it is very simple to add tests to a Flyway migration and roll back the migration if these fail. All you need to do is to throw a SQL Exception if a test is failed, using RAISERROR (SQL Server), RAISE_ERROR (DB2 and Derby) or RAISE EXCEPTION (PostgreSQL). This is detected by Flyway, and will result in the migration being rolled back.

I'm not entirely sure that this is what you want. If you are suggesting keeping the transaction open indefinitely while you do exploratory tests, I cannot think of a way of doing this in any other way than the method I suggest in the article you quoted.

It is possible to use an AfterMigrate callback together with a placeholder to cause a migration to roll back from the commandline even if it succeeds. You can therefore prepare and run tests as part of the migration and cause the migration to roll back whatever the results of the test. I can provide details of how to do this if it helps

Phil Factor
  • 41
  • 2
  • Thanks for the recommendations, however no that isn't what I was looking for. I think you might be assuming I'm looking for something more complicated than I am. I'm simply looking for a "test" mode where a migration is attempted, but not committed. Literally the only code difference (assuming a Postgres context as example) would be at the very end, rather than submitting all prior changes with a `COMMIT;` it would be `ROLLBACK;` - thats literally it. This would achieve testing a migration without committing it. And that is NOT the same thing as committing a migration and then rolling it back. – Alexi Theodore Aug 31 '22 at 17:10
  • Sorry, but I'm not suggesting the committing of a transaction and then rolling it back. I'm suggesting that the migration is run but not committed. The exception merely tells Flyway to roll the migration back, but if it is at the end of the migration then the entire migration will have been successfully run but not yet committed. You will see by the message you attach to the exception that it was so. Why not just take an existing migration that you have, and just add the RAISE EXCEPTION code and see what happens? Flyway will tell you what it does, especially in verbose mode. – Phil Factor Sep 01 '22 at 16:59
  • I tried it already, and yes it does technically produce the same result in the end. But its a bit of a hack. I have to edit the code to implement this (there are some other ways, but ultimately they all require that the error-throwing mechanism be in code). I just wish there was a simple CLI flag. Thanks for your help though! – Alexi Theodore Sep 01 '22 at 18:07
  • Here is a more thorough description of the technique https://www.red-gate.com/hub/product-learning/flyway/try-before-you-commit-in-flyway – Phil Factor Sep 21 '22 at 13:06
  • Thank you! That was indeed very informative... – Alexi Theodore Sep 26 '22 at 16:22