YouTube Database API Infrastructure

PostgreSQL database with REST API for YouTube-related data management.

Quick Start

1. Prerequisites

• Docker Desktop installed and running
• Python 3.10+ installed
• Git (optional)

2. Setup

# Navigate to project
cd C:\Projects\database_youtube

# Copy environment file and edit with your settings (if .env doesn't exist)
# copy .env.example .env
# Edit .env and set POSTGRES_PASSWORD to a secure password

# Start all services (PostgreSQL, API, Backup)
docker-compose -f docker/docker-compose.yml up -d

# Wait for services to be ready (check logs)
docker-compose -f docker/docker-compose.yml logs -f

# Verify API is running
curl http://localhost:8000/health

Note: The API now runs in Docker and doesn't require a terminal window to stay open. It will automatically restart if it crashes.

3. Verify Setup

• Database: http://localhost:5432 (PostgreSQL)
• API: http://localhost:8000 (FastAPI)
• API Docs: http://localhost:8000/docs (Swagger UI)
• Health Check: http://localhost:8000/health

Project Structure

database_youtube/
├── docker/              # Docker configuration
├── api/                 # FastAPI application
├── scripts/             # Utility scripts (backup, restore, etc.)
├── data/                # Database data and backups
└── tests/               # Unit tests

API Endpoints

Health

• GET /health - API health check
• GET /api/v1/health/ - API health check (v1)
• GET /api/v1/health/db - Database connection check

Note: The API currently provides only health endpoints. YouTube-related tables (videos, video_labels, transcripts, etc.) are managed by other processes and accessed directly via the database.

Backup & Restore

Manual Backup

python scripts/backup.py

Restore

python scripts/restore.py data/backups/daily/backup_20241205.sql

Automated Backups

Backups run automatically daily at 2:00 AM (configurable in .env).

Development

Running Tests

# Run API tests
python test/test_api.py

Database Migrations

# Create migration
alembic revision --autogenerate -m "description"

# Apply migrations
alembic upgrade head

Configuration

All configuration is in .env file. See .env.example for available options.

Security Notes

• Never commit .env file to git
• Use strong database passwords
• Consider adding API authentication for production
• Restrict network access to database port

Docker Services

The project includes three Docker services:

1. postgres - PostgreSQL database (port 5432)
2. api - FastAPI application (port 8000)
3. backup - Automated backup service (runs daily at 2:00 AM)

All services are configured in docker/docker-compose.yml and start automatically with docker-compose up -d.

Troubleshooting

Database won't start

• Check if port 5432 is already in use
• Check Docker logs: docker-compose -f docker/docker-compose.yml logs postgres
• Verify Docker Desktop is running

API won't start

• Check API logs: docker logs database-youtube-api
• Verify database is healthy: docker ps (should show "healthy" status)
• Check API can connect to database: docker exec database-youtube-api python -c "from api.config import settings; print(settings.database_url)"

Connection refused

• Ensure containers are running: docker ps
• Check POSTGRES_HOST and POSTGRES_PORT in .env or docker-compose.yml
• Verify firewall settings
• For API: Ensure it's using postgres as hostname (Docker service name), not localhost

Support

For issues or questions, refer to database_plan.md for detailed architecture and design decisions.