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

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.

Answer 2

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.

Answer 3

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.

Answer 4

For MySQL

When I land on a new DB:

Firstly, I check structure:

mysqldump --no-data --skip-comments --skip-extended-insert -h __DB_HOSTNAME__ -u __DB_USERNAME__ -p __DB1_NAME__ | sed 's/ AUTO_INCREMENT=[0-9]*//g' > FILENAME_1.sql
mysqldump --no-data --skip-comments --skip-extended-insert -h __DB_HOSTNAME__ -u __DB_USERNAME__ -p __DB2_NAME__ | sed 's/ AUTO_INCREMENT=[0-9]*//g' > FILENAME_2.sql
diff FILENAME_1.sql FILENAME_2.sql > DIFF_FILENAME.txt
cat DIFF_FILENAME.txt | less

Thanks to stackoverflow users I could write this quick script to find structure differences.

src : https://stackoverflow.com/a/8718572/4457531 & https://stackoverflow.com/a/26328331/4457531

In a second step, I check datas, table by table with mysqldiff. It's a bit archaic but a php loop based on information_schema datas make job surely

For versioning, I use the same way but I format a SQL update script (to upgrade or rollback) with diff results and I use version number convention (with several modifications the version number look like an ip address).

initial version : 1.0.0
                  ^ ^ ^
                  | | |
structure change: - | |
datas added: -------- |
datas updated: --------

Answer 5

If you are still looking for options : have a look at neXtep designer. It is a free GPL database development environment based on the concepts of version control. In the environment you always work with versioned entities and can focus on the data model development. Once a release is done, the SQL generation engine plugged on the version control system can generate any delta you need between 2 versions, and will offer you some delivery mechanism if you need.

Among other things, you can synchronize and reverse synchronize your database during developments, create data model diagrams, query your database using integrated SQL clients, etc.

Have a look at the wiki for more information : http://www.nextep-softwares.com/wiki

It currently supports Oracle, MySql and PostgreSql and is in java so the product runs on windows, linux and mac.