Schema VC

Summary of SQL Database Version Control tools

Last updated: 25 Jun 2014

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: 4 Jun 2013, available on GitHub.

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, available on GitHub.

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.1, 5 Dec 2013; Git repository: 30 Apr 2014.

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: 1.3.10, 24 Jun 2014; Git repository, 24 Jun 2014.

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: 29 Oct 2013.

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: 3.2.0, 2 Jun 2014; Git repository: 24 Jun 2014.

Sqitch

Summary: Supports various databases (Postgres, MySQL, Oracle, etc). Somewhat similar to depesz Versioning above. Review to be published.

Input format: Native SQL scripts which must be written by the DBA or database developer, both to make a change and to back it out.

Dependencies: Perl, DBD::Pg (driver for Postgres access), several other Perl modules.

Limitations: Scripts to rollback and verify (test) changes need to be created manually (as a unit with the implementation script).

Latest version: v0.973, 3 Jul 2013; Git repository: 24 Jun 2014.

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: Mercurial repository, 11 Aug 2012 (copied from 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.

Schema Migration

There are tools that support database schema “migration”, e.g., Active Record Migrations, South and Alembic which are tied to specific programming frameworks (Ruby on Rails, Django) or ORMs (SQLAlchemy). We have chosen not to review those kinds of projects as they presuppose a particular development environment.

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: