How should the database schema be migrated?

Question

I am using a bunch of freelance programmers to develop a web application. We are in the early stages of development, meaning that we are making many changes to our database schema. We have one test database and one production database, we also have a replica of the live database, which gets a clone of the live database every night (mostly used to make it easier to recreate bugs found in the live web application. Since I use many freelancers, which I do not know, I often do not trust their database skills and they do not have any understanding of the outmost importance of the data in our live database.

My question is what kind of process you would recommend after a freelancer determines the need to make a change in a database. They will currently commit change to our database schema together with their pull request (on GitHub), so I can review their proposed database changes. They are also free to apply the changes to our live database. My question is then how I should migrate the live data to the new schema. I thought about asking the developer of a patch to also create an alter table statement, which I would run on the live database, but I do not trust that they will be careful enough. I prefer such migration to be automatic (at least in most cases) in order to be able to deploy on a daily basis, but I do not know whether that is at all realistic. I also need to have a process that I trust so much that I do not need to make any tests on the live system if I have tested a pull request on our test system (working with our test database).

How can this be done?

Answer 1

If you don't trust the developers, I don't see how you can let them make changes to the live database.

Have them change the snapshot database, review and test that, and then you apply the changes to the production database.

Also, it might be worth considering whether you could reduce the number of changes you have to make to the database schema with a little bit better planning, or even whether it is really necessary to deploy to the live site daily.

Answer 2

This sounds like you could benefit from a separation of architecture, system design, user experience and engineering.

The Architects provide general guidance for the tech solution, such as "we'll use a .NET SOAP Web Service here" and "let's use an off the shelf application for this."

The System Design team takes the guidance from the Architects and creates a concrete tech solutions:

Create a Products web service:

• WCF, .NET 4.5
• Need a "products" table with columns x, y and z
• Authentication is handled by Foo

Then you engage User Experience to design the User Interface.

Finally, you hand all this information and visual designs to the untrusted freelancer Engineers who put fingers to keyboard and make this happen.

In this way, the Engineers aren't making design and architectural decisions --- which is not to say they shouldn't be allowed do make these decisions. If an Engineer comes up with a problem that System Design or User Experience didn't account for, they need to talk with the lead developer (you). From there you will need to grab the developer and chat with System Design or UX to hash out the solution, which the engineer then implements.

You might not have individual people for each role. Smaller software shops will probably have people wearing more than one hat (architect, system design and lead developer might be one person), but clearly defining those roles and who fills them can help manage a disjointed engineering workforce.

Answer 3

There are Database Wizard applications that they could probably help you to automate the process; However the general process of upgrading database should be like this:

First you need an Upgrade script, where you can safely upgrade your database Schema and Data from version 2.8.13 to 2.8.14, for example.

The script should be totally SQL -- ideally, and should carefully Select the existing data in your database, Create a new table or Modify the existing ones, Modify the existing data in order to fit the new column types, etc. if necessary, and then Insert the data into the new table.

Then, obviously, creating a back-up/snapshot of the database is a must before applying any changes on the production server.

This should make the upgrades on the production server much easier, if you constantly upgrade the database and don't dismiss any of the upgrade scripts, otherwise upgrading a 2.8.13 to 2.9.3 might be a bit of extra work. The upgrade script could be manually written by your developers/DBA, or be an auto-generated script via a third-party tool. Either way you need to manually check it out to make sure it won't break anything and apply it on the Staging environment before the actual Production database.

Note: Your database version probably won't match your application version. So you don't need to have an upgrade script based on your application version. You only need it when the database schema has been modified.