Let’s discuss SQL in Clojure. Specifically we’re going to take a look at two libraries and use their power to connect to databases, make SQL queries in an ‘Clojure-like’ manner, and be able to create, delete, and modify databases and tables. The two libraries we’re looking at today are Lobos and Korma. Lobos primarily handles create, alter, and drop commands, whereas Korma focuses on queries to the database (although does handle inserting new data, updating old data, and deleting data). With that let’s delve right into the world of SQL in Clojure!
Important note: I’m going to be walking you through some basic usage here. This can all primarily be referred to on the documentation for both libraries, or alternatively in one location here. For more in depth documentation, do check out the project pages and their source code.
Introduction
As mentioned in the first paragraph, we’ll be using Lobos for our schema and migration handling and Korma for our querying and data manipulation. There is some slight overlap between the two libraries (albeit not too much), so keep that in mind. For our examples I’ll focus on support for PostgreSQL, but both these libraries have support for a variety of database management systems.
Lobos
Setting up the database connection
; Step 1: dependencies - in your project.clj file
[lobos "1.0.0-beta1"]
[postgresql "9.1-901.jdbc4"]
; Step 2: include Lobos into your namespace
(use '(lobos connectivity core schema))
; Step 3: create and store your connection details in a variable
(def db
{:classname "org.postgresql.Driver"
:subprotocol "postgresql"
:user "admin"
:password "1234"
:subname "//localhost:5432/test"})
; Step 4: connect to db
(open-global db)
Creating a table
(create
(table :customers ; Create a table named customers
(integer :id :primary-key) ; With an integer primary key
(varchar :firstname 75))) ; and a varchar first name
Altering a table schema
(alter
(table :customers
(varchar :firstname 50)))
Dropping a table
(drop (table :customers))
Migrations and Lobos project setup
Lobos has an intelligent way of integrating with your projects through a src/lobos foloder with the files: src/lobos/config.clj, src/lobos/helpers.clj, and src/lobos/migrations.clj. For the purposes of this tutorial we shall touch on config and migrations – for more information on how to use the helpers file check out the github documentation here. So why would you want to create all these extra files? Well first off, Lobos intelligently manages migrations using the migrations namespace. Linking it to a file allows you to handle migrations easily and effectively. The config file lets you store all the relevant configuration information about your database connection in one location. To set the file up you simply take what we learned previously about setting up a database connection with Lobos and paste it into a single config.clj file. It’s as simple as that. Migration setup is detailed below
; Step 1: Namespace setup
(ns lobos.migrations
; This helps prevent conflicts with named Clojure commands
(:refer-clojure :exclude [alter drop
bigint boolean char double float time])
; The primary elements of the Lobos library you will use for migrations
(:use (lobos [migration :only [defmigration]] core schema
; The two files that we just discussed previously are referenced here
config helpers)))
; Step 2: Migration definition
; Lobos uses the defmigration macro to define migrations
; Each defmigration command needs a segment that defines what it is that is being pushed up
; and how to revert it. I.E. How do I change add this migration to the database and then
; how do I remove it? Below is an example from the github documentation. Notice how
; it utilizes the commands we learned earlier with respect to creating and dropping tables
(defmigration add-users-table
(up [] (create
(table :users
(varchar :name 100 :unique)
(check :name (> (length :name) 1)))))
(down [] (drop (table :users))))
; Step 3: Migrate and Rollback
; This is down by solely using the migrate command in a REPL
; Similarly you can rollback through the REPL. Both are shown below
; Importantly though, you must load in the Lobos symbols into the REPL first
; As shown with the use line below
(use 'lobos.core 'lobos.connectivity 'lobos.migration 'lobos.migrations)
(migrate)
(rollback)
Korma
Setting up the database connection
; Step 1: Dependencies - in your project.clj file
; Korma requires itself and JDBC. Remember to include your database library (in this case postgresql)
[korma "0.3.0-RC6"]
[org.clojure/java.jdbc "0.2.3"]
[postgresql "9.1-901.jdbc4"]
; Step 2: Include Korma's database utils in your namespace
(use 'korma.db)
; Step 3: Utilizing the defdb macro, create a database connection
(defdb db (postgres {:db "test"
:user "admin"
:password "1234"}))
Entities
Korma’s entities are the building blocks for any query. There is one entity for each table and essentially defines the table structure. In the example below we define an entity using the defentity macro. The entity is a state, linked to the table state_st. The entity has a has-many relationship with the address entity (not listed below).
A few things to note. :pk can define the primary key of a table, however, the default is ‘id’ so you usually won’t find yourself declaring the :pk. :table, too, is optional, if the table name matches the entity name (e.g. if the table for the state entity was state). There are a number of other parameters you can declare (many of which are optional) which you can check out here.
; Step 1: Include korma.core (use 'korma.core) ; Step 2 create an entity. The following example is from the online documentation (defentity state (table :state_st) ;; sets the table to "state_st" (has-many address))
Queries
Select queries are incredibly straightforward. Again, I direct you to the online documentation for more details, below is a basic example.
(select users
(fields :first)
(with email
(fields :email)
(where {:email [like "%_test%"]})))
Data modification
Below are basic examples for modification of data. For more detailed code check out the documentation.
; Update queries
(update users ; Update all users rows
(set-fields {:status "active" ; setting the status field as "active"
:beta false}) ; at the beta field as false
(where {:visits [> 10]})) ; for all entries where visits are > 10
; Insert queries
(insert users ; Insert a single value into users
(values {:first "john" :last "doe"})) ; This can obviously be extended to have multiple values
; Delete queries
(delete users ; Deletes all users that have
(where {:visits [< 3]})) ; more than 3 visits.
Conclusion
I hope that with this post you got a brief glance at how to use SQL with Clojure and a glimpse at the two powerful libraries Lobos and Korma.
Delta Data 