Permalink
Switch branches/tags
Nothing to show
Find file
Fetching contributors…
Cannot retrieve contributors at this time
378 lines (263 sloc) 9.9 KB

Working with Korma for SQL

Problem

You want to work with data stored in a relational database without writing SQL by hand.

Solution

Use Korma as a DSL for generating SQL queries and traversing relationships.

First, fire up a fresh Leiningen project with Korma (the SQL library) and PostgreSQL as dependencies.

If you’re a Mac user and don’t have PostgreSQL installed yet, you can go here http://postgresapp.com/ for an easy to use dmg.

After you’ve got PostgreSQL running (presumably on localhost at port 5432) you run the following command to create the database we’ll be using for our recipes:

createdb -h 127.0.0.1 learnkorma

Now to create the project:

lein new learn-korma

Then the project.clj with the appropriate dependencies:

(defproject learn-korma "0.0.1-doesnt-matter-youre-not-shipping-this"
  :description "Doesn't matter"
  :url "http://uss-who-cares.com/"
  :license {:name "Eclipse Public License"
            :url "http://www.eclipse.org/legal/epl-v10.html"}
  :dependencies [[org.clojure/clojure "1.5.1"]
                 [korma "0.3.0-RC6"]
                 [org.postgresql/postgresql "9.2-1002-jdbc4"]])

Next, define a database connection. Korma uses c3p0 to create pooled connections.

