Name

sqitchtutorial - A tutorial introduction to Sqitch

Synopsis

sqitch *

Description

This tutorial explains how to create a sqitch-enabled project, use a VCS for deployment planning, and work with other developers to make sure changes remain in sync and in the proper order.

We'll start by creating new project from scratch, a fictional antisocial networking site called Flipr. All examples use Git as the VCS and PostgreSQL as the storage engine, but for the most part you can substitute other VCSes and database engines in the examples as appropriate.

Starting a New Project

Usually the first thing to do when starting a new project is to create a source code repository. So let's do that with Git:

> mkdir flipr
> cd flipr 
> git init .
Initialized empty Git repository in /flipr/.git/
> touch README.md
> git add .
> git commit -am 'Fist post!'

If you're a Git user and want to follow along the history, the repository used in these examples is on GitHub.

Now that we have a repository, let's get started with Sqitch.

> sqitch --engine pg init
Created sql/deploy
Created sql/revert
Created sql/test
Created sqitch.conf

Let's have a look at sqitch.conf:

[core]
    engine = pg
    # plan_file = sqitch.plan
    # sql_dir = sql
    # deploy_dir = sql/deploy
    # revert_dir = sql/revert
    # test_dir = sql/test
    # extension = sql
# [core "pg"]
    # db_name = 
    # client = psql
    # sqitch_schema = sqitch
    # password = 
    # port = 
    # host = 
    # username =

Pretty simple. It picked up on the fact that we're creating changes for the PostgreSQL engine, thanks to the --engine pg option, and saved it to the file. It also wrote a commented-out [core "pg"] section with all the available PostgreSQL engine-specific settings commented out and ready to be edited as appropriate.

By default, Sqitch will read sqitch.conf in the current directory for settings. But it will also read ~/.sqitch/sqitch.conf for global settings. Since PostgreSQL's psql client is not in the path on my system, let's go ahead an tell it globally where to find the client:

sqitch config --user core.pg.client /var/lib/pgsql/bin/psql

Have a look at ~/.sqitch/sqitch.conf and you'll see this:

[core "pg"]
    client = /var/lib/pgsql/bin/psql

Back to the repository. Let's commit these changes and start creating the database changes.

> git add .
> git commit -am 'Initialize Sqitch configuration.'
[master (root-commit) a22000d] Initialize Sqitch configuration.
 1 file changed, 16 insertions(+)
 create mode 100644 sqitch.conf

Our First Deployment

First, our project will need a database user. This is the application user, who will have only limited access to objects in the database. Run this command:

> sqitch add-step appuser
Created sql/deploy/appuser.sql
Created sql/revert/appuser.sql
Created sql/test/appuser.sql

The add-step command creates deploy and revert scripts that represent a new change step. Now we edit these files. The deploy script's job is to create the user. So we add this to sql/deploy/appuser.sql:

CREATE ROLE flipr WITH LOGIN;

The revert script's job is to precisely revert the change to the deploy script, so we add this to sql/revert/appuser.sql:

DROP ROLE flipr;

Now we can try deploying this change:

> createdb flipr_test
> sqitch --db-name flipr_test deploy --untracked
Adding metadata tables to flipr_test
Deploying HEAD+ to flipr_test
  + appuser

First Sqitch created the metadata tables used to track database changes. The structure and name of the metadata tables varies between databases (PostgreSQL uses a schema to namespace its metadata; MySQL and SQLite use a prefix). Next, Sqitch deploys changes. Here it is deploying something called HEAD+. Normally you would see a tag here, but since we've just added the files and they are not yet committed to Git, there is no tag. HEAD+ is a special tag reserved for use by Sqitch. It's enabled only by the use of the --untracked option, which allows Sqitch to find an deploy untracked changes.

With this change deployed, if you connect to the database, you'll be able to see the role:

> psql -d flipr_test -c '\du flipr'
           List of roles
 Role name | Attributes | Member of 
-----------+------------+-----------
 flipr     |            | {}

And we can also see how the deployment was recorded via the status command, which reads the metadata tables from the database:

> sqitch -d flipr_test status
# On database flipr_test
# Tag:  HEAD+
# Step: appuser
# Date: 2012-04-09 18:43:45
#
Nothing to deploy (up-to-date)

Let's make sure that we can revert the change:

> sqitch --db-name flipr_test revert
Reverting all changes from flipr_test
  - appuser

And now the user should be gone:

> psql -d flipr_test -c '\du flipr'
            List of roles
 Role name | Attributes | Member of 
-----------+------------+-----------

And the status message should reflect as much:

> sqitch -d flipr_test status --untracked
# On database flipr_test
# Nothing deployed.
#
# Changes not deployed:
# * HEAD+
#   appuser
#
Use "sqitch deploy --untracked" to deploy these changes

We've again used the --untracked option, otherwise the appuser step would not appear in the list of "Changes not yet deployed."

We still have a record that the change happened, visible via the log command:

> sqitch -d flipr_test log
step appuser reverted
By:   david
Date: 2012-04-09 18:45:47
Tag:  HEAD+

step appuser deployed
By:   david
Date: 2012-04-09 18:43:45
Tag:  HEAD+

Cool. Now let's commit it.

> git add .
> git commit -m 'Add app user.'
[master 36acafd] Add app user.
 2 files changed, 2 insertions(+)
 create mode 100644 sql/deploy/appuser.sql
 create mode 100644 sql/revert/appuser.sql

And then deploy again:

> sqitch --db-name flipr_test deploy
Deploying 36acafd to flipr_test
  + appuser

Notice we no longer need the --untracked option. That's because we've committed the step to Git, so Sqitch can read it from the Git history. And now the user should be back:

