After seven years of development and hundreds of production database deployments, I finally decide it was time to release Sqitch v1.0.0, and today’s the day. I took the opportunity to resolve all known bugs in previous releases, so there’s no new functionality since v0.9999. Still, given the typical attention given to a significant milestone release like 1.0.0, my employer published a blog post describing a bit of the history and philosophy behind Sqitch.

The new site goes into great detail describing how to install Sqitch, but the important links are:

• CPAN — Install Sqitch from CPAN
• Docker — Run Sqitch from a Docker container
• Homebrew — Homebrew Sqitch on macOS
• GitHub — Sqitch releases on GitHub

Thanks to everyone who helped get Sqitch to this point, I appreciate it tremendously. I’m especially grateful to:

• Kurk Spendlove, my manager when I started at iovation in late 2011, who gave me the space and the time to figure this thing out for most of 2012.
• Ștefan Suciu has contributed a ton, notably Firebird support.
• Shawn Sorichetti has lately taken on the task of merge manager, reviewing and approving all recent Sqitch pull requests.
• Johan Wärlander contributed Exasol support.
• Luca Ferrari and @BeaData provided the Italian localization.
• Thomas Iguchi provided the German localization.
• Dave Rolsky and Curtis Poe, as well as Ștefan and Shawn, who have agreed to help manage the project, ensuring a stable and productive future for the project.
• Craig Brewster gave me super useful feedback on the new site design and logos.
• Stephen Lovell and Gaston Figueroa helped me diagnose and fix issues with the new site in MSIE/Edge.
• The dozens of other contributors who have helped make Sqitch a success.
• The countless people who have reported, commented on, and helped diagnose Sqitch issues.

Thanks a million for all your help and support!

I was traveling last week, and knowing I would be offline a fair bit, not to mention seriously jet-lagged, I put my hacking efforts into getting MySQL support into Sqitch. I merged it in yesterday; check out the tutorial if you’re interested in it. I expect to release v0.980 with the MySQL support in a couple of weeks; testing and feedback would most appreciated.

There is a caveat, though: it requires MySQL v5.6.4. So if you’re stuck with an older MySQL, it won’t work. There are two reasons to require v5.6.4:

• The microsecond precision support in DATETIME values, added in v5.6.4. This makes it much easier to keep things in the proper order (deployments usually take less than a second).
• The SIGNAL functionality, introduced in v5.5. This allows the schema to mock a check constraint in the Sqitch database, as well as make it much easier to write verify tests (as described in the tutorial and figured out on StackOverflow).

But if you can afford to take advantage of a relatively modern MySQL, give it a shot!

The next release also makes a backwards-incompatible change to the SQLite engine: the default Sqitch database is no longer $db_dir/$db_name-sqitch.$suffix, but $db_dir/sqitch.$suffix. In other words, if you were deploying to a db named /var/db/myapp.db, Sqitch previously kept its metadata in /var/db/myapp-sqitch.db, but now will keep it in /var/db/sqitch.db. This is to make it more like the other engines (MySQL defaults to a database named “sqitch”, and Postgres and Oracle default to a schema named “sqitch”).

It’s also useful if you use the SQLite ATTACHDATABASE command to manage multiple database files in a single project. In that case, you will want to use the same metadata file for all the databases. Keep them all in the same directory with the same suffix and you get just that with the default sqitch database.

If you’d like it to have a different name, use sqitch config core.sqlite.sqitch_db $name to configure it. This will be useful if you don’t want to use the same Sqitch database to manage multiple databases, or if you do, but they live in different directories.

I haven’t released this change yet, and I am not a big-time SQLite user. So if this makes no sense, please comment on this issue. It’ll be a couple of weeks before I release v0.980, so there is time to reverse if if there’s consensus that it’s a bad idea.

But given another idea I’ve had, I suspect it will be okay. The idea is to expand on the concept of a Sqitch “target” by giving it its own command and configuration settings. Basically, it would be sort of like Git remotes: use URIs to specify database connection and parameter info (such as the sqitch database name for SQLite). These can be passed to database-touching commands, such as deploy, revert, log, and the like. They can also be given names and stored in the configuration file. The upshot is that it would enable invocations such as

sqitch deploy production
sqitch log qa
sqitch status pg://localhost/flipr?sqitch_schema=meta

See the GitHub issue for a fuller description of this feature. I’m certain that this would be useful at work, as we have a limited number of databases that we deploy each Sqitch project to, and it’s more of a PITA for my co-workers to remember to use different values for the --db-host, --db-user, --db-name and friends options. The project itself would just store the named list of relevant deployment targets.

And it alleviates the issue of specifying a different Sqitch database on SQLite or MySQL, as one can just create a named target that specifies it in the URI.

Not sure when I will get to this feature, though. I think it would be great to have, and maybe iovation would want me to spend some time on it in the next couple of months. But it might also be a great place for someone else to get started adding functionality to Sqitch.

Oh, and before I forget: it looks like Sqitch might soon get CUBRID support, too, thanks to Ștefan Suciu. Stay tuned!

Looking for the comments? Try the old layout.

Just a quick post to announce that I’ve set up a Sqitch Google Group. I’ve been getting a lot more email about it lately, and a fair bit of it should be shared more generally. So if you’re interested in Sqitch, sign up! (Don’t like web forums? Me neither. Feel free to subscribe by email, instead.)

Looking for the comments? Try the old layout.

I found myself with a little unexpected time at work recently, and since we use Oracle (for a few more months), I decided to port Sqitch. Last night, I released v0.970 with full support for Oracle. I did the development against an 11.2 VirtualBox VM, though I think it should work on 10g, as well.

Sqitch is available from the usual locations. For Oracle support, you’ll need the Instant Client, including SQL*Plus. Make sure you have $ORACLE_HOM set and you’ll be ready to install. Via CPAN, it’s

cpan install App::Sqitch DBD::Oracle

Via Homebrew:

brew tap theory/sqitch
brew install sqitch-oracle

Via ActiveState PPM, install ActivePerl, then run:

ppm install App-Sqitch DBD-Oracle

There are a few other minor tweaks and fixed in this release; check the release notes for details.

Want more? I will be giving a half-day tutorial, entitled “Agile Database Development,” on database development with Git, Sqitch, and pgTAP at on May 22 PGCon 2013 in Ottawa, Ontario. Come on up!

Looking for the comments? Try the old layout.

If Sqitch is to succeed, it needs to get into the hands of as many people as possible. That means making it easy to install for people who are not Perl hackers and don’t want to deal with CPAN. The Sqitch Homebrew Tap is my first public stab at that. It provides a series of “Formulas” for Homebrew users to easily download, build, and install Sqitch and all of its dependencies.

If you are one of these lucky people, here’s how to configure the Sqitch tap:

brew tap theory/sqitch

Now you can install the core Sqitch application:

brew install sqitch

That’s it. Make sure it works:

