How do you version your database schema?

Eran_Galperin_10585 0
Viewed 96165

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).

versioning schema mysql sql
16 Answers
user_45250
Jason_Jackson_131030

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.

user_68190

You didn't mention which RDBMS you're using, but if it's MS SQL Server, Red-Gate's SQL Compare has been indispensable to us in creating deltas between object creation scripts.

jalbert_13603880

I'm not one to toot my own horn, but I've developed an internal web app to track changes to database schemas and create versioned update scripts.

This tool is called Brazil and is now open source under a MIT license. Brazil is ruby / ruby on rails based and supports change deployment to any database that Ruby DBI supports (MySQL, ODBC, Oracle, Postgres, SQLite).

Support for putting the update scripts in version control is planned.

user_56030

We're exporting the data to a portable format (using our toolchain), then importing it to a new schema. no need for delta SQL. Highly recommended.

Shachar_138970
Swaroop_C_H_48690

I use Firebird database for most development and I use FlameRobin administration tool for it. It has a nice option to log all changes. It can log everything to a one big file, or one file per database change. I use this second option, and then I store each script in version control software - earlier I used Subversion, now I use Git.

I assume you can find some MySQL tool that has the same logging feature like FlameRobin does for Firebird.

In one of database tables, I store the version number of the database structure, so I can upgrade any database easily. I also wrote a simple PHP script that executes those SQL scripts one by one on any target database (database path and username/password are supplied on the command line).

There's also an option to log all DML (insert, update delete) statements, and I activate this while modifying some 'default' data that each database contains.

I wrote a nice white paper on how I do all this in detail. You can download the paper in .pdf format along with demo PHP scripts from here.

Milan_Babukov_146900

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.

Admin1
Related