Clojure jdbc create-table statement does not run unless using Leiningen REPL

Question

I've got a small Clojure program that uses the Clojure JDBC tools to create a table in an HSQL database. However, it only seems to actually create the table if I run it from Leiningen's REPL. It does not create the table if I run the code using lein run or from my IDE (IntelliJ). There are no exceptions reported. In both cases, the output is just "(0)".

Here's the code snippet:

(ns tramway.core
  (:require [clojure.java.io :as io]
            [clojure.java.jdbc :as sql]))

(def hsql-db {:subprotocol "hsqldb"
              :subname "file:/tmp/tramwaydb"
              :user "SA"
              :password ""})

(defn -main []
  (println (sql/with-connection hsql-db (sql/create-table
                                 :footfall
                                 [:id "INTEGER" "GENERATED ALWAYS AS IDENTITY(START WITH 1)"]
                                 [:sample_date "DATE"]
                                 [:exhibition "varchar(255)"]))))

And since I'm using Leiningen, here's my project.clj:

(defproject tramway "1.0.0-SNAPSHOT"
  :description "Description here"
  :dependencies [[org.clojure/clojure "1.3.0"]
                 [org.clojure/java.jdbc "0.1.4"]
                 [org.hsqldb/hsqldb "2.2.8"]]
  :main tramway.core)

If I do:

$ lein repl

tramway.core=> (-main)
(0)
nil

and then check /tmp/tramway.log I can see the CREATE TABLE executed successfully.

However, if I do:

$ rm -rf /tmp/tramway.*
$ lein run
(0)

and then check the same file, it is empty. It does create the .log, .properties, and .script files. All but the .log file have content; there's just no record of the CREATE TABLE having been run.

What am I doing wrong? I would expect to have the same result whether I run my (-main) function from the REPL or have Leiningen run it automatically.

I have also tried taking the table creation out of the -main function and running it just as a script through my IDE, and I still get the same bad result.

Answer 1

This is a common HSQLDB configuration question.

HSQLDB's default configuration is not intended for test usage. As a result, it delays writing and synching the .log entries by 500 milliseconds and it does not shutdown the database when the connection is closed. Try either of these settings in your URL:

:subname "file:/tmp/tramwaydb;hsqldb.write_delay=false"

or

:subname "file:/tmp/tramwaydb;shutdown=true"

See the various option here: http://hsqldb.org/doc/2.0/guide/dbproperties-chapt.html