Recently I have been digging into an open systems tool for managing database changes. The tool is called Fluent Migrator. It brings a lot of flexibility that may be of use to you in different scenarios.
Fluent Migrator operates against many different data engines both SQL and NoSql. Because it uses a fluent syntax, it allows you to execute the same script against these different engines, translating the requested modifications from the fluent syntax to the commands native to the various engines.
This means you could write a fluent script to create a table, modify a table or even manipulate the data a table contains. Then, once the script is complete, it can be executed against any number of data engines without modifying the script.
What about capabilities to a specific data engine itself? I don’t know about all the engines supported, but the fluent migrator engine has extensions specific to SQL Server as one of the most popular data engines allowing you to perform nearly any database action necessary. Additionally, you can execute TSQL embedded in your script, or even execute database commands found in a file. There is very little that is not exposed to you in some form or another.
One additonal feature of Fluent Migrator that I found most compelling is that you can configure the scripts so that it tracks the history of their applicaiton to a data store, resulting in a run once scenario. It maintains a table in the modified database with a history of executed scripts. This change tracking is all built into Fluent Migrator so that you don’t have to roll your own, or have concern for if it has been applied.
A Fluent Migrator script is considered a unit of work. So, if you have multiple things such as adding a column to a table, and then populating that column based on other data, it could all be written in a single script thus encapsulating a single unit of work.
Take a look at fluent migrator. It’s available on GitHub, and may be downloaded through NuGet as well.
Cheers,
Ben