Database Design and Migrations

Comprehensive guide to database design, Django models, and migration management in Buildly.

Overview

Buildly Core uses Django’s ORM (Object-Relational Mapping) to interact with databases. This guide covers database design principles, model creation, relationships, and migration strategies.

Supported Databases

Buildly Core supports multiple database backends:

  • PostgreSQL (Recommended for production)

  • MySQL/MariaDB

  • SQLite (Development only)

  • Oracle (Enterprise)

Configuration:

# settings.py
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'buildly_db',
        'USER': 'buildly_user',
        'PASSWORD': 'secure_password',
        'HOST': 'localhost',
        'PORT': '5432',
        'CONN_MAX_AGE': 600,
        'OPTIONS': {
            'connect_timeout': 10,
        }
    }
}

Django Models

Basic Model Structure

# myapp/models.py
from django.db import models
from django.contrib.auth import get_user_model
from django.core.validators import MinValueValidator, MaxValueValidator

User = get_user_model()

class BaseModel(models.Model):
    """Abstract base model with common fields"""
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

    class Meta:
        abstract = True

class Category(BaseModel):
    """Product category"""
    name = models.CharField(max_length=100, unique=True)
    description = models.TextField(blank=True)
    parent = models.ForeignKey(
        'self',
        on_delete=models.CASCADE,
        null=True,
        blank=True,
        related_name='subcategories'
    )

    class Meta:
        verbose_name_plural = 'Categories'
        ordering = ['name']

    def __str__(self):
        return self.name

Field Types

class Product(BaseModel):
    # Character fields
    name = models.CharField(max_length=255)
    slug = models.SlugField(unique=True)
    sku = models.CharField(max_length=50, unique=True)

    # Text fields
    description = models.TextField()
    short_description = models.CharField(max_length=500, blank=True)

    # Numeric fields
    price = models.DecimalField(max_digits=10, decimal_places=2)
    stock = models.IntegerField(default=0)
    weight = models.FloatField(null=True, blank=True)

    # Boolean fields
    is_active = models.BooleanField(default=True)
    is_featured = models.BooleanField(default=False)

    # Date/Time fields
    published_at = models.DateTimeField(null=True, blank=True)

    # File fields
    image = models.ImageField(upload_to='products/', null=True, blank=True)
    datasheet = models.FileField(upload_to='documents/', null=True, blank=True)

    # JSON field (PostgreSQL)
    metadata = models.JSONField(default=dict, blank=True)

    # Foreign key
    category = models.ForeignKey(
        Category,
        on_delete=models.SET_NULL,
        null=True,
        related_name='products'
    )

    # Validators
    rating = models.IntegerField(
        validators=[MinValueValidator(1), MaxValueValidator(5)],
        null=True,
        blank=True
    )

Relationships

One-to-Many (ForeignKey)

class Author(BaseModel):
    name = models.CharField(max_length=200)
    email = models.EmailField(unique=True)

    def __str__(self):
        return self.name

class Book(BaseModel):
    title = models.CharField(max_length=300)
    author = models.ForeignKey(
        Author,
        on_delete=models.CASCADE,  # Delete books when author is deleted
        related_name='books'  # Access via author.books.all()
    )

    def __str__(self):
        return self.title

ON_DELETE Options:

# CASCADE - Delete related objects
author = models.ForeignKey(Author, on_delete=models.CASCADE)

# SET_NULL - Set to NULL (requires null=True)
category = models.ForeignKey(Category, on_delete=models.SET_NULL, null=True)

# PROTECT - Prevent deletion if related objects exist
user = models.ForeignKey(User, on_delete=models.PROTECT)

# SET_DEFAULT - Set to default value (requires default)
status = models.ForeignKey(Status, on_delete=models.SET_DEFAULT, default=1)

# DO_NOTHING - Do nothing (dangerous, use carefully)
legacy = models.ForeignKey(Legacy, on_delete=models.DO_NOTHING)

Many-to-Many

class Tag(BaseModel):
    name = models.CharField(max_length=50, unique=True)

    def __str__(self):
        return self.name

class Article(BaseModel):
    title = models.CharField(max_length=300)
    content = models.TextField()
    tags = models.ManyToManyField(Tag, related_name='articles')

    def __str__(self):
        return self.title

With Through Model:

class Student(BaseModel):
    name = models.CharField(max_length=200)

class Course(BaseModel):
    name = models.CharField(max_length=200)
    students = models.ManyToManyField(
        Student,
        through='Enrollment',
        related_name='courses'
    )

class Enrollment(BaseModel):
    student = models.ForeignKey(Student, on_delete=models.CASCADE)
    course = models.ForeignKey(Course, on_delete=models.CASCADE)
    enrolled_date = models.DateField(auto_now_add=True)
    grade = models.CharField(max_length=2, blank=True)

    class Meta:
        unique_together = ['student', 'course']

One-to-One

class UserProfile(BaseModel):
    user = models.OneToOneField(
        User,
        on_delete=models.CASCADE,
        related_name='profile'
    )
    bio = models.TextField(blank=True)
    avatar = models.ImageField(upload_to='avatars/', null=True, blank=True)
    phone = models.CharField(max_length=20, blank=True)

    def __str__(self):
        return f"Profile of {self.user.username}"

Model Methods

Instance Methods

class Order(BaseModel):
    order_number = models.CharField(max_length=20, unique=True)
    user = models.ForeignKey(User, on_delete=models.CASCADE)
    status = models.CharField(max_length=20, default='pending')
    total_amount = models.DecimalField(max_digits=10, decimal_places=2)

    def get_absolute_url(self):
        """Return URL for order detail"""
        return f'/orders/{self.order_number}/'

    def calculate_total(self):
        """Calculate order total from items"""
        return sum(item.subtotal for item in self.items.all())

    def can_be_cancelled(self):
        """Check if order can be cancelled"""
        return self.status in ['pending', 'processing']

    def cancel(self):
        """Cancel the order"""
        if self.can_be_cancelled():
            self.status = 'cancelled'
            self.save()
            return True
        return False

Class Methods and Managers

class ProductManager(models.Manager):
    """Custom manager for Product"""

    def active(self):
        """Return only active products"""
        return self.filter(is_active=True, stock__gt=0)

    def featured(self):
        """Return featured products"""
        return self.active().filter(is_featured=True)

    def by_category(self, category_id):
        """Filter by category"""
        return self.active().filter(category_id=category_id)

class Product(BaseModel):
    # ... fields ...

    objects = ProductManager()

    @classmethod
    def create_with_defaults(cls, name, **kwargs):
        """Create product with default values"""
        defaults = {
            'is_active': True,
            'stock': 0,
        }
        defaults.update(kwargs)
        return cls.objects.create(name=name, **defaults)

    def save(self, *args, **kwargs):
        """Override save to add custom logic"""
        if not self.slug:
            from django.utils.text import slugify
            self.slug = slugify(self.name)
        super().save(*args, **kwargs)

Indexes and Optimization

Database Indexes

class Product(BaseModel):
    name = models.CharField(max_length=255, db_index=True)
    sku = models.CharField(max_length=50, unique=True)
    category = models.ForeignKey(Category, on_delete=models.SET_NULL, null=True)

    class Meta:
        indexes = [
            models.Index(fields=['name']),
            models.Index(fields=['category', '-created_at']),
            models.Index(fields=['is_active', 'stock']),
        ]
        # Unique together constraint
        unique_together = [['name', 'category']]

Query Optimization

# Bad - N+1 queries problem
products = Product.objects.all()
for product in products:
    print(product.category.name)  # Additional query for each product

# Good - Use select_related for ForeignKey
products = Product.objects.select_related('category').all()
for product in products:
    print(product.category.name)  # No additional queries

# Good - Use prefetch_related for ManyToMany
articles = Article.objects.prefetch_related('tags').all()
for article in articles:
    for tag in article.tags.all():  # No additional queries
        print(tag.name)

Migrations

Creating Migrations

# Create migrations for all app changes
python manage.py makemigrations

# Create migration for specific app
python manage.py makemigrations myapp

# Create empty migration (for data migrations)
python manage.py makemigrations --empty myapp

Applying Migrations

# Apply all migrations
python manage.py migrate

# Apply migrations for specific app
python manage.py migrate myapp

# Apply specific migration
python manage.py migrate myapp 0003

# Show migration status
python manage.py showmigrations

# Rollback migration
python manage.py migrate myapp 0002

Data Migrations

# Generated migration file
# myapp/migrations/0004_populate_defaults.py

from django.db import migrations

def populate_categories(apps, schema_editor):
    """Forward migration - populate data"""
    Category = apps.get_model('myapp', 'Category')

    categories = [
        {'name': 'Electronics', 'description': 'Electronic products'},
        {'name': 'Books', 'description': 'Books and publications'},
        {'name': 'Clothing', 'description': 'Apparel and accessories'},
    ]

    for cat_data in categories:
        Category.objects.create(**cat_data)

def remove_categories(apps, schema_editor):
    """Reverse migration - remove data"""
    Category = apps.get_model('myapp', 'Category')
    Category.objects.filter(
        name__in=['Electronics', 'Books', 'Clothing']
    ).delete()

class Migration(migrations.Migration):
    dependencies = [
        ('myapp', '0003_auto_20231201_1234'),
    ]

    operations = [
        migrations.RunPython(populate_categories, remove_categories),
    ]

Complex Migrations

# myapp/migrations/0005_complex_migration.py

from django.db import migrations, models

class Migration(migrations.Migration):
    dependencies = [
        ('myapp', '0004_populate_defaults'),
    ]

    operations = [
        # Add field
        migrations.AddField(
            model_name='product',
            name='discount_percentage',
            field=models.IntegerField(default=0),
        ),

        # Remove field
        migrations.RemoveField(
            model_name='product',
            name='old_field',
        ),

        # Rename field
        migrations.RenameField(
            model_name='product',
            old_name='desc',
            new_name='description',
        ),

        # Alter field
        migrations.AlterField(
            model_name='product',
            name='price',
            field=models.DecimalField(max_digits=12, decimal_places=2),
        ),

        # Add index
        migrations.AddIndex(
            model_name='product',
            index=models.Index(fields=['name', 'category'], name='prod_name_cat_idx'),
        ),
    ]

Database Queries

Basic Queries

# Get all objects
products = Product.objects.all()

# Get filtered objects
active_products = Product.objects.filter(is_active=True)

# Get single object
product = Product.objects.get(id=1)

# Get or create
product, created = Product.objects.get_or_create(
    sku='ABC123',
    defaults={'name': 'New Product', 'price': 99.99}
)

# Update or create
product, created = Product.objects.update_or_create(
    sku='ABC123',
    defaults={'price': 89.99}
)

Complex Queries

from django.db.models import Q, F, Count, Sum, Avg, Max, Min

# Q objects for complex queries
products = Product.objects.filter(
    Q(name__icontains='phone') | Q(description__icontains='phone'),
    Q(price__gte=100) & Q(price__lte=1000)
)

# F expressions for field comparisons
discounted = Product.objects.filter(price__lt=F('original_price') * 0.8)

# Aggregations
stats = Product.objects.aggregate(
    total=Count('id'),
    avg_price=Avg('price'),
    max_price=Max('price'),
    min_price=Min('price'),
    total_stock=Sum('stock')
)

# Annotate
categories = Category.objects.annotate(
    product_count=Count('products'),
    avg_price=Avg('products__price')
).filter(product_count__gt=0)

Raw SQL

# Raw queries when ORM is not sufficient
products = Product.objects.raw(
    'SELECT * FROM myapp_product WHERE price > %s',
    [100]
)

# Execute custom SQL
from django.db import connection

with connection.cursor() as cursor:
    cursor.execute("UPDATE myapp_product SET stock = stock + %s WHERE id = %s", [10, 1])
    cursor.execute("SELECT name, price FROM myapp_product WHERE price > %s", [100])
    results = cursor.fetchall()

Transactions

Atomic Operations

from django.db import transaction

# Function decorator
@transaction.atomic
def create_order_with_items(user, items_data):
    """Create order and items atomically"""
    order = Order.objects.create(
        user=user,
        order_number=generate_order_number(),
        status='pending'
    )

    for item_data in items_data:
        OrderItem.objects.create(
            order=order,
            product_id=item_data['product_id'],
            quantity=item_data['quantity'],
            price=item_data['price']
        )

    return order

# Context manager
def process_payment(order):
    with transaction.atomic():
        # Both operations succeed or both fail
        Payment.objects.create(
            order=order,
            amount=order.total_amount,
            status='completed'
        )
        order.status = 'paid'
        order.save()

Savepoints

from django.db import transaction

with transaction.atomic():
    # Main transaction
    order = Order.objects.create(...)

    # Create a savepoint
    sid = transaction.savepoint()

    try:
        # Risky operation
        process_payment(order)
        transaction.savepoint_commit(sid)
    except Exception:
        # Rollback to savepoint
        transaction.savepoint_rollback(sid)
        order.status = 'payment_failed'
        order.save()

Database Constraints

Field Constraints

class Product(BaseModel):
    name = models.CharField(max_length=255, unique=True)
    sku = models.CharField(max_length=50, unique=True)
    price = models.DecimalField(
        max_digits=10,
        decimal_places=2,
        validators=[MinValueValidator(0)]
    )
    stock = models.IntegerField(
        default=0,
        validators=[MinValueValidator(0)]
    )

    class Meta:
        constraints = [
            # Check constraint
            models.CheckConstraint(
                check=models.Q(price__gte=0),
                name='price_non_negative'
            ),
            models.CheckConstraint(
                check=models.Q(stock__gte=0),
                name='stock_non_negative'
            ),
            # Unique constraint
            models.UniqueConstraint(
                fields=['name', 'category'],
                name='unique_product_per_category'
            ),
        ]

Best Practices

βœ… DO:

  • Use PostgreSQL for production

  • Create indexes for frequently queried fields

  • Use select_related and prefetch_related

  • Write data migrations for complex changes

  • Use transactions for related operations

  • Add appropriate constraints

  • Keep models focused and cohesive

  • Use abstract base models for common fields

❌ DON’T:

  • Use SQLite in production

  • Create unnecessary indexes (they slow writes)

  • Perform queries in loops (N+1 problem)

  • Edit migration files after applying them

  • Use raw SQL unless necessary

  • Store sensitive data without encryption

  • Create overly complex models

  • Ignore database constraints

Coming Soon

Note

Advanced Topics Coming Soon:

  • Multi-database routing

  • Database sharding strategies

  • Advanced query optimization

  • Database replication setup

  • Backup and restore procedures

Next Steps

Further Reading:

Video Resources:

Note

For production deployments, always use PostgreSQL with proper indexing and optimization strategies.