# Session 8: WebApp - Database (PostgreSQL) ## Overview Welcome to Session 8! In this session, we'll focus on databases, particularly PostgreSQL, which is a powerful, open-source object-relational database system. We'll learn how to set up PostgreSQL for our Django application, create efficient database schemas, and optimize database operations for better performance. ## Learning Objectives - Understand the fundamentals of relational databases - Set up PostgreSQL for Django - Create efficient database schemas with proper relationships - Write advanced queries using Django's ORM - Implement database migrations - Optimize database performance - Back up and restore databases ## Topics Covered ### 1. Introduction to Relational Databases Relational databases organize data into tables (relations) with rows and columns. They follow a set of principles known as the relational model. #### Key Concepts: - **Tables (Relations)**: Collections of related data held in a structured format - **Rows (Records/Tuples)**: Individual entries in a table - **Columns (Fields/Attributes)**: Specific pieces of data within each row - **Primary Key**: Unique identifier for each row - **Foreign Key**: A field that links to a primary key in another table - **Indexes**: Data structures that improve the speed of data retrieval - **Constraints**: Rules enforced on data columns (e.g., NOT NULL, UNIQUE) ### 2. Why PostgreSQL? PostgreSQL is one of the most advanced open-source relational databases with many advantages: - **Standards Compliance**: Follows SQL standards closely - **ACID Compliance**: Ensures data validity despite errors or power failures - **Advanced Data Types**: Supports arrays, JSON, geographic objects, and custom types - **Extensibility**: Can be extended with custom functions, operators, data types - **Performance**: Handles complex queries and large datasets efficiently - **Concurrency**: Multiple users can access the database simultaneously without locking - **Reliability**: Strong reputation for data integrity and correctness - **Community Support**: Large, active community and extensive documentation ### 3. Setting Up PostgreSQL with Django #### Installing PostgreSQL: ```bash # macOS (using Homebrew) brew install postgresql brew services start postgresql # Ubuntu/Debian sudo apt update sudo apt install postgresql postgresql-contrib sudo systemctl start postgresql sudo systemctl enable postgresql # Windows # Download and install from https://www.postgresql.org/download/windows/ ``` #### Creating a Database: ```bash # Access PostgreSQL command line psql -U postgres # Create database and user CREATE DATABASE todo_app; CREATE USER todo_user WITH PASSWORD 'password'; ALTER ROLE todo_user SET client_encoding TO 'utf8'; ALTER ROLE todo_user SET default_transaction_isolation TO 'read committed'; ALTER ROLE todo_user SET timezone TO 'UTC'; GRANT ALL PRIVILEGES ON DATABASE todo_app TO todo_user; # Exit PostgreSQL \q ``` #### Configure Django to use PostgreSQL: ```bash # Install the psycopg2 adapter pip install psycopg2-binary ``` ```python # todo_backend/settings.py DATABASES = { 'default': { 'ENGINE': 'django.db.backends.postgresql', 'NAME': 'todo_app', 'USER': 'todo_user', 'PASSWORD': 'password', 'HOST': 'localhost', 'PORT': '5432', } } ``` ### 4. Database Schema Design Good database design is crucial for application performance and maintainability. #### Principles of Schema Design: - **Normalization**: Organizing data to reduce redundancy - **Appropriate Relationships**: One-to-One, One-to-Many, Many-to-Many - **Proper Indexing**: Adding indexes to frequently queried columns - **Choosing the Right Data Types**: Using the most appropriate type for each column #### Enhancing Our Todo App Schema: ```python # tasks/models.py from django.db import models from django.contrib.auth.models import User class Category(models.Model): name = models.CharField(max_length=100) color = models.CharField(max_length=7, default="#000000") # Hex color user = models.ForeignKey(User, on_delete=models.CASCADE, related_name='categories') def __str__(self): return self.name class Meta: verbose_name_plural = "Categories" class Priority(models.Model): LEVELS = ( ('LOW', 'Low'), ('MEDIUM', 'Medium'), ('HIGH', 'High'), ('URGENT', 'Urgent') ) level = models.CharField(max_length=10, choices=LEVELS, default='MEDIUM') def __str__(self): return self.level class Meta: verbose_name_plural = "Priorities" class Task(models.Model): title = models.CharField(max_length=200) description = models.TextField(blank=True) completed = models.BooleanField(default=False) created_at = models.DateTimeField(auto_now_add=True) updated_at = models.DateTimeField(auto_now=True) due_date = models.DateTimeField(null=True, blank=True) user = models.ForeignKey(User, on_delete=models.CASCADE, related_name='tasks') category = models.ForeignKey(Category, on_delete=models.SET_NULL, null=True, blank=True, related_name='tasks') priority = models.ForeignKey(Priority, on_delete=models.SET_DEFAULT, default=2, related_name='tasks') def __str__(self): return self.title class Meta: ordering = ['due_date', 'priority', 'created_at'] indexes = [ models.Index(fields=['user', 'completed']), models.Index(fields=['due_date']), ] ``` ### 5. Database Migrations Migrations are Django's way of propagating changes you make to your models into your database schema. ```bash # Create migrations based on model changes python manage.py makemigrations # Apply migrations to the database python manage.py migrate # Show migration status python manage.py showmigrations # Revert to a specific migration python manage.py migrate app_name migration_name ``` ### 6. Advanced Django ORM Queries Django's Object-Relational Mapper (ORM) provides a powerful abstraction for database queries. ```python # Basic queries # Get all tasks tasks = Task.objects.all() # Filter tasks completed_tasks = Task.objects.filter(completed=True) high_priority = Task.objects.filter(priority__level='HIGH') overdue_tasks = Task.objects.filter(due_date__lt=timezone.now(), completed=False) # Complex queries with Q objects from django.db.models import Q urgent_or_high = Task.objects.filter(Q(priority__level='URGENT') | Q(priority__level='HIGH')) urgent_not_completed = Task.objects.filter(Q(priority__level='URGENT') & ~Q(completed=True)) # Ordering ordered_tasks = Task.objects.order_by('due_date', '-priority') # Aggregation and annotation from django.db.models import Count, Avg, Max, Min category_counts = Category.objects.annotate(num_tasks=Count('tasks')) user_tasks_stats = User.objects.annotate( task_count=Count('tasks'), completed_count=Count('tasks', filter=Q(tasks__completed=True)) ) # Select related (for foreign keys) and prefetch related (for reverse relations) tasks_with_category = Task.objects.select_related('category', 'priority').all() categories_with_tasks = Category.objects.prefetch_related('tasks').all() ``` ### 7. Database Performance Optimization Optimizing database performance is crucial for scalable applications. #### Optimization Techniques: 1. **Indexing**: - Add indexes to frequently queried fields - Be careful not to over-index as it can slow down writes ```python class Meta: indexes = [ models.Index(fields=['user', 'completed']), models.Index(fields=['due_date']), ] ``` 2. **Query Optimization**: - Use `select_related` and `prefetch_related` to reduce database hits - Use `values` or `values_list` when you only need specific fields - Use `defer` or `only` to select or exclude specific fields ```python # Get only specific fields task_titles = Task.objects.values_list('title', flat=True) # Exclude heavy fields tasks_without_description = Task.objects.defer('description') # Only load specific fields tasks_minimal = Task.objects.only('id', 'title', 'completed') ``` 3. **Database Connection Pooling**: - Use a connection pooler like pgBouncer for production - Configure Django's connection pool size ```python # settings.py DATABASES = { 'default': { # ...database settings... 'CONN_MAX_AGE': 600, # Keep connections alive for 10 minutes } } ``` 4. **Denormalization**: - Selectively duplicate data to reduce joins for frequent queries - Use caching for read-heavy operations ### 8. Database Backup and Restore Regular backups are essential for data safety. #### PostgreSQL Backup: ```bash # Backup a database to a file pg_dump -U todo_user -d todo_app -f backup.sql # Backup with compression pg_dump -U todo_user -d todo_app | gzip > backup.sql.gz ``` #### PostgreSQL Restore: ```bash # Restore from a file psql -U todo_user -d todo_app -f backup.sql # Restore from a compressed file gunzip -c backup.sql.gz | psql -U todo_user -d todo_app ``` #### Automating Backups: ```bash #!/bin/bash # backup_script.sh BACKUP_DIR="/path/to/backups" TIMESTAMP=$(date +"%Y%m%d_%H%M%S") BACKUP_FILE="$BACKUP_DIR/todo_app_$TIMESTAMP.sql.gz" pg_dump -U todo_user -d todo_app | gzip > $BACKUP_FILE # Keep only the last 7 backups ls -t $BACKUP_DIR/*.sql.gz | tail -n +8 | xargs rm -f ``` ## Practice Exercises 1. Set up PostgreSQL and configure it with your Django project 2. Enhance the Task model with categories, priorities, and due dates 3. Create migrations and apply them to your database 4. Write advanced queries using the Django ORM to retrieve tasks with different filters 5. Optimize your database schema with appropriate indexes 6. Create a backup script for your database ## Additional Resources - [PostgreSQL Documentation](https://www.postgresql.org/docs/) - [Django Database Documentation](https://docs.djangoproject.com/en/stable/topics/db/) - [Django Database Optimization Tips](https://docs.djangoproject.com/en/stable/topics/db/optimization/) - [PostgreSQL Indexing Strategies](https://www.postgresql.org/docs/current/indexes-strategies.html) - [Django Migrations](https://docs.djangoproject.com/en/stable/topics/migrations/) ## Next Steps Now that you've learned how to work with PostgreSQL and implement advanced database features, you're ready to move on to [Session 9: WebApp - Deployment](Session-9-WebApp-Deployment.md) where you'll learn how to deploy your application to production environments.