Skip to content
This repository

Example

Test data

Here’s an extended example of working with a database that might hold data for
a a blog or website. Below is the data I’m working with (in PostgreSQL).

foo=# select * from post;
 id |     title     |            content             | category_id 
----+---------------+--------------------------------+-------------
  1 | Cows rule     | Everyone likes cows, right?    |           1
  2 | Clojure rocks | Everyone likes Clojure, right? |           2
foo=# select * from category;
 id |    name     
----+-------------
  1 | Cows
  2 | Programming
foo=# select * from tag;
 id |     name      
----+---------------
  1 | Current event
  2 | Editorial
  3 | Elucidation
foo=# select * from comment;
 id | post_id | name  | content 
----+---------+-------+---------
  1 |       2 | Brian | Me too!
foo=# select * from post_tag;
 id | post_id | tag_id 
----+---------+--------
  1 |       1 |      1
  2 |       1 |      3
  3 |       2 |      2
  4 |       2 |      3

We’ll use this DB with clojure.contrib.sql:

(require '[clojure.contrib [sql :as sql]])
(def db {:classname "org.postgresql.Driver"
         :subprotocol "postgresql"
         :subname "//localhost/foo"
         :username "foo"
         :password "foo"})

Oyako setup

First we define the relationships between the tables. Supported relationship
types are :has-one, :has-many, :belongs-to and :habtm (“has and belongs to
many”, to join two tables via a third join table).

(require '(oyako [core :as oyako]))
(def my-data (oyako/make-datamap db
              [:post     [belongs-to :category]
                         [has-many   :comment]
                         [habtm      :tag via :post_tag]]
              [:tag      [habtm :post via :post_tag]]
              [:category [has-many :post]]
              [:comment  [belongs-to :post]]))

make-datamap is a convenience macro to help you build datamaps. You should
pass in a database handle, which will be used whenever you run queries using
this datamap. Oyako will also use the DB handle to fetch and store some
metadata about your tables.

A datamap is just a hash-map and you can create it by hand if you want. Our
datamap looks like this (yikes):

user> my-data
{:comment {:post {:child-key :id, :parent-key :post_id, :as :post,
:child :post, :parent :comment, :type :belongs-to}}, :category {:post
{:child-key :category_id, :parent-key :id, :as :post, :child :post,
:parent :category, :type :has-many}}, :tag {:post {:child-key :id,
:parent-key :id, :as :post, :child :post, :parent :tag, :type :habtm,
:via :post_tag, :parent-join-key :tag_id, :child-join-key :post_id}},
:post {:tag {:child-key :id, :parent-key :id, :as :tag, :child :tag,
:parent :post, :type :habtm, :via :post_tag, :parent-join-key :post_id,
:child-join-key :tag_id}, :comment {:child-key :post_id, :parent-key
:id, :as :comment, :child :comment, :parent :post, :type :has-many},
:category {:child-key :id, :parent-key :category_id, :as :category,
:child :category, :parent :post, :type :belongs-to}}}

Yeah, that’s why you want to use the macro to build it.

Oyako will guess at the proper column names based on the names of the tables.
By default primary keys are id and foreign keys are <table>_id with the
table-name singularized. You can override this behavior by passing in column
names explicitly or (TODO) by passing in your own inflector function.

To use this datamap, there’s a with-datamap macro. This uses the database
handle you specified earlier to open a DB connection, and executes queries in
the context of some datamap.

(with-datamap my-data ...)

For convenience, you could write a macro to shorten this a bit:

(defmacro with-db [& body] `(with-datamap my-data ~@body))

For even more convenience, use def-helper to define this macro for you:

(oyako/def-helper with-db my-data)

Note, this captures the value of my-data. If you want the ability to
re-bind these vars and have the helper function see the new values, do this:

(oyako/def-helper with-db #'my-data)

Anatomy of a query

A query is a hash-map that looks like this:

{:table :post
 :columns [:id, :title]
 :where {:id 2}
 :includes [{:table :comment}]}

If you want to make this hash yourself, go ahead.

user> (with-db (oyako/fetch-all {:table :post
                                 :columns [:id :title]
                                 :where {:id 2}
                                 :includes [{:table :comment}]}))
({:id 2,
  :title "Clojure rocks",
  :comment [{:id 1, :post_id 2, :name "Brian", :content "Me too!"}]})

Thankfully there are helper functions to help you build these things.
Everything starts with query:

user> (require '(oyako [query :as q]))
nil
user> (q/query :post)
{:table :post}

Modifications to the query are made by chaining it through functions that
add or modify clauses on the query. For example:

user> (-> (q/query :post)
          (q/where {:id 2})
          (q/includes :comment))
{:includes [{:table :comment}], :where {:id 2}, :table :post}

This allows you easily store queries and tweak them later. Calling where
or includes multiple times will stack; order, limit and offset will
operate in a last-one-wins manner.

Chaining functions is common enough to warrant a shortcut. This
is equivalent to the example above:

user> (q/query-> :post q/where {:id 2} q/includes :comment)

query-> takes a table name, then function/argument pairs, and threads
the query through the functions in order.

To make this convenient, you probably want to import the query functions from
oyako.query into your namespace via use. If you want to avoid the
namespace pollution, you can use keywords. Oyako will translate the keywords
into functions. So this is also equivalent:

user> (q/query-> :post :where {:id 2} :includes :comment)

You can “register” your own keywords with the query function by defining
methods on the oyako.query/clause multimethod. There’s a convenience function
register-query-clause to help with this.

This can be useful to extend the query language Oyako understands All parts of
Oyako can then take advantage. Example:

user> (defn id [query x] (q/where query ["id = ?" x]))
#'user/id
user> (q/register-query-clause :id id)
#<MultiFn clojure.lang.MultiFn@7af3e0>
user> (q/query-> :post :id 1)
{:where {"id = ?" (1)}, :table :post}

Fetching data

oyako.core/fetch-all uses a datamap and a query to fetch data. Remember
that with-db (which we defined above) will tell Oyako which database handle
and datamap to use.

Most basic, fetch everything from one table:

user> (with-db (oyako/fetch-all :post))
({:id 1,
  :title "Cows rule",
  :content "Everyone likes cows, right?",
  :category_id 1}
 {:id 2,
  :title "Clojure rocks",
  :content "Everyone likes Clojure, right?",
  :category_id 2})

You can also fetch “one” row rather than “all”.

user> (with-db (oyako/fetch-one :post))
{:id 1,
 :title "Cows rule",
 :content "Everyone likes cows, right?",
 :category_id 1}

fetch-one returns the first record it finds. “First” is undefined
unless you specify an order:

user> (with-db (oyako/fetch-one :post :order :id))
{:id 1,
 :title "Cows rule",
 :content "Everyone likes cows, right?",
 :category_id 1}

To add an SQL WHERE clause to your query, use :where. You can
use a string, vector (for parameterized queries), or hash-map specifying
key/value pairs. WHERE clauses are ANDed together.

user> (with-db (oyako/fetch-one :post :where ["id = ?" 2]))
{:id 2,
 :title "Clojure rocks",
 :content "Everyone likes Clojure, right?",
 :category_id 2}
;; equivalent: (with-db (oyako/fetch-one :post :where {:id 2}))

More advanced fetching

Now let’s fetch posts but also include categories, tags and comments.

user> (with-db (oyako/fetch-all :post :includes [:comment :category :tag]))
({:id 1,
  :title "Cows rule",
  :content "Everyone likes cows, right?",
  :category_id 1,
  :comment nil,
  :tag [{:id 1, :name "Current event"}],
  :category {:id 1, :name "Cows"}}
 {:id 2,
  :title "Clojure rocks",
  :content "Everyone likes Clojure, right?",
  :category_id 2,
  :comment [{:id 1, :post_id 2, :name "Brian", :content "Me too!"}],
  :tag [{:id 2, :name "Editorial"}],
  :category {:id 2, :name "Programming"}})

Notice how each post has a vector of tags and comments, but a single category.
Oyako is smart enough to figure this out based on the datamap.

includes can be a keyword, or a query. Specify multiple includes by
putting them in a vector.

Suppose we want all tags, all posts for each tag, and the category for
each post.

user> (with-db (oyako/fetch-all :tag :includes (q/query-> :post :includes :category)))
({:id 1,
  :name "Current event",
  :post
  [{:id 1,
    :title "Cows rule",
    :content "Everyone likes cows, right?",
    :category_id 1,
    :category {:id 1, :name "Cows"}}]}
 {:id 2,
  :name "Editorial",
  :post
  [{:id 2,
    :title "Clojure rocks",
    :content "Everyone likes Clojure, right?",
    :category_id 2,
    :category {:id 2, :name "Programming"}}]}
 {:id 3,
  :name "Elucidation",
  :post
  [{:id 2,
    :title "Clojure rocks",
    :content "Everyone likes Clojure, right?",
    :category_id 2,
    :category {:id 2, :name "Programming"}}
   {:id 1,
    :title "Cows rule",
    :content "Everyone likes cows, right?",
    :category_id 1,
    :category {:id 1, :name "Cows"}}]})

How many queries did this take? Just four.

user> (oyako/debug (with-db (oyako/fetch-all :tag :includes (q/query-> :post :includes :category))))
["SELECT \"tag\".\"id\", \"tag\".\"name\" FROM \"tag\";"]
["SELECT \"post\".\"id\", ... FROM \"post\" WHERE \"post\".\"id\" IN (1, 2, 3);"]
["SELECT \"category\".\"id\", ... FROM \"category\" WHERE \"category\".\"id\" IN (1, 2);"]
["SELECT * FROM \"post_tag\" WHERE \"post_tag\".\"post_id\" IN (1, 2) AND \"post_tag\".\"tag_id\" IN (1, 2, 3);"]

You can also specify “WHERE”, “LIMIT”, “ORDER” clauses for subqueries:

user> (with-db (oyako/fetch-all :post :where {:id 2}
                                :includes (q/query-> :tag :where {:name "Elucidation"})))
({:id 2,
  :title "Clojure rocks",
  :content "Everyone likes Clojure, right?",
  :category_id 2,
  :tag [{:id 3, :name "Elucidation"}]})

Oyako also adds metadata so you can tell what table your data came from.

user> (meta (with-db (oyako/fetch-one :post)))
{:table :post, :columns (:id :title :content :category_id)}

N+1 queries

Here is a simple example showing the N+1 queries problem Oyako purports to
solve. Given a table foo and a table baz where foo has-many baz, a naive
solution for fetching all foos with their associated bazzes might be something
like this:

(defn naive []
 (sql/with-connection db
   (dorun
    (for [foo (sql/with-query-results res
                ["SELECT * FROM foo"]
                (doall res))]
      (assoc foo :baz
             (sql/with-query-results res
               ["SELECT * FROM baz WHERE foo_id = ?" (:id foo)]
               (doall res)))))))

This does one query to get the foos and then one query for every foo, to get
the associated bazzes (hence 1 foo + N baz queries). This is not good.

Now using Oyako:

(def foo (oyako/make-datamap db [:foo [has-many :baz]]))
(oyako/def-helper with-foo db foo)
(defn oyako []
  (dorun
   (with-foo
     (oyako/fetch-all :foo includes :baz))))

The results are equivalent:

user> (= (oyako) (naive))
true

But Oyako is a bit faster:

user> (time (naive))
"Elapsed time: 829.874007 msecs"
nil
user> (time (oyako))
"Elapsed time: 23.97307 msecs"
nil
Something went wrong with that request. Please try again.