How decently track the database structure modifica

Question

The main problem is the versioning of the database structure.

The standard mysqldump and pg_dump utilities do not produce very well suited files for versioning.

The dump commands produces the dump files with autoincrement values, TOC entries and so on. Since these objects is subject to continuous changes it always produces the huge difference files.

PostgreSQL Diff

 --
--- TOC entry 2630 (class 0 OID 0)
+-- TOC entry 2549 (class 0 OID 0)
 -- Dependencies: 6
 -- Name: SCHEMA adm; Type: COMMENT; Schema: -; Owner: admin
@@ -61,5 +61,5 @@

MySQL Diff

--- Dump completed on 2010-07-20 14:33:44
+-- Dump completed on 2010-08-11  8:59:39
Index: /db.sql
===================================================================
--- /db.sql (revision 1274)
+++ /db.sql (revision 1317)
@@ -36,5 +36,5 @@
   `message` text,
   PRIMARY KEY  (`id`)
-) ENGINE=MyISAM AUTO_INCREMENT=21122 DEFAULT CHARSET=utf8;
+) ENGINE=MyISAM AUTO_INCREMENT=23730 DEFAULT CHARSET=utf8;

Any suggestions/links/utilities on better way of version control are appreciated!

Thank you.

Answer 1

I wouldn't use MySQL dumps because they are mostly used for data backup, and you usually don't use version control to manage data backups. Instead, I would just version control the install script or the SQL file used to set up the initial database structure.

For small projects, I usually just have a file called install.sql which contains all of my CREATE statements and schema.txt which describes the schema. For larger projects, you might want to use something like dbForge, which allows for database schema versioning in the professional edition, though it's a bit pricey if that's all you're using it for.

Check out this article on Coding Horror (especially the first link in that post) for more guidance.

Answer 2

Depesz recently wrote a blog post on "HOW TO MANAGE CHANGES TO YOUR DATABASE?"

I'd say:

• If you simply store each object's schema in SVN, you still need to deploy changes with ordering dependencies and data modifications, so all that really buys you in itself is categorising your history of changes to the objects involved.
• Write scripts to perform all your changes, including scripts to undo your changes.
• Use apgdiff to produce (PostgreSQL) database schema diffs.

Answer 3

You can use free Another PostgreSQL Diff Tool for PostgreSQL databases for comparison of your development schema and production schema. You simply update your development database the way you are most comfortable to do. When you want to update production database to the state of development database, you do dump of development database schema(s) and production database schema(s) and let apgdiff to compare them. It will produce you output that contains DDL statements needed to transform your production database to the state of development database.

In fact it's up to you how to deploy apgdiff to your development cycle, all it does is that it creates output with DDL statements to "move" your production database to the same state as development database is.

On the website you can find info about how it works, how to Use it, what statements are supported etc. There is also article about PostgreSQL schema upgrade at my blog at www.fordfrog.name (I was allowed to include only one link so could not make this address link too).

Answer 4

Take a look at LiquiBase (http://www.liquibase.org/)

It's a tool designed for allowing developers to commit database changes to SVN, and then apply them safely and automatically to the database.

Changes can either be reverse engineered by comparing two databases, or hand coded by the developer and committed.

It also ensures that database changes are applied in the correct order, and only applied once to a given database.

Answer 5

We simply version the scripts used to create the database from scratch. The developers edit the scripts in the text files, and not in the database. Developers do not have access to the production SQL servers, and the DBA team uses tools specifically designed to compare database schemas (in our case, Red-Gate SQLCompare) in order to do production builds. They'll create a new, empty database from the scripts, and use the compare tool to detect changes. Some changes can be automatically applied, and some must be hand-altered.

It's not a perfect system, but it's worked fairly well for us so far.