Skip to content

Database Management

PostgreSQL 16+ with schema-per-tenant isolation. Migration commands, schema management, and connection configuration.

Configuration

# backend/config/settings/base.py
DATABASES = {
    "default": env.db("DATABASE_URL",
        default="postgres://lms:lms@localhost:5432/lms")
}
DATABASES["default"]["ENGINE"] = "django_pgschemas.postgresql"
DATABASE_ROUTERS = ("django_pgschemas.routers.TenantAppsRouter",)

The custom engine (django_pgschemas.postgresql) manages PostgreSQL search_path switching based on the active tenant.

Schema Architecture

PostgreSQL Database (lms)
├── public               # Tenant registry, content types
├── main                 # LMS home site (admin, allauth, users)
├── tenant_acme          # Tenant "Acme Lending" business data
├── tenant_demo          # Tenant "Demo Corp" business data
└── ...                  # One schema per tenant

Each dynamic tenant schema contains the full set of LMS application tables (loans, payments, borrowers, etc.).

Migration Commands

migrateschema

Run Django migrations across schemas:

# All schemas (public + static + dynamic)
uv run python manage.py migrateschema -as

# Dynamic tenant schemas only
uv run python manage.py migrateschema -ds

# Static schemas only (public, main)
uv run python manage.py migrateschema -ss

# Specific schema by name
uv run python manage.py migrateschema -s tenant_acme

# Parallel execution across tenants
uv run python manage.py migrateschema -ds --parallel

# Exclude specific schema
uv run python manage.py migrateschema -as -x tenant_demo

runschema

Run any management command across schemas:

# Load fixtures into a specific tenant
uv run python manage.py runschema loaddata seed.json -s tenant_acme

# Run a command on all dynamic tenants
uv run python manage.py runschema check -ds

whowill

Preview which schemas a command would target (dry run):

uv run python manage.py whowill -ds
# Output: tenant_acme, tenant_demo, ...

Creating a New Tenant

When a Tenant object is created with auto_create_schema = True:

  1. A new PostgreSQL schema (tenant_{slug}) is created
  2. All tenant-app migrations are applied to the new schema
  3. A Domain record maps the subdomain to the tenant
from apps.tenants.models import Tenant, Domain

tenant = await Tenant.objects.acreate(
    name="Acme Lending",
    slug="acme",
    status="active",
)
# Schema tenant_acme is auto-created and migrated

await Domain.objects.acreate(
    tenant=tenant,
    domain="acme.app.lendsmart.io",
    is_primary=True,
)

Data Migrations

RunSQL (Preferred for Tenant Schemas)

Use RunSQL instead of RunPython for data migrations in tenant apps. RunPython triggers StateApps construction which can fail with cross-schema FK references:

# GOOD — safe for tenant schemas
migrations.RunSQL(
    sql="UPDATE django_content_type SET app_label = 'new_app' WHERE ...",
    reverse_sql="UPDATE django_content_type SET app_label = 'old_app' WHERE ...",
)

# AVOID in tenant schemas — may fail with StateApps construction
migrations.RunPython(my_function, reverse_code=my_reverse)

SeparateDatabaseAndState

For moving models between apps:

migrations.SeparateDatabaseAndState(
    database_operations=[
        migrations.RunSQL("ALTER TABLE old_app_model RENAME TO new_app_model"),
    ],
    state_operations=[
        migrations.CreateModel(name="Model", fields=[...]),
    ],
)

Connection Management

  • Driver: psycopg 3 (async-capable)
  • Connection pooling: Django's built-in connection management (CONN_MAX_AGE)
  • Test cleanup: Autouse fixtures close connections after each test and terminate all sessions on teardown

Test Connection Cleanup

# backend/conftest.py
@pytest.fixture(autouse=True)
def _close_db_connections():
    yield
    connections.close_all()

@pytest.fixture(scope="session")
def _terminate_db_connections_on_teardown(django_db_setup):
    yield
    # pg_terminate_backend for all sessions to allow DB drop

Backup Considerations

Schema-per-tenant architecture affects backup strategy:

  • Full dump: pg_dump captures all schemas in one dump
  • Per-tenant dump: Use pg_dump -n tenant_acme for single-tenant backups
  • Restore: Must restore public schema first (tenant registry), then tenant schemas
  • Migrations after restore: Run migrateschema -as to apply any pending migrations

See Also