S-expressions is the way Walkable allow you to write arbitrary SQL expressions in your paredit/parinfer-powered editors without compromising security.
Note about SQL examples:
- S-expressions can end up as SQL strings in either
SELECTstatements orWHEREconditions. For demonstrating purpose, the strings are wrapped inSELECT ... as qso the SQL outputs are executable, except ones with tables and columns. - SQL output may differ when you
requiredifferent implementations (ie(require 'walkable.sql-query-builder.impl.postgres)vs(require 'walkable.sql-query-builder.impl.sqlite)).
Primitive types
123
(jdbc/query your-db ["SELECT 123 AS q"])
[{:q 123}]
nil
(jdbc/query your-db ["SELECT NULL AS q"])
[{:q nil}]
"hello world"
(jdbc/query your-db ["SELECT ? AS q" "hello world"])
[{:q "hello world"}]
"hello\"; DROP TABLE users"
(jdbc/query your-db ["SELECT ? AS q" "hello\"; DROP TABLE users"])
[{:q "hello\"; DROP TABLE users"}]
Columns
Note
The examples just use backticks as quote marks. Depending on your emitter configuration, Walkable will emit SQL strings using whatever quote marks you specified.
:my-table/a-column
(jdbc/query your-db ["SELECT `my_table`.`a_column` AS `my-table/a-column` FROM `my_table`"])
[{:my-table/a-column 42}, ...other records...]
Comparison
Walkable comes with some comparison operators: :=, :<, :>,
:<=, :>=. They will result in SQL operators with the same name,
but also handle multiple arity mimicking their Clojure equivalents.
[:= 1 2]
(jdbc/query your-db ["SELECT (1 = 2) AS q"])
[{:q false}]
[:<= 1 2]
(jdbc/query your-db ["SELECT (1 <= 2) AS q"])
[{:q true}]
[:< 1 2 3 1]
(jdbc/query your-db ["SELECT ((1 < 2) AND (2 < 3) AND (3 < 1)) AS q"])
[{:q false}]
[:= 0]
(jdbc/query your-db ["SELECT true AS q"])
[{:q true}]
[:>= 1000]
(jdbc/query your-db ["SELECT true AS q"])
[{:q true}]
String comparison operators: =, like, match, glob:
[:= "hello" "hi"]
(jdbc/query your-db ["SELECT (? = ?) AS q" "hello" "hi"])
[{:q false}]
[:like "abcd" "abc%"]
(jdbc/query your-db ["SELECT (? LIKE ?) AS q" "abcd" "abc%"])
[{:q true}]
Use them on some columns, too:
[:= :my-table/its-column "hi"]
(jdbc/query your-db ["SELECT (`my_table`.`its_column` = ?) AS q FROM `my_table`" "hi"])
[{:q true}]
Math
Basic math operators work just like their Clojure equivalents: :+, :-, :*, :/:
[:+ 1 2 4 8]
(jdbc/query your-db ["SELECT (1 + 2 + 4 + 8) AS q"])
[{:q 15}]
Feel free to mix them:
[:+ [:*] [:* 2 4 7] [:/ 0.25]]
(jdbc/query your-db ["SELECT (1 + (2 * 4 * 7) + (1/0.25)) AS q"])
[{:q 61.0}]
:* with no argument result in 1
String manipulation
[:str "hello " nil "world" 123]
(jdbc/query your-db ["SELECT (CONCAT(?, NULL, ?, 123) AS q" "hello " "world"])
[{:q "hello world123"}]
[:subs "hello world"]
(jdbc/query your-db ["SELECT (CONCAT(?, NULL, ?, 123) AS q" "hello " "world"])
[{:q "hello world123"}]
[:str "hello " nil "world" 123]
(jdbc/query your-db ["SELECT (CONCAT(?, NULL, ?, 123) AS q" "hello " "world"])
[{:q "hello world123"}]
Conversion between types
Use the :cast operator:
[:cast "2" :integer]
(jdbc/query your-db ["SELECT CAST(? as INTEGER) AS q" "2"])
[{:q 2}]
[:cast 3 :text]
(jdbc/query your-db ["SELECT CAST(3 as TEXT) AS q"])
[{:q "3"}]
Logic constructs
:and and :or accept many arguments like in Clojure:
[:and true true false]
(jdbc/query your-db ["SELECT (true AND true AND false) AS q"])
[{:q false}]
[:and]
(jdbc/query your-db ["SELECT (true) AS q"])
[{:q true}]
[:or]
(jdbc/query your-db ["SELECT (NULL) AS q"])
[{:q nil}]
:not accepts exactly one argument:
[:not true]
(jdbc/query your-db ["SELECT (NOT true) AS q"])
[{:q false}]
Party time! Mix them as you wish:
[:and [:= 4 [:* 2 2]] [:not [:> 1 2]] [:or [:= 2 3] [:= 4 4]]]
(jdbc/query your-db ["SELECT (((4)=((2)*(2))) AND (NOT ((1)>(2))) AND (((2)=(3)) OR ((4)=(4)))) AS q"])
[{:q true}]
Notice
Please note that Walkable S-expressions are translated directly to SQL equivalent. Your DBMS may throw an exception if you ask for this:
[:or 2 true]
(jdbc/query your-db ["SELECT (2 OR true) AS q"])
ERROR: argument of OR must be type boolean, not type integer
Don't be surprised if you see [:not nil] is ... nil!
[:not nil]
(jdbc/query your-db ["SELECT (NOT NULL) AS q"])
[{:q nil}]
Other constructs
:when, :if, :case and :cond look like in Clojure...
[:when true "yay"] ;; or [:if true "yay"]
(jdbc/query your-db ["SELECT (CASE WHEN ( true ) THEN ( ? ) END) AS q" "yay"])
[{:q "yay"}]
[:if [:= 1 2] "yep" "nope"]
(jdbc/query your-db ["SELECT (CASE WHEN ((1)=(2)) THEN ( ? ) ELSE ( ? ) END) AS q" "yay" "nope"])
[{:q "nope"}]
[:case [:+ 0 1] 2 3]
(jdbc/query your-db ["SELECT (CASE (0+1) WHEN (2) THEN (3) END) AS q"])
[{:q nil}]
[:case [:+ 0 1] 2 3 4]
(jdbc/query your-db ["SELECT (CASE (0+1) WHEN (2) THEN (3) ELSE (4) END) AS q"])
[{:q 4}]
[:cond [:= 0 1] "wrong" [:< 2 3] "right"]
(jdbc/query your-db ["SELECT (CASE WHEN ((0)=(1)) THEN ( ? ) WHEN ((2)<(3)) THEN ( ? ) END) AS q" "wrong" "right"])
[{:q "right"}]
...except the fact that you must supply real booleans to them, not just some truthy values.
[:cond
[:= 0 1]
"wrong"
[:> 2 3]
"wrong again"
true ;; <= must be literally `true`, not `:default` or something else
"default"]
(jdbc/query your-db ["SELECT (CASE WHEN ((0)=(1)) THEN ( ? ) WHEN ((2)>(3)) THEN ( ? ) WHEN ( true ) THEN ( ? ) END) AS q" "wrong" "wrong again" "default"])
[{:q "default"}]
Pseudo columns
In your floor-plan you can define so-called pseudo columns that look just like normal columns from client-side view:
;; floor-plan
;; :person/yob is a real column
{:pseudo-columns {:person/age [:- 2018 :person/yob]}}
You can't tell the difference from client-side:
[{[:person/by-id 9]
[:person/yob]}]
[{[:person/by-id 9]
[:person/age]}]
[{(:people/all {:filters [:= 1988 :person/yob]})
[:person/name]}]
[{(:people/all {:filters [:= 30 :person/age]})
[:person/name]}]
Behind the scenes, Walkable will expand the pseudo columns to whatever they are defined. You can also use pseudo columns in other pseudo columns' definition, but be careful as Walkable won't check circular dependencies for you.
Please note you can only use true columns from the same table in the definition of pseudo columns. For instance, the following doesn't make sense:
;; floor-plan
{:pseudo-columns {:person/age [:- 2018 :pet/yob]}}
Your RDMS will throw an exception in that case anyway.
Define your own operators
There are some convenient marcros to help you "import" SQL functions/operators: walkable.sql-query-builder.expressions/import-functions and walkable.sql-query-builder.expressions/import-infix-operators.
More complex operators may require implementing multimethod walkable.sql-query-builder.expressions/process-operator or even a harder one walkable.sql-query-builder.expressions/process-unsafe-expression.
Todo: more docs.
Bonus: JSON in Postgresql
The following expressions work in Postgresql:
[:= 1
[:cast [:get-as-text [:jsonb {:a 1}] "a"] :integer]]
(jdbc/query your-db ["SELECT ((1)=(CAST ((?::jsonb)->>( ? ) AS INTEGER))) AS q" "{\"a\" :1}" "a"])
[{:q true}]
[:or [:= 2 [:array-length [:array 1 2 3 4] 1]]
[:contains [:jsonb {:a 1 :b 2}]
[:jsonb {:a 1}]]
[:jsonb-exists [:jsonb {:a 1 :b 2}]
"a"]]
(jdbc/query your-db ["SELECT (((2)=(array_length (ARRAY[1, 2, 3, 4], 1)))
OR ((?::jsonb)@>(?::jsonb))
OR (jsonb_exists (?::jsonb, ? )))
AS q"
"{\"a\":1,\"b\":2}" "{\"a\":1}" "{\"a\":1,\"b\":2}" "a"])
[{:q true}]