Schema VC

Summary of SQL Database Version Control tools

Last updated: 25 Nov 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 Postgres. 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 Postgres 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 Postgres 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 Postgres DDL features.

Latest version: For download: 0.7.0, 25 Nov 2013; Git repository: 25 Nov 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 Postgres 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 Postgres, 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 Postgres, 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 Postgres 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 Postgres 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.

Musings on Python, Postgres and other species

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: