Today I want to introduce what I think may be a new term…Database Migration First Development.
Most database developers use a number of different techniques to make changes to the next version of a database. This includes the very first instance of a database, as well as making modifications to an existing database. Here are some processes used in the Microsoft world.
- Using a database modeling tool, a database diagram is created. The diagram is compared to an existing database, empty or populated, and scripts are generated to alter the database so that it conforms to the new model. In this case, the scripts can be saved as files, and modified for edge cases
- Using Entity Framework, changes are made to the EF models, and database change scripts are generated by comparing the previous version to the new version of the models. In this case, the change code is created as class objects, and can be modified to handle edge cases.
- Using a Visual Studio database project, all objects are maintained in SQL syntax. During deployment, changes are compared to a database and alterations are scripted and applied. In this case, table modifications are all auto generated. If you want to use the tool as it is designed, you cannot modify intermediate script files. You have to know how and what it will do automatically, and code around edge cases. This is very painful, when edge cases occur
I have found that it is easier to turn this whole process on its head. Modeling tools easily synchronize from a database that has changed externally. Entity Framework models can be updated using T4 templates. Database projects can also be easily updated by comparing it to an existing database. So, my solution has started with making the database change first.
My preferred tool is a Dot Net project using Fluent Migrator library. If I want to add a column to a table, I write a migration to perform that task. I then run that migration on my design database. Then, I can synchronize that change into any of my other tools. I write the change once, synchronize with my external tools, and have code that is ready to deploy whenever I release my code. I never miss a database change. My database design and code is always synchronized with my application code.
There’s some food for thought…maybe you might consider using Database Migration First Development.
Cheers,
Ben