Building a Clojure web app backed by Postgres

In this article, we’ll create a Clojure web app talking to a Postgres database. You should know, that I’m by no means an expert on the subject - this is just front-end developer exploring new areas. Big thanks to my friend Chris Wolfe who helped me understand a lot of concepts presented in this article (and was extremely patient at that).

The app

We’ll build a simple app for storing cooking recipes called NerdRecipes. I’ll continue working on the same concept app in future articles. You can find the final code for this part here: https://github.com/smogg/nerd-fitness. The app will be based on the simple Compojure template. The app will be talking to a Postgres database and it will have a few endpoints to save/fetch the recipes. It will be fully tested. In the future we will build authentication/authorization for our endpoints as well as the UI. The latter part most likely will be done in Reagent (with re-frame).

The beginning

Let’s create a new project by running lein new compojure nerd-recipes. You should get a file structure similar to this:

The database

As mentioned earlier, we’ll use Postgres for our project. I won’t go into too much detail about setting up a database since a simple web search will reveal everything you need to know. I’m using Postgres app for Mac.

In Clojure, we’ll use jdbc.pool.c3p0 to set up a connection pool we’ll use for our app:

Update project.clj with above dependencies:

:dependencies [... 
               [org.clojure/java.jdbc "0.7.3"] 
               [org.postgresql/postgresql "42.1.4"] 
               [clojure.jdbc/clojure.jdbc-c3p0 "0.3.3"]]

Let’s setup a database connection, create

; src/nerd-recipes/db.clj
(ns nerd-recipes.db 
 (:require [jdbc.pool.c3p0 :as pool])) 
 
(def spec 
  (pool/make-datasource-spec 
    {:subprotocol "postgresql" 
     :subname "//localhost:5432/nerd_recipes" 
     :user "admin" 
     :password ""}))

As you can see, we’ve set up our connection to look for a database named nerd_recipes.

First, connect to the database from the command line using default Postgres user (or just double click postgres in Postgresapp I mentioned before):

psql -U postgres

Next, run some SQL statements to create a new database and a new user for our app:

CREATE USER admin;
CREATE DATABASE nerd_recipes;
GRANT ALL PRIVILEGES ON DATABASE nerd_recipes TO admin;

Let’s save above commands under dev/init_dev_db.sql for future use. You (or anyeone else) will be able to run the file using psql -U postgres -f dev/inti_dev_db.sql from the command line.

Let’s try our database connection in Clojure REPL:

lein repl 
 
