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 SELECT statements or WHERE conditions. For demonstrating purpose, the strings are wrapped in SELECT ... as q so the SQL outputs are executable, except ones with tables and columns.
  • SQL output may differ when you require different implementations (ie (require 'walkable.sql-query-builder.impl.postgres) vs (require 'walkable.sql-query-builder.impl.sqlite)).

Primitive types

S-expression
SQL output
result
123
(jdbc/query your-db ["SELECT 123 AS q"])
[{:q 123}]

S-expression
SQL output
result
nil
(jdbc/query your-db ["SELECT NULL AS q"])
[{:q nil}]

S-expression
SQL output
result
"hello world"
(jdbc/query your-db ["SELECT ? AS q" "hello world"])
[{:q "hello world"}]

S-expression
SQL output
result
"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.

S-expression
SQL output
result
: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.

S-expression
SQL output
result
[:= 1 2]
(jdbc/query your-db ["SELECT (1 = 2) AS q"])
[{:q false}]

S-expression
SQL output
result
[:<= 1 2]
(jdbc/query your-db ["SELECT (1 <= 2) AS q"])
[{:q true}]

S-expression
SQL output
result
[:< 1 2 3 1]
(jdbc/query your-db ["SELECT ((1 < 2) AND (2 < 3) AND (3 < 1)) AS q"])
[{:q false}]

S-expression
SQL output
result
[:= 0]
(jdbc/query your-db ["SELECT true AS q"])
[{:q true}]

S-expression
SQL output
result
[:>= 1000]
(jdbc/query your-db ["SELECT true AS q"])
[{:q true}]

String comparison operators: =, like, match, glob:

S-expression
SQL output
result
[:= "hello" "hi"]
(jdbc/query your-db ["SELECT (? = ?) AS q" "hello" "hi"])
[{:q false}]

S-expression
SQL output
result
[:like "abcd" "abc%"]
(jdbc/query your-db ["SELECT (? LIKE ?) AS q" "abcd" "abc%"])
[{:q true}]

Use them on some columns, too:

S-expression
SQL output
result
[:= :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: :+, :-, :*, :/:

S-expression
SQL output
result
[:+ 1 2 4 8]
(jdbc/query your-db ["SELECT (1 + 2 + 4 + 8) AS q"])
[{:q 15}]

Feel free to mix them:

S-expression
SQL output
result
[:+ [:*] [:* 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

S-expression
SQL output
result
[:str "hello " nil "world" 123]
(jdbc/query your-db ["SELECT (CONCAT(?, NULL, ?, 123) AS q" "hello " "world"])
[{:q "hello world123"}]

S-expression
SQL output
result
[:subs "hello world"]
(jdbc/query your-db ["SELECT (CONCAT(?, NULL, ?, 123) AS q" "hello " "world"])
[{:q "hello world123"}]

S-expression
SQL output
result
[: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:

S-expression
SQL output
result
[:cast "2" :integer]
(jdbc/query your-db ["SELECT CAST(? as INTEGER) AS q" "2"])
[{:q 2}]

S-expression
SQL output
result
[: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:

S-expression
SQL output
result
[:and true true false]
(jdbc/query your-db ["SELECT (true AND true AND false) AS q"])
[{:q false}]

S-expression
SQL output
result
[:and]
(jdbc/query your-db ["SELECT (true) AS q"])
[{:q true}]

S-expression
SQL output
result
[:or]
(jdbc/query your-db ["SELECT (NULL) AS q"])
[{:q nil}]

:not accepts exactly one argument:

S-expression
SQL output
result
[:not true]
(jdbc/query your-db ["SELECT (NOT true) AS q"])
[{:q false}]

Party time! Mix them as you wish:

S-expression
SQL output
result
[: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}]

Notes

Please note that Walkable S-expressions are translated directly to SQL equivalent. Your DBMS may throw an exception if you ask for this:

S-expression
SQL output
result
[: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!

S-expression
SQL output
result
[:not nil]
(jdbc/query your-db ["SELECT (NOT NULL) AS q"])
[{:q nil}]

nil can not be checked with :=. Use :nil? instead

S-expression
SQL output
result
[:= nil nil]
(jdbc/query your-db ["SELECT (NULL = NULL) AS q"])
[{:q nil}]

S-expression
SQL output
result
[:nil? nil]
(jdbc/query your-db ["SELECT (NULL IS NULL) AS q"])
[{:q true}]

Other constructs

:when, :if, :case and :cond look like in Clojure...

S-expression
SQL output
result
[:when true "yay"] ;; or [:if true "yay"]
(jdbc/query your-db ["SELECT (CASE WHEN ( true ) THEN ( ? ) END) AS q" "yay"])
[{:q "yay"}]

S-expression
SQL output
result
[:if [:= 1 2] "yep" "nope"]
(jdbc/query your-db ["SELECT (CASE WHEN ((1)=(2)) THEN ( ? ) ELSE ( ? ) END) AS q" "yay" "nope"])
[{:q "nope"}]

S-expression
SQL output
result
[:case [:+ 0 1] 2 3]
(jdbc/query your-db ["SELECT (CASE (0+1) WHEN (2) THEN (3) END) AS q"])
[{:q nil}]

S-expression
SQL output
result
[: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}]

S-expression
SQL output
result
[: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.

S-expression
SQL output
result
[: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:

Query for a real column
Query for a pseudo column
[{[:person/by-id 9]
  [:person/yob]}]
[{[:person/by-id 9]
  [:person/age]}]

Filter with a real column
Filter with a pseudo column
[{(: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:

S-expression
SQL output
result
[:= 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}]

S-expression
SQL output
result
[: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}]

results matching ""

    No results matching ""