How To Manage Database Schema Changes

Goal: Version database schema changes alongside the code that requires them. Make both deploy and rollback easier.

Steps

1. Accomplish the goal Ensure SQL solution folder exists
2. Create two SQL scripts for each feature or story you're working on: {story}-UP.sql and {story}-DOWN.sql.
3. Write the one schema change into the -UP.sql script. Don't try to get your entire final schema in one step; just do one change.
4. Apply the -UP.sql script to your database.
5. Manually verify the resulting schema is correct.
6. Implement as much of the code change as you can with just the amount of schema change you've implemented so far.
7. Use the -UP.sql script as a guide to write the -DOWN.sql script.
8. Apply the -DOWN.sql script to your database.
9. Manually verify that you are back to the original schema.
10. Commit code and scripts to source control.
11. Does your -UP.sql script get to the whole final schema you want?

    No:

    1. Repeat from step 2. You will modify/extend the same script files.

12. Done!

Ensure SQL solution folder exists

1. In your solution folder, create a physical directory named SQL.
2. In Visual Studio, add the newly created SQL directory as a Solution Folder.
3. Done!