> sqitch --version
sqitch (App::Sqitch) 0.953

It won’t do you much good without support for your database, though. Currently, there is a build for PostgreSQL. Note that this requires the Homebrew core PostgreSQL server:

brew install sqitch_pg

Sqitch hasn’t been ported to other database engines yet, but once it is, expect other formulas to follow. But if you use PostgreSQL (or just want to experiment with it), you’re ready to rock! I suggest following along the tutorial, downloading, or taking in the latest iteration of the introductory presentation (video of an older version on Vimeo).

My thanks to IRC user “mistym” for the help and suggestions in getting this going. My Ruby is pretty much rusted through, soI could not have done it without the incredibly responsive help!

Looking for the comments? Try the old layout.

New today: Sqitch v0.950. There are a few bug fixes, but the most interesting new feature in this release is the verify command, as well as the complementary --verify option to the deploy command. The add command has created test scripts since the beginning; they were renamed verify in v0.940. In v0.950 these scripts are actually made useful.

The idea is simply to test that a deploy script did what it was supposed to do. Such a test should make no assumptions about data or state other than that affected by the deploy script, so that it can be run against a production database without doing any damage. If it finds that the deploy script failed, it should die.

This is easier than you might at first think. Got a Sqitch change that creates a table with two columns? Just SELECT from it:

SELECT user_id, name
  FROM user
 WHERE FALSE;

If the table does not exist, the query will die. Got a change that creates a function? Make sure it was created by checking a privilege:

SELECT has_function_privilege('insert_user(text, text)', 'execute');

PostgreSQL will throw an error if the function does not exist. Not running PostgreSQL? Well, you’re probably not using Sqitch yet, but if you were, you might force an error by dividing by zero. Here’s an example verifying that a schema exists:

SELECT 1/COUNT(*)
  FROM information_schema.schemata
 WHERE schema_name = 'myapp';

At this point, Sqitch doesn’t care at all what you put into your verify scripts. You just need to make sure that they indicate failure by throwing an error when passed to the database command-line client.

The best time to run a change verify script is right after deploying the change. The --verify option to the deploy command does just that. If a verify script fails, the deploy is considered to have failed. Here’s what failure looks like:

> sqitch deploy
Deploying changes to flipr_test
  + appschema ................. ok
  + users ..................... ok
  + insert_user ............... ok
  + change_pass @v1.0.0-dev1 .. ok
  + lists ..................... psql:verify/lists.sql:7: ERROR:  column "timestamp" does not exist
LINE 1: SELECT nickname, name, description, timestamp
                                            ^
Verify script "verify/lists.sql" failed.
not ok
Reverting all changes
  - change_pass @v1.0.0-dev1 .. ok
  - insert_user ............... ok
  - users ..................... ok
  - appschema ................. ok
Deploy failed

Good, right? In addition, you can always verify the state of a database using the verify command. It runs the verify scripts for all deployed changes. It also ensures that all the deployed changes were deployed in the same order as they’re listed in the plan, and that no changes are missing. The output is similar to that for deploy:

> sqitch verify
Verifying flipr_test
  * appschema ................. ok
  * users ..................... ok
  * insert_user ............... ok
  * change_pass @v1.0.0-dev1 .. ok
  * lists ..................... ok
  * insert_list ............... ok
  * delete_list ............... ok
  * flips ..................... ok
  * insert_flip ............... ok
  * delete_flip @v1.0.0-dev2 .. ok
  * pgcrypto .................. ok
  * insert_user ............... ok
  * change_pass ............... ok
Verify successful

Don’t want verification tests/scripts? Use --no-verify when you call sqitch add and none will be created. Or tell it never to create verify scripts by setting the turning off the add.with_verify option:

sqitch config --bool add.with_verify no

If you somehow run deploy --verify or verify anyway, Sqitch will emit a warning for any changes without verify scripts, but won’t consider them failures.

Up Front Dependency Checking

The other significant change in v0.950 is that the deploy and revert commands (and, by extension the rebase command) now verify that dependencies have been checked before deploying or reverting anything. Previously, Sqitch checked the dependencies for each change before deploying it, but it makes much more sense to check them for all changes to be deployed before doing anything at all. This reduces the chances of unexpected reversions.

Still hacking on Sqitch, of course, though nearly all the commands I initially envisioned are done. Next up, I plan to finally implement support for SQLite, add a few more commands to simplify plan file modification, and to create a new site, since the current site is woefully out-of-date. Until then, though, check out this presentation and, of course, the tutorial.

Looking for the comments? Try the old layout.

I’m pleased to announce the release of Sqitch v0.940. The focus of this release? Sanity.

I’ve been doing a lot of Sqitch-based database development at work. Overall it has worked quite well. Except for one thing: often the order in which changes would be arranged would change from one run to the next. Oy.

Out of Order

The reason? The plan parser would perform a topological sort of all the changes between tags based on their dependencies. I’ve been careful, for the most part, to keep my changes in the proper order in our plan files, but the topological sort would often pick a different order. Still valid in terms of dependency ordering, but different from the plan file.

Given the same inputs, the sort always produced the same order. However, whenever I added a new changes (and I do that all the time while developing), there would then be a new input, which could result in a completely different order. The downside is that I would add a change, run sqitch deploy, and it would die because it thought something needed to be deployed that had already been deployed, simply because it sorted it to come after an undeployed change. So annoying.. It also caused problems in for production deployments, because different machines with different Perls would sort the plans in different ways.

So I re-wrote the sorting part of the the plan parser so that it no longer sorts. The list of changes is now always identical to the order in the plan file. It still checks dependencies, of course, only now it throws an exception if it finds an ordering problem, rather than re-ordering for you. I’ve made an effort to tell the user how to move things around in the plan file to fix ordering issues, so hopefully everything will be less mysterious.

Of course, many will never use dependencies, in which case this change has effect. But it was important to me, as I like to specify dependencies as much as I can, for my own sanity.

See? There’s that theme!

Everyone has a Mom

Speaking of ordering, as we have been starting to do production deployments, I realized that my previous notion to allow developers to reorder changes in the plan file without rebuilding databases was a mistake. It was too easy for someone to deploy to an existing database and miss changes because there was nothing to notice that changes had not been deployed. This was especially a problem before I addressed the ordering issue.

Even with ordering fixed, I thought about how git push works, and realized that it was much more important to make sure things really were consistent than it was to make things slightly more convenient for developers.

So I changed the way change IDs are generated. The text hashed for IDs now includes the ID of the parent change (if there is one), the change dependencies, and the change note. If any of these things change, the ID of the change will change. So they might change a lot during development, while one moves things around, changes dependencies, and tweaks the description. But the advantage is for production, where things have to be deployed exactly right, with no modifications, or else the deploy will fail. This is sort of like requiring all Git merges to be fast-forwarded, and philosophically in line with the Git practice of never changing commits after they’re pushed to a remote repository accessible to others.

