Back
insert! (clj)
(source)function
(insert! connectable table key-map)
(insert! connectable table key-map opts)
Syntactic sugar over `execute-one!` to make inserting hash maps easier.
Given a connectable object, a table name, and a data hash map, inserts the
data as a single row in the database and attempts to return a map of generated
keys.
Examples
next-jdbc
(ns next.jdbc.sql-test
"Tests for the syntactic sugar SQL functions."
(:require [clojure.test :refer [deftest is testing use-fixtures]]
[next.jdbc :as jdbc]
[next.jdbc.specs :as specs]
[next.jdbc.sql :as sql]
[next.jdbc.test-fixtures
:refer [with-test-db ds column default-options
derby? jtds? maria? mssql? mysql? postgres? sqlite?]]
[next.jdbc.types :refer [as-other as-real as-varchar]]))
(deftest test-insert-delete
(let [new-key (cond (derby?) :1
(jtds?) :ID
(maria?) :insert_id
(mssql?) :GENERATED_KEYS
(mysql?) :GENERATED_KEY
(postgres?) :fruit/id
:else :FRUIT/ID)]
(testing "single insert/delete"
(is (== 5 (new-key (sql/insert! (ds) :fruit
{:name (as-varchar "Kiwi")
:appearance "green & fuzzy"
:cost 100 :grade (as-real 99.9)}
{:suffix
(when (sqlite?)
"RETURNING *")}))))
(is (= 5 (count (sql/query (ds) ["select * from fruit"]))))
(is (= {:next.jdbc/update-count 1}
(sql/delete! (ds) :fruit {:id 5})))
(is (= 4 (count (sql/query (ds) ["select * from fruit"])))))
(testing "multiple insert/delete"
(is (= (cond (derby?)
[nil] ; WTF Apache Derby?
(mssql?)
[8M]
(maria?)
[6]
:else
[6 7 8])
(mapv new-key
(sql/insert-multi! (ds) :fruit
[:name :appearance :cost :grade]
[["Kiwi" "green & fuzzy" 100 99.9]
["Grape" "black" 10 50]
["Lemon" "yellow" 20 9.9]]
{:suffix
(when (sqlite?)
"RETURNING *")}))))
(is (= 7 (count (sql/query (ds) ["select * from fruit"]))))
(is (= {:next.jdbc/update-count 1}
(sql/delete! (ds) :fruit {:id 6})))
(is (= 6 (count (sql/query (ds) ["select * from fruit"]))))
(is (= {:next.jdbc/update-count 2}
(sql/delete! (ds) :fruit ["id > ?" 4])))
(is (= 4 (count (sql/query (ds) ["select * from fruit"])))))
(testing "multiple insert/delete with sequential cols/rows" ; per #43
(is (= (cond (derby?)
[nil] ; WTF Apache Derby?
(mssql?)
[11M]
(maria?)
[9]
:else
[9 10 11])
(mapv new-key
(sql/insert-multi! (ds) :fruit
'(:name :appearance :cost :grade)
'(("Kiwi" "green & fuzzy" 100 99.9)
("Grape" "black" 10 50)
("Lemon" "yellow" 20 9.9))
{:suffix
(when (sqlite?)
"RETURNING *")}))))
(is (= 7 (count (sql/query (ds) ["select * from fruit"]))))
(is (= {:next.jdbc/update-count 1}
(sql/delete! (ds) :fruit {:id 9})))
(is (= 6 (count (sql/query (ds) ["select * from fruit"]))))
(is (= {:next.jdbc/update-count 2}
(sql/delete! (ds) :fruit ["id > ?" 4])))
(is (= 4 (count (sql/query (ds) ["select * from fruit"])))))
(testing "multiple insert/delete with maps"
(is (= (cond (derby?)
[nil] ; WTF Apache Derby?
(mssql?)
[14M]
(maria?)
[12]
:else
[12 13 14])
(mapv new-key
(sql/insert-multi! (ds) :fruit
[{:name "Kiwi"
:appearance "green & fuzzy"
:cost 100
:grade 99.9}
{:name "Grape"
:appearance "black"
:cost 10
:grade 50}
{:name "Lemon"
:appearance "yellow"
:cost 20
:grade 9.9}]
{:suffix
(when (sqlite?)
"RETURNING *")}))))
(is (= 7 (count (sql/query (ds) ["select * from fruit"]))))
(is (= {:next.jdbc/update-count 1}
(sql/delete! (ds) :fruit {:id 12})))
(is (= 6 (count (sql/query (ds) ["select * from fruit"]))))
(is (= {:next.jdbc/update-count 2}
(sql/delete! (ds) :fruit ["id > ?" 10])))
(is (= 4 (count (sql/query (ds) ["select * from fruit"])))))
(testing "empty insert-multi!" ; per #44 and #264
(is (= [] (sql/insert-multi! (ds) :fruit
[:name :appearance :cost :grade]
[]
{:suffix
(when (sqlite?)
"RETURNING *")})))
;; per #264 the following should all be legal too:
(is (= [] (sql/insert-multi! (ds) :fruit
[]
{:suffix
(when (sqlite?)
"RETURNING *")})))
(is (= [] (sql/insert-multi! (ds) :fruit
[]
[]
{:suffix
(when (sqlite?)
"RETURNING *")})))
(is (= [] (sql/insert-multi! (ds) :fruit [])))
(is (= [] (sql/insert-multi! (ds) :fruit [] []))))))
(deftest enum-pg
(when (postgres?)
(let [r (sql/insert! (ds) :lang-test {:lang (as-other "fr")}
jdbc/snake-kebab-opts)]
(is (= {:lang-test/lang "fr"} r)))))
seancorfield/next-jdbc
(ns next.jdbc.sql-test
"Tests for the syntactic sugar SQL functions."
(:require [clojure.test :refer [deftest is testing use-fixtures]]
[next.jdbc :as jdbc]
[next.jdbc.specs :as specs]
[next.jdbc.sql :as sql]
[next.jdbc.test-fixtures
:refer [with-test-db ds column default-options
derby? jtds? maria? mssql? mysql? postgres? sqlite?]]
[next.jdbc.types :refer [as-other as-real as-varchar]]))
(deftest test-insert-delete
(let [new-key (cond (derby?) :1
(jtds?) :ID
(maria?) :insert_id
(mssql?) :GENERATED_KEYS
(mysql?) :GENERATED_KEY
(postgres?) :fruit/id
:else :FRUIT/ID)]
(testing "single insert/delete"
(is (== 5 (new-key (sql/insert! (ds) :fruit
{:name (as-varchar "Kiwi")
:appearance "green & fuzzy"
:cost 100 :grade (as-real 99.9)}
{:suffix
(when (sqlite?)
"RETURNING *")}))))
(is (= 5 (count (sql/query (ds) ["select * from fruit"]))))
(is (= {:next.jdbc/update-count 1}
(sql/delete! (ds) :fruit {:id 5})))
(is (= 4 (count (sql/query (ds) ["select * from fruit"])))))
(testing "multiple insert/delete"
(is (= (cond (derby?)
[nil] ; WTF Apache Derby?
(mssql?)
[8M]
(maria?)
[6]
:else
[6 7 8])
(mapv new-key
(sql/insert-multi! (ds) :fruit
[:name :appearance :cost :grade]
[["Kiwi" "green & fuzzy" 100 99.9]
["Grape" "black" 10 50]
["Lemon" "yellow" 20 9.9]]
{:suffix
(when (sqlite?)
"RETURNING *")}))))
(is (= 7 (count (sql/query (ds) ["select * from fruit"]))))
(is (= {:next.jdbc/update-count 1}
(sql/delete! (ds) :fruit {:id 6})))
(is (= 6 (count (sql/query (ds) ["select * from fruit"]))))
(is (= {:next.jdbc/update-count 2}
(sql/delete! (ds) :fruit ["id > ?" 4])))
(is (= 4 (count (sql/query (ds) ["select * from fruit"])))))
(testing "multiple insert/delete with sequential cols/rows" ; per #43
(is (= (cond (derby?)
[nil] ; WTF Apache Derby?
(mssql?)
[11M]
(maria?)
[9]
:else
[9 10 11])
(mapv new-key
(sql/insert-multi! (ds) :fruit
'(:name :appearance :cost :grade)
'(("Kiwi" "green & fuzzy" 100 99.9)
("Grape" "black" 10 50)
("Lemon" "yellow" 20 9.9))
{:suffix
(when (sqlite?)
"RETURNING *")}))))
(is (= 7 (count (sql/query (ds) ["select * from fruit"]))))
(is (= {:next.jdbc/update-count 1}
(sql/delete! (ds) :fruit {:id 9})))
(is (= 6 (count (sql/query (ds) ["select * from fruit"]))))
(is (= {:next.jdbc/update-count 2}
(sql/delete! (ds) :fruit ["id > ?" 4])))
(is (= 4 (count (sql/query (ds) ["select * from fruit"])))))
(testing "multiple insert/delete with maps"
(is (= (cond (derby?)
[nil] ; WTF Apache Derby?
(mssql?)
[14M]
(maria?)
[12]
:else
[12 13 14])
(mapv new-key
(sql/insert-multi! (ds) :fruit
[{:name "Kiwi"
:appearance "green & fuzzy"
:cost 100
:grade 99.9}
{:name "Grape"
:appearance "black"
:cost 10
:grade 50}
{:name "Lemon"
:appearance "yellow"
:cost 20
:grade 9.9}]
{:suffix
(when (sqlite?)
"RETURNING *")}))))
(is (= 7 (count (sql/query (ds) ["select * from fruit"]))))
(is (= {:next.jdbc/update-count 1}
(sql/delete! (ds) :fruit {:id 12})))
(is (= 6 (count (sql/query (ds) ["select * from fruit"]))))
(is (= {:next.jdbc/update-count 2}
(sql/delete! (ds) :fruit ["id > ?" 10])))
(is (= 4 (count (sql/query (ds) ["select * from fruit"])))))
(testing "empty insert-multi!" ; per #44 and #264
(is (= [] (sql/insert-multi! (ds) :fruit
[:name :appearance :cost :grade]
[]
{:suffix
(when (sqlite?)
"RETURNING *")})))
;; per #264 the following should all be legal too:
(is (= [] (sql/insert-multi! (ds) :fruit
[]
{:suffix
(when (sqlite?)
"RETURNING *")})))
(is (= [] (sql/insert-multi! (ds) :fruit
[]
[]
{:suffix
(when (sqlite?)
"RETURNING *")})))
(is (= [] (sql/insert-multi! (ds) :fruit [])))
(is (= [] (sql/insert-multi! (ds) :fruit [] []))))))
(deftest enum-pg
(when (postgres?)
(let [r (sql/insert! (ds) :lang-test {:lang (as-other "fr")}
jdbc/snake-kebab-opts)]
(is (= {:lang-test/lang "fr"} r)))))
jacekschae/learn-reitit-course-files
(ns cheffy.conversation.db
(:require [next.jdbc.sql :as sql]
[next.jdbc :as jdbc])
(:import (java.util UUID)))
(defmethod dispatch :insert-message
[[_ db {:keys [conversation-id to from] :as message}]]
(jdbc/with-transaction [tx db]
(sql/insert! tx :message
(-> message (assoc :uid from) (dissoc :to :from))
(:options db))
(jdbc/execute-one! tx ["UPDATE conversation
SET notifications = notifications + 1
WHERE conversation_id = ?
AND uid = ?" conversation-id to])))
(defmethod dispatch :start-conversation
[[_ db {:keys [to from] :as message}]]
(with-open [conn (jdbc/get-connection db)]
(let [conn-opts (jdbc/with-options conn (:options db))
conversing (jdbc/execute-one! conn-opts ["SELECT a.conversation_id
FROM conversation a
JOIN conversation b
ON a.conversation_id = b.conversation_id
WHERE a.uid = ? AND b.uid = ?" to from])]
(if-let [conversation-id (:conversation/conversation-id conversing)]
(do
(dispatch [:insert-message conn-opts (assoc message :conversation-id conversation-id)])
conversation-id)
(jdbc/with-transaction [tx conn]
(let [conversation-id (str (UUID/randomUUID))]
(sql/insert! tx :message (-> message
(assoc :uid from :conversation-id conversation-id)
(dissoc :from :to))
(:options db))
(sql/insert-multi! tx :conversation
[:notifications :uid :conversation_id]
[[1 to conversation-id]
[0 from conversation-id]])
conversation-id))))))
jacekschae/learn-reitit-course-files
(ns cheffy.conversation.db
(:require [next.jdbc.sql :as sql]
[next.jdbc :as jdbc])
(:import (java.util UUID)))
(defmethod dispatch :insert-message
[[_ db {:keys [conversation-id to from] :as message}]]
(jdbc/with-transaction [tx db]
(sql/insert! tx :message
(-> message (assoc :uid from) (dissoc :to :from))
(:options db))
(jdbc/execute-one! tx ["UPDATE conversation
SET notifications = notifications + 1
WHERE conversation_id = ?
AND uid = ?" conversation-id to])))
(defmethod dispatch :start-conversation
[[_ db {:keys [to from] :as message}]]
(with-open [conn (jdbc/get-connection db)]
(let [conn-opts (jdbc/with-options conn (:options db))
conversing (jdbc/execute-one! conn-opts ["SELECT a.conversation_id
FROM conversation a
JOIN conversation b
ON a.conversation_id = b.conversation_id
WHERE a.uid = ? AND b.uid = ?" to from])]
(if-let [conversation-id (:conversation/conversation-id conversing)]
(do
(dispatch [:insert-message conn-opts (assoc message :conversation-id conversation-id)])
conversation-id)
(jdbc/with-transaction [tx conn]
(let [conversation-id (str (UUID/randomUUID))]
(sql/insert! tx :message (-> message
(assoc :uid from :conversation-id conversation-id)
(dissoc :from :to))
(:options db))
(sql/insert-multi! tx :conversation
[:notifications :uid :conversation_id]
[[1 to conversation-id]
[0 from conversation-id]])
conversation-id))))))
jacekschae/learn-reitit-course-files
(ns cheffy.conversation.db
(:require [next.jdbc.sql :as sql]
[next.jdbc :as jdbc]))
(defmethod dispatch :insert-message
[[_ db {:keys [conversation-id to from] :as message}]]
(jdbc/with-transaction [tx db]
(sql/insert! tx :message
(-> message (assoc :uid from) (dissoc :to :from))
(:options db))
(jdbc/execute-one! tx ["UPDATE conversation
SET notifications = notifications + 1
WHERE converstaion_id = ?
AND uid = ?" conversation-id to])))
pilosus/dienstplan
(ns dienstplan.db-test
(:require
[clojure.spec.gen.alpha :as gen]
[clojure.test :refer [deftest is testing use-fixtures]]
[dienstplan.db :as db]
[dienstplan.fixture :as fix]
[dienstplan.spec :as spec]
[next.jdbc :as jdbc]
[next.jdbc.sql :as sql]
[next.jdbc.date-time :as date-time]
[clojure.spec.alpha :as s]))
(deftest ^:integration test-rota-update!
(testing "Update rota"
(doseq [[before after expected description] params-rota-update!]
(testing description
(jdbc/with-transaction [conn db/db]
(let [_ (db/rota-insert! before)
_ (db/rota-update! after)
rota (db/duty-get rota-channel rota-name)]
(is (= expected (dissoc rota :rota/id)))))))))
(deftest test-db-schema-rota
(testing "DB table rota"
(doseq [rota-params params-gen-rota]
(jdbc/with-transaction [conn db/db]
(date-time/read-as-instant)
(let [inserted (sql/insert! conn :rota rota-params)]
(is (s/valid? ::spec/db-rota inserted)))
(.rollback conn)))))
(deftest test-db-schema-mention
(testing "DB table mention"
(doseq [mention-params params-gen-mention]
(jdbc/with-transaction [conn db/db]
(date-time/read-as-instant)
(let [now (new java.sql.Timestamp (System/currentTimeMillis))
rota (sql/insert!
conn :rota
{:name "rota" :channel "chan" :description "my rota"
:created_on now :updated_on now})
params
(update
mention-params
:mention/rota_id
(constantly (:rota/id rota)))
inserted (sql/insert! conn :mention params)]
(is (s/valid? ::spec/db-mention inserted)))
(.rollback conn)))))