Skip to content

Instantly share code, notes, and snippets.

@RickMoynihan
Created February 26, 2010 15:09
Show Gist options
  • Save RickMoynihan/315787 to your computer and use it in GitHub Desktop.
Save RickMoynihan/315787 to your computer and use it in GitHub Desktop.
Rails like Database Migrations in Clojure... Using clojure.contrib.sql
(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)
}))
;;; 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)))))
@RickMoynihan
Copy link
Author

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

@pupeno
Copy link

pupeno commented Aug 12, 2011 via email

@RickMoynihan
Copy link
Author

Let me know if you do, good luck!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment