razorblade

A SQL library for Clojure designed to be primitive yet composable. As with any sharp object, take care not to cut yourself during use.

Motivation

Razorblade is designed to stay as close to raw SQL as possible and yet provide composable interfaces. If you've found yourself using clojure.java.jdbc and tiring of string manipulation, or using Korma and straining when writing gnarlier queries or deviating from the Korma happy path, this library may be for you.

Razorblade's goals are as follows:

• Inserting raw SQL in any part of your query should be extremely simple.
• Conditionally including/excluding parts of a query should be extremely simple.
• The SQL generated by Razorblade should be immediately obvious.
• Support MySQL and Postgres DB querying (others welcome too but not a strict goal of this).

Korma, ClojureQL, and native clojure.java.jdbc are great options and you should investigate them before deciding whether Razorblade is right for you.

Razorblade may not be for you if you experience any of the following symptoms:

• You only build simple queries (use Korma or clojure.java.jdbc)
• You don't know SQL that well or desire a non-SQL representation of querying (use Korma or ClojureQL respectively)
• You want a library that can intelligently build SQL based on schema / entity relationships. (use Korma)

Remaining Items for a beta release

• Support limit, order, group by, having operations
• Provide for object sanitization somehow. Thinking something like as-fields [args] body to avoid (field ...) calls
• Provide Korma-style before/after transformation examples (should stay separate from exec/query calls though. Thinking another with-* wrapper

Usage

Not recommended just yet. The bold may proceed, however.

All functions are built to return a map of :text and :params. Arguments can be virtually anything and will be coerced as appropriate.

In your project.clj:

  [razorblade "0.1.0-SNAPSHOT"]

; all examples below assume a setup like so
(ns my.cool.app
  (:use [razorblade.core :only [select update delete insert insert-into set-fields from where join prefix combine-clauses]])
  (:require [razorblade.op :as op]))

; building simple queries is straightforward.
(select :name (from :dingos))
;yields
{:text "select name from dingos" :params []}

; keywords are coerced to db-representative table/field names
(select [:dingos.baby-eaten-count :dingos.name] (from :dingos))
;yields
{:text "select dingos.baby_eaten_count, dingos.name from dingos" :params []}

; logical operators live in the op namespace to avoid conflicts with their Clojure equivalents
(select :*
  (from :dingos)
  (where (op/and
           (op/= :baby-eaten-count 5)
           (op/is-null :perished-at))))
;yields
{:text "select * from dingos where baby_eaten_count = 5 AND perished_at IS NULL" :params []}

; string literals and user input must go through the field function, most other literals can
(select :*
  (from :dingos)
  (where (op/and
           (op/= :baby-eaten-count (field 5))
           (op/in :name (field ["richard" "blueberry"])))))
;yields
{:text "select * from dingos where baby_eaten_count = ? AND name in (?,?)" :params [5 "richard" "blueberry"]}

; raw sql is always welcome
(select :*
  (from :dingos)
  (where (op/and
           (op/< :perished-at "now()")
           (op/in :name (field ["richard" "blueberry"])))))
;yields
{:text "select * from dingos where perished_at < now() AND name in (?,?)" :params [5 "richard" "blueberry"]}

;this does the same thing
(select :*
  (from :dingos)
  (where (op/and
           "perished_at < now()"
           (op/in :name (field ["richard" "blueberry"])))))

; nil clauses get removed
(select :*
  (from :dingos)
  (where (op/and
           (when false (op/= :baby-eaten-count (field 5)))
           (op/in :name (field ["richard" "blueberry"])))))
;yields
{:text "select * from dingos where name in (?,?)" :params ["richard" "blueberry"]}

; executing queries is straightforward too
(query (select :name (from :dingos)))

; and of course you can always pass in your own. The above is equivalent to
(query {:text "select name from dingos" :params []})
; and
(query "select name from dingos")

; there are two ways to configure Razorblade. Both use the same configuration as clojure.java.jdbc - http://clojure.github.com/java.jdbc/#clojure.java.jdbc/with-connection
; Traditional binding
(binding [razorblade.core/*db-spec* "postgres://localhost/my-db"]
  (query "select name from dingos"))

; You can also set a default if you'd rather
(razorblade.core/set-default-db-spec "postgres://localhost/my-db")

; *db-spec* always overrides the default when it is bound

; of course, you can delete
; however, anything that doesn't return records should use (exec ...), not (query ...)
(delete (from :living-dingos)
  (where
    (op/or
      (op/<> :status (field "living"))
      (op/< :deceased-at "now()"))))
; yields
{:text "delete from living_dingos where (status <> ?) OR (deceased_at < now())" :params ["living"]}

; you can update too. Note that a set statement is (set-fields ...) so as not to conflict with core/set
(update :living-dingos
  (set-fields (op/= :status (field "deceased"))
              (op/= :deceased-by-id 4))
  (where (op/is-not-null :deceased-at)))
; yields
{:text "update living_dingos set status = ?, deceased_by_id = 4 where deceased_at is not null" :params ["deceased"]}

; inserts are more-or-less the same as in clojure.java.jdbc, although table names go through clause transformation
(insert :living-dingos
  {:name "Raspberry" :spottings 0}
  {:name "Robert"    :spottings 5})

; insert-into is a normal statement though
(insert-into :living_dingos [:name :spottings]
  (select [:name 0]
    (from :dingos)
    (where (op/<> :status (field "deceased")))))
;yields
{:text "insert into living_dingos (name,spottings) select name, 0 from dingos where status <> ?" :params []}

; you can build your own operators too. It's perhaps a bit awkward right now, though (patches welcome - much is missing)

(defn cast [field-clause as-clause]
  (combine-clauses ["CAST"
                    (combine-clauses [field-clause as-clause]
                                     :join-str " AS "
                                     :wrap-parens true)]
                   :join-str ""))
; (cast :foo "int") yields
{:text "CAST(foo AS int)" :params []}

; Any function that returns a map containing :text and :params will work
; but using combine-clauses will translate most arguments into their appropriate representation

; This scales to more intricate queries
  (select (prefix :dingos [:id :name])
   (from :dingos)
   (join :dingo-facts (op/= :dingos.id :dingo-facts.dingo-id))
   (where
    (op/or
     (op/and
      "dingo_facts.created_at > (NOW() - INTERVAL '1 DAY')"
      (op/> :dingo-facts.updated-at "NOW() - INTERVAL '1 DAY'")
      (op/> :dingo-facts.last-seen-at (op/- "now()" "INTERVAL '1 DAY')"))
      (op/= :dingo-facts.user-id (:id user))
      (op/in :dingo-facts.category excluded-categories))
     (op/and
      (op/= :user-id (:id user))
      (op/not-in :dingo-facts.category (field excluded-categories)))))
;yields
{:text "select dingos.id, dingos.name from dingos inner join dingo_facts on dingos.id = dingo_facts.dingo_id where ((dingo_facts.created_at > (NOW() - INTERVAL '1 DAY')) AND (dingo_facts.updated_at > NOW() - INTERVAL '1 DAY') AND (dingo_facts.last_seen_at > now() - INTERVAL '1 DAY')) AND (dingo_facts.user_id = 4) AND (dingo_facts.category in (1,2,3,4))) OR ((user_id = 4) AND (dingo_facts.category not in (1,2,3,4)))"
 :params []}

License

Copyright © 2013 Scott Parker

Distributed under the Eclipse Public License, the same as Clojure.