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:
At the end, I finally began doing the following.
The first schema release, I would create a file called:
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.
So actually, the
upgrade file would be the working version
used for development. For release then I would create a
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
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.