PostgreSQL with Clojure and HoneySQL

In this article, we will be looking at something very interesting that I came across while working with database queries in Clojure. I will be taking you step by step through the process of integrating and interacting with a PostgreSQL database in Clojure and run SQL queries on the same, first, the regular way and then move to a nicer way of getting the same job accomplished.

Ok, that was too much of convoluted talk. Let us see what I meant :-)

Pre-requisites

1. Install PostgreSQL — You can follow the installation instructions for your specific OS, there are many tutorials online, just Google it!
2. Install Leiningen — instructions here — https://leiningen.org/#install
3. Some familiarity of working with Clojure code

Preparing PostgreSQL database

As a first step, let us create a PostgreSQL database for us to work with.

Note: I will be working with a local instance of PostgreSQL

Open a terminal and type in :

psql -U <username>

Most PostgreSQL installations come with a default user postgres I will be working with that user through this article.

So typing in

psql -U postgres

will bring you to the prompt

psql (13.3)
Type "help" for help.
postgres=#

At the prompt, type in :

create database ecomdb;

check that the database is created

\l
;;                       List of databases
            Name            |     Owner     | Encoding
----------------------------+---------------+----------
 ecomdb                     | postgres      | UTF8    

The database is created. Let us connect to the database ecomdb and create the tables needed

\c ecomdb
;; You are now connected to database "ecomdb" as user "postgres"

Type in :

create table if not exists customer (
id SERIAL PRIMARY KEY NOT NULL,
name VARCHAR(100),
email VARCHAR(100),
mobile BIGINT);

Let us check if the table has been created :

\dt
;;           List of relations
 Schema |   Name   | Type  |  Owner
--------+----------+-------+----------
 public | customer | table | postgres
(1 row)

Clojure Project

As always, let us begin by creating a project

lein new app psql-demo

configure the required Clojure libraries as dependencies. Open up the project.clj file in the created project folder and replace its contents with the one below

(defproject postgres-demo "0.1.0-SNAPSHOT"
  :description "FIXME: write description"
  :url "http://example.com/FIXME"
  :license {:name "EPL-2.0 OR GPL-2.0-or-later WITH Classpath-exception-2.0"
            :url "https://www.eclipse.org/legal/epl-2.0/"}
  :dependencies [[org.clojure/clojure "1.10.3"]
                 [com.github.seancorfield/next.jdbc "1.3.834"]
                 [org.postgresql/postgresql "42.2.10"]
                 [com.github.seancorfield/honeysql "2.3.928"]]
  :main ^:skip-aot postgres-demo.core
  :target-path "target/%s"
  :profiles {:uberjar {:aot :all
                       :jvm-opts ["-Dclojure.compiler.direct-linking=true"]}})

Highlighted in bold above are the libraries that we will be working with

1. com.github.seancorfield/next.jdbc — As you might have guessed, this is the library that provides the jdbc interface to talk to variety of databases.
2. org.postgresql/postgresql — This dependency provides the JDBC Driver that enables communication with PostgreSQL
3. com.github.seancorfield/honeysql — This library provides a beautiful way to describe SQL queries as Clojure data structures and enables to build queries programmatically

Now, let us download the dependencies, run :

lein deps

We are all set to fire up the REPL and explore.

REPL

Fire up the repl :

lein repl

Note: the above command is to be run under the project folder i.e postgres-demo

Interacting with the PostgreSQL database

Let us begin with the next.jdbc library and then bring in the honeysql

