HoneyEQL

HoneyEQL is a Clojure library enables you to query the database declaratively using the EDN Query Language(EQL). It aims to simplify the effort required to work with the relational databases in Clojure. It also provides database modification operations support using Clojure maps with namespace qualified keys.

Supports Postgres (9.4 & above) and MySQL (8.0 & above)

Rationale

When a query involves more than one table, the declarative nature of SQL depreciates. Depends on the type of relationship, we have to put appropriate join conditions.

Let's assume that we have the following schema.

To get all the films of an actor with the id 148 along with his/her first name & last name, we have to query it as

(jdbc/execute! ds ["SELECT actor.first_name, actor.last_name, film.title
                    FROM actor
                    LEFT OUTER JOIN film_actor ON film_actor.actor_id = actor.actor_id
                    LEFT OUTER JOIN film ON film_actor.film_id = film.film_id
                    WHERE actor.actor_id = ?" 148])

The query result would look like

[{:actor/first_name "EMILY", :actor/last_name "DEE", :film/title "ANONYMOUS HUMAN"}
 {:actor/first_name "EMILY", :actor/last_name "DEE", :film/title "BASIC EASY"}
 {:actor/first_name "EMILY", :actor/last_name "DEE", :film/title "CHAMBER ITALIAN"}
 ...]

Then we need to do the group by operation on the first_name & last_name attributes at the application layer to get the exact result that we want!

How about making these steps truly declarative?

With HoneyEQL, we can do it as

(heql/query-single 
  db-adapter  
  {[:actor/actor-id 148] 
   [:actor/first-name 
    :actor/last-name 
    {:actor/films 
     [:film/title]}]})

The above query yields the results in the exact-shape that we wanted and without any explicit data transformations.

{:actor/first-name "EMILY"
 :actor/last-name "DEE"
 :actor/films [{:film/title "ANONYMOUS HUMAN"}
               {:film/title "BASIC EASY"}
               {:film/title "CHAMBER ITALIAN"}
               ...]}

As the query syntax is made up of Clojure's data structures, we can construct it dynamically at runtime.

HoneyEQL transforms the EQL into single efficient SQL and query the database using next.jdbc.

Documentation

Running Tests

> ./test/database/start.sh # starts four docker containers
> clj -X:test # run after all the docker container started running
> ./test/database/stop.sh # stops all the four running docker containers

Acknowledgements

Walkable is the inspiration behind HoneyEQL.

HoneyEQL is not possible without the following excellent Clojure libraries.

• HoneySQL
• next-jdbc
• inflections
• data-json

The samples in the documentation of HoneyEQL uses the Sakila database from JOOQ extensively.

License

The use and distribution terms for this software are covered by the Eclipse Public License - v 2.0. By using this software in any fashion, you are agreeing to be bound by the terms of this license.