Add Pre-compare Script Option for DACPAC Publishing #482
Description
Bringing this item over from VS Developer Community since the core implementation would be in Microsoft.Build.Sql and DacFx.
Often times there are updates and revisions that need to be made to a database that cannot be automatically handled by a deployment script generated by a DACPAC, such as adding new non-nullable columns to a table or data migrations that must happen because a column or table is being dropped.
The first instinct is to add a pre-deployment script with the code necessary to execute changes so that the DACPAC will publish correctly, but this fails because the pre-deployment script is run AFTER the comparison takes place to generate the deployment script. This has been a known source of confusion for at least 15 years (https://learn.microsoft.com/en-us/archive/blogs/gertd/pre-deployment-scripts) and there are still questions about it appearing on places like Stack Overflow and even issues being reported in the community feedback (https://developercommunity.visualstudio.com/t/SQL-database-project-pre-deployment-scri/224190?ref=native&refTime=1708466421969&refUserId=fd449ac5-d5e8-690b-8ba8-adeb29ac3e19).
All of the workarounds require manually writing a change script to run before a DACPAC is published, which is fine, but then involve a variety of approaches to manage running the script outside of Visual Studio or the normal publishing process, or splitting known changes into separate source code updates that are checked in and published in stages, and so on. While these are all solutions that technically work, they are arguably confusing and convoluted.
Would it be possible to add an additional feature to run a “pre-compare” script during publishing? This way users could include code for any changes they want to take place before a DACPAC performs a comparison. This would also enable developers to include this script as part of a .sqlproj, and have it automatically be handled as part of the normal publish process.
The expected workflow would be:
Run pre-compare script
Compare and generate deployment script
Run pre-deployment script
Run deployment script
Run post-deployment script