datus-postgresql

PostgreSQL database adapter for Datus.

Overview

This adapter provides PostgreSQL connectivity for Datus, supporting full SQL operations, metadata discovery, schema management, and materialized views via the standard PostgreSQL protocol.

Installation

pip install datus-postgresql

This will automatically install the required dependencies:

• datus-agent
• datus-sqlalchemy (which includes SQLAlchemy and psycopg2)

Usage

The adapter is automatically registered with Datus when installed. Configure your database connection:

namespace:
  postgresql_prod:
    type: postgresql
    host: localhost
    port: 5432
    username: postgres
    password: your_password
    database: mydb
    schema: public

Or use programmatically:

from datus_postgresql import PostgreSQLConnector, PostgreSQLConfig

# Using config object
config = PostgreSQLConfig(
    host="localhost",
    port=5432,
    username="postgres",
    password="password",
    database="mydb",
    schema_name="public",
)

connector = PostgreSQLConnector(config)

# Or using dict
connector = PostgreSQLConnector({
    "host": "localhost",
    "port": 5432,
    "username": "postgres",
    "password": "password",
    "database": "mydb",
})

# Use context manager for automatic cleanup
with connector:
    # Test connection
    connector.test_connection()

    # Execute queries
    result = connector.execute({"sql_query": "SELECT * FROM users LIMIT 10"})
    print(result.sql_return)

    # Get tables
    tables = connector.get_tables(schema_name="public")
    print(f"Tables: {tables}")

    # Get table schema
    columns = connector.get_schema(schema_name="public", table_name="users")
    for col in columns:
        print(f"  {col['name']}: {col['type']}")

    # Get materialized views
    mvs = connector.get_materialized_views(schema_name="public")
    print(f"Materialized Views: {mvs}")

Configuration Options

Features

• Full SQL Support: Execute queries, DDL, DML operations
• Metadata Discovery: Automatic discovery of databases, schemas, tables, views, and materialized views
• DDL Generation: Reconstruct CREATE TABLE / VIEW / MATERIALIZED VIEW statements
• Schema Management: Switch between schemas seamlessly
• Sample Data: Extract sample rows for data profiling
• Connection Management: SQLAlchemy-based connection pooling with SSL support
• Multiple Result Formats: pandas, arrow, csv, list

Code Structure

datus-postgresql/
├── datus_postgresql/
│   ├── __init__.py          # Package exports
│   ├── config.py            # PostgreSQLConfig (Pydantic model)
│   └── connector.py         # PostgreSQLConnector implementation
├── tests/
│   ├── unit/                # Unit tests with mocks (no database needed)
│   └── integration/         # Integration tests (requires PostgreSQL)
│       ├── conftest.py      # Test fixtures and TPC-H data setup
│       ├── test_integration.py  # General integration tests
│       └── test_tpch.py     # TPC-H benchmark data tests
├── scripts/
│   └── init_tpch_data.py    # TPC-H data initialization script
├── docker-compose.yml       # PostgreSQL test container
├── pyproject.toml
└── README.md

Testing

Quick Start

# 1. Start PostgreSQL test container
docker-compose up -d

# 2. Run tests
pytest tests/unit/ -v              # Unit tests (no database needed)
pytest tests/integration/ -v       # Integration tests (requires PostgreSQL)
pytest tests/ -v                   # All tests

Test Types

• Unit tests: Configuration and connector logic with mocks (no database needed)
• Integration tests: Real database operations (SQL, metadata, schema)
• TPC-H tests: Analytical query tests using TPC-H benchmark data

TPC-H Integration Tests

The adapter includes TPC-H benchmark data tests for validating analytical query capabilities.

TPC-H Tables

Running TPC-H Tests

# Start PostgreSQL container
docker-compose up -d

# Run TPC-H integration tests
pytest tests/integration/test_tpch.py -v

# Initialize TPC-H data manually (for ad-hoc testing)
python scripts/init_tpch_data.py \
    --host localhost --port 5432 \
    --username test_user --password test_password \
    --database test --schema public

# Drop and recreate TPC-H tables
python scripts/init_tpch_data.py --drop

Environment Variables

Tests use these default values (matching docker-compose.yml):

Requirements

• Python >= 3.12
• PostgreSQL >= 12
• datus-agent >= 0.2.1
• datus-sqlalchemy >= 0.1.0

License

Apache License 2.0

Related Packages

• datus-sqlalchemy - SQLAlchemy base connector
• datus-mysql - MySQL adapter
• datus-redshift - Amazon Redshift adapter
• datus-snowflake - Snowflake adapter