Back
get-connection (clj)
(source)function
(get-connection spec)
(get-connection spec opts)
(get-connection spec user password)
(get-connection spec user password opts)
Given some sort of specification of a database, return a new `Connection`.
In general, this should be used via `with-open`:
```clojure
(with-open [con (get-connection spec opts)]
(run-some-ops con))
```
If you call `get-connection` on a `DataSource`, it just calls `.getConnection`
and applies the `:auto-commit` and/or `:read-only` options, if provided.
If you call `get-connection` on anything else, it will call `get-datasource`
first to try to get a `DataSource`, and then call `get-connection` on that.
If you want different per-connection username/password values, you can
either put `:user` and `:password` into the `opts` hash map or pass them
as positional arguments.
Examples
next-jdbc
(ns next.jdbc-test
"Basic tests for the primary API of `next.jdbc`."
(:require [clojure.core.reducers :as r]
[clojure.string :as str]
[clojure.test :refer [deftest is testing use-fixtures]]
[next.jdbc :as jdbc]
[next.jdbc.connection :as c]
[next.jdbc.test-fixtures
:refer [with-test-db db ds column
default-options stored-proc?
derby? hsqldb? jtds? mssql? mysql? postgres? sqlite?]]
[next.jdbc.prepare :as prep]
[next.jdbc.result-set :as rs]
[next.jdbc.specs :as specs]
[next.jdbc.types :as types])
(:import (com.zaxxer.hikari HikariDataSource)
(com.mchange.v2.c3p0 ComboPooledDataSource PooledDataSource)
(java.sql ResultSet ResultSetMetaData)))
(deftest spec-tests
(let [db-spec {:dbtype "h2:mem" :dbname "clojure_test"}]
;; some sanity checks on instrumented function calls:
(jdbc/get-datasource db-spec)
(jdbc/get-connection db-spec)
;; and again with options:
(let [db-spec' (jdbc/with-options db-spec {})]
(jdbc/get-datasource db-spec')
(jdbc/get-connection db-spec'))))
(deftest basic-tests
;; use ds-opts instead of (ds) anywhere you want default options applied:
(let [ds-opts (jdbc/with-options (ds) (default-options))]
(testing "plan"
(is (= "Apple"
(reduce (fn [_ row] (reduced (:name row)))
nil
(jdbc/plan
ds-opts
["select * from fruit where appearance = ?" "red"]))))
(is (= "Banana"
(reduce (fn [_ row] (reduced (:no-such-column row "Banana")))
nil
(jdbc/plan
ds-opts
["select * from fruit where appearance = ?" "red"])))))
(testing "execute-one!"
(is (nil? (jdbc/execute-one!
(ds)
["select * from fruit where appearance = ?" "neon-green"])))
(is (= "Apple" ((column :FRUIT/NAME)
(jdbc/execute-one!
ds-opts
["select * from fruit where appearance = ?" "red"]))))
(is (= "red" (:fruit/looks-like
(jdbc/execute-one!
ds-opts
["select appearance as looks_like from fruit where id = ?" 1]
jdbc/snake-kebab-opts))))
(let [ds' (jdbc/with-options ds-opts jdbc/snake-kebab-opts)]
(is (= "red" (:fruit/looks-like
(jdbc/execute-one!
ds'
["select appearance as looks_like from fruit where id = ?" 1])))))
(jdbc/with-transaction+options [ds' (jdbc/with-options ds-opts jdbc/snake-kebab-opts)]
(is (= (merge (default-options) jdbc/snake-kebab-opts)
(:options ds')))
(is (= "red" (:fruit/looks-like
(jdbc/execute-one!
ds'
["select appearance as looks_like from fruit where id = ?" 1])))))
(is (= "red" (:looks-like
(jdbc/execute-one!
ds-opts
["select appearance as looks_like from fruit where id = ?" 1]
jdbc/unqualified-snake-kebab-opts)))))
(testing "execute!"
(let [rs (jdbc/execute!
ds-opts
["select * from fruit where appearance = ?" "neon-green"])]
(is (vector? rs))
(is (= [] rs)))
(let [rs (jdbc/execute!
ds-opts
["select * from fruit where appearance = ?" "red"])]
(is (= 1 (count rs)))
(is (= 1 ((column :FRUIT/ID) (first rs)))))
(let [rs (jdbc/execute!
ds-opts
["select * from fruit order by id"]
{:builder-fn rs/as-maps})]
(is (every? map? rs))
(is (every? meta rs))
(is (= 4 (count rs)))
(is (= 1 ((column :FRUIT/ID) (first rs))))
(is (= 4 ((column :FRUIT/ID) (last rs)))))
(let [rs (jdbc/execute!
ds-opts
["select * from fruit order by id"]
{:builder-fn rs/as-arrays})]
(is (every? vector? rs))
(is (= 5 (count rs)))
(is (every? #(= 5 (count %)) rs))
;; columns come first
(is (every? qualified-keyword? (first rs)))
;; :FRUIT/ID should be first column
(is (= (column :FRUIT/ID) (ffirst rs)))
;; and all its corresponding values should be ints
(is (every? int? (map first (rest rs))))
(is (every? string? (map second (rest rs))))))
(testing "execute! with adapter"
(let [rs (jdbc/execute! ; test again, with adapter and lower columns
ds-opts
["select * from fruit order by id"]
{:builder-fn (rs/as-arrays-adapter
rs/as-lower-arrays
(fn [^ResultSet rs _ ^Integer i]
(.getObject rs i)))})]
(is (every? vector? rs))
(is (= 5 (count rs)))
(is (every? #(= 5 (count %)) rs))
;; columns come first
(is (every? qualified-keyword? (first rs)))
;; :fruit/id should be first column
(is (= :fruit/id (ffirst rs)))
;; and all its corresponding values should be ints
(is (every? int? (map first (rest rs))))
(is (every? string? (map second (rest rs))))))
(testing "execute! with unqualified"
(let [rs (jdbc/execute!
(ds)
["select * from fruit order by id"]
{:builder-fn rs/as-unqualified-maps})]
(is (every? map? rs))
(is (every? meta rs))
(is (= 4 (count rs)))
(is (= 1 ((column :ID) (first rs))))
(is (= 4 ((column :ID) (last rs)))))
(let [rs (jdbc/execute!
ds-opts
["select * from fruit order by id"]
{:builder-fn rs/as-unqualified-arrays})]
(is (every? vector? rs))
(is (= 5 (count rs)))
(is (every? #(= 5 (count %)) rs))
;; columns come first
(is (every? simple-keyword? (first rs)))
;; :ID should be first column
(is (= (column :ID) (ffirst rs)))
;; and all its corresponding values should be ints
(is (every? int? (map first (rest rs))))
(is (every? string? (map second (rest rs))))))
(testing "execute! with :max-rows / :maxRows"
(let [rs (jdbc/execute!
ds-opts
["select * from fruit order by id"]
{:max-rows 2})]
(is (every? map? rs))
(is (every? meta rs))
(is (= 2 (count rs)))
(is (= 1 ((column :FRUIT/ID) (first rs))))
(is (= 2 ((column :FRUIT/ID) (last rs)))))
(let [rs (jdbc/execute!
ds-opts
["select * from fruit order by id"]
{:statement {:maxRows 2}})]
(is (every? map? rs))
(is (every? meta rs))
(is (= 2 (count rs)))
(is (= 1 ((column :FRUIT/ID) (first rs))))
(is (= 2 ((column :FRUIT/ID) (last rs)))))))
(testing "prepare"
;; default options do not flow over get-connection
(let [rs (with-open [con (jdbc/get-connection (ds))
ps (jdbc/prepare
con
["select * from fruit order by id"]
(default-options))]
(jdbc/execute! ps))]
(is (every? map? rs))
(is (every? meta rs))
(is (= 4 (count rs)))
(is (= 1 ((column :FRUIT/ID) (first rs))))
(is (= 4 ((column :FRUIT/ID) (last rs)))))
;; default options do not flow over get-connection
(let [rs (with-open [con (jdbc/get-connection (ds))
ps (jdbc/prepare
con
["select * from fruit where id = ?"]
(default-options))]
(jdbc/execute! (prep/set-parameters ps [4]) nil {}))]
(is (every? map? rs))
(is (every? meta rs))
(is (= 1 (count rs)))
(is (= 4 ((column :FRUIT/ID) (first rs))))))
(testing "statement"
;; default options do not flow over get-connection
(let [rs (with-open [con (jdbc/get-connection (ds))]
(jdbc/execute! (prep/statement con (default-options))
["select * from fruit order by id"]))]
(is (every? map? rs))
(is (every? meta rs))
(is (= 4 (count rs)))
(is (= 1 ((column :FRUIT/ID) (first rs))))
(is (= 4 ((column :FRUIT/ID) (last rs)))))
;; default options do not flow over get-connection
(let [rs (with-open [con (jdbc/get-connection (ds))]
(jdbc/execute! (prep/statement con (default-options))
["select * from fruit where id = 4"]))]
(is (every? map? rs))
(is (every? meta rs))
(is (= 1 (count rs)))
(is (= 4 ((column :FRUIT/ID) (first rs))))))
(testing "transact"
(is (= [{:next.jdbc/update-count 1}]
(jdbc/transact (ds)
(fn [t] (jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"]))
{:rollback-only true})))
(is (= 4 (count (jdbc/execute! (ds) ["select * from fruit"])))))
(testing "with-transaction rollback-only"
(is (not (jdbc/active-tx?)) "should not be in a transaction")
(is (= [{:next.jdbc/update-count 1}]
(jdbc/with-transaction [t (ds) {:rollback-only true}]
(is (jdbc/active-tx?) "should be in a transaction")
(jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"]))))
(is (= 4 (count (jdbc/execute! (ds) ["select * from fruit"]))))
(is (not (jdbc/active-tx?)) "should not be in a transaction")
(with-open [con (jdbc/get-connection (ds))]
(let [ac (.getAutoCommit con)]
(is (= [{:next.jdbc/update-count 1}]
(jdbc/with-transaction [t con {:rollback-only true}]
(is (jdbc/active-tx?) "should be in a transaction")
(jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"]))))
(is (= 4 (count (jdbc/execute! con ["select * from fruit"]))))
(is (= ac (.getAutoCommit con))))))
(testing "with-transaction exception"
(is (thrown? Throwable
(jdbc/with-transaction [t (ds)]
(jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"])
(is (jdbc/active-tx?) "should be in a transaction")
(throw (ex-info "abort" {})))))
(is (= 4 (count (jdbc/execute! (ds) ["select * from fruit"]))))
(is (not (jdbc/active-tx?)) "should not be in a transaction")
(with-open [con (jdbc/get-connection (ds))]
(let [ac (.getAutoCommit con)]
(is (thrown? Throwable
(jdbc/with-transaction [t con]
(jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"])
(is (jdbc/active-tx?) "should be in a transaction")
(throw (ex-info "abort" {})))))
(is (= 4 (count (jdbc/execute! con ["select * from fruit"]))))
(is (= ac (.getAutoCommit con))))))
(testing "with-transaction call rollback"
(is (= [{:next.jdbc/update-count 1}]
(jdbc/with-transaction [t (ds)]
(let [result (jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"])]
(.rollback t)
;; still in a next.jdbc TX even tho' we rolled back!
(is (jdbc/active-tx?) "should be in a transaction")
result))))
(is (= 4 (count (jdbc/execute! (ds) ["select * from fruit"]))))
(is (not (jdbc/active-tx?)) "should not be in a transaction")
(with-open [con (jdbc/get-connection (ds))]
(let [ac (.getAutoCommit con)]
(is (= [{:next.jdbc/update-count 1}]
(jdbc/with-transaction [t con]
(let [result (jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"])]
(.rollback t)
result))))
(is (= 4 (count (jdbc/execute! con ["select * from fruit"]))))
(is (= ac (.getAutoCommit con))))))
(testing "with-transaction with unnamed save point"
(is (= [{:next.jdbc/update-count 1}]
(jdbc/with-transaction [t (ds)]
(let [save-point (.setSavepoint t)
result (jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"])]
(.rollback t save-point)
;; still in a next.jdbc TX even tho' we rolled back to a save point!
(is (jdbc/active-tx?) "should be in a transaction")
result))))
(is (= 4 (count (jdbc/execute! (ds) ["select * from fruit"]))))
(is (not (jdbc/active-tx?)) "should not be in a transaction")
(with-open [con (jdbc/get-connection (ds))]
(let [ac (.getAutoCommit con)]
(is (= [{:next.jdbc/update-count 1}]
(jdbc/with-transaction [t con]
(let [save-point (.setSavepoint t)
result (jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"])]
(.rollback t save-point)
result))))
(is (= 4 (count (jdbc/execute! con ["select * from fruit"]))))
(is (= ac (.getAutoCommit con))))))
(testing "with-transaction with named save point"
(is (= [{:next.jdbc/update-count 1}]
(jdbc/with-transaction [t (ds)]
(let [save-point (.setSavepoint t (name (gensym)))
result (jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"])]
(.rollback t save-point)
result))))
(is (= 4 (count (jdbc/execute! (ds) ["select * from fruit"]))))
(with-open [con (jdbc/get-connection (ds))]
(let [ac (.getAutoCommit con)]
(is (= [{:next.jdbc/update-count 1}]
(jdbc/with-transaction [t con]
(let [save-point (.setSavepoint t (name (gensym)))
result (jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"])]
(.rollback t save-point)
result))))
(is (= 4 (count (jdbc/execute! con ["select * from fruit"]))))
(is (= ac (.getAutoCommit con)))))))
(deftest issue-146
;; since we use an embedded PostgreSQL data source, we skip this:
(when-not (or (postgres?)
;; and now we skip MS SQL because we can't use the db-spec
;; we'd need to build the jdbcUrl with encryption turned off:
(and (mssql?) (not (jtds?))))
(testing "Hikari and SavePoints"
(with-open [^HikariDataSource ds (c/->pool HikariDataSource
(let [db (db)]
(cond-> db
;; jTDS does not support isValid():
(jtds?)
(assoc :connectionTestQuery "SELECT 1")
;; HikariCP needs username, not user:
(contains? db :user)
(assoc :username (:user db)))))]
(testing "with-transaction with unnamed save point"
(is (= [{:next.jdbc/update-count 1}]
(jdbc/with-transaction [t ds]
(let [save-point (.setSavepoint t)
result (jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"])]
(.rollback t save-point)
result))))
(is (= 4 (count (jdbc/execute! ds ["select * from fruit"]))))
(with-open [con (jdbc/get-connection ds)]
(let [ac (.getAutoCommit con)]
(is (= [{:next.jdbc/update-count 1}]
(jdbc/with-transaction [t con]
(let [save-point (.setSavepoint t)
result (jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"])]
(.rollback t save-point)
result))))
(is (= 4 (count (jdbc/execute! con ["select * from fruit"]))))
(is (= ac (.getAutoCommit con))))))
(testing "with-transaction with named save point"
(is (= [{:next.jdbc/update-count 1}]
(jdbc/with-transaction [t ds]
(let [save-point (.setSavepoint t (name (gensym)))
result (jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"])]
(.rollback t save-point)
result))))
(is (= 4 (count (jdbc/execute! ds ["select * from fruit"]))))
(with-open [con (jdbc/get-connection ds)]
(let [ac (.getAutoCommit con)]
(is (= [{:next.jdbc/update-count 1}]
(jdbc/with-transaction [t con]
(let [save-point (.setSavepoint t (name (gensym)))
result (jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"])]
(.rollback t save-point)
result))))
(is (= 4 (count (jdbc/execute! con ["select * from fruit"]))))
(is (= ac (.getAutoCommit con))))))))
(testing "c3p0 and SavePoints"
(with-open [^PooledDataSource ds (c/->pool ComboPooledDataSource (db))]
(testing "with-transaction with unnamed save point"
(is (= [{:next.jdbc/update-count 1}]
(jdbc/with-transaction [t ds]
(let [save-point (.setSavepoint t)
result (jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"])]
(.rollback t save-point)
result))))
(is (= 4 (count (jdbc/execute! ds ["select * from fruit"]))))
(with-open [con (jdbc/get-connection ds)]
(let [ac (.getAutoCommit con)]
(is (= [{:next.jdbc/update-count 1}]
(jdbc/with-transaction [t con]
(let [save-point (.setSavepoint t)
result (jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"])]
(.rollback t save-point)
result))))
(is (= 4 (count (jdbc/execute! con ["select * from fruit"]))))
(is (= ac (.getAutoCommit con))))))
(testing "with-transaction with named save point"
(is (= [{:next.jdbc/update-count 1}]
(jdbc/with-transaction [t ds]
(let [save-point (.setSavepoint t (name (gensym)))
result (jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"])]
(.rollback t save-point)
result))))
(is (= 4 (count (jdbc/execute! ds ["select * from fruit"]))))
(with-open [con (jdbc/get-connection ds)]
(let [ac (.getAutoCommit con)]
(is (= [{:next.jdbc/update-count 1}]
(jdbc/with-transaction [t con]
(let [save-point (.setSavepoint t (name (gensym)))
result (jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"])]
(.rollback t save-point)
result))))
(is (= 4 (count (jdbc/execute! con ["select * from fruit"]))))
(is (= ac (.getAutoCommit con))))))))))
(deftest folding-test
(jdbc/execute-one! (ds) ["delete from fruit"])
(with-open [con (jdbc/get-connection (ds))
ps (jdbc/prepare con ["insert into fruit(name) values (?)"])]
(jdbc/execute-batch! ps (mapv #(vector (str "Fruit-" %)) (range 1 1001))))
(testing "foldable result set"
(testing "from a Connection"
(let [result
(with-open [con (jdbc/get-connection (ds))]
(r/foldcat
(r/map (column :FRUIT/NAME)
(jdbc/plan con ["select * from fruit order by id"]
(default-options)))))]
(is (= 1000 (count result)))
(is (= "Fruit-1" (first result)))
(is (= "Fruit-1000" (last result)))))
(testing "from a DataSource"
(doseq [n [2 3 4 5 100 300 500 700 900 1000 1100]]
(testing (str "folding with n = " n)
(let [result
(try
(r/fold n r/cat r/append!
(r/map (column :FRUIT/NAME)
(jdbc/plan (ds) ["select * from fruit order by id"]
(default-options))))
(catch java.util.concurrent.RejectedExecutionException _
[]))]
(is (= 1000 (count result)))
(is (= "Fruit-1" (first result)))
(is (= "Fruit-1000" (last result)))))))
(testing "from a PreparedStatement"
(let [result
(with-open [con (jdbc/get-connection (ds))
stmt (jdbc/prepare con
["select * from fruit order by id"]
(default-options))]
(r/foldcat
(r/map (column :FRUIT/NAME)
(jdbc/plan stmt nil (default-options)))))]
(is (= 1000 (count result)))
(is (= "Fruit-1" (first result)))
(is (= "Fruit-1000" (last result)))))
(testing "from a Statement"
(let [result
(with-open [con (jdbc/get-connection (ds))
stmt (prep/statement con (default-options))]
(r/foldcat
(r/map (column :FRUIT/NAME)
(jdbc/plan stmt ["select * from fruit order by id"]
(default-options)))))]
(is (= 1000 (count result)))
(is (= "Fruit-1" (first result)))
(is (= "Fruit-1000" (last result)))))))
(deftest connection-tests
(testing "datasource via jdbcUrl"
(when-not (postgres?)
(let [[url etc] (#'c/spec->url+etc (db))
ds (jdbc/get-datasource (assoc etc :jdbcUrl url))]
(cond (derby?) (is (= {:create true} etc))
(mssql?) (is (= (cond-> #{:user :password}
(not (jtds?))
(conj :encrypt :trustServerCertificate))
(set (keys etc))))
(mysql?) (is (= #{:user :password :useSSL :allowMultiQueries}
(disj (set (keys etc)) :disableMariaDbDriver)))
:else (is (= {} etc)))
(is (instance? javax.sql.DataSource ds))
(is (str/index-of (pr-str ds) (str "jdbc:"
(cond (jtds?)
"jtds:sqlserver"
(mssql?)
"sqlserver"
:else
(:dbtype (db))))))
;; checks get-datasource on a DataSource is identity
(is (identical? ds (jdbc/get-datasource ds)))
(with-open [con (jdbc/get-connection ds {})]
(is (instance? java.sql.Connection con)))))))
(deftest issue-204
(testing "against a Connection"
(is (seq (with-open [con (jdbc/get-connection (ds))]
(jdbc/on-connection
[x con]
(jdbc/execute! x ["select * from fruit"]))))))
(testing "against a wrapped Connection"
(is (seq (with-open [con (jdbc/get-connection (ds))]
(jdbc/on-connection
[x (jdbc/with-options con {})]
(jdbc/execute! x ["select * from fruit"]))))))
(testing "against a wrapped Datasource"
(is (seq (jdbc/on-connection
[x (jdbc/with-options (ds) {})]
(jdbc/execute! x ["select * from fruit"])))))
(testing "against a Datasource"
(is (seq (jdbc/on-connection
[x (ds)]
(jdbc/execute! x ["select * from fruit"]))))))
(deftest issue-256
(testing "against a Connection"
(is (seq (with-open [con (jdbc/get-connection (ds))]
(jdbc/on-connection+options
[x con] ; raw connection stays raw
(is (instance? java.sql.Connection x))
(jdbc/execute! x ["select * from fruit"]))))))
(testing "against a wrapped Connection"
(is (seq (with-open [con (jdbc/get-connection (ds))]
(jdbc/on-connection+options
[x (jdbc/with-options con {:test-option 42})]
;; ensure we get the same wrapped connection
(is (instance? java.sql.Connection (:connectable x)))
(is (= {:test-option 42} (:options x)))
(jdbc/execute! x ["select * from fruit"]))))))
(testing "against a wrapped Datasource"
(is (seq (jdbc/on-connection+options
[x (jdbc/with-options (ds) {:test-option 42})]
;; ensure we get a wrapped connection
(is (instance? java.sql.Connection (:connectable x)))
(is (= {:test-option 42} (:options x)))
(jdbc/execute! x ["select * from fruit"])))))
(testing "against a Datasource"
(is (seq (jdbc/on-connection+options
[x (ds)] ; unwrapped datasource has no options
;; ensure we get a wrapped connection (empty options)
(is (instance? java.sql.Connection (:connectable x)))
(is (= {} (:options x)))
(jdbc/execute! x ["select * from fruit"]))))))
next-jdbc
At some point, the datasource/connection tests should probably be extended
to accept EDN specs from an external source (environment variables?)."
(:require [clojure.string :as str]
[clojure.test :refer [deftest is testing use-fixtures]]
[next.jdbc.connection :as c]
[next.jdbc.protocols :as p]
[next.jdbc.specs :as specs]
[next.jdbc.test-fixtures :refer [with-test-db db]]))
(deftest test-uri-strings
(testing "datasource via String"
(let [db-spec (db)
db-spec (if (= "embedded-postgres" (:dbtype db-spec))
(assoc db-spec :dbtype "postgresql")
db-spec)
[url etc] (#'c/spec->url+etc db-spec)
{:keys [user password]} etc
etc (dissoc etc :user :password)
uri (-> url
;; strip jdbc: prefix for fun
(str/replace #"^jdbc:" "")
(str/replace #";" "?") ; for SQL Server tests
(str/replace #":sqlserver" "") ; for SQL Server tests
(cond-> (and user password)
(str/replace #"://" (str "://" user ":" password "@"))))
ds (p/get-datasource (assoc etc :jdbcUrl uri))]
(when (and user password)
(with-open [con (p/get-connection ds {})]
(is (instance? java.sql.Connection con)))))))
next-jdbc
What's left to be tested:
* ReadableColumn protocol extension point"
(:require [clojure.core.protocols :as core-p]
[clojure.datafy :as d]
[clojure.string :as str]
[clojure.test :refer [deftest is testing use-fixtures]]
[next.jdbc.protocols :as p]
[next.jdbc.result-set :as rs]
[next.jdbc.specs :as specs]
[next.jdbc.test-fixtures :refer [with-test-db ds column
default-options
derby? mssql? mysql? postgres?]])
(:import (java.sql ResultSet ResultSetMetaData)))
(deftest metadata-result-set
(let [metadata (with-open [con (p/get-connection (ds) {})]
(-> (.getMetaData con)
(.getTables nil nil nil (into-array ["TABLE" "VIEW"]))
(rs/datafiable-result-set (ds) {})))]
(is (vector? metadata))
(is (map? (first metadata)))
;; we should find :something/table_name with a value of "fruit"
;; may be upper/lower-case, could have any qualifier
(is (some (fn [row]
(some #(and (= "table_name" (-> % key name str/lower-case))
(= "fruit" (-> % val name str/lower-case)))
row))
metadata))))
(deftest clob-reading
(when-not (or (mssql?) (mysql?) (postgres?)) ; no clob in these
(with-open [con (p/get-connection (ds) {})]
(try
(p/-execute-one con ["DROP TABLE CLOBBER"] {})
(catch Exception _))
(p/-execute-one con [(str "
CREATE TABLE CLOBBER (
ID INTEGER,
STUFF CLOB
)")]
{})
(p/-execute-one con
[(str "insert into clobber (id, stuff)"
"values (?,?), (?,?)")
1 "This is some long string"
2 "This is another long string"]
{})
(is (= "This is some long string"
(-> (p/-execute-all con
["select * from clobber where id = ?" 1]
{:builder-fn (rs/as-maps-adapter
rs/as-unqualified-lower-maps
rs/clob-column-reader)})
(first)
:stuff))))))
next-jdbc
(ns next.jdbc.datafy-test
"Tests for the datafy extensions over JDBC types."
(:require [clojure.datafy :as d]
[clojure.set :as set]
[clojure.test :refer [deftest is testing use-fixtures]]
[next.jdbc :as jdbc]
[next.jdbc.datafy]
[next.jdbc.result-set :as rs]
[next.jdbc.specs :as specs]
[next.jdbc.test-fixtures
:refer [with-test-db db ds
derby? jtds? mysql? postgres? sqlite?]]))
(deftest connection-datafy-tests
(testing "connection datafication"
(with-open [con (jdbc/get-connection (ds))]
(let [reference-keys (cond-> basic-connection-keys
(derby?) (-> (disj :networkTimeout)
(conj :networkTimeout/exception))
(jtds?) (-> (disj :clientInfo :networkTimeout :schema)
(conj :clientInfo/exception
:networkTimeout/exception
:schema/exception)))
data (set (keys (d/datafy con)))]
(when-let [diff (seq (set/difference data reference-keys))]
(println (format "%6s :%-10s %s"
(:dbtype (db)) "connection" (str (sort diff)))))
(is (= reference-keys
(set/intersection reference-keys data)))))))
(deftest database-metadata-datafy-tests
(testing "database metadata datafication"
(with-open [con (jdbc/get-connection (ds))]
(let [reference-keys (cond-> basic-database-metadata-keys
(jtds?) (-> (disj :clientInfoProperties :rowIdLifetime)
(conj :clientInfoProperties/exception
:rowIdLifetime/exception))
(postgres?) (-> (disj :rowIdLifetime)
(conj :rowIdLifetime/exception))
(sqlite?) (-> (disj :clientInfoProperties :rowIdLifetime)
(conj :clientInfoProperties/exception
:rowIdLifetime/exception)))
data (set (keys (d/datafy (.getMetaData con))))]
(when-let [diff (seq (set/difference data reference-keys))]
(println (format "%6s :%-10s %s"
(:dbtype (db)) "db-meta" (str (sort diff)))))
(is (= reference-keys
(set/intersection reference-keys data))))))
(testing "nav to catalogs yields object"
(with-open [con (jdbc/get-connection (ds))]
(let [data (d/datafy (.getMetaData con))]
(doseq [k (cond-> #{:catalogs :clientInfoProperties :schemas :tableTypes :typeInfo}
(jtds?) (disj :clientInfoProperties)
(sqlite?) (disj :clientInfoProperties))]
(let [rs (d/nav data k nil)]
(is (vector? rs))
(is (every? map? rs))))))))
(comment
(def con (jdbc/get-connection (ds)))
(rs/datafiable-result-set (.getTables (.getMetaData con) nil nil nil nil) con {})
(def ps (jdbc/prepare con ["SELECT * FROM fruit WHERE grade > ?"]))
(require '[next.jdbc.prepare :as prep])
(prep/set-parameters ps [30])
(.execute ps)
(.getResultSet ps)
(.close ps)
(.close con))
next-jdbc
(ns next.jdbc.test-fixtures
"Multi-database testing fixtures."
(:require [clojure.string :as str]
[next.jdbc :as jdbc]
[next.jdbc.prepare :as prep]
[next.jdbc.sql :as sql])
(:import (io.zonky.test.db.postgres.embedded EmbeddedPostgres)))
(defn- do-commands
"Example from migration docs: this serves as a test for it."
[connectable commands]
(if (instance? java.sql.Connection connectable)
(with-open [stmt (prep/statement connectable)]
(run! #(.addBatch stmt %) commands)
(into [] (.executeBatch stmt)))
(with-open [conn (jdbc/get-connection connectable)]
(do-commands conn commands))))
Tests can reach into here and call ds (above) to get a DataSource for use
in test functions (that operate inside this fixture)."
[t]
(doseq [db test-db-specs]
(reset! test-db-spec db)
(if (= "embedded-postgres" (:dbtype db))
(reset! test-datasource
(.getPostgresDatabase ^EmbeddedPostgres @embedded-pg))
(reset! test-datasource (jdbc/get-datasource db)))
(let [fruit (if (mysql?) "fruit" "FRUIT") ; MySQL is case sensitive!
btest (if (mysql?) "btest" "BTEST")
auto-inc-pk
(cond (or (derby?) (hsqldb?))
(str "GENERATED ALWAYS AS IDENTITY"
" (START WITH 1, INCREMENT BY 1)"
" PRIMARY KEY")
(postgres?)
(str "GENERATED ALWAYS AS IDENTITY"
" PRIMARY KEY")
(mssql?)
"IDENTITY PRIMARY KEY"
(sqlite?)
"PRIMARY KEY AUTOINCREMENT"
:else
"AUTO_INCREMENT PRIMARY KEY")]
(with-open [con (jdbc/get-connection (ds))]
(when (stored-proc?)
(try
(jdbc/execute-one! con ["DROP PROCEDURE FRUITP"])
(catch Throwable _)))
(try
(do-commands con [(str "DROP TABLE " fruit)])
(catch Exception _))
(try
(do-commands con [(str "DROP TABLE " btest)])
(catch Exception _))
(when (postgres?)
(try
(do-commands con ["DROP TABLE LANG_TEST"])
(catch Exception _))
(try
(do-commands con ["DROP TYPE LANGUAGE"])
(catch Exception _))
(do-commands con ["CREATE TYPE LANGUAGE AS ENUM('en','fr','de')"])
(do-commands con ["
CREATE TABLE LANG_TEST (
LANG LANGUAGE NOT NULL
)"]))
(do-commands con [(str "
CREATE TABLE " fruit " (
ID INTEGER " auto-inc-pk ",
NAME VARCHAR(32),
APPEARANCE VARCHAR(32) DEFAULT NULL,
COST INT DEFAULT NULL,
GRADE REAL DEFAULT NULL
)")])
(let [created (atom false)]
;; MS SQL Server does not support bool/boolean:
(doseq [btype ["BOOL" "BOOLEAN" "BIT"]]
;; Derby does not support bit:
(doseq [bitty ["BIT" "SMALLINT"]]
(try
(when-not @created
(do-commands con [(str "
CREATE TABLE " btest " (
NAME VARCHAR(32),
IS_IT " btype ",
TWIDDLE " bitty "
)")])
(reset! created true))
(catch Throwable _))))
(when-not @created
(println (:dbtype db) "failed btest creation")
#_(throw (ex-info (str (:dbtype db) " has no boolean type?") {}))))
(when (stored-proc?)
(let [[begin end] (if (postgres?) ["$$" "$$"] ["BEGIN" "END"])]
(try
(do-commands con [(str "
CREATE PROCEDURE FRUITP" (cond (hsqldb?) "() READS SQL DATA DYNAMIC RESULT SETS 2 "
(mssql?) " AS "
(postgres?) "() LANGUAGE SQL AS "
:else "() ") "
" begin " " (if (hsqldb?)
(str "ATOMIC
DECLARE result1 CURSOR WITH RETURN FOR SELECT * FROM " fruit " WHERE COST < 90;
DECLARE result2 CURSOR WITH RETURN FOR SELECT * FROM " fruit " WHERE GRADE >= 90.0;
OPEN result1;
OPEN result2;")
(str "
SELECT * FROM " fruit " WHERE COST < 90;
SELECT * FROM " fruit " WHERE GRADE >= 90.0;")) "
" end "
")])
(catch Throwable t
(println 'procedure (:dbtype db) (ex-message t))))))
(sql/insert-multi! con :fruit
[:name :appearance :cost :grade]
[["Apple" "red" 59 nil]
["Banana" "yellow" nil 92.2]
["Peach" nil 139 90.0]
["Orange" "juicy" 89 88.6]]
{:return-keys false})
(t)))))
next-jdbc
At some point, the datasource/connection tests should probably be extended
to accept EDN specs from an external source (environment variables?)."
(:require [clojure.string :as str]
[clojure.test :refer [deftest is testing]]
[next.jdbc.connection :as c]
[next.jdbc.protocols :as p])
(:import (com.zaxxer.hikari HikariDataSource)
(com.mchange.v2.c3p0 ComboPooledDataSource PooledDataSource)))
(deftest test-get-connection
(doseq [db test-dbs]
(println 'test-get-connection (:dbtype db))
(testing "datasource via Associative"
(let [ds (p/get-datasource db)]
(is (instance? javax.sql.DataSource ds))
(is (str/index-of (pr-str ds) (str "jdbc:" (:dbtype db))))
;; checks get-datasource on a DataSource is identity
(is (identical? ds (p/get-datasource ds)))
(with-open [con (p/get-connection ds {})]
(is (instance? java.sql.Connection con)))))
(testing "datasource via String"
(let [[url _] (#'c/spec->url+etc db)
ds (p/get-datasource url)]
(is (instance? javax.sql.DataSource ds))
(is (str/index-of (pr-str ds) url))
(.setLoginTimeout ds 0)
(is (= 0 (.getLoginTimeout ds)))
(with-open [con (p/get-connection ds {})]
(is (instance? java.sql.Connection con)))))
(testing "datasource via jdbcUrl"
(let [[url etc] (#'c/spec->url+etc db)
ds (p/get-datasource (assoc etc :jdbcUrl url))]
(if (= "derby" (:dbtype db))
(is (= {:create true} etc))
(is (= {} etc)))
(is (instance? javax.sql.DataSource ds))
(is (str/index-of (pr-str ds) (str "jdbc:" (:dbtype db))))
;; checks get-datasource on a DataSource is identity
(is (identical? ds (p/get-datasource ds)))
(.setLoginTimeout ds 1)
(is (= 1 (.getLoginTimeout ds)))
(with-open [con (p/get-connection ds {})]
(is (instance? java.sql.Connection con)))))
(testing "datasource via HikariCP"
;; the type hint is only needed because we want to call .close
(with-open [^HikariDataSource ds (c/->pool HikariDataSource db)]
(is (instance? javax.sql.DataSource ds))
;; checks get-datasource on a DataSource is identity
(is (identical? ds (p/get-datasource ds)))
(with-open [con (p/get-connection ds {})]
(is (instance? java.sql.Connection con)))))
(testing "datasource via c3p0"
;; the type hint is only needed because we want to call .close
(with-open [^PooledDataSource ds (c/->pool ComboPooledDataSource db)]
(is (instance? javax.sql.DataSource ds))
;; checks get-datasource on a DataSource is identity
(is (identical? ds (p/get-datasource ds)))
(with-open [con (p/get-connection ds {})]
(is (instance? java.sql.Connection con)))))
(testing "connection via map (Object)"
(with-open [con (p/get-connection db {})]
(is (instance? java.sql.Connection con))))))
xtdb/xtdb
(ns xtdb.jdbc-startup-test
(:require [clojure.java.io :as io]
[clojure.test :as t]
[juxt.clojars-mirrors.nextjdbc.v1v2v674.next.jdbc :as jdbc]
[xtdb.api :as xt]
[xtdb.fixtures :as fix]
[xtdb.fixtures.jdbc :as fj])
(:import (clojure.lang ExceptionInfo)
(java.util.concurrent ExecutionException Executors TimeUnit)
(java.util.concurrent.locks ReadWriteLock ReentrantReadWriteLock)))
(t/deftest startup-race-2776
;; test concurrent startups do not fail due to races in the schema setup
(fix/with-tmp-dirs #{db-dir}
(doseq [[dialect-kw {:keys [db-spec dialect reset]}]
{:postgres
{:dialect 'xtdb.jdbc.psql/->dialect
:db-spec {:dbtype "postgresql" :dbname "xtdbtest", :user "postgres", :password "postgres"}
:reset
(fn reset-pg [db-spec]
(with-open [conn (jdbc/get-connection (dissoc db-spec :dbname))]
(jdbc/execute! conn ["DROP DATABASE IF EXISTS xtdbtest"])
(jdbc/execute! conn ["CREATE DATABASE xtdbtest"]))
db-spec)}
:h2
{:dialect 'xtdb.jdbc.h2/->dialect
:db-spec {:dbtype "h2" :dbname (str (io/file db-dir "xtdbtest"))}
:reset (fn reset-h2 [db-spec] (io/delete-file (:dbname db-spec) true))}}
:when (fj/jdbc-dialects dialect-kw)]
(let [exec (Executors/newFixedThreadPool 4)
futs (atom [])
nextjournal/clerk
;; # How Clerk Works 🕵🏻♀️
(ns how-clerk-works
{:nextjournal.clerk/toc true}
(:require [next.jdbc :as jdbc]
[nextjournal.clerk :as clerk]
[nextjournal.clerk.parser :as parser]
[nextjournal.clerk.eval :as eval]
[nextjournal.clerk.analyzer :as ana]
[weavejester.dependency :as dep]))
;; For side effectful functions that should be cached, like a database query, you can add a value like this `#inst` to control when evaluation should happen.
(def query-results
(let [_run-at #_(java.util.Date.) #inst "2021-05-20T08:28:29.445-00:00"
ds (next.jdbc/get-datasource {:dbtype "sqlite" :dbname "chinook.db"})]
(with-open [conn (next.jdbc/get-connection ds)]
(clerk/table (next.jdbc/execute! conn ["SELECT AlbumId, Bytes, Name, TrackID, UnitPrice FROM tracks"])))))
seancorfield/next-jdbc
(ns ^:no-doc next.jdbc.default-options
"Implementation of default options logic."
(:require [next.jdbc.protocols :as p]))
(extend-protocol p/Connectable
DefaultOptions
(get-connection [this opts]
(p/get-connection (:connectable this)
(merge (:options this) opts))))
leafclick/pgmig
(require '[next.jdbc :as jdbc]
'[common.util :refer [print-result]])
(print-result
(:next.jdbc/update-count (jdbc/execute-one! (get-connection) ["delete from bar"])))
souenzzo/atemoia
(ns atemoia.server-test
(:require [atemoia.server :as atemoia]
[cheshire.core :as json]
[clojure.test :refer [deftest is]]
[io.pedestal.http :as http]
[io.pedestal.test :refer [response-for]]
[next.jdbc :as jdbc]
[next.jdbc.result-set :as rs]))
(deftest hello
(let [atm-conn (jdbc/with-options (jdbc/get-connection {:jdbcUrl "jdbc:h2:mem:"})
{:builder-fn rs/as-lower-maps})
service-fn (-> {::atemoia/atm-conn atm-conn}
atemoia/create-service
http/dev-interceptors
http/create-servlet
::http/service-fn)]
(atemoia/install-schema {::atemoia/atm-conn atm-conn})
(is (= []
(-> service-fn
(response-for :get "/todo")
:body
(json/parse-string true)))
"fetching todos before creating")
(is (= 201
(-> service-fn
(response-for :post "/todo"
:body (json/generate-string {:note "hello world"}))
:status))
"creating a todo")
(is (= [{:todo/id 1
:todo/note "hello world"}]
(-> service-fn
(response-for :get "/todo")
:body
(json/parse-string true)))
"fetching the todos, after creating one")
(dotimes [idx 10]
(is (= 201
(-> service-fn
(response-for :post "/todo"
:body (json/generate-string {:note (str "hello world" idx)}))
:status))
(str "creating many todos: idx = " idx)))
(is (== 10
(-> service-fn
(response-for :get "/todo")
:body
(json/parse-string true)
count))
"fetching many todos")))