Curious what text is hashed for the IDs? Check out the new show command!

Rebase

As a database hacker, I still need things to be relatively convenient for iterative development. So I’ve also added the rebase command. It’s simple, really: It just does a revert and a deploy a single command. I’m doing this all day long, so I’m happy to save myself a few steps. It’s also nice that I can do sqitch rebase @HEAD^ to revert and re-apply the latest change over and over again without fear that it will fail because of an ordering problem. But I already mentioned that, didn’t I?

Order Up

Well, mostly. Another ordering issue I addressed was for the revert --to option. It used to be that it would find the change to revert to in the plan, and revert based on the plan order. (And did I mention that said order might have changed since the last deploy?) v0.940 now searches the database for the revert target. Not only that, the full list of changes to deploy to revert to the target is also returned from the database. In fact, the revert no longer consults the plan file at all. This is great if you’ve re-ordered things, because the revert will always be the reverse order of the previous deploy. Even if IDs have changed, revert will find the changes to revert by name. It will only fail if you’ve removed the revert script for a change.

So simple, conceptually: revert reverts in the proper order based on what was deployed before. deploy deploys based on the order in the plan.

Not @FIRST, Not @LAST

As a result of the improved intelligence of revert, I have also deprecated the @FIRST and @LAST symbolic tags. These tags forced a search of the database, but were mainly used for revert. Now that revert always searches the database, there’s nothing to force. They’re still around for backward compatibility, but no longer documented. Use @ROOT and @HEAD, instead.

Not Over

So lots of big changes, including some compatibility changes. But I’ve tried hard to make them as transparent as possible (old IDs will automatically be updated by deploy). So take it for a spin!

Meanwhile, I still have quite a few other improvements I need to make. On my short list are:

• Checking all dependencies before deploying or reverting any changes.
• Adding the verify command to run acceptance tests.
• Adding a --no-run option to deploy so that existing databases can be upgraded to Sqitch.
• Adding a check command to sanity-check a plan, scripts, and a database.

Looking for the comments? Try the old layout.

It has been a while since I last blogged about Sqitch. The silence is in part due to the fact that I’ve moved from full-time Sqitch development to actually putting it to use building databases at work. This is exciting, because it needs the real-world experience to grow up.

That’s not to say that nothing has happened with Sqitch. I’ve just released v0.931 which includes a bunch of improvement since I wrote about v0.90. First a couple of the minor things:

• Sqitch now checks dependencies before reverting, and dies if they would be broken by the revert. This change, introduced in v0.91, required that the dependencies be moved to their own table, so if you’ve been messing with an earlier version of Sqitch, you’ll have to rebuild the database. Sorry about that.
• I fixed a bunch of Windows-related issues, including finding the current user’s full name, correctly setting the locale for displaying dates and times, executing shell commands, and passing tests. The awesome ActiveState PPM Index has been invaluable in tracking these issues down.
• Added the bundle command. All it does is copy your project configuration file, plan, and deploy, revert, and test scripts to a directory you identify. The purpose is to be able to export the project into a directory structure suitable for distribution in a tarball, RPM, or whatever. That my not sound incredibly useful, since copying files is no big deal. However, the long-term plan is to add VCS support to Sqitch, which would entail fetching scripts from various places in VCS history. At that point, it will be essential to use bundle to do the export, so that scripts are properly exported from the VCS history. That said, I’m actually using it already to build RPMs. Useful already!

Symbolic References

And now the more immediately useful changes. First, I added new symbolic tags, @FIRST and @LAST. These represent the first and last changes currently deployed to a database, respectively. These complement the existing @ROOT and @HEAD symbolic tags, which represent the first and last changes listed in the plan. The distinction is important: The change plan vs actual deployments to a database.

The addition of @FIRST and @LAST may not sounds like much, but there’s more.

I also added forward and reverse change reference modifiers ^ and ~. The basic idea was stolen from Git Revisions, though the semantics vary. For Sqitch changes, ^ appended to a name or tag means “the change before this change,” while ~ means “the change after this change”. I find ^ most useful when doing development, where I’m constantly deploying and reverting a change as I work. Here’s how I do that revert:

sqitch revert --to @LAST^

That means “revert to the change before the last change”, or simply “revert the last change”. If I want to revert two changes, I use two ^s:

sqitch revert --to @LAST^^

To go back any further, I need to use an integer with the ^. Here’s how to revert the last four changes deployed to the database:

sqitch revert --to @LAST^4

The cool thing about this is that I don’t have to remember the name of the change to revert, as was previously required. And of course, if I just wanted to deploy two changes since the last deployment, I would use ~~:

sqitch deploy --to @LAST~~

Nice, right? One thing to bear in mind, as I was reminded while giving a [Sqitch presentation][slides] to PDXPUG: Changes are deployed in a sequence. You can think of them as a linked list. So this command:

sqitch revert @LAST^^

Does not mean to revert the second-to-last change, leaving the two after it. It will revert the last change and the penultimate change. This is why I actually encourage the use of the --to option, to emphasize that you’re deploying or reverting all changes to the named point, rather than deploying or reverting the named point in isolation. Sqitch simply doesn’t do that.

Internationalize Me

One more change. With today’s release of v0.931, there is now proper internationalization support in Sqitch. The code has been localized for a long time, but there was no infrastructure for internationalizing. Now there is, and I’ve stubbed out files for translating Sqitch messages into French and German. Adding others is easy.

If you’re interested in translating Sqitch’s messages (only 163 of them, should be quick!), just fork Sqitch, juice up your favorite gettext editor, and start editing. Let me know if you need a language file generated; I’ve built the tools to do it easily with dzil, but haven’t released them yet. Look for a post about that later in the week.

Presentation

Oh, and that PDXPUG presentation? Here are the slides (also for download and on Slideshare). Enjoy!

Looking for the comments? Try the old layout.

Sqitch v0.90 dropped last week (updated to v0.902 today). The focus of this release of the “sane database change management” app was cross-project dependencies. Jim Nasby first put the idea for this feature into my head, and then I discovered that our first Sqitch-using project at work needs it, so blame them.

Depend On It

Earlier versions of Sqitch allow you to declare dependencies on other changes within a project. For example, if your project has a change named users_table, you can create a new change that requires it like so:

sqitch add --requires users_table widgets_table

As of v0.90, you can also require a change from different Sqitch project. Say that you have a project that installs a bunch of utility functions, and that you want to require it in your current Sqitch project. To do so, just prepend the project name to the name of the change you want to require:

sqitch add --requires utils:uuidgen widgets_table

When you go to deploy your project, Sqitch will not deploy the widgets_table change if the uuidgen change from the utils project is not already present.

Sqitch discriminates projects simply by name, as required since v0.80. When you initialize a new Sqitch project, you have to declare its name, too:

siqtch init --name utils

