The Perl Toolchain Summit needs more sponsors. If your company depends on Perl, please support this very important event.
 / 
App-Sqitch-0.31-TRIAL
River stage one • 2 direct dependents • 2 total dependents
43 ++
/ sqitch

Name

sqitch - SQL Change Management

Synopsis

  sqitch [<options>] <command> [<command-options>] [<args>]

Description

Sqitch is a VCS-aware SQL 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 on your database or development choices.

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.

VCS integration

Sqitch likes to use your VCS history to determine in what order to execute changes. No need to keep track of execution order; your VCS already tracks information sufficient for Sqitch to figure it out for you.

Dependency resolution

Deployment steps can declare dependencies on other deployment steps. This ensures proper order of execution, even when you've committed changes to your VCS out-of-order.

No numbering

Change deployment is managed either by maintaining a plan file or, more usefully, your VCS history. As such, there is no need to number your changes, although you can if you want. Sqitch doesn't much care what you name your changes.

Bundling

Rely on your VCS history for deployment but have Sqitch bundle up changes for distribution. Sqitch can read your VCS history and write out a plan file along with the appropriate deployment and reversion scripts. Once the bundle is installed on a new system, Sqitch can use the plan file to deploy or the changes in the proper order.

Reduced Duplication

If you're using a VCS to track your changes, you don't have to duplicate entire change scripts for simple changes. As long as the changes are idempotent, you can change your code directly, and Sqitch will know it needs to be updated.

Terminology

step

A named unit of change. A step name must be used in the file names of its deployment and a reversion scripts. It may also be used in a test script file name.

tag

A known deployment state with a list one or more steps that define the tag. A tag also implies that steps from previous tags in the plan have been applied. Think of it is a version number or VCS revision. A given point in the plan may have one or more tags.

state

The current state of the database. This is represented by the most recently-deployed steps and tags. If the state of the database is the same as the most recent tag, then it is considered "up-to-date".

plan

A list of one or more tags and associated steps that define the order of deployment execution. Sqitch reads the plan to determine what steps to execute to change the database from one state to another. The plan may be represented by a "Plan File" or by VCS history.

deploy

The act of deploying database changes to reach a tagged deployment point. Sqitch reads the plan, checks the current state of the database, and applies all the steps necessary to change the state to the specified tag.

revert

The act of reverting database changes to reach an earlier tagged deployment point. Sqitch checks the current state of the database, reads the plan, and reverts all steps necessary to return the state to an earlier tag.

Options

     --plan-file  FILE    Path to a deployment plan file.
     --engine     ENGINE  Database engine.
     --client     PATH    Path to the engine command-line client.
  -d --db-name    NAME    Database name.
  -u --username   USER    Database user name.
     --host       HOST    Database server host name.
     --port       PORT    Database server port number.
     --sql-dir    DIR     Path to directory with deploy and revert scripts.
     --deploy-dir DIR     Path to directory with SQL deployment scripts.
     --revert-dir DIR     Path to directory with SQL reversion scripts.
     --test-dir   DIR     Path to directory with SQL test scripts.
     --extension  EXT     SQL script file name extension.
     --dry-run            Execute command without making any changes.
     --etc-path           Print the path to the etc directory and exit.
     --quiet              Quiet mode with non-error output suppressed.
     --verbose            Increment verbosity.
     --version            Print the version number and exit.
     --help               Print a usage statement and exit.
     --man                Print the complete documentation and exit.

Options Details

--plan-file
  sqitch --plan-file plan.conf

Path to the deployment plan file. Defaults to ./sqitch.plan. If this file is not present, Sqitch will attempt to read from VCS files. If no supported VCS system is in place, an exception will be thrown. See "Plan File" for a description of its structure.

--engine
  sqitch --engine pg

The database engine to use. Supported engines include:

--client
  sqitch --client /usr/local/pgsql/bin/psql

Path to the command-line client for the database engine. Defaults to a client in the current path named appropriately for the specified engine.

-d
--db-name
  sqitch --db-name widgets
  sqitch -d bricolage

Name of the database. For some engines, such as PostgreSQL and MySQL, the database must already exist. For others, such as SQLite, the database will be automatically created on first connect.

-u
--user
--username
  sqitch --username root
  sqitch --user postgres
  sqitch -u Mom

User name to use when connecting to the database. Does not apply to all engines.

--host
  sqitch --host db.example.com

Host name to use when connecting to the database. Does not apply to all engines.

--port
  sqitch --port 7654

Port number to connect to. Does not apply to all engines.

--sql-dir
  sqitch --sql-dir migrations/

Path to directory containing deployment, reversion, and test SQL scripts. It should contain subdirectories named deploy, revert, and (optionally) test. These may be overridden by --deploy-dir, --revert-dir, and --test-dir. Defaults to ./sql.

--deploy-dir
  sqitch --deploy-dir db/up

Path to a directory containing SQL deployment scripts. Overrides the value implied by --sql-dir.

