“Movie Catalog API” (PostgreSQL, FastAPI)

Goal: Create an API that lists and manages movie/TV show data from a dataset. Focus on CRUD operations and clean data modeling.

📦 Stack

• FastAPI
• PostgreSQL
• SQLAlchemy

📊 Dataset

Use the IMDb Movie Dataset or generate a smaller version with:

• Title
• Genre(s)
• Release year
• Duration
• Director
• Average rating

🔧 API Endpoints

• GET /movies – list all movies
• GET /movies/{id} – get movie by ID
• GET /movies/search?genre=Action&year=2020 – filter
• POST /movies – add new movie
• PUT /movies/{id} – update
• DELETE /movies/{id} – delete

🧠 Learnings

• Normalize genres into a separate table (many-to-many)
• Write efficient queries
• Work with PostgreSQL joins
• Handle numeric filters (ratings, year range)

 

FOLDER STRUCTURE

city_events_api/
├── app/
│   ├── __init__.py
│   ├── main.py               # FastAPI app instance
│   ├── config.py             # DB and app settings
│   ├── models/
│   │   ├── __init__.py
│   │   └── event.py          # SQLAlchemy Event model
│   ├── schemas/
│   │   ├── __init__.py
│   │   └── event.py          # Pydantic schemas
│   ├── crud/
│   │   ├── __init__.py
│   │   └── event.py          # DB logic (CRUD operations)
│   ├── api/
│   │   ├── __init__.py
│   │   └── routes_event.py   # API route definitions
│   ├── db/
│   │   ├── __init__.py
│   │   ├── session.py        # DB connection
│   │   └── base.py           # Base class for models
│   └── utils/
│       └── populate.py       # Script to load fake or real data
├── alembic/                  # DB migrations
│   └── ...
├── alembic.ini
├── requirements.txt
└── README.md