/ 
Punk-Sqitch-0.01
/ Punk::Sqitch

NAME

Punk::Sqitch - schema change management for Punk applications, on Sqitch

SYNOPSIS

# the database punk.yml configures is the target, every time
punk sqitch deploy
punk sqitch deploy --env production
punk sqitch status --database analytics
punk sqitch add users -n 'users table'

# the pieces, from Perl
my $t = Punk::Sqitch->target_for({ dsn => 'dbi:Pg:host=db;dbname=shop',
                                   user => 'shop', password => $pw });
# $t->{uri}      db:pg://shop@db/shop
# $t->{engine}   pg
# $t->{password} $pw   - handed to Sqitch through $SQITCH_PASSWORD, never the URI

DESCRIPTION

Sqitch manages database schema as a plan of changes with deploy, revert and verify scripts, recorded in a registry in the database itself. Punk::Sqitch makes it the path of least resistance in a Punk application: punk sqitch runs any Sqitch command against the database the application is configured for, so there is one description of that database - the database keyword, or the database block of config/punk.yml with its secret behind $env - and Sqitch is told the target on every run rather than keeping its own copy in sqitch.conf.

The install needs one thing the Perl side cannot supply: Sqitch runs scripts through the database's own client, sqlite3 (3.3.9 or later), psql or mysql, so the host that deploys needs that binary on its PATH. The application's workers never do.

Installing App::Sqitch

As of App::Sqitch v1.6.1 its own t/engine.t dies on a recent Test::MockModule (the test reads $@ after an unmock that clears it), so cpanm App::Sqitch reports a failure with no failed assertion and does not install. cpanm --notest App::Sqitch does.

THE TARGET

target_for(\%database)

Maps a database options hash - dsn, and optionally user and password - to a Sqitch target:

dbi:SQLite:dbname=var/app.db                     db:sqlite:var/app.db
dbi:Pg:dbname=shop                               db:pg:shop
dbi:Pg:host=h;port=5433;dbname=shop   + user     db:pg://user@h:5433/shop
dbi:Pg:dbname=shop;sslmode=require               db:pg:shop?sslmode=require
dbi:mysql:database=shop;host=h        + user     db:mysql://user@h/shop
dbi:MariaDB:database=shop                        db:mysql:shop

Returns { uri, engine, password }. The URI never carries the password: Sqitch reads one from $SQITCH_PASSWORD, and a password that is not in the URI is not in sqitch -v's output, a process listing or a log. run localises the variable around each command.

DSN keys other than the host, port and database name become query parameters (sslmode, application_name, ...), which is how URI::db spells them and what Sqitch's engines hand to DBI. A driver other than SQLite, Pg, mysql and MariaDB croaks naming it: declare the target in sqitch.conf for those.

Other engines

Mapped from the DBI driver out of the box: SQLite, Pg, mysql and MariaDB, Oracle (sid or service_name as the database), Firebird and ClickHouse. Two escape hatches cover the rest, both as keys on the database options - the keyword's or the punk.yml block's:

database dsn => 'dbi:Pg:dbname=shop;host=h', sqitch_engine => 'cockroach';
database dsn => 'dbi:ODBC:DSN=warehouse',
         sqitch_target => 'db:vertica://etl@warehouse.internal/shop',
         user => 'etl', password => $pw;

sqitch_engine keeps the mapping and replaces the engine name, for the engines that ride on another's driver - CockroachDB and Yugabyte on DBD::Pg. sqitch_target is the application's own URI, used as it is whatever the DSN says (the password still travels separately). A driver with neither croaks naming both ways out, and a third:

engine_for($driver => \%spec)

Punk::Sqitch->engine_for(Vertica => { engine => 'vertica', name => 'database' });

Register, or replace, the mapping for a DBI driver: engine is Sqitch's engine name, name the DSN key holding the database name, aliases other keys that also name it, and path => 1 for a file-backed engine whose name is a path and whose URI has no authority, the SQLite shape. engines lists the engine names currently mapped.

The check (pending) knows where Pg, CockroachDB, Yugabyte, Oracle, Vertica, Snowflake and Exasol keep their registry (a schema) and where MySQL and ClickHouse do (a database beside the target); for an engine it does not know - Firebird keeps a separate database file behind the server - it reports an error rather than a guess, and punk sqitch status still works. The model skeleton has types for SQLite, Pg, CockroachDB, Yugabyte, MySQL, Oracle and Firebird (register_types adds an engine), and the client probe knows every client Sqitch 1.6 looks for (register_client adds one).

