Maintenance of databases can impose quite some overhead. Most projects have several development and test databases, or multiple production databases at different customer sites, and the version of these database instances differs.
Recently, a new open source tool has been released called
DbMaintain. DbMaintain automates execution of scripts to a relational database. It enables bringing database scripts into version control just like regular source code and can be used to transparently deploy databases from development to production. DbMaintain automates the part of the deployment which is most difficult to automate, opening the door for fully automated deployments.
The tool works as follows: Database scripts are organized in folder tree, like demonstrated in following example:
scripts/01_v1.0/01_products_and_orders.sql
02_users.sql
02_v1.1/01_add_barcode_column.sql
02_drop_itemcode_column.sql
Folder and script names must start with an index number followed by an underscore and a description. The index indicates the sequence of the scripts or script folders.
The database keeps track of all executed scripts in the table DBMAINTAIN_SCRIPTS:
FILE_NAME CHECKSUM SUCCEEDED
01_v1.0/01_products_and_orders.sql q34dxg37ds97ze97sdfz 1
01_v1.0/02_users.sql fXrZ45FsGs6SDhd4zs2h 1
02_v1.1/01_add_barcode_column.sql rz56eR1x78qePMr67q45 1
02_v1.1/02_drop_itemcode_column.sql 7dR3dg634g8kUfxcX9oW 1
Suppose you add a new script 02_v1.1/03_add_productid_sequence.sql. The next time you call the
updateDatabase operation, the database is updated incrementally by executing this new script.
DbMaintain is not the first project trying to automate database deployments, comparable projects exist such as dbmigrate, migratedb, liquibase and dbdeploy. DbMaintain however goes a lot further than these projects in terms of the features that it offers:
- The tool doesn't just 'suppose' that you don't modify existing scripts or add scripts out-of-sequence, it actually verifies it. For each script, a checksum is stored. If an existing script changed, an error is given and the database is not updated.
- A from-scratch mode is supported: if used, all script manipulations are allowed: If you modify an existing scripts or add one out-of-sequence, all database objects are dropped and the database is recreated from-scratch. The from-scratch option is ideal for development or test databases or any database in which preservation of data is not a requirement.
- Support is offered for repeatable scripts, that have to be written such that they can be executed multiple times. They can always be changed: if so they are simply re-executed.
- Postprocessing scripts can be defined that are executed each time some script was executed.
- Multiple target databases or database users are supported.
- Support for oracle stored procedures.
- A script archive (a jar file) can be created and used for deployment. Such an archive can be published as a project artifact, just like you would publish a regular jar or war file.
- Support for script renames: Scripts can be renamed and reorganized, as long as the relative sequence of the scripts doesn't change.
DbMaintain originates from the project
unitils, a library offering various utilities for unit/integration testing. The 'dbmaintainer' feature of unitils was created to automatically maintain a database that is used to execute automatic tests, but as features were added the dbmaintainer became more generally usable. That's why we decided to split it off into a new project.
