Skip to content

PostgreSQL

The PostgreSQL provider uses SQLAlchemy under the covers as the ORM to communicate with the database. It is the recommended provider for production deployments.

Overview

PostgreSQL is a production-grade relational provider that supports the full range of database capabilities, including native JSON and array columns. It provides real ACID transactions, connection pooling, and schema management.

Installation

pip install "protean[postgresql]"

This installs psycopg2-binary, a pre-compiled binary that works out of the box with no system dependencies.

For production deployments, you may prefer psycopg2 (compiled from source against your system's libpq). To use it, install it explicitly in place of psycopg2-binary:

pip install psycopg2

Both packages provide the same psycopg2 Python module — only one should be installed at a time. See the psycopg2 installation guide for system prerequisites when building from source.

Configuration

[databases.default]
provider = "postgresql"
database_uri = "postgresql://postgres:postgres@localhost:5432/postgres"

Configuration Options

Option Default Description
provider Required Must be "postgresql" for PostgreSQL
database_uri Required Connection string (see format below)
schema None Database schema to use (e.g. "myapp")
pool_size 5 Number of connections in the SQLAlchemy connection pool
max_overflow 10 Additional connections allowed beyond pool_size

Connection String Format

postgresql://[username]:[password]@[host]:[port]/[database]

# Examples:
postgresql://postgres:postgres@localhost:5432/postgres
postgresql://user:pass@db.example.com:5432/myapp
postgresql://user:pass@db.example.com/myapp?sslmode=require

Capabilities

The PostgreSQL provider supports the following capabilities:

  • ✅ CRUD -- Create, Read, Update, Delete single records
  • ✅ FILTER -- Query/filter records with lookup criteria
  • ✅ BULK_OPERATIONS -- update_all(), delete_all()
  • ✅ ORDERING -- Server-side ORDER BY support
  • ✅ TRANSACTIONS -- Real commit/rollback ACID atomicity
  • ✅ OPTIMISTIC_LOCKING -- Version-based concurrency control
  • ✅ RAW_QUERIES -- Execute raw SQL queries
  • ✅ SCHEMA_MANAGEMENT -- Create/drop tables and indices
  • ✅ CONNECTION_POOLING -- SQLAlchemy connection pool management
  • ✅ NATIVE_JSON -- PostgreSQL JSONB column type
  • ✅ NATIVE_ARRAY -- PostgreSQL ARRAY column type

PostgreSQL is the only built-in provider that supports all 12 capability flags (excluding SIMULATED_TRANSACTIONS, which is specific to the Memory provider).

SQLAlchemy Model

You can supply a custom SQLAlchemy Model in place of the one that Protean generates internally, allowing you full customization.

import sqlalchemy as sa

from protean import Domain
from protean.adapters.repository.sqlalchemy import SqlalchemyModel
from protean.fields import Integer, String

domain = Domain()
domain.config["databases"]["default"] = {
    "provider": "postgresql",
    "database_uri": "postgresql://postgres:postgres@localhost:5432/postgres",
}


@domain.aggregate
class Provider:
    name: String()
    age: Integer()


@domain.database_model(part_of=Provider)
class ProviderCustomModel:
    name = sa.Column(sa.Text)
    age = sa.Column(sa.Integer)


domain.init()
with domain.domain_context():
    database_model_cls = domain.repository_for(Provider)._database_model
    assert issubclass(database_model_cls, SqlalchemyModel)

Note

The column names specified in the model should exactly match the attribute names of the Aggregate or Entity it represents.

Raw Queries

Use the raw() method to execute SQL directly:

results = domain.providers["default"].raw(
    "SELECT * FROM users WHERE age > :age",
    {"age": 21}
)

Raw queries execute immediately in their own transaction context. Results are returned as-is from the database without entity conversion.

Slow Query Detection

Protean installs SQLAlchemy before_cursor_execute / after_cursor_execute listeners on the engine at provider construction time and emits two structured log events per query:

  • protean.adapters.repository.sqlalchemy.query at DEBUG for every query (opt-in via normal level configuration).
  • protean.adapters.repository.sqlalchemy.slow_query at WARNING when a query exceeds the configured threshold.

Both events carry statement, parameters, duration_ms, and threshold_ms. The slow-query logger is separate so operators can route its alerts independently (e.g. to PagerDuty) while leaving the DEBUG tracing logger silent in production.

Tune the behavior via the [logging] section of domain.toml:

[logging]
slow_query_threshold_ms = 100    # WARN when a query exceeds this (ms)
slow_query_truncate_chars = 500  # max statement length in log events

Set slow_query_threshold_ms = 0 to WARN on every query (useful during performance investigations). Correlation context (correlation_id, causation_id) flows onto every record automatically via the root logger's ProteanCorrelationFilter.

Limitations

  • Requires Running Server -- PostgreSQL must be installed and running as a separate service. Use make up to start Protean's Docker-based development services.
  • Connection Overhead -- Each connection consumes server resources. Tune pool_size and max_overflow for your workload.

Next Steps