Sqitch Cheat Sheet

Overview

Sqitch is a database change management application that takes a dependency-based approach rather than the numbered-migration approach used by most tools. Changes are identified by name rather than version number, and dependencies between changes are declared explicitly. This allows for more flexible branching and merging of database changes alongside application code.

Sqitch uses native database scripting (plain SQL) for deploy, revert, and verify scripts. It does not require a specific framework, ORM, or programming language. Sqitch stores its metadata in the target database using a schema called sqitch. It supports PostgreSQL, MySQL, SQLite, Oracle, Firebird, Snowflake, Exasol, and Vertica.

Installation

# macOS via Homebrew
brew install sqitch --with-postgres-support

# Or with all database support
brew install sqitch

# Debian/Ubuntu
sudo apt-get install sqitch

# Docker
docker pull sqitch/sqitch

# CPAN (Perl)
cpanm App::Sqitch DBD::Pg  # For PostgreSQL
cpanm App::Sqitch DBD::mysql  # For MySQL

# Verify installation
sqitch --version

Core Commands

Command	Description
sqitch init	Initialize a new Sqitch project
sqitch add	Add a new change
sqitch deploy	Deploy pending changes
sqitch revert	Revert changes
sqitch verify	Verify deployed changes
sqitch status	Show deployment status
sqitch log	Show deployment history
sqitch plan	Show the deployment plan
sqitch tag	Tag the current state
sqitch rework	Modify a previously deployed change
sqitch rebase	Revert and re-deploy changes
sqitch check	Check for undeployed dependencies
sqitch bundle	Bundle project for distribution

Getting Started

Initialize a Project

# Initialize a Sqitch project for PostgreSQL
sqitch init myproject --engine pg

# Initialize for MySQL
sqitch init myproject --engine mysql

# Initialize for SQLite
sqitch init myproject --engine sqlite

# This creates:
# sqitch.conf — project configuration
# sqitch.plan — deployment plan
# deploy/ — deploy scripts directory
# revert/ — revert scripts directory
# verify/ — verify scripts directory

Add Changes

# Add a change
sqitch add users -n "Create users table"

# Add a change with dependencies
sqitch add posts -r users -n "Create posts table"

# Add a change with multiple dependencies
sqitch add post_tags -r posts -r tags -n "Create post_tags junction table"

# Add a change that depends on a specific change
sqitch add user_profiles -r users -n "Add user profiles"

Deploy Scripts

-- deploy/users.sql
BEGIN;

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_users_email ON users(email);

COMMIT;

-- deploy/posts.sql
BEGIN;

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    status VARCHAR(20) DEFAULT 'draft',
    user_id INTEGER NOT NULL REFERENCES users(id),
    published_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_posts_user_id ON posts(user_id);
CREATE INDEX idx_posts_status ON posts(status);

COMMIT;

Revert Scripts

-- revert/users.sql
BEGIN;
DROP TABLE IF EXISTS users;
COMMIT;

-- revert/posts.sql
BEGIN;
DROP TABLE IF EXISTS posts;
COMMIT;

Verify Scripts

-- verify/users.sql
BEGIN;

SELECT id, email, name, password_hash, created_at, updated_at
FROM users
WHERE FALSE;

ROLLBACK;

-- verify/posts.sql
BEGIN;

SELECT id, title, content, status, user_id, published_at, created_at
FROM posts
WHERE FALSE;

SELECT 1/COUNT(*)
FROM information_schema.table_constraints
WHERE table_name = 'posts'
  AND constraint_type = 'FOREIGN KEY';

ROLLBACK;

Configuration

sqitch.conf

[core]
    engine = pg
    plan_file = sqitch.plan
    top_dir = .

[engine "pg"]
    target = db:pg://localhost:5432/mydb
    registry = sqitch
    client = psql

[deploy]
    verify = true
    mode = change

[revert]
    # Number of changes to revert by default
    # to = @HEAD^

[verify]
    # No specific verify settings needed

Target Configuration

# Add named targets
sqitch target add dev db:pg://localhost:5432/mydb_dev
sqitch target add staging db:pg://staging-host:5432/mydb_staging
sqitch target add production db:pg://prod-host:5432/mydb_production

# Deploy to specific target
sqitch deploy dev
sqitch deploy staging
sqitch deploy production

# Show configured targets
sqitch target list

User Configuration

