SQLY - a minimal EDN DSL for SQL generation

SQLY is yet another EDN based DSL for describing SQL queries. It’s limited to generating SELECT statements only at this point.

EXAMPLE 1

(require '[sqly.core :as sql])

(sql/sql
 '{:select   :*                         ; note the quote
   :from     :myapp.cues
   :where    {:zb-id      :is-not-nil   ; :is-not-nil & :is-nil are special
              :event-type :is-not-nil}
   :order-by [[:name :asc]
              [:foo.datetime :desc]]
   :group-by [:context.event-type
              (month :datetime)
              (day :datetime)]})

generates the following SQL:

select *
from myapp.cues
where (zb_id is not null and event_type is not null)
group by "context.event_type",month(datetime),day(datetime)
order by name asc,"foo.datetime" desc

EXAMPLE 2

(sql/sql
 '{:select   {:hour  (date-trunc "hour" :datetime) ; note the quote
              :type  :context.event-type
              :count (count 1)}
   :from     :myapp.cues
   :where    {:context.event-type :is-not-nil}
   :group-by [:context.event-type
              (date-trunc "hour" :datetime)]
   :order-by [[:hour :desc]]})

generates:

select date_trunc('hour',datetime) as hour,
       "context.event_type" as type,
       count(1) as count
from myapp.cues
where "context.event_type" is not null
group by "context.event_type",date_trunc('hour',datetime)
order by hour desc

EXAMPLE 3

(sql/sql
 '{:with {:get-offer (sql
                      {:select {:count (count 1)}
                       :from :myapp.cues
                       :where (= :cue "myapp.get-offer")})
          :purchase (sql
                     {:select {:count (count 1)}
                      :from :myapp.cues
                      :where (= :cue "myapp.purchase")})}
   :do {:select {:purchases-per-offer-loads
                 (* 1.0
                    (/ (sql {:select :purchase.count :from :purchase})
                       (sql {:select :get-offer.count :from :get-offer})))}}})

generates:

with
        get_offer as (select count(1) as count
                      from myapp.cues
                      where cue = 'myapp.get-offer'),
        purchase as (select count(1) as count
                     from myapp.cues
                     where cue = 'myapp.purchase')
select 1.0 * ((select "purchase.count" from purchase)
              / (select "get_offer.count" from get_offer))
       as purchases_per_offer_loads

EXAMPLE 4

(sql/sql
 '{:select [{:abc.time (date-format :abc-clnt/datetime "%Y-%m-%d %T")}
            {:abc.value-email :abc/value.profile.email}]
   :from   {:abc :abc-prod/abc-table1}
   :join   [{:type :left
             :from {:abc-clnt :abc-prod/abc-clnt}
             :on   {:abc-clnt/abc-id :abc/value.abc-id}}
            {:type :left
             :from {:abc-pmnts :abc-prod/abc-pmnts}
             :on   {:abc-pmnts/basket-id :abc-clnt/abc-id}}]
   :where  {:abc-clnt/event-name "btn-clicked"}})

generates:

select
       date_format(abc_clnt.datetime,'%Y-%m-%d %T') as "abc.time",
       abc."value.profile.email" as "abc.value_email"
from abc_prod.abc_table_1 as abc
where abc_clnt.event_name = 'btn-clicked'
left join abc_prod.abc_clnt as abc_clnt on abc_clnt.abc_id = abc."value.abc_id"
left join abc_prod.abc_pmnts as abc_pmnts on abc_pmnts.basket_id = abc_clnt.abc_id

License - Apache 2.0

Copyright 2018 Omnyway Inc.

Licensed under the Apache License, Version 2.0 (the “License”); you may not use this file except in compliance with the License. You may obtain a copy of the License at

http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an “AS IS” BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.