Pie Crust🔗ℹ

Pie Crust is an automated (RESTful) CRUD API generator for use with the library for Web Applications in Racket.

Warning: This library should be treated as an alpha release. It has not been stress-tested or bullet-proofed. Bug reports, feature requests, and pull requests are welcomed at the Github repo.

With all that said, feel free to try it out and have fun! Use cases and ideas for improvement are welcome, as noted above.

1 Quick Start🔗ℹ

Pie Crust provides a lightweight way to bind an SQL database table(s) to a REST-based API providing responses in JSON format. It enables you to generate API endpoints for creating, reading/querying, updating, and deleting data in the underlying table(s) by defining a dispatching function (based on dispatch-rules) that can be used with serve/servlet.

1.1 Minimal Example🔗ℹ

(Running code for this example is in the piecrust/tests/minimal-example.rkt file.)

Consider an SQLite database set up like this:

The following code will launch a JSON-based API on http://localhost:8000.

(require piecrust)

(define dbc (sqlite3-connect ...))

(define store-api

(create-api-manager "/api-endpoint"

dbc          ; database connection

"Stores"     ; table name

; table column schema

`((["Store_id" id] integer "Internal ID")

("Name" text #f "Store name")

("Address" text))

"Store_id")) ; primary key

(serve/servlet (api-dispatcher store-api)

#:servlet-regexp #rx""

#:servlet-path "/")

There are several overloaded forms to specifying the column schema. See the full documentation for create-api-manager below for details.

1.1.1 Generated endpoints and request formats🔗ℹ

With the server above running, the follow endpoints are provided:

• (GET)     /api-endpoint
  Retrieves all data records in the table as a list of JSON dictionaries.

  Query Parameters
  Name and value pattern	Example
  _fields=<field-name>,...	/api-endpoint?_fields=id,name
  Retrieves only the specified fields from each record. Field names are case-sensitive. If only one field is specified, then a list of values is returned (rather than a list of dictionaries).
  _distinct=1	/api-endpoint?_distinct=1
  Retrieves only unique records with the selected set of fields (i.e. does not include duplicates).
  _sort=<field-name>	/api-endpoint?_sort=address
  _order=[asc|desc]	/api-endpoint?_sort=address&_order=desc
  Sorts records by the specified field in ascending/descending order.
  *=<data>	/api-endpoint?*=street
  Searches for the specified data in any of the fields of each record. If <data> contains an asterisk (*), it is treated as a wildcard (corresponding to % in SQL syntax). The asterisk is used because escaping % in a URL can be annoying.
  <field-name>=<data>	/api-endpoint?name=LLC
  Searches for the specified data in the named field. If multiple fields are specified as separate query parameters, the criteria are combined with AND. If a * query parameter is specified, any named fields are ignored. See the note above about the wildcard character in <data>.
  _joins=1	/api-endpoint?_joins=1
  Includes nested data from joined tables in the output. By default, joins are not performed and included. See Details on the #:joins clause.

  
  

• (GET)     /api-endpoint/<id>
  Retrieves the single data record with the given primary key value (<id> as a JSON dictionary. Nested data from joined tables is included in the output. See Details on the #:joins clause.

  Query Parameters
  Name and value pattern	Example
  _fields=<field-name>,...	/api-endpoint/42?_fields=name,address
  Retrieves only the specified fields. Field names are case-sensitive.

  
  

• (POST)     /api-endpoint
  Adds a new row to the database table. A JSON dictionary must be provided in the body of the POST request. For example:

  { "name" : "CoCost", "address" : "987 Center Lane" }

  Upon success, responds with a dictionary containing the primary key field and value of the newly added row.
  

• (PUT)     /api-endpoint/<id>
  Updates the row identified by the specified primary key value (<id>). A JSON dictionary must be provided in the body of the POST request with the fields and values that are to be updated.
  

• (DELETE)     /api-endpoint/<id>
  Deletes the row identified by the specified primary key value (<id>).
  

1.1.2 Notes on the generated API🔗ℹ

• The default for the JSON keys generated and used for query parameters is to lowercase the specified SQL column names (e.g. Address becomes address).

• On a POST request to create a new record, the primary key field and its value is ignored if it is specified in the JSON dictionary in the body of the request.

• Use api-dispatcher procedure to extract a request handling function from the API generated by create-api-manager, suitable for use to launch a server.

1.2 Extended Example🔗ℹ

(Running code for this example is in the piecrust/tests/shopping-list-example.rkt file.)

Consider an SQLite database set up with two tables like this:

The following code will set up and launch a JSON-based API on http://localhost:8000.

(require piecrust)

(define dbc (sqlite3-connect ...))

; A default generic request handler for root path "/" requests

(define (main-servlet req)

(if (equal? "" (path/param-path (first (url-path (request-uri req)))))

(response/xexpr `(body (h1 "Shopping List - API Endpoints")

(a ([href "/api/stores"]) "Stores") (br)

(a ([href "/api/v1/items"]) "Items")))

(next-dispatcher))) ; hand everything else off to the next dispatcher

; Stores API endpoint

