Duct handler.sql

A Duct library that provides a way of constructing simple Ring
handler functions that execute SQL expressions.

Installation

To install, add the following to your project :dependencies:

[duct/handler.sql "0.4.0"]

Usage

This library is designed to be used with a routing library with Duct
bindings, such as Ataraxy.

Querying

Querying the database generally follows a HTTP GET request. There
are two keys for creating handlers that query the database:

:duct.handler.sql/query - for multiple results
:duct.handler.sql/query-one - for when you have only one result

The simplest usage is a handler that queries the database the same way
each time:

{[:duct.handler.sql/query :example.handler.product/list]
 {:db  #ig/ref :duct.database/sql
  :sql ["SELECT * FROM products"]}}

In the above example, a composite key is used to provide a unique
identifier for the handler.

The :db option should be a duct.database.sql.Boundary record, and
the :sql option should be a clojure.java.jdbc query vector.

If you omit the :db option, the ig/prep stage will default it to
#ig/ref :duct.database/sql. This means you can write:

{[:duct.handler.sql/query :example.handler.product/list]
 {:sql ["SELECT * FROM products"]}}

If you want to change the query based on the request, you can
destructure the parameters you want in the :request option:

{[:duct.handler.sql/query-one :example.handler.product/find]
 {:db      #ig/ref :duct.database/sql
  :request {{:keys [id]} :route-params}
  :sql     ["SELECT * FROM products WHERE id = ?" id]}}

The response can also be altered. The :rename option is available
for renaming keys returned in the result set:

{[:duct.handler.sql/query :example.handler.product/list]
 {:db     #ig/ref :duct.database/sql
  :sql    ["SELECT id, name FROM products"]
  :rename {:id :product/id, :name :product/name}}}

The :hrefs option adds hypertext references based on URI
Templates:

{[:duct.handler.sql/query :example.handler.product/list]
 {:db    #ig/ref :duct.database/sql
  :sql   ["SELECT id, name FROM products"]
  :hrefs {:self "/products{/id}"}}}

The :hrefs key takes template parameters from the result fields, and
from the request destructuring.

Finally, the :remove key allows keys to be removed from the
response. This is useful if you want a key to be used in a href, but
not to show up in the final response:

{[:duct.handler.sql/query :example.handler.product/list]
 {:db     #ig/ref :duct.database/sql
  :sql    ["SELECT id, name FROM products"]
  :hrefs  {:self "/products{/id}"}
  :remove [:id]}}

Updating

Sometimes a HTTP request will alter the database. There are two keys
for creating handlers that update the database:

:duct.handler.sql/insert - for inserting rows
:duct.handler.sql/execute - for updating or deleting rows

The :duct.handler.sql/insert key is designed to respond to a HTTP
POST event and send a “Created” 201 response with a “Location”
header created from the generated ID of an INSERT. For example:

{[:duct.handler.sql/insert :example.handler.product/create]
 {:db       #ig/ref :duct.database/sql
  :request  {{:strs [name]} :form-params}
  :sql      ["INSERT INTO products (name) VALUES (?)" name]
  :location "/products{/last_insert_rowid}"}}

The generated ID is returned differently depending on the database
being used. For SQLite, the ID is returned in the
last_insert_rowid() field. Because () are not valid characters in
URI templates, these are removed when the field name is sanitized.

The :duct.handler.sql/execute doesn’t have to worry about generated
keys; it’s designed to report to HTTP DELETE and PUT requests. If
the SQL updates one or more rows, a “No Content” 204 response is
returned, otherwise, if zero rows are updated, a 404 response is
returned.

For example:

{[:duct.handler.sql/execute :example.handler.product/update]
 {:db       #ig/ref :duct.database/sql
  :request  {{:keys [id]} :route-params, {:strs [name]} :form-params}
  :sql      ["UPDATE products SET name = ? WHERE id = ?" name id]}
 [:duct.handler.sql/execute :example.handler.product/destroy]
 {:db       #ig/ref :duct.database/sql
  :request  {{:keys [id]} :route-params}
  :sql      ["DELETE FROM products WHERE id = ?" id]}}

Full Example

Together with a router like Ataraxy, the configuration might look
something like:

{:duct.core/project-ns example
 :duct.core/environment :production
 :duct.module.web/api {}
 :duct.module/sql     {}
 :duct.module/ataraxy
 {"/products"
  {[:get]        [:product/list]
   [:get "/" id] [:product/find ^uuid id]
   [:post {{:strs [name]} :form-params}]
   [:product/create name]
   [:put "/" id {{:strs [name]} :form-params}]
   [:product/update ^uuid id name]
   [:delete "/" id]
   [:product/destroy ^uuid id]}}
 [:duct.handler.sql/query :example.handler.product/list]
 {:sql ["SELECT * FROM products"]}
 [:duct.handler.sql/query-one :example.handler.product/find]
 {:request {[_ id] :ataraxy/result}
  :sql     ["SELECT * FROM products WHERE id = ?" id]}
 {[:duct.handler.sql/insert :example.handler.product/create]
 {:request  {[_ name] :ataraxy/result}
  :sql      ["INSERT INTO products (name) VALUES (?)" name]
  :location "/products{/last_insert_rowid}"}}
{[:duct.handler.sql/execute :example.handler.product/update]
 {:request {[_ id name] :ataraxy/result}
  :sql     ["UPDATE products SET name = ? WHERE id = ?" name id]}
 [:duct.handler.sql/execute :example.handler.product/destroy]
 {:request {[_ id] :ataraxy/result}
  :sql     ["DELETE FROM products WHERE id = ?" id]}}}

Note that the :db key can be omitted in this case, because the
:duct.module/sql module will automatically populate the handlers
with a database connection.

Caveats

This library can produce simple handlers that require only the
information present in the request map. When paired with a good
routing library, this can be surprisingly powerful.

However, don’t overuse this library. If your requirements for a
handler are more complex, then create your own init-key method for
the handler. It’s entirely possible, even likely, that your app will
contain handlers created via this library, and handlers that are
created through your own init-key methods.

License

Distributed under the Eclipse Public License either version 1.0 or (at
your option) any later version.