> psql -d flipr_test -c '\du flipr'
           List of roles
 Role name | Attributes | Member of 
-----------+------------+-----------
 flipr     |            | {}

When we look at the status, the deployment is tagged with the SHA1 of the commit:

> sqitch -d flipr_test status
# On database flipr_test
# Tag:  36acafd
# Step: appuser
# Date: 2012-04-09 18:52:42
#
Nothing to deploy (up-to-date)

More Configuration

I'm getting a little tired of always having to type --db-name flipr_test, aren't you? Let's just make that the default, shall we?

> sqitch config core.pg.db_name flipr_test

Now we can leave it out, unless we need to deploy to another database. Which we will, eventually, but at least our examples will be simpler from here on in, e.g.:

> sqitch status
# On database flipr_test
# Tag:  36acafd
# Step: appuser
# Date: 2012-04-09 18:52:42
#
Nothing to deploy (up-to-date)

Yay, that allows things to be a little more concise. Let's make some more changes!

Deploy with Dependency

Let's add another deployment, this time to create a table. Our app will need users, of course, so we'll create a table for them. First, add the new step:

> sqitch add-step users --requires appuser
Created sql/deploy/users.sql
Created sql/revert/users.sql
Created sql/test/users.sql

Now edit the scripts. In sql/deploy/users.sql, we put:

-- requires: appuser
BEGIN;
SET client_min_messages = 'warning';

CREATE TABLE users (
    nickname  TEXT        PRIMARY KEY,
    password  TEXT        NOT NULL,
    timestamp TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

GRANT SELECT ON users TO flipr;
COMMIT;

A few things to notice here. On the first line, we've declared a dependency on the appuser step. Although that step has already been added and committed, and therefore should always be applied before the users step, it's a good idea to be explicit about dependencies. This becomes especially important when committing a number of steps at one time, such as when merging a patch or a branch.

The syntax of the dependency declaration is simple: Before any other lines, create an SQL -- comment that simply uses the word "requires" followed by a colon and then a comma-delimited list of required steps. The requirements may also be listed on separate lines, providing they all start with -- requires:.

Notice that all of the SQL code is wrapped in a transaction. This is handy for PostgreSQL deployments, because DDLs are transactional. The upshot is that if any part of the deployment fails, the whole step fails. Such may work less-well for database engines that don't support transactional DDLs.

Just before the commit, the GRANT command grants SELECT access on the table to the flipr user created by the appuser step. This is why we need the dependency.

Now for the revert script. Add this to sql/revert/users.sql:

DROP TABLE users;

Couldn't be much simpler, right? Let's deploy this bad boy:

> sqitch deploy --untracked
Deploying HEAD+ to flipr_test
  + users

The users table should have been created:

> psql -d flipr_test -c '\d users'
                     Table "public.users"
  Column   |           Type           |       Modifiers        
-----------+--------------------------+------------------------
 nickname  | text                     | not null
 password  | text                     | not null
 timestamp | timestamp with time zone | not null default now()
Indexes:
    "users_pkey" PRIMARY KEY, btree (nickname)

Now have a look at the status:

> sqitch status
# On database flipr_test
# Tag:  HEAD+
# Step: users
# Date: 2012-04-09 20:41:31
#
Nothing to deploy (up-to-date)

Success! However, we've once again deployed an untracked change with no tags. In general, we want tags, so let's revert the change:

> sqitch revert --to 36acafd
Reverting HEAD+ from flipr_test
  - users

Note that we've used the --to option to revert only to the 36acafd tag, which represents the deployment of the appuser step. (You can see this tag by running sqitch log, in case you don't have your SHA1 hashes memorized). Now commit and deploy again:

> git add .
> git commit -am 'Add users table.'
[master fa650af] Add users table.
 2 files changed, 16 insertions(+)
 create mode 100644 sql/deploy/users.sql
 create mode 100644 sql/revert/users.sql
> sqitch deploy
Deploying fa650af to flipr_test
  + users

Looks good. Check the status:

> sqitch status --show tags
# On database flipr_test
# Tag:  fa650af
# Step: users
# Date: 2012-04-09 20:45:23
#
# Tags:
#    fa650af - 2012-04-09 20:45:23 - david
#    36acafd - 2012-04-09 19:04:50 - david
#
Nothing to deploy (up-to-date)

Note the use of --show tags, which adds the "Tags" section to the output, so that we can use a list of what tags were deployed, when, and by whom.

Add Two at Once

Let's add a couple more steps to add functions for managing users.

> sqitch add-step insert_user --requires users --requires appuser
Adding sql/deploy/insert_user.sql
       -- requires: users, appuser
Adding sql/revert/insert_user.sql

> sqitch add-step change_pass --requires users --requires appuser
Adding sql/deploy/change_pass.sql
       -- requires: users, appuser
Adding sql/revert/change_pass.sql

Here we've taken advantage of the --requires option to have Sqitch write the stubbed deploy file with the -- requires: line already filled in. Have a look:

> cat sql/deploy/insert_user.sql
-- requires: users, appuser

Nice, huh? Yeah, okay, so it's a little thing. Little things matter, am I right? Let's write the code. Here's what sql/deploy/insert_user.sql should look like:

-- requires: users, appuser

BEGIN;

CREATE OR REPLACE FUNCTION insert_user(
    nickname TEXT,
    password TEXT
) RETURNS VOID LANGUAGE SQL SECURITY DEFINER AS $$
    INSERT INTO users VALUES($1, md5($2));
$$;

GRANT EXECUTE ON FUNCTION insert_user(TEXT, TEXT) TO flipr;

COMMIT;

