HoneyEQL
HoneyEQL is a Clojure library enables you to query database using the EDN Query Language(EQL). HoneyEQL transforms the EQL into single efficient SQL and query the database using next.jdbc.
HoneyEQL powers GraphQLize.
CAUTION: HoneyEQL is at its early stages now. It is not production-ready yet!. It currently supports Postgres (9.4 & above) and MySQL (8.0 & above) only.
Table of contents
- Getting Started
- Queries
- Coercion
- Metadata
Getting Started
Let's get started by adding HoneyEQL to your project.
In addition, you will need to add dependencies for the JDBC drivers you wish to use for whatever databases you are using and preferably a connection pooling library like HikariCP or c3p0.
This documentation uses deps and assumes you are connecting to the the sakila database created from this JOOQ's example repository.
;; deps.edn
{:paths ["src"]
:deps {org.graphqlize/honeyeql {:mvn/version "0.1.0-alpha8"}
hikari-cp {:mvn/version "2.10.0"}
org.postgresql/postgresql {:mvn/version "42.2.8"}
mysql/mysql-connector-java {:mvn/version "8.0.19"}}}
The next step is initializing the db-adapter using either db-spec-map or
Postgres with db-spec map
(ns core
(:require [honeyeql.db :as heql-db]))
(def db-adapter (heql-db/initialize {:dbtype "postgres"
:dbname "sakila"
:user "postgres"
:password "postgres"}))
MySQL with db connection pool
(ns core
(:require [honeyeql.db :as heql-db]
[hikari-cp.core :as hikari]))
(def db-adapter
(heql-db/initialize
(hikari/make-datasource
{:server-name "localhost"
:maximum-pool-size 1
:jdbc-url "jdbc:mysql://localhost:3306/sakila"
:driver-class-name "com.mysql.cj.jdbc.MysqlDataSource"
:username "root"
:password "mysql123"})))
Then we query the database using either query-single to retrieve a single item or query to retrieve multiple items.
(ns core
(:require ; ...
[honeyeql.core :as heql]))
; ...
(heql/query-single
db-adapter
[{[:actor/actor-id 1] [:actor/first-name
:actor/last-name]}])
; returns
{:actor/first-name "PENELOPE"
:actor/last-name "GUINESS"}
(heql/query
db-adapter
[{[] [:language/name]}])
; returns
({:language/name "English"} {:language/name "Italian"}
{:language/name "Japanese"} {:language/name "Mandarin"}
{:language/name "French"} {:language/name "German"})
Supports all kind of relationships as well
one-to-one relationship
(heql/query-single
db-adapter
[{[:city/city-id 3] [:city/city
{:city/country [:country/country]}]}])
one-to-many relationship
(heql/query-single
db-adapter
[{[:country/country-id 2] [:country/country
{:country/cities [:city/city]}]}])
many-to-many relationship
(heql/query-single
db-adapter
[{[:actor/actor-id 148] [:actor/first-name
{:actor/films [:film/title]}]}])
Pagination
Limit and Offset
(heql/query
db-adapter
'[{([] {:limit 2 :offset 2})
[:actor/actor-id :actor/first-name]}])
; returns
({:actor/actor-id 3, :actor/first-name "ED"}
{:actor/actor-id 4, :actor/first-name "JENNIFER"})
Both limit and offset can be applied on one-to-many and many-to-many relationships as well.
(heql/query-single
db-adapter
'[{[:country/country-id 2]
[:country/country
; one-to-many relationship
{(:country/cities {:limit 2 :offset 2})
[:city/city]}]}])
(heql/query
db-adapter
'[{[:actor/actor-id 148]
[:actor/first-name
; many-to-many relationship
{(:actor/films {:limit 1 :offset 2})
[:film/title]}]}])
Sorting
HoneyEQL supports sorting using the :order-by parameter. It takes a vector similar to HoneySQL and transform that to a corresponding ORDER BY SQL clause to sort the return value.
; sorting by :language/name
(heql/query
db-adapter
'[{([] {:order-by [:language/name]})
[:language/name]}])
; returns
({:language/name "English"} {:language/name "French"} {:language/name "German"}
{:language/name "Italian"} {:language/name "Japanese"} {:language/name "Mandarin"})
; sorting by :language/name in descending order
(heql/query
db-adapter
'[{([] {:order-by [[:language/name :desc]]}) ; vector of vector!
[:language/name]}])
; returns
({:language/name "Mandarin"} {:language/name "Japanese"} {:language/name "Italian"}
{:language/name "German"} {:language/name "French"} {:language/name "English"})
; sorting by multiple attributes
; :actor/first-name is ascending order and then :actor/last-name in descending order
(heql/query
db-adapter
'[{([] {:order-by [:actor/first-name [:actor/last-name :desc]]
:limit 2})
[:actor/first-name :actor/last-name]}])
; returns
({:actor/first-name "ADAM" :actor/last-name "HOPPER"}
{:actor/first-name "ADAM" :actor/last-name "GRANT"})
Type Mappings
While retrieving the data from the database, HoneyEQL coerce the return value to the corresponding JVM type as mentioned in the below table.
| Type | Postgres | MySQL |
|---|---|---|
| java.lang.Long | integer, int, int2 int4, smallint, smallserial, serial, serial2, serial4, bigint,int8,bigserial,serial8 | SMALLINT, MEDIUMINT, INT, TINYINT UNSIGNED, SMALLINT UNSIGNED, MEDIUMINT UNSIGNED, YEAR, INT UNSIGNED, BIGINT |
| java.math.BigDecimal | real, float4, float8, double precision,numeric,decimal | REAL, FLOAT, DOUBLE, DECIMAL, NUMERIC |
| java.lang.String | bit, bit varying, char, character varying, varchar, citext, bpchar, macaddr8, text, money | CHAR, VARCHAR, TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT, ENUM, SET, BINARY, VARBINARY, TINYBLOB, BLOB, LONGBLOB, BIT |
| java.lang.Boolean | boolean | TINYINT(1), BIT(1) |
| java.util.UUID | uuid | -- |
| java.time.LocalDate | date | DATE |
| java.time.LocalTime | time, time without time zone | TIME |
| java.time.OffsetTime | timetz, time with time zone | -- |
| java.time.LocalDateTime | timestamp, timestamp without time zone | DATETIME, TIMESTAMP |
| java.time.OffsetDateTime | timestamptz, timestamp with time zone | -- |
Metadata
In addition to querying, HoneyEQL supports quering the metadata of the database also.
(heql/meta-data db-adapter)
; returns
{:entities ...
:attributes ...
:namespaces ...}
The visual representation of the above data for the Postgres Sakila database is available in the below links
Can you improve this documentation?Edit on GitHub
cljdoc is a website building & hosting documentation for Clojure/Script libraries
× close