# Set user info (stored in ~/.sqitch/sqitch.conf)
sqitch config --user user.name "Alice Smith"
sqitch config --user user.email "alice@example.com"

sqitch.plan

# The plan file tracks all changes and their dependencies
%syntax-version=1.0.0
%project=myproject
%uri=https://github.com/org/myproject

users 2025-01-15T10:00:00Z Alice Smith <alice@example.com> # Create users table
posts [users] 2025-01-15T11:00:00Z Alice Smith <alice@example.com> # Create posts table
tags 2025-01-16T09:00:00Z Alice Smith <alice@example.com> # Create tags table
post_tags [posts tags] 2025-01-16T10:00:00Z Alice Smith <alice@example.com> # Junction table
@v1.0 2025-01-16T12:00:00Z Alice Smith <alice@example.com> # Tag v1.0

user_profiles [users] 2025-02-01T10:00:00Z Bob Jones <bob@example.com> # User profiles

Advanced Usage

Tagging Releases

# Tag the current state
sqitch tag v1.0 -n "Release 1.0"

# Deploy up to a specific tag
sqitch deploy --to @v1.0

# Revert to a specific tag
sqitch revert --to @v1.0

# View tags
sqitch plan

Reworking Changes

# Rework a previously deployed change (e.g., add column to users)
sqitch rework users -n "Add role column to users"

# This:
# 1. Copies current deploy/users.sql to deploy/users@v1.0.sql
# 2. Copies current revert/users.sql to revert/users@v1.0.sql
# 3. Lets you edit deploy/users.sql with the new changes
# 4. New revert/users.sql should revert back to the @v1.0 state

-- deploy/users.sql (reworked)
BEGIN;

ALTER TABLE users ADD COLUMN role VARCHAR(20) DEFAULT 'user';
CREATE INDEX idx_users_role ON users(role);

COMMIT;

-- revert/users.sql (reworked — reverts to previous state)
BEGIN;

DROP INDEX IF EXISTS idx_users_role;
ALTER TABLE users DROP COLUMN IF EXISTS role;

COMMIT;

Rebase

# Revert and re-deploy from a specific change
sqitch rebase --onto users

# Revert everything and re-deploy
sqitch rebase

# Rebase on a specific target
sqitch rebase production --onto v1.0

Docker Usage

# Run Sqitch via Docker
docker run --rm -it \
  -v $(pwd):/repo \
  -v ~/.sqitch:/home/sqitch/.sqitch \
  sqitch/sqitch \
  deploy db:pg://host.docker.internal:5432/mydb

# Create an alias for convenience
alias sqitch='docker run --rm -it -v $(pwd):/repo -v ~/.sqitch:/home/sqitch/.sqitch sqitch/sqitch'

Bundle for Distribution

# Create a standalone bundle
sqitch bundle --dest-dir release/

# The bundle contains:
# release/
#   sqitch.conf
#   sqitch.plan
#   deploy/
#   revert/
#   verify/

# Deploy from bundle
cd release
sqitch deploy db:pg://target-host:5432/mydb

CI/CD Integration

# GitHub Actions
name: Database Migration
on:
  push:
    branches: [main]
    paths: ['deploy/**', 'revert/**', 'verify/**', 'sqitch.plan']
jobs:
  migrate:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - name: Deploy to staging
        run: |
          docker run --rm \
            -v $(pwd):/repo \
            sqitch/sqitch \
            deploy "db:pg://${{ secrets.STAGING_DB_URL }}"
      - name: Verify
        run: |
          docker run --rm \
            -v $(pwd):/repo \
            sqitch/sqitch \
            verify "db:pg://${{ secrets.STAGING_DB_URL }}"

Troubleshooting

Issue	Solution
Deploy fails on dependency	Ensure dependent changes are listed in sqitch.plan and already deployed
Verify fails	Check verify script selects from correct columns; ensure table structure matches
Cannot connect to database	Verify connection URI format: db:pg://user:pass@host:port/dbname
Revert order wrong	Sqitch reverts in reverse dependency order; check sqitch.plan for correct dependencies
Plan conflicts after merge	Resolve conflicts in sqitch.plan like any text file; ensure change names are unique
Registry schema missing	Run sqitch deploy on a fresh database; it creates the sqitch schema automatically
Client binary not found	Set the client path in sqitch.conf for your engine
Checksum mismatch	Change scripts should not be modified after deployment; use rework instead
Docker networking issues	Use host.docker.internal for localhost connections from Docker