Author image Αριστοτέλης Παγκαλτζής


SQL::PatchDAG - A minimal DB schema patch manager



 use DBI;
 use SQL::PatchDAG;
 my $dbh = DBI->connect( ... );

 # setup:
 my $applied = $dbh->selectcol_arrayref( 'SELECT name FROM schemapatch' );
 my $patches = SQL::PatchDAG->from( 'patches', applied => $applied );

 # consistency check prior to application start:

 # application of missing patches:
 while ( my ( $name, $fn, $sql ) = $patches->get_next_unapplied ) {
   print $fn, "\n";
   $dbh->do( $sql );
   $dbh->do( 'INSERT INTO schemapatch (name) VALUES (?)', undef, $name );

 # helper script for creating new patches:
 $patches->run( @ARGV );


 -- preceding-patches = schemapatch
 CREATE TABLE schemapatch ( name VARCHAR(255) PRIMARY KEY );


This module manages a directory containing SQL files that must be run in a particular order. This order is specified implicitly by the contents of the files: each of them must contain a dependency declaration, which the module provides code to help maintain. This provides a merge-friendly way to introduce schema patches in a code base across multiple branches.

Patch application itself is up the caller. The module does not talk to a database.



Takes a list of key/value pairs and returns an instance with that configuration.

Typically you will use the from constructor rather than calling new directly.

The following parameters are available:


The name of the directory containing the SQL files.


The binmode to apply to filehandles when opening patch files.

Defaults to :unix.


A reference to an array listing the names of the patches which have been applied.

Defaults to an empty array.


A reference to hash of arrays, in which each key is the name of a known patch and its value is the list of patches it depends on.

Defaults to an empty hash.


Takes a directory name and a set of key/value pairs and returns an instance with that configuration. It will read the directory and parse the dependencies from each SQL file to populate the set of known patches.

Patches are expected to be have an .sql extension. The full basename of a patch is taken as its patch name.

Additionally, the directory may contain files with an .ignore extension, which is a convenience feature for switching branches during development. Normally if you create and apply a patch on one branch and then switch to another branch, your application will no longer start because the database contains a patch which is not in the patch directory on that branch. This is annoying if the application would work (mostly when the patch makes no incompatible changes to your schema and only adds things to it). To allow the application to start despite having applied the patch, you can create an ignore file with the same basename as the patch. When "from" finds such a file, an extraneous patch of the same name will be ignored rather than causing an error. (It is a good idea to add patches/*.ignore to your VCS ignore file to avoid accidentally committing these files.)


Throws an error if the sets of applied and known patches are not the same.


Returns either the name, filename and contents of the next patch to apply, or the empty list if there is no patch to apply.

Throws an error if there are extraneous patches.


Takes the name of a patch to create and a flag indicating whether to recreate an existing patch. Returns the path to the (re)created patch file.

An appropriate dependency declaration is computed and written to the file automatically.

When creating a new patch, the patch must not exist.

When recreating an existing patch, the patch must exist. Its dependecies are recomputed as if the patch had not existed and are rewritten to the file, but the rest of its contents is preserved.


Takes a list of paramters, and either calls "open" or "create" accordingly and then execs $EDITOR on the path returned, or else outputs a usage message and exits.

You would normally pass @ARGV to this method.


Returns the list of known patches.


Returns the list of applied patches.


Takes and returns a list of patch names, filtering out the ones which have been found in the patch directory.


Takes and returns a list of patch names, filtering out the ones which have already been applied.


Takes a patch name and returns its dependencies as a list.


Returns the value of the dir attribute.


Returns the value of the binmode attribute.


Reads the patch directory and returns its contents as a list.


Takes the name of a patch to open and a flag indicating whether to open it read-only or read/write. Returns the path to the patch and a filehandle with the configured binmode applied.

The filename must consist of only [a-z0-9_-] and may not begin with a [-].

When opening a patch read-only, it must already exist; when opening it read-write, it may be created.


Aristotle Pagaltzis <>


This software is copyright (c) 2020 by Aristotle Pagaltzis.

This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.