Example 3 - "Bookstore” application
Schema
We are going to use this schema as the sample schema for the examples that follow - This is the Gravity Bookstore sample database from DatabaseStar.
This schema contains the following tables:
book: a list of all books available in the store.
book_author: stores the authors for each book, which is a many-to-many relationship.
author: a list of all authors.
book_language: a list of possible languages of books.
publisher: a list of publishers for books.
customer: a list of the customers of the Gravity Bookstore.
customer_address: a list of addresses for customers, as a customer can have more than one address, and an address has more than one customer.
address_status: a list of statuses for an address, because addresses can be current or old.
address: a list of addresses in the system.
country: a list of countries that addresses are in.
cust_order: a list of orders placed by customers.
order_line: a list of books that are a part of each order.
shipping_method: the possible shipping methods for an order.
order_history: the history of an order, such as ordered, cancelled, delivered.
order_status: the possible statuses of an order.
Here is the ERD of this sample bookstore database:
This is the schema in the Neurelo Schema Language JSON Specification format
GET endpoint examples
Get one author by id 19283
GET https://$ENV_API_URL/rest/author/19283 ?select={"$scalars": true, "$related": true}Select first 10 publisher and books ordered by publisher name
GET https://$ENV_API_URL/rest/publisher ?select={"$scalars": true, "book": {"$scalars": true}} &order_by=[{"publisher_name": "asc"}] &take=10Select customer scalars and their orders. Filter by email containing "cdbaby.com"
GET https://$ENV_API_URL/rest/customer ?select={"$scalars": true, "cust_order": {"order_id": true, "order_line": {"$scalars": true}}} &filter={"email": {"contains": "@cdbaby.com"}}Select book_id and the number of books sold where sales > 8
GET https://$ENV_API_URL/rest/order_line ?select={"book_id": true} &aggregate={"_count": ["line_id"]} &group_by=["book_id"] &having={"book_id": {"_count": {"gt": 8}}}Get list of customer order with order_history and scalars. Filter by status is Order Received
GET https://$ENV_API_URL/rest/cust_order ?select={"order_history": {"order_status": {"$scalars": true}, "status_date": true}, "$scalars": true} &filter={"order_history": {"every": {"order_status": {"status_value": {"equals": "Order Received"}}}}} &take=3Get list of books not translated to "English” (.English. - includes “English”, “British English”, “United States English”), or “Spanish
GET https://$ENV_API_URL/rest/book ?select={"book_id": true, "title": true} &filter={"book_language": {"NOT": [{"language_name": {"contains": "English"}}, {"language_name": "Spanish"}]}}
POST endpoint examples
Create a new book with author
POST https://$ENV_API_URL/rest/book ?select={"$scalars": true, "$related": true} { "book_id": 19283, "title": "DUNE: A new hope", "book_author": { "create": { "author": { "create": { "author_id": 19283, "author_name": "Frank Lucas" } } } } }Create many authors
POST https://$ENV_API_URL/rest/author [ { "author_id": 19284, "author_name": "Francis Hube" }, { "author_id": 19285, "author_name": "Brandon Stevens" }, { "author_id": 19286, "author_name": "Red Roberts" } ]Create a new author with many books
POST https://$ENV_API_URL/rest/author ?select={"$scalars": true, "$related": true} { "author_id": 19288, "author_name": "Arthur Doyle", "book_author": { "create": [ { "book": { "create": { "book_id": 19286, "title": "Sherlock Holmes: Mistery one" } } }, { "book": { "create": { "book_id": 19287, "title": "Sherlock Holmes: Mistery two" } } } ] } }Create a customer with address and address status
POST https://$ENV_API_URL/rest/customer ?select={"$scalars": true, "customer_address": {"address": {"$scalars": true}}} { "customer_id": 19201, "first_name": "Watson", "email": "[email protected]", "customer_address": { "create": { "status_id": 1, "address": { "create": { "address_id": 19201, "city": "San Francisco", "street_name": "Big Street", "street_number": "1" } } } } }
PATCH endpoint examples
Update a single address
PATCH https://$ENV_API_URL/rest/address/19200 ?select={"$scalars": true} { "city": "London", "street_name": "Baker Street", "street_number": "221B" }Update multiple addresses based on filter
PATCH https://$ENV_API_URL/rest/address ?filter={"city": "London"} { "city": "London", "street_name": "Baker Street", "street_number": "221B" }Update book title where title and language
PATCH https://$ENV_API_URL/rest/book ?filter={"AND": {"title": "Bed time stories", "book_language": {"language_name": "Russian"}}} { "isbn13": "the-new-isbn" }
DELETE endpoint examples
Delete publishers that have forbidden books published
DELETE https://$ENV_API_URL/rest/publisher ?filter={"book": {"some": {"title": "Forbidden text as of now"}}}
Last updated