Run Tests Against your Database

Testing code like (+ 1 1) is trivial. What you often need is to be able to test the code that queries and inserts data into your database. This is not quite as trivial.

• Add org.testcontainers/postgresql and org.mybatis/mybatis-migrations to your :dev alias

  Testcontainers is a set of libraries which roll up all the logic needed to run things like Postgres in your tests.

  We are adding MyBatis migrations here as a library so that we can get the same result as using the migrate command.

  {:paths ["src"]
   :deps {org.clojure/clojure {:mvn/version "1.12.0"}
          ;; HTTP server
          ring/ring {:mvn/version "1.13.0"}
          ;; Routing
          metosin/reitit-ring {:mvn/version "0.7.2"}
          ;; Logging Facade
          org.clojure/tools.logging {:mvn/version "1.3.0"}
          ;; Logging Implementation
          org.slf4j/slf4j-simple {:mvn/version "2.0.16"}
          ;; HTML Generation
          hiccup/hiccup {:mvn/version "2.0.0-RC3"}
          ;; Database Queries
          com.github.seancorfield/next.jdbc {:mvn/version "1.3.955"}
          ;; Postgres Driver
          org.postgresql/postgresql {:mvn/version "42.7.4"}
          ;; Database Connection Pooling
          com.zaxxer/HikariCP {:mvn/version "6.0.0"}
          ;; Unify Environment Variables + .env Handing
          io.github.cdimascio/dotenv-java {:mvn/version "3.0.2"}
          ;; HTTP Middleware
          ring/ring-defaults {:mvn/version "0.5.0"}
          ;; Background Jobs
          msolli/proletarian {:mvn/version "1.0.86-alpha"}
          ;; JSON Reading and Writing
          cheshire/cheshire {:mvn/version "5.13.0"}
          ;; Dynamic SQL Generation
          com.github.seancorfield/honeysql {:mvn/version "2.6.1203"}}
   :aliases {:dev {:extra-paths ["dev" "test"]
                   :extra-deps {nrepl/nrepl {:mvn/version "1.3.0"}
                                ;; Test Runner
                                lambdaisland/kaocha {:mvn/version "1.91.1392"}
                                ;; Run Postgres in Tests
                                org.testcontainers/postgresql {:mvn/version "1.20.3"}
                                ;; Run Migrations on the Test Database
                                org.mybatis/mybatis-migrations {:mvn/version "3.4.0"}}}
  
  
             :format {:deps {dev.weavejester/cljfmt {:mvn/version "0.13.0"}}}
             :lint   {:deps {clj-kondo/clj-kondo {:mvn/version "2024.09.27"}}}}}

• Create a example.test-system namespace and put the following in it.

  The reason we are calling it test-system instead of test-db is because later on we will put helpers to start test versions of other parts of the system we usually pass around.

  The reason we don't call it test-utils or something generic like that is because the natural tendency will be to use that as a dumping ground for all sorts of potpourri.

  This code will start up a docker container running postgres in the background.

  (ns example.test-system
    (:import (org.testcontainers.containers PostgreSQLContainer)
             (org.testcontainers.containers.wait.strategy Wait)
             (org.testcontainers.utility DockerImageName)))
  
  (set! *warn-on-reflection* true)
  
  (defn start-pg-test-container
    []
    (let [container (PostgreSQLContainer.
                      (-> (DockerImageName/parse "postgres")
                          (.withTag "17")))]
      (.start container)
      (.waitingFor container (Wait/forListeningPort))
      container))

• Connect to the test database

  You need to inspect PostgreSQLContainer object to get the info needed to make a DataSource

  (ns example.test-system
    (:require [next.jdbc :as jdbc])
    (:import (org.testcontainers.containers PostgreSQLContainer)
             (org.testcontainers.containers.wait.strategy Wait)
             (org.testcontainers.utility DockerImageName)))
  
  (set! *warn-on-reflection* true)
  
  (defn start-pg-test-container
    []
    (let [container (PostgreSQLContainer.
                      (-> (DockerImageName/parse "postgres")
                          (.withTag "17")))]
      (.start container)
      (.waitingFor container (Wait/forListeningPort))
      container))
  
  (defn get-test-db
    []
    (let [container (start-pg-test-container)]
      (jdbc/get-datasource
        {:dbtype   "postgresql"
         :jdbcUrl  (str "jdbc:postgresql://"
                        (PostgreSQLContainer/.getHost container)
                        ":"
                        (PostgreSQLContainer/.getMappedPort container PostgreSQLContainer/POSTGRESQL_PORT)
                        "/"
                        (PostgreSQLContainer/.getDatabaseName container)
                        "?user="
                        (PostgreSQLContainer/.getUsername container)
                        "&password="
                        (PostgreSQLContainer/.getPassword container))})))
  

