# SQLAlchemy Join Testing Project A comprehensive project for examining and testing SQLAlchemy's joining capabilities with complex relationships and realistic data. ## Features - PostgreSQL database with Docker Compose - Complex model relationships (7 entities with 5+ relationships) - <1 Customizable test data generation - =� 10 different join query examples - � Performance measurement for each query - <� Easy to customize and extend ## Project Structure ``` sqlalchemy-example/ � config.py # Database configuration � database.py # Connection and session management � models.py # SQLAlchemy models with relationships � seed_data.py # Test data generation script � test_queries.py # Join query examples � docker-compose.yaml # PostgreSQL setup � .env # Environment variables ``` ## Model Relationships The project includes 7 interconnected models: 1. **User** - Central entity 2. **Post** - Belongs to User and Category 3. **Comment** - Belongs to Post and User (with self-referential replies) 4. **Tag** - Many-to-many with Post 5. **Category** - Has many Posts 6. **Likes** - Many-to-many between User and Post 7. **Followers** - Self-referential many-to-many on User ## Setup ### 1. Install Dependencies ```bash uv sync ``` ### 2. Start PostgreSQL ```bash docker-compose up -d ``` ### 3. Generate Test Data ```bash # Default: 100 users, 500 posts, 2000 comments python seed_data.py # Custom amounts python seed_data.py --users 200 --posts 1000 --comments 5000 # Reset database and seed python seed_data.py --reset # All options python seed_data.py \ --users 100 \ --posts 500 \ --comments 2000 \ --categories 10 \ --tags 30 \ --likes 5000 \ --followers 1000 \ --reset ``` ### 4. Run Query Tests ```bash # Run all tests python test_queries.py # Run specific test (1-10) python test_queries.py 3 ``` ## Query Examples The `test_queries.py` file includes 10 different join patterns: 1. **Simple Join** - Posts with Authors (2 tables) 2. **Multiple Joins** - Posts with Authors and Categories (3 tables) 3. **Complex Join** - Post + Author + Category + Comment + Commenter (5 tables) 4. **Many-to-Many** - Posts with Tags 5. **Aggregation** - Posts per Category with counts 6. **Subquery** - Users with most posts 7. **Super Complex** - 6+ table join with aggregations 8. **Eager Loading** - Using `joinedload` strategy 9. **Self-referential** - User followers 10. **Complex Filtering** - Multiple conditions across joins ## Customization ### Modify Models Edit [models.py](models.py) to add new relationships or fields: ```python class Post(Base): # Add new field rating = Column(Integer, default=0) # Add new relationship ratings = relationship("Rating", back_populates="post") ``` ### Create Custom Queries Add new test functions to [test_queries.py](test_queries.py): ```python @measure_time def test_my_custom_query(): """My custom query description""" print_header("My Custom Query") session = get_session() stmt = select(Post).join(User).where(User.is_active == True) results = session.execute(stmt).scalars().all() print_results(results) session.close() ``` ### Adjust Data Volume Modify seed counts in [seed_data.py](seed_data.py) or use command-line arguments: ```bash # Small dataset for quick testing python seed_data.py --users 10 --posts 50 --comments 100 # Large dataset for performance testing python seed_data.py --users 1000 --posts 10000 --comments 50000 ``` ## Configuration ### Database Settings Edit [.env](.env) to change database connection: ```env DB_USER=testuser DB_PASSWORD=testpass DB_HOST=localhost DB_PORT=5432 DB_NAME=testdb ECHO_SQL=true # Set to false to disable SQL logging ``` ### Docker PostgreSQL Modify [docker-compose.yaml](docker-compose.yaml) to change PostgreSQL settings: ```yaml environment: POSTGRES_USER: myuser POSTGRES_PASSWORD: mypassword POSTGRES_DB: mydb ports: - "5433:5432" # Use different port ``` ## Useful Commands ```bash # Start database docker-compose up -d # Stop database docker-compose down # View database logs docker-compose logs -f postgres # Connect to PostgreSQL docker exec -it sqlalchemy-postgres psql -U testuser -d testdb # Reset and reseed database python seed_data.py --reset # Install new dependencies uv add ``` ## Tips for Testing 1. **Enable SQL Logging**: Set `ECHO_SQL=true` in `.env` to see generated SQL 2. **Start Small**: Test with small datasets first, then scale up 3. **Measure Performance**: Each query test shows execution time 4. **Compare Strategies**: Try `joinedload` vs `selectinload` for different scenarios 5. **Use PostgreSQL EXPLAIN**: Connect to database and run `EXPLAIN ANALYZE` on queries ## Troubleshooting ### Connection Refused ```bash # Check if PostgreSQL is running docker-compose ps # Restart containers docker-compose restart ``` ### Unique Constraint Errors ```bash # Reset the database python seed_data.py --reset ``` ### Slow Queries - Reduce data volume with `--users`, `--posts`, etc. - Check indexes in [models.py](models.py) - Set `ECHO_SQL=false` to reduce logging overhead ## License MIT