Database Versioning Strategy

db-versioning-strategy.md

SQL Folder Structure Example:

./src/main/sql
|___ deprecated/
|              |___ postgres/
|                           |___ v2/
|                                  |___ ddl.sql (contains CREATE TABLE statements)
|                                  |___ dml.sql (contains INSERT INTO statements)
|                                  |___ test-data.sql (contains INSERT INTO statements)
|___ latest/
|          |___ oracle/
|          |          |___ ddl.sql (contains CREATE TABLE statements)
|          |          |___ dml.sql (contains INSERT INTO statements)
|          |          |___ test-data.sql (contains INSERT INTO statements)
|          |___ current-version.txt
|___ v1/
|      |___ postgres/
|                   |___ ddl.sql (contains CREATE TABLE statements)
|                   |___ dml.sql (contains INSERT INTO statements)
|                   |___ test-data.sql (contains INSERT INTO statements)
|___ v2/
|      |___ postgres/
|      |            |___ ddl.sql (contains ALTER TABLE statements)
|      |            |___ dml.sql (contains INSERT INTO statements)
|      |            |___ rollback-plan/
|      |                              |___ plan.md
|      |                              |___ ddl.sql (contains ALTER TABLE statements)
|      |                              |___ dml.sql (contains DELETE FROM statements)
|      |___ oracle/
|                 |___ ddl.sql (contains CREATE TABLE statements)
|                 |___ dml.sql (contains INSERT INTO statements)
|                 |___ test-data.sql (contains INSERT INTO statements)
|___ v3-rollbacked/
|                 |___ oracle/
|                 |          |___ ddl.sql (contains ALTER TABLE statements)
|                 |          |___ rollback-plan/
|                 |                            |___ plan.md
|                 |                            |___ ddl.sql (contains ALTER TABLE statements)
|                 |___ rollback-to-version.txt
|___ release-mapping.txt

• At the beginning and before releasing v1, sql folder will contain latest folder. It will include ddl.sql for creating new tables, dml.sql for insert essential data for the application, and test-data.sql for insert test data for development environment and for running test cases. current-version.txt will contain the text v1.

• Upon releasing db-v1, v1 folder will be created which will contain scripts equivalent to latest folder.

• When developing for a new application release (v2 or later) with database changes, all changes will be in v2 folder and also will be merged into existing scripts in latest folder. v2 folder can include ddl.sql for creating new tables, altering existing tables, or deleting existing tables. It can also include dml.sql and test-data.sql. current-version.txt will contain the text v2

• Also, rollback-plan folder will be created which contains instructions on how to do database rollback accompanied by necessary scripts.

• If a new database needs to be supported (e.g. oracle), a new folder will be created next to the previous database folder (e.g. postgres), which will include same files as in latest folder.

• deprecated folder contains any deprecated databases with the latest version of complete scripts.

• In case of a rollback, latest will be updated to match previous state and also add suffix -rollbacked to the version folder. rollback-to-version.txt will contain the version that rollbacked to (i.e. v2 or even v1 if rollbacked 2 versions).

• release-mapping.txt contains mapping between database releases and application releases, in the following format:

 db.v1=app.v1.0
 db.v2=app.v5.2.1