How to delete or create a database from clojure.java.jdbc?

Question

I would like to create / delete 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 the transaction, and apparently you cannot delete or create the databases inside the transaction. Any ideas?

Thanks!

Answer 1

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

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

Answer 2

Transactionless functionality has been translated into db-do-commands .

Now this slightly simpler version works:

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

If you do not specify false as the second argument, it will not work as it will try to start the transaction.

Answer 3

With newer clojure versions, the proposed approach no longer works. I managed 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") 

Answer 4

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-command conn false (line name "CREATE DATABASE"))))

 (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) ))) 

Typically, you need to connect without providing a database or connecting to another database (not the one you delete)

It will be converted 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) )))