What is the best website/web-app upgrade process

Question

We have a great process for upgrading our clients' websites as far as updating html/js code and assets is concerned (by using Subversion) that we are very happy with.

However, when it comes to upgrading databases, we are without any formal process.

If we add new tables/fields to our development database, when it comes to rolling it out to the production server we have to remember our changes and replicate them. We cannot simply copy the development database on top of the production database as client data would be lost (e.g. blog posts, account info etc).

We are also now in the process of building a web-app which is going to come across the same issues.

Does anyone have a solution that makes this process easier and less prone to error? How do big web-apps get round the problem?

Thanks.

Answer 1

In my opinion your code should always be able to create your database from scratch, therefore it should also handle upgrades too. It should check a field in the database to see what version the schema is at and handle the upgrades to the latest version.

Answer 2

I had some good luck with: http://anantgarg.com/2009/04/22/bulletproof-subversion-web-workflow/

The author has a database versioning workflow (with PHP script), which is decent.

Answer 3

Some frameworks have tools which deal with the database upgrade. For example rails migrations are pretty nice. If no convenient tool is available for your platform you could try scripting modifications to your development database.

In my company we use this model for some of our largest projects: If the X is the just deployed version of our application and it's not different then the latest development version. We create a new directory for the scripts naming it for example - version x + 1 and add it to the subversion repository. When developer wants to make modification to the development database, he creates the .sql script with a name "1 - does something.sql" that makes the modifications (they must be indestructible), saves it and then runs it on the development database. He commits the web app code and the sql scripts. Each developer does the same and maintains the order of the execution of scripts. When we need to deploy the version X+1 - we copy the x+1 web app code and the scripts to the production server, we backup the database, run the sql scripts one by one on the production database and deploy the new web application code.

After that we open a new (x + 2) sql script directory and repeat the proces ...

Answer 4

I think that adding controls to the development process is paramount. At one of my past jobs, we had to script out all database changes. These scripts were then passed to the DBA with instructions on what environment to deploy them in. At the end of the day, you can implement technical solutions, but if the project is properly documented (IF!!!) then when it comes time for deployment, the developers should remember to migrate scripts, along with code files. My $.02

Answer 5

We basically have a similar approach as Senad, we maintain a changes.sql file in our repo that developers put their changes in. When we deploy to production, we:

Run a test deployment to the QA server:

• first reproduce the production environment (app & db) in the QA server
• run changes.sql against the qa db
• deploy the app to qa
• run integration tests.

When we are sure the app runs fine in qa with the scripted changes to the db (ie. nobody forgot to include their db changes in the changes.sql, or references, etc.) we:

• backup the production database
• run the scripts in the changes.sql file against the production db
• deploy the app
• clear the changes.sql file

All the deployment is run through automated scripts so we now we can reproduce it.

Hope this help

Answer 6

We have folder migrations/ inside almost every project and tehere are so called, "up" and "down" scripts (sql). Every developer is obliged to write his own up/down script and to verify it against testing environment.

There are other tools and frameworks for migrations, but we haven't got the time to test it...

Some are: DoctrineDB, rails migrations, propel (I think...), capistrano can do it also..