How do you version your database schema? [closed]

Question

Closed. This question is opinion-based. It is not currently accepting answers.

---

Want to improve this question? Update the question so it can be answered with facts and citations by editing this post.

Closed 4 months ago.

How do you prepare your SQL deltas? do you manually save each schema-changing SQL to a delta folder, or do you have some kind of an automated diffing process?

I am interested in conventions for versioning database schema along with the source code. Perhaps a pre-commit hook that diffs the schema?

Also, what options for diffing deltas exist aside from DbDeploy?

EDIT: seeing the answers I would like to clarify that I am familiar with the standard scheme for running a database migration using deltas. My question is about creating the deltas themselves, preferably automatically.

Also, the versioning is for PHP and MySQL if it makes a difference. (No Ruby solutions please).

Answer 1

I am using strict versioning of the database schema (tracked in a separate table). Scripts are stored in version control, but they all verify current schema version before making any change.

Here is the full implementation for SQL Server (the same solution could be developed for MySQL if needed): How to Maintain SQL Server Database Schema Version

Answer 2

I also developed a set of PHP scripts where developers can submit their deltasql scripts to a central repository.

In one of the database tables (called TBSYNCHRONIZE), I store the version number of the latest executed script, so I can upgrade any database easily by using the web interface or a client developed on purpose for Eclipse.

The web interface allows to manage several projects. It supports also database "branches".

You can test the application at http://www.gpu-grid.net/deltasql (if you login as admin with password testdbsync). The application is open source and can be downloaded here: http://sourceforge.net/projects/deltasql

deltasql is used productively in Switzerland and India, and is popular in Japan.

Answer 3

Some months ago I searched tool for versioning MySQL schema. I found many useful tools, like Doctrine migration, RoR migration, some tools writen in Java and Python.

But no one of them was satisfied my requirements.

My requirements:

1. No requirements , exclude PHP and MySQL
2. No schema configuration files, like schema.yml in Doctrine
3. Able to read current schema from connection and create new migration script, than represent identical schema in other installations of application.

I started to write my migration tool, and today I have beta version.

Please, try it, if you have an interest in this topic. Please send me future requests and bugreports.

Source code: bitbucket.org/idler/mmp/src Overview in English: bitbucket.org/idler/mmp/wiki/Home Overview in Russian: antonoff.info/development/mysql-migration-with-php-project

Answer 4

See

Is there a version control system for database structure changes?

How do I version my MS SQL database in SVN?

and Jeff's article

Get Your Database Under Version Control

I feel your pain, and I wish there were a better answer. This might be closer to what you were looking for.

Mechanisms for tracking DB schema changes

Generally, I feel there is no adequate, accepted solution to this, and I roll my own in this area.

Answer 5

I use http://code.google.com/p/oracle-ddl2svn/

Answer 6

I don't manage deltas. I make changes to a master database and have a tool that creates an XML based build script based on the master database.

When it comes time to upgrade an existing database I have a program that uses the XML based build script to create a new database and the bare tables. I then copy the data over from the old database using INSERT INTO x SELECT FROM y and then apply all indexes, constraints and triggers.

New tables, new columns, deleted columns all get handled automatically and with a few little tricks to adjust the copy routine I can handle column renames, column type changes and other basic refactorings.

I wouldn't recommend this solution on a database with a huge amount of data but I regularly update a database that is over 1GB with 400 tables.