Skip to content

Multi-database support

Pyvelm v1.x adds portable database backends first; multi-database routing (one process, many PostgreSQL tenants) ships after additional backends pass the same install/smoke bar. This guide is the user-facing companion to ADR 001: SQLAlchemy Core.

Two tracks

Track What it means First release
Portability One process, one DSN — PostgreSQL, SQLite, MySQL/MariaDB, MSSQL, Oracle v1.0.0 (Postgres + SQLite); v1.1 (MySQL/MariaDB); v1.2 (MSSQL); v1.3 (Oracle)
Multi-DB routing One process, many PostgreSQL databases (db selector, per-DB sessions) After Oracle — preview code exists; not a release target until portability is complete

v1.1–v1.3 are for additional backends, not tenant routing. Routing hooks (pool_map, PYVELM_DATABASES) are in the tree as a preview for early adopters only.

Configuration

Set PYVELM_DSN to a SQLAlchemy URL:

# PostgreSQL (production default)
export PYVELM_DSN="postgresql+psycopg://pyvelm:pyvelm@localhost:5432/pyvelm"

# Legacy form (normalised automatically)
export PYVELM_DSN="postgresql://pyvelm:pyvelm@localhost:5432/pyvelm"

# SQLite (dev / CI — single process only)
export PYVELM_DSN="sqlite:////tmp/pyvelm-dev.db"
export PYVELM_DSN="sqlite:///./var/app.db"

# MySQL / MariaDB (v1.1+)
export PYVELM_DSN="mysql+pymysql://pyvelm:pyvelm@localhost:3306/pyvelm"
export PYVELM_DSN="mariadb+pymysql://pyvelm:pyvelm@localhost:3306/pyvelm"

# Microsoft SQL Server (v1.2+ — requires pip install pyvelm[mssql] + ODBC Driver 18)
export PYVELM_DSN="mssql+pyodbc://user:pass@localhost:1433/pyvelm?driver=ODBC+Driver+18+for+SQL+Server&TrustServerCertificate=yes"

# Oracle (v1.3+ — requires pip install pyvelm[oracle])
export PYVELM_DSN="oracle+oracledb://user:pass@localhost:1521/?service_name=XEPDB1"

All CLI commands (pyvelm migrate, pyvelm serve, cron) read the same variable.

Backend matrix

Backend Status Role Constraints
PostgreSQL v1.0 Production reference Bundled module migrations; full feature set
SQLite v1.0 Dev, CI, embedded demos Single process; no multi-worker production
MySQL / MariaDB v1.1 Common OSS hosting Greenfield install + autogen; CI test-mysql + test-mariadb
Microsoft SQL Server v1.2 (in progress) Windows / Azure SQL Greenfield + autogen; pyodbc optional extra; OFFSET/FETCH pagination
Oracle v1.3 target Enterprise Greenfield + autogen; oracledb optional extra; identity columns

PostgreSQL

  • Reference backend for docs, performance, and bundled migrations/*.py.
  • Use postgresql+psycopg:// (psycopg 3 driver).

MySQL / MariaDB

  • Use mysql+pymysql:// or mariadb+pymysql:// (PyMySQL driver, bundled in pyvelm).
  • CI runs test-mysql (MySQL 8) and test-mariadb (MariaDB 11).
  • Quoted identifiers require ANSI_QUOTES — set automatically on connect.
  • Bundled Postgres-only migrations/*.py are skipped; use greenfield install + model-driven apply_schema_diff.
  • INSERT uses LAST_INSERT_ID() (no RETURNING dependency).

Microsoft SQL Server

  • Use mssql+pyodbc:// (install pip install pyvelm[mssql] and ODBC Driver 18).
  • CREATE TABLE IF NOT EXISTS is not used — tables are created only when absent.
  • Parameter placeholders use ? (pyodbc); the connection adapter rewrites portable %s SQL.
  • ALTER TABLE uses ADD col type (not ADD COLUMN) for new columns on upgrade.
  • QUOTED_IDENTIFIER ON is set on connect (double-quoted identifiers match other backends).
  • Pagination uses OFFSET … ROWS FETCH NEXT … ROWS ONLY (SQL Server 2012+).
  • Insert IDs use SCOPE_IDENTITY() (no RETURNING dependency).
  • Bundled Postgres-only migrations/*.py are skipped; greenfield install + model-driven apply_schema_diff.

Oracle

  • Use oracle+oracledb:// (install pip install pyvelm[oracle]; thin mode needs no Instant Client for basic TCP).
  • Integer PKs use GENERATED BY DEFAULT AS IDENTITY (12c+).
  • CREATE TABLE IF NOT EXISTS is not used — tables are created only when absent.
  • Bundled Postgres-only migrations/*.py are skipped; greenfield install + model-driven apply_schema_diff.
  • CI runs test-oracle (gvenzl/oracle-free service; slower startup than other matrix jobs).

SQLite

Supported for:

  • Local development without Docker Postgres
  • Fast CI jobs
  • Embedded / offline demos

Not supported for:

  • Multi-worker production (Gunicorn workers > 1 on one SQLite file)
  • Serverless ephemeral disk (use managed Postgres)
  • Replaying historical Postgres-only migration scripts — use greenfield install
  • model-driven apply_schema_diff instead

Schema reset on SQLite drops all tables (or deletes the file), not DROP SCHEMA … CASCADE.

Architecture

PYVELM_DSN
    └── sqlalchemy.create_engine
            ├── pool (checkout per request / CLI command)
            ├── DialectCapabilities (backend flags)
            ├── dialects/ (per-backend helpers: mysql, mssql, oracle, …)
            └── ConnectionAdapter → Environment.conn
                    ├── model.py (DML / DDL via Core)
                    ├── domain.py (Core boolean expressions)
                    └── db_autogen.py (Inspector)

Unchanged conceptually: BaseModel, recordsets, env.cache, domain language, module loader, views, ACL, Registry.

Not adopted: SQLAlchemy ORM (see ADR 001).

Migrations policy

Source PostgreSQL SQLite
Model-driven install + apply_schema_diff Yes Yes
Bundled pyvelm/modules/*/migrations/*.py Yes (replay on upgrade) No (Postgres DDL)
Hand-written app migrations Postgres-authored Greenfield + autogen

Hand-written migration modules may declare supported_backends = ("postgresql",) (see module migration protocol in migrations.md).

v1.0 exit criteria

  • pyvelm migrate succeeds on Postgres and SQLite
  • examples/basic.py (or equivalent smoke) passes on both
  • HTTP smoke tests pass in CI for both backends
  • Docs updated: cli.md, deployment.md, migrations.md

Roadmap

Version Deliverable
v1.0.0 SQLAlchemy Core layer; Postgres + SQLite end-to-end
v1.1.0 MySQL / MariaDB — same install/smoke bar as v1.0
v1.2.0 Microsoft SQL Server — greenfield install + CI smoke subset
v1.3.0 Oracle — greenfield install + nightly CI smoke
v1.4.0+ Multi-DB routing on Postgres (selector, pool_map, session binding)
Later Optional Alembic for app-authored migrations

v1.1 exit criteria (MySQL / MariaDB)

  • DialectCapabilities for mysql / mariadb (placeholders, ILIKE, RETURNING, schema reset)
  • pyvelm migrate greenfield install on MySQL/MariaDB service container
  • examples/basic.py (or equivalent smoke) passes
  • HTTP smoke subset in CI matrix (alongside Postgres + SQLite)
  • Bundled Postgres-only migrations/*.py skipped; model-driven apply_schema_diff carries schema

Multi-DB routing (preview — not v1.1)

Routing is implemented in preview but deferred until v1.1/v1.2 portability ships. When PYVELM_DATABASES lists tenant Postgres databases:

  • Config — comma-separated key=dsn or JSON array (see below)
  • Middleware — DatabaseSelectorMiddleware sets the active DB from cookie, host, or /web/db/<key>/… path
  • app.state — pool_map and lazy registry_cache per database
  • Sessions — bound to the selected database (pyvelm_db cookie)
  • CLI — pyvelm migrate --database tenant_a
  • UI — /web/database/selector (Odoo-style database picker)

Example:

export PYVELM_DSN="postgresql+psycopg://pyvelm:pyvelm@localhost:5432/main"
export PYVELM_DATABASES='tenant_a=postgresql+psycopg://pyvelm:pyvelm@localhost:5432/tenant_a'

Or JSON:

export PYVELM_DATABASES='[{"key":"tenant_a","dsn":"postgresql+psycopg://…","label":"Tenant A"}]'

See ADR 001 for implementation status and roadmap.