Database & Migrations#

Complete guide to configuring databases, managing migrations, and running raw SQL in Django.

🗄️ Database Configuration#

SQLite (Default)#

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

PostgreSQL#

# Install driver
pip install psycopg2-binary

# myproject/settings.py
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'mydb',
        'USER': 'myuser',
        'PASSWORD': 'mypassword',
        'HOST': 'localhost',
        'PORT': '5432',
    }
}

# Or use environment variables
from decouple import config

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': config('DB_NAME'),
        'USER': config('DB_USER'),
        'PASSWORD': config('DB_PASSWORD'),
        'HOST': config('DB_HOST', default='localhost'),
        'PORT': config('DB_PORT', default='5432'),
    }
}

MySQL#

# Install driver
pip install mysqlclient
# Or
pip install pymysql

# myproject/settings.py
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'mydb',
        'USER': 'myuser',
        'PASSWORD': 'mypassword',
        'HOST': 'localhost',
        'PORT': '3306',
    }
}

Multiple Databases#

# myproject/settings.py
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'main_db',
        # ...
    },
    'analytics': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'analytics_db',
        # ...
    }
}

# Usage
from django.db import connections
analytics_db = connections['analytics']

🔄 Migrations#

Create Migrations#

# Create migrations for all apps
python manage.py makemigrations

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

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

# Show migration SQL without applying
python manage.py makemigrations --dry-run

# Verbose output
python manage.py makemigrations --verbosity 2

Apply Migrations#

# Apply all pending migrations
python manage.py migrate

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

# Apply specific migration
python manage.py migrate myapp 0001_initial

# Show migration status
python manage.py showmigrations

# Show migration status for specific app
python manage.py showmigrations myapp

Undo Migrations#

# Rollback last migration
python manage.py migrate myapp 0001_initial

# Rollback all migrations for app
python manage.py migrate myapp zero

# Rollback to specific migration
python manage.py migrate myapp 0002_add_field

Migration Status#

# Show all migrations and their status
python manage.py showmigrations

# Output:
# myapp
#  [X] 0001_initial
#  [X] 0002_add_field
#  [ ] 0003_remove_field  # Not applied

📝 Migration Files#

Auto-Generated Migration#

# myapp/migrations/0001_initial.py
from django.db import migrations, models

class Migration(migrations.Migration):
    initial = True

    dependencies = []

    operations = [
        migrations.CreateModel(
            name='Post',
            fields=[
                ('id', models.BigAutoField(auto_created=True, primary_key=True, serialize=False, verbose_name='ID')),
                ('title', models.CharField(max_length=200)),
                ('content', models.TextField()),
                ('created_at', models.DateTimeField(auto_now_add=True)),
            ],
        ),
    ]

Manual Migration (Data Migration)#

# myapp/migrations/0002_populate_slugs.py
from django.db import migrations

def populate_slugs(apps, schema_editor):
    Post = apps.get_model('myapp', 'Post')
    for post in Post.objects.all():
        post.slug = post.title.lower().replace(' ', '-')
        post.save()

def reverse_populate_slugs(apps, schema_editor):
    Post = apps.get_model('myapp', 'Post')
    Post.objects.update(slug='')

class Migration(migrations.Migration):
    dependencies = [
        ('myapp', '0001_initial'),
    ]

    operations = [
        migrations.RunPython(populate_slugs, reverse_populate_slugs),
    ]

Common Migration Operations#

# Add field
migrations.AddField(
    model_name='post',
    name='slug',
    field=models.SlugField(max_length=200, unique=True),
),

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

# Alter field
migrations.AlterField(
    model_name='post',
    name='title',
    field=models.CharField(max_length=300),
),

# Rename field
migrations.RenameField(
    model_name='post',
    old_name='old_name',
    new_name='new_name',
),

# Add index
migrations.AddIndex(
    model_name='post',
    index=models.Index(fields=['created_at'], name='post_created_idx'),
),

# Remove index
migrations.RemoveIndex(
    model_name='post',
    name='post_created_idx',
),

🔧 Migration Commands#

Fake Migrations#

# Mark migration as applied without running SQL
python manage.py migrate --fake myapp 0001_initial

# Useful when:
# - Migrating existing database
# - Fixing migration state
# - Testing migrations

Squash Migrations#

# Combine multiple migrations into one
python manage.py squashmigrations myapp 0001 0005

# Creates new migration combining 0001-0005
# Original migrations still exist but are marked as replaced

Migration Conflicts#

# If migrations conflict, create merge migration
python manage.py makemigrations --merge

# Resolves conflicts between migration branches

🗃️ Raw SQL#

Using connection.cursor()#

# myapp/views.py
from django.db import connection

def raw_query_view(request):
    with connection.cursor() as cursor:
        # Execute raw SQL
        cursor.execute("SELECT * FROM myapp_post WHERE id = %s", [1])
        row = cursor.fetchone()

        # Fetch all
        cursor.execute("SELECT * FROM myapp_post")
        rows = cursor.fetchall()

        # Fetch many
        cursor.execute("SELECT * FROM myapp_post")
        rows = cursor.fetchmany(10)

        # Execute multiple queries
        cursor.execute("""
            SELECT p.*, u.username 
            FROM myapp_post p 
            JOIN auth_user u ON p.author_id = u.id
            WHERE p.created_at > %s
        """, ['2024-01-01'])
        rows = cursor.fetchall()

    return render(request, 'myapp/results.html', {'rows': rows})

Using Model.objects.raw()#

# myapp/views.py
from .models import Post

def raw_orm_view(request):
    # Returns model instances
    posts = Post.objects.raw('SELECT * FROM myapp_post WHERE id = %s', [1])

    for post in posts:
        print(post.title)  # Access as model instance

    # With joins
    posts = Post.objects.raw("""
        SELECT p.*, u.username as author_name
        FROM myapp_post p
        JOIN auth_user u ON p.author_id = u.id
    """)

    for post in posts:
        print(post.title, post.author_name)

Executing DDL (Data Definition Language)#

# In migration
from django.db import migrations, connection

def create_custom_index(apps, schema_editor):
    with connection.cursor() as cursor:
        cursor.execute("""
            CREATE INDEX IF NOT EXISTS post_title_idx 
            ON myapp_post(title)
        """)

def drop_custom_index(apps, schema_editor):
    with connection.cursor() as cursor:
        cursor.execute("DROP INDEX IF EXISTS post_title_idx")

class Migration(migrations.Migration):
    operations = [
        migrations.RunPython(create_custom_index, drop_custom_index),
    ]

Multiple Databases#

from django.db import connections

def query_analytics_db():
    with connections['analytics'].cursor() as cursor:
        cursor.execute("SELECT * FROM analytics_data")
        return cursor.fetchall()

🔍 Database Inspection#

Inspect Database Schema#

# Show database schema
python manage.py inspectdb

# Output to file
python manage.py inspectdb > models.py

# Inspect specific table
python manage.py inspectdb myapp_post

# Inspect specific database
python manage.py inspectdb --database=analytics

Database Shell#

# PostgreSQL
python manage.py dbshell

# Execute SQL directly
# psql> SELECT * FROM myapp_post;

🛠️ Database Utilities#

Reset Database#

# Delete database and recreate
# SQLite
rm db.sqlite3
python manage.py migrate

# PostgreSQL/MySQL
# Drop and recreate database manually, then:
python manage.py migrate

Flush Database#

# Clear all data but keep structure
python manage.py flush

# Interactive confirmation
python manage.py flush --noinput  # Skip confirmation

Load Data#

# Load fixtures
python manage.py loaddata fixtures/initial_data.json

# Load from multiple files
python manage.py loaddata fixtures/*.json

Dump Data#

# Dump all data
python manage.py dumpdata > fixtures/all_data.json

# Dump specific app
python manage.py dumpdata myapp > fixtures/myapp_data.json

# Dump specific model
python manage.py dumpdata myapp.Post > fixtures/posts.json

# Exclude specific apps
python manage.py dumpdata --exclude auth --exclude contenttypes > fixtures/data.json

⚠️ Migration Best Practices#

✅ Always create migrations - Never edit database directly
✅ Test migrations - Test on development before production
✅ Review migration files - Check auto-generated migrations
✅ Use data migrations - For data transformations
✅ Backup before migrating - Especially in production
✅ One migration per change - Easier to rollback
✅ Don't edit applied migrations - Create new migration instead
✅ Use transactions - For data migrations (see Advanced)

✅ Next Steps#

Learn ORM Queries to query data efficiently
Learn CRUD Operations for database operations
Learn Advanced Topics for transactions

Pro Tip: Always test migrations on a copy of production data before applying to production. Use --dry-run to preview SQL!