I am working with a team of web developers. We are already using Git for version control of our code and it works well. However, while we are changing our code, it is also common to change the database structure, adding / deleting / renaming columns and tables. The normal answer to that is migration files, and we are already using the migration function of laravel.
Soon, we find that some old project takes a long time in running the migration file. This is mainly because the same column was renamed a number of times. Some columns that no longer exists in the latest version are still added and then deleted when running the migration file.
Is there a way to do database version control in a better way? (We are using MySQL)
9
Your version control strategy seems fine, you just need a performance optimization for your migrations. You wrote
Soon we find that some old project takes long time in running the migration file
But why do you have to run the migrations more than once? Once the migrations are done to a specific intermediate version of the db schema of your dev database, make a full database dump and put that dump under source control as well. Whenever you have to run newer migrations later again, do not start from “zero”, start from that intermediate version.
4
It sounds like you have a stability issue in your database design. Work on stabilizing the design. Data tends to stick around for years. Your database version control seems to be working, but appears to dealing with an overly volatile database design.
The database is your persistence layer. If you are frequently deleting tables and columns, you don’t seem to have a good handle on what you need to persist. In years of practice, I don’t recall needing to rename a column that had made it into production. Even adding tables and columns is relatively infrequent.
Consider applying the Open/Closed principle to your database design. This may require you to spend a little more time clarifying your requirements.
For most system I have worked with a new version of code comes with an update script which will make the necessary modifications for that release. That may include adding tables, columns and data, modify existing columns and data, and rarely deleted columns or tables.
There are methods that allow you to design the changes so that they are compatible with the existing release. Most of them are well covered by the Open/Closed principle. (Don’t modify columns in ways that will break existing code. Provide a defaulting mechanism for new columns that need to be populated value.)
You haven’t provided enough detail to provide a solution for your application. I’ve built systems with revisions to the production database every month. You need to analyze how the changes will work with the existing and new version of the code. You can rarely just drop in a new database like a new set of code.
There are many open source projects back by databases that deal with the same issues. The ones I’ve used always have upgrade scripts, and often have downgrade scripts. The script usually only cover one version. The code running the upgrade will apply all the upgrade or downgrade scripts required to get from one version to the next. Having downgrade scripts is great when you need to debug an issue that may have been introduced in a prior release.
While it is possible to have upgrade/downgrade scripts that cover multiple releases, the testing effort is likely to outweigh the benefit. If you’ve followed the Open/Closed principle, it should be rare that you are applying and undoing changes when upgrading or downgrading multiple releases.
There are tools which will generate the schema changes between the current schema and the desired schema. They still leave you with the issue of and data updates that are required. Making sure you get it right when columns and tables may have be updated, renamed and updated again will be very difficult.
2