Getting started

;; First you'll need to add Korma as a dependency in your lein/cake project:
[korma "0.4.0"]

;; You'll also need the JDBC driver for your database. These are easy to find if 
;; you search for "my-db jdbc driver maven".
;; Example for postgres:
[org.postgresql/postgresql "9.2-1002-jdbc4"]
;; Example for h2:
[com.h2database/h2 "1.3.170"]
;; Example for sqlite:
[org.xerial/sqlite-jdbc "3.7.15-M1"]


(use 'korma.db)
(require '[clojure.string :as str])

(def db {:classname   "org.h2.Driver"
         :subprotocol "h2"
         :subname     "resources/db/korma.db"})
;; this creates a connection map

(def db (h2 {:db "resources/db/korma.db"}))
;; this creates the same connection map as before, but
;; using the helper (h2 ...).

(def db (h2 {:db "resources/db/korma.db"
             :user "sa"
             :password ""
             :naming {:keys str/lower-case
                      ;; set map keys to lower
                      :fields str/upper-case}}))
                      ;; but field names are upper
;; you can pass additional options

;; Helpers for common databases:
(def pg (postgres ..))
(def ms (mssql ..))
(def msac (msaccess ..))
(def orc (oracle ..))
(def mys (mysql ..))
(def sqll (sqlite3 ..))
(def h2-db (h2 ..))

;; Pass the connection map to the defdb macro:
(defdb korma-db db)

;; Or without predefining a connection map:
(defdb prod (postgres {:db "korma"
                       :user "korma"
                       :password "kormapass"
                       ;; optional keys
                       :host "myhost"
                       :port "4567"
                       :delimiters ""}))
                       ;; remove delimiters 

To define a connection to a database you use the (defdb) macro, which takes a map of database parameters. Those familiar with will notice this is the same connection map you would use there. To speed things up a bit, Korma provides a set of convenience functions for common database specs.

(defdb) creates a connection pool for your db spec (using the wonderful c3p0 library) to make sure resources are used efficiently. It also sets the last created pool as the default for all queries. As such, connection management is essentially taken care of for you.


(use 'korma.core)
(require '[clojure.string :as str])

(declare users email address state account posts)

(defentity users
  ;; Basic configuration
  (pk :id) ;; by default "id". This line is unnecessary.
           ;; it's used for relationships joins.
  (table :users) ;; by default the name of the symbol.
                 ;; The line above is also unecessary.
  (database db) ;; if none is specified the last defdb
                ;; will be used. Also unnecessary.
  (entity-fields :first :last) ;; default fields for selects

  ;; Mutations
  (prepare (fn [{last :last :as v}]
             (if last
               (assoc v :last (str/upper-case last)) v)))
  ;; apply a function before storing in the db
  ;; in this case the function changes the "last" field
  ;; to upper case.
  (transform (fn [{first :first :as v}]
               (if first
                 (assoc v :first (str/capitalize first)) v)))
  ;; apply a function to all select results
  ;; in this case the function changes the "first" field
  ;; to capitalized.

  ;; Relationships
  (has-one address)
      ;; assumes = address.users_id
  (has-many email)
      ;; assumes = email.users_id
      ;; but gets the results in a second query
      ;; for each element
  (belongs-to account)
      ;; assumes users.account_id =
  (many-to-many posts :users_posts))
      ;; assumes a table users_posts with columns users_id
      ;; and posts_id
      ;; like has-many, also gets the results in a second
      ;; query for each element

;; Subselects can be used as entities too!
(defentity subselect-example
  (table (subselect users
            (where {:active true}))

(defentity email
  (belongs-to users))

(defentity address
  (pk :my_pk) ;; sets the primary key to "my_pk"
  (belongs-to users)
  (belongs-to state {:fk :id_state}))
      ;; you can optionally specify the foreign key
      ;; assumes = address.id_state

(defentity state
  (table :state_st) ;; sets the table to "state_st"
  (has-many address))

(defentity account
  (has-one users))

(defentity posts
  (many-to-many users :users_posts))

Let's suppose that we have some tables in a database. We have a users table with some user data. The user has many emails. It also has one address. It belongs to an account. It has a many to many relationship with posts. Email, address, account, posts are all different tables. We also have a table with states that has a relationship with address. And one last table users_posts to store the many to many relationship.

Entities map one to one with tables and are the initial building block for all your queries. You can specify a number of properties associated with them, such as the table name, an alias for the table, the primary key and so on. You can also set functions to be run before a record goes to the database as the result of an insert/update, or functions to be mapped over the results of a select. This allows you to apply common mutations to your data without having to think about it at every step.

Lastly, entities let you specify the relationships to be used when you do select queries. With these relationships defined, you can then simply use the (with) function in your select query to join the entities and return the results.

Entities offer a great deal of flexibility. The second example shown here demonstrates how you can even use subselects as entities, enabling you to join on these seamlessly within your normal queries.

You need to create entities for each table that participate in a relationship. Remember that when the primary key is not "id", the table name is not the name of the entity defined or the foreign key is not in the format "tablename_id" you have to define them in the entity.

select queries

(select users
  (with address) ;; include other entities based on
                 ;; their relationship
  (with posts) ;; include a many-to-many relationship
               ;; you can use (with) for any relationship
               ;; defined in the entity
  (fields [:first :firstname] :last
      ;; you can alias a field using a vector of [field alias]
  (modifier "DISTINCT") ;; you can add a modifier
  (aggregate (count :*) :cnt :status)
      ;; You specify alias and optionally a field to group by
      ;; available aggregates:
      ;; sum, first, last, min, max, avg, stdev, count
  (where {:first "john"
          :last [like "doe"]
          :date_joined [<= (sqlfn now)]})
      ;; You can use an abritrary sql function by calling
      ;; (sqlfn fn-name & params)
  (join email (= :email.users_id :id))
      ;; You can do joins manually
  (where { [in (subselect email
                          (fields :id)
                          (where {:sent true}))]})
      ;; When necessary, you can use subselects in your
      ;; queries just like you would a normal select
  (order :id :ASC)
  (group :status)
  (having {:cnt [> 3]})
      ;; You can use having clauses with the same syntax used
      ;; by where clauses.
  (limit 3)
  (offset 3))

;;You can also compose select queries over time:
(def base (-> (select* users)
              (fields :first :last)
              (order :created)))

(-> base
    (with :email)
    (where (> :visits 20))

Select queries are the most interesting of the query types and have a number of tools to help make them simple. As discussed in the entities section, you can use the (with) function to include a relation. If you do so, you'll also want to specify the exact fields to be returned in the query using the (fields) function, which takes a variable number of keywords representing the field names you want. The (modifier) function add a modifier to the beginning of a query. Likewise, you can use the (aggregate) function to call one of SQL's aggregators by specifying the function, an alias, and optionally a field to group by. One thing to note in all of this is that fields are always keywords and any without a table prefix are assumed to be for the current entity unless they're aliased.

We'll go more in depth about (where) and (having) clauses below, but as you can see, you have access to all the other parts of a select that you'd expect: (subselect), (join), (group), (order), (limit), and (offset). Subselects work just like a select clause does, but they can be embedded anywhere in your query. Joins allow you to manually control how related tables a brought together by taking a standard where predicate. Group and order clauses will be evaluated in the order they're added to the query. The default ordering direction is ASC.

;; Let's look at where clauses with a little more depth:
;; And everything that you can do with (where), you can
;; also do with (having)
;; To use (having) you need a (group) clause of course.
(-> (select* users)
    (where {:first "john"
            :last "doe"})
    (as-sql)) ;; we use (as-sql) to see the string of SQL
              ;; that will be run. To run this you can
              ;; replace (as-sql) by (exec) or (select)

;; Is the same as:
(-> (select* users)
    (where (and (= :first "john")
                (= :last "doe")))

;; Multiple where's are joined with AND, so this
;; is also the same:
(-> (select* users)
    (where {:first "john"})
    (where {:last "doe"})
;;     ("USERS"."FIRST" = ? AND "USERS"."LAST" = ?)"

;; You can use other predicates for map values too
(-> (select* users)
    (where (or {:age [> 5]}
               {:last [like "doe"]}))

;; Is the same as:
(-> (select* users)
    (where (or (> :age 5)
               (like :last "doe")))
;;     ("USERS"."AGE" > ? OR "USERS"."LAST" LIKE ?)"

;; Available predicates:
[=, like, and, or, >, <, >=, <=, in, not-in, not, not=, between]

(where) and (having) clauses are sort of their own mini-DSL. Anywhere you would provide a clause, you can use a map where each key in the map represents a field and each value is its value. Just like with the fields function, keys specified without a table prefix will be prefixed for the current entity. Also, a field's value can be a vector specifying a different comparison function to be used. Each clause that results from a map will be AND'd together.

You can also call the (where) and (having) predicates like any normal function, allowing you to compose your predicate clauses as if they were standard Clojure code. Fields in Korma are always specified as keywords and will be prefixed appropriately.

The examples here use the (where) clause, but it's the same for the (having) clause.

(select users
 (fields :first)
 (with email
   (fields :email) ;; the context is now email.*
   (where {:email [like "%_test%"]})))

(select users
  (fields :first)
  (where {:last [like "doe"]})
  (with address
    (with state (fields :state)) ;; you can nest withs
    (fields :city :zip :address1 :address2)))

With clauses act almost like selects, in that they can actually be further refined using all the standard functions you would use in a select. This allows for a great deal of flexibility when describing your relations.

set operations

;; Using set operations
  (queries (subselect users
             (where {:id 7}))
           (subselect users
             (where {:id 1}))
           (subselect users
             (where {:id 1})))
  (order :first))
;; union operations remove duplicates

;; You can compose set operations:
(def base (-> (union*)
              (queries (subselect users
                         (where {:id 7})))))
(-> base
    (queries (subselect users
               (where {:id 1})))
    (queries (subselect users
               (where {:id 1})))
;; Same result as above

  (queries (subselect users
             (where {:id 7}))
           (subselect users
             (where {:id 1}))
           (subselect users
             (where {:id 1})))
  (order :first))
;; union-all do not remove duplicates.
;; You can compose union-all with (union-all*)

  (queries (subselect users
             (where {:id [in [1 7]]}))
           (subselect users
             (where {:id 1}))
           (subselect users
             (where {:id 1})))
  (order :first))
;; intersect only keeps what is in all queries.
;; You can compose intersect with (intersect*)

Set operations include union, union-all and intersect operations. You can use the (queries) function to add groups of queries to the operation.

You can't use (select) inside (queries), you have to use the (subselect) macro.

update queries

(update users
  (set-fields {:status "active"
               :beta false})
  (where {:visits [> 10]}))

;; You can also compose updates over time:
(def base (-> (update* users)
              (set-fields {:status "active"})))

(-> base
    (set-fields {:beta false})
    (where {:visits [> 10]})
;; Does the same thing 

Update queries use the (set-fields) function to specifiy the fields to be updated. Multiple calls to set-fields will be merged together, allowing you to build the update over time. Updates also allow where clauses as you would expect.

insert queries

;; You can insert a single value:
(insert users
  (values {:first "john" :last "doe"}))

;; or a collection of several:
(insert users
  (values [{:first "john" :last "doe"}
           {:first "jane" :last "doe"}]))

;; You can also compose inserts:
(def base (-> (insert* users)
            (values {:first "john" :last "doe"})))

(-> base
  (values {:first "jane" :last "doe"})
;; Same thing as the collection insert

Insert queries use the function (values) to add records. It takes either a single map or a collection of maps and returns the id of the first inserted record.

delete queries

(delete users
  (where {:visits [< 3]})) 

;;You can also compose deletes:
(def base (-> (delete* users)
            (where {:visits [< 3]})))

(-> base
  (where {:status [not= "active"]})

Delete queries only allow where clauses and not including one will cause all records for the given entity to be deleted. The result of a delete is the id of the first record deleted.


;; you can get a string of the SQL instead of executing
;; by using the sql-only mode
  (select users))

;; => "SELECT * FROM users"

;; You can print a string of the sql, with the params and
;; return fake results by using the dry-run mode
  (select users
    (where {:age [> 5]})))

;; dry run :: SELECT * FROM users WHERE (user.age > ?) :: [5]
;; => [{:id 1}]

By default, when exec is called, it simply generates the SQL string and params necessary for your query and sends those to your database. Sometimes, however, what you really want is to just generate the string, or even do a dry run where you see the SQL printed to the console. As such, korma includes a couple of other execution modes that allow you to do exactly that. These also apply to queries that are composed over time and then executed using the (exec) function.

;;transaction, rollback, and is-rollback? are in korma.db
  (insert users
    (values {:first "cool"}))
  (insert address
    (values {:address1 "cool"})))

  (if-not (valid?)
  (when-not (is-rollback?)
    (println "success!")))

You can do transactions in Korma simply by using the (transaction) macro, which ensures that all queries executed within it are part of a single transaction. You can then use the (rollback) function to force the transaction to rollback if necessary.

(select users
  (fields :id :first (raw "users.last"))
  (where {:first [like "%_test5"]}))

;;Or when all else fails, you can simply use exec raw
(exec-raw ["SELECT * FROM users WHERE age > ?" [5]] :results) 

You may find yourself in need of functionality that Korma doesn't have. Luckily, there are a couple of facilities that help you here: (raw) which injects a string into a query and (exec-raw) which allows you to write a parameterized query directly.