Rails like Database Migrations in Clojure... Using clojure.contrib.sql

Migration-Syntax.clj

(def migrations (sorted-map
                 ;; Migrations are numbered by integer values to explicitly document them

                 1 {
                    :doc "Foo Table"
                    :up  (fn []
                            (create-table
                             :Foo
                             [:id :int "PRIMARY KEY" "NOT NULL GENERATED ALWAYS AS IDENTITY"]
                                        ; store a JSON blob in here for the screening record
                             [:logdata "CLOB(4k)"]))
                    :down #(drop-table :Foo)
                    }

                 2 {
                    :doc "Bar Table"
                    :up #(create-table :BarTable
                                       [:id :int "NOT NULL UNIQUE"]
                                       [:blah "VARCHAR(20)"]
                                       [:status "VARCHAR(10)"]
                                       [:comments "VARCHAR(255)"]
                                       [:recorded_at "TIMESTAMP"])
                    :down #(drop-table :BarTable)
                    }))

Rails-a-like-Database-Migrations.clj

;;; The use and distribution terms for this software are covered by the 
;;; Eclipse Public License 1.0, which can be found at 
;;; 
;;; http://opensource.org/licenses/eclipse-1.0.php
;;;
;;; By using this software in any fashion, you are agreeing to be bound 
;;; by the terms of this license. You must not remove this notice, or any 
;;; other, from this software. 

(ns railsy.migrations
  (:use (clojure.contrib
         [sql :only (insert-values
                     delete-rows
                     do-commands
                     with-connection
                     create-table
                     drop-table
                     transaction
                     with-query-results)]
         [logging :only (info warn)]
         [core :only (.?.)]
         [java-utils :only (as-str)]))
  (:import (java.sql SQLException)))

(def *application-db* {:classname "org.apache.derby.jdbc.EmbeddedDriver"
                       :subprotocol "derby"
                       :subname "MyDB"
                       :create true
                       })

(defn- execute-migration [direction]
  (fn [[version { migration-fn direction
                  doc :doc }]]
    (info (str (direction {:up "Applying migration "
                           :down "Undoing migration "}) version " " doc))
    (transaction
     (migration-fn)
     (if (= :up direction)
       (insert-values :SchemaVersion
                      [:version]
                      [version])
       (delete-rows :SchemaVersion ["version=?" version])))))

(defn- run-migrations [direction from to]
  "Run the specified migrations on the database"
  (dorun (map (execute-migration direction) (if (= :up direction)
                                              (take (- to from) (nthnext migrations from))
                                              (reverse (take (- from to) (nthnext migrations to)))))))

(defn- create-schema-table-if-needed [direction to]
  (try
   (info "Attempting to create SchemaVersion table")
   (create-table :SchemaVersion [:version :int "NOT NULL UNIQUE"])
   (info "No SchemaVersion table exists - first run installing migrations")
   (try 
    (run-migrations direction 0 to)
    (catch Exception ex
      (warn "Error running migrations: " ex)))
   (catch Exception e
     (when-not (= java.sql.BatchUpdateException (.?. e getCause getClass))
       (throw (SQLException. "Unknown error whilst creating SchemaVersion table" e))))))

(defn migrate
  "Pass it :up or :down and a version to migrate to.  If no arguments
are supplied we assume application of all migrations."
  ([] (migrate :up (count migrations)))
  ([direction to]
     (with-connection *application-db*
       (when (= :up direction)
         (create-schema-table-if-needed direction to))
       (let [current-version (or (with-query-results rs
                                   ["SELECT MAX(version) as version FROM SchemaVersion"]
                                   (:version (first rs)))
                                 0)]
         (run-migrations direction current-version to)))))

Do you plan to or have you converted this into a reusable library?

Maybe one day, but not in the foreseeable future. As I've simply not been doing much Clojure work with databases recently.

The code above was used in a production application and has been tested to work with JavaDB/Derby. I suspect it might need some minor modifications to work on other databases like MySQL. I'd imagine the only function that might require porting would be the create-schema-table-if-needed function as in Derby I couldn't find a way to test for the presence of a table other than by catching exceptions. In other databases this may well be different.

You're more than welcome to use this code under the terms of the Eclipse Public License v1.0

Thanks for the quick reply. I'll give it a try and maybe turn it into a lib, we'll see.

On Fri, Aug 12, 2011 at 14:18, RickMoynihan < ***@***.***>wrote: Maybe one day, but not in the foreseeable future. As I've simply not been doing much Clojure work with databases recently. The code above was used in a production application and has been tested to work with JavaDB/Derby. I suspect it might need some minor modifications to work on other databases like MySQL. I'd imagine the only function that might require porting would be the create-schema-table-if-needed function as in Derby I couldn't find a way to test for the presence of a table other than by catching exceptions. In other databases this may well be different. ## Reply to this email directly or view it on GitHub: https://gist.github.com/315787

## J. Pablo Fernández [email protected] (http://pupeno.com)

Let me know if you do, good luck!