THE APPLICATION

The sqitch directory

Everything Sqitch owns lives together under sqitch/ in the application root - the plan, the conf and the three script directories:

myapp/
  app.psgi
  config/punk.yml
  lib/MyApp.pm
  sqitch/
    sqitch.conf
    sqitch.plan
    deploy/  revert/  verify/

punk sqitch init creates it and every command runs from it, so the application root stays the application's. A project with those files loose in the root - the layout before this, or one made by sqitch init by hand - is used where it is; nothing has to move.

Because each project runs from its own directory, a relative SQLite path is resolved against the application root, in punk.yml and in a --target of your own alike, and the directory it names is created on deploy if it is missing.

project_dir($root)

The directory holding the application's Sqitch project: sqitch/ under the root when it holds a plan or a conf, the root itself when that does, and sqitch/ for a project that does not exist yet.

ensure_target_dir($target, $root)

The directory a SQLite target lives in, created when missing (relative paths against $root); nothing for any other engine. Returns what it made, or undef.

find_root($dir?)

The directory holding app.psgi, walking up from $dir (default the current directory) - the rule every punk command uses.

databases_for(root => $dir, env => $name, registrar => $app?)

The configured databases as { name => \%options }, with default for the unnamed one. From the application's registrar when it can answer ($app->databases, Punk 0.31), else from config/punk.yml resolved for the environment through Punk::Config - secrets included, because Sqitch needs the real password.

database_for(\%databases, $name?)

One database's options, or a croak listing the names there are.

THE CHECK

my $r = Punk::Sqitch->pending(root => $root, database => \%db);
# { project, plan_file, registry, deployed => N, pending => [...],
#   drift => undef|"...", error => undef|"..." }

Is the schema behind the code? The plan against the registry, computed here and used twice: by punk sqitch pending as a deploy gate and by Punk::Plugin::Sqitch at boot. Sqitch's own status prints the answer and exits 0 either way, and its check reports something else - a deployed script edited since, by hash - so this is the other half.

pending(root => $dir, database => \%db, %opts)

pending is the plan's changes beyond the number the registry holds for the project. Counts, not names: a reworked change is in the plan twice under one name and in the registry twice with two ids, so the first N plan entries are what N deployed rows stand for. The Nth deployed name is checked against the Nth plan entry, and a mismatch - or more rows than the plan has entries - is drift: something deployed here that the plan does not describe, which no deploy will fix. A registry that does not exist yet is "nothing deployed", everything pending, and not an error; one that cannot be reached or read is error, and the caller decides - the boot check warns and starts.

Options: target (from target_for, computed otherwise), conf (from read_conf, read from sqitch.conf in the root otherwise), plan_file (from plan_file_for otherwise). A missing plan croaks naming the path and punk sqitch init.

One SELECT through DBI, with no App::Sqitch loaded: the boot check runs in a web worker's parent, where loading a Moo application to answer a yes/no question would be the wrong cost.

read_conf($file)

sqitch.conf as a flat hash, section.key and section.sub.key ([engine "sqlite"] gives engine.sqlite.*), comments and quotes handled. Only the project's own file: a registry renamed in ~/.sqitch/sqitch.conf is not seen.

conf_get(\%conf, $key, $engine?, $target?)

One key by Sqitch's precedence: target.$target.$key, then engine.$engine.$key, then core.$key.

plan_file_for($root, \%conf, $engine?)

plan_file from the conf (relative to the root), else top_dir (or the root) joined with sqitch.plan.

read_plan($file)

{ project, uri, changes => [names], tags => [names], deps => { change => [deps] }, requires => [projects] }: the %project and %uri pragmas, every change's name in order (a reworked one twice), the tags, each change's dependencies, and the other projects those dependencies name (project:change). A plan without %project croaks.

pending_all(root => $dir, database => \%db, projects => \@projects?)

pending for every project in the list (projects when none is given), in the list's order: a plugin project's plan from its own directory, the registry the target's.

projects(root => $dir, app_only => 0|1)

