Book Image

Clojure Web Development Essentials

By : Ryan Baldwin
Book Image

Clojure Web Development Essentials

By: Ryan Baldwin

Overview of this book

Table of Contents (19 chapters)
Clojure Web Development Essentials
About the Author
About the Reviewers

The Quick Korma Crash Course

This is the ultra quick and dirty guide to Korma. We'll use the existing artists and albums tables in the hipstr database, between which a simple 1-artist-to-many-albums relationship exists:

We'll gloss over just enough to make use of Korma for these two tables and how they relate to one another in the database. You can get a far more detailed and richer overview of everything Korma provides by visiting the official site at

Define the database specification

You define the database Korma will use by using the korma.db/defdb macro. The defdb macro accepts a standard JDBC database map, similar to the one we created in the hipstr.models.connection namespace:

(defdb hipstr-db {:classname   "org.postgresql.Driver"
                  :subprotocol "postgresql"
                  :subname     "//localhost/postgres"
                  :user        "hipstr"
                  :password    "p455w0rd"}

This defines the database specification for our local hipstr database. By default, all Korma entities, unless otherwise stated, will use the most recently defined defdb.

Korma entities

An entity is a Korma representation of a database table. Each database table we want to interact with will be done so through an entity. Picture an entity as being an object version of a database table.

An entity is defined by using the defentity macro. By default, the name of the entity maps to the table. So, in our case, the artists and albums tables would each have the following defentity declarations:

(use 'korma.core)
(defentity artists)
(defentity albums)

The preceding explanation is the most basic definition of entities, and it makes some assumptions, which we'll override later on.

Defining the primary key

Korma assumes that the primary key for the entity is mapped to either an id or [entityname]_id column on the table. However, our tables do not conform to that assumption, as our primary keys are artists.artist_id and albums.album_id respectively. We can override the default primary keys using korma.core's pk function:

(defentity artists
  (pk :artist_id))
(defentity albums
  (pk :album_id))

Defining relationships between entities

Korma allows us to define the one-to-many relationship between artists and albums. We do this by using the has-many function on the artists table, and the belongs-to function on the albums table:

(defentity artists
  (pk :artist_id)
  (has-many albums))
(defentity albums
  (pk :album_id)
  (belongs-to artists {:fk :artist_id}))                   ;#1

Notice at the #1, that we have to define the foreign key. This is because the foreign key doesn't conform to Korma's assumptions of id or [entityname]_id. Defining these relationships provides Korma with join information for the generated SQL.

Constructing SELECT queries

Select queries are made using the select function, followed by an entity, and an optional body of forms. At its simplest, we can select all the records in a table by doing the following:

(select artists)

This will return all the columns of all the artists in our artists table.

Alternatively, we can restrict which fields to retrieve using the fields function:

(select artists
  (fields :artist_id :name))

This will return only the artist_id and name columns of all the artists in the table.

We can provide a where clause for filtering results by using the where function, which accepts a map of key/value pairs:

(select artists
  (fields :artist_id :name)
  (where {:name "Brant" :artist_id 10}))

The preceding code will select all the artists with the name Brant and an artist_id that is 10 (admittedly, kind of a useless query). If we wanted to select all the artists with the name Brant or the name Smokey Fouler, we could provide a series of maps tied together using the or function:

(select artists
        (fields :artist_id :name)
        (where (or {:name "Brant"}
                   {:name "Smokey Fouler"})))

Conversely, we can make multiple calls to where, which will "and" all the clauses together:

(select artists
        (fields :artist_id :name)
        (where (or {:name "Brant"}
                   {:name "Smokey Fouler"}))
        (where (not (= :updated_at :created_at))))

On our recently-added albums page, we return the ten most recent albums. We do this by using Korma's limit and order functions:

(select albums
        (order :created_at :DESC)
        (limit 10))

Additionally, we can join the albums table back to the artists table and restrict which artists' albums are returned by using a combination of Korma's join and where functions:

(select albums
  (join artists)
  (where { "Brant"}))

This will return all the columns with only Brant's albums. Additionally, we can return some artist information along with each album by using Korma's with function:

(select albums
  (with artists)
  (where { "Brant"}))

The preceding script will return all the columns for the artist named Brant, and all of Brant's albums. This is equivalent to the following SQL:

SELECT albums.*, artists.*
FROM albums
LEFT JOIN artists ON artists.artist_id=albums.artist_id
WHERE = 'Brant'

However, this can pose a problem because both the artists and albums tables have similarly named fields, such as created_at, updated_at, and name. The fields function not only allows us to specify which fields we want returned from the database, but also any aliases we want to give those fields:

(select albums
        (fields :album_id [:name :album_name]
                [:created_at :album_created_at]
                [:updated_at :album_updated_at])
        (with artists
              (fields [:name :artist_name]
                      [:created_at :artist_created_at]
                      [:updated_at :artist_updated_at]))
        (where { "Brant"}))

This will alias the column to albums.album_name, albums.created_at to albums.album_created_at, and so on.

Constructing INSERT queries

Inserting records using Korma is relatively trivial. Just call Korma's insert function and pass it the entity and a map of values:

(insert artists (values {:name "Maude Squad"}))

Be careful however, as Korma will generate an insert statement to include every key in the map. For example, the following insert query fails because the :fake_column key doesn't map to any column on the artists table:

(insert artists (values {:name "Maude Squad"
                         :fake_column "Will destroy you."}))

Constructing UPDATE queries

You can update a record using Korma's update and set-fields functions:

(update artists
  (set-fields {:name "Carlos Hungus"})
  (where {:name "Hungus"}))

The preceding script will update all the artist names to Carlos Hungus where the artist name is currently Hungus. Much like insert however, Korma will blindly try to update any column name that you give it.

Constructing DELETE queries

You can delete records using Korma's delete function. For example, we could blow away all of our artists by executing the following:

(delete artists)

The preceding script is something you're unlikely to want to use. Instead, to delete records for a particular artist, we could do something like the following:

(delete artists
  (where {:name "Carlos Hungus"}))

We can use the where function, as shown in the preceding code, to restrict which records get deleted.

Using raw SQL

If you find yourself in a position where Korma doesn't support what you want to do, or where using Korma produces more complexity than the query itself (a more likely situation), you can use Korma's exec-raw function to execute an SQL string. For example:

(exec-raw ["SELECT, count(*)
           FROM artists art
           INNER JOIN albums alb on art.artist_id = alb.artist_id
           GROUP BY
           HAVING count(*) > ?" [1]] :results)

Using transactions

You can wrap any number of Korma actions inside a korma.db/transaction form to perform a transaction. If anything fails inside the transaction block, then the actions will automatically be rolled back. Otherwise, if everything executes successfully, the transaction will be committed:

  (delete artists (where {:name "Carlos Hungus"}))
  (update artists (set-fields {:name "Marjory Marjoram"})
    (where {:artist_id 100})))

The preceding transaction will execute successfully. Thus, any changes performed inside the transaction will be committed. However, consider the following code:

  (delete artists (where {:name "Carlos Hungus"}))
  (update artists (set-fields {:name "Marjory Marjoram"})
    (where {:fake_id 100})))

Here, the update action will fail because :fake_id is not a valid column on the artists table. As such, the transaction will roll back.


For a complete list of Korma examples, take a look at the detailed examples at