Inquery
vanilla SQL with params for Clojure/Script
Install / Use
/learn @tolitius/InqueryREADME
inquery
vanilla SQL with params for Clojure/Script
- no DSL
- no comments parsing
- no namespace creations
- no defs / defqueries
- no dependencies
- no edn SQL
just "read SQL with :params"
why
SQL is a great language, it is very expressive and exremely well optimized and supported by "SQL" databases. it needs no wrappers. it should live in its pure SQL form.
inquery does two things:
- reads SQL files
- substitutes params at runtime
Clojure APIs cover all the rest
using inquery
inquery is about SQL: it does not require or force a particular JDBC library or a database.
But to demo an actual database conversation, this example will use "funcool/clojure.jdbc" to speak to a sample H2 database since both of them are great.
There is nothing really to do other than to bring the queries into a map with a make-query-map function:
$ make repl
=> (require '[inquery.core :as q]
'[jdbc.core :as jdbc])
dbspec along with a set of queries would usually come from config.edn / consul / etc :
=> (def dbspec {:subprotocol "h2"
:subname "file:/tmp/solar"})
=> (def queries (q/make-query-map #{:create-planets
:find-planets
:find-planets-by-mass
:find-planets-by-name}))
inquiry by default will look under sql/* path for queries. In this case "dev-resources" is in a classpath:
▾ dev-resources/sql/
create-planets.sql
find-planets-by-mass.sql
find-planets-by-name.sql
find-planets.sql
Ready to roll, let's create some planets:
=> (with-open [conn (jdbc/connection dbspec)]
(jdbc/execute conn (:create-planets queries)))
check out the solar system:
=> (with-open [conn (jdbc/connection dbspec)]
(jdbc/fetch conn (:find-planets queries)))
[{:id 1, :name "Mercury", :mass 330.2M}
{:id 2, :name "Venus", :mass 4868.5M}
{:id 3, :name "Earth", :mass 5973.6M}
{:id 4, :name "Mars", :mass 641.85M}
{:id 5, :name "Jupiter", :mass 1898600M}
{:id 6, :name "Saturn", :mass 568460M}
{:id 7, :name "Uranus", :mass 86832M}
{:id 8, :name "Neptune", :mass 102430M}
{:id 9, :name "Pluto", :mass 13.105M}]
find all the planets with mass less or equal to the mass of Earth:
=> (with-open [conn (jdbc/connection dbspec)]
(jdbc/fetch conn (-> (:find-planets-by-mass queries)
(q/with-params {:max-mass 5973.6}))))
[{:id 1, :name "Mercury", :mass 330.2M}
{:id 2, :name "Venus", :mass 4868.5M}
{:id 3, :name "Earth", :mass 5973.6M}
{:id 4, :name "Mars", :mass 641.85M}
{:id 9, :name "Pluto", :mass 13.105M}]
which planet is the most artsy:
=> (with-open [conn (jdbc/connection dbspec)]
(jdbc/fetch conn (-> (:find-planets-by-name queries)
(q/with-params {:name "%art%"}))))
[{:id 3, :name "Earth", :mass 5973.6M}]
escaping
by default inquery will "SQL escape" all the parameters that need to be substituted in a query.
in case you need to not escape the params inquery has options to not escape the whole query with {:esc :don't}:
=> (with-open [conn (jdbc/connection dbspec)]
(jdbc/fetch conn (-> (:find-planets-by-name queries)
(q/with-params {:name "%art%"}
{:esc :don't}))))
or per individual parameter with {:as val}:
=> (with-open [conn (jdbc/connection dbspec)]
(jdbc/fetch conn (-> (:find-planets-by-name queries)
(q/with-params {:name {:as ""}
:mass 42}))))
things to note about escaping
nils are converted to "null":
=> (-> "name = :name" (q/with-params {:name nil}))
"name = null"
{:as nil} or {:as ""} are "as is", so it will be replaced with an empty string:
=> (-> "name = :name" (q/with-params {:name {:as nil}}))
"name = "
=> (-> "name = :name" (q/with-params {:name {:as ""}}))
"name = "
"" will become a "SQL empty string":
=> (-> "name = :name" (q/with-params {:name ""}))
"name = ''"
see tests for more examples.
dynamic queries
inquery can help out with some runtime decision making to build SQL predicates.
with-preds function takes a map of {pred-fn sql-predicate}.<br/>
for each "true" predicate function its sql-predicate will be added to the query:
=> (q/with-preds "select planet from solar_system where this = that"
{#(= 42 42) "and type = :type"})
"select planet from solar_system where this = that and type = :type"
=> (q/with-preds "select planet from solar_system where this = that"
{#(= 42 42) "and type = :type"
#(= 28 34) "and size < :max-size"})
"select planet from solar_system where this = that and type = :type"
if both predicates are true, both will be added:
=> (q/with-preds "select planet from solar_system where this = that"
{#(= 42 42) "and type = :type"
#(= 28 28) "and size < :max-size"})
"select planet from solar_system where this = that and type = :type and size < :max-size"
some queries don't come with where clause, for these cases with-preds takes a prefix:
=> (q/with-preds "select planet from solar_system"
{#(= 42 42) "and type = :type"
#(= 28 34) "and size < :max-size"}
{:prefix "where"})
"select planet from solar_system where type = :type"
developer will know the (first part of the) query, so this decision is not "hardcoded".
=> (q/with-preds "select planet from solar_system"
{#(= 42 42) "and type = :type"
#(= 34 34) "and size < :max-size"}
{:prefix "where"})
"select planet from solar_system where type = :type and size < :max-size"
in case none of the predicates are true, "where" prefix won't be used:
=> (q/with-preds "select planet from solar_system"
{#(= 42 -42) "and type = :type"
#(= 34 28) "and size < :max-size"}
{:prefix "where"})
"select planet from solar_system"
type safety
sql parameters
inquery uses a type protocol SqlParam to safely convert clojure/script values to sql strings:
(defprotocol SqlParam
"safety first"
(to-sql-string [this] "trusted type will be SQL'ized"))
it:
- prevents sql injection
- properly handles various data types
- is extensible for custom types
common types are handled out of the box:
(q/to-sql-string nil) ;; => "null"
(q/to-sql-string "earth") ;; => "'earth'"
(q/to-sql-string "pluto's moon") ;; => "'pluto''s moon'" ;; note proper escaping
(q/to-sql-string 42) ;; => "42"
(q/to-sql-string true) ;; => "true"
(q/to-sql-string :jupiter) ;; => "'jupiter'"
(q/to-sql-string [1 2 nil "mars"]) ;; => "(1,2,null,'mars')"
(q/to-sql-string #uuid "f81d4fae-7dec-11d0-a765-00a0c91e6bf6") ;; => "'f81d4fae-7dec-11d0-a765-00a0c91e6bf6'"
(q/to-sql-string #inst "2023-01-15T12:34:56Z") ;; => "'2023-01-15T12:34:56Z'"
(q/to-sql-string (java.util.Date.)) ;; => "'Wed Mar 26 09:42:17 EDT 2025'"
custom types
you can extend SqlParam protocol to handle custom types:
(defrecord Planet [name mass])
(extend-protocol inquery.core/SqlParam
Planet
(to-sql-string [planet]
(str "'" (:name planet) " (" (:mass planet) " x 10^24 kg)'")))
(q/to-sql-string (->Planet "neptune" 102)) ;; => "'neptune (102 x 10^24 kg)'"
its built in
no need to call "to-sql-string" of course, inquery does it internally:
;; find planets discovered during specific time range with certain composition types
(let [query "SELECT * FROM planets
WHERE discovery_date BETWEEN :start_date AND :end_date
AND name NOT IN :excluded_planets
AND composition_type IN :allowed_types
AND is_habitable = :habitable
AND discoverer_id = :discoverer"
params {:start_date (Instant/parse "2020-01-01T00:00:00Z")
:end_date (java.util.Date.)
:excluded_planets ["mercury" "venus" "earth"]
:allowed_types [:rocky :gas-giant :ice-giant]
:habitable true
:discoverer (UUID/fromString "f81d4fae-7dec-11d0-a765-00a0c91e6bf6")}]
(q/with-params query params))
;; => "SELECT * FROM planets
;; WHERE discovery_date BETWEEN '2020-01-01T00:00:00Z' AND 'Wed Mar 26 09:48:32 EDT 2025'
;; AND name NOT IN ('mercury','venus','earth')
;; AND composition_type IN ('rocky','gas-giant','ice-giant')
;; AND is_habitable = true
;; AND discoverer_id = 'f81d4fae-7dec-11d0-a765-00a0c91e6bf6'"
batch upserts
inquery provides functions to safely convert collections for batch operations:
seq->batch-params- converts a sequence of sequences to a string suitable for batch inserts/updatesseq->update-vals- legacy version that quotes all values (even numbers)
;; using seq->bat
