Add Pre-compare Script Option for DACPAC Publishing

[dzsquared]

https://developercommunity.visualstudio.com/t/Add-Pre-compare-Script-Option-for-DACPAC/10594162

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

[tony-donley]

Thanks Drew! :-)

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

It would also be nice to flag that 'pre-compare' script as 'One Time' or 'Every Time'.

'One Time' would be used for most advanced schema changes and data transformation, where the script should only execute once per target database and then be archived, not execute at every deployment.

Would that require something like a "schema version" mark in the target database to know if that script has executed or not? Or would we just have to write our scripts to be stateless by reading the schema metadata and react conditionally?

'Every Time might do things like:

• set a maintenance flag earlier than the pre-deploy
• make temporary permission changes that are later reverted in the generated script or in post-deploy

[biltongza]

Chiming in here with my two cents, I love DACPACs for the mental model of "this is what my database should look like, make it happen" but this is an area where the experience could be better. As mentioned, sometimes there are things that I simply don't want the DACPAC to do, either because it can't (the non nullable column is an example) but also sometimes because the approach it takes to a change is not ideal and I want more control over it.

The other part of the experience that is not ideal, is that there is sometimes data that I want source controlled. For example static/seed data.

For this reason, I've landed up using something like DbUp twice: once as a pre-deployment step before the DACPAC, and once as a post-deployment step after the DACPAC. I use the pre-deployment for any changes like adding non nullable columns or anything else I don't want the DACPAC to change, and then the post-deployment for things like data.

I pick DbUp because it makes the experience much smoother, I don't need to focus as much on writing my scripts in an idempotent way because it uses a journaling table to know what has and hasn't been run yet.

It would be lovely if the DACPAC could handle this kind of scenario.