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.

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.

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))

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.

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.

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."}))

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.

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.

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)

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.