I’ve wondered a bit as to whether that was sufficient. Within a small organization, it’s probably no big deal, as there is unlikely to be much namespace overlap. But thinking longer term, I could foresee folks developing and distributing interdependent open-source Sqitch projects. And without a central name registry, conflicts are likely to pop up. To a certain degree, the risks can be minimized by comparing project URIs, but that works only for project registration, not dependency specification. But perhaps it’s enough. Thoughts?

It’s All Relative

Next up I plan to implement the SQLite support and the bundle command. But first, I want to support relative change specifications. Changes have an order, both in the plan and as deployed to the database. I want to be able to specify relative changes, kind of like you can specify relative commits in Git. So, if you want to revert just one change, you could say something like this:

sqitch revert HEAD^

And that would revert one change. I also think the ability to specify later changes might be useful. So if you wanted to deploy to the change after change foo, you could say something like:

sqitch deploy foo+

You can use ^ or + any number of times, or specify numbers for them. These would both revert three changes:

sqitch revert HEAD^^^
sqitch revert HEAD^3

I like ^ because of its use in Git, although perhaps ~ is more appropriate (Sqitch does not have concepts of branching or multiple parent changes). But + is not a great complement. Maybe - and + would be better, if a bit less familiar? Or maybe there is a better complement to ^ or ~ I haven’t thought of? (I don’t want to use characters that have special meaning in the shell, like <>, if I can avoid it.) Suggestions greatly appreciated.

Oops

A discovered a bug late in the development of v0.90. Well, not so much a bug as an oversight: Sqitch does not validate dependencies in the revert command. That means it’s possible to revert a change without error when some other change depends on it. Oops. Alas, fixing this issue is not exactly trivial, but it’s something I will have to give attention to soon. While I’m at it, I will probably make dependency failures fail earlier. Watch for those fixes soon.

And You?

Still would love help getting a dzil plugin to build Local::TextDomain l01n files. I suspect this would take a knowledgable Dist::Zilla user a couple of hours to do. (And thanks to @notbenh and @RJBS for getting Sqitch on Dist::Zilla!) And if anyone really wanted to dig into Sqitch, Implementing a bundle command would be a great place to start.

Or just give it a try! You can install it from CPAN with cpan App::Sqitch. Read the tutorial for an overview of what Sqitch is and how it works. Thanks!

Looking for the comments? Try the old layout.

Last night, I uploaded Sqitch v0.80, the latest dev release of the simple database change management system I’ve been working on. I’m kind of stunned by the sheer number of changes in this release, given that the interface has not changed much. Mainly, there’s more you in this version. That is, like Git, the first thing you’ll want to do after installing Git is tell it who you are:

> sqitch config --user user.name 'Marge N. O’Vera'
> sqitch config --user user.email 'marge@example.com'

This information is now recorded for every change added to a project plan, as well as every commit to the database (deploys, reverts, and failures). If you don’t tell Sqitch who you are, it will try to guess, but you might not like who it finds.

Changes and tags now also require a note to be associated with them, kind of like a Git commit message. This allows a bit more context to be provided about a change or tag, since the name may not be sufficient. All of this is recorded in the plan file, which makes it harder to edit by hand, since the lines are so much longer now. An example:

%syntax-version=1.0.0-b1
%project=flipr
%uri=https://github.com/theory/sqitch-intro/

appuser 2012-08-01T15:04:13Z Marge N. O’Vera <marge@example.com> # App database user with limited permissions.
users [:appuser] 2012-08-01T15:36:00Z Marge N. O’Vera <marge@example.com> # Creates table to track our users.
insert_user [:users :appuser] 2012-08-01T15:41:17Z Marge N. O’Vera <marge@example.com> # Creates a function to insert a user.
change_pass [:users :appuser] 2012-08-01T15:41:46Z Marge N. O’Vera <marge@example.com> # Creates a function to change a user password.
@v1.0.0-dev1 2012-08-01T15:48:04Z Marge N. O’Vera <marge@example.com> # Tag v1.0.0-dev1.

But each change and tag is still on a single line, so it’s not too bad if you absolutely must edit it. Still, I expect to discourage that in favor of adding more commands for manipulating it (adding and removing dependencies, changing the note, etc.).

Given all this data, the output of the log command has expanded quite a lot. Here’s an example from the tutorial’s example project:

On database flipr_test
Deploy 7ad1cc6d1706c559dceb3101e7c21786dc7d7b4c
Name:      change_pass
Committer: Marge N. O’Vera 
Date:      2012-08-01 22:20:54 +0200

    Change change_pass to use pgcrypto.

Deploy 799ecd26730a684cf02a889c30371a0af55150cc
Name:      insert_user
Committer: Marge N. O’Vera 
Date:      2012-08-01 22:20:54 +0200

    Change insert_user to use pgcrypto.

Revert 799ecd26730a684cf02a889c30371a0af55150cc
Name:      insert_user
Committer: Marge N. O’Vera 
Date:      2012-08-01 22:20:52 +0200

    Change insert_user to use pgcrypto.

Revert 7ad1cc6d1706c559dceb3101e7c21786dc7d7b4c
Name:      change_pass
Committer: Marge N. O’Vera 
Date:      2012-08-01 22:20:52 +0200

    Change change_pass to use pgcrypto.

Deploy 7ad1cc6d1706c559dceb3101e7c21786dc7d7b4c
Name:      change_pass
Committer: Marge N. O’Vera 
Date:      2012-08-01 22:20:46 +0200

    Change change_pass to use pgcrypto.

Deploy 799ecd26730a684cf02a889c30371a0af55150cc
Name:      insert_user
Committer: Marge N. O’Vera 
Date:      2012-08-01 22:20:46 +0200

    Change insert_user to use pgcrypto.

Note the use of color to identify the event type: green for deploys and blue for reverts. Failures appear in red. Not sure I like it yet, but I think it might be useful. We’ll see.

Back to the plan. Notice that it now also includes pragmas for a project name and URI. Those lines again:

%syntax-version=1.0.0-b1
%project=flipr
%uri=https://github.com/theory/sqitch-intro/

The project name is required when initializing a Sqitch project, but the URI is optional (at least for now). The point of these data points is double:

• The project name is used (along with the current timestamp and your name and email address) when hashing changes and tags to generate IDs. This ensures that the IDs are likely to be globally unique.
• In the future, you will be able to declare cross-project dependencies.

The second point is the more important. The plan is to require the name of a project before the : in a dependency. For example, if I wanted to require the insert_user change from the “flipr” project plan above, I would declare it as flipr:insert_user. Sqitch will then know to check for it. I will be adding this pretty soon, since it requires some database changes and we’re going to need it at work. The need for database changes is also why v0.80 is still a dev release. (However I don’t expect the plan format to change beyond this tweak to dependency specification.)

Beyond that, next steps include:

• Creating an RPM targeting work’s servers. This will probably not be public, though I might add the spec file to the public project.
• Starting to use Sqitch for some work projects. This will be the first real-world use, which I deem essential for proving the technology. I hope that it does not lead to any more radical redesigns. :–)
• Implement the SQLite interface to iron out any kinks in the engine API.
• Switch to Dist::Zilla for building the distribution. I would love a volunteer to help with this; I expect it to be simple for someone well-versed in Dist::Zilla.
• Add support for localization. Sqitch already uses Locale::TextDomain throughout, so it’s localization-ready. We just need the tools put in place as described in the dzil ticket. Again, I would love help with this.
• Implement the Bundle command. Should be pretty simple, since, for now at least, all it does is copy files and directories.
• Add VCS integration. This is less important than it once was, but will still help a lot when working with Sqitch within a VCS. The bundle command would also need to be updated, once this work was done.

But even with all that, I think that Sqitch is finally ready for some serious tire-kicking. To get started, skim the tutorial and take it for a spin (install it by running cpan DWHEELER/App-Sqitch-0.80-TRIAL.tar.gz). Let me know what you like, what you don’t like, and let’s have a discussion about it.

Oh, and for discussions, where should I set up a mail list? Google Groups? Someplace else?

Looking for the comments? Try the old layout.

Just uploaded Sqitch v0.70 and v0.71. The big change is the introduction of the log command, which allows one to view the deployment history in a database. All events are logged and searchable, including deploys, failed deploys, and reverts. Unlike most other database migration systems, Sqitch has the whole history, so even if you revert back to the very beginning, there is still a record of everything that happened.

I stole most of the interface for the log command from git-log, including:

• Colorized output
• Searching against change and committer names via regular expressions
• A variety of formatting options (“full”, “long”, “medium”, “oneline”, etc.)
• Extensible formatting with printf-style codes

Here are a couple of examples searching the tutorial’s test database:

> sqitch -d flipr_test log -n 3
On database flipr_test
Deploy 18d7aab59bd0c914a561dc324b1da5549605c376
Name:   change_pass
Date:   2012-07-07 13:26:30 +0200

Deploy 87b4e131897ec370d78be177a3f91fdc877a2515
Name:   insert_user
Date:   2012-07-07 13:26:30 +0200

Deploy 20d9af30b97a3071dce12d91665dcd6237265d60
Name:   pgcrypto
Date:   2012-07-07 13:26:30 +0200

> sqitch -d flipr_test log -n 6 --format oneline --abbrev 7
On database flipr_test
18d7aab deploy change_pass
87b4e13 deploy insert_user
20d9af3 deploy pgcrypto
540359a deploy delete_flip
d4dce7d deploy insert_flip
b715d73 deploy flips

> sqitch -d flipr_test log -n 4 --event revert --event fail --format \
'format:%a %eed %{blue}C%{6}h%{reset}C - %c%non %{cldr:YYYY-MM-dd}d at %{cldr:h:mm a}d%n' 
On database flipr_test
theory reverted 9df095 - appuser
on 2012-07-07 at 1:26 PM

theory reverted 9df0959d078b - users
on 2012-07-07 at 1:26 PM

theory reverted 9df095131e25 - insert_user
on 2012-07-07 at 1:26 PM

theory reverted 9df09502c559 - change_pass
on 2012-07-07 at 1:26 PM

I’m pretty happy with this. Not sure how much it will be used, but it works great!

Looking for the comments? Try the old layout.

I’ve just released Sqitch v0.60. The main change is the implementation of the status command, which allows one to see the current deployment status of a database. An example from the updated tutorial:

> sqitch status
# On database flipr_test
# Change:   18d7aab59bd0c914a561dc324b1da5549605c376
# Name:     change_pass
# Deployed: 2012-07-07 13:23:42 +0200
# By:       theory
# 
Nothing to deploy (up-to-date)

If there are changes in the plan after the most recently deployed change, they would be listed like so:

> sqitch status
# On database flipr_test
# Change:   540359a3892d1476f9ca6ccf7d3f9993ac383b68
# Name:     delete_flip
# Tag:      @v1.0.0-dev2
# Deployed: 2012-07-06 19:31:14 +0200
# By:       theory
# 
Undeployed changes:
  * pgcrypto
  * insert_user
  * change_pass

You can also ask it to show the list of deployed changes and applied tags:

> sqitch status --show-tags --show-changes
# On database flipr_test
# Change:   18d7aab59bd0c914a561dc324b1da5549605c376
# Name:     change_pass
# Deployed: 2012-07-07 13:26:30 +0200
# By:       theory
# 
# Changes:
#   change_pass - 2012-07-07 13:26:30 +0200 - theory
#   insert_user - 2012-07-07 13:26:30 +0200 - theory
#   pgcrypto    - 2012-07-07 13:26:30 +0200 - theory
#   delete_flip - 2012-07-07 13:26:30 +0200 - theory
#   insert_flip - 2012-07-07 13:26:30 +0200 - theory
#   flips       - 2012-07-07 13:26:30 +0200 - theory
#   delete_list - 2012-07-07 13:26:30 +0200 - theory
#   insert_list - 2012-07-07 13:26:30 +0200 - theory
#   lists       - 2012-07-07 13:26:30 +0200 - theory
#   change_pass - 2012-07-07 13:26:30 +0200 - theory
#   insert_user - 2012-07-07 13:26:30 +0200 - theory
#   users       - 2012-07-07 13:26:30 +0200 - theory
#   appuser     - 2012-07-07 13:26:30 +0200 - theory
# 
# Tags:
#   @v1.0.0-dev2 - 2012-07-07 13:26:30 +0200 - theory
#   @v1.0.0-dev1 - 2012-07-07 13:26:30 +0200 - theory
# 
Nothing to deploy (up-to-date)

The --date-format option allows one to display the dates in a variety of formats, inspired by the git log --date option:

> sqitch status --date-format long
# On database flipr_test
# Change:   18d7aab59bd0c914a561dc324b1da5549605c376
# Name:     change_pass
# Deployed: 7 juillet 2012 13:26:30 CEST
# By:       theory
# 
Nothing to deploy (up-to-date)

Want to give it a try? Install it with cpan D/DW/DWHEELER/App-Sqitch-0.60-TRIAL.tar.gz and follow along the tutorial.

Now I’m off to add the log command, which shows a history of all deploys and reverts.

Looking for the comments? Try the old layout.

This week, I released v0.50 of Sqitch, the database change management app I’ve been working on for the last couple of months. Those interested in how it works should read the tutorial. A lot has changed since v0.30; here are some highlights:

