CODEWITHSUNDEEP
postgresqlclojure
prismofeverything
prismofeverything

Reputation: 9049

How do I drop or create a database from clojure.java.jdbc?

I would like to create/drop a database from clojure.java.jdbc. This fails:

(require '[clojure.java.jdbc :as sql])

(def db
  {:classname "org.postgresql.Driver"
   :subprotocol "postgresql"
   :subname "//localhost/postgres"
   :user "postgres"})

(defn drop-database [name]
  (sql/do-commands (str "drop database " name)))

(sql/with-connection db 
  (drop-database "db_name"))

because do-commands starts a transaction, and apparently you can't drop or create databases inside a transaction. Any ideas?

Thanks!

Upvotes: 9

Views: 2476

Answers (4)

Indrajith Indraprastham
Indrajith Indraprastham

Reputation: 1348

This is the only solution that worked for me

(def datasource-options {:auto-commit        true
                         :read-only          false
                         :connection-timeout 30000
                         :validation-timeout 5000
                         :idle-timeout       600000
                         :max-lifetime       1800000
                         :minimum-idle       10
                         ;; :maximum-pool-size  10
                         :pool-name          "db-pool"
                         :adapter            (:database-adapter env)
                         :username           (:database-username env)
                         :password           (:database-password env)
                         :database-name      (:database-name env)
                         :server-name        (:database-host env)
                         :port-number        (:database-port env)
                         :register-mbeans    false})

(defonce datasource
  (delay (make-datasource datasource-options)))

(defn db-jdbc-uri [& {:as args}]
  (let [datasource-options (merge datasource-options args)]
    (format "jdbc:%s://%s:%s/%s?user=%s&password=%s"
            (datasource-options :adapter) (datasource-options :server-name)
            (datasource-options :port-number) (datasource-options :database-name)
            (datasource-options :username) (datasource-options :password))))

(defn create-database [name] (println {:connection-uri (db-jdbc-uri :database-name "")}) (jdbc/with-db-connection [conn {:connection-uri (db-jdbc-uri :database-name "")}] (jdbc/db-do-commands conn false (str "CREATE DATABASE " name))))

(defn drop-database [name]
  (jdbc/with-db-connection [conn {:connection-uri (db-jdbc-uri :database-name "")}]
      (jdbc/db-do-commands conn false (str "DROP DATABASE " name) )))

Basically you need to connect without providing a database or connecting to a different database (not the one you are deleting)

This will get transilated to this code.

  (defn create-database [name]
  (jdbc/with-db-connection [conn {:connection-uri "jdbc:postgresql://localhost/postgres?user=<name>&password=<pass>"}]
    (jdbc/db-do-commands conn false (str "CREATE DATABASE " name) )))

Upvotes: 0

amoe
amoe

Reputation: 4569

The transactionless execution functionality was rolled into db-do-commands.

Now this slightly simpler version is working:

(jdbc/db-do-commands postgres-db false "CREATE DATABASE foo")

If you don't specify false as the second argument, it won't work as it will attempt to start a transaction.

Upvotes: 3

user3759920
user3759920

Reputation: 9

With newer clojure versions, the suggested approach no longer works. I was successful with this function:

(defn exec-db-command [db command]
  (jdbc/with-db-connection [conn db]
    (with-open [s (.createStatement (:connection conn))]
      (.executeUpdate s command))))

(exec-db-command db "create database foo")

Upvotes: 0

Brian Carper
Brian Carper

Reputation: 72926

Take the source for do-commands (here) and remove the call to transaction:

(defn drop-database [name]
  (sql/with-connection db
    (with-open [s (.createStatement (sql/connection))]
      (.addBatch s (str "drop database " name))
      (seq (.executeBatch s)))))

Upvotes: 5

Related Questions