Database Setup¶

The starter kit uses PostgreSQL with the pgvector extension for vector similarity search. This guide covers the recommended database providers.

Provider Comparison¶

Recommended: Neon¶

Neon is the recommended option for most deployments due to its serverless architecture and excellent developer experience.

Setup¶

1. Create account at neon.tech
2. Create a new project
3. Enable pgvector extension:
   sql CREATE EXTENSION vector;
4. Copy the connection string

Configuration¶

DATABASE_URL=postgres://user:password@ep-xxx.region.aws.neon.tech/dbname?sslmode=require

Features¶

• Serverless: Scales to zero when idle
• Branching: Create database branches for development/testing
• Point-in-time recovery: Restore to any point in time
• Connection pooling: Built-in connection pooler

Connection Pooling¶

Use the pooled connection string for Django:

# Pooled connection (recommended for Django)
DATABASE_URL=postgres://user:password@ep-xxx.region.aws.neon.tech/dbname?sslmode=require&pgbouncer=true

# Direct connection (for migrations)
DATABASE_URL_DIRECT=postgres://user:password@ep-xxx.region.aws.neon.tech/dbname?sslmode=require

Alternative: Supabase¶

Supabase is a good choice if you want additional features like authentication, storage, and edge functions.

Setup¶

1. Create account at supabase.com
2. Create a new project
3. Enable pgvector in SQL editor:
   sql CREATE EXTENSION vector;
4. Go to Settings → Database for connection string

Configuration¶

# Transaction pooler (recommended)
DATABASE_URL=postgres://postgres.[project-ref]:[password]@aws-0-[region].pooler.supabase.com:6543/postgres

# Direct connection
DATABASE_URL_DIRECT=postgres://postgres:[password]@db.[project-ref].supabase.co:5432/postgres

Supabase Features¶

• Built-in authentication (can replace or complement allauth)
• Storage for files and media
• Edge functions
• Real-time subscriptions
• REST and GraphQL APIs

Note on Authentication¶

When using Supabase, you can either:
1. Use django-allauth (recommended, current setup)
2. Use Supabase Auth with Django JWT validation
3. Hybrid approach for specific use cases

Self-Hosted: Docker Compose¶

For complete control or cost-sensitive deployments, self-host PostgreSQL.

Docker Compose Configuration¶

# compose.yaml
services:
  db:
    image: pgvector/pgvector:pg16
    restart: always
    volumes:
      - postgres_data:/var/lib/postgresql/data
    environment:
      POSTGRES_DB: advantch
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: ${DB_PASSWORD}
    ports:
      - "5432:5432"
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U postgres"]
      interval: 10s
      timeout: 5s
      retries: 5

volumes:
  postgres_data:

Configuration¶

DATABASE_URL=postgres://postgres:password@localhost:5432/advantch

Production Considerations¶

For production self-hosted deployments:

1. Connection Pooling: Add PgBouncer
   yaml pgbouncer: image: edoburu/pgbouncer environment: DATABASE_URL: postgres://postgres:password@db:5432/advantch POOL_MODE: transaction MAX_CLIENT_CONN: 100 DEFAULT_POOL_SIZE: 20

2. Backups: Schedule regular backups
   bash pg_dump -U postgres advantch > backup_$(date +%Y%m%d).sql

3. Monitoring: Add pg_stat_statements
   sql CREATE EXTENSION pg_stat_statements;

4. High Availability: Consider Patroni or Stolon for clustering

Django Configuration¶

settings.py¶

import dj_database_url

DATABASES = {
    "default": dj_database_url.config(
        default="postgres://postgres:postgres@localhost:5432/advantch",
        conn_max_age=600,
        conn_health_checks=True,
    )
}

pgvector Setup¶

Ensure pgvector is available:

# After running migrations
from django.db import connection

with connection.cursor() as cursor:
    cursor.execute("CREATE EXTENSION IF NOT EXISTS vector")

Migrations¶

Running Migrations¶

# Local
uv run python manage.py migrate

# Production (Fly.io)
fly ssh console -C "python manage.py migrate"

# Production (Docker)
docker compose exec django python manage.py migrate

Migration Best Practices¶

1. Always backup before migrations
2. Test migrations on a database branch (Neon) or staging
3. Use --plan to preview migrations:
   bash python manage.py migrate --plan

Vector Search Setup¶

For AI features using pgvector:

from pgvector.django import VectorField

class Document(models.Model):
    content = models.TextField()
    embedding = VectorField(dimensions=1536)  # OpenAI embedding size

    class Meta:
        indexes = [
            # IVFFlat index for faster similarity search
            models.Index(
                fields=['embedding'],
                name='document_embedding_idx',
                opclasses=['vector_cosine_ops']
            )
        ]

Troubleshooting¶

Connection Issues¶

1. Check connection string format
2. Verify SSL mode for cloud providers
3. Check firewall/security groups
4. Use connection pooler for serverless

Performance Issues¶

1. Enable pg_stat_statements for query analysis
2. Check index usage with EXPLAIN ANALYZE
3. Monitor connection count
4. Consider read replicas for read-heavy workloads

Next Steps¶

• Quick Start
• Deploy Backend
• Local Testing Setup