• Run your migrations against the test database.

  Let's break down this code a little:

  1. We run an "UpOperation." This is the thing that will run all our SQL against the test database. No I don't know why the method is called .operate and not .run, .call, .execute, or something else sensible.
  2. The first argument to this operation is a "DataSourceConnectionProvider." Nothing magic here, just forwards the database connection along.
  3. The second argument tells the operation where to find our migration scripts. As part of this we load in the properties file for our development environment. We do this so that any settings which are important there are carried over.
  4. Next argument is a DatabaseOperationOption with sendFullScript set to true. Without this the migration framework will see every ; as signifying a new statement. This setting is in development.properties but for whatever reason isn't forwarded properly without setting it explicitly.
  5. The last nil argument is a place for us to customize how it prints to the console.

  (ns example.test-system
    (:require [clojure.java.io :as io]
              [next.jdbc :as jdbc])
    (:import (java.util Properties)
             (org.apache.ibatis.migration DataSourceConnectionProvider FileMigrationLoader)
             (org.apache.ibatis.migration.operations UpOperation)
             (org.apache.ibatis.migration.options DatabaseOperationOption)
             (org.testcontainers.containers PostgreSQLContainer)
             (org.testcontainers.containers.wait.strategy Wait)
             (org.testcontainers.utility DockerImageName)))
  
  (set! *warn-on-reflection* true)
  
  (defn start-pg-test-container
    []
    (let [container (PostgreSQLContainer.
                      (-> (DockerImageName/parse "postgres")
                          (.withTag "17")))]
      (.start container)
      (.waitingFor container (Wait/forListeningPort))
      container))
  
  (defn get-test-db
    []
    (let [container (start-pg-test-container)]
      (jdbc/get-datasource
        {:dbtype   "postgresql"
         :jdbcUrl  (str "jdbc:postgresql://"
                        (PostgreSQLContainer/.getHost container)
                        ":"
                        (PostgreSQLContainer/.getMappedPort container PostgreSQLContainer/POSTGRESQL_PORT)
                        "/"
                        (PostgreSQLContainer/.getDatabaseName container)
                        "?user="
                        (PostgreSQLContainer/.getUsername container)
                        "&password="
                        (PostgreSQLContainer/.getPassword container))})))
  
  (defn run-migrations
    [db]
    (let [scripts-dir (io/file "migrations/scripts")
          env-properties (io/file "migrations/environments/development.properties")]
      (with-open [env-properties-stream (io/input-stream env-properties)]
        (.operate (UpOperation.)
                  (DataSourceConnectionProvider. db)
                  (FileMigrationLoader.
                    scripts-dir
                    "UTF-8"
                    (doto (Properties.)
                      (.load env-properties-stream)))
                  (doto (DatabaseOperationOption.)
                    (.setSendFullScript true))
                  nil))))

• Create a function named with-test-db.

  This should get a test datasource, run migrations on it, then forward that to a callback function.

  (ns example.test-system
    (:require [clojure.java.io :as io]
              [next.jdbc :as jdbc])
    (:import (java.util Properties)
             (org.apache.ibatis.migration DataSourceConnectionProvider FileMigrationLoader)
             (org.apache.ibatis.migration.operations UpOperation)
             (org.apache.ibatis.migration.options DatabaseOperationOption)
             (org.testcontainers.containers PostgreSQLContainer)
             (org.testcontainers.containers.wait.strategy Wait)
             (org.testcontainers.utility DockerImageName)))
  
  (set! *warn-on-reflection* true)
  
  (defn start-pg-test-container
    []
    ...)
  
  (defn get-test-db
    []
    ...)
  
  (defn run-migrations
    [db]
    ...)
  
  (defn with-test-db
    [callback]
    (let [db (get-test-db)]
      (run-migrations db)
      (callback db)))

• Make a test that uses the database

  Counting is a kind of math, so let's add a test which verifies that COUNT(*) functions correctly when we insert rows into prehistoric.hominid.

  (ns example.math-test
    (:require [clojure.test :as t]
              [example.test-system :as test-system]
              [next.jdbc :as jdbc]))
  
  (t/deftest one-plus-one
    (t/is (= (+ 1 1) 2) "One plus one equals 2!"))
  
  (t/deftest counting-works
    (test-system/with-test-db
      (fn [db]
        (jdbc/execute! db ["INSERT INTO prehistoric.hominid(name) VALUES (?)" "Grunto"])
        (jdbc/execute! db ["INSERT INTO prehistoric.hominid(name) VALUES (?)" "Blingus"])
        (t/is (= (:count (jdbc/execute-one! db ["SELECT COUNT(*) as count FROM prehistoric.hominid"]))
                 2)))))
  

