项目作者: duct-framework

项目描述 :
Duct library for building simple database-driven handlers
高级语言: Clojure
项目地址: git://github.com/duct-framework/handler.sql.git
创建时间: 2017-09-02T21:34:16Z
项目社区:https://github.com/duct-framework/handler.sql

开源协议:

下载


Duct handler.sql

Build Status

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:

  1. [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:

  1. {[:duct.handler.sql/query :example.handler.product/list]
  2. {:db #ig/ref :duct.database/sql
  3. :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:

  1. {[:duct.handler.sql/query :example.handler.product/list]
  2. {: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:

  1. {[:duct.handler.sql/query-one :example.handler.product/find]
  2. {:db #ig/ref :duct.database/sql
  3. :request {{:keys [id]} :route-params}
  4. :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:

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

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

  1. {[:duct.handler.sql/query :example.handler.product/list]
  2. {:db #ig/ref :duct.database/sql
  3. :sql ["SELECT id, name FROM products"]
  4. :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:

  1. {[:duct.handler.sql/query :example.handler.product/list]
  2. {:db #ig/ref :duct.database/sql
  3. :sql ["SELECT id, name FROM products"]
  4. :hrefs {:self "/products{/id}"}
  5. :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:

  1. {[:duct.handler.sql/insert :example.handler.product/create]
  2. {:db #ig/ref :duct.database/sql
  3. :request {{:strs [name]} :form-params}
  4. :sql ["INSERT INTO products (name) VALUES (?)" name]
  5. :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:

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

Full Example

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

  1. {:duct.core/project-ns example
  2. :duct.core/environment :production
  3. :duct.module.web/api {}
  4. :duct.module/sql {}
  5. :duct.module/ataraxy
  6. {"/products"
  7. {[:get] [:product/list]
  8. [:get "/" id] [:product/find ^uuid id]
  9. [:post {{:strs [name]} :form-params}]
  10. [:product/create name]
  11. [:put "/" id {{:strs [name]} :form-params}]
  12. [:product/update ^uuid id name]
  13. [:delete "/" id]
  14. [:product/destroy ^uuid id]}}
  15. [:duct.handler.sql/query :example.handler.product/list]
  16. {:sql ["SELECT * FROM products"]}
  17. [:duct.handler.sql/query-one :example.handler.product/find]
  18. {:request {[_ id] :ataraxy/result}
  19. :sql ["SELECT * FROM products WHERE id = ?" id]}
  20. {[:duct.handler.sql/insert :example.handler.product/create]
  21. {:request {[_ name] :ataraxy/result}
  22. :sql ["INSERT INTO products (name) VALUES (?)" name]
  23. :location "/products{/last_insert_rowid}"}}
  24. {[:duct.handler.sql/execute :example.handler.product/update]
  25. {:request {[_ id name] :ataraxy/result}
  26. :sql ["UPDATE products SET name = ? WHERE id = ?" name id]}
  27. [:duct.handler.sql/execute :example.handler.product/destroy]
  28. {:request {[_ id] :ataraxy/result}
  29. :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

Copyright © 2017 James Reeves

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