in a web application, how do keep the database structure up to date?

Question

If your data change after the application is deployed, how do you keep the database up to date?

I mean, you can add or remove table, thats a simple task. Altering an existing table can be trivial too. But if you change the structure often, how do you keep that under a control?

I used to keep a table with a current database version in database. Then every upgrade was an SQL file that did its work - create new table, add a column or move the data. Files were named after those versions - so if my upgrade script got the database version 10, it just took all files from 11.sql to N.sql and applied each one of them incrementing database version number at the same time.

This seems to be working all right, but I'm wondering - what's your strategy for such tasks?
Also this system doesn't seem perfect if I normalize a table in one "patch" and after that I denormalize it again for whatever reasons. Then it's done twice.

But, writing a complete upgrade script each time I change something seems painful, and error-prone. At least more then such atomic changes.

Also, I can expect that diffrent customers have diffrent database versions running at any time, so I really should have a way to go up from any point.

Answer 1

Use a tool like RedGate's SQLCompare or xSQL Object from xSQL Software to generate your diff/delta T-SQL scripts on the fly.

You can even integrate it as part of your build process if you like.

For different customers with different databases, you simply have different reference databases for them that you compare against. Once you release an update to a customer, you update your own reference site with the same diff script.

That's it in a nutshell.

Answer 2

Personally I use a very similar process to what you have listed, I can see your argument about changing, but VERY rarely do I make a change, then change it back to the old way on a production site. In testing, yes, that happens, but in terms of an actual production site, I don't see it as a big deal.

Keeping individual version scripts, IMHO are not only good for deployment, but also good for providing items that can be checked into source control.

Answer 3

You should look into a tool called Powerdesigner. You can download a 15 day fully operational trial. It will help you model, keep changes up-to-date, etc.

It's a great tool for doing what you're asking for and a whole lot more.

Answer 4

We create a directory for each release within our version control repository. We wrote a script that reads the scripts in that folder and runs them in filename order (so we write scripts with names such as 32.0.0_AddColumnXxxxYyyy). This dotted format allows us to insert scripts into the sequence as needed.

When we update a site, the new scripts are detected and run. This has an advantage over a tool like SQL Compare (which I love dearly) because we can do one time modifications to data. However, we do run SQL Compare and SQL Data Compare (on selected tables) after the update to ensure that the procedure worked properly. After a successful run, the entire operation is committed and the database updates the "run scripts" information so these scripts are not run again.

The nice thing about doing it this way is that we can't "forget" a script. Additionally, when we attempt to roll to the testing or staging database, we often find hidden assumptions that the alternate datasets ferret out before it reaches production.

Another advantage to this is that we can keep various installations at different functionality levels for different customers, yet when we do upgrade, all the scripts are in place and ready to run. The only problem that I have had with this scheme is an "out of order" patch being applied to a user's database... you have to write your scripts to detect that the original state is as expected and to abort if not.

Answer 5

Many frameworks use the concept of "migrations" -- scripts that upgrade your database schema from one revision to the next higher revision. Likewise, the downgrade script is generally useful to have too, in case you need to back out a change. Sometimes these scripts are in SQL, sometimes they are abstracted (e.g. Ruby on Rails). You can design these scripts by hand or use a tool like SQL Compare that others have mentioned.

It's also helpful to create a table in your database with one column, one row, to indicate the schema revision. Some frameworks that support migrations rely on this to know which migration scripts to apply to upgrade or downgrade the schema.

Your application should have a suite of tests you can run to validate the application functionality. You could also add functional tests to this suite that inspect the schema and confirm the expected tables, columns, procedures, etc. exist. As you revise the project, revise the tests. Just as tests of application functionality must be tracked in source control along with the code they test, so must tests of schema structure be tracked.

Finally, the database design forms the foundation of your application design. Proper software engineering should result in a relatively stable database schema before you deploy. Subsequent changes to the database schema should be both small and infrequent.

Answer 6

I do it in much the same way as you. I keep a database Release Notes file that has all of the changes with my most recent at the top listed with the subversion revision number. It also contains the SQL that was run to apply this change.

In parallel I maintain a database model (I use Azzurri Clay in Eclipse) so that I can regenerate my model at any time. Any changes that are required, I first make in the model and then update my Release Notes. Azzurri can't generate ALTERations though only CREATEs.

This is all stored under subversion so that I can roll back when required. I probably should keep some sort of link between the svn revision of my app and the revision of my model.

Answer 7

firstly, we introduced a version table the schema that tracks the version number of the application for which the schema is set and we track the verison of each invidual table. we have a schema version we hard code into the application to check against this application version. We dont want the application accessing the wrong version of the DB. we then have a set of scripts for each table that migrates from the previous table version to the current version. we then have a target table that we embed in the application to know what version of each table is expected in the new version to see if we match everything. if not, we apply the various migration scripts to the schema to get the db up to snuff.

complicated? somewhat. life saving. abosolutely. nothing like chasing erros in an app because the schema is wrong.

Answer 8

You should probably read the aticle that Atwood wrote on DB version control on coding horror a while back: Is Your Database Under Version Control?