(require '[next.jdbc :as jdbc])

First, we need to create a datasource For that we need to specify the database connection details in the form of a map like so:

(def db-config
  {:dbtype "postgresql"
   :dbname "ecomdb"
   :host "localhost"
   :user "postgres"
   :password "postgres"})

we specify :

the dbtype — will be postgresql in our case,

the host where the database server is running, localhost in our case

the dbname — the database name to connect to, ecomdb here and

the user/password details.

Using the above specification, we create a datasource , which I name as db

(def db (jdbc/get-datasource db-config))

With the datasource created, we can now start interacting with the PostgreSQL database

Let us do a simple SQL query to fetch all the rows in the table customer

(jdbc/execute! db ["select * from customer"])
;; []

jdbc/execute! — method from the next.jdbc library

db — the datasource that we just created

followed by a vector with the SQL query in the form of a string.

As expected, returns an empty vector, nothing yet in our table.

Let us insert a row in the table

(jdbc/execute! db ["
 insert into customer (name,email,mobile)
  values('john','john@doe.org', 9898989898)"])
;; [#:next.jdbc{:update-count 1}]

The result indicates that a row was added in. Let us verify the same

(jdbc/execute! db ["select * from customer"])
;; [#:customer{:id 1, :name "john", :email "john@doe.org", :mobile 9898989898}]

Great! the row has indeed been inserted.

Let us insert a few more rows

(jdbc/execute! db ["
 insert into customer (name,email,mobile)
  values ('sandy','sandy@hello.com', 6666689898),
         ('san', 'san@san.com', 8787878787)"])
;; [#:next.jdbc{:update-count 2}]

Verifying again

(jdbc/execute! db ["select * from customer"])
;; [#:customer{:id 1, :name "john", :email "john@doe.org", :mobile 9898989898} 
   #:customer{:id 2, :name "sandy", :email "sandy@hello.com", :mobile 6666689898} 
   #:customer{:id 3, :name "san", :email "san@san.com", :mobile 8787878787}]

All good so far. Now, comes the interesting part. All this while, we have been working with plain SQL queries, represented as strings right. We will now try to represent the queries in a more data-oriented manner.

With HoneySQL

HoneySQL brings in some amazing syntactic sugar to the process of structuring the SQL queries. It allows us to describe the queries in the form of a map data structure, a much more eloquent and neater way of doing it in my opinion.

Let us use honeysql to create the same queries we were working with earlier. First up, do a require of the library

(refer-clojure :exclude '[filter for group-by into partition-by set update])
(require '[honey.sql :as sql]
         '[honey.sql.helpers :refer :all :as h]
         '[clojure.core :as c])

Fetching all rows in the table customer, this is how you define the query

(def sql-map {:select [:*]
               :from [:customer]})

The pieces that make up the SQL statement are defined as key/value pairs of a map. In the simple example above, the sql-map has two entries

1. key — select clause with the value of [:*] which is nothing but *
2. key — from that has a value of the table name customer

To convert this to the standard SQL query string that we were working with earlier, we need to user the format method from honeysql like so:

(-> sql-map
    (sql/format))
;; ["SELECT * FROM customer"]

You see that it returns the string representation of the query.

Let us try one more example :

(def sql-map {:select [:*]
              :from [:customer]
              :where [:= :id 1]})
(-> sql-map
    (sql/format))
;; ["SELECT * FROM customer WHERE id = ?" 1]

As you see, the nicely represented query as a Clojure map is converted into the string representation with the appropriate parameterised values.

As I said, honeysql adds some nice syntactic sugar to the sql queries being built, hence the honey in its name, perhaps :-)

To execute the above query you follow the same mechanics like so :

(jdbc/execute! db (sql/format sql-map))
;; [#:customer{:id 1, :name "john", :email "john@doe.org", :mobile 9898989898}]

we get back the row with id=1

We can even list the specific columns that you want to be fetched like so:

(def sql-map {:select [:name :email]
              :from [:customer]
              :where [:= :id 1]})
(jdbc/execute! db (sql/format sql-map)
;; [#:customer{:name "john", :email "john@doe.org"}]

Let us insert a few more rows using honeysql . Let us define a map

(def sql-map {:insert-into [:customer]
              :columns [:name :email :mobile]
              :values [["hello" "hello@hello.com" 8787878787]
                       ["whatis" "what@whatis.org" 1234567890]]})

:insert-into — specifies the table to insert the values into

:columns — specifies the columns the values are being associated with

:values — specifies the actual values to be inserted. Notice that this is a collection of rows ( vector of vectors )

Now, we can execute the query

(jdbc/execute! db (sql/format sql-map))
;; [#:next.jdbc{:update-count 2}]

We get a return message saying that 2 rows have been inserted. Let us verify the same.

(def sql-map {:select [:*]
               :from [:customer]})
(jdbc/execute! db (sql/format sql-map))
;; [#:customer{:id 1, :name "john", :email "john@doe.org", :mobile 9898989898} 
    #:customer{:id 2, :name "sandy", :email "sandy@hello.com", :mobile 6666689898} 
    #:customer{:id 3, :name "san", :email "san@san.com", :mobile 8787878787} 
   #:customer{:id 4, :name "hello", :email "hello@hello.com", :mobile 8787878787} 
   #:customer{:id 5, :name "whatis", :email "what@whatis.org", :mobile 1234567890}
   ]

We see that the rows have been inserted.

Let us now try another query :

(def sql-map {:select [:*]
              :from [:customer]
              :where [:and [:> :id 2][:< :id 5]]})

In this case, we are using an AND to combine multiple where clauses.

Let us execute this sql query

(jdbc/execute! db (sql/format sql-map))
;; [#:customer{:id 3, :name "san", :email "san@san.com", :mobile 8787878787} #:customer{:id 4, :name "hello", :email "hello@hello.com", :mobile 8787878787}]

As expected, it returns the rows with id between 2 & 5 which would be rows with id=3 and id=4Great!

Let us try an update query. Let us update the email of the customer with id=2 with a new one. First, we have to define the query map

(def sql-map {:update :customer
              :set {:email "sandy@sandy.com"}
              :where [:= :id 2]})

Let us execute this :

(jdbc/execute! db (sql/format sql-map ))
;; [#:next.jdbc{:update-count 1}]

Return message says that a row has been updated. Let us quickly verify

(def sql-map {:select [:name :email]
              :from [:customer]
              :where [:= :id 2]})
(jdbc/execute! db (sql/format sql-map))
;; [#:customer{:name "sandy", :email "sandy@sandy.com"}]

So, we see that the email has been updated to the new one, awesome!

As one last example, let us try a delete query

Let us delete the last row ( with id=5 )

(def sql-map {:delete-from :customer
              :where [:= :id 5]})
(jdbc/execute! db (sql/format sql-map ))
;; [#:next.jdbc{:update-count 1}]

Let us verify ( using the Clojure threaded macros here )

(->> {:select :*
              :from :customer}
     (sql/format)
     (jdbc/execute! db))
;; [#:customer{:id 1, :name "john", :email "john@doe.org", :mobile 9898989898} #:customer{:id 3, :name "san", :email "san@san.com", :mobile 8787878787} #:customer{:id 4, :name "hello", :email "hello@hello.com", :mobile 8787878787} #:customer{:id 2, :name "sandy", :email "sandy@sandy.com", :mobile 6666689898}]

We see that the customer with id=5 has been removed.

There are of-course may more advanced SQL queries that can be built with honeysql using purely data. That is the power of honeysql. It is an interesting take and definitely more easier to structure and modify the clauses in the queries since they are all maps

Let me give an example before closing.

Say, we start with a query like this, which picks up all the columns in the rows :

(def sql-map {:select [:*]
               :from [:customer]})

But, later realise that you need just the name and email and not the mobile, we can modify this sql-map during execution since it is just a map. This is done like so :

(-> sql-map
    (dissoc :select)
    (select :name :email)
    (sql/format))
;; ["SELECT name, email FROM customer"]

We use the map's dissoc method to disassociate the currently assigned value to :select and reassign it with a new value on the next line. You see the result of the sql/format method. The updated query is seen reflected there. This is the power of using a data-oriented approach.

Let us run this :

(-> sql-map
    (dissoc :select)
    (:select :name :email)
    (sql/format)
    (->> (jdbc/execute! db)))

As you see, we are replacing the select clause with a different one and execute the new query to get the desired result, thats fabulous, isn't it!

Putting together all the code pieces that we worked on through this article here, just for your reference :

PSQL :

;; Setup the PSQL database needed to work with
psql -U <username>
psql -U postgres
create database ecomdb;
\l
\c ecomdb
create table if not exists customer (
id SERIAL PRIMARY KEY NOT NULL,
name VARCHAR(100),
email VARCHAR(100),
mobile BIGINT);
\dt

Clojure :

;; Create a new Clojure project
lein new app psql-demo
;; Configure all the dependencies
(defproject postgres-demo "0.1.0-SNAPSHOT"
  :description "FIXME: write description"
  :url "http://example.com/FIXME"
  :license {:name "EPL-2.0 OR GPL-2.0-or-later WITH Classpath-exception-2.0"
            :url "https://www.eclipse.org/legal/epl-2.0/"}
  :dependencies [[org.clojure/clojure "1.10.3"]
                 [com.github.seancorfield/next.jdbc "1.3.834"]
                 [org.postgresql/postgresql "42.2.10"]
                 [com.github.seancorfield/honeysql "2.3.928"]]
  :main ^:skip-aot postgres-demo.core
  :target-path "target/%s"
  :profiles {:uberjar {:aot :all
                       :jvm-opts ["-Dclojure.compiler.direct-linking=true"]}})
;; Download and setup the dependent Clojure libraries
lein deps
;; Get into the REPL to explore!
lein repl
;; Start ...
(require '[next.jdbc :as jdbc])
(def db-config
  {:dbtype "postgresql"
   :dbname "ecomdb"
   :host "localhost"
   :user "postgres"
   :password "postgres"})
(def db (jdbc/get-datasource db-config))
(jdbc/execute! db ["select * from customer"])
(jdbc/execute! db ["
 insert into customer (name,email,mobile)
  values('john','john@doe.org', 9898989898)"])
(jdbc/execute! db ["select * from customer"])
(jdbc/execute! db ["
 insert into customer (name,email,mobile)
  values ('sandy','sandy@hello.com', 6666689898),
         ('san', 'san@san.com', 8787878787)"])
(jdbc/execute! db ["select * from customer"])
(refer-clojure :exclude '[filter for group-by into partition-by set update])
(require '[honey.sql :as sql]
         '[honey.sql.helpers :refer :all :as h]
         '[clojure.core :as c])
(def sql-map {:select [:*]
               :from [:customer]})
(-> sql-map
    (sql/format))
(def sql-map {:select [:*]
              :from [:customer]
              :where [:= :id 1]})
(-> sql-map
    (sql/format))
(jdbc/execute! db (sql/format sql-map))
(def sql-map {:select [:name :email]
              :from [:customer]
              :where [:= :id 1]})
(jdbc/execute! db (sql/format sql-map)
(def sql-map {:insert-into [:customer]
              :columns [:name :email :mobile]
              :values [["hello" "hello@hello.com" 8787878787]
                       ["whatis" "what@whatis.org" 1234567890]]})
(jdbc/execute! db (sql/format sql-map))
(def sql-map {:select [:*]
               :from [:customer]})
(jdbc/execute! db (sql/format sql-map))
(def sql-map {:select [:*]
              :from [:customer]
              :where [:and [:> :id 2][:< :id 5]]})
(jdbc/execute! db (sql/format sql-map))
(def sql-map {:update :customer
              :set {:email "sandy@sandy.com"}
              :where [:= :id 2]})
(jdbc/execute! db (sql/format sql-map ))
(def sql-map {:select [:name :email]
              :from [:customer]
              :where [:= :id 2]})
(jdbc/execute! db (sql/format sql-map))
(def sql-map {:delete-from :customer
              :where [:= :id 5]})
(jdbc/execute! db (sql/format sql-map ))
(->> {:select :*
              :from :customer}
     (sql/format)
     (jdbc/execute! db))
(def sql-map {:select [:*]
               :from [:customer]})
(-> sql-map
    (dissoc :select)
    (select :name :email)
    (sql/format))
(-> sql-map
    (dissoc :select)
    (:select :name :email)
    (sql/format)
    (->> (jdbc/execute! db)))

Ok, that does it for this article. Hope that gave you a good intro to the way you interact with PostgreSQL using Clojure and also on working with the powerful honeysql library to construct your SQL queries.

More later, see you until then.