> For the complete documentation index, see [llms.txt](https://docs.neurelo.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.neurelo.com/neurelo-api-reference-rest/examples-of-neurelo-auto-generated-rest-api-endpoints/example-3-bookstore-application.md). # 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](https://www.databasestar.com/sample-bookstore-database/). 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
JSON Schema ```json { "objects": { "address": { "properties": { "address_id": { "type": "integer", "identifier": true }, "city": { "type": "string", "sourceType": [ "VarChar", 100 ], "nullable": true }, "country": { "nullable": true, "$ref": "#/objects/country", "relation": { "attrKey": [ "country_id" ], "foreignAttrKey": [ "country_id" ], "sourceName": "fk_addr_ctry", "onUpdate": "NoAction", "onDelete": "NoAction" } }, "country_id": { "type": "integer", "nullable": true }, "cust_order": { "type": "array", "items": { "$ref": "#/objects/cust_order" } }, "customer_address": { "type": "array", "items": { "$ref": "#/objects/customer_address" } }, "street_name": { "type": "string", "sourceType": [ "VarChar", 200 ], "nullable": true }, "street_number": { "type": "string", "sourceType": [ "VarChar", 10 ], "nullable": true } } }, "address_status": { "properties": { "address_status": { "type": "string", "sourceType": [ "VarChar", 30 ], "nullable": true }, "status_id": { "type": "integer", "identifier": true } } }, "author": { "properties": { "author_id": { "type": "integer", "identifier": true }, "author_name": { "type": "string", "sourceType": [ "VarChar", 400 ], "nullable": true }, "book_author": { "type": "array", "items": { "$ref": "#/objects/book_author" } } } }, "book": { "properties": { "book_author": { "type": "array", "items": { "$ref": "#/objects/book_author" } }, "book_id": { "type": "integer", "identifier": true }, "book_language": { "nullable": true, "$ref": "#/objects/book_language", "relation": { "attrKey": [ "language_id" ], "foreignAttrKey": [ "language_id" ], "sourceName": "fk_book_lang", "onUpdate": "NoAction", "onDelete": "NoAction" } }, "isbn13": { "type": "string", "sourceType": [ "VarChar", 13 ], "nullable": true }, "language_id": { "type": "integer", "nullable": true }, "num_pages": { "type": "integer", "nullable": true }, "order_line": { "type": "array", "items": { "$ref": "#/objects/order_line" } }, "publication_date": { "type": "string", "format": "date", "sourceType": "Date", "nullable": true }, "publisher": { "nullable": true, "$ref": "#/objects/publisher", "relation": { "attrKey": [ "publisher_id" ], "foreignAttrKey": [ "publisher_id" ], "sourceName": "fk_book_pub", "onUpdate": "NoAction", "onDelete": "NoAction" } }, "publisher_id": { "type": "integer", "nullable": true }, "title": { "type": "string", "sourceType": [ "VarChar", 400 ], "nullable": true } } }, "book_author": { "properties": { "author": { "$ref": "#/objects/author", "relation": { "attrKey": [ "author_id" ], "foreignAttrKey": [ "author_id" ], "sourceName": "fk_ba_author", "onUpdate": "NoAction", "onDelete": "NoAction" } }, "author_id": { "type": "integer" }, "book": { "$ref": "#/objects/book", "relation": { "attrKey": [ "book_id" ], "foreignAttrKey": [ "book_id" ], "sourceName": "fk_ba_book", "onUpdate": "NoAction", "onDelete": "NoAction" } }, "book_id": { "type": "integer" } }, "constraints": [ { "type": "identifier", "property": [ "book_id", "author_id" ], "sourceName": "pk_bookauthor" } ] }, "book_language": { "properties": { "book": { "type": "array", "items": { "$ref": "#/objects/book" } }, "language_code": { "type": "string", "sourceType": [ "VarChar", 8 ], "nullable": true }, "language_id": { "type": "integer", "identifier": true }, "language_name": { "type": "string", "sourceType": [ "VarChar", 50 ], "nullable": true } } }, "country": { "properties": { "address": { "type": "array", "items": { "$ref": "#/objects/address" } }, "country_id": { "type": "integer", "identifier": true }, "country_name": { "type": "string", "sourceType": [ "VarChar", 200 ], "nullable": true } } }, "cust_order": { "properties": { "address": { "nullable": true, "$ref": "#/objects/address", "relation": { "attrKey": [ "dest_address_id" ], "foreignAttrKey": [ "address_id" ], "sourceName": "fk_order_addr", "onUpdate": "NoAction", "onDelete": "NoAction" } }, "customer": { "nullable": true, "$ref": "#/objects/customer", "relation": { "attrKey": [ "customer_id" ], "foreignAttrKey": [ "customer_id" ], "sourceName": "fk_order_cust", "onUpdate": "NoAction", "onDelete": "NoAction" } }, "customer_id": { "type": "integer", "nullable": true }, "dest_address_id": { "type": "integer", "nullable": true }, "order_date": { "type": "string", "format": "date-time", "sourceType": [ "Timestamp", 6 ], "nullable": true }, "order_history": { "type": "array", "items": { "$ref": "#/objects/order_history" } }, "order_id": { "type": "integer", "default": { "function": "autoincrement" }, "identifier": true }, "order_line": { "type": "array", "items": { "$ref": "#/objects/order_line" } }, "shipping_method": { "nullable": true, "$ref": "#/objects/shipping_method", "relation": { "attrKey": [ "shipping_method_id" ], "foreignAttrKey": [ "method_id" ], "sourceName": "fk_order_ship", "onUpdate": "NoAction", "onDelete": "NoAction" } }, "shipping_method_id": { "type": "integer", "nullable": true } } }, "customer": { "properties": { "cust_order": { "type": "array", "items": { "$ref": "#/objects/cust_order" } }, "customer_address": { "type": "array", "items": { "$ref": "#/objects/customer_address" } }, "customer_id": { "type": "integer", "identifier": true }, "email": { "type": "string", "sourceType": [ "VarChar", 350 ], "nullable": true }, "first_name": { "type": "string", "sourceType": [ "VarChar", 200 ], "nullable": true }, "last_name": { "type": "string", "sourceType": [ "VarChar", 200 ], "nullable": true } } }, "customer_address": { "properties": { "address": { "$ref": "#/objects/address", "relation": { "attrKey": [ "address_id" ], "foreignAttrKey": [ "address_id" ], "sourceName": "fk_ca_addr", "onUpdate": "NoAction", "onDelete": "NoAction" } }, "address_id": { "type": "integer" }, "customer": { "$ref": "#/objects/customer", "relation": { "attrKey": [ "customer_id" ], "foreignAttrKey": [ "customer_id" ], "sourceName": "fk_ca_cust", "onUpdate": "NoAction", "onDelete": "NoAction" } }, "customer_id": { "type": "integer" }, "status_id": { "type": "integer", "nullable": true } }, "constraints": [ { "type": "identifier", "property": [ "customer_id", "address_id" ], "sourceName": "pk_custaddr" } ] }, "order_history": { "properties": { "cust_order": { "nullable": true, "$ref": "#/objects/cust_order", "relation": { "attrKey": [ "order_id" ], "foreignAttrKey": [ "order_id" ], "sourceName": "fk_oh_order", "onUpdate": "NoAction", "onDelete": "NoAction" } }, "history_id": { "type": "integer", "default": { "function": "autoincrement" }, "identifier": true }, "order_id": { "type": "integer", "nullable": true }, "order_status": { "nullable": true, "$ref": "#/objects/order_status", "relation": { "attrKey": [ "status_id" ], "foreignAttrKey": [ "status_id" ], "sourceName": "fk_oh_status", "onUpdate": "NoAction", "onDelete": "NoAction" } }, "status_date": { "type": "string", "format": "date-time", "sourceType": [ "Timestamp", 6 ], "nullable": true }, "status_id": { "type": "integer", "nullable": true } } }, "order_line": { "properties": { "book": { "nullable": true, "$ref": "#/objects/book", "relation": { "attrKey": [ "book_id" ], "foreignAttrKey": [ "book_id" ], "sourceName": "fk_ol_book", "onUpdate": "NoAction", "onDelete": "NoAction" } }, "book_id": { "type": "integer", "nullable": true }, "cust_order": { "nullable": true, "$ref": "#/objects/cust_order", "relation": { "attrKey": [ "order_id" ], "foreignAttrKey": [ "order_id" ], "sourceName": "fk_ol_order", "onUpdate": "NoAction", "onDelete": "NoAction" } }, "line_id": { "type": "integer", "default": { "function": "autoincrement" }, "identifier": true }, "order_id": { "type": "integer", "nullable": true }, "price": { "type": "string", "format": "decimal", "sourceType": [ "Decimal", 5, 2 ], "nullable": true } } }, "order_status": { "properties": { "order_history": { "type": "array", "items": { "$ref": "#/objects/order_history" } }, "status_id": { "type": "integer", "identifier": true }, "status_value": { "type": "string", "sourceType": [ "VarChar", 20 ], "nullable": true } } }, "publisher": { "properties": { "book": { "type": "array", "items": { "$ref": "#/objects/book" } }, "publisher_id": { "type": "integer", "identifier": true }, "publisher_name": { "type": "string", "sourceType": [ "VarChar", 400 ], "nullable": true } } }, "shipping_method": { "properties": { "cost": { "type": "string", "format": "decimal", "sourceType": [ "Decimal", 6, 2 ], "nullable": true }, "cust_order": { "type": "array", "items": { "$ref": "#/objects/cust_order" } }, "method_id": { "type": "integer", "identifier": true }, "method_name": { "type": "string", "sourceType": [ "VarChar", 100 ], "nullable": true } } } } } ```
### GET endpoint examples * Get one author by id 19283 ```json GET https://$ENV_API_URL/rest/author/19283 ?select={"$scalars": true, "$related": true} ``` * Select first 10 publisher and books ordered by publisher name ```json GET https://$ENV_API_URL/rest/publisher ?select={"$scalars": true, "book": {"$scalars": true}} &order_by=[{"publisher_name": "asc"}] &take=10 ``` * Select customer scalars and their orders. Filter by email containing "[cdbaby.com](http://cdbaby.com/)" ```json 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 ```json 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 ```json 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=3 ``` * Get list of books not translated to "English” (.*English.* - includes “English”, “British English”, “United States English”), or “Spanish ```json 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 ```json 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 ```json 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 ```json 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 ```json POST https://$ENV_API_URL/rest/customer ?select={"$scalars": true, "customer_address": {"address": {"$scalars": true}}} { "customer_id": 19201, "first_name": "Watson", "email": "watson@solvesmysteries.com", "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 ```json 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 ```json 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 ```json 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 ```json DELETE https://$ENV_API_URL/rest/publisher ?filter={"book": {"some": {"title": "Forbidden text as of now"}}} ```