And sql/revert/insert_user.sql should look something like this:

DROP FUNCTION insert_user(TEXT, TEXT);

Now for change_pass; sql/deploy/change_pass.sql might look like this:

-- requires: users, appuser

BEGIN;

CREATE OR REPLACE FUNCTION change_pass(
    nick    TEXT,
    oldpass TEXT,
    newpass TEXT
) RETURNS BOOLEAN LANGUAGE plpgsql SECURITY DEFINER AS $$
BEGIN
    UPDATE users
       SET password = md5($3)
     WHERE nickname = $1
       AND password = md5($2);
    RETURN FOUND;
END;
$$;

GRANT EXECUTE ON FUNCTION change_pass(TEXT, TEXT, TEXT) TO flipr;
COMMIT;

And of course, its revert script, sql/revert/change_pass.sql, should look something like:

DROP FUNCTION change_pass(TEXT, TEXT, TEXT);

Test em out!

> sqitch deploy --untracked
Deploying HEAD+ to flipr_test
  + change_pass
  + insert_user

Do we have the functions?

> psql -d flipr_test -c '\df'
                                    List of functions
 Schema |    Name     | Result data type |          Argument data types          |  Type  
--------+-------------+------------------+---------------------------------------+--------
 public | change_pass | boolean          | nick text, oldpass text, newpass text | normal
 public | insert_user | void             | nickname text, password text          | normal

And what's the status?

> sqitch status --show tags
# On database flipr_test
# Tag:  HEAD+
# Step: insert_user
# Date: 2012-04-09 20:45:23
#
# Tags:
#    HEAD+   - 2012-04-09 20:45:23 - david
#    fa650af - 2012-04-09 20:45:23 - david
#    36acafd - 2012-04-09 19:04:50 - david
#
Nothing to deploy (up-to-date)

Looks good. Let's revert, commit, and re-deploy, as usual.

> sqitch revert --to HEAD
Reverting HEAD+ from flipr_test
  - insert_user
  - change_pass

Note the use of --to HEAD to revert untracked changes. We also could have explicitly used --to fa650af to revert to that tag, but HEAD is a nice shortcut. Let's do the commit and re-deploy dance:

> git add .
> git commit -m 'Add `insert_user()` and `change_pass()`.'
[master 803e6b8] Add `insert_user()` and `change_pass()`.
 4 files changed, 25 insertions(+)
 create mode 100644 sql/deploy/change_pass.sql
 create mode 100644 sql/deploy/insert_user.sql
 create mode 100644 sql/revert/change_pass.sql
 create mode 100644 sql/revert/insert_user.sql

> sqitch deploy
Deploying 803e6b8 to flipr_test
  + change_pass
  + insert_user

> sqitch status --show tags
# On database flipr_test
# Tag:  HEAD+
# Step: insert_user
# Date: 2012-04-09 20:45:23
#
# Tags:
#    803e6b8 - 2012-04-09 21:33:32 - david
#    fa650af - 2012-04-09 20:45:23 - david
#    36acafd - 2012-04-09 19:04:50 - david
#
Nothing to deploy (up-to-date)

Great, we're fully up-to-date!

Ship It!

Let's do a first release of our app. Let's call it 1.0.0-dev1 Since we want to have it go out with deployments tied to the release, let's tag it:

> git tag v1.0.0-dev1 -am 'Tag v1.0.0-dev1'

We can test deployment to make sure the tag gets picked up like so:

> createdb flipr_dev
> sqitch --db-name flipr_dev deploy
Deploying 36acafd to flipr_dev
  + appuser
Deploying fa650af to flipr_dev
  + users
Deploying 803e6b8/v1.0.0+dev1 to flipr_dev
  + insert_user
  + change_pass

All four steps were deployed, great! Let's have a look at the state:

> sqitch --db-name flipr_dev status --show tags
# On database flipr_dev
# Tags: 803e6b8, v1.0.0-dev1
# Step: change_pass
# Date: 2012-04-09 22:14:00
#
# Tags:
#    803e6b8, v1.0.0-dev1 - 2012-04-09 22:14:00 - david
#    fa650af              - 2012-04-09 22:13:55 - david
#    36acafd              - 2012-04-09 22:13:53 - david
#
Nothing to deploy (up-to-date)

Note that all the tags are included, and that the two tags 803e6b8 and v1.0.0-dev1 actually represent the same point in time. This is handy for development, but is a bit over-the-top for production deployments. We can get around this by bundling up all the steps with the explicit tag, leaving out the individual commit SHA1s:

> sqitch bundle --tags-only
Bundling in bundle/
Config written to bundle/sqitch.conf
Bundling v1.0.0-dev1
  - appuser
  - users
  - change_pass
  - insert_user
Plan written to bundle/sqitch.plan

Now we can package up the bundle directory and distribute it. When it gets installed somewhere, users can use Sqitch to deploy to the database. Of course, the Git history is no longer available, so Sqitch will use the sqitch.plan file to figure things out. Let's have a look at it:

cat bundle/sqitch.plan

[v1.0.0-dev1]
appuser
users
change_pass
insert_user

Pretty simple. It shows the tag being deployed and the steps that constitute it as a simple list. Notice that the SHA1s for each step are not present; the --tags-only option to the bundle command limits the plan only to tags.

Let's test deploying it:

> cd bundle
> createdb flipr_prod
> sqitch --db-name flipr_prod deploy
Deploying v1.0.0-dev1 to flipr_prod
  + appuser
  + users
  + insert_user
  + change_pass

Looks much the same as before, eh? But have a look at the status:

