simple-crud-jdbc

A clojure macro for executing simple CRUD queries on any SQL database.

Installation

[simple-crud-jdbc "0.2.0"]

Synopsis

(require '[simple-crud-jdbc :refer [crud-for]])
(require '[clojure.java.jdbc :refer [create-table-ddl db-do-commands]])
(def db-conn {:jdbc-url "jdbc:postresql://localhost/crud?user=postgres"})
(db-do-commands db-conn (jdbc/create-table-ddl :fruit
                                               [[:id "serial" :primary :key]
                                                [:name "varchar(32)"]
                                                [:appearance "varchar(32)"]
                                                [:cost :int]])
(crud-for :fruit {:connection db-conn})

;; Create
(create! {:name "pear", :appearance "pear shaped", :cost 230})
; => {:id 1 :name "pear" :appearance "pear shaped" :cost 230}
(create! {:name "grape", :appearance "purple", :cost 210})
; => {:id 2, :name "grape", :appearance "purple", :cost 210}
(create! {:name "grapefruit", :appearance "grapefruit shaped", :cost 100})
; => {:id 3, :name "grapefruit", :appearance "grapefruit shaped", :cost 100}

;; Read
(read-one {:id 2})
; => {:id 2, :name "grape", :appearance "purple", :cost 210}
(read-all {:id 2})
; => [{:id 2, :name "grape", :appearance "purple", :cost 210}]
(set (read-all [:like :name "grape%"]))
; => #{{:id 2, :name "grape", :appearance "purple", :cost 210}
;      {:id 3, :name "grapefruit", :appearance "grapefruit shaped", :cost 100}}
(set (read-all true))
; equivalently, (read-all {})
; => #{{:id 2, :name "grape", :appearance "purple", :cost 210}
;      {:id 3, :name "grapefruit", :appearance "grapefruit shaped", :cost 100}
;      {:id 1 :name "pear" :appearance "pear shaped" :cost 230}}

;; Update
(update! {:appearance "small & purple" :cost 240} {:id 2})
; => 1
(read-one {:id 2})
; => {:id 2, :name "grape", :appearance "small & purple", :cost 240}
(update! {:cost 90} [:like :appearance "%shaped"])
; => 2
(set (read-all [:like :appearance "%shaped"]))
; => #{{:id 3, :name "grapefruit", :appearance "grapefruit shaped", :cost 90}
;      {:id 1 :name "pear" :appearance "pear shaped" :cost 90}}

;; Delete
(delete! [:or [:= :id 1] [:= :id 3]])
; => 2
(read-all true)
; => [{:id 2, :name "grape", :appearance "purple", :cost 210}]
(delete! {:name "grape"})
; => 1
(read-all true)
; => []

Rationale

For any non-trivial database operations, I usually reach for yesql or hugsql. Both of these offer extreme flexibility by creating clojure fns from raw SQL files. But the simple things, like creating an single row from a map of attributes or finding a row or a set of rows based on the value of some attributes can involve a frustrating amount of boilerplate.

simple-crud-jdbc handles those simple cases when all you need to touch is one table. It has one macro, crud-for, which takes a keywordized table name and a map of options and generates fns to create, read, update, and delete rows for that table. It uses honeysql for flexible where clauses in Read, Update, and Delete operations and provides a map shorthand for when your where clause is a conjunction of zero or more equality statements.

(crud-for [table & [options]])

Generated fns

These are the default fns generated by crud-for:

• (create! [conn attrs]): Creates a row in the table with the specified attrs. Return value is database dependent: for PostgreSQL, returns a map of the created row.

• (read-one [conn where]): Performs a SELECT * FROM <table> WHERE <where> LIMIT 1. No order is defined, and if where does not uniquely specify a row, which row you get back is not defined. Returns a map of the row, or nil if none matches.

• (read-all [conn where]): Performs SELECT * FROM <table> WHERE <where>. Return a seq of the results. No ordering is guaranteed.

• (update! [conn attrs where]): Overwrites the values of all columns in (keys attrs) for rows matching where with the associated values in attrs. Returns the number of rows affected.

• (delete! [conn where]): Deletes all rows matching where. Return the number of rows affected.

Where clauses

TBD;

Options

• :connection: An implicit connection. Must be a symbol or nil. Default is nil.

  When present, two arity methods for every fn defined are created. One takes a database connection and attribute/where clause data as appropriate. The other does not take a connection, and instead uses the connection stored in the var of the :connection option.

  When :connection is nil, only generates the full arity fns, requiring an explicit connection to be passed on each use.

• :create, :read-one, :read-all, :update, :delete: Metadata for the matching fn. Symbol, map, or nil. Defaults create!, read-one, read-all, update!, delete!, respectively.

  When a symbol is supplied it is the name of the fn defined for that operation. When nil is supplied, no fn for that operation is generated. When a map is supplied, it must have a :var key with a symbol, which will be the var name that is defed for that operation. The rest of the map is used as metadata for that var.

  (crud-for :fruit {:read-all all-fruit, :update nil, :delete nil
                    :create {:var raw-fruit-create!, :private true
                             :doc (str "Raw create. Allows id to be specified."
                                       " Wrap with more restrictive fn.")}})

  This will do something equivalent to the following:

  (defn- raw-fruit-create!
    "Raw create. Allows id to be specified. Wrap with more restrictive fn."
    [conn attrs] ...create code...)
  (defn read-one [conn where] ...read-one code...)
  (defn all-fruit [conn where] ...read-all code...)
  ; no update or delete fns created

Future

• better tests; Test on other RDBMSs
• extensible where clause shorthands (eg, Long => {:id Long})
• default sort?
• support clojure.jdbc?
• schema/spec support?