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
docker-compose up -d
Test it works
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 Compatibility: Automatic public ↔ SQLUser schema mapping for Prisma, SQLAlchemy, Drizzle, Hibernate introspection. Catalog emulation with 6 pg_catalog tables + 5 functions. See pg_catalog Documentation

• 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

💻 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, authentication
• 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
Start development environment
docker-compose up -d
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