• The plan file is now required. This can make merges more annoying, but thanks to a comment from Jakub Narębski, I discovered that Git can be configured to use a “union merge driver”, which seems to simplify things a great deal. See the tutorial for a detailed example.
• The plan now consists solely of a list of changes, roughly analogous to Git commits. Tags are simply pointers to specific changes.
• Dependencies are now specified in the plan file, rather than in the deployment scripts. Once the plan file became required, this seemed like the much more obvious place for them.
• The plan file now goes into the top-level directory of a project (which defaults to the current directory, assumed to be the top level directory of a VCS project), while the configuration file goes into the current directory. This allows one to have multiple top-level directories for different database engines, each with its own plan, and a single configuration file for them all.

Seems like a short list, but in reality, this release is the first I would call almost usable. Most of the core functionality and infrastructure is in place, and the architectural designs have been finalized. There should be much less flux in how things work from here on in, though this is still very much a developer release. Things might still change, so I’m being conservative and not doing a “stable” release just yet.

What works

So what commands actually work at this point? All of the most important functional ones:

• sqitch init – Initialize a Sqitch project. Creates the project configuration file, a plan file, and directories for deploy, revert, and test scripts
• sqitch config – Configure Sqitch. Uses the same configuration format as Git, including cascading local, user, and system-wide configuration files
• sqitch help – Get documentation for specific commands
• sqitch add – Add a new change to the plan. Generates deploy, revert, and test scripts based on user-modifiable templates
• sqitch tag – Tag the latest change in the plan, or show a list of existing tags
• sqitch deploy – Deploy changes to a database. Includes a --mode option to control how to revert changes in the event of a deploy failure (not at all, to last tag, or to starting point)
• sqitch revert – Revert changes from a database
• sqitch rework – Copy and modify a previous change

Currently, only PostgreSQL is supported by deploy and revert; I will at least add SQLite support soon.

The rework command is my solution to the problem of code duplication. It does not (yet) rely on VCS history, so it still duplicates code. However, it does so in such a way that it is still easier to see what has changed, because the new files are actually used by the previous instance of the command, while the new one uses the existing files. So a diff command, while showing the new files in toto, actually shows what changed in the existing scripts, making it easier to follow. I think this is a decent compromise, to allow Sqitch to be used with or without a VCS, and without disabling the advantages of VCS integration in the future.

The only requirement for reworking a change is that there must be a tag on that change or a change following it. Sqitch uses that tag in the name of the files for the previous instance of the change, as well as in internal IDs, so it’s required to disambiguate the scripts and deployment records of the two instances. The assumption here is that tags are generally used when a project is released, as otherwise, if you were doing development, you would just go back and modify the change’s scripts directly, and revert and re-deploy to get the changes in your dev database. But once you tag, this is a sort of promise that nothing will be changed prior to the tag.

I modify change scripts a lot in my own database development projects. Naturally, I think it is important to be free to change deployment scripts however one likes while doing development, and also important to promise not to change them once they have been released. As long as tags are generally thought of as marking releases or other significant milestones, it seems a reasonable promise not to change anything that appears before a tag.

See the tutorial for a detailed example. In a future release, VCS integration will be added, and the duplicated files will be unnecessary, too. But the current approach has the advantage that it will work anywhere, VCS or no. The VCS support will be backward-compatible with this design (indeed, it depends on it).

Still To Do

I think I might hold off a bit on the VCS integration, since the rework command no longer requires it. There also needs to be support for database engines other than PostgreSQL. But otherwise, mostly what needs to be done is the informational commands, packaging, and testing:

• sqitch status – Show the current deployment status of a database
• sqitch log – Show the deploy and revert history of a database
• sqitch bundle – Bundle up the configuration, plan, and scripts for distribution packaging
• sqitch test – Test changes. Mostly hand-wavy; see below
• sqitch check – Validate a database deployment history against the plan

I will likely be working on the status and log commands next, as well as an SQLite engine, to make sure I have the engine encapsulation right.

Outstanding Questions

I’m still pondering some design decisions. Your thoughts and comments greatly appreciated.

• Sqitch now requires a URI, which is set in the local configuration file by the init command. If you don’t specify one, it just creates a UUID-based URI. The URI is required to make sure that changes have unique IDs across projects (a change may have the same name as in another project). But maybe this should be more flexible? Maybe, like Git, Sqitch should require a user name and email address, instead? They would have to be added to the change lines of the plan, which is what has given me pause up to now. It would be annoying to parse.

• How should testing work? When I do PostgreSQL stuff, I am of course rather keen on pgTAP. But I don’t think it makes sense to require a particular format of output or anything of that sort. It just wouldn’t be engine-neutral enough. So maybe test scripts should just run and considered passing if the engine client exits successfully, and failing if it exited unsuccessfully? That would allow one to use whatever testing was supported by the engine, although I would have to find some way to get pgTAP to make psql exit non-zero on failure.

  Another possibility is to require expected output files, and to diff them. I’m not too keen on this approach, as it makes it much more difficult to write tests to run on multiple engine versions and platforms, since the output might vary. It’s also more of a PITA to maintain separate test and expect files and keep them in sync. Still, it’s a tried-and-true approach.

Help Wanted

Contributions would be warmly welcomed. See the to-do list for what needs doing. Some highlights and additional items:

• Convert to Dist::Zilla
• Implement the Locale::TextDomain-based localization build. Should be done at distribution build time, not install time. Ideally, there would be a Dist::Zilla plugin to do it, based pattern implemented in this example Makefile (see also this README).
• The web site could use some updating, though I realize it will regularly need changing until most of the core development has completed and more documentation has been written.
• Handy with graphics? The project could use a logo. Possible themes: SQL, databases, change management, baby Sasquatch.
• Packaging. It would greatly help developers and system administrators who don’t do CPAN if they could just use their familiar OS installers to get Sqitch. So RPM, Debian package, Homebrew, BSD Ports, and Windows distribution support would be hugely appreciated.

Take it for a Spin!

Please do install the v0.51 developer release from the CPAN (run cpan D/DW/DWHEELER/App-Sqitch-0.51-TRIAL.tar.gz) and kick the tires a bit. Follow along the tutorial to get a feel for it, or even just review the tutorial example’s Git history to get a feel for it. And if there is something you want out of Sqitch that you don’t see, please feel free to file an issue with your suggestion.

Looking for the comments? Try the old layout.

After all of the thinking and rethinking about how to manage a Sqitch plan, I am just about done with all the changes to make it all work. One of the changes I’ve made is that tags are no longer objects that stand on their own between change steps, but are simply names the refer to specific change steps. Not only is this much more like how a VCS thinks of tags (basically another name for a single commit), but it also greatly simplifies the code for iterating over a plan and updating metadata in the database.

But now that a plan is, in its essence, just a list of “steps”, I’m wondering if I should change that term. I originally used the term “steps” because the original plan was to have a deploy work on a tag-to-tag basis, where a single tag could have a series of changes associated with it. By that model, each change was a “step” toward deploying the tag. If any of the steps for a single tag failed, they were all reverted.

But while one can still specify a tag as a deploy target (and optionally have it revert to an earlier tag one failure), it no longer makes sense to think of each change script as a step toward deploying a target. It’s just a change. Yes, as an object it has separate deploy, revert, and test scripts associated with it, but I’m thinking it still makes sense to call them “changes” instead of “steps.” Because they’re individual things, rather than collections of things that lead to some goal.

What do you think?

In other renaming news, I’m thinking of changing the default directory that stores the step/change scripts. Right now it’s sql (though you can make it whatever you want). The plan file goes into the current directory (assumed to be the root directory of your project), as does the local configuration file. So the usual setup is:

% find .
./sqitch.conf
./sqitch.plan
./sql/deploy/
./sql/revert/
./sql/test/

I’m thinking of changing this in two ways:

• Make the default location of the plan file be in the top-level script directory. This is because you might have different Sqitch change directories for different database platforms, each with its own plan file.
• Change the default top-level script directory to ..

As a result, the usual setup would be:

% find .
./sqitch.conf
./sqitch.plan
./deploy/
./revert/
./test/

If you still wanted the change scripts kept in all in a subdirectory, say db/, it would be:

% find .
./sqitch.conf
./db/sqitch.plan
./db/deploy/
./db/revert/
./db/test/

And if you have a project with, say, two sqitch deployment setups, one for PostgreSQL and one for SQLite, you might make it:

% find .
./sqitch.conf
./postgres/sqitch.plan
./postgres/deploy/
./postgres/revert/
./postgres/test/
./sqlite/sqitch.plan
./sqlite/deploy/
./sqlite/revert/
./sqlite/test/

This works because the configuration file has separate sections for each engine (PostgreSQL and SQLite), and so can be used for all the projects; only the --top-dir option would need to change to switch between them. Each engine has its own plan file.

And yeah, having written out here, I’m pretty convinced. What do you think? Comments welcome.

Looking for the comments? Try the old layout.

On reflection, the one thing that bothers me about the proposal to abandon the VCS in yesterday’s post is that things could be deployed out-of-order. Take these two sections from the example plan toward the end of the post:

# Some procedures.
+add_user :roles :users_table
+del_user :@alpha
-dr_evil
+upd_user :add_user !dr_evil
@beta     # woo!

+crypto
+add_user :roles :users_table :crypto
@gamma

If this was run on an empty database, it would be deployed in this order:

• +crypto
• +add_user
• +del_user
• -dr_evil
• +upd_user
• @beta
• @gamma

Notice that crypto would be deployed before the @beta tag, despite the fact that it appears in the plan after @beta. Yes, one would end up with the correct database, but the actual deployment varies from the plan in a way that might be disconcerting to a user. I don’t like it.

Another issue is that there is no way to go back to @beta here, because there are no previous copies of add_user in the database. In theory this probably would not often be a big deal, but in practice, hey, sometimes you just need to go back in time no matter what. Maybe you need to repeatedly test a data migration (as opposed to a DDL change). I can certainly imagine needing that flexibility. So much for my “insight.”

I’m back to thinking about VCS integration again. I know I keep going back and forth on this stuff. I apologize. I appreciate the thoughtful comments and feedback I’ve received, and am taking the time to try to get this stuff right so that I can stop thinking about it in the future. I really want to reduce the complexity of database change management, but retain flexibility for those who need it. So yeah, I keep taking two steps forward and one step back, but there is overall forward momentum (I have had to trash less code than I expected).

The Duplicative Pattern

Back to my original complaint from yesterday: how can the plan indicate where to look in the VCS history for a particular copy of a file? As a corollary: is is possible to also support folks not using a VCS (which was one of the advantages to yesterday’s proposal)? Let’s take this plan as a working example:

%plan-syntax-v1
+users_table
+add_user :users_table
+del_user :users_table
+upd_user :users_table

Let’s say that we need to fix a bug in add_user. First we have to add it to the plan again:

% sqitch add add_user
Error: Step "add_user" already exists. Add a tag to modify it.

So we can’t repeat a step within a tag (or, in this case, when there are no tags). Let’s tag it and try again:

% sqitch tag alpha
% sqitch add add_user -vv
notice: Copied sql/deploy/add_user.sql to sql/deploy/add_user@alpha.sql
notice: Copied sql/revert/add_user.sql to sql/revert/add_user@alpha.sql
notice: Copied sql/deploy/add_user.sql to sql/revert/add_user.sql
notice: Copied sql/test/add_user.sql to sql/test/add_user@alpha.sql
Backed up previous version of "add_user"
Added "add_user" step. Modify these files:
sql/deploy/add_user.sql
sql/revert/add_user.sql
sql/test/add_user.sql

I use added verbosity (-vv) here to show what files are copied for the “backup” (the “notice” lines). So now the plan looks like this:

%plan-syntax-v1
+users_table
+add_user :users_table
+del_user :users_table
+upd_user :users_table
@alpha

+add_user :add_user@alpha

Note how the new step implicitly requires the previous version of the step (as of @alpha), and thus all of its dependencies. This is a clean way to “upgrade” the step.

Now you can edit sql/deploy/add_user.sql to make your changes, starting with the existing code. You can also edit sql/test/add_user.sql in order to update the tests for the new version. You don’t need to edit sql/revert/add_user.sql unless your changes are not idempotent.

Of course, this pattern leads to all the code duplication I complained about before, but there is nothing to be done in the absence of a VCS. The advantage is that we retain a complete history, so we can go back and forth as much as we want, regardless of whether we’re updating an existing database or creating a new database. The only change I need to make to the plan syntax is to ban the use of @ in step and tag names. Probably a good idea, anyway.

By the way, if we omit the extra verbosity, the output would look like this:

% sqitch add add_user
Backed up previous version of "add_user"
Added "add_user" step. Modify these files:
sql/deploy/add_user.sql
sql/revert/add_user.sql
sql/test/add_user.sql

Other than the “Backed up” line, the output is the same as for adding any other step. Maybe there would be something to say that the previous version was copied to the new version. But the point is to make it clear to the user what files are available to be edited.

VCS Integration

Let’s try again with a VCS. Starting at the same point as in the non-VCS example, we try to add a step

% sqitch add add_user
Error: Step "add_user" already exists. Add a tag to modify it.

So add the tag:

% sqitch tag alpha
% sqitch add add_user
Error: Cannot find reference to @alpha in the Git history. Please run `git tag alpha` to continue.

In order to be sure that we can use the VCS history, we need the tag there. Perhaps we could automatically add the tag in Git via sqitch tag, or have an option to do it. Either way, we need to have the same tag in the VCS so that we can travel back in time. So let’s do it:

% git tag alpha -am 'Tag alpha for Sqitch.'
% sqitch add add_user
Added "add_user" step. Modify these files:
sql/deploy/add_user.sql
sql/revert/add_user.sql
sql/test/add_user.sql

