search

dataplat / dbops

⚙ dbops - Powershell module that provides continuous database deployments on any scale
MIT License
155 stars 39 forks source link

Repeatable migrations #123

Open lowlydba opened 3 years ago

lowlydba commented 3 years ago

Being able to specify a script (via filename or special directory) that gets re-applied whenever the hash changes. It would be dependent on #120 getting done (adding file hashes), but IMO would bridge the feature parity gap between Flyway and Dbops for 99% of use cases.

Thanks again for a great tool! I'd be happy to help do some work towards this if needed.

nvarscar commented 3 years ago

Now that I have a working example of an enhanced table, I would like to get more input on how you envision this working.

Currently, there is already an option to run a modified script if you're using DBOps packages, which store hashes internally and are able to distinct if a script on a filesystem is different from how it was imported into a build previously.

I imagine that you're talking about achieving similar functionality in Install-DBOScript without having to build packages - by comparing every script's hash with a recorded hash from when that particular script was previously executed. Is that right?

lowlydba commented 3 years ago

That's correct. In our setup we aren't quite able to utilize the full fledged CI/CD workflow currently, so we're just relying on the Install-DboScript command to do migrations.

Comparing the hashes would make it a lot easier to have a View definition live in a single script, versus having to create a new migration script for it per change when using Install-DboScript. I'm not sure if it makes more sense to just apply this logic to all scripts, or only apply it to a folder like PreScripts.

This may be a bit of an edge case, so thanks for looking into it!

nvarscar commented 3 years ago

The more I look at it, the more I think that this functionality would benefit the module greatly, perhaps offering a third potential pipeline scenario that supports modified files.

It seems, however, that it's not going to be a quick win. The only validation DbUp UpgradeEngine class does is script name validation, and the overrides might not be so easy to implement. But I'll give it an honest attempt at the very least.