SQL Database Version Control – Summary

Update: Please visit the Schema VC page for updated information.

Before moving on to discussing how the Pyrseas version control tools deal with the database changes we’ve been using as examples, it seems appropriate to summarize the tools reviewed so far.

The following classifies the tools according to the main feature or mode of operation, and lists them alphabetically within each group. I’ve added some other tools that were not reviewed but were mentioned in comments, in PostgreSQL mailing lists, or elsewhere. Except where otherwise mentioned, the tools are open source software and support PostgreSQL.

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.

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.

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: Does not currently support all PostgreSQL features.

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.

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.

dbsteward

Summary: 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: Unknown

Limitations: Apparently, XML-based data dictionary must be maintained manually.

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.

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.

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.

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

Other

ChronicDB

Commercial product, supports Linux (Debian and RPM packages).

EMS DB Comparer for PostgreSQL

Commercial product, only available for Microsoft Windows platforms.

neXtep Designer

GPL licensed, IDE based. multiple DBMS support.

This summary has also been posted in a separate page in this blog, and I’ll update that from time to time. If anyone has any corrections or updates, please let me know in the comments.

6 thoughts on “SQL Database Version Control – Summary”

    1. No, I hadn’t heard about it. I presume you mean the DB Solo Schema Comparison Tool. It appears that it can compare the schemas of two databases via direct connection, generate a report on differences and also generate a DDL script to synchronize them. Not sure where it can tie in to a VCS.

  1. What about Toad Data Modeler?

    I’m using it since it was Case Studio.

    It has support for many things and is good when dealing with tables with data. It has an issue with sequences of serial columns, but I manage to edit the diff script manually.

    1. Thanks for the info. Just to clarify, my interest is on tools (primarily open source) that can assist in the management of PostgreSQL database changes in conjunction with a VCS, such as Git or Subversion. Toad Data Modeler, DB Solo (mentioned by Bob above) and even the three that I included under “Other” appear to fall more in the CASE and Data Modeling arena. These may provide output or even perform certain functions that assist in schema versioning, but their focus is much broader.

      1. I’m afraid I can’t really tell what DataGrove is or does. I couldn’t find a simple description on its website, only that it “does all the behind-the-scenes magic” but without explanation of what the magic may entail. All the examples refer to MySQL so it seems their focus is on that rather than on PostgreSQL (the pachyderms in the title of this blog). DataGrove appears to be managing copies of (whole) MySQL databases which doesn’t quite compare to version control of database schemas, i.e., the meta-information describing the tables, columns, functions, etc.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s