When working with databases, it’s best practice to create migration scripts to which apply your database changes, rather than using GUI/designer tools which need you to manually apply changes across multiple environments.
I’ve been working with a client where there isn’t a specific migration tool to run all these migration files at the end of project, but they need to be bundled into a single file and attached to the change tracking system.
I keep these files under version control and prefix migration files with
M_ and rollback files with
In Bash concatenating these files is simple:
cat M_* > ./migration.sql
In Powershell it isn’t too different:
Get-Content M_* | Set-Content .\migration.sql
When dealing with rollback scripts I create the rollback which opposes the migration with the same number which makes the files easy to navigate:
Rollback scripts are slightly more interesting as they need to be run in reverse.
We can also achieve this in Powershell with a minor tweak to our previous command:
Get-Content R_* | Sort-Object -Property Name -Descending | Get-Content | Set-Content .\migration.sql
This will firstly get all our rollback files and then reverses the order, then gets the content of each file and writes it to a single file.
Although I prefer doing these types of tasks with Bash, it’s still quite easy to remember the Powershell commands if you don’t have access to Bash on a Windows system.