Querying a Postgres Database - Clojure

Question

I am trying to query a postgres database that I have set up locally on my machine.

I have created a file

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

(def database
  {:classname "com.postgres.jdbc.Driver"
   :subprotocol "postgres"
   :subname "mydb" ; In the guide this was //127.0.0.1:3306/mydb. Is the first part my computer IP address?
   :user "admin"
   :password "secret"})

If I then try to query the database using (in the REPL)

(jdbc/query database ["SELECT * FROM table"])

I get the error ConnectException Connection refused java.net.PlainSocketImpl.socketConnect (PlainSocketImpl.java:-2)

Note I expect the database to be empty as I haven't entered any information into the database

What mistake have I made when defining my database?

To insert a user is this correct:

(defn register-user! [db name]
  (jdbc/insert! db :user {:name name}))

Answer 1

clojure.java.jdbc documentation site doesn't cover all the possibilities and doesn't have too much detail.

I would point you to the doc of get-connection function which is quite extensive:

(...) db-spec is usually a map containing connection
  parameters but can also be a URI or a String. The various possibilities are described
  below:
  DriverManager (preferred):
    :dbtype      (required) a String, the type of the database (the jdbc subprotocol)
    :dbname      (required) a String, the name of the database
    :host        (optional) a String, the host name/IP of the database
                            (defaults to 127.0.0.1)
    :port        (optional) a Long, the port of the database
                            (defaults to 3306 for mysql, 1433 for mssql/jtds, else nil)
    (others)     (optional) passed to the driver as properties.
  Raw:
    :connection-uri (required) a String
                 Passed directly to DriverManager/getConnection
  Other formats accepted:
  Existing Connection:
    :connection  (required) an existing open connection that can be used
                 but cannot be closed (only the parent connection can be closed)
  DriverManager (alternative / legacy style):
    :subprotocol (required) a String, the jdbc subprotocol
    :subname     (required) a String, the jdbc subname
    :classname   (optional) a String, the jdbc driver class name
    (others)     (optional) passed to the driver as properties.
  Factory:
    :factory     (required) a function of one argument, a map of params
    (others)     (optional) passed to the factory function in a map
  DataSource:
    :datasource  (required) a javax.sql.DataSource
    :username    (optional) a String
    :user        (optional) a String - an alternate alias for :username
                            (added after 0.3.0-beta2 for consistency JDBC-74)
    :password    (optional) a String, required if :username is supplied
  JNDI:
    :name        (required) a String or javax.naming.Name
    :environment (optional) a java.util.Map
  java.net.URI:
    Parsed JDBC connection string (see java.lang.String format next)
  java.lang.String:
    subprotocol://user:password@host:post/subname
                 An optional prefix of jdbc: is allowed."

The latest clojure.java.jdbc version also includes Clojure specs for db-spec map.

In your case it could be:

{:dbtype "postgresql")
 :dbname "mydb"
 :host "127.0.0.1"
 :port 5432
 :user "admin"
 :password "secret"}

Answer 2

Your configuration is fine, and works for me when I use my own local postgres db, though the config given in the other answer is cleaner IMO.

The best approach when doing this kind of thing is to be able to talk to the database using a third party client, before trying to do so programatically.

Assuming you're on a *nix OS, you should be able to use psql to administer the database. Create your role, assign a password, create a database, create a table, and insert a row or two into the table. In the postgres config, turn off ssl, to keep it simple until you get it working, then add it if you need it.

The hardest part is simply getting the postgres config correct. Once you have verified access and use with psql, then the clojure part is trivial.

FWIW, working and up-to-date versions of dependencies you should have are:

[org.clojure/java.jdbc "0.7.0-alpha1"]
[postgresql/postgresql "9.3-1102.jdbc41"]