mark a migration script as "post" code deploy, generate release scripts FOR and EXCLUDING "post" code migrations
We have to carefully create schema release scripts and coordinate them with code deploys in order to maintain compatibility, so we often have "pre" and "post" schema scripts. The "pre" scripts are run before code, and the "post" scripts are run after code.
In our cases, the 'post' deploy scripts are generally just cleanup of schema, removal of things that are no longer referenced by the code. This includes removal of any "backward compatibility" that is no longer needed. It could be dropping tables or columns, or removal of a column from the SELECT output of a stored procedure.
Currently we manage this by manually creating the two different release scripts, but I would love to see a way to have both a 'pre' and a 'post' code migration/schema script ready to go for a single deploy without having to manually create them. This is preventing us from automatically generating our SQL deploy scripts.
What if a naming convention or some other metadata on the migration script triggered it to be included (or not included) in a particular schema deploy script? So for instance, by default the system could work as it always does, but if a "post-deploy" mark was checked on a migration, then the normal generation of a deploy script would NOT include that migration. But if an additional parameter were supplied when generating the deploy script, then it would include the post deploy migration? That would allow us to easily generate both from our CI system.
Thunder
shared this idea
-
Timothy Batts
commented
I've noticed this with testing the Migrations v2. I do not agree with the concept of always executing all migration scripts first. It is possible that in a staging or production environment that several different changesets are included in a single release. There may have been changes in one minor release that affected an object, and then further changes later in another changeset that require a custom migration script. V1 was very good about determining order of operations based on the order the changes were checked in to source control. With v2 there is no option to determine order of operations, and the later migration script changes can be executed prior to earlier non-migration script changes being applied.
Yes, you can script pre-conditions so that the migration script only executes when the database schema is in the correct state, and can even script to create the necessary schema objects as part of your migration script. But we could do that 10 years ago already with a simple handwritten script.
A big part of the value of Red-Gate tools for us has always been the ability to reduce the manual work in scripting trivial items such as adding tables and columns, etc. and letting the comparison apis do that for us, focusing our attention on custom scripting that needs to be done to maintain the correct state of existing data. With the "execute first" mandate of the migrations v2, in my opinion this is a step backward.
-
Chattabaugh
commented
Having the ability to use post-deploy migration scripts could eliminate a lot of custom DDL scripting. For example: Adding a new table or column often requires seeding seeding with data. Pre-deploy scripts require adding the DDL changes to the script. Post deploy would only need the data changes.