There is one major process that your team is likely ignoring that holds a large stake in your application, can you guess what it is?
Did I hear someone say Database migration woop woop top of the class!
More often than not, we properly version code but deploy the database as one big sql dump. Practically all teams I have interacted with during deployment present a .sql file with instructions that Mysqldump or a similar tool should be put to employ.
The biggest disadvantage to this method is that the database and the application don’t remain in sync for the various versions available. At first glance this may not seem like that much of an issue since most of the time we are only concerned about the final version of the db. However when you think about it you realize that if the version of db you have is only the latest, then earlier versions of the code are practically unusable since they work with the implicit assumption of how the schema of the database is structured.
Application <-> Database mismatch maybe your biggest problem but other problems quickly become apparent when you deploy and need to scale your app. Since the entire db is just one big dump, you can not easily automate the task of scaling it over the cloud.
Well now that we have talked of the evils of the dump, what can we do?
The easiest option is to store diffs of the database. Here I don’t mean in the sense that git does but rather storing ALTER scripts in an orderly incremental manner in a folder called migrations. Sample contents would be:
And when say we alter the users table then:
We then commit the files to our code repository of choice.
This ensures that at whatever point you checkout your code, you can also be able to generate the necessary schema.
For those who work with frameworks, your framework probably ships with a schema builder in one form or another. For those cases you may wish to version the schema files rather that the sql file.
For those of us who prefer the db to be framework agnostic, the following tools can be a great help to help you run your diffs
How do you manage database migrations within your own team? Tell us in the comment section below.