Take the 2-minute tour ×
Webmasters Stack Exchange is a question and answer site for pro webmasters. It's 100% free, no registration required.

For a while now I've been keeping a website I'm developing in the standard dev/production phases. Its been pretty simple: Mercurial repo for dev, repo for production. Do work in dev, get approved, push to production.

But now I'm trying to apply this process to a new website that has a database and am struggling on how to figure out a development strategy. What I didn't mention above is that I do all my work on my own repo, push it to dev, then later push it to production, so its 3 different servers. So how do I manage my database?

The obvious solution of mysqldump every commit isn't going to happen, and a dump at the end of the day isn't all that helpful when you want to undo later one change that happened in the middle of the day.

What is the best way to accomplish this?

share|improve this question
add comment

3 Answers

The solution actually may depend on database used.

But generally, keep three servers (dev, test, prod), and work separate structure dump from data dump. You can keep versioning track of structure through the generated SQL files.

Well, I've seems this process being used for many medium/big projects, and I recommend it.

share|improve this answer
add comment

Most revision control systems are designed for source control. They're not designed for versioning other sorts of data, like binary data or databases.

In most cases, there isn't really a reason to version your data--not for development anyway. Generally, your source code ought not to rely on a particular data state.

Usually, what's versioned is the schema (and maybe stored procedures). And since the only way this can be meaningfully done is for the diff engine to contain a full DDL parser, the revision control system is usually tied to the data modeling software you're using.

But since that type of software can be quite expensive, there are also other options. For instance, you could simply version a schema dump or a database creation/initialization script. Alternatively, you could manually write upgrade scripts to upgrade from one version of the database to another. This can be a pain, but that's why you should try to get your data model correct from the start. If you're constantly changing your database schema from one revision to the next, then you're probably doing something wrong.

As for the data, I usually just store test data separately in a CSV file or something. And that can typically be imported automatically through the application, which will handle the conversion process to whatever database schema it's using.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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