user=> (require '[nerd-recipes.db :as db]) 
nil 
user=> (require '[clojure.java.jdbc :as j]) 
nil 
user=> (j/db-do-commands db/spec "create table test (name varchar)") 
(0) 
user=> (j/insert! db/spec :test {:name "Testing database connection"}) 
({:name "Testing database connection"}) 
user=> (j/query db/spec "select * from test") 
({:name "Testing database connection"}) 
user=> (j/db-do-commands db/spec "drop table test") 
(0)

If everything went well you should see similar responses. We required java.jdbc and the connection pool we’ve set up previously in nerd-recipes.db. Then we used java.jdbc to create the test table, add an entry in the same table and then we verified that everything actually got saved to the database. Last but not least, we removed the same table and left the database as we’ve found it.

The SQL (hugging)

Running jdbc/db-do-commands with SQL strings in Clojure all the time is not pretty nor fun. Hugsql to the rescue - it will let you write plain SQL (with some additional helpers) and then automagically import those statements into your Clojure code (for example by defining functions in current namespace). It’s a great library with even better documentation that separates concerns you want separated.

Add [com.layerware/hugsql "0.4.8"] in your project.clj dependencies and create a new file under src/nerd_recipes/db/sql/recipes.sql. Next, add a few simple SQL statements for creating the recipes table, where we will store our app’s recipes. We are also creating a few handy statements we’ll use to create new or select recipes.

-- :name create-recipes-table :! 
CREATE TABLE IF NOT EXISTS recipes (
  id bigserial PRIMARY KEY,
  name varchar NOT NULL,
  description text NOT NULL,
  created_at timestamp NOT NULL default current_timestamp
)
 
-- :name drop-recipes-table :! 
DROP TABLE IF EXISTS recipes
 
-- :name insert-recipe :! :n 
INSERT INTO recipes (name, description)
VALUES (:name, :description)
 
-- :name recipe-by-name-like :? :1 
SELECT * FROM recipes WHERE name LIKE :name-like
 
-- :name recipes-with-cols :? :* 
SELECT :i*:cols FROM recipes

Hugsql is gonna use the part after -- :name ... to define Clojure functions under the corresponding name. The :? or :! part tells hugsql what kind of result is expected. For recipes-with-cols we used :* on the :name line, and :i:cols later, in the sql statement. This is literally expanding a Clojure collection (items will be separated by commas) inside your SQL. Neat! Refer to Hugsql docs to learn more about what just happened.

Let’s now make those functions available in Clojure:

Create a new file under src/nerd_recipes/db/recipes.clj

; src/nerd_recipes/db/recipes.clj
(ns nerd-recipes.db.recipes 
  (:require [hugsql.core :as hugsql])) 
 
(hugsql/def-db-fns "nerd_recipes/db/sql/recipes.sql")

The def-db-fns macro is going to create functions in current namespace from SQL statements we defined before. The following functions should now be available:

nerd-recipes.db.recipes/create-recipes-table
nerd-recipes.db.recipes/drop-recipes-table
nerd-recipes.db.recipes/insert-recipe
nerd-recipes.db.recipes/recipe-by-name-like
nerd-recipes.db.recipes/recipes-with-cols

Again, let’s make sure that everything is working as expected in the REPL:

lein repl 
 
user=> (require '[nerd-recipes.db :as db] '[nerd-recipes.db.recipes :as db-recipes]) 
nil 
user=> (db-recipes/create-recipes-table db/spec) 
[0] 
user=> (db-recipes/insert-recipe db/spec {:name "Pierogi" :description "Polish pierogies are the best"}) 
1 
user=> (db-recipes/recipe-by-name-like db/spec {:name-like "Pierogi"}) 
({:id 1, :name "Pierogi", :description "Polish pierogies are the best", :created_at #inst "2017-11-17T12:39:58.075779000-00:00"}) 
user=> (db-recipes/drop-recipes-table db/spec) 
[0]

Notice we no longer need java.jdbc to run the commands. You can remove it from your project’s dependencies as we’ll no longer be using it in this project.

Let’s also create another namespace to separate database functions from the rest of our application’s code:

; src/nerd-recipes/recipes.clj
(ns nerd-recipes.recipes 
 (:require [nerd-recipes.db.recipes :as db-recipes] 
           [nerd-recipes.db :as db])) 
 
(defn add-recipe 
 [name description] 
 (db-recipes/insert-recipe db/spec {:name name :description description})) 
 
(defn get-recipe 
 [name] 
 (db-recipes/recipe-by-name-like db/spec {:name-like name})) 
 
(defn get-recipes 
 ([] 
  (get-recipes ["name" "description"])) 
 ([cols] 
  (db-recipes/recipes-with-cols db/spec {:cols (map name cols)})))

This way you won’t have to think about database logic at all when creating/getting recipes elsewhere in your app.

The migrations

Next, let’s set up database migrations. We’ll use Drift - a simple, file-based migration library for Clojure similar to Rails. Add [drift "1.5.2"] in your :plugins and :dependencies sections inside project.clj.

By default, Drift will call the config.migrate-config/migrate-config function to get the settings map. Create that file and put some basic setup there:

; src/config/migrate_config.clj
(ns config.migrate-config) 
 
(defn migrate-config 
 [] 
 {:directory "/src/migrations" 
  :current-version current-db-version 
  :update-version update-db-version })

Drift will look for migrations to run under the specified :directory and will rely on two functions - current-db-version and update-db-version to get current database version and update it respectively. We’ll store that information inside schema_migrations table, so let’s start by creating the corresponding .sql and .clj files (like we did in the previous step for recipes table). Let’s put the file under src/config/db/sql/schema_migrations.sql:

-- :name create-schema-migrations-table :! 
create table if not exists schema_migrations (
  version integer not null,
  created_at timestamp not null default current_timestamp
)
 
-- :name current-db-version :? :1 
select * from schema_migrations
order by created_at
 
-- :name insert-db-version :! :n 
insert into schema_migrations (version)
  values (:version)

src/config/db/schema_migrations.clj

(ns config.db.schema-migrations 
  (:require [hugsql.core :as hugsql])) 
 
(hugsql/def-db-fns "config/db/sql/schema_migrations.sql")

We can now use the new namespace and write missing functions for config.migrate-config:

; src/config/migrate_config.clj
(ns config.migrate-config 
 (:require [config.db.schema-migrations :as db-schema-migrations] 
           [nerd-recipes.db :as db] 
           [drift.builder :refer [incremental-migration-number-generator]])) 
 
(defn current-db-version 
  [] 
  (or (:version (db-schema-migrations/current-db-version db/spec)) 
      0)) 
 
(defn update-db-version 
 [version] 
 (db-schema-migrations/insert-db-version db/spec {:version version})) 
 
(defn migrate-config 
 [] 
 {:directory "/src/migrations" 
  :current-version current-db-version 
  :update-version update-db-version 
  :init (fn [_] (db-schema-migrations/create-schema-migrations-table db/spec)) 
  :migration-number-generator incremental-migration-number-generator})

You’ll notice we also added two new keys to Drift settings - :init and :migration-number-generator. First one makes sure that schema_migrations table is present before we start migrating our database. The second one tells Drift which function to use when coming up with a new database version number. We used Drift’s built-in incremental-migration-number-generator which basically bumps the current version number by one.

You should now be able to run lein migrate to run the migrations. At the end of the process you should see a message similar to the following:

INFO: No changes were made to the database.

We don’t have any actual migrations written yet, so that’s expected. Let’s write one:

; src/migrations/001_create_tables.clj 
(ns migrations.001-create-tables 
  (:require [nerd-recipes.db :as db] 
            [nerd-recipes.db.recipes :as db-recipes])) 
 
(defn up 
  [] 
  (db-recipes/create-recipes-table db/spec)) 
 
(defn down 
  [] 
  (db-recipes/drop-recipes-table db/spec))

Drift expects two functions to be present in a migration file - up and down. Running lein migrate now should create the tables. The process should end with a message similar to the following:

INFO: Migrated to version: 1

You can double check that database migration was properly recorded in schema_migrations table from the command line:

$ psql nerd_recipes -U admin -c "select * from schema_migrations"
 version |         created_at
---------+----------------------------
       1 | 2017-11-17 13:38:21.055254
(1 row)

The tests

Last but not least, let’s implement a few tests for our app. Let’s start by creating a new script that’s gonna create a database/user for testing (similar to what we did with development database):

dev/init_test_db.sql

CREATE USER test_user;
CREATE DATABASE nerd_recipes_test;
GRANT ALL PRIVILEGES ON DATABASE nerd_recipes_test TO test_user;

and let’s run the script from the command line:

$ psql -U postgres -f dev/init_test_db.sql

Next, create a helper for our tests. It’s gonna run the init function we passed to Drift settings, run all the migrations for us as well as redef the connection to use the test database instead of the development one:

; test/nerd_recipes/test_utils.clj
(ns nerd-recipes.test-utils 
 (:require [nerd-recipes.db :as db] 
           [migrations.001-create-tables :as create-tables])) 
 
(def test-db-spec 
   {:subprotocol "postgresql" 
    :subname "//localhost:5432/nerd_recipes_test" 
    :user "test_user" 
    :password ""}) 
 
(defn test-db-fixture 
  [tests] 
  (with-redefs [nerd-recipes.db/spec test-db-spec] 
    (drift/run-init nil) 
    (runner/migrate-up-all) 
    (try 
      (tests) 
      (finally 
        (runner/migrate-down-all)))))

Now we can test the recipes namespace and verify our functions are working:

(ns nerd-recipes.recipes-test 
 (:require [clojure.test :refer :all] 
           [nerd-recipes.test-utils :as tu] 
           [nerd-recipes.recipes :as recipes])) 
 
(use-fixtures :once tu/test-db-fixture) 
 
(deftest recipes-test 
  (testing "Adding/getting recipes" 
    (recipes/add-recipe "Pierogi" "Polish pierogies are the best") 
    (recipes/add-recipe "Kiełbasa" "Nothing like polish sausage") 
    (let [{:keys [id name description]} (recipes/get-recipe "Pierogi")] 
      (is (= id 1)) 
      (is (= name "Pierogi")) 
      (is (= description "Polish pierogies are the best")))) 
  (testing "Getting all recipes" 
    (is (= (recipes/get-recipes) 
           [{:name "Pierogi" :description "Polish pierogies are the best"} 
            {:name "Kiełbasa" :description "Nothing like polish sausage"}])) 
    (is (= (recipes/get-recipes [:name]) 
           [{:name "Pierogi"} 
            {:name "Kiełbasa"}]))))

Run lein test from the command line and make sure everything passes.

The summary

I hope it was fairly straight forward - please let me know if something was incorrect/unclear by emailing me at [email protected] To clean things up before the next article I’m gonna add the following plugins:

• environ - so we don’t have to hard-code database settings
• cljfmt - to clean up project’s indentation
• auto - to automatically re-run the tests

Next time we’ll actually create a few Compojure routes and add an authentication system.