SVC (software version control) such as Git, Subversion are being used to track changes in software source. But what about tracking changes in databases schemas, users and other configuration?
Below are two open source projects that aim at managing and storing the changes to the database in source control:
Liquibase
Site: http://www.liquibase.org/
Liquibase is an open source project, allowing a development team to track changes to the database schema, users and data by specifying the changes in XML, YAML, JSON and SQL files, which can be tracked via source control system such as Git, Subversion, etc.
Each change has a unique ID (similar to a version). The Liquibase files can be used to “play” the changes by converting the XML/YAML/JSON files to SQL, and running them on the target QA, staging and production environments, for example: adding new columns to the database.
Example of change in YAML:
- changeSet:
id: 2
author: nvoxland
changes:
- addColumn:
tableName: person
columns:
- column:
name: username
type: varchar(8)
- changeSet:
id: 3
author: nvoxland
changes:
- addLookupTable:
existingTableName: person
existingColumnName:state
newTableName: state
newColumnName: id
newColumnDataType: char(2)
Liquibase supports most of the wide spread databases such as:
- Apache Derby
- DB2
- Firebird
- H2
- HSQL
- Informix
- MS SQL Server
- MySQL
- Oracle
- PostgreSQL
- SQLite
- Sybase
Flyway
Site: https://flywaydb.org/
Flyway is very similar to Liquibase. The difference is that code changes are created as SQL files (not XML or YAML).
At the first time, Flyway will create a table in the database called SCHEMA_VERSION. This table stores the changes made to the database by Flyway.
Flyway can be invoked and migrate the database from one version to another in the following manners:
- Through Flyway command line
- Embedded as part of your Java application.
It is useful to put Flyway at part of the application initialisation phase. Its library will check the SCHEMA_VERSION in the database and will automatically update the database if needed.
Supported databases:
- DB2
- Derby
- H2
- HSQL
- Oracle
- MySQL / MariaDB
- MS SQL Server
- Phoenix
- PostgreSQL
- Redshift
- SAP HANA
- soliDB
- SQL Azure
- SQLite
- Sybase ASE
- Vertica