Working with Databases

Configuring Databases

Django supports multiple database backends, including PostgreSQL, MySQL, SQLite, and Oracle. To configure the database, you need to modify the DATABASES setting in your project's settings.py file.

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',  # or 'mysql', 'sqlite3', 'oracle'
        'NAME': 'your_database_name',
        'USER': 'your_database_user',
        'PASSWORD': 'your_database_password',
        'HOST': 'your_database_host',  # Empty for localhost through domain sockets or '127.0.0.1' for localhost through TCP.
        'PORT': 'your_database_port',  # Set to empty string for default.
    }
}

• ENGINE: Specifies the database backend to use.
• NAME: Name of your database.
• USER: Username for the database.
• PASSWORD: Password for the database.
• HOST: Database host (use empty string for localhost).
• PORT: Port number for the database.

For SQLite, which is the default setting for Django, the configuration looks like this:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': BASE_DIR / 'db.sqlite3',
    }
}

Using Django’s ORM for Queries

Defining Models

Django models define the structure of our database tables. Each model maps to a single database table. Here is an example:

from django.db import models

class Author(models.Model):
    name = models.CharField(max_length=100)
    birth_date = models.DateField()
class Book(models.Model):
    title = models.CharField(max_length=200)
    author = models.ForeignKey(Author, on_delete=models.CASCADE)
    published_date = models.DateField()
    price = models.DecimalField(max_digits=10, decimal_places=2)

Creating and Applying Migrations

After defining models, we should create and apply migrations to sync our models with the database schema. This can be achieved by:

python manage.py makemigrations
python manage.py migrate

Performing CRUD Operations

CRUD is an acronym that stands for Create, Read, Update, and Delete. These are the four basic operations that can be performed on data in a database.

• Create: This operation involves adding new records to a database. In a web application, this typically corresponds to a form where a user can input data that gets saved to the database.

author = Author(name='George Orwell', birth_date='1903-06-25')
author.save()

• Read: This operation involves retrieving data from the database. It can be as simple as fetching a single record or as complex as querying for multiple records based on certain criteria.

# Reading all books
all_books = Book.objects.all()

# Reading a specific book by ID
specific_book = Book.objects.get(id=1)

# Filtering books by author's name
books_by_author = Book.objects.filter(author__name='George Orwell')

• Update: This operation involves modifying existing records in the database. It generally requires first retrieving the record, making changes to it, and then saving the changes back to the database.

book = Book.objects.get(id=1)
book.price = 24.99
book.save()

• Delete: This operation involves removing records from the database. It typically requires identifying the record to be deleted and then removing it.

book = Book.objects.get(id=1)
book.delete()

Query Optimization

Django provides several ways to optimize database queries:

• Select Related: Use select_related() for foreign key relationships to reduce the number of database queries.

books = Book.objects.select_related('author').all()

• Prefetch Related: Use prefetch_related() for many-to-many and reverse foreign key relationships.

authors = Author.objects.prefetch_related('book_set').all()

• Using Indexes: Add indexes to fields that are frequently used in queries.

class Book(models.Model):
    title = models.CharField(max_length=200, db_index=True)
    # other fields...

• Avoiding N+1 Queries: Minimize the number of queries in a loop by using select_related() or prefetch_related().

authors = Author.objects.prefetch_related('book_set').all()
for author in authors:
    books = author.book_set.all()  # No additional queries are made

• Database Functions and Expressions: Use database functions and expressions for complex queries.

from django.db.models import F, Sum

total_price = Book.objects.aggregate(total=Sum(F('price') * F('quantity')))