Database versioning

Goal

• to have a clear tracking of database changes and thus of its version(s)
• to be able to roll out database changes without needing access to the database itself
• to have a database schema available without getting it from the PROD env
• to have a minimal data set to test with
• eventually to be able to quickly set-up a functional test database from the baseline, change scripts and the data set

What DB versioning is NOT

• it is not database backup
• it does not version data, but database schema/structure, however you can push data changes via "data_change_scripts"
• it cannot revert to a previous DB version; so it goes incremental only

Terms

• baseline - represents the database schema including database objects like: triggers, views, etc; the file is named "xx.yy.zz.sql"
• change script - represents a file that alters the database structure which contains one ore more SQL - commands; the file is named "xx.yy.zz.sql"
• data change script - represents a file that alters the database data which contains one ore more SQL commands; the file is named "xx.yy.zz.sql". This type of file should be used to propagate minor data changes to all development environments. One example could be for adding new static pages into a given table or insert a new access right, etc
• test data - represents a minimal test data that should work with the latest baseline and change scripts available; the file is named "data.sql"
• z_db_versioning - represents the table that will be created in each versioned database and will hold the current version of the database
• XX - represents the major no. of database
• YY - the minor
• ZZ - the revision point

Directory structure

• all the DB versioning related files are stored under "db" directory(this name is configurable per project)
• the baselines are stored under "/app_root/db/baselines/"
• the change scripts under "/app_root/db/change_scripts/"
• the data change scripts under "/app_root/db/data_change_scripts/"
• and the test data here "/app_root/db/test_data/"

Workflow

Important! given the two change scripts types(structure and data) the system keeps track of 2 versions: one being for structure and one for data. Both of them are displayed in CodePax under Database Versioning section. The versions for each type are independent and you can use change scripts only, data change scripts only or both of them.

The diagram below represents the workflow and available operations for each development environment: