Skip to content

Database Configuration

DocuElevate uses SQLAlchemy as its ORM and Alembic for schema migrations. Any SQLAlchemy-compatible database is supported; this guide covers the most common choices.

Table of Contents

Database Configuration Wizard

DocuElevate includes a guided Database Configuration Wizard accessible at /database-wizard. The wizard walks you through building a connection string step by step — no need to remember the exact URL format.

How to Access

Navigate to /database-wizard in your browser, or find the link under Admin → Settings. On the Settings page, click the DB Wizard button in the toolbar, or look for the Open Database Wizard link next to the database_url setting.

Wizard Steps

  1. Choose Database Type — select SQLite, PostgreSQL, or MySQL/MariaDB.
  2. Connection Details — enter host, port, database name, credentials, and SSL mode (auto-populated with sensible defaults).
  3. Test & Apply — test the connection before committing, then copy the generated DATABASE_URL into your .env file.

The wizard generates the full SQLAlchemy connection string and lets you test connectivity directly from the UI. After testing, copy the DATABASE_URL=… line into your .env file (or Docker Compose environment) and restart DocuElevate.

REST API

The wizard is backed by a REST API under /api/database/:

Endpoint Method Description
/api/database/backends GET List supported database backends
/api/database/build-url POST Build a connection string from components
/api/database/parse-url POST Parse a connection string into components
/api/database/validate-url POST Validate URL format without connecting
/api/database/test-connection POST Test connectivity to a database

Note: All write endpoints require admin authentication.

Database Migration Tool

The Migrate Data tab (on the same /database-wizard page) lets you copy all your data from one database to another — for example, migrating from the built-in SQLite database to an external PostgreSQL or MySQL instance.

When to Use

  • Moving from a development SQLite database to a production PostgreSQL instance.
  • Migrating to a managed cloud database (AWS RDS, Google Cloud SQL, Azure Database for PostgreSQL, Supabase, etc.).
  • Consolidating data from one database engine to another.

How It Works

  1. Enter (or auto-fill) the Source Database URL — this is your current database.
  2. Enter the Target Database URL — the new, empty database to copy data into.
  3. Click Test Source and Test Target to verify both connections.
  4. Click Preview Migration to see a table-by-table row count.
  5. Confirm and click Start Migration to copy all data.

The migration tool: - Creates the full schema in the target database from the application models. - Copies all rows table by table in dependency order (parent tables first). - Stamps the Alembic migration version to head in the target.

After migration, update your DATABASE_URL environment variable to point at the new database and restart DocuElevate.

REST API

Endpoint Method Description
/api/database/preview-migration POST Preview tables and row counts
/api/database/migrate POST Execute the full data migration

Warning: Always run the migration against an empty target database. The tool does not delete existing data in the target before copying.

Supported Databases

Database Recommended Use Notes
SQLite Development, single-user demos Default. File-based. Not safe for multi-replica deployments.
PostgreSQL Production, multi-replica Strongly recommended for production. Full feature support.
MySQL / MariaDB Production (alternative) Supported; PostgreSQL preferred for JSON column support.

Configuration

Set the DATABASE_URL environment variable to point at your database:

# SQLite (default)
DATABASE_URL=sqlite:///./app/database.db

# PostgreSQL
DATABASE_URL=postgresql://user:password@host:5432/docuelevate

# PostgreSQL with SSL
DATABASE_URL=postgresql://user:password@host:5432/docuelevate?sslmode=require

# MySQL
DATABASE_URL=mysql+pymysql://user:password@host:3306/docuelevate

For Docker Compose, add this to your .env file or docker-compose.yml:

environment:
  DATABASE_URL: postgresql://docuelevate:secret@postgres:5432/docuelevate

SQLite (Development)

SQLite requires no additional services and is the zero-configuration default.

DATABASE_URL=sqlite:///./app/database.db

Limitations:

  • Cannot be safely shared across multiple processes or containers.
  • Not suitable for any multi-replica deployment.
  • No support for concurrent writes under load.
  • Backup is a simple file copy, but requires the application to be stopped to ensure consistency.

When to use: Local development, automated testing, or single-user self-hosted setups with no redundancy requirement.

PostgreSQL (Production)

PostgreSQL is the recommended database for any production deployment.

Install and Create the Database

-- Run as a PostgreSQL superuser (e.g. postgres)
CREATE USER docuelevate WITH PASSWORD 'strongpassword';
CREATE DATABASE docuelevate OWNER docuelevate;
GRANT ALL PRIVILEGES ON DATABASE docuelevate TO docuelevate;

Docker Compose with Bundled PostgreSQL

Add a postgres service to your docker-compose.yml:

services:
  postgres:
    image: postgres:16-alpine
    environment:
      POSTGRES_USER: docuelevate
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
      POSTGRES_DB: docuelevate
    volumes:
      - postgres_data:/var/lib/postgresql/data
    restart: unless-stopped

  api:
    depends_on:
      - postgres
    environment:
      DATABASE_URL: postgresql://docuelevate:${POSTGRES_PASSWORD}@postgres:5432/docuelevate

volumes:
  postgres_data:

Set POSTGRES_PASSWORD in your .env file.

Managed Cloud PostgreSQL

Using a managed service (AWS RDS, Google Cloud SQL, Azure Database for PostgreSQL, Supabase, etc.) is recommended for production because it handles backups, point-in-time recovery, and maintenance.

Example for Amazon RDS:

DATABASE_URL=postgresql://docuelevate:strongpassword@my-instance.us-east-1.rds.amazonaws.com:5432/docuelevate?sslmode=require

Connection String Format

postgresql://<user>:<password>@<host>:<port>/<database>[?<options>]

Common options:

Option Description
sslmode=require Require TLS for the connection
sslmode=verify-full Require TLS and verify the server certificate
connect_timeout=10 Connection timeout in seconds
application_name=docuelevate Identifies the connection in pg_stat_activity

MySQL / MariaDB

MySQL and MariaDB are supported via the PyMySQL driver.

# Install the driver (it is not included by default)
pip install pymysql cryptography

# Connection string
DATABASE_URL=mysql+pymysql://user:password@host:3306/docuelevate?charset=utf8mb4

Requirements:

  • Use the utf8mb4 charset for full Unicode support.
  • Set innodb_large_prefix=ON if using MySQL < 5.7.7.
  • PostgreSQL is preferred because SQLAlchemy's JSON column type has better support with it.

Schema Migrations with Alembic

DocuElevate uses Alembic exclusively to manage all database schema changes. On application startup, init_db() automatically applies any pending Alembic migrations, so the database schema is always kept in sync with the running code.

Note: Prior to this change, a manual _run_schema_migrations() helper in app/database.py applied schema changes outside of Alembic. That function is now deprecated and will be removed in a future release. All schema changes are tracked as Alembic revisions in migrations/versions/.

How It Works

  1. Fresh databases — Base.metadata.create_all() creates all tables from the SQLAlchemy models, then Alembic stamps the version to head (no migrations need to run).
  2. Existing databases — alembic upgrade head applies any pending migration scripts.
  3. CLI usage — You can still run alembic upgrade head manually or in CI/CD before the application starts.

Apply Migrations

Run this command whenever you update DocuElevate or change the schema:

alembic upgrade head

This applies all pending migrations in order. It is idempotent — safe to run multiple times.

Check Current Version

alembic current

View Migration History

alembic history --verbose

Roll Back One Migration

alembic downgrade -1

Roll Back to a Specific Revision

alembic downgrade <revision_id>

Creating a New Migration (Developers)

After changing app/models.py:

alembic revision --autogenerate -m "describe your change"

Review the generated file in migrations/versions/ before applying it.

Tip: For detailed guidance on naming conventions, idempotent patterns, parallel-branch workflows, and resolving merge conflicts, see the Migration Workflow Guide.

Validating the Migration Chain

A CI check and pre-commit hook validate that the migration chain has no broken references, duplicate revisions, or diverged heads. Run the check locally:

python scripts/check_alembic_migrations.py
python scripts/check_alembic_migrations.py --verbose   # extra detail

If you see "Multiple migration heads detected", two branches added migrations from the same parent. Create a merge migration:

alembic merge heads -m "merge_parallel_branches"

For a complete walk-through, see the Migration Workflow Guide.

Automating Migrations in Docker Compose

Add a short-lived migrate service that runs before the API and Worker:

services:
  migrate:
    image: ghcr.io/christianlouis/docuelevate:latest
    command: alembic upgrade head
    env_file: .env
    depends_on:
      postgres:
        condition: service_healthy
    restart: "no"

  api:
    depends_on:
      migrate:
        condition: service_completed_successfully

Automating Migrations in Kubernetes (Helm)

The Helm chart includes a pre-install and pre-upgrade Job hook that runs alembic upgrade head automatically before new pods start. No manual intervention is needed during helm upgrade.

Connection Pooling

SQLAlchemy manages a connection pool automatically. DocuElevate selects the pool strategy based on the database backend:

  • SQLite — uses NullPool (a fresh connection per request, closed immediately). This avoids the QueuePool limit reached TimeoutError that can occur under concurrent load because SQLite does not benefit from persistent connection pooling.
  • PostgreSQL / MySQL — uses a bounded QueuePool whose size is configurable via environment variables.
# Tune these for PostgreSQL / MySQL (ignored when using SQLite):
DB_POOL_SIZE=10         # Number of persistent connections per worker (default: 10)
DB_MAX_OVERFLOW=20      # Additional connections allowed beyond pool_size (default: 20)
DB_POOL_TIMEOUT=30      # Seconds to wait for a connection from the pool (default: 30)
DB_POOL_RECYCLE=1800    # Recycle connections after 30 minutes (default: 1800)

All backends also enable pool_pre_ping, which sends a lightweight health-check before each connection is handed out. This detects stale or dropped connections and transparently reconnects.

For PgBouncer (external connection pooling), point DATABASE_URL at your PgBouncer instance and use transaction-mode pooling:

DATABASE_URL=postgresql://docuelevate:password@pgbouncer:5432/docuelevate?prepared_statements=false

Disable prepared_statements when using PgBouncer in transaction mode.

Backup Procedures

DocuElevate's built-in Backup & Restore feature (Admin → Backup & Restore) supports all three database backends natively, using the native dump tools of each database.

Backend Backup tool Archive extension Restore tool
SQLite sqlite3 (built-in Python) .db.gz sqlite3 (built-in Python)
PostgreSQL pg_dump .pgsql.gz psql
MySQL/MariaDB mysqldump .mysql.gz mysql

Passwords are passed via the PGPASSWORD (PostgreSQL) and MYSQL_PWD (MySQL) environment variables so they are never exposed on the process command line.

Prerequisites

For PostgreSQL and MySQL backups the corresponding CLI client must be installed on the worker host (the container / server that runs Celery workers):

# PostgreSQL clients (Debian/Ubuntu)
apt-get install -y postgresql-client

# MySQL clients (Debian/Ubuntu)
apt-get install -y default-mysql-client

The binaries required are:

  • PostgreSQL: pg_dump (backup) and psql (restore)
  • MySQL / MariaDB: mysqldump (backup) and mysql (restore)

Using the Admin Dashboard

Navigate to Admin → Backup & Restore to:

  • Trigger manual backups (hourly / daily / weekly)
  • Download backup archives
  • Upload and restore a backup archive
  • Configure retention and remote storage destinations

PostgreSQL – manual backup/restore

Manual backup using DocuElevate's archive format (for use with the UI restore):

pg_dump --format=plain --no-password \
  -h localhost -U docuelevate docuelevate \
  | gzip > docuelevate_$(date +%Y%m%d_%H%M).pgsql.gz

Restore via the DocuElevate UI: upload the .pgsql.gz file on the Backup & Restore page.

Manual restore using native tools (custom format):

pg_dump -h localhost -U docuelevate -F c docuelevate > docuelevate_$(date +%Y%m%d_%H%M).dump
pg_restore -h localhost -U docuelevate -d docuelevate docuelevate_20240101_1200.dump

Automated daily backup (cron example):

0 2 * * * pg_dump --format=plain -h localhost -U docuelevate docuelevate | gzip > /backups/docuelevate_$(date +\%Y\%m\%d).pgsql.gz

Use your cloud provider's automated backup feature when available (e.g., RDS automated snapshots, Cloud SQL backups).

MySQL / MariaDB – manual backup/restore

Manual backup using DocuElevate's archive format (for use with the UI restore):

MYSQL_PWD=yourpassword mysqldump --single-transaction --routines --triggers \
  -h localhost -u docuelevate docuelevate \
  | gzip > docuelevate_$(date +%Y%m%d_%H%M).mysql.gz

Restore via the DocuElevate UI: upload the .mysql.gz file on the Backup & Restore page.

Manual restore using native tools:

gunzip -c docuelevate_20240101_1200.mysql.gz | mysql -h localhost -u docuelevate -p docuelevate

SQLite – manual backup/restore

# Stop the application first, or use SQLite's online backup API
cp app/database.db /backups/docuelevate_$(date +%Y%m%d).db

Performance Optimization

PostgreSQL Index Recommendations

Run ANALYZE periodically to keep query planner statistics up to date:

ANALYZE;

For large deployments, consider adding indexes on frequently queried columns. Review the Alembic migrations in migrations/versions/ for the current schema and add any additional indexes as a new Alembic migration.

Vacuum and Autovacuum

PostgreSQL's autovacuum daemon runs automatically, but for write-heavy workloads you may want to tune it or run VACUUM ANALYZE manually after large batch imports.

Query Monitoring

Enable pg_stat_statements to monitor slow queries:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT query, mean_exec_time, calls
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 20;

Troubleshooting

"Connection refused" when starting

  • Verify the database host and port are reachable from the application container.
  • For Docker Compose, ensure the postgres service has started before the api service — use depends_on with condition: service_healthy.
  • Check PostgreSQL is listening: pg_isready -h <host> -p 5432

"SSL connection required"

Add ?sslmode=require to your DATABASE_URL, or set sslmode=disable if connecting to an internal-only database that does not use TLS.

Alembic migration fails with "relation already exists"

The database is ahead of Alembic's tracked version. Stamp the current version without re-running migrations:

alembic stamp head

Then retry alembic upgrade head.

"too many connections" error

Either increase max_connections in postgresql.conf or add PgBouncer in front of PostgreSQL. The default PostgreSQL max_connections is 100; reduce DB_POOL_SIZE per worker to stay within this limit.

"QueuePool limit reached" TimeoutError (SQLite)

If you see TimeoutError: QueuePool limit of size 5 overflow 10 reached, your deployment is still running an older version of DocuElevate that used a bounded connection pool for SQLite. Upgrade to the latest release — SQLite now uses NullPool, which eliminates this error entirely. If you are already on the latest version and are still seeing pool exhaustion, ensure you are not overriding the engine creation manually.

For more help, see the Troubleshooting Guide.