--revert-dir
  sqitch --revert-dir db/up

Path to a directory containing SQL reversion scripts. Overrides the value implied by --sql-dir.

--test-dir
  sqitch --test-dir db/t

Path to a directory containing SQL test scripts. Overrides the value implied by --sql-dir.

--extension
  sqitch --extension ddl

The file name extension on deployment, reversion, and test SQL scripts. Defaults to sql.

--dry-run
  sqitch --dry-run

Execute the Sqitch command without making any actual changes. This allows you to see what Sqitch would actually do, without doing it. Implies a verbosity level of 1; add extra --verboses for greater verbosity.

--etc-path
  sqitch --etc-path

Print out the path to the Sqitch etc directory and exit. This is the directory where the system-wide configuration file lives, as well as deployment script templates.

--quiet

Suppress normal output messages. Error messages will still be emitted to STDERR. Overrides any value specified by --verbose.

--verbose
  sqitch --verbose --verbose

A value between 0 and 3 specifying how verbose Sqitch should be. Unless --quiet is specified, the default is 1, meaning that Sqitch will output basic status messages as it does its thing. Values of 2 and 3 each cause greater verbosity. Ignored if --quiet is specified.

--help
  sqitch --help

Outputs a brief description of the options supported by sqitch and exits.

--man
  sqitch --man

Outputs this documentation and exits.

-V
--version
  sqitch --version

Outputs the program name and version and exits.

Sqitch Commands

init

Create the plan file and deployment script directories if they do not already exist. This command is useful for starting a new Sqitch project.

status

Output information about the current status of the deployment, including a list of tags, deployments, and dates in chronological order. If any deploy scripts are not currently deployed, they will be listed separately. Options:

-s
--show

How much information to show. This option may be passed without a -s or --show. Possible values include:

latest

Show only the latest change. This is the default.

tags

Show a list of all deployed tags.

steps

Show a list of all deployed steps.

format

The format to use to display tags or steps, if either are specified via the --show option. The value may be any one of:

oneline
table
log

Output the complete change history in reverse chronological order. Options:

-t
--tag

Show only changes for the specified tag. This option may be passed without a -t or --tag.

-s
--step

Show only the specified step.

-n
--max-count

Limit the number of changes to show.

--skip

Skip the specified number changes before starting to show the log output.

--since

Show changes more recent than a specific date.

--until

Show changes older than a specific date.

--user

Limit the changes output to ones where the user name matches the specified pattern (regular expression).

--reverse

Output the changes in reverse sort order.

--format

The format to use. The value may be any one of:

short
oneline
full
table
check

Sanity check the deployment scripts. Checks include:

  • Make sure all deployment scripts have complementary reversion scripts.

  • Make sure no deployment script appears more than once in the plan file.

add-step

Add a new step. The specified name will be used to create stub deployment and reversion scripts. The contents of these scripts must then be edited to carry out the actual changes to the database. Options:

-n
--named

The name of the step. Must be a valid step name. This option may be passed without a -n or --named.

-r
--requires

Name of a step that is required by the new step.

-c
--conflicts

Name of a step that conflicts with the new step.

deploy

Deploy changes. Configuration properties may be specified under the [deploy] section of the configuration file, or via sqitch config:

  sqitch config deploy.$property $value

Options and configuration properties:

--to

Step or tag to deploy up to. Defaults to the latest tag or to the VCS HEAD commit. Property name: deploy.to.

-u
--untracked

Also deploy any steps that have been added to the deploy directory but have not yet been added to the plan or committed to the VCS. Useful when developing new deployment steps but have not yet committed them.

revert

Revert changes. Configuration properties may be specified under the [revert] section of the configuration file, or via sqitch config:

  sqitch config revert.$property $value

Options and configuration properties:

--to

Tag to revert to. Defaults to reverting all changes. Property name: revert.to.

test

Test changes. All SQL scripts in --test-dir will be run. [XXX Not sure whether to have subdirectories for tests and expected output and to diff them, or to use some other approach.]

config

Get and set project, user, or system Sqitch options. By default, the options will be written to the local configuration file, sqitch.conf

bundle

Bundle up deployment and reversion scripts and write out a plan file. Configuration properties may be specified under the [bundle] section of the configuration file, or via sqitch config bundle.$property $value command. Options and configuration properties:

--from

Tag to start the plan from. All tags and steps prior to that tag will not be included in the plan, and their change scripts Will be omitted from the bundle directory. Useful if you've rejiggered your deployment steps to start from a point later in your VCS history than the beginning of time. Property name: bundle.from.

--to

Tag with which to end the plan. No steps or tags after that tag will be included in the plan, and their change scripts will be omitted from the bundle directory. Property name: bundle.to.

--tags-only

Write the plan file with deployment targets listed under VCS tags, rather than individual commits. Property name: bundle.tags_only.

--destdir

