Skip to content

Database Configuration

Configure and optimize database connections for PyCharter.

Connection Strings

PostgreSQL

# Basic
"postgresql://user:pass@localhost:5432/dbname"

# With SSL
"postgresql://user:pass@host:5432/dbname?sslmode=require"

# With connection pool
"postgresql://user:pass@host:5432/dbname?pool_size=10"

MySQL

"mysql://user:pass@localhost:3306/dbname"

SQLite

"sqlite:///path/to/database.db"

"sqlite:///:memory:"  # In-memory

Contract Store Configuration

from pycharter import PostgresContractStore

store = PostgresContractStore(
    connection_string="postgresql://user:pass@localhost/pycharter",
    pool_size=10,
    pool_timeout=30,
)
store.connect()

Database Initialization

# Initialize database schema
pycharter db init

# Run migrations
pycharter db upgrade

# Check status
pycharter db current

Backups before upgrades

pycharter db upgrade backs up an existing SQLite database before migration preparation or schema changes. It uses SQLite's online backup API, including committed WAL contents, and checks the copied database before publishing it. Snapshots use unique UTC-stamped .bak names in a backups directory beside the database. On POSIX systems, new backup directories and files are private to the current user. Existing backup files are never replaced.

Create a separate snapshot with:

pycharter db backup
pycharter db backup --out ./backups/before-upgrade.db

These commands use the shared database resolver: an explicit database URL or global --database-url, then PYCHARTER_DATABASE_URL, the nearest pycharter.cfg, and finally the platform user-data directory. Explicit SQLite database URLs must be absolute; a relative --out path is relative to the current directory. The output file must not exist. The output filesystem must support hard links so a completed snapshot can be published atomically.

A failed backup stops the upgrade before any migration preparation writes. SQLite lock waits are limited to 30 seconds without progress. A failed migration keeps its completed pre-upgrade snapshot. An upgrade that creates a new database has no existing file to back up. In-memory SQLite and URI-mode connections need an explicit --no-backup bypass.

After making and verifying a separate backup, bypass automatic backup with:

pycharter db upgrade --no-backup

For PostgreSQL, both db backup and an upgrade without --no-backup exit nonzero with pg_dump guidance. Configure libpq for the same database selected by PyCharter, using PGSERVICE or PGHOST, PGPORT, PGUSER, and PGDATABASE, with your normal authentication. Choose an unused output filename:

pg_dump --format=custom --file pycharter-before-upgrade.dump
pg_restore --list pycharter-before-upgrade.dump
# After verifying that the backup meets your recovery requirements:
pycharter db upgrade --no-backup

Use your normal maintenance procedure to stop writers before schema upgrades. Online backup alone does not hold writers stopped through the subsequent migration. db restore is tracked separately under STORE-6.

Environment Variables

export PYCHARTER_DATABASE_URL="postgresql://user:pass@localhost/pycharter"
export DB_POOL_SIZE=10
export DB_POOL_TIMEOUT=30

import os
from pycharter import PostgresContractStore

store = PostgresContractStore(os.environ["PYCHARTER_DATABASE_URL"])

SSH Tunnel for Secure Connections

from pycharter import DatabaseExtractor

extractor = DatabaseExtractor(
    connection_string="postgresql://user:pass@localhost/db",
    ssh_tunnel={
        "host": "bastion.example.com",
        "port": 22,
        "username": "deploy",
        "key_file": "~/.ssh/id_rsa",
    },
)

Connection Pooling Best Practices

  1. Size the pool appropriately - Match to expected concurrent connections
  2. Set timeouts - Prevent hanging connections
  3. Use connection recycling - Prevent stale connections
  4. Monitor pool usage - Track connection wait times

See Also