The projects a target verb runs over: the plugins' in deploy order - found by loading the application class (load_app) so its plugins register them - then the application's own. Each is { name, dir, plan_file, conf, plan, plugin => 0|1 }. With app_only the application's alone, and nothing is loaded.

app_project(root => $dir, conf => \%conf?, plan_file => $file?)

The application's own project entry.

project_list(\%app_project, \@plugin_projects)

The plugins' projects then the application's, checking the one policy the arrangement needs: with plugin projects the registry keeps its default name ("The registry keeps its default name" in Punk::Plugin::Sqitch).

app_class_for($root)

The application class named in app.psgi, without running it.

load_app($root)

Load that class - lib/ under the root on @INC - with the boot check held off, so its plugins register their projects. Croaks naming the class when it will not load.

target_abs($target, $root)

A SQLite target made absolute against the root, for a run whose working directory is a plugin's project; other engines' targets already are.

registry_for(\%db, $target, \%conf)

Where Sqitch keeps the registry for a target, as its engines decide it: for SQLite a file - the registry name (sqitch unless the conf says otherwise) in place of the target's basename, keeping its extension, so app.db gives sqitch.db beside it, an absolute registry as is; for PostgreSQL a schema in the target database; for MySQL a database on the same server. { engine, name, kind, path?, connect => [@dbi_args] }.

SCAFFOLDING

project_name_for($app_class)

A Sqitch project name for an application class: lowercased, :: to _, always inside Sqitch's [A-Za-z][\w-]*.

skeleton(meta => \%meta, engine => $engine, %opts)

The deploy, revert and verify scripts for a Punk::Model's table, from its metadata (table, fields, field, primary), for sqlite, pg or mysql. Types map integer, string, number and boolean; the primary key becomes the engine's autoincrement when it is an integer; required is NOT NULL; a type with no mapping is text with a comment asking for one. Options project, change and model fill the header. Returns { deploy, revert, verify }.

register_types($engine => \%types)

Punk::Sqitch->register_types(vertica => {
    integer => 'BIGINT', string => 'VARCHAR(4000)', number => 'FLOAT',
    boolean => 'BOOLEAN', primary_integer => 'IDENTITY PRIMARY KEY',
    txn => 0, false => 'FALSE' });

The skeleton's types for an engine: integer, string, number and boolean for Punk::Model's field types, primary_integer for the integer primary key's whole declaration, txn whether the scripts wrap in BEGIN/COMMIT, false the WHERE-nothing predicate. Replaces a built-in engine's as readily as it adds one.

register_client($engine => \%client)

Punk::Sqitch->register_client(mydb => { name => 'mydb-cli', args => ['--version'],
                                        min => '2.0', alternatives => ['mydb'] });

The client an engine deploys through: name on PATH, alternatives the other names to try, args what prints its version, min the oldest Sqitch accepts.

client_for($engine, \%conf)

The client binary the engine deploys through - Sqitch's default name, or [engine "x"] client from the conf - found on PATH and asked its version: { name, path, version, ok, why }. A sqlite3 older than 3.3.9, which Sqitch refuses, is not ok.

RUNNING SQITCH

run(verb => $verb, args => \@args, target => $target?, cwd => $dir?, verbosity => $n?)

One Sqitch command, in-process, through App::Sqitch::Command. For a verb that takes a target (deploy, revert, verify, status, log, check, checkout, rebase, upgrade, bundle) the target's URI is passed as --target unless the arguments already carry one, and its password as $SQITCH_PASSWORD for the duration. With cwd the command runs from that directory and the previous one is restored afterwards, whatever happens. Returns the exit code by Sqitch's own rules: 0, the exception's code for one of Sqitch's (a failed deploy is 2), 2 for a usage error, with the message on STDERR as punk sqitch: ....

In-process rather than exec sqitch because Punk::Command's main localises STDOUT and STDERR and returns an exit code, so a test of punk sqitch status reads both without spawning. The sqitch binary is not required on PATH; the engine's client is.

takes_target($verb)

Whether a verb accepts --target.

SEE ALSO

Punk::Command::Sqitch, App::Sqitch, sqitch, Punk.

AUTHOR

LNATION <email@lnation.org>

LICENSE AND COPYRIGHT

This software is Copyright (c) 2026 by LNATION <email@lnation.org>.

This is free software, licensed under:

The Artistic License 2.0 (GPL Compatible)