(require '[korma.db :refer :all])

(defdb db
  (postgres {:db "learnkorma"}))

Next, you will need to define and create your entities. Entities represent individual tables in your database. Here you’ll define an entity for blog posts:

(require '[korma.core :refer :all])

(defentity posts
  (pk :id)
  ;; Upper-case table names by default in H2. We handled it for the columns->keys earlier with naming.
  (table :posts))

;; serial is the default Postgres type for autoincrementing integer primary keys
(def create-posts (str "CREATE TABLE posts (id serial, title text, content text, created_on timestamp default current_timestamp);"))

;; Korma function to exec raw SQL, should only be used where strictly necessary.
(exec-raw create-posts)

Normally you’d use a proper migration library for your schema, but we’re keeping it simple for the sake of demonstration.

When only a single connection is defined it will be used by default and you don’t have to pass it in explicitly. You can use insert to add records to the database. Each record is represented by a map. The names of the keys in the map must match the names of the columns in the database.

We’re using entity-fields to define which fields we want to retrieve by default in select queries that don’t have fields specified.

(insert posts
  (values {:title "First post" :content "blah blah blah"}))

You can then query the data using select. If the query is successful a sequence of maps will be returned. Each map will contain keys representing the column names.

(select posts (limit 1))

You can also update existing records using update:

(update posts
  (set-fields {:title "Best Post"})
  (where {:title "First post"}))

Deleting records is done using delete:

(delete posts
  (where {:title "Best Post"}))
Discussion

Korma provides a simple and intuitive way to construct SQL queries from Clojure. The advantage of using Korma is that the queries are written as regular code instead of SQL strings. You can easily compose queries together and abstract common operations.

Korma provides many additional features in addition to those you saw above. Let’s examine some of these below.

When defining entities you can pass in a number of options. These include table to specify the table name directly, entity-fields for default fields to be selected:

You can also define entity relations using the has-one, has-many, belongs-to and many-to-many:

(defentity orders
  ;; order is a reserved name in korma and H2.
  (database db)
  (table :orders))

(defentity billable
  (database db)
  (table :billable))

;; forward declaring customer so address can refer to it before it exists
(declare customer)

(defentity address
  (database db)
  (table :address))

(defentity phone
  (database db)
  ;; WHO WAS
  (table :phone))

(defentity customer
  ;; specify which database directly, using the one you defined earlier
  (database db)

  (table :customer)

  ;; By default assumed id as a primary key
  ;; produces query:
  ;; "customer.id = address.customer_id"
  (has-one address)
  (has-many phone)

  ;; You can override the default foreign key
  ;; name by using :fk as seen below
  (belongs-to billable {:fk :cust_id})
  (many-to-many orders :customer_orders))


(def create-orders (str "CREATE TABLE orders (id serial, billable_id int);"))

(def create-billable (str "CREATE TABLE billable (id serial, cust_id int, item text);"))

(def create-address (str "CREATE TABLE address (id serial, customer_id int, addy text, current boolean default true);"))

(def create-phone (str "CREATE TABLE phone (id serial, customer_id int, phone text);"))

(def create-customer (str "CREATE TABLE customer (id serial, name text, tentacles boolean, registered timestamp default current_timestamp);"))

(doseq [mah-sql [create-orders create-billable create-address create-phone create-customer]]
  ;; doseq is used when you want to perform a side-effecting function on each element of a seq
  ;; The implication when you see "do" is that you're doing something for side-effects
  ;; Also exec-raw allows you to pass an explicit database parameter.
  (exec-raw db mah-sql))

;; doseq just returns nil when it's done. It's still an expression just like everything
;; else in Clojure despite being ostensibly for side effects.

Korma also allows us to create subselects

(defentity sub-customers
  (table (subselect customer
           (where :orders_pending))
         :customersWithOrders))

Creating test data for the upcoming queries:

;; You can insert multiple rows at a time by passing a vector to "values"
(insert customer
        (values [{:name "Dmitri"  :tentacles false}
                 {:name "Chris"   :tentacles false}
                 {:name "Brandy"  :tentacles false}
                 {:name "Cthulhu" :tentacles true}]))

(def dmitri  (first (select customer
                    (where {:name "Dmitri"}))))
(def chris   (first (select customer
                    (where {:name "Chris"}))))
(def brandy  (first (select customer
                    (where {:name "Brandy"}))))
(def cthulhu (first (select customer
                    (where {:name "Cthulhu"}))))

(insert billable
        (values {:cust_id (:id brandy) :item "Chainsaw for cutting down trees in the backyard"}))
(insert billable
        (values {:cust_id (:id dmitri) :item "Caviar"}))
(insert billable
        (values {:cust_id (:id chris) :item "Bottles of whiskey for bribery"}))
(insert address
        (values {:customer_id (:id chris) :addy "San Francisco!"}))
(insert address
        (values {:customer_id (:id dmitri) :addy "Elsewhere"}))
(insert address
        (values {:customer_id (:id brandy) :addy "San Francisco!"}))

Selection queries define all the common relational operations such as aggregate, join, order, group and having:

(select customer
  (fields [:tentacles])
  (group :tentacles))

You can include results from other related entities using the with clause:

(select customer
  (with address))

Korma also allows doing manual joins as can be seen below:

(select customer
  (join address (= :address.customer_id :id)))
(select customer
  (with address)
  (where {:address.id
          [in (subselect address
              (fields :id)
              (where {:current true}))]}))

Korma queries are comoposable

(-> (select* customer) (with :address) (select))

Queries can be further decorated using the modifier clause:

(select customer
  (modifier "DISTINCT"))

SQL functions can be called using sqlfn followed by the name and optional parameters:

(select customer
  (where {:registered [<= (sqlfn now)]}))

When something can’t be expressed in Korma you can use raw queries:

(exec-raw ["SELECT * FROM customer WHERE id = ?" [4]] :results)

It should also be noted that Korma can enable some rather nice query composition based APIs:

(defn customers []
  (-> (select* customer) (with address)))

(defn constrainer [field]
  (fn [table value]
    (-> table (where {field value}))))

(def customer-fields [:tentacles :id :name :registered])
(def all-constrainers (map constrainer customer-fields))

;; closed over function via lexical scope
(def constrain-tentacles (first all-constrainers))

(defn yay-area-residents [customers]
  (-> customers (where {:address.addy "San Francisco!"})))

(defn customers-with-tentacles []
      (-> (customers) (constrain-tentacles true) (select)))

(defn customers-without-tentacles []
      (-> (customers) (constrain-tentacles false) (select)))

(defn customers-without-tentacles-in-sf []
      (-> (customers) (constrain-tentacles false) (yay-area-residents) (select)))
See Also
  • For more information see the official project page.