iris-pgwire: PostgreSQL Wire Protocol for InterSystems IRIS

Access IRIS through the entire PostgreSQL ecosystem - Connect BI tools, Python frameworks, data pipelines, and thousands of PostgreSQL-compatible clients to InterSystems IRIS databases with zero code changes.

📊 Why This Matters

Verified compatibility with PostgreSQL clients across 8 languages - no IRIS-specific drivers needed:

• Tested & Working: Python (psycopg3, asyncpg), Node.js (pg), Java (JDBC), .NET (Npgsql), Go (pgx), Ruby (pg gem), Rust (tokio-postgres), PHP (PDO)
• BI Tools: Apache Superset, Metabase, Grafana (use standard PostgreSQL driver)
• ORMs: SQLAlchemy, Prisma, Sequelize, Hibernate, Drizzle

Connection: postgresql://localhost:5432/USER - that’s it!

🚀 Quick Start

Docker (Fastest - 60 seconds)

git clone https://github.com/intersystems-community/iris-pgwire.git
cd iris-pgwire
Create persistent IRIS container (for development/testing)
./scripts/create_persistent_container.sh
Start PGWire server
export IRIS_HOST=localhost IRIS_PORT=21972 IRIS_USERNAME=_SYSTEM IRIS_PASSWORD=SYS IRIS_NAMESPACE=USER
python -m iris_pgwire.server
Test it works (in another terminal)
psql -h localhost -p 5432 -U _SYSTEM -d USER -c "SELECT 'Hello from IRIS!'"

Python Package

pip install iris-pgwire psycopg[binary]
Configure IRIS connection
export IRIS_HOST=localhost IRIS_PORT=1972 IRIS_USERNAME=_SYSTEM IRIS_PASSWORD=SYS IRIS_NAMESPACE=USER
Start server
python -m iris_pgwire.server

ZPM Installation (Existing IRIS)

For InterSystems IRIS 2024.1+ with ZPM package manager:

// Install the package
zpm "install iris-pgwire"
// Start the server manually
do ##class(IrisPGWire.Service).Start()
// Check server status
do ##class(IrisPGWire.Service).ShowStatus()

From terminal:

# Install
iris session IRIS -U USER 'zpm "install iris-pgwire"'
Start server
iris session IRIS -U USER 'do ##class(IrisPGWire.Service).Start()'

First Query

import psycopg
with psycopg.connect('host=localhost port=5432 dbname=USER') as conn:
cur = conn.cursor()
cur.execute('SELECT COUNT(*) FROM YourTable')
print(f'Rows: {cur.fetchone()[0]}')

✅ Client Compatibility

171/171 tests passing across 8 programming languages:

ORMs & BI Tools: Prisma, Sequelize, Hibernate, Drizzle, Apache Superset, Metabase, Grafana

See Client Compatibility Guide for detailed testing results and ORM setup examples.

🎯 Key Features

• pgvector Syntax: Use familiar <=> and <#> operators - auto-translated to IRIS VECTOR_COSINE/DOT_PRODUCT. HNSW indexes provide 5× speedup on 100K+ vectors. See Vector Operations Guide

• ORM & DDL Compatibility: Automatic public ↔ SQLUser schema mapping and PostgreSQL DDL transformations (stripping fillfactor, GENERATED columns, USING btree, etc.) for seamless migrations. See DDL Compatibility Guide

• Enterprise Security: SCRAM-SHA-256, OAuth 2.0, IRIS Wallet authentication. Industry-standard security matching PgBouncer, YugabyteDB. See Deployment Guide

• Performance: ~4ms protocol overhead, dual backend (DBAPI/Embedded), async SQLAlchemy support. See Performance Benchmarks

🎉 Release 1.3.0 / IRIS 2024.2+ Compatibility

• Full IRIS 2024.2+ compatibility: Automatic %EXACT wrapping for SELECT DISTINCT and UNION ensures parity with PostgreSQL set semantics.
• Enhanced RETURNING emulation: Multi-column and RETURNING * pipelines are handled with richer metadata, supplemental selects, and session-local lookups.
• ON CONFLICT support: DO NOTHING and DO UPDATE branches map to IRIS logic while preserving consistent RETURNING output.
• Metadata-driven DEFAULTs: The translator now resolves DEFAULT references via IRIS metadata so that INSERT/UPDATE statements stay intact.
• Global boolean translation: PostgreSQL true/false literals translate to their IRIS equivalents automatically across all SQL paths.
• Session pinning for DBAPI: Connections stay bound to the original session to maintain identity lookups (LAST_IDENTITY(), %EXACT, etc.) during emulation.

💻 Usage Examples

Command-Line (psql)

# Connect to IRIS via PostgreSQL protocol
psql -h localhost -p 5432 -U _SYSTEM -d USER
Simple queries
SELECT * FROM MyTable LIMIT 10;
Vector similarity search
SELECT id, VECTOR_COSINE(embedding, TO_VECTOR('[0.1,0.2,0.3]', DOUBLE)) AS score
FROM vectors
ORDER BY score DESC
LIMIT 5;

Python (psycopg3)

import psycopg
with psycopg.connect('host=localhost port=5432 dbname=USER user=_SYSTEM password=SYS') as conn:
# Simple query
with conn.cursor() as cur:
cur.execute('SELECT COUNT(*) FROM MyTable')
count = cur.fetchone()[0]
print(f'Total rows: {count}')
# Parameterized query
with conn.cursor() as cur:
    cur.execute('SELECT * FROM MyTable WHERE id = %s', (42,))
    row = cur.fetchone()

# Vector search with parameter binding
query_vector = [0.1, 0.2, 0.3]  # Works with any embedding model
with conn.cursor() as cur:
    cur.execute("""
        SELECT id, VECTOR_COSINE(embedding, TO_VECTOR(%s, DOUBLE)) AS score
        FROM vectors
        ORDER BY score DESC
        LIMIT 5
    """, (query_vector,))
    results = cur.fetchall()

Async SQLAlchemy with FastAPI

from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession, async_sessionmaker
from sqlalchemy import text
from fastapi import FastAPI, Depends
Setup
engine = create_async_engine("postgresql+psycopg://localhost:5432/USER")
SessionLocal = async_sessionmaker(engine, class_=AsyncSession, expire_on_commit=False)
app = FastAPI()
async def get_db():
async with SessionLocal() as session:
yield session
FastAPI endpoint with async IRIS query
@app.get("/users/{user_id}")
async def get_user(user_id: int, db: AsyncSession = Depends(get_db)):
result = await db.execute(
text("SELECT * FROM users WHERE id = :id"),
{"id": user_id}
)
return result.fetchone()

📚 Documentation Index

📖 Complete Documentation → - Full navigation hub with all guides, architecture docs, and troubleshooting

Getting Started

• Installation Guide - Docker, PyPI, ZPM, Embedded Python deployment
• Quick Start Examples - First queries with psql, Python, FastAPI
• BI Tools Setup - Superset, Metabase, Grafana integration

Features & Capabilities

• Features Overview - pgvector, ORM compatibility, DDL transformations, authentication
• DDL Compatibility - Automatic handling of PostgreSQL-specific DDL (fillfactor, generated columns, enums)
• pg_catalog Support - 6 catalog tables + 5 functions for ORM introspection
• Vector Operations - High-dimensional vectors, parameter binding
• Client Compatibility - 171 tests across 8 languages

Architecture & Performance

• Architecture Overview - System design, dual backend, components
• Performance Benchmarks - ~4ms overhead, HNSW indexes
• Deployment Guide - Production setup, authentication, SSL/TLS

Development & Reference

• Roadmap & Limitations - Current status, future enhancements
• Developer Guide - Development setup, contribution guidelines
• Testing Guide - Test framework, validation

⚡ Production Ready

171/171 tests passing - Verified compatibility with Python, Node.js, Java, .NET, Go, Ruby, Rust, PHP PostgreSQL clients

What Works: Core protocol (queries, transactions, COPY), Enterprise auth (SCRAM-SHA-256, OAuth 2.0), pgvector operators, ORM introspection

Architecture: SSL/TLS via reverse proxy (nginx/HAProxy), OAuth 2.0 instead of Kerberos - industry patterns matching PgBouncer, YugabyteDB

See Roadmap & Limitations for details

🤝 Contributing

# Clone repository
git clone https://github.com/intersystems-community/iris-pgwire.git
cd iris-pgwire
Install development dependencies
uv sync --frozen
Create persistent IRIS test container
./scripts/create_persistent_container.sh
Run tests (automatically starts PGWire server via fixtures)
pytest tests/
Run tests
pytest -v

Code Quality: black (formatter), ruff (linter), pytest (testing)

🔗 Links

• Repository: https://github.com/intersystems-community/iris-pgwire
• IRIS Documentation: https://docs.intersystems.com/iris/
• PostgreSQL Protocol: https://www.postgresql.org/docs/current/protocol.html
• pgvector: https://github.com/pgvector/pgvector

📄 License

MIT License - See LICENSE for details

Questions? Open an issue on GitHub