Database Operations - PostgreSQL DVD Rental Demo

Overview

Demonstrates working with PostgreSQL databases using the DVD rental sample schema. This example showcases Claude Code's ability to understand complex database schemas and generate realistic, interconnected test data.

What This Demonstrates

Schema analysis and understanding (15 interconnected tables)
Realistic test data generation with referential integrity
Query optimization and performance tuning
Database operations in containers (Podman/Docker)
Complex JOIN queries and aggregations

Database Schema

The DVD rental database contains 15 tables representing a fictional DVD rental business:

Core Tables: film, actor, film_actor, category, film_category
Business Tables: customer, rental, payment, inventory, store, staff
Location Tables: address, city, country, language

Download Files

Get the CLAUDE.md, database schema, and container configuration:

Download database.md Download Schema (SQL) Download Compose File

Quick Start

1. Start PostgreSQL Container

podman-compose -f dvdrental-compose.yml up -d
# OR with Docker:
docker-compose -f dvdrental-compose.yml up -d

2. Verify Schema Loaded

podman exec -it dvdrental_postgres psql -U postgres -d dvdrental -c "\dt"

3. Ask Claude to Generate Data

I have a PostgreSQL 18 database running in a Podman container with the DVD rental schema.
The container is named 'dvdrental_postgres' and the database is 'dvdrental' with user 'postgres' and password 'password'.

Please analyze the schema structure and generate realistic fictional data for all tables. Create:
- 10-20 countries and 50-100 cities
- 200+ actors with realistic names
- 100+ films with creative titles, descriptions, and ratings
- 15+ categories (Action, Comedy, Drama, Horror, etc.)
- 5-10 stores with realistic addresses
- 50+ customers spread across different cities
- Rental transactions and payments with realistic dates
- Inventory linking films to stores

Please import the data directly into the running container and then run some sample queries to demonstrate the populated database.

Sample Prompts

Query Optimization

This rental report query is slow (paste EXPLAIN ANALYZE output). Analyze the execution plan and suggest indexes or query improvements.

Schema Analysis

Analyze the DVD rental schema and explain the relationships between tables. Create an ER diagram description and identify the key business rules enforced by constraints.

Complex Query Building

Write a query to find the top 10 customers by total rental spending, including their full names, email addresses, and total amount spent. Include customers with zero rentals.

Data Generation for Specific Table

Generate 50 realistic film entries with creative titles, engaging descriptions, varied ratings (G, PG, PG-13, R, NC-17), and release years between 2000-2023. Include rental rates between $0.99-$6.99.

Tools & Documentation

PostgreSQL Documentation

Official PostgreSQL documentation and SQL reference.

PostgreSQL Docs →

Docker PostgreSQL

Official PostgreSQL Docker images for local development.

Docker Hub →

pgAdmin

Popular PostgreSQL administration and development tool.

pgAdmin →

Why This Example?

This example demonstrates Claude Code's strength in:

Understanding complex schemas - 15 interconnected tables with various relationship types
Generating realistic data - Creating coherent, related data across multiple tables
Respecting constraints - Foreign keys, check constraints, data types
Creative data generation - Film titles, actor names, addresses that feel real
Query assistance - Helping write complex JOINs and aggregations

Safety Guidelines

                🔒 NEVER connect to production databases
🧪 ALWAYS use test/development databases
⚠️ NEVER include credentials in CLAUDE.md
📋 Review generated SQL before execution
💾 BACKUP before schema changes

            