SQL Change Management Sans Duplication
In the previous episode in this series, I had one more issue with regard to SQL change management that I wanted to resolve:
- There is still more duplication of code than I would like, in that a procedure defined in one change script would have to be copied whole to a new script for any changes, even simple single-line changes.
So let’s see what we can do about that. Loading it into Git, our first example looks like this:
> alias sqlhist="git log -p —format=’[%H%d]’ —name-only —reverse sql/deploy \
| awk ‘/^\[/ {print \"\"} /./’"
> sqlhist
[3852b378aa029cc610a03806e8268ed452dce8a6 (alpha)]
sql/deploy/users_table.sql
[32883d5a08691351b07928fa4e4fb7e68c500973 (beta)]
sql/deploy/add_widget.sql
sql/deploy/widgets_table.sql
[b8b9f5c152675305c6b2d3e105d55a25019e0828 (HEAD, gamma, master)]
sql/deploy/add_user.sql
(Aside: I’ve created an alias, sqlhist, on the first line, so that all the Git
and Awk magic doesn’t clutter the remaining examples.)
So, we’ve got the creation of the users table under the alpha tag, the
addition of the widgets table and an accompanying add_widget() function
under the beta tag, and the creation of the add_user() function under the
gamma tag. So far so good. Now, let’s say that gamma has been deployed to
production, and now we’re ready to add a feature for the next release.
Modify This
It turns out that our users really want a timestamp for the time a widget was
created. So let’s add a new change script that adds a created_at column to
the widgets table. First we add sql/deploy/widgets_created_at.sql with:
— requires: widgets_table
ALTER TABLE widgets ADD created_at TIMESTAMPTZ;
And then the accompanying revert script, sql/revert/widgets_created_at.sql:
ALTER TABLE widgets DROP COLUMN IF EXISTS created_at;
Commit them and now our deployment configuration looks like this:
> sqlhist
[3852b378aa029cc610a03806e8268ed452dce8a6 (alpha)]
sql/deploy/users_table.sql
[32883d5a08691351b07928fa4e4fb7e68c500973 (beta)]
sql/deploy/add_widget.sql
sql/deploy/widgets_table.sql
[b8b9f5c152675305c6b2d3e105d55a25019e0828 (gamma)]
sql/deploy/add_user.sql
[44ba615b7813531f0acb6810cbf679791fe57bf2 (HEAD, master)]
sql/deploy/widgets_created_at.sql
So far so good. We have a simple delta script that modifies the existing
table, and there is no code duplication. Time to modify the add_widget()
function to insert the timestamp. Recall that, in the
first article in
this series, I created a separate sql/deploy/add_widgets_v2.sql file, copied
the existing function in its entirety into the new file, and modified it
there. If we were to do that here, the resulting deployment configuration
would look something like this:
> sqlhist
[3852b378aa029cc610a03806e8268ed452dce8a6 (alpha)]
sql/deploy/users_table.sql
[32883d5a08691351b07928fa4e4fb7e68c500973 (beta)]
sql/deploy/add_widget.sql
sql/deploy/widgets_table.sql
[b8b9f5c152675305c6b2d3e105d55a25019e0828 (gamma)]
sql/deploy/add_user.sql
[44ba615b7813531f0acb6810cbf679791fe57bf2]
sql/deploy/widgets_created_at.sql
[dfba488cfd9145928a25d8d48de3231da84s4bd2 (HEAD, master)]
sql/deploy/add_widget_v2.sql
Which would be fine, except that if someone else wanted to see what had
changed, here’s what git diff would output:
> git diff HEAD^ sql/deploy
diff —git a/sql/deploy/add_widget_v2.sql b/sql/deploy/add_widget_v2.sql
new file mode 100644
index 0000000..9132195
—- /dev/null
+++ b/sql/deploy/add_widget_v2.sql
@@ -0,0 +1,8 @@
+— requires widgets_created_at
+CREATE OR REPLACE FUNCTION add_widget(
+ username TEXT,
+ widgetname TEXT
+) RETURNS VOID LANGUAGE SQL AS $$
+ INSERT INTO widgets (created_by, name, created_at)
+ VALUES ($1, $2, NOW());
+$$;
So, what changed in the add_widget() function between gamma and now? One
cannot tell from this diff: it looks like a brand new function. And no
web-based VCS interface will show you, either; it’s just not inherent in the
commit. We have to actually know that it was just an update to an existing
function, and what files to manually diff, like so:
> diff -u sql/deploy/add_widget.sql sql/deploy/add_widget_v2.sql
—- sql/deploy/add_widget.sql 2012-01-28 13:06:24.000000000 -0800
+++ sql/deploy/add_widget_v2.sql 2012-01-28 13:26:59.000000000 -0800
@@ -1,8 +1,8 @@
—- requires: widgets_table
-
+— requires: widgets_created_at
CREATE OR REPLACE FUNCTION add_widget(
username TEXT,
widgetname TEXT
) RETURNS VOID LANGUAGE SQL AS $$
- INSERT INTO widgets (created_by, name) VALUES ($1, $2);
+ INSERT INTO widgets (created_by, name, created_at)
+ VALUES ($1, $2, NOW());
$$;
Much better, but how annoying is that? It doesn’t allow us to really take advantage of the VCS, all because we need SQL changes to run in a very specific order.
But let’s ignore that for the moment. Let’s just throw out the commit with
add_widgets_v2.sql and go ahead and change the add_widget change script
directly. So the history now looks like this:
> sqlhist
[3852b378aa029cc610a03806e8268ed452dce8a6 (alpha)]
sql/deploy/users_table.sql
[32883d5a08691351b07928fa4e4fb7e68c500973 (beta)]
sql/deploy/add_widget.sql
sql/deploy/widgets_table.sql
[b8b9f5c152675305c6b2d3e105d55a25019e0828 (gamma)]
sql/deploy/add_user.sql
[44ba615b7813531f0acb6810cbf679791fe57bf2]
sql/deploy/widgets_created_at.sql
[e4b970aa36f27451fe377791eab040a73c6eb47a (HEAD, epsilon, master)]
sql/deploy/add_widget.sql
Naturally, the add_widget script appears twice now, once under the beta
tag and once under epsilon (which I’ve just tagged). What are the
consequences for our migration? Well, if we were to build a new database from
the beginning, running these migrations as listed here, we would get an error
while applying the beta changes:
ERROR: column "created_at" of relation "widgets" does not exist
LINE 5: INSERT INTO widgets (created_by, name, created_at)
This is because the created_at column won’t exist until the
widgets_created_at change is applied. That won’t do, will it? Fortunately,
Git knows exactly what the add_widget deploy script looked like under the
beta tag, and we can ask it:
> git show beta:sql/deploy/add_widget.sql
— requires: widgets_table
CREATE OR REPLACE FUNCTION add_widget(
username TEXT,
widgetname TEXT
) RETURNS VOID LANGUAGE SQL AS $$
INSERT INTO widgets (created_by, name) VALUES ($1, $2);
$$;
Boom, there it is, with no reference to created_at. Using this technique,
our SQL deployment app can successfully apply all of our database changes by
iterating over the list of changes and applying the contents of each script
at the time of the appropriate commit or tag. In other words, it could apply
the output from each of these commands:
git show alpha:sql/deploy/users_table.sql
git show beta:sql/deploy/widgets_table.sql
git show beta:sql/deploy/add_widget.sql
git show gamma:sql/deploy/add_user.sql
git show 44ba615b7813531f0acb6810cbf679791fe57bf2:sql/deploy/widget_created_at.sql
git show epsilon:sql/deploy/add_widget.sql
And everything will work exactly as it should: the original version of the
add_widget change script will be for the beta tag, and the next version
will be applied for the epsilon tag. Not bad, right? We get a nice, clean
Git history and can exploit it to manage the changes.
Reversion to the Mean
But what about reversion? What if the deploy to epsilon failed, and we need
to revert back to gamma? Recall that in the
first article, I
eliminated duplication by having the add_widget_v2 revert script simply
call the add_widget deploy script. But such is not possible now that we’ve
changed add_widget in place. What to do?
The key is for the change management script to know the difference between a
new change script and a modified one. Fortunately, Git knows that, too, and we
can get it to cough up that information with a simple change to the sqlhist
alias: instead of passing —name-only, pass —name-status:
% alias sqlhist="git log -p —format=’[%H%d]’ —name-status —reverse sql/deploy \
| awk ‘/^\[/ {print \"\"} /./’"
Using this new alias, our history looks like:
> sqlhist
[3852b378aa029cc610a03806e8268ed452dce8a6 (alpha)]
A sql/deploy/users_table.sql
[32883d5a08691351b07928fa4e4fb7e68c500973 (beta)]
A sql/deploy/add_widget.sql
A sql/deploy/widgets_table.sql
[b8b9f5c152675305c6b2d3e105d55a25019e0828 (gamma)]
A sql/deploy/add_user.sql
[44ba615b7813531f0acb6810cbf679791fe57bf2]
A sql/deploy/widgets_created_at.sql
[e4b970aa36f27451fe377791eab040a73c6eb47a (HEAD, epsilon, master)]
M sql/deploy/add_widget.sql
Now we have a letter defining the status of each file. An “A” means the file
was added in that commit; an “M” means it was modified. But the upshot is
that, to revert to gamma, our change management can see that add_widget
was modified in epsilon, and, rather than apply a revert change script, it
can just apply the version of the script as it existed under gamma:
> git show gamma:sql/deploy/add_widget.sql
— requires: widgets_table
CREATE OR REPLACE FUNCTION add_widget(
username TEXT,
widgetname TEXT
) RETURNS VOID LANGUAGE SQL AS $$
INSERT INTO widgets (created_by, name) VALUES ($1, $2);
$$;
And there we are, right back to where we should be. Of course, the remaining
epsilon deploy script, widget_created_at, was added in its commit, so we
just apply the revert script and we’re set, back to gamma.
Still Configurable
To get back to the original idea of a migration configuration file, I still think it’s entirely do-able. All we need to is to have the change management app generate it, just as before. When it comes to modified — rather than added — deploy scripts, it can automatically insert new scripts with the full copies of previous versions, much as before. The resulting configuration would look something like this:
[3852b378aa029cc610a03806e8268ed452dce8a6 (alpha)]
sql/deploy/users_table.sql
[32883d5a08691351b07928fa4e4fb7e68c500973 (beta)]
sql/deploy/add_widget.sql
sql/deploy/widgets_table.sql
[b8b9f5c152675305c6b2d3e105d55a25019e0828 (gamma)]
sql/deploy/add_user.sql
[44ba615b7813531f0acb6810cbf679791fe57bf2]
sql/deploy/widgets_created_at.sql
[e4b970aa36f27451fe377791eab040a73c6eb47a (HEAD, epsilon, master)]
sql/deploy/add_widget_v2.sql
Note that last line, where we now have add_widget_v2. The change management
script would simply generate this file, and create an additional revert script
with the same name that just contains the contents of the deploy script as it
was under the gamma tag.
Too Baroque?
Having written down these ideas that have plagued by brain for the last week, along with some examples using Git to confirm them, I’m convinced more than ever that this is entirely workable. But it also leads me to wonder if it’s too baroque. I intend these posts as a rough spec for how this thing should work, and I plan to implement it in the coming weeks. But I’m wondering how difficult it will be to explain it all to people?
So let me see if I can break it down to a few simple rules.
- In general, you should create independent deploy and revert scripts for your
SQL. Put a
CREATE TABLEstatement into its own script. If it requires some some other table, require declare the dependency. If you need to change it later, create a new script that usesALTER TABLE. - In special cases where a simple change cannot be made without copying something wholesale, and where the deploy script is idempotent, you may simply modify the deploy script in-place.
That’s about it. The idempotence
of the deploy script is important for ensuring consistency, and applies very
well to features such as
user-defined functions.
For other objects, there are generally ALTER statements that allow changes
to be made without wholesale copying of existing code.
So what am I missing? What have I overlooked? What mistakes in my logic have I made? Do you think this will be too tricky to implement, or to use? Is it hard to understand? Your comments would be greatly appreciated, because I am going to write an app to do this stuff, and want to get it right.
Thanks for sticking with me through all the thought experiments. For my next post on this topic, I expect to have an interface spec for the new app.
