2

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)

Improve this question
9
  • Possible duplicate of Database source control
    – gnat
    Sep 14, 2016 at 5:57
  • 1
    @gnat: instead of focussing on finding something which looks like a dupe as quickly as possible, you might also focus on what is the actual question, and ask yourself if can the title be changed to match that question.
    – Doc Brown
    Sep 14, 2016 at 6:05
  • @DocBrown this answer in duplicate addresses what is asked here. Specifically, Flyway does (I didn't check many other answers, maybe these cover it too)
    – gnat
    Sep 14, 2016 at 6:09
  • @gnat: Flyway is a way of improving the speed of laravel migrations? Sure? This is far from beeing obvious. However, that other question is obviously not what is asked here. The OP already knows how to put his database under source control .
    – Doc Brown
    Sep 14, 2016 at 6:11
  • @DocBrown per my reading neither title nor tags indicate that the question is laravel specific
    – gnat
    Sep 14, 2016 at 6:14

2 Answers 2

Reset to default
3

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.

Improve this answer
4
  • Your suggestion solve the problem when you are migrating the project to an empty server. However, there are customer coming back few years later, paying for an upgrade. There are already data in the database so it is a must to alter table instead of building a new one.
    – cytsunny
    Sep 14, 2016 at 7:12
  • @cytsunny: ok, but that is a different question which has not much to do with version control. If you have a customer coming back "a few years later, paying for an upgrade", it sounds you have to migrate their database just once, and if they have to wait for some hours for the migration, then it might be probably acceptable. Or do you have a different scenario in mind?
    – Doc Brown
    Sep 14, 2016 at 8:08
  • I understand that not much can be done, but this is exactly the scenario that makes me asking this question. We need to deploy the project to the customer's server as the customer don't want to pay us for hosting. They also refuse to provide another server for migration, so that hour waiting for migration means they need to stop using the system for same amount of time. Currently the time is still acceptable as it is just around 10 mins (sounds not long, but remember it is just the structure of database), but the time will keep on growing.
    – cytsunny
    Sep 14, 2016 at 8:30
  • 2
    @cytsunny: sounds you are overestimating the problem a bit. Each migration step for one production DB you will have to lift in the future will have to be executed just once (if no errors occur, of course), so if for example the customer you mentioned will be migrated again in the future to a newer version, the migration steps already applied won't be executed again. I recommend you start only to optimize when you have real, recurring performance problems.
    – Doc Brown
    Sep 14, 2016 at 9:03
-1

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.

Improve this answer
2
  • First, I am just a developer and stabilizing the design is out of my reach. Second, I think our team is doing quite well in stabilizing the design. It is just too many new version in the pass 5 years. (One version fits one customer for their custom function.) Third, at least give concrete solution if you think stabilizing the design is the solution? Simply pointing that there are methods is not enough, so sorry I must down-vote this answer.
    – cytsunny
    Sep 15, 2016 at 2:55
  • @cytsunny If you are adding and removing or worse yet removing and adding back columns when upgrading multiple releases, I don't believe your team is following the Open/Closed principle. What worries me most is renaming columns and tables. Dropping tables and columns comes a close second.
    – BillThor
    Sep 15, 2016 at 4:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.