> sqitch --db-name flipr_prod status --show tags
# On database flipr_prod
# Tag:  v1.0.0-dev1
# Step: change_pass
# Date: 2012-04-09 22:17:38
#
# Tags:
#    v1.0.0-dev1 - 2012-04-09 22:17:38 - david
#
Nothing to deploy (up-to-date)

No commit SHA1s, just v1.0.0-dev1.

Flip Out

Now that we've got the basics of user management done, let's get to work on the core of our product, the "flip." Since other folks are working on other tasks in the repository, we'll work on a branch, so we can all stay out of each other's way. So let's branch:

> git checkout -b flips
Switched to a new branch 'flips'

Now we can add a new step to create a table for our flips.

> sqitch add-step flips --requires users
Adding sql/deploy/flips.sql
       -- requires: users
Adding sql/revert/flips.sql

You know the drill by now. Edit sql/deploy/flips.sql:

-- requires: users
BEGIN;
SET client_min_messages = 'warning';

CREATE TABLE flips (
    id        BIGSERIAL   PRIMARY KEY,
    nickname  TEXT        NOT NULL REFERENCES users(nickname),
    body      TEXT        NOT NULL DEFAULT '' CHECK ( length(body) <= 180 ),
    timestamp TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp()
);

GRANT SELECT ON flips TO flipr;
COMMIT;

And edit sql/revert/flips.sql:

DROP TABLE flips;

And give it a whirl:

> sqitch deploy --untracked
Deploying HEAD+ to flipr_test
  + flips

Look good?

> sqitch status --show tags
# On database flipr_test
# Tag:  HEAD+
# Step: flips
# Date: 2012-04-10 22:30:02
#
# Tags:
#    HEAD+   - 2012-04-10 22:30:02 - david
#    803e6b8 - 2012-04-09 21:33:32 - david
#    fa650af - 2012-04-09 20:45:23 - david
#    36acafd - 2012-04-09 19:04:50 - david
#
Nothing to deploy (up-to-date)

Yep. Make it so!

> git add .
> git commit -am 'Add flipr table.'
[flips 4aa888e] Add flipr table.
 2 files changed, 18 insertions(+)
 create mode 100644 sql/deploy/flips.sql
 create mode 100644 sql/revert/flips.sql

Ooh, but wait, we forgot to revert the change before committing! Well, no worries, we can update all the tags.

> sqitch retag
Retagging flipr_test
  . 36acafd
  . fa650af
  . 803e6b8
  + v1.0.0-dev1
  - HEAD+
  + 4aa888e
Tags: 4 (1 removed, 1 added).

In the list of tags, . means no change, + means added, and - means removed.

Oh, it added the v1.0.0-dev1 tag. Recall that we never deployed it to the test database before. That's cool, forgot about that. Then it removed the HEAD+ tag and added 4aa888e. Let's examine the status:

> sqitch status --show tags
# On database flipr_test
# Tag:  4aa888e
# Step: flips
# Date: 2012-04-10 22:30:02
#
# Tags:
#    4aa888e              - 2012-04-10 22:30:02 - david
#    803e6b8, v1.0.0-dev1 - 2012-04-09 21:33:32 - david
#    fa650af              - 2012-04-09 20:45:23 - david
#    36acafd              - 2012-04-09 19:04:50 - david
#
Nothing to deploy (up-to-date)

Great, looks like the tags are all in sync. That's handy. Of course, it's still a good idea to test sqitch revert and sqitch deploy a few times, just to make sure the changes are always carried out properly, but it's nice to know we don't have to take extra steps just to commit untracked changes.

Wash, Rinse, Repeat

Now comes the time to add functions to manage flips. I'm sure you have things nailed down now. Go ahead and add insert_flip and delete_flip steps and commit them. The insert_flip deploy script might look something like:

-- requires: flips, appuser
BEGIN;
CREATE OR REPLACE FUNCTION insert_flip(
   nickname TEXT,
   body     TEXT
) RETURNS BIGINT LANGUAGE sql SECURITY DEFINER AS $$
    INSERT INTO flips (body, nickname)
    VALUES ($1, $2)
    RETURNING id;
$$;
GRANT EXECUTE ON FUNCTION insert_flip(TEXT, TEXT) to flipr;
COMMIT;

And the delete_flip deploy script might look something like:

-- requires: flips, appuser
BEGIN;
CREATE OR REPLACE FUNCTION delete_flip(
   flip_id BIGINT
) RETURNS BOOLEAN LANGUAGE plpgsql SECURITY DEFINER AS $$
BEGIN
    DELETE FROM flips WHERE id = flip_id;
    RETURN FOUND;
END;
$$;
GRANT EXECUTE ON FUNCTION delete_flip(BIGINT) to flipr;
COMMIT;

The revert scripts are:

DROP FUNCTION insert_flip(TEXT, TEXT);

And:

DROP FUNCTION delete_flip(BIGINT);

Check the example git repository for the complete details. Test deploy and revert, commit, and retag. The status should end up looking something like this:

> sqitch status --show tags
# On database flipr_test
# Tag:  74c49ef
# Step: insert_flip
# Date: 2012-04-10 23:00:29
#
# Tags:
#    74c49ef              - 2012-04-10 23:00:29 - david
#    4aa888e              - 2012-04-10 22:30:02 - david
#    803e6b8, v1.0.0-dev1 - 2012-04-09 21:33:32 - david
#    fa650af              - 2012-04-09 20:45:23 - david
#    36acafd              - 2012-04-09 19:04:50 - david
#
Nothing to deploy (up-to-date)

Good, we've finished this feature. Time to merge back into master.

Emergency

Let's do it:

