S-expressions

S-expressions is the way Walkable allow you to express SQL meanings in a Lispy syntax in a highly composable way without compromising security.

Note about the examples in this page:

  • 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 independently executable, except ones with tables and columns.

  • SQL output may differ when you specify different SQL flavors in your registry or even provide your own customization.

  • jdbc/query is used here but of course you can use any sql library.

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

  • 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

[:+ [:*] (1)
    [:* 2 4 7]
    [:/ 0.25]]
1 :* with no argument result in 1
(jdbc/query your-db ["SELECT (1 + (2 * 4 * 7) + (1/0.25)) AS q"])
[{:q 61.0}]

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"}]

Type conversion

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}]

Important note

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}]

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 (1)
 "default"]
1 must be literally true, not :default or anything else
(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"}]

Define your own operators

Docs coming soon.