Sqitch

Sane database change management

View project onGitHub

Sqitch is a database change management application. What makes it different from your typical migration-style approaches? A few things:

No opinions

Sqitch is not integrated with any framework, ORM, or platform. Rather, it is a standalone change management system with no opinions about your database engine, application framework, or development environment.

Native scripting

Changes are implemented as scripts native to your selected database engine. Writing a PostgreSQL application? Write SQL scripts for psql. Writing a MySQL-backed app? Write SQL scripts for mysql.

Dependency resolution

Database changes may declare dependencies on other changes—even on changes from other Sqitch projects. This ensures proper order of execution, even when you’ve committed changes to your VCS out-of-order.

No numbering

Change deployment is managed by maintaining a plan file. As such, there is no need to number your changes, although you can if you want. Sqitch doesn’t much care how you name your changes.

Iterative development

Up until you tag and release your application, you can modify your change deployment scripts as often as you like. They’re not locked in just because they’ve been committed to your VCS. This allows you to take an iterative approach to developing your database schema. Or, better, you can do test-driven database development.

Installation

There are a number of ways to install Sqitch. Each database engine also requires external dependencies:

  • PostgreSQL requires PostgreSQL
  • SQLite requires SQLite
  • MySQL requires MySQL
  • Firebird requires Firebird
  • Oracle requires Instant Client (and the setting of the $ORACLE_HOME environment variable)
  • Vertica requires vsql and the Vertica ODBC driver from Vertica
  • Exasol requires EXAplus and the Exasol ODBC driver from Exasol
  • Snowflake requires SnowSQL and the Snowflake ODBC driver from Snowflake

Docker

The Sqitch Docker image and shell script is by far the quickest and easiest way to get started with Sqitch. It supports PostgreSQL, SQLite, MySQL, and Firebird. Simply pull the image, download the shell script, make it executable, and go:

docker pull sqitch/sqitch
curl -L https://git.io/fAX6Z -o sqitch && chmod +x sqitch
./sqitch help

CPAN

The canonical source for Sqitch releases is CPAN. Use the cpan client to download and build Sqitch and its dependencies from Source; database engine dependencies must be installed first:

  • PostgreSQL: cpan App::Sqitch DBD::Pg
  • SQLite: cpan App::Sqitch DBD::SQLite
  • Oracle: cpan App::Sqitch DBD::Oracle
  • MySQL: cpan App::Sqitch DBD::mysql
  • Firebird: cpan App::Sqitch DBD::Firebird
  • Vertica, Exasol, Snowflake: cpan App::Sqitch DBD::ODBC

Homebrew

The Sqitch Homebrew Tap quickly builds Sqitch on you Mac. Some engines require the installation of dependencies in advance; consult the docs for build details.

First, configure the tap: brew tap sqitchers/sqitch. Then install the variants you need:

  • PostgreSQL: brew install sqitch --with-postgres-support
  • SQLite: brew install sqitch --with-sqlite-support
  • Oracle: HOMEBREW_ORACLE_HOME=$ORACLE_HOME brew install sqitch --with-oracle-support
  • MySQL: brew install sqitch --with-mysql-support
  • Firebird: brew install sqitch --with-firebird-support
  • Vertica: brew install sqitch --with-vertica-support
  • Snowflake: brew install sqitch --with-snowflake-support

Debian

The Debian Sqitch Apt package tends to lag behind several versions, but provides easy access to Debian and Ubuntu users:

  • Apt: apt-get install sqitch
  • PostgreSQL: apt-get install libdbd-pg-perl postgresql-client
  • SQLite: apt-get install libdbd-sqlite3-perl sqlite3
  • Oracle: apt-get install libdbd-oracle-perl
  • MySQL: apt-get install libdbd-mysql-perl mysql-client
  • Firebird 3.0 (Debian 9 and later): apt-get install libdbd-firebird-perl firebird3.0-utils
  • Firebird Classic (Debian 8 and earlier): apt-get install libdbd-firebird-perl firebird2.5-classic
  • Firebird Super (Debian 8 and earlier): apt-get install libdbd-firebird-perl firebird2.5-super
  • Vertica, Exasol, Snowflake: apt-get install libdbd-odbc-perl

Apt + cpanminus

An alternative that gets you the latest version of Sqitch is to just apt-get the external dependencies, and then build Sqitch itself via cpanminus:

  • Apt: apt-get install build-essential cpanminus perl perl-doc
  • Sqitch: cpanm --quiet --notest App::Sqitch
  • PostgreSQL: apt-get install postgresql libdbd-pg-perl
  • SQLite: apt-get install sqlite libdbd-sqlite3-perl
  • Oracle: apt-get install libdbd-oracle-perl
  • MySQL: apt-get install mysql libdbd-mysql-perl
  • Firebird: apt-get install firebird libdbd-firebird-perl
  • Vertica, Exasol, Snowflake: apt-get install libdbd-odbc-perl

Yum + cpanminus

The same basic pattern on Red Hat-derived systems calls for eternal dependencies to be yum-installed, and for Sqitch itself to be built and installed by cpanminus:

  • Yum: sudo yum install perl-devel perl-CPAN \
    && curl -L https://cpanmin.us | perl - --sudo App::cpanminus
  • Sqitch: cpanm --quiet --notest App::Sqitch
  • PostgreSQL: yum install postgresql perl-DBD-Pg
  • SQLite: yum install sqlite perl-DBD-SQLite
  • Oracle: yum install perl-DBD-Oracle
  • MySQL: yum install mysql perl-DBD-mysql
  • Firebird: yum install firebird-classic perl-DBD-Firebird perl-Time-HiRes
  • Vertica, Exasol, Snowflake: yum install perl-DBD-ODBC

Windows

The most reliable way to run Sqitch on Windows, aside from the Docker image, is to:

ActiveState PPM

An alternative is to use the ActiveState PPM, though builds tend to lag behind a few versions:

  • Install ActivePerl
  • Run ppm install App-Sqitch
  • For Oracle support, also run ppm install DBD-Oracle
  • For Firebird support, also run ppm install DBD-Firebird

Getting Started

Tutorials

The documentation includes an tutorial for each database engine demonstrating the creation, development, and maintenance of a database with Sqitch.

Mail List

Need help? subscribe to the mail list.

Presentations

Sane Database Change Management with Sqitch

A one hour technical introduction to Sqitch, with detailed usage examples to help get you started.

Agile Database Development

Three hour technical tutorial originally presented at PGCon 2013 and updated in January 2014, covering source code control with Git, database change control with Sqitch, and test-driven database development with pgTAP.