Summary of SQL Database Version Control tools
Last updated: 3 May 2013
The following classifies the tools according to the main feature or mode of operation, and lists them alphabetically within each group. Except where otherwise mentioned, the tools are open source software and support PostgreSQL. Where available, the Summary headings link to more detailed reviews in this blog.
Difference Engines
Andromeda
Summary: More than a difference engine, compares input data dictionary to information_schema views, and applies changes to upgrade the connected database.
Input format: YAML
Dependencies: PHP, Apache, PEAR, PL/Perl
Limitations: Data dictionary needs to be created and maintained manually. Limited support of PostgreSQL data types and other features.
Latest version: For download: 0.1.0. Feb 2009; Subversion: r1401, 13 Dec 2010.
apgdiff
Summary: Compares two SQL DDL input files, and outputs SQL statements to upgrade the first schema to match the second.
Input format: output of pg_dump -s
Dependencies: Java
Limitations: Does not currently support all PostgreSQL features.
Latest version: 2.4, 21 Sep 2012; Git repository: 21 Sep 2012.
Pyrseas
Summary: Compares an input data dictionary to the pg_* catalogs of a connected database, and outputs SQL statements to upgrade the latter to match the former. Also provides utility to output catalog information in YAML/JSON format.
Input format: YAML (JSON subset)
Dependencies: Python, psycopg2, LibYAML
Limitations: Support nearly all PostgreSQL features.
Latest version: For download: 0.6.1, 31 Jan 2013; Git repository: 3 May 2013.
Triangulum
Summary: Successor to Andromeda (see above) but with more focused objectives.
Input format: YAML
Dependencies: PHP, Spyc (YAML library)
Limitations: Data dictionary needs to be created and maintained manually. Limited support of PostgreSQL data types (but better than Andromeda) and other features.
Latest version: For downloand: Alpha 3, 5 Feb 2011; Subversion: r226, 5 Feb 2011.
Version Control of Database Changes
dbdeploy
Summary: Database refactoring tool similar to Liquibase (see below). Converts database changes specified in XML to SQL statements to upgrade a target database.
Input format: XML
Dependencies: Java, JDBC
Limitations: Primary support is for DBMSs other than PostgreSQL, so lowest common denominator coverage of PG features.
Latest version: For download: 3.0 Milestone 3, 15 Mar 2011; Subversion: r171, 4 Apr 2011.
dbsteward
Summary: Note: Need to take a closer look. Announced in the pgsql-general mailing list. Expected to be available as open source by PGCon 2011. Similar to Liquibase (see below), but see this message for more details.
Input format: XML
Dependencies: PHP and PEAR
Limitations: Apparently, XML-based data dictionary must be maintained manually.
Latest version: Git repository, 13 Feb 2012.
depesz Versioning
Summary: Mechanism to control database upgrades implemented as named sets of “patches.”
Input format: SQL scripts
Dependencies: Perl (to list patch dependencies)
Limitations: Scripts to rollback changes need to be created manually.
Latest version: Git repository: 16 June 2011.
Liquibase
Summary: Database refactoring tool. Converts database changes specified in XML to SQL statements to upgrade a target database.
Input format: XML
Dependencies: Java, JDBC driver
Limitations: Primary support is for DBMSs other than PostgreSQL, so lowest common denominator coverage of PG features.
Latest version: For download: 2.0.5, 2 May 2012; Git repository: 3 Apr 2013.
Database as VCS
Post Facto
Summary: Uses a number of PostgreSQL databases to establish a Subversion-like repository, so that schema changes to one database can be committed to the repository, and then the changes can be propagated from the repository to other databases.
Input format: none
Dependencies: Python, pyPgSQL
Limitations: Still in alpha status. Last PG version supported: 8.3.
Latest version: Subversion: r34, 12 Dec 2009.
Catalog Snapshots
POV
Summary: Allows you to save a snapshot of the PostgreSQL catalogs, make some schema changes, and possibly roll back the changes by restoring the snapshot.
Input format: none
Dependencies: Uses pgcrypto if available
Latest version: Git repository: 16 Jan 2011.
Other
There are other tools that may provide some of the capabilties needed for SQL database version control, but are generally broader in scope. The following have been suggested by commenters on this site or elsewhere: ChronicDB (commercial product, supports Linux–Debian and RPM packages), EMS DB Comparer for PostgreSQL (commercial product, only available for Microsoft Windows platforms), and neXtep Designer (GPL licensed, IDE based. multiple DBMS support).
If you have any corrections or updates, please let me know in the comments.
