Minimal Clojure Korma example to read data from a postgresql database

clojure-korma-minimal.md

Let's play around with a postgres database and Clojure. To query and insert a database, we need to create one:

docker run --rm --name eventdb -e POSTGRES_PASSWORD=foo -e POSTGRES_USER=foo -e POSTGRES_DB=foo -d -p 5432:5432 postgres

This creates a postgres database named foodb, with password, user and tablename also being foo. Ports are exposed to our local machine and we can access the database at localhost:5432.

Now, we need to connect to the database and create a table, which can be queried and filled with korma. You can use a library to do this, but for a minimal example this is not necessary:

user@host ~ % docker exec -it eventdb su postgres -c psql
psql (9.6.4)
Type "help" for help.

postgres=# 

This gives us an interactive mode for use with our database. To create a table, we need to connect to our database foo first:

postgres=# \c foo;
You are now connected to database "foo" as user "postgres".

Create a table like this:

foo=# CREATE TABLE events (id SERIAL, aggregate INT, type varchar, version INT, data varchar);
CREATE TABLE

I am not an expert in postgresql and I usually do not manually create these tables, but this should be okay for a minimal example.

---

Let's switch to Clojure. I will use korma to query and insert data into the database.

First lets create a leiningen project with the following project.clj:

(defproject myproject "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.9.0-alpha17"]
                 [korma "0.4.3"]
                 [org.postgresql/postgresql "42.1.4"]])

And our code on core.clj could look like this:

(ns myproject.core
  (:use [korma.core]
        [korma.db]))

(defdb pg (postgres
            {:host "localhost"
             :port "5432"
             :db "foo"
             :user "foo"
             :password "foo"
             :delimiters ""}))

(defentity events
  (entity-fields :id :aggregate :type :version :data))

(select events)
;; => ()

(insert events
        (values {:aggregate 0 :type "test" :version 1 :data "hello, world!"}))

(select events)
;; => ({:id 1, :aggregate 0, :type "test", :version 1, :data "hello, world!"})

That's all we need to read and insert data into a postgresql database in Clojure.