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

(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.