Swing the heartache: Interactive DB maintenance in Lisp

Every now and then, I'm still blown away by the elegant power that the repl (read-eval-print-loop) of Common Lisp provides for everyday tasks. A recent example: I needed to fix a broken column definition in one of my Postgres databases. I'm not a DB pro, so I will just drop the column. But of course, we want to retain the old data, so here we go (using CL-SQL):

SOMEDB> (connect +db-spec+)
#
SOMEDB> (defvar *olddata* (select [id] [data] :from [sometable]))
*OLDDATA*
SOMEDB> (execute-command "ALTER TABLE sometable DROP COLUMN data;")
NIL
SOMEDB> (execute-command "ALTER TABLE sometable ADD COLUMN data varchar;")
NIL
SOMEDB> (loop for (id value) in *olddata*
                  do (update-records [sometable] 
                                 :attributes '(data) 
                                 :values (list value) 
                                 :where [= [id] id]))
NIL
SOMEDB> (disconnect)

That's it. First we open a connection, store away the olddata (which will be returned as a list of tuples) in a global variable, modify the table and finally restore the data. Now, what I find really nice about this is that I can operate on the data as if I had an intersection of psql, shell and a real programming language, which is pretty much the point of this blog post. I think that Ruby probably provides a similar environment with irb. And, hey, today I learned that XEmacs comes with an interface to postgres, so I might have been able to do it from within my favourite editor, too ...