Demo for Clojure Connection to PostgreSQL and H2 Databases via Next.JDBC

If you need a SQL database, the best answer is usually Postgres (nee PostgreSQL). The easiest way to access Postgres from Clojure is using the Next.JDBC library.

This demo runs Postgres in a Docker container and shows basic access via Next.JDBC

Setup

Ensure Docker is Installed

Make sure you have docker installed on your computer. You should be able to do the following on the command line:

> docker --version
Docker version 20.10.7, build 20.10.7-0ubuntu1~20.04.2

You should not have to use sudo to make the above work. If it fails, please see this page on StackOverflow.com

Verify your Docker Access to Postgres

Kick off a local version of Postgres running in Docker via:

> ./pg-start.bash
c406d9cb25f1f57025620ac32d97707eef6f11b44e763d853dad03a7a015b5e5    # <= Docker container ID

Verify Postgres is running via the PSQL command:

> psql-start.bash --version
psql (PostgreSQL) 14.0 (Debian 14.0-1.pgdg110+1)

All Postgres files will be saved locally under your home directory in:

$HOME/docker/volumes/postgres

You can stop the Postgres container by typing:

> ./pg-stop.bash        # or just type:  docker stop pg-docker

Feel free to look inside the two Bash scripts to see the details. Note that we are using Postgres 14 at present.

Interact with the Postgres DB via PSQL from the Command Line:

> psql-start.bash
psql (14.0 (Debian 14.0-1.pgdg110+1))
Type "help" for help.

postgres=# select version();
                                   version
------------------------------------------------------------------------------------------
PostgreSQL 14.0 (Debian 14.0-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
(1 row)

postgres=# exit

or run as a one-liner:

> ./psql-start.bash  --command='select version();'
                                                           version
-----------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 14.2 (Debian 14.2-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
(1 row)

Run the Unit Tests for H2 and Postgres

No Postgres, No Problem!

If you don’t have Docker available, or if you don’t want to run the Postgres unit test, no problem! Just find go to the file test/tst/demo/jdbc_postgres.clj and make edits so the Var postgres-enable looks like this:

(def postgres-enable false) ; ***** change value to `false` to disable usage of PostgreSQL DB *****

Now, all the Postgres unit tests will be skipped, but your H2 database tests will still run (H2 is a pure Java DB, which doesn’t require installation of the DB and/or Docker).

Unit Tests to Demonstrate Next.JDBC

Prepare to Run the Unit Tests with Postgres (if enabled)

Before you can run the unit tests, you need to create the database example:

> psql-start.bash
psql (14.2)
Type "help" for help.

postgres=# create database example;         # create the db `example`
CREATE DATABASE

postgres=# \l                               # list all DBs
List of databases
Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges
-----------+----------+----------+------------+------------+-----------------------
example   | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
|          |          |            |            | postgres=CTc/postgres
template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
|          |          |            |            | postgres=CTc/postgres
(4 rows)

postgres=# exit         # or use CTRL-D

Run the Unit Tests

> lein clean ; lein test

-----------------------------------
   Clojure 1.10.3    Java 17.0.1
-----------------------------------

lein test tst.demo.jdbc-h2

lein test tst.demo.jdbc-postgres

Ran 7 tests containing 42 assertions.
0 failures, 0 errors.

and then clean up the container:

> ./pg-stop.bash        # or just type:  docker stop pg-docker

License

Copyright © Alan Thompson

Distributed under the Eclipse Public License either version 1.0 or (at your option) any later version.