Specify a destination directory. The plan file and deploy, revert, and test directories will be written to it. Defaults to "bundle". Property name: bundle.destdir.

help

Show help for a specific command or, if no command is specified, show the same documentation as --help.

Configuration

Sqitch configuration can be set up on a project, user, or system-wide basis. The format of the configuration file, named sqitch.conf, is the same as for git.

Here's an example of a configuration file that might be useful checked into a VCS for a project that deploys to PostgreSQL and stores its deployment scripts with the extension ddl under the migrations directory. It also wants bundle to be created in the _build/sql directory, and to deploy starting with the "gamma" tag:

  [core]
      engine    = pg
      db        = widgetopolis
      sql_dir   = migrations
      extension = ddl

  [revert]
      to        = gamma

  [bundle]
      from      = gamma
      tags_only = yes
      dest_dir  = _build/sql

And here's an example of useful configuration in ~/.sqitch/sqitch.conf, to point to system-specific engine information:

  [core.pg]
      client    = /usr/local/pgsql/bin/psql
      username  = theory

  [core.mysql]
      client    = /usr/local/mysql/bin/mysql
      username  = root

  [core.sqlite]
      client    = /usr/local/bin/sqlite3

Various commands read from the configuration file and adjust their operation accordingly. See sqitch-config for a list.

Plan File

A plan file describes the deployment tags and scripts to be run against a database. In general, if you use a VCS, you probably won't need a plan file, since your VCS history should be able to provide all the information necessary to derive a deployment plan. However, if you really do need to maintain a plan file by hand, or just want to better understand the file as output by the bundle command, read on.

Format

The contents of the plan file are plain text encoded as UTF-8. It is divided up into sections that denote deployment states. Each state has a bracketed, space-delimited list of one or more tags to identify it, followed by any number of deployment steps. Here's an example of a plan file with a single state and a single step:

 [alpha]
 users_table

The state has one tag, named "alpha", and one step, named "users_table". A state may of course have many steps. Here's an expansion:

 [root alpha]
 users_table
 insert_user
 update_user
 delete_user

This state has two tags, "root" and "alpha", and four steps, "users_table", "insert_user", "update_user", and "delete_user".

Most plans will have multiple states. Here's a longer example with three states:

 [root alpha]
 users_table
 insert_user
 update_user
 delete_user

 [beta]
 widgets_table
 list_widgets

 [gamma]
 ftw

Using this plan, to deploy to the "beta" tag, the "root"/"alpha" state steps must be deployed, as must the "beta" steps. To then deploy to the "gamma" tag, the "ftw" step must be deployed. If you then choose to revert to the "alpha" tag, then the "gamma" step ("ftw") and all of the "beta" steps will be reverted in reverse order.

Using this model, steps cannot be repeated between states. One can repeat them, however, if the contents for a file in a given tag can be retrieved from a VCS. An example:

 [alpha]
 users_table

 [beta]
 add_widget
 widgets_table

 [gamma]
 add_user

 [44ba615b7813531f0acb6810cbf679791fe57bf2]
 widgets_created_at

 [HEAD epsilon master]
 add_widget

This example is derived from a Git log history. Note that the "add_widget" step is repeated under the state tagged "beta" and under the last state. Sqitch will notice the repetition when it parses this file, and then, if it is applying all changes, will fetch the version of the file as of the "beta" tag and apply it at that step, and then, when it gets to the last tag, retrieve the deployment file as of its tags and apply it. This works in reverse, as well, as long as the changes in this file are always idempotent.

Grammar

Here is the EBNF Grammar for the plan file:

  plan-file   = { <state> | <empty-line> | <comment> }* ;

  state       = <tags> <steps> ;

  tags        = "[" <taglist> "]" <line-ending> ;
  taglist     = <tag> | <tag> <white-space> <taglist> ;
  tag         = [ <name> ] -<tagrel> ;
  tagsuffix   = <punct> [ <num> ] ;

  steps       = { <step> | <empty-line> | <line-ending> }* ;
  step        = <name> <line-ending> ;

  empty-line  = [ <white-space> ] <line-ending> ;
  line-ending = [ <comment> ] <EOL> ;
  comment     = [ <white-space> ] "#" [ <string> ] ;

  punct       = ? a punctuation character ? ;
  num         = ? numeric charcters ?
  name        = ? non-white space characters ? ;
  white-space = ? white space characters ? ;
  string      = ? non-EOL characters ? ;

See Also

The original design for Sqitch was sketched out in a number of blog posts:

Other tools that do database change management include:

Rails migrations

Numbered migrations for Ruby on Rails.

Module::Build::DB

Numbered changes in pure SQL, integrated with Perl's Module::Build build system. Does not support reversion.

DBIx::Migration

Numbered migrations in pure SQL.

Versioning

PostgreSQL-specific dependency-tracking solution by depesz.

Author

David E. Wheeler <david@justatheory.com>

License

Copyright (c) 2012 iovation Inc.

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.