> git checkout master
Switched to branch 'master'
> git pull
Updating 803e6b8..2fae0b3
Fast-forward
 sql/deploy/delete_list.sql |   15 +++++++++++++++
 sql/deploy/insert_list.sql |   12 ++++++++++++
 sql/deploy/lists.sql       |   16 ++++++++++++++++
 sql/revert/delete_list.sql |    1 +
 sql/revert/insert_list.sql |    1 +
 sql/revert/lists.sql       |    2 ++
 12 files changed, 91 insertions(+)
 create mode 100644 sql/deploy/delete_list.sql
 create mode 100644 sql/deploy/insert_list.sql
 create mode 100644 sql/deploy/lists.sql
 create mode 100644 sql/revert/delete_list.sql
 create mode 100644 sql/revert/insert_list.sql
 create mode 100644 sql/revert/lists.sql

Hrm, that's interesting. Looks like someone made some changes to master. Looks like some list support was added. Well, let's see what happens when we merge our changes.

> git merge --no-ff flips
Merge made by the 'recursive' strategy.
 sql/deploy/delete_flip.sql |   12 ++++++++++++
 sql/deploy/flips.sql       |   16 ++++++++++++++++
 sql/deploy/insert_flip.sql |   12 ++++++++++++
 sql/revert/delete_flip.sql |    1 +
 sql/revert/flips.sql       |    2 ++
 sql/revert/insert_flip.sql |    1 +
 6 files changed, 44 insertions(+)
 create mode 100644 sql/deploy/delete_flip.sql
 create mode 100644 sql/deploy/flips.sql
 create mode 100644 sql/deploy/insert_flip.sql
 create mode 100644 sql/revert/delete_flip.sql
 create mode 100644 sql/revert/flips.sql
 create mode 100644 sql/revert/insert_flip.sql

Hey, looks good, everything merged cleanly. Let's have a look at our Git history:

> git log --oneline
5e30f38 Merge branch 'flips'
2fae0b3 Merge tag 'lists'
2a84155 Merge branch 'lists'
9703969 Add `insert_flip()` and `delete_flip()`.
675f78d Add `insert_list()` and `delete_list()`.
faaa4d0 Add flipr table.
591e388 Add `lists` table.
803e6b8 Add `insert_user()` and `change_pass()`.
fa650af Add users table.
36acafd Add app user.
e076e84 Initialize Sqitch configuration.
9ccc485 Add README text.
7b8dd19 First post!

Whoa, wait, what? Recall that we left off at commit 803e6b8. After that we should have our two commits to the "flips" branch and end with a merge commit. But notice here we have some other stuff interleaved. Commit 591e388 adds a "lists" table before our faaa4d0 commit adds the "flips" table. Then there is another interloper commit, 675f78d, which adds some functions to insert and delete lists, before our 9703969 commit adds functions to insert and delete flips. And there, second from the top, is the explanation, 2fae0b3 Merge branch 'lists'. Why, someone else was working on another branch and merged stuff into master before we did! Now things are all mixed up.

Well, the truth is, we got lazy. Those changes when we pulled master from the origin should have raised a red flag. In truth, it's considered a bad practice not to look at what's changed in master before merging in a branch. What one should do is either:

Rebase the branch from on master before merging. This "rewinds" the branch changes, pulls from master, and then replays the changes back on top of master.
Create a patch and apply that to master. This is the sort of thing you might have to do if you're sending changes to another user, especially if the VCS is not Git.

So let's restore things to how they were at master:

> git reset --hard origin/master
HEAD is now at 2fae0b3 Merge tag 'lists'

That throws out our merge, which thankfully we have not yet pushed. Now let's go back to our branch and rebase it on master:

> git checkout flips
Switched to branch 'flips'
> git rebase master
First, rewinding head to replay your work on top of it...
Applying: Add flipr table.
Applying: Add `insert_flip()` and `delete_flip()`.

Notice that it rewinds the changes since we branched from master, pulls from master, and then re-applies our commits. Look at the Git log to make sure things were applied properly:

> git log --oneline
54d680f Add `insert_flip()` and `delete_flip()`.
d07f3dc Add flipr table.
2fae0b3 Merge tag 'lists'
2a84155 Merge branch 'lists'
675f78d Add `insert_list()` and `delete_list()`.
591e388 Add `lists` table.
803e6b8 Add `insert_user()` and `change_pass()`.
fa650af Add users table.
36acafd Add app user.
e076e84 Initialize Sqitch configuration.
9ccc485 Add README text.
7b8dd19 Fist post!

Much better. Now all of our "flips" changes come after the "lists" changes. But wait, we can't merge into master quite yet. Or, at least, it's not a good idea. We need to first make sure that Sqitch deployments still work properly. Why? Have a look at this:

> sqitch status
# On database flipr_test
# Tag:  74c49ef (unknown)
# Step: insert_flip
# Date: 2012-04-10 23:00:29
#
# Unknown tags:
#  (use "sqitch retag" to fix)
#    74c49ef
#    28df2ve
#
# Undeployed changes:
#   (no conflicts detected; use "sqitch deploy" to deploy the changes)
#
#    591e388
#      - lists
#    675f78d
#      - insert_list
#      - delete_list

Uh-oh. First of all, the second line tells us that the current tag, 74c49ef, is unknown. That's because we rebased from master, so our local commits got new SHA1s. Fortunately, we can fix that problem by re-tagging:

> sqitch retag
Retagging flipr_test
  . 36acafd
  . fa650af
  . 803e6b8
  . v1.0.0-dev1
  . 4aa888e
  - 28df2ve
  + d07f3dc
  - 74c49ef
  + 54d680f
  
Tags: 7 (2 removed, 2 added).

Looks like it made some important updates. Let's see:

