Skip to content

Session 8 WebApp Database

Jump to bottom
Maximiliano Militzer edited this page Apr 7, 2025 · 1 revision

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:

# 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:

# 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:

# Install the psycopg2 adapter
pip install psycopg2-binary
# 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:

# 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.

# 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.

# 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
class Meta:
    indexes = [
        models.Index(fields=['user', 'completed']),
        models.Index(fields=['due_date']),
    ]
  1. 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
# 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')
  1. Database Connection Pooling:
    • Use a connection pooler like pgBouncer for production
    • Configure Django's connection pool size
# settings.py
DATABASES = {
    'default': {
        # ...database settings...
        'CONN_MAX_AGE': 600,  # Keep connections alive for 10 minutes
    }
}
  1. 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:

# 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:

# 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:

#!/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

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 where you'll learn how to deploy your application to production environments.

Clone this wiki locally