Connecting clojure to Postgresql db

Question

I am trying to connect my clojure program to postgresql db. I have the required dependencies. This is my repository.clj file

(ns webdev.repository
  (:require [clojure.java.jdbc :as db] ))

(defn create-tables [db]
  (db/execute! db ["create table if not exists movies(id serial not null,
name varchar not null, primary key (id));"])

  (db/execute! db ["create table if not exists users(id varchar not null,
f_name varchar not null, l_name varchar not null, primary key(id));"])
  )

This is part of my core.clj file

(ns webdev.core
  (:require [webdev.repository :as repo])
  (:require [ring.adapter.jetty :as jetty]
            [ring.middleware.reload :refer [wrap-reload]]
            [compojure.core :refer [defroutes GET]]
            [compojure.route :refer [not-found]]
            [ring.handler.dump :refer [handle-dump]]
            ))

(def db "postgresql://localhost:5432/webdev")
(repo/create-tables db) ;;call to create the tables
...
...

When I run this, I get an error saying

Caused by: org.postgresql.util.PSQLException: This ResultSet is closed.
    at org.postgresql.jdbc2.AbstractJdbc2ResultSet.checkClosed(AbstractJdbc2ResultSet.java:2654)
    at org.postgresql.jdbc2.AbstractJdbc2ResultSet.setFetchSize(AbstractJdbc2ResultSet.java:1771)
    at org.postgresql.jdbc4.Jdbc4Statement.createResultSet(Jdbc4Statement.java:39)
    at org.postgresql.jdbc2.AbstractJdbc2Statement$StatementResultHandler.handleResultRows(AbstractJdbc2Statement.java:211)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1773)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:512)

Unable to find out what I am doing wrong.

EDIT

Select statements seem to work. But insert and create table statements give the error "This result set is closed"

Top-Level code runs at compilation. Don't put that there. Look into proper means of db migration or if you want to kick-start, call that function in your -main and from the repl while developing. — cfrick
– cfrick, Commented Mar 11, 2019 at 12:06
@cfrick Tried calling it from main. Still the same error. All select statements seem to work. But insert and create table statements throw "This result set is closed" — Ashwin
– Ashwin, Commented Mar 12, 2019 at 5:25

Simon Brooke · Accepted Answer · 2019-03-13 10:28:15Z

2

Quick solution using conman and hugsql:

(ns ^{:doc "Database access functions, mostly from Luminus template."}
  your-project.db.core
  (:require
    [clojure.java.jdbc :as jdbc]
    [conman.core :as conman]
    [hugsql.core :as hugsql]
    [mount.core :refer [defstate]]
   [postgre-types.json :refer [add-json-type add-jsonb-type]]))

(defstate ^:dynamic *db*
           :start (conman/connect! {:jdbc-url-env (env :database-url)
                                    :jdbc-url "jdbc:postgresql://127.0.0.1/yourdb_dev?user=user&password=thisisnotsecure"
                                    :driver-class-name "org.postgresql.Driver"})
           :stop (conman/disconnect! *db*))

(conman/bind-connection *db* "sql/queries.auto.sql" "sql/queries.sql")

But even simpler, this is what you get for free using Luminus. Just start your project

lein new luminus my-project +postgres

and it works out of the box.

Brief summary: don't reinvent wheels, unless you're learning to be a wheelbuilder. Stand upon the shoulders of giants.

answered Mar 13, 2019 at 10:28

Simon Brooke

1723 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

myguidingstar Over a year ago

Another option instead of Luminus is Duct framework

Alan Thompson · Accepted Answer · 2019-03-11 16:27:05Z

Here is an example using H2: https://github.com/cloojure/demo-jdbc

You can clone the repo and run via lein test. As cfrick says, the call to create the DB should not be at the top-level, which runs during the compilation phase. Instead, the call to create-table should be inside a function that is called from -main (or in the example as part of a unit test).

(ns tst.demo.jdbc
  (:use demo.core tupelo.core tupelo.test)
  (:require
    [clojure.java.jdbc :as jdbc]
    [hikari-cp.core :as pool]
  ))