> sqitch status
# On database flipr_test
# Tag:  54d680f
# Step: insert_flip
# Date: 2012-04-10 23:00:29
#
# Undeployed changes:
#   (no conflicts detected; use "sqitch deploy" to deploy the changes)
#
#    591e388
#      - lists
#    675f78d
#      - insert_list
#      - delete_list

Great, now the tag looks right. But we still have undeployed changes. This is because the rebase from master added the "lists" branch changes. They come before our "flips" changes in the repository history, which means that, technically, the the "flips" changes have been applied out-of-order. Fortunately for us, however, there appear to be no conflicts (no mutual dependencies between the "flips" and "lists" changes to muck things up). So we can just take its advice and run sqitch deploy:

> sqitch deploy --untracked
Deploying 591e388 to flipr_test
  + lists
Deploying 675f78d to flipr_test
  + delete_list
  + insert_list

So where does that leave us?

> sqitch status
# On database flipr_test
# Tag:  54d680f
# Step: insert_flip
# Date: 2012-04-10 23:32:43
#

Note that we are still on the 54d680f tag but the date has been updated. Sqitch has successfully merged the "lists" changes into the database.

What would have happened if there had been a conflict? In that case, we would have had to revert the "flips" changes and deployed again, so that the changes could be re-applied in the proper order. The commands to do that would have been:

> sqitch revert 54d680f^
> sqitch deploy

Note the use of ^ at the end of 54d680f^. That means, "the tag just before 54d680f", and is a nice shortcut so that we don't have to troll the history to find that tag. So it would revert to that tag, and them the call to sqitch deploy would have deployed the "lists" changes, followed by the "flips" changes, so that everything would be in the proper order

Of course, this is the sort of thing you want to avoid in a production context. When doing development, you can muck with your change history in any number of ways. But you really want to get things nailed down before you do a release, which should have a set plan that does not change -- until the next release adds new steps -- but never mixes things in.

Merges Mastered

And now, finally, we can merge into master:

> git checkout master
Switched to branch 'master'
> git merge --no-ff flips
Merge made by the 'recursive' strategy.
 sql/deploy/delete_flip.sql |   12 ++++++++++++
 sql/deploy/flips.sql       |   16 ++++++++++++++++
 sql/deploy/insert_flip.sql |   12 ++++++++++++
 sql/revert/delete_flip.sql |    1 +
 sql/revert/flips.sql       |    2 ++
 sql/revert/insert_flip.sql |    1 +
 6 files changed, 44 insertions(+)
 create mode 100644 sql/deploy/delete_flip.sql
 create mode 100644 sql/deploy/flips.sql
 create mode 100644 sql/deploy/insert_flip.sql
 create mode 100644 sql/revert/delete_flip.sql
 create mode 100644 sql/revert/flips.sql
 create mode 100644 sql/revert/insert_flip.sql

And double-check our work:

> git log --oneline
e161a90 Merge branch 'flips'
54d680f Add `insert_flip()` and `delete_flip()`.
d07f3dc Add flipr table.
2fae0b3 Merge tag 'lists'
2a84155 Merge branch 'lists'
675f78d Add `insert_list()` and `delete_list()`.
591e388 Add `lists` table.
803e6b8 Add `insert_user()` and `change_pass()`.
fa650af Add users table.
36acafd Add app user.
e076e84 Initialize Sqitch configuration.
9ccc485 Add README text.
7b8dd19 Fist post!

Much much better, a nice clean master now. And because it is now identical to the "flips" branch, we can just carry on. Tag it so that we can use real tags for bundling a release later:

> git tag flips -am 'Tag flips merge.'

In Place Changes

Uh-oh, someone just noticed that MD5 hashing is not particularly secure. Why? Have a look at this:

> psql -d flipr_test -c "
    SELECT insert_user('foo', 'secr3t'), insert_user('bar', 'secr3t');
    SELECT * FROM users;
"
 nickname |             password             |           timestamp           
----------+----------------------------------+-------------------------------
 foo      | 9695da4dd567a19f9b92065f240c6725 | 2012-04-11 23:00:02.135612+00
 bar      | 9695da4dd567a19f9b92065f240c6725 | 2012-04-11 23:00:02.135612+00

If user "foo" ever got access to the database, she could quickly discover that user "bar" has the same password and thus be able to exploit the account. Not a great idea. So we need to modify the insert_user() and change_pass() functions to fix that. How? we'll use pgcrypto's crypt() function to encrypt passwords with a salt, so that they're all unique. We just add a step to add pgcrypto to the database, and then we can use it. The deploy script should be:

CREATE EXTENSION pgcrypto;

And the revert script should just DROP EXTENSION pgcrypto. Then we can use it. But how to deploy the changes to insert_user() and change_pass()?

Normally, modifying functions in database changes is a PITA. You have to take steps like these:

Copy sql/deploy/insert_user.sql to sql/deploy/insert_user_crypt.sql.
Edit sql/deploy/insert_user_crypt.sql to switch from MD5() to crypt() and to add a dependency on the pgcrypto step.
Copy sql/deploy/insert_user.sql to sql/revert/insert_user_crypt.sql. Yes, copy the original change script to the new revert step.
Test the changes to make sure you can deploy and revert the insert_user_crypt step.
Now do the same for the change_pass scripts.

That doesn't seem too bad. And you can do that with Sqitch, if you want. But it makes for rather bad VCS management. This is effectively a one-line change to each function, but we're copying their entire contents to new files. Hell, the reversion script is identical to the original deploy script.

Perhaps worse, though, is that it's much more difficult to look at changes in the VCS. After committing the insert_user_crypt step, what does the diff look like?