• Only start the test container once.

  Starting a docker image for every test is pretty wasteful. The only benefit is getting a totally clean slate, but we can achieve that in a different way.

  Instead of calling start-pg-test-container in get-test-db, we will call that function in a delay. A delay will only run the code inside of it once its dereferenced and cache the result for any subsequent calls. To be extra sure it will only run once, we'll pair it with defonce.

  As part of this we will tell the JVM to close the container when it exits. If we didn't do this we would end up with dangling docker containers. A shutdown hook isn't perfect - there are ways you can close the JVM without it getting a chance to run - but its good enough for local development.

  (ns example.test-system
    (:require [clojure.java.io :as io]
              [next.jdbc :as jdbc])
    (:import (java.util Properties)
             (org.apache.ibatis.migration DataSourceConnectionProvider FileMigrationLoader)
             (org.apache.ibatis.migration.operations UpOperation)
             (org.apache.ibatis.migration.options DatabaseOperationOption)
             (org.testcontainers.containers PostgreSQLContainer)
             (org.testcontainers.containers.wait.strategy Wait)
             (org.testcontainers.utility DockerImageName)))
  
  (set! *warn-on-reflection* true)
  
  (defn start-pg-test-container
    []
    (let [container (PostgreSQLContainer.
                     (-> (DockerImageName/parse "postgres")
                         (.withTag "17")))]
      (.start container)
      (.waitingFor container (Wait/forListeningPort))
      container))
  
  (defonce pg-test-container-delay
    (delay
      (let [container (start-pg-test-container)]
        (.addShutdownHook (Runtime/getRuntime)
                          (Thread. #(PostgreSQLContainer/.close container)))
        container)))
  
  (defn get-test-db
    []
    (let [container @pg-test-container-delay]
      (jdbc/get-datasource
       {:dbtype   "postgresql"
        :jdbcUrl  (str "jdbc:postgresql://"
                       (PostgreSQLContainer/.getHost container)
                       ":"
                       (PostgreSQLContainer/.getMappedPort container PostgreSQLContainer/POSTGRESQL_PORT)
                       "/"
                       (PostgreSQLContainer/.getDatabaseName container)
                       "?user="
                       (PostgreSQLContainer/.getUsername container)
                       "&password="
                       (PostgreSQLContainer/.getPassword container))})))
  

• Create a clean slate for each test

  If you were to run the counting-works test multiple times in the REPL, it would start to fail after the first time. This is because we aren't starting a fresh container each time anymore and data from previous test runs will still be around.

  To deal with this we will make use of the CREATE DATABASE ... TEMPLATE ... feature of postgres.

  For each test, we come up with a new database name. Having an atom we just count up with is an easy way to do that. We also make sure that migrations are only run on the "template database" once using a delay.

  (ns example.test-system
    (:require [clojure.java.io :as io]
              [next.jdbc :as jdbc])
    (:import (java.util Properties)
             (org.apache.ibatis.migration DataSourceConnectionProvider FileMigrationLoader)
             (org.apache.ibatis.migration.operations UpOperation)
             (org.apache.ibatis.migration.options DatabaseOperationOption)
             (org.testcontainers.containers PostgreSQLContainer)
             (org.testcontainers.containers.wait.strategy Wait)
             (org.testcontainers.utility DockerImageName)))
  
  (set! *warn-on-reflection* true)
  
  (defn start-pg-test-container
    []
    ...)
  
  (defonce pg-test-container-delay
    ...)
  
  (defn get-test-db
    []
    ...)
  
  (defn run-migrations
    [db]
    ...)
  
  (defonce migrations-delay
    (delay (run-migrations (get-test-db))))
  
  (def test-counter (atom 0))
  
  (defn with-test-db
    [callback]
    @migrations-delay
    (let [test-database-name (str "test_" (swap! test-counter inc))
          container          @pg-test-container-delay
          db                 (get-test-db)]
      (jdbc/execute!
       db
       [(format "CREATE DATABASE %s TEMPLATE %s;"
                test-database-name
                (PostgreSQLContainer/.getDatabaseName container))])
  
      (try
        (let [db (jdbc/get-datasource
                  {:dbtype   "postgresql"
                   :jdbcUrl  (str "jdbc:postgresql://"
                                  (PostgreSQLContainer/.getHost container)
                                  ":"
                                  (PostgreSQLContainer/.getMappedPort container
                                                                      PostgreSQLContainer/POSTGRESQL_PORT)
                                  "/"
                                  test-database-name
                                  "?user="
                                  (PostgreSQLContainer/.getUsername container)
                                  "&password="
                                  (PostgreSQLContainer/.getPassword container))})]
          (callback db))
        (finally
          (jdbc/execute! db
                         [(format "DROP DATABASE %s;" test-database-name)])))))

• Mark everything but with-test-db as ^:private

  ^:private in Clojure isn't as strong as private in Java, but it will suffice. The goal is just to make it clear to code using the example.test-system namespace that with-test-db is the only thing that is part of its public API.

  This feels extra, but test code is still code. Everything but with-test-db is machinery you might want to revisit later.

  (ns example.test-system
    (:require [clojure.java.io :as io]
              [next.jdbc :as jdbc])
    (:import (java.util Properties)
             (org.apache.ibatis.migration DataSourceConnectionProvider FileMigrationLoader)
             (org.apache.ibatis.migration.operations UpOperation)
             (org.apache.ibatis.migration.options DatabaseOperationOption)
             (org.testcontainers.containers PostgreSQLContainer)
             (org.testcontainers.containers.wait.strategy Wait)
             (org.testcontainers.utility DockerImageName)))
  
  (set! *warn-on-reflection* true)
  
  (defn ^:private start-pg-test-container
    []
    (let [container (PostgreSQLContainer.
                     (-> (DockerImageName/parse "postgres")
                         (.withTag "17")))]
      (.start container)
      (.waitingFor container (Wait/forListeningPort))
      container))
  
  (defonce ^:private pg-test-container-delay
    (delay
      (let [container (start-pg-test-container)]
        (.addShutdownHook (Runtime/getRuntime)
                          (Thread. #(PostgreSQLContainer/.close container)))
        container)))
  
  (defn ^:private get-test-db
    []
    (let [container @pg-test-container-delay]
      (jdbc/get-datasource
       {:dbtype   "postgresql"
        :jdbcUrl  (str "jdbc:postgresql://"
                       (PostgreSQLContainer/.getHost container)
                       ":"
                       (PostgreSQLContainer/.getMappedPort container PostgreSQLContainer/POSTGRESQL_PORT)
                       "/"
                       (PostgreSQLContainer/.getDatabaseName container)
                       "?user="
                       (PostgreSQLContainer/.getUsername container)
                       "&password="
                       (PostgreSQLContainer/.getPassword container))})))
  
  (defn ^:private run-migrations
    [db]
    (let [scripts-dir (io/file "migrations/scripts")
          env-properties (io/file "migrations/environments/development.properties")]
      (with-open [env-properties-stream (io/input-stream env-properties)]
        (.operate (UpOperation.)
                  (DataSourceConnectionProvider. db)
                  (FileMigrationLoader.
                   scripts-dir
                   "UTF-8"
                   (doto (Properties.)
                     (.load env-properties-stream)))
                  (doto (DatabaseOperationOption.)
                    (.setSendFullScript true))
                  nil))))
  
  (defonce ^:private migrations-delay
    (delay (run-migrations (get-test-db))))
  
  (def ^:private test-counter (atom 0))
  
  (defn with-test-db
    [callback]
    @migrations-delay
    (let [test-database-name (str "test_" (swap! test-counter inc))
          container          @pg-test-container-delay
          db                 (get-test-db)]
      (jdbc/execute!
       db
       [(format "CREATE DATABASE %s TEMPLATE %s;"
                test-database-name
                (PostgreSQLContainer/.getDatabaseName container))])
  
      (try
        (let [db (jdbc/get-datasource
                  {:dbtype   "postgresql"
                   :jdbcUrl  (str "jdbc:postgresql://"
                                  (PostgreSQLContainer/.getHost container)
                                  ":"
                                  (PostgreSQLContainer/.getMappedPort container
                                                                      PostgreSQLContainer/POSTGRESQL_PORT)
                                  "/"
                                  test-database-name
                                  "?user="
                                  (PostgreSQLContainer/.getUsername container)
                                  "&password="
                                  (PostgreSQLContainer/.getPassword container))})]
          (callback db))
        (finally
          (jdbc/execute! db
                         [(format "DROP DATABASE %s;" test-database-name)])))))

• Run your tests

  Do so first with just test, but after try loading the example.math-test namespace into a REPL and running (t/run-tests).