Example 2 - "DVD Rentals" application
Schema
This is a DVD Rentals example database schema from PostgreSQL Tutorial.
There are 15 tables in the DVD Rental database:
actor – stores actors data including first name and last name.
film – stores film data such as title, release year, length, rating, etc.
film_actor – stores the relationships between films and actors.
category – stores film’s categories data.
film_category- stores the relationships between films and categories.
store – contains the store data including manager staff and address.
inventory – stores inventory data.
rental – stores rental data.
payment – stores customer’s payments.
staff – stores staff data.
customer – stores customer data.
address – stores address data for staff and customers
city – stores city names.
country – stores country names.
Here is the ERD of the sample DVD rentals database:
This is the schema in the Neurelo Schema Language JSON Specification format
GET endpoint examples
Retrieve all actors
GET https://$ENV_API_URL/rest/actorsRetrieve a specific actor by id
GET https://$ENV_API_URL/rest/actor/159or
GET https://$ENV_API_URL/rest/actor?filter={"actor_id":159}Retrieve a specific actor
GET https://$ENV_API_URL/rest/actor?filter={"first_name":"John", "last_name":"Wayne"}Retrieve 5 customers
GET https://$ENV_API_URL/rest/customer?take=5Retrieve all films and their actors
GET https://$ENV_API_URL/rest/film?select={"film_actor": {"actor":{"$scalars": true}}, "$scalars": true}Retrieve stores and their employees
GET https://$ENV_API_URL/rest/store?select={"staff":{"$scalars": true}, "$scalars": true}Retrieve rentals and films
GET https://$ENV_API_URL/rest/rental?select={"inventory":{"film": {"$scalars": true}}, "$scalars": true}&take=5Retrieve films by category
GET https://$ENV_API_URL/rest/category?select={"film_category":{"film": {"$scalars": true}}, "$scalars": true}&take=5Retrieve customers that have rented a movie after 2006-01-01
GET https://$ENV_API_URL/rest/category?select={"$scalars": true}&filter={"rental": {"some": {"rental_date": {"gte": "2006-01-01T00:00:00.000Z"}}}}Retrieve customers that have more than 35 rentals
GET https://$ENV_API_URL/rest/category?select={"customer_id": true}&aggregate={"_count": ["rental_id"]}&group_by=["customer_id"]&having={"rental_id": {"_count": {"gt": 35}}}Retrieve count of films in english by category
GET https://$ENV_API_URL/rest/category?select={"category_id": true}&filter={"film": {"language": {"name": {"contains": "English"}}}}&aggregate={"_count": ["film_id"]}&group_by=["category_id"]Get all films where (special features contains "Commentaries" OR length > 160) AND replacement cost > 29
GET https://$ENV_API_URL/rest/category?select={"$scalars": true}&filter={"OR": [{"special_features": {"has": "Commentaries"}}, {"length": {"gt": 160}}], "replacement_cost": {"gte": 29}}
POST endpoint examples
Create a new actor
POST https://$ENV_API_URL/rest/actor { "content": "This is my first blog post", "title": "My first blog post" }Create multiple rentals
POST https://$ENV_API_URL/rest/rental [ { "customer_id": 2, "inventory_id": 1, "rental_date": "2023-07-11T14:50:58.951Z", "staff_id": 1 }, { "customer_id": 524, "inventory_id": 2, "rental_date": "2023-07-11T14:50:58.951Z", "staff_id": 1 } ]Create a film and related data
POST https://$ENV_API_URL/rest/film?select={"$scalars": true, "language": {"$scalars": true}, "film_category": {"category": {"$scalars": true}}, "film_actor": {"actor": {"$scalars": true}}} { "title": "Shrek 3", "language": { "connect": { "language_id": 2 } }, "film_category": { "create": { "category": { "create": { "name": "Swamp" } } } }, "film_actor": { "create": { "actor": { "create": { "first_name": "Prince", "last_name": "Arthur" } } } } }
PATCH endpoint examples
Update a film
PATCH https://$ENV_API_URL/rest/film/1001?select={"film_actor": {"actor":{"$scalars": true}}, "$scalars": true} { "description": "Book of Life" }
DELETE endpoint examples
Delete an actor
DELETE https://$ENV_API_URL/rest/actor/201?select={"$scalars": true, "$related": true}Delete films by name
DELETE https://$ENV_API_URL/rest/film?filter={"title": {"contains": "Shark"}}
Last updated