> git diff HEAD^
diff --git a/sql/deploy/insert_user_crypt.sql b/sql/deploy/insert_user_crypt.sql
new file mode 100644
index 0000000..fa8d0c6
--- /dev/null
+++ b/sql/deploy/insert_user_crypt.sql
@@ -0,0 +1,8 @@
+-- requires: users, appuser, pgcrypto
+
+CREATE OR REPLACE FUNCTION insert_user(
+    nickname TEXT,
+    password TEXT
+) RETURNS VOID LANGUAGE SQL AS $$
+    INSERT INTO users values($1, crypt($2, gen_salt('md5')));
+$$;
diff --git a/sql/revert/insert_user_crypt.sql b/sql/revert/insert_user_crypt.sql
new file mode 100644
index 0000000..a7f4e31
--- /dev/null
+++ b/sql/revert/insert_user_crypt.sql
@@ -0,0 +1,8 @@
+-- requires: users, appuser
+
+CREATE OR REPLACE FUNCTION insert_user(
+    nickname TEXT,
+    password TEXT
+) RETURNS VOID LANGUAGE SQL AS $$
+    INSERT INTO users values($1, md5($2));
+$$;

Oy. It looks like two new functions were added, not that we just changed one. How annoying is that? Of course, if we know the history, we can diff things without using the Git history, simply by comparing sql/deploy/insert_user.sql to sql/deploy/insert_user_crypt.sql:

> diff -u sql/deploy/insert_user.sql sql/deploy/insert_user_crypt.sql 
--- sql/deploy/insert_user.sql	2012-04-09 14:15:55.000000000 -0700
+++ sql/deploy/insert_user_crypt.sql	2012-04-11 16:27:18.000000000 -0700
@@ -4,5 +4,5 @@
     nickname TEXT,
     password TEXT
 ) RETURNS VOID LANGUAGE SQL AS $$
-    INSERT INTO users values($1, md5($2));
+    INSERT INTO users values($1, crypt($2, gen_salt('md5')));
 $$;

That makes much more sense, but requires special knowledge to realize you can't just git diff to see what changed. Wouldn't it be great if we could just make the change right in the file, commit it to the VCS, and have the deployment and reversion "just work"?

It's a VCS

Sqitch can. Rather than take all the above steps, you can just modify sql/deploy/insert_user.sql and sql/deploy/change_padd.sql in place. There is only one requirement for this: All changes to the deployment script, including the original version, must be idempotent. That is, the deploy script can applied multiple times without changing the result beyond the initial application. That's it, the only requirement.

Fortunately, our function deployment scripts are already idempotent, thanks to the use of the OR REPLACE expression. No matter how many times a deployment script is run, he end result will be the same instance of the function, with no duplicates or errors.

As a result, there is no need to explicitly add steps. So go ahead. Modify the script to switch to crypt(). Make this change to sql/deploy/insert_user.sql:

 @@ -1,8 +1,8 @@
 --- requires: users, appuser
 +-- requires: users, appuser, pgcrypto

  CREATE OR REPLACE FUNCTION insert_user(
      nickname TEXT,
      password TEXT
  ) RETURNS VOID LANGUAGE SQL AS $$
 -    INSERT INTO users values($1, md5($2));
 +    INSERT INTO users values($1, crypt($2, gen_salt('md5')));
  $$;

