vanilla SQL with params for Clojure/Script
just "read SQL with :params
"
SQL is a great language, it is very expressive and exremely well optimized and supported by "SQL" databases. I don't believe it needs any kind of wrappers. It should live in its pure SQL form.
inquery
does two things:
Clojure APIs cover all the rest
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:
$ boot dev
boot.user=> (require '[inquery.core :as q]
'[jdbc.core :as jdbc])
dbspec
along with a set of queries
would usually come from config.edn
/ consul / etc :
boot.user=> (def dbspec
{:subprotocol "h2"
:subname "file:/tmp/solar"})
boot.user=> (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:
boot.user=> (with-open [conn (jdbc/connection dbspec)]
(jdbc/execute conn (:create-planets queries)))
check out the solar system:
boot.user=> (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:
boot.user=> (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 art
sy:
boot.user=> (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}]
$ lumo -i src/inquery/core.cljc --repl
Lumo 1.2.0
ClojureScript 1.9.482
Docs: (doc function-name-here)
Exit: Control+D or :cljs/quit or exit
cljs.user=> (ns inquery.core)
depending on how a resource path is setup, an optional parameter {:path "..."}
could help to specify the path to queries:
inquery.core=> (def queries
(make-query-map #{:create-planets
:find-planets
:find-planets-by-mass}
{:path "dev-resources/sql"}))
#'inquery.core/queries
inquery.core=> (print queries)
{:create-planets -- create planets
drop table if exists planets;
create table planets (id bigint auto_increment, name varchar, mass decimal);
insert into planets (name, mass) values ('Mercury', 330.2),
('Venus', 4868.5),
('Earth', 5973.6),
('Mars', 641.85),
('Jupiter', 1898600),
('Saturn', 568460),
('Uranus', 86832),
('Neptune', 102430),
('Pluto', 13.105);
, :find-planets -- find all planets
select * from planets;
, :find-planets-by-mass -- find planets under a certain mass
select * from planets where mass <= :max-mass
}
inquery.core=> (-> queries
:find-planets-by-mass
(with-params {:max-mass 5973.6}))
-- find planets under a certain mass
select * from planets where mass <= 5973.6
development scratchpad with sample shortcuts:
$ boot dev
boot.user=> (require '[scratchpad :as sp :refer [dbspec queries]])
boot.user=> (sp/execute dbspec (:create-planets queries))
boot.user=> (sp/fetch dbspec (: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}]
boot.user=> (sp/fetch dbspec (:find-planets-by-mass queries) {: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}]
Copyright © 2017 tolitius
Distributed under the Eclipse Public License either version 1.0 or (at your option) any later version.
Can you improve this documentation? These fine people already did:
anatoly & AnatolyEdit on GitHub
cljdoc is a website building & hosting documentation for Clojure/Script libraries
× close