This post is part of the Web Application Development with Clojure tutorial. You may want to read the previous posts before continuing with this post.

• Part 1: Project Setup
• Part 2: Data model definition with Lobos and Korma
• Part 3: Loading Fixures with clj-yaml and HTML Templating with Enlive
• Part 4: Adding CSS, Post detail page and Simple Authentication
• Part 5: Blog Administration Area

Updated: 31st January, 2012 – Renamed all ‘users’ to ‘authors’ to prevent problems with PostgreSQL

Introduction

In part 1 of this series, we learnt how to setup a Clojure web application project – Clog a blogging engine. In this second part of the tutorial we will start working on the application’s database layer – setting up the schema, creating migrations and finally creating the entities for the model. This terminology should be familiar for people who already worked with other web application frameworks such as Ruby on Rails or Play! framework. If you have never used these frameworks, do not worry no knowledge of those is assumed in this tutorial.

Source code on github

The code for this series is now available on github and the source code is tagged with part names. If you want to checkout the code for a specific part of this tutorial you can do so using the following command:

git clone git://github.com/vijaykiran/clog.git
cd clog
git checkout part1

Database Schema Setup

Normally when you want to work with a database, you will create a schema using DDL (Data Definition Language). DDL contains SQL statements like “CREATE TABLE AUTHOR …” etc. This will define the schema objects you need to store your application’s data. In this post however, I’d like to show you how you can create and manage the schema using a Clojure library called Lobos.

Lobos library provides a DSL (Domain Specific Language) that helps you in defining the database schema and migrations in a Database-agnostic way. By that I mean you can define your schema in Clojure and you can use Lobos to create the schema on any database e.g. PostgreSQL, MySQL or HSQL.

Before we begin, we need to create a database. As you might have seen in part 1‘s dependency listing, for this tutorial we will use PostgreSQL as the database.

Database Setup

First install PostgreSQL for your platform by downloading it from here. Create a user with login clog and your choice of password. Create a database named clogdb and make sure that the user has permissions on the database. To perform these steps you can either use the pgAdmin GUI tool to manage the database or you can use psql command line tool. If you prefer the latter, you can use the following commands to create the database and user:

CREATE USER clog WITH PASSWORD 'Your_Choice_Of_Password';
 
CREATE DATABASE clogdb;
 
GRANT ALL PRIVILEGES ON DATABASE clogdb TO clog;

Schema Creation using Lobos

Before we start writing code in Clojure with Lobos, let us take a quick look at the terminology.

Migrations

Migrations are incremental steps that you have taken to evolve your database schema. Let me explain what a migration means with a concrete example. When you are creating the tables for our blog engine, you will create a table called Posts to store our blog posts. The Posts table might contain the following columns: id, publishedDate, status, content, title. To create a table with these fields you’ll use the following SQL DDL:

CREATE TABLE posts ( id INTEGER UNIQUE,
                     title text NOT NULL,
                     content text,
                     published_date DATETIME NOT NULL,
                     STATUS text NOT NULL,
                     CONSTRAINT posts_id_key PRIMARY KEY (id));

Now at a later point of time you want to add rating to all the blog posts, that means you need to alter the schema to add a new column to the Posts table. This can be done using following Alter table statement.

ALTER TABLE posts
      ADD rating INTEGER;

In the above scenario, your database schema on the whole, has two versions or revisions. Version one that resulted when you executed the CREATE table statement and Version one when you executed the Alter table version. You can treat the two statements you used to modify the database as two migrations. Initially your database schema is at Version 0, once the first migration is executed, you have updated your database to version 1. After second statement, the ALTER TABLE one, is executed your database will be at version 2.

To “rollback” the schema you need a statement which will drop the added rating column, and another to drop the table completely which will result in database resulting in Version 0. When all these SQL statements are combined we get what we call “migrations”. Migrations help you “migrate” and “rollback” the database. Think of the migrations as a version control mechanism for your database.

Now that we know what migrations mean, let us see how to create the schema and migrations using Lobos.

Schema Definition using Lobos migrations

We will define all the schema related operations and migrations in a Clojure namespace called migrations. First create a new directory called lobos under src. If you are using IntelliJ IDEA, then right click the src directory and select New > Package and create a package named lobos. Inside the src/lobos create a file called migrations.clj. Add the following code to the file:

(ns lobos.migrations
  ;; exclude some clojure built-in symbols so we can use the lobos' symbols
  (:refer-clojure :exclude [alter drop
                            bigint boolean char double float time])
  ;; use only defmigration macro from lobos
  (:use (lobos [migration :only [defmigration]]
          core
          schema)))
 
;;; Defines the database for lobos migrations
(def clogdb
  {:classname "org.postgresql.Driver"
   :subprotocol "postgresql"
   :subname "clogdb"
   :user "clog"
   :password "clog"})

The code snippet above defines a namespace migration with one var clogdb which is a map of database connection information. You may need to change the database password with the one you used while creating the database user.

We create our first migration that will add the users table:

(defmigration add-authors-table
  ;; code be executed when migrating the schema "up" using "migrate"
  (up [] (create clogdb
           (table :authors (integer :id :primary-key )
             (varchar :username 100 :unique )
             (varchar :password 100 :not-null )
             (varchar :email 255))))
  ;; Code to be executed when migrating schema "down" using "rollback"
  (down [] (drop (table :authors ))))

Next we define the migration that will create the posts table as shown below:

(defmigration add-posts-table
  (up [] (create clogdb
           (table :posts (integer :id :primary-key )
             (varchar :title 250)
             (text :content )
             (boolean :status (default false))
             (timestamp :created (default (now)))
             (timestamp :published )
             (integer :author [:refer :authors :id] :not-null))))
  (down [] (drop (table :posts ))))

The above code is pretty much similar to the authors table migration – a title column, text column and a couple of timestamp columns to store the created and published date and times. Also, every post belongs to an author, so we create a foreign key to the authors table using :refer. Here we are referring to the :authors table’s :id column.

To run the above code, start a REPL console if it isn’t already running. Type the following commands into the console:

(use 'lobos.core 'lobos.connectivity 'lobos.migration 'lobos.migrations)
(open-global clogdb)
(migrate)

You should see the following output in the console:

WARNING: alter already refers to: #'clojure.core/alter in namespace: user, being replaced by: #'lobos.core/alter
WARNING: drop already refers to: #'clojure.core/drop in namespace: user, being replaced by: #'lobos.core/drop
WARNING: complement already refers to: #'clojure.core/complement in namespace: user, being replaced by: #'lobos.migration/complement
nil
{:default-connection {:connection #, :db-spec {:subprotocol "postgresql", :classname "org.postgresql.Driver", :subname "clogdb", :user "clog", :password "clog"}}}
add-authors-table
add-posts-table
nil

To cross check what relations are created in the database, you can use psql and try the following commands:

clogdb=# d
d
             List OF relations
 Schema |       Name       | TYPE  | Owner
--------+------------------+-------+-------
 public | lobos_migrations | TABLE | clog
 public | posts            | TABLE | clog
 public | authors            | TABLE | clog
(3 ROWS)
 
clogdb=# d authors
d authors
             TABLE "public.authors"
  COLUMN  |          TYPE          | Modifiers
----------+------------------------+-----------
 id       | INTEGER                | NOT NULL
 username | CHARACTER VARYING(100) |
 password | CHARACTER VARYING(100) | NOT NULL
 email    | CHARACTER VARYING(255) |
Indexes:
    "authors_primary_key_id" PRIMARY KEY, btree (id)
    "authors_unique_username" UNIQUE, btree (username)
Referenced BY:
    TABLE "posts" CONSTRAINT "posts_fkey_user" FOREIGN KEY ("author") REFERENCES authors(id)
 
clogdb=# d posts
d posts
                  TABLE "public.posts"
  COLUMN   |            TYPE             |   Modifiers
-----------+-----------------------------+---------------
 id        | INTEGER                     | NOT NULL
 title     | CHARACTER VARYING(250)      |
 content   | text                        |
 STATUS    | BOOLEAN                     | DEFAULT FALSE
 created   | TIMESTAMP WITHOUT TIME zone | DEFAULT now()
 published | TIMESTAMP WITHOUT TIME zone |
 author    | INTEGER                     | NOT NULL
Indexes:
    "posts_primary_key_id" PRIMARY KEY, btree (id)
Foreign-KEY constraints:
    "posts_fkey_author" FOREIGN KEY ("author") REFERENCES authors(id)

Lobos keeps track of which migrations are run using the lobos_migrations table. As you can see below our current lobos_migrations contains two migrations we have written and executed.

clogdb=# SELECT * FROM lobos_migrations;
SELECT * FROM lobos_migrations;
      name
-----------------
 add-authors-TABLE
 add-posts-TABLE
(2 ROWS)

Entity Definition

After creating the database, now we’ll define our entities using the SQL library Korma. What is Korma ?

Korma is a domain specific language for Clojure that takes the pain out of working with your favorite RDBMS. Built for speed and designed for flexibility, Korma provides a simple and intuitive interface to your data that won’t leave a bad taste in your mouth.

Korma makes it easier to deal with the database. Instead of using SQL queries, you can use Clojure to access the database. To get the data from the tables, first we need to define entities using the Korma’s defnentity macro. First create a file called models.clj in your src/clog folder. And add the following code to the file:

(ns clog.models
  (:use korma.db
        korma.core))
 
(defdb clogdb
  {:classname "org.postgresql.Driver"
   :subprotocol "postgresql"
   :subname "clogdb"
   :user "clog"
   :password "clog"})
 
(defentity authors)
(defentity posts)

The above code defines two entities authors and posts. Let me show you how we can insert an author record into the database and select the list of authors from the database. Start Clojure REPL, if it isn’t running already. Type the following code into REPL:

(use 'korma.db 'korma.core 'clog.models)
(insert  authors (values {:id 1, :username "vijay", :password "password", :email "mail AT vijaykiran.com"}) )

This will insert a new record into the database. You can use the select function to get the list of authors from the authors table.

(select authors)
[{:id 1, :username "vijay", :password "password", :email "mail AT vijaykiran.com"}]

Source Code

If you are stuck at any point and want to directly checkout the code, you can use the following commands to get the code that is tagged with part2

git clone git://github.com/vijaykiran/clog.git
cd clog
git checkout part2

Conclusion

This concludes the part 2 of this series. In this part of the tutorial we saw how to create a simple schema using Lobos library, also we defined the entities for the tables we created using the Korma library. In the next part I’ll show you how to populate sample data into the database and we’ll create our first template to display the data in the browser.

Make sure you subscribe to the RSS feed or follow me on Twitter to get notified.

If you have any feedback on this article and its content, you can contact me via Twitter.