Meta Database

So I was looking for a way to version control database schemas, but I never found something that worked for me. I found all these options that never seem to match what I wanted:

• metabase
• dbv
• delta sql
• Propel
• phinx
• doctrine
• redbeanphp

At the end, I finally began doing the following.

The first schema release, I would create a file called:

• init-1.0.sql

This would contain all the sql statements needed to initialize the database. To indicate this is the current version I would create a symlink to it:

• init.sql -> init-1.0.sql

The next schema update I would create file with the commands to transform the schema. i.e. additional CREATE DATABASE or ALTER TABLE etc.

• upgrade-1.1.sql

So actually, the upgrade file would be the working version used for development. For release then I would create a new init file. Either doing it manually or by doing:

cat init-1.0.sql upgrade-1.1.sql > init-1.1.sql

To indicate that this is the current version, the symlink would be updated:

• init.sql -> init-1.1.sql

Final notes

This would end-up with a history of schema changes and scripts that could be used to upgrade the database schema from any version to any later version.