And make this change to sql/deploy/change_pass.sql:

 @@ -1,4 +1,4 @@
 --- requires: users, appuser
 +-- requires: users, appuser, pgcrypto

  CREATE OR REPLACE FUNCTION change_pass(
      nick    TEXT,
 @@ -7,9 +7,9 @@ CREATE OR REPLACE FUNCTION change_pass(
  ) RETURNS BOOLEAN LANGUAGE plpgsql AS $$
  BEGIN
      UPDATE users
 -       SET password = md5($3)
 +       SET password = crypt($3, gen_salt('md5'))
       WHERE nickname = $1
 -       AND password = md5($2);
 +       AND password = crypt($2, password);
      RETURN FOUND;
  END;
  $$;

And then test deployment:

> sqitch deploy --untracked
Deploying HEAD+ to flipr_test
  + insert_user
  + change_pass

So, are the changes deployed?

> psql -d flipr_test -c "
    DELETE FROM users;
    SELECT insert_user('foo', 'secr3t'), insert_user('bar', 'secr3t');
    SELECT * FROM users;
"
 nickname |              password              |           timestamp           
----------+------------------------------------+-------------------------------
 foo      | $1$l6OEKyF3$kv5ae7505ROub75d9QKTh/ | 2012-04-12 18:55:43.422211+00
 bar      | $1$J4NJDgaJ$578i9Lt6b8ohJwi6WhNNO1 | 2012-04-12 18:55:43.422211+00

Awesome, the stored passwords are different now. But can we revert, even though we haven't written any reversion scripts?

> sqitch revert --to HEAD
Reverting HEAD+ from flipr_test
  - change_pass
  - insert_user

Did that work, are the MD5() passwords back?

> psql -d flipr_test -c "
    DELETE FROM users;
    SELECT insert_user('foo', 'secr3t'), insert_user('bar', 'secr3t');
    SELECT * FROM users;
"
 nickname |             password             |           timestamp           
----------+----------------------------------+-------------------------------
 foo      | 9695da4dd567a19f9b92065f240c6725 | 2012-04-12 18:56:03.292423+00
 bar      | 9695da4dd567a19f9b92065f240c6725 | 2012-04-12 18:56:03.292423+00

Yes, it works! How, you ask? Simple. Because Sqitch uses your VCS history to determine what steps to deploy, it can tell that the insert_user() and change_pass() scripts have changed, and just deploy the new version. When you revert, it simply retrieves the old version from the history and applies that, instead. And that should underscore the importance of the idempotence of in-place modifications to change scripts. Because if the reversion had failed, or left the newer version still in place, it simply would not work.

This is a valuable feature, and certainly keeps the VCS history cleaner, so that it's easier to tell what changed in our database functions in a given commit. But you do have to use it with care. If you have any doubts about changing something in place, or risk orphaning some object, use the copy-and-paste approach instead.

So let's go ahead and commit these changes and bring the database up-to-date:

> git commit -m 'Use pgcrypto to encrypt passwords.'
[master eea942d] Use pgcrypto to encrypt passwords.
 2 files changed, 3 insertions(+), 3 deletions(-)

> sqitch deploy
Deploying eea942d to flipr_test
  + insert_user
  + change_pass

> sqitch -d flipr_test status
# On database flipr_test
# Tag:  eea942d
# Step: change_pass
# Date: 2012-04-12 18:59:26
#
Nothing to deploy (up-to-date)

What about Bundling?

Of course, this only works if Sqitch is using the VCS history to fetch earlier versions of deployment scripts to do reversions. Such are obviously not available in a distributed bundle. Or are they? Let's create a new release and find out. Let's tag a v1.0.0-b1 release and create a new bundle:

> git tag v1.0.0-b1 -am 'Tag v1.0.0-b1'
> sqitch bundle --tags-only
Bundling in bundle/
Config written to bundle/sqitch.plan
Bundling v1.0.0-dev1
  - appuser
  - users
  - change_pass
  - insert_user
Bundling lists
  - insert_list
  - delete_list
Bundling flips
  - insert_flip
  - delete_flip
Bundling v1.0.0-b1
  - change_pass_v2
  - insert_user_v2
Plan written to bundle/sqitch.plan

Hey, look, the change_pass and insert_user steps are listed twice, but in the second instance, they have _v2 appended to their names. Let's have a look at the plan:

> cd bundle
> cat sqitch.plan
[v1.0.0-dev1]
  appuser
  users
  change_pass
  insert_user

[lists]
  insert_list
  delete_list

[flips]
  insert_flip
  delete_flip

[v1.0.0-b1]
  change_pass_v2
  insert_user_v2

Well that looks pretty much just like the output of the bundling. What if we deploy it to the flip_prod database, which we built to v1.0.0-dev1 earlier. Here's what it looks like before we deploy the latest changes:

> sqitch -d flipr_prod status
# On database flipr_prod
# Tag:  v1.0.0-dev1
# Step: change_pass
# Date: 2012-04-09 22:17:38
#
# Changes not deployed:
# * lists
#   insert_list
#   delete_list
# * flips
#   insert_flip
#   delete_flip
# * v1.0.0-b1
#   change_pass_v2
#   insert_user_v2
#
Use "sqitch deploy" to deploy these changes

So, let's deploy.

> sqitch -d flipr_prod deploy
Deploying lists to flipr_prod
  + insert_list
  + delete_list
Deploying flips to flipr_prod
  + insert_flip
  + delete_flip
Deploying v1.0.0+b1 to flipr_prod
  + change_pass_v2
  + insert_user_v2

And now the status is:

> sqitch -d flipr_prod status
# On database flipr_prod
# Tag:  v1.0.0-dev1
# Step: insert_user_v2
# Date: 2012-04-12 19:14:34
#
Nothing to deploy (up-to-date)

All up-to-date. If necessary, we can also revert:

> sqitch -d flipr_prod revert --to flips
Reverting v1.0.0-b1 from flipr_prod
  - insert_user_v2
  - change_pass_v2

> sqitch -d flipr_prod status
# On database flipr_prod
# Tag:  flips
# Step: delete_flip
# Date: 2012-04-12 19:14:29
#
# Changes not deployed:
# * v1.0.0-b1
#   change_pass_v2
#   insert_user_v2
#
Use "sqitch deploy" to deploy these changes

Nice, it just works! How? The secret is that, while the VCS history is not available when deploying from this plan, it was certainly available when the plan was written. When we ran sqitch bundle, Sqitch fetched all versions of the deployment scripts from the repository and wrote them out as they existed at the time of each tag. In other words, sql/deploy/insert_user.sql was written to the bundle as it existed at the time of the v1.0.0-dev1 tag, and sql/deploy/insert_user_v2.sql was written as a copy of sql/deploy/insert_user.sql at the time of the v1.0.0-b1 tag.

Put another way: when bundling the changes and writing the plan, Sqitch copied the entire migration script for each step in time, just as if you had duplicated code in new migration scripts the old-fashioned way. So you get the benefit of exploiting the VCS history in you development practices, while still getting the benefits of explicit, separate steps in the distribution you ship.

To install App::Sqitch, copy and paste the appropriate command in to your terminal.

cpanm

cpanm App::Sqitch

CPAN shell

perl -MCPAN -e shell
install App::Sqitch

For more information on module installation, please visit the detailed CPAN module installation guide.

	Global
`s`	Focus search bar
`?`	Bring up this help dialog

	GitHub
`g` `p`	Go to pull requests
`g` `i`	go to github issues (only if github is preferred repository)

	POD
`g` `a`	Go to author
`g` `c`	Go to changes
`g` `i`	Go to issues
`g` `d`	Go to dist
`g` `r`	Go to repository/SCM
`g` `s`	Go to source
`g` `b`	Go to file browse

	Search terms
module: (e.g. module:Plugin)
distribution: (e.g. distribution:Dancer auth)
author: (e.g. author:SONGMU Redis)
version: (e.g. version:1.00)