Spike to see what an alias aware SQL query DSL might look like. Make joins easier to add. Deal with munging/demunging column names. Boils down to a HoneySQL query and a row-fn demunger.

queries.clj

(s/def ::columns (s/coll-of qualified-keyword? :type set?))
(s/def ::from (s/coll-of simple-keyword? :type set?))
(s/def ::aliases (s/map-of simple-keyword? simple-keyword?))
(s/def ::left-join (s/map-of simple-keyword? ::where))
(s/def ::where (s/coll-of ::condition))
(s/def ::condition (s/cat :op #{:=} :args (s/+ any?)))

(def my-query1
  {::columns   #{:SITE/NAME :BL/NAME}
   ::from      #{:SITE}
   ::left-join {:BL [[:= :SITE/SITE_ID :BL/SITE_ID]]}
   ::where     [[:= :SITE/SITE_ID "BB"]]})

(def my-query2
  {::columns #{:SITE/NAME :SITE2/NAME}
   ::aliases {:SITE :SITE :SITE2 :SITE}
   ::from    #{:SITE :SITE2}})

(defn query-plan
  [{:keys [::columns ::from ::aliases ::left-join ::where]}]
  (let [table-kmap (memoize (fn [s] (name (gensym (first (name s))))))
        column-as (map (comp keyword gensym name) columns)
        column-sel #(keyword (str (table-kmap (namespace %)) "." (name %)))
        query-filter (fn [[op & args]]
                       (let [prep-arg #(if (keyword? %) (column-sel %) %)]
                         (into [op] (map prep-arg args))))
        filters (map query-filter where)
        table-as (juxt #(get aliases % %) (comp keyword table-kmap name))
        column-kmap (zipmap column-as columns)
        left-join-term (mapcat
                         (fn [[k v]]
                           [(table-as k) (into [:and] (map query-filter v))])
                         left-join)]
    {:row-fn (fn [row] (set/rename-keys row column-kmap))
     :query  (cond-> {:select (map (juxt val key) (zipmap column-as (map column-sel columns)))
                      :from   (map table-as from)}
               (seq left-join-term) (assoc :left-join left-join-term)
               (seq filters) (assoc :where (into [:and] filters)))}))

(comment
  (let [{:keys [query row-fn]} (query-plan my-query1)
        opts {:identifiers string/upper-case :row-fn row-fn}
        sql (sql/format query)]
    {:query query
     :sql sql
     :xrel  (try (take 10 (j/query local-db sql opts))
                 (catch Exception e
                   (.getMessage e)))}))