Note the lack of a “Backed up” line. It’s not necessary, since the code is already preserved in the Git history. Now we can edit the files in place and commit them to Git as usual. Sqitch will ask Git for the add_user step files as of the alpha tag when deploying the first instance of the step, and the current version for the latter. One can add add_user as many times as one likes, as long as there are always tags between instances.

Unbungled Bundle

Here’s the clincher for this iteration of this thing. My original plan for bundling (that is, packaging up the plan and change scripts for distribution outside the VCS) had the generation of a plan with different names than the plan in the VCS. That is, running:

% sqitch bundle

Against the above plan, the resulting plan in bundle/sqitch.plan would look something like this:

%plan-syntax-v1
+users_table
+add_user :users_table
+del_user :users_table
+upd_user :users_table
@alpha

+add_user_v2 :add_user

Note the add_user_v2 step there. Doesn’t exist in the original plan in the VCS, but was necessary in order to generate the change scripts for distribution bundling, so that all steps could be available for deployment outside the VCS:

% ls bundle/sql/deploy/add_user*
bundle/sql/deploy/add_user.sql
bundle/sql/deploy/add_user_v2.sql

This meant that databases deployed from the VCS would have a different deployment plan (and deployment history) than those deployed from a tarball distributed with the bundled plan. But hey, if we can create the files with the tag name in them for the non-VCS environment, we can do the same when bundling. So now, the bundled plan will be exactly the same as in the VCS, and the migration files will just be named with the tag, as appropriate:

% ls bundle/sql/deploy/add_user*
bundle/sql/deploy/add_user.sql
bundle/sql/deploy/add_user@alpha.sql

Much better. Much more consistent throughout. And must less stuff stored in the database to boot (no full deploy scripts copied to the DB).

Back to Work

So, I’m off to to continue modifying the plan code to support the syntax I proposed yesterday post, as well as the ability to have duplicate steps under different tags. Then I will start working on this proposal for how to name scripts and duplicate them.

That is, assuming that nothing else comes up to make me revise my plans again. Man, I sure hope not. This proposal nicely eliminates inconsistencies in the plan and deployment history regardless of whether deploying from a VCS, a bundled distribution, or a non-VCS project, the results should be the same. And it was those inconsistencies that I had been struggling with.

But hey, if I have overlooked something (again!), please do let me know.

Looking for the comments? Try the old layout.

I’ve been working on the parser for the proposed new deployment plan format, and spent a day thinking it wasn’t going to work at all. But by the end of the day yesterday, I was back on board with it. I think I’ve also figured out how to eliminate the VCS dependency (and thus a whole level of complication). So first, the plan format:

• Names of things (steps and tags) cannot start or end in punctuation characters
• @ indicates a tag
• + indicates a step to be deployed
• - indicates a step to be reverted
• # starts a comment
• : indicates a required step
• ! indicates a conflicting step
• % is for Sqitch directives

So, here’s an example derived from a previous example:

%plan-syntax-v1
+roles
+users_table
+dr_evil
@alpha

# Some procedures.
+add_user :roles :users_table
+del_user :@alpha
-dr_evil
+upd_user :add_user !dr_evil
@beta     # woo!

So we start with a directive for the version of the plan file (thanks for the suggestion, Tom Davis!). Then we have deployments of the roles, users_table, and dr_evil steps. After that, it’s tagged as alpha.

Next, we have a comment, then the deployment of the add_user step. It requires that the roles and users_table steps be deployed. Then we deploy del_user. It requires all steps as of the $alpha tag. Next we revert the dr_evil step. Why? Because the next line deploys upd_user, which conflicts with dr_evil (and requires add_user). And finally, we tag it as beta.

There are a number of things I like about these changes:

• Dependencies are spelled out in the plan file, rather than the deploy scripts. This allows the deploy scripts to have nothing special about them at all.

• We now have a way to explicitly revert a step as part of the overall plan. This is useful for ensuring that conflicts can be dealt with.

• We can deploy to any point in the plan by specifying a step:

  sqitch deploy add_user
  

  Or a tag:

  sqitch deploy @alpha
  

  For steps that are duplicated, we can disambiguate by specifying a tag:

  sqitch deploy dir_evil --as-of @alpha
  

  Naturally, this requires that a step not be repeated within the scope of a single tag.

Now, as for the VCS dependency, my impetus for this was to allow Sqitch to get earlier versions of a particular deploy script, so that it could be modified in place and redeployed to make changes inline, as described in an earlier post. However I’ve been troubled as to how to indicate in the plan where to look in the VCS history for a particular copy of a file. Yesterday, I had an insight: why do I need the earlier version of a particular deploy script at all? There are two situations where it would be used, assuming a plan that mentions the same step at two different points:

1. To run it as it existed at the first point, and to run it the second time as it exists at that time.
2. To run it in order to revert from the second point to the first.

As to the first, I could not think of a reason why that would be necessary. If I’m bootstrapping a new database, and the changes in that file are idempotent, is it really necessary to run the earlier version of the file at all? Maybe it is, but I could not think of one.

The second item is the bit I wanted, and I realized (thanks in part to prompt from Peter van Hardenberg while at PGCon) that I don’t need a VCS to get the script as it was at the time it was deployed. Instead, all I have to do is store the script in the database as it was at the time it was run. Boom, reversion time travel without a VCS.

As an example, take the plan above. Say we have a database that has been deployed all the way to @beta. Let’s add the add_user step again:

%plan-syntax-v1
+roles
+users_table
+dr_evil
@alpha

# Some procedures.
+add_user :roles :users_table
+del_user :@alpha
-dr_evil
+upd_user :add_user !dr_evil
@beta     # woo!

+crypto
+add_user :roles :users_table :crypto
@gamma

The last two lines are the new ones. At this point, the sql/deploy/add_user.sql script has been modified to fix a bug that now requires the crypto step. If we deploy to a new database, Sqitch will notice that the same step is listed twice and apply it only once. This works because, even though add_user is listed before pg_crypto, it is actually applied as described in its second declaration. So the run order would be:

• crypto
• add_user
• del_user
• upd_user

Note that this works because crypto declares no dependencies itself. If it did, it would be shuffled as appropriate. It would not work if it required, say, upd_user, as that would create a circular dependency (add_user — crypto — upd_user — add_user).

Now say we want to deploy the changes to the production database, which is currently on @beta. That simply runs:

• crypto
• add_user

If something went wrong, and we needed to revert, all Sqitch has to do is to read add_user from the database, as it was deployed previously, and run that. This will return the add_user function to its previous state. So, no duplication and no need for a VCS.

The one thing that scares me a bit is being able to properly detect circular dependencies in the plan parser. I think it will be pretty straight-forward for steps that require other steps. Less so for steps that require tags. Perhaps it will just have to convert a tag into an explicit dependence on all steps prior to that tag.

So, I think this will work. But I’m sure I must have missed something. If you notice it please enlighten me in the comments. And thanks for reading this far!

Looking for the comments? Try the old layout.