To create any non-trivial application, you need some way of persisting data. While there are a lot solutions available, Datomic, Neo4j, Samza, just to name a few, but for various reasons, a lot of us still use relational databases. One reason might be because there is a pre-existing Rails application that still needs to interoperate with the Clojure application you are building. Another reason could be because of the exisiting deployment environment requires you to use a certain type of database (I encountered this first hand while working for a large client).
+--------------+ +---------------+
| | | |
| Ruby | | |
| on | | Compojure |
| Rails | | (Clojure app) |
| | | |
| ^ | | ^ |
| | | | | |
+-----+--------+ +---------+-----+
| | | |
| | | |
| | | |
| | +------------+ | |
| | | | | |
| | | | | |
| +------+ Postgres +---+ |
| | (DB) | |
+-----------> <-------+
| |
+------------+
Fortunately there are many libaries available for interacting with relational databases in Clojure (see Clojure-Toobox). After some experimentation I found that the combination of sqlingvo, drift and java.jdbc to be the best set of libraries for managing and manipulating data stored in relational databases.
+--------------------------------------+
| |
| Drift <----------------------------+ Migration manager
| |
| +-----------------------------+ |
| | | |
| | SQLingvo <--------------------------+ SQL query generator
| | | |
| | +-----------------------+ | |
| | | | | |
| | | java.jdbc <-------------------------+ Database adapter
| | | | | |
| | +-----------------------+ | |
| | | |
| +-----------------------------+ |
| |
+--------------------------------------+
Unlike other migration solutions, drift does not concern itself with providing a DSL for generating SQL queries – it simply aids in the generation of migration files and runs pending migrations and also allows users to roll back previously ran migrations.
To generate and execute SQL queries we will use sqlingvo and java.jdbc. java.jdbc is low level wrapper for database interaction while sqlingvo allows for the creation of SQL queries using Clojure datastructures.
To get started we need to create our database user and database. It's worth noting that sqlingvo supports for mysql, oracle, sqlite, sqlserver, and vertica, but in our example we will use postgres.
So we will start by creating a user called drifter and a database called drift_sqlingvo_dev.
Creates user:
$ createuser -s -r drifter
Creates a database names drift_sqlingvo_dev
$ createdb -O drifter drift_sqlingvo_dev
Then we'll to create our Clojure project:
lein new sample-drift-sqlingvo
cd into the project directory and edit and open the project.clj file and add the following dependencies:
[org.postgresql/postgresql "9.3-1102-jdbc41"]
[org.clojure/java.jdbc "0.3.6"]
[drift "1.5.2"]
[sqlingvo "0.7.8"]and also add drift as a plugin, so that we can run lein migrate.
You file should look something like this:
(defproject sample-drift-sqlingvo "0.1.0-SNAPSHOT"
:description "FIXME: write description"
:url "http://example.com/FIXME"
:license {:name "Eclipse Public License"
:url "http://www.eclipse.org/legal/epl-v10.html"}
:dependencies [[org.clojure/clojure "1.6.0"]
[org.postgresql/postgresql "9.3-1102-jdbc41"]
[org.clojure/java.jdbc "0.3.6"]
[drift "1.5.2"]
[sqlingvo "0.7.8"]]
:plugins [[drift "1.5.2"]])Run lein repl on the terminal and Leiningen should download all your project dependencies as well as start a repl session.
Open src/sample-drift-sqlingvo/core.clj and edit the file to look like this:
(ns sample-drift-sqlingvo.core
(:require [clojure.java.jdbc :as j]))
(def db {:classname "org.postgresql.Driver"
:subprotocol "postgresql"
:user "drifter"
:password ""
:subname "//localhost:5432/drift_sqlingvo_dev"})
(j/query db ["SELECT * FROM users"])
Here we imported clojure.java.jdbc namespace, and used it's query function. We pass query a the vector containing the following SQL statement "SELECT * FROM users".
Let's go ahead and execute the last line. If you don't know how, check out Getting started with Clojure in Vim. When you do execute the last line, you should get the following message:
CompilerException org.postgresql.util.PSQLException: ERROR: relation "articles" does not exist Position: 15, compiling:(sample_drift_sqlingvo/core.clj:1:36)
This is to be expected since we have not created a user table for Postgres to query. The important thing is that you've verified that you can successfully connect with your Postgres database.
Before we can generate the migration for creating the user table, we need to configure drift. First we create src/config/migrate_config.clj in your project.
(ns config.migrate-config
(:refer-clojure :exclude [distinct group-by union])
(:require [clojure.java.jdbc :as j]
[sqlingvo.core :refer :all]
[drift.builder :refer [timestamp-migration-number-generator]]
[sqlingvo.core :refer :all]
[sqlingvo.db :refer [postgresql]]))
Let's declare the database configuration:
(def db-config {:classname "org.postgresql.Driver"
:subprotocol "postgresql"
:user "adobo"
:password ""
:subname "//localhost:5432/adobo_development"})