by Dmitri Sotnikov and Chris Allen
Use Korma as a DSL for generating SQL queries and traversing relationships.
Before starting, add [korma "0.3.0-RC6"] and
[org.postgresql/postgresql "9.2-1002-jdbc4"] to your project’s
dependencies or start a REPL using lein-try:
$ lein try korma org.postgresql/postgresqlTo follow along with this recipe, you’ll need a running SQL database and an existing table to connect to. We suggest PostgreSQL.[1]
After you have PostgreSQL running (presumably on localhost:5432), run the following command to create a database for this recipe:
# On Mac:
$ /Applications/Postgres.app/Contents/MacOS/bin/createdb learn_korma
# Everyone else:
$ createdb learn_kormaTo connect to the learn_korma database, use defdb with the postgres helper. Because Korma is a rather large DSL, it is acceptable to :refer :all its contents into model namespaces:
(require '[korma.db :refer :all])
(defdb db
(postgres {:db "learn_korma"}))To interact with a table in your database, define and create what Korma calls entities. Here you’ll define an entity for blog posts:
(defentity posts
(pk :id)
(table :posts) ; Table name
(entity-fields :title :content)) ; Default fields to SELECTNormally you’d use a proper migration library for your schema, but for the sake of simplicity, we’ll create a table manually. Use the exec-raw function to execute raw SQL statements against the database. You should only do this where strictly necessary:
(def create-posts (str "CREATE TABLE posts "
"(id serial, title text, content text,"
"created_on timestamp default current_timestamp);"))
(exec-raw create-posts)Now that the posts table exists, you can invoke insert against posts with a map’s values 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:
(insert posts
(values nil {:title "First post" :content "blah blah blah"}))To retrieve values from the database, query using select. Successful queries will return a sequence of maps, each containing keys representing the column names:
(select posts (limit 1))
;; -> [{:created_on #inst "2013-11-01T19:21:10.652920000-00:00",
;; :content "blah blah blah",
;; :title "First post",
;; :id 1}]To correct or change existing records, use the update macro. Invoke update against posts, providing a set-fields declaration to specify what should change and a where declaration narrowing what records to make those changes to:
(update posts
(set-fields {:title "Best Post"})
(where {:title "First post"}))
;; -> {:title "Best Post", :id 1 ...}The delete macro works similarly to update, but doesn’t take a set-fields declaration:
(delete posts
(where {:title "Best Post"}))
(select posts)
;; -> []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 and abstract common operations.
Korma exposes these abilities through its entity system. Entities are an abstraction over traditional SQL tables that mask the complexity of SQL’s crufty and complicated DDL (data definition language). Via the defentity macro, you have access to all of the power of traditional SQL, packaged in a readable, Clojure-based DSL.
When defining entities with defentity, you can pass in a number of options. Some common options include table to specify a table name, pk to specify the default ID field (primary key), entity-fields to specify the default fields for SELECT statements, or even db to specify which database the entity belongs in.
Entities also simplify defining relations between tables. Entity declaration statements such as has-one, has-many, belongs-to, and many-to-many define relationships to other entities. Consider adding an author to each of our blog posts:
;; Create authors, assuming posts has an author_id
(defentity authors
;; By default, foreign-key will be :authors_id, but that is a little
;; awkward
(has-many posts {:fk :author_id}))
;; Redefine posts such that it assumes it has an author_id
(defentity posts
(belongs-to authors {:fk :author_id}))
;; Create the authors table
(exec-raw "CREATE TABLE authors (id serial, name text);")
;; Add the authors_id field to posts
(exec-raw "ALTER TABLE posts ADD COLUMN author_id int;")
(def ryan (insert authors (values {:name "Ryan"})))
ryan
;; -> {:name "Ryan", :id 1}
(insert posts (values [{:title "My first post!", :author_id (:id ryan)}
{:title "My second post.", :author_id (:id ryan)}]))
(select posts
(where {:author_id (:id ryan)}))
;; -> [{:author_id 1,
;; ...
;; :title "My first post!",
;; :id 4}
;; {:author_id 1,
;; ...
;; :title "My second post.",
;; :id 5}]Stemming from its entity system, Korma provides DSL versions of common SQL statements such as select, update, insert, and delete. One of the most interesting query types is select, which provides support for most every SELECT statement option, include simplified table joins (via its relation helpers). Some notable helpers include aggregate, join, order, group, and having. Chances are, if it is an SQL statement feature, Korma has a helper for it.
Korma’s DSL isn’t only convenient, it’s also composable. Using select* instead of select returns a query as a value, instead of an evaluated result. You can pipeline query values through regular select helpers to build up or store partial queries. Finally, invoke select on a query value to execute it and receive its result:
(defn authors-posts
"Retrieve all posts for a person with a given name"
[name]
(-> (select* posts)
(with authors)
(where {:authors.name name})))
;; Find the title of all posts by the author named "Ryan"
(-> (authors-posts "Ryan")
(where (like :title "%second%"))
(fields :title)
select)
;; -> [{:title "My second post."}]Another convenience Korma provides is default connections. You may have noticed in the examples that we never referred to the db we defined. When only a single connection is defined, it will be used by default and you don’t have to pass it explicitly. If you like, you can define multiple connections and wrap series of statements in a with-db call:
(with-db db
(select (authors-posts "Ryan")))-
The official Korma project page