Appendix A. Using Korma – a Clojure DSL for SQL

In this book, we exclusively made use of YeSQL, a library that generates Clojure functions from native SQL. For an example application as small and simple as hipstr, YeSQL might be a tad overkill – its real beauty and elegance comes to light in larger projects that make use of large queries or lots of underlying database functionality. Furthermore, YeSQL does a decent job of abstracting away the underlying data model from the Clojure code.

That being said, there are many people who prefer using a domain specific language to interact with the database. This appendix will introduce you to Korma, a pure-Clojure DSL for SQL. This appendix will not cover Korma in its entirety (it's pretty full-fledged). Instead, this appendix will cover the following:

• How to tie object models back to database tables

• A light overview of selecting and inserting data using Korma

• Port the connection, album-model, and user-model namespaces from YeSQL to Korma

Getting Korma

Add the following code to the Leiningen :dependencies in the hipstr project file:

[korma "0.4.0"]

At the time of writing this book, Version 0.4.0 is the most recent stable build of Korma, and it was released around late August of 2014. You may want to check if there's a newer version at https://github.com/korma/Korma/.

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 http://www.sqlkorma.com/docs.

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 {:artists.name "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 {:artists.name "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 artists.name = '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 {:artists.name "Brant"}))

This will alias the albums.name 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 art.name, count(*)
           FROM artists art
           INNER JOIN albums alb on art.artist_id = alb.artist_id
           GROUP BY art.name
           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:

(transaction
  (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:

(transaction
  (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.

Tip

For a complete list of Korma examples, take a look at the detailed examples at http://sqlkorma.com/docs.

Port the models from YeSQL to Korma

The following pages are what our hipstr.models.connection, hipstr.models.artist-model, and hipstr.models.album-model will be when ported from YeSQL to Korma. Note that the interfaces for each ported function are kept the same as YeSQL's generated functions, meaning that the ported functions will accept maps instead of explicit literals. If we were to design the model layer with Korma first and foremost in mind, our interfaces would have been simpler. The goal of this port is to illustrate how we can write the YeSQL-generated functions using Korma, and still have it work without having to modify the rest of the application.

Porting hisptr.models.connection

Porting the connection is done by simply adding a call to the korma.db/defdb macro:

(ns hipstr.models.connection
  (:require [environ.core :refer [env]])
  (:use korma.db))
(def db-spec {:classname   (env :db-classname)
              :subprotocol (env :db-subprotocol)
              :subname     (env :db-subname)
              :user        (env :db-user)
              :password    (env :db-password)})

; Declares the hipstr-db Korma database connection,
; which leverages our already existing db-spec
(defdb hipstr-db db-spec)

Porting hisptr.models.user-model

To port hipstr.models.user-model to use Korma instead of YeSQL, we will re-write the YeSQL-generated functions. We'll keep the function signatures the same as the YeSQL-generated functions.

First, include a reference to korma.core and comment out the reference to yesql.core (but we'll leave it in, in case you want to more easily switch back and forth):

(ns hipstr.models.user-model
  (:require ;[yesql.core :refer [defqueries]]
            [crypto.password.bcrypt :as password]
            [hipstr.models.connection :refer [db-spec]]
            [noir.session :as session])
  (:use [korma.core]))

Since we're not using YeSQL, we can also comment out the call to defqueries:

;(defqueries "hipstr/models/users.sql" {:connection db-spec})

Finally, we declare our users table as a Korma entity, and then port the two YeSQL-generated functions, get-user-by-name and insert-user<!:

; declare our users table, which in our hipstr application
; is pretty straight forward.
; For Korma, however, we have to define the primary key because
; the name of the primary key is neither 'id' or 'users_id'
; ([tablename]_id)
(defentity users
  (pk :user_id))

; -- name: get-user-by-username
; -- Fetches a user from the DB based on username.
; SELECT *
; FROM users
; WHERE username=:username
 (defn get-user-by-username
  "Fetches a user from the DB based on username."
  [username]
  (select users (where username)))

; -- name: insert-user<!
; -- Inserts a new user into the Users table
; -- Expects :username, :email, and :password
; INSERT INTO users (username, email, pass)
; VALUES (:username, :email, :password)
(defn insert-user<!
  "Inserts a new user into the Users table. Expects :username, :email, and :password"
  [user]
  (insert users (values user)))

By keeping the interfaces of the ported functions the same as the YeSQL-generated ones, we don't have to adjust any of the calling code in the application. At this point, you can restart your dev server and use the signup form to create a new user.

Porting hipstr.models.album-model

Porting the albums-model is a little bit more involved, but the principles are the same. We want to remove the dependencies on YeSQL, and then write new functions using Korma that match the interfaces of the generated YeSQL equivalents:

(ns hipstr.models.album-model
  (:require ;[yesql.core :refer [defqueries]]
            [clojure.java.jdbc :as jdbc]
            [taoensso.timbre :as timbre]
            [hipstr.models.connection :refer [hipstr-db]])
  (:use [korma.core]
        [korma.db]))

;(defqueries "hipstr/models/albums.sql" {:connection db-spec})
;(defqueries "hipstr/models/artists.sql" {:connection db-spec})

(declare artists albums)

; define our artists entity.
; by default korma assumes the entity and table name map
(defentity artists
  ; We must define the primary key because it does not
  ; adhere to the korma defaults.
  (pk :artist_id)

  ; define the relationship between artists and albums
  (has-many albums))

; define the albums entity
(defentity albums
  ; again, we have to map the primary key to our korma definition.
  (pk :album_id)

  ; We can define the foreign key relationship of the albums back
  ; to the artists table
  (belongs-to artists {:fk :artist_id}))

; -- name: get-recently-added
; -- Gets the 10 most recently added albums in the db.
; SELECT art.name as artist, alb.album_id, alb.name as album_name,
;        alb.release_date, alb.create_date
; FROM artists art
; INNER JOIN albums alb ON art.artist_id = alb.artist_id
; ORDER BY alb.create_date DESC
; LIMIT 10
(defn get-recently-added
  "Gets the 10 most recently added albums in the db."
  []
  (select albums
    (fields :album_id
     [:name :album_name] :release_date :created_at)
    (with artists (fields [:name :artist]))
    (order :created_at :DESC)
    (limit 10)))

; -- name: get-by-artist
; -- Gets the discography for a given artist.
; SELECT alb.album_id, alb.name, alb.release_date
; FROM albums alb
; INNER JOIN artists art on alb.artist_id = art.artist_id
; WHERE
;   art.name = :artist
; ORDER BY alb.release_date DESC
(defn get-by-artist
  "Gets the discography for a given artist."
  ; for backwards compatibility it is expected that the
  ; artist param is a map, {:artist [value]}
  [artist]
  (select albums
    (join artists)
    ; for backwards compatibility we need to rename the :albums.name
; field to :album_name
    (fields :albums.album_id [:albums.name :album_name]
           :albums.release_date)
    (where {:artists.name (:artist artist)})
    (order :release_date :DESC)))

;-- name: insert-album<!
;-- Adds the album for the given artist to the database
;INSERT INTO albums (artist_id, name, release_date)
;VALUES (:artist_id, :album_name, date(:release_date))
(defn insert-album<!
  "Adds the album for the given artist to the database."
  ; for backwards compatibility it is expected that the
  ; album param is a map,
  ; {:artist_id :release_date :album_name :artist_name}
  ; As such we'll have to rename the :album_name key and remove
  ; the :artist_name.This is because korma will attempt to use all
  ; keys in the map when inserting, and :artist_name will destroy
  ; us with rabid vitriol.
  [album]
  (let [album (-> (clojure.set/rename-keys album {:album_name :name})
                  (dissoc :artist_name)
                  (assoc :release_date
                  (sqlfn date (:release_date album))))]
    (insert albums (values album))))

; -- name: get-album-by-name
; -- Fetches the specific album from the database for a particular
; -- artist.
; SELECT a.*
; FROM albums a
; WHERE
;   artist_id = :artist_id and
;   name = :album_name
(defn get-album-by-name
  "Fetches the specific album from the database for a particular
   artist."
  ; for backwards compatibility it is expected that the
  ; album param is {:artist_id :artist_name}
  [album]
  (first
   (select albums
          (where {:artist_id (:artist_id album)
                  :name (:artist_name album)}))))

; -- name: insert-artist<!
; -- Inserts a new artist into the database.
; INSERT INTO artists(name)
; VALUES (:artist_name)
(defn insert-artist<!
  "Inserts a new artist into the database."
  ; for backwards compatibility it is expected that the
  ; artist param is {:artist_name}
  [artist]
  (let [artist (clojure.set/rename-keys
                artist {:artist_name :name})]
    (insert artists (values artist))))

; -- name: get-artist-by-name
; -- Retrieves an artist from the database by name.
; SELECT *
; FROM artists
; WHERE name=:artist_name
(defn get-artist-by-name
  "Retrieves an artist from the database by name."
  ;for backwards compatibility it is expected that the
  ; artist_name param is {:artist_name}
  [artist_name]
  (first
   (select artists
           (where {:name (:artist_name artist_name)}))))

Finally, we have to port the add-album! function, because the way Korma wraps transactions is different than YeSQL's. In YeSQL, we have to get a symbol to a transaction and pass that to all our methods, whereas, in Korma, we merely have to wrap everything in a transaction form:

(defn add-album!
  "Adds a new album to the database."
  [album]
  (transaction
   (let [artist-info {:artist_name (:artist_name album)}
         ; fetch or insert the artist record
         artist (or (get-artist-by-name artist-info)
                    (insert-artist<! artist-info))
         album-info (assoc album :artist_id (:artist_id artist))]
     (or (get-album-by-name album-info)
         (insert-album<! album-info)))))

If you restart your dev server, you'll find that the recently-added albums, as well as the artist and albums pages, behave as they did before.