(define store-api

(create-api-manager "api/stores" ; API endpoint

dbc          ; database connection

"Stores"     ; table name

`((["Store_id" id] integer "Internal ID")

("Name" text "Store name")

("Address" text))  ; table columns

"Store_id" ; primary key

#:request-wrapper

(λ (req crud-op api-handler)

(match crud-op

['delete (response/jsexpr "not supported" #:code 400)]

[_ (api-handler req)]))

#:fallback-request-handler main-servlet))

; Items API endpoint

(define items-api

(create-api-manager "api/v1/items"

dbc

"Items"

`(("ID" integer)

(["Store_id" store-id] integer #f "Store ID")

("Name" text #f) ; must be non-null

("Brand" text))

"ID"

#:response-wrapper

(λ (js crud-op)

(response/jsexpr (make-immutable-hasheq

`((meta . #hasheq((endpoint . "api/v1/items")))

(data . ,js)))))

#:error-wrapper

(λ (exn crud-op resp-code jsexpr)

(response/jsexpr

(make-immutable-hasheq `((operation . ,(symbol->string crud-op))

(message . ,jsexpr)))

#:code resp-code))

#:fallback-request-handler (api-dispatcher store-api)))

(serve/servlet (api-dispatcher items-api)

#:servlet-regexp #rx"" #:servlet-path "/" #:launch-browser? #f)

1.2.1 API hooks🔗ℹ

The extended example above demonstrates several hooks that are supplied with the create-api-manager form.

• Request handling functions can be chained together using the #:fallback-request-handler. In this case, the Items API endpoint path is handled first. If the request URL path does not match that, it will be delegated to the (api-dispatcher store-api) handler. If the request URL path does not match "api/stores" either, then main-servlet will be used to handle the request.

• The #:request-wrapper option provides a hook to install a function that can examine and build a response to the request prior to the generated API handlers. The supplied function takes the original request instance, a crud-op/c symbol, and a callback function (which is the one generated by the API to handle that type of request). The supplied function may decide to either (1) produce its own response (perhaps based on authentication results, or, like in this example, to deny access to the 'delete functionality of the Stores API), (2) construct a modified request object, or (3) pass the request as it on to the default API handler function.

• In a similar vein, the #:response-wrapper hook allows one to supply a function that can examine the final JSON result produced by the API and decide how to modify it, if desired, to produce the final response?. In the example, the Items API wraps all responses to the CRUD operations in another layer of JSON with some additional metadata.

• The generated API will handle exn:fail:api:error and exn:fail:sql exceptions with a generic response (and a 400 or 500 code, respectively). To construct an alternate error response, the #:error-wrapper option allows the user to supply a function that takes the exception object, the CRUD operation that was being handled, and the response code and message that the API generated, and can decide how to construct and produce their own response.

2 API Documentation🔗ℹ

procedure crud-op/c : contract?

= (symbols 'create 'query 'read 'update 'delete)

The endpoint-path should be a slash-separated path string specifying the base endpoint from which the API will be served, for example, "/myapi/v1".

sql-column-name should be the name of the column in the SQL table. json-key-name will be used as the key label for matching fields in the JSON output. Requests to create, update, or query data must also use the JSON key name, as the SQL column name is only used internally in the API to bind to the database. If a json-key-name is not specified, the function supplied to the #:field-name-generator option is used to build the JSON key name from the SQL column name. The default generator is (compose string->symbol string-downcase). The null-ok? parameter corresponds to (the absence of) a NOT NULL constraint in the SQL table. If not specified, it is assumed to be #t. The column description is only used for metadata purposes, and if not specified is just the column name.

See the API hooks section for descriptions of the optional keyword arguments.

Details on the #:joins clause

The #:joins clause specifies additional tables to which the primary one may be joined, based on its primary key. It supports three scenarios, described in the next paragraph. In the description that follows, "primary table" refers to the table declared in the create-api-manager clause. The "secondary table" is the one from which associated data is nested in the primary JSON output. Example code for each of the three scenarios is provided in the piecrust/tests/book-author-example.rkt file of the Github repo.)

procedure join-spec/c : contract?

=  (or/c (list/c symbol? (list/c string? (listof col-schema)) string?)       (list/c symbol? (list/c string? (listof col-schema) string?) string?)       (list/c symbol? (list/c string? (listof col-schema) string?)               (list/c string? string?) string?))

• One-to-many (where a foreign key in the secondary table links to the primary key of the primary table) Specify the JSON key with which the joined data will be associated (as a nested JSON list in the returned dictionary for each row of data), the name of the foreign table, a list of columns to include from matching rows in the foreign table, and the name of the foreign key.

  `((json-key ["sec-tbl" (col-schema ...)] "for-key/sec-tbl"))

• Many-to-one (where a foreign key in the primary table links to the primary key of a secondary table) Specify the JSON key with which the joined data will be associated (as a nested dictionary - not a list), the name of the foreign table, its list of columns to include, and its primary key; then, the foreign key in the primary table.

  `((json-key ["sec-tbl" (col-schema ...) "pri-key/sec-tbl"] "for-key/prim-tbl"))

• Many-to-many (where a junction table is used to associate pairs of rows from the primary and a secondary table) Provide the JSON key with which the data will be associated in the output; the name of the secondary table, its list of columns to select, and the primary key of the secondary table; and then the name of the junction table and the foreign key in the junction table that corresponds to the primary key of the secondary table; and, finally, the name of the (second) foreign key in the junction table that corresponds to the primary key of the primary table.

  `((json-key ["sec-tbl" (col-schema ...) "pri-key/sec-tbl"]

  ["j-tbl" "for-key-of-sec-tbl/j-tbl"]

  "for-key-of-prim-tbl/j-tbl"))