Connecting a Clojure REPL to a PostgreSQL Docker Container

Ivar Thorson bio photo By Ivar Thorson

In this tutorial, we’ll launch Postgres (a.k.a. PostgreSQL) in a Docker container, create a Postgres database, and connect to it from a Clojure REPL. Postgres is a great open source SQL database with a long history, and is a good choice for many small-to-medium scale projects.

First, we need to download the official Postgres image, named postgres, create a new container, and start the image running in that container. Remember, in the parlance of Docker, “images” are the recipe and “containers” are the cake(s) made from that recipe. By default, Docker containers can make connections to the outside world, but the outside world cannot connect to containers. So we need to “publish” a port that has been exposed in the Docker image.

Downloading the docker image, instantiating it, setting a password, and publishing a port can all be done in a single command:

docker run  --name my-postgres-container --env POSTGRES_PASSWORD=mysecretpassword -p 5432:5432 --detach postgres

where

-p 5432:5432 Publish port 5432 in the container as 5432 on localhost, so you can connect
-name        Gives a name to this container
-env    Environment variable(s)
-detach Disconnect terminal

An important note, as I mentioned in the article on using Docker to deploy Clojure apps, is that the docker run command creates a new container – it “bakes a new cake”. If you want to launch a container you have already baked, you’ll use the docker start command instead, as we’ll see below.

You can check that the Docker container is running with

docker container ls

or more succinctly

docker ps

Next, let’s use another Docker trick to create a second postgres cake, this time running the psql command, and link it to the other container. We want this container to be deleted when we close it (--rm), and to be linked to the my-postgres-container so that it can access the server we just started. It is interesting that we can use psql without having it installed on our local machine; we’re using the command that is inside a container!

docker run -it --rm --link my-postgres-container:postgres postgres psql -h postgres -U postgres

In that terminal, let’s now create a test database:

CREATE DATABASE testdb;

You may now hit control-d to quit the terminal if you want to close it. In my case, I often leave it open for debugging.

Make a new Clojure project, and be sure to add these lines to the :dependencies:

             [org.clojure/java.jdbc "0.7.9"]
             [org.postgresql/postgresql "42.2.5"]

Now spin a up a Clojure REPL in that project, and run the following commands, one at a time, and note the output of each.

(ns mytest (:require [clojure.java.jdbc :as sql]))

(def db-spec {:dbtype "postgresql" :dbname "testdb" :user "postgres" :password "mysecretpassword"})

(sql/query db-spec ["SELECT 3*5 AS result"])

(sql/db-do-commands db-spec (sql/create-table-ddl :testing [[:data :text]]))

(sql/insert! db-spec :testing {:data "hahaha"})

(sql/insert! db-spec :testing {:data "lol"})

(sql/query db-spec ["SELECT * FROM testing"])

Now it’s time to develop that app to your heart’s content.

When you are done with your app, you can stop your container with:

docker container stop my-postgres-container

At later times, you can restart the container, and use its state, with:

docker container start my-postgres-container

You can delete the stopped container with:

docker container rm my-postgres-container

Note that if you want a second container for postgres – say, for a different project – you could make a second container called “my-postgres-container2” using the docker run command near the top of this. Make as many containers as you need, and start and stop them as required.

Keeping the database in the container is fine during first steps and testing, but it can be slow and wasteful of disk. The reccommended solution is use Docker Volumes to be able to upgrade the Postgres version separately from the data itself.

An example of how to do that is in this case is:

docker volume create --name postgresvol

docker run  --name my-postgres-container --env POSTGRES_PASSWORD=mysecretpassword -p 5432:5432 --detach -v postgresvol:/var/lib/postgresql/data postgres

References

  • The Clojure JDBC docs were a helpful starting point for learning how to use JDBC.
  • The Clojure JDBC SQL page is also very good.
  • The Docker Getting Started Guide. Docker documentation is detailed and helpful. The getting started guide is decent, but not as example-heavy and in-depth as I would have wanted.
  • Docker Documentation on Binding Container Ports to the Host. This has a critical quote that helped me on a related problem: “By default Docker containers can make connections to the outside world, but the outside world cannot connect to containers.” Even though I had exposed the ports in docker images with “EXPOSE”, they were not actually be reachable from the host unless you launch the container with docker run -P ... or docker run -p 5432:5432. Kind of a gotcha for newbies, I feel.