(def raw-db-spec
  {:classname   "org.h2.Driver"
   :subprotocol "h2:mem"    ; the prefix `jdbc:` is added automatically
   :subname     "demo;DB_CLOSE_DELAY=-1" ; `;DB_CLOSE_DELAY=-1` very important!!!
                     ; http://www.h2database.com/html/features.html#in_memory_databases
                     ; http://makble.com/using-h2-in-memory-database-in-clojure
   :user        "sa"        ; "system admin"
   :password    ""          ; empty string by default
   })

(dotest
  ; creates & drops a connection (& transaction) for each command
  (jdbc/db-do-commands raw-db-spec ["drop table if exists langs"
                                    "drop table if exists releases"])

  ; Creates and uses a connection for all commands
  (jdbc/with-db-connection
    [conn raw-db-spec]
    (jdbc/db-do-commands
      conn
      [(jdbc/create-table-ddl :langs
                              [[:id :serial]
                               [:lang "varchar not null"]])
       (jdbc/create-table-ddl :releases
                              [[:id :serial]
                               [:desc "varchar not null"]
                               [:langId "numeric"]])]))

  ; create & use a connection for multiple commands
  (jdbc/with-db-connection
    [conn raw-db-spec]
    (jdbc/insert-multi! raw-db-spec :langs ; => ({:id 1} {:id 2})
                        [{:lang "Clojure"}
                         {:lang "Java"}])

    (let [result (jdbc/query raw-db-spec ["select * from langs"])]
      (is= result [{:id 1, :lang "Clojure"}
                   {:id 2, :lang "Java"}])))

  ; Wraps all commands in a single transaction
  (jdbc/with-db-transaction
    [tx raw-db-spec]
    (let [clj-id (grab :id (only (jdbc/query tx ["select id from langs where lang='Clojure'"])))]
      (jdbc/insert-multi! tx :releases
                          [{:desc "ancients" :langId clj-id}
                           {:desc "1.8" :langId clj-id}
                           {:desc "1.9" :langId clj-id}]))
    (let [java-id (grab :id (only (jdbc/query tx ["select id from langs where lang='Java'"])))]
      (jdbc/insert-multi! tx :releases
                          [{:desc "dusty" :langId java-id}
                           {:desc "8" :langId java-id}
                           {:desc "9" :langId java-id}
                           {:desc "10" :langId java-id}])))

  ; Creates and uses a connection for each command
  (let [
        ; note cannot wrap select list in parens or get "bulk" output
        result-0 (jdbc/query raw-db-spec ["select langs.lang, releases.desc
                                             from    langs join releases
                                             on     (langs.id = releases.langId)
                                             where  (lang = 'Clojure') "])
        result-1 (jdbc/query raw-db-spec ["select l.lang, r.desc
                                             from    langs as l
                                                       join releases as r
                                             on     (l.id = r.langId)
                                             where  (l.lang = 'Clojure') "])
        result-2 (jdbc/query raw-db-spec ["select langs.lang, releases.desc
                                              from    langs, releases
                                              where  ( (langs.id = releases.langId)
                                                and    (lang = 'Clojure') ) "])
        result-3 (jdbc/query raw-db-spec ["select l.lang, r.desc
                                            from    langs as l, releases as r
                                            where  ( (l.id = r.langId)
                                              and    (l.lang = 'Clojure') ) "])
        ]
    (nl)
    (spyx-pretty result-0)
    ;(sets= result-0 result-1 result-2 result-3  ; #todo use this
    ;       [{:lang "Clojure", :desc "1.8"}
    ;        {:lang "Clojure", :desc "1.9"}
    ;        {:lang "Clojure", :desc "ancients"}])
    (is (= (set [{:lang "Clojure", :desc "1.8"}
                 {:lang "Clojure", :desc "1.9"}
                 {:lang "Clojure", :desc "ancients"}])
           (set result-0)
           (set result-1)
           (set result-2)
           (set result-3))) ))

Collectives™ on Stack Overflow

Connecting clojure to Postgresql db

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related