Bookstore Database and API Implementation

This repository contains the design and implementation of a bookstore database using Supabase, along with a RESTful API for querying book information. The database includes two tables: Books and Authors. A set of advanced SQL queries is also provided. Additionally, a NestJS-based RESTful API has been developed for querying and filtering book data.

Project Overview

Part 1: Database Design and Implementation

The database consists of two relational tables:

• Books: Contains information about books, including the title, price, publication date, and author details.
• Authors: Contains information about authors, including their name, country, and a foreign key relation to the books table.

Tables:

• Books table has columns for:

  • book_id: Primary key (auto-incremented).
  • title: Name of the book.
  • author_id: Foreign key referencing the Authors table.
  • price: Price of the book.
  • publish_date: Publication date of the book.

• Authors table has columns for:

  • author_id: Primary key (auto-incremented).
  • name: Name of the author.
  • country: Country of the author.
  • user_id: Foreign key referencing the Supabase auth.users(id) table for user authentication.

Part 2: RESTful Edge Function

A RESTful API endpoint GET /books is implemented using NestJS and Supabase Edge Functions. This endpoint supports the following features:

• Filtering: Books can be filtered by author_id.
• Sorting: Books can be sorted by publish_date.
• Pagination: Books can be retrieved in paginated results.

API Features:

• GET /books:
  • Supports filtering books by author using an optional query parameter: author_id.
  • Supports sorting books by publish date using an optional query parameter: sort.
  • Supports pagination with parameters page and limit.
  • Secure access using Supabase Auth for authenticated users.

Postman Collection: You can test the API using this Postman link.

Part 3: Advanced SQL Queries

Here are the SQL queries for the advanced tasks:

Query 1: Find all authors who have published more than 5 books

SELECT
  author_id
FROM
  books
GROUP BY
  author_id
HAVING
  COUNT(*) > 5;

Explanation: This query groups the books by author_id and selects only those authors who have published more than 5 books.

Query 2: Calculate the average book price for each country

SELECT
  country,
  AVG(price) AS average_price
FROM
  books
JOIN authors ON books.author_id = authors.author_id
GROUP BY
  country;

Explanation: This query joins the books table with the authors table to calculate the average price of books per country.

Query 3: Retrieve a list of books including author names, sorted by price in descending order

SELECT 
  b.title, 
  a.name AS author_name, 
  b.price, 
  b.publish_date
FROM books b
JOIN authors a ON b.author_id = a.author_id
ORDER BY b.price DESC;

Explanation: This query retrieves the list of books, including author names, and sorts the books by price in descending order.

Database Creation Scripts

The database tables were created using the following SQL scripts:

Authors Table:

-- Create authors table with user_id for authentication
CREATE TABLE authors (
    author_id SERIAL PRIMARY KEY,
    user_id UUID REFERENCES auth.users(id),
    name VARCHAR(255) NOT NULL,
    country VARCHAR(100)
);

Books Table:

-- Create books table with user_id and author_id for relationships
CREATE TABLE books (
    book_id SERIAL PRIMARY KEY,
    user_id UUID REFERENCES auth.users(id),
    title VARCHAR(255) NOT NULL,
    author_id INTEGER REFERENCES authors(author_id),
    price FLOAT(4) NOT NULL,
    publish_date DATE NOT NULL
);

Setup Instructions

1. Clone the Repository:

   git clone https://github.com/behrouzsedighi/task-supabase
   cd bookstore-api

2. Install Dependencies: Ensure you have Node.js installed. Then, install the required dependencies:

   npm install

3. Set Up Supabase:

   • Create a Supabase project and configure it with the required tables.
   • Use the provided SQL scripts to create the authors and books tables in your Supabase database.

4. Environment Configuration: Set up the environment variables in a .env file:

   SUPABASE_URL=your_supabase_url
   SUPABASE_API_KEY=your_supabase_api_key

5. Run the Application: Start the API server:

   npm run start

6. Testing:

   • You can test the API using the provided Postman collection link: Postman Collection.

Design Choices and Explanation

1. Database Design:

   • The authors table is linked to the books table using a foreign key author_id. Each book record references an author and contains necessary details like title, price, and publish date.
   • The user_id in both tables ensures that the data is associated with a specific authenticated user in Supabase.

2. API Security:

   • The API is secured using Supabase Auth, ensuring that only authenticated users can access the data. Tokens are verified for every request.

3. Pagination and Query Parameters:

   • The GET /books endpoint supports pagination with page and limit parameters, making it efficient for large datasets.

Contributions

Feel free to fork this repository and contribute. Please ensure that you follow best practices for writing clean, maintainable code.