iris-pgwire

License: MIT Python 3.11+ Coverage: 92%

PostgreSQL wire protocol server for InterSystems IRIS. Connects BI tools, Python frameworks, data pipelines, and any PostgreSQL-compatible client to IRIS databases — no IRIS-specific drivers needed.

Connection string: postgresql://user:pass@localhost:5432/USER

Quick Start

Docker (recommended)

git clone https://github.com/intersystems-community/iris-pgwire.git
cd iris-pgwire
docker compose up -d

# Test it (PGWire runs on port 5432 inside the IRIS container)
psql -h localhost -p 5432 -U _SYSTEM -d USER -c "SELECT 'Hello from IRIS!'"

Python package

pip install iris-pgwire psycopg[binary]

export IRIS_HOST=localhost IRIS_PORT=1972 \
       IRIS_USERNAME=_SYSTEM IRIS_PASSWORD=SYS IRIS_NAMESPACE=USER

python -m iris_pgwire.server

First query

import psycopg

with psycopg.connect("host=localhost port=5432 dbname=USER user=_SYSTEM password=SYS") as conn:
    with conn.cursor() as cur:
        cur.execute("SELECT COUNT(*) FROM MyTable")
        print(cur.fetchone()[0])

Verified Client Compatibility

Tested against real IRIS instances via the wire protocol:

Language Clients
Python psycopg3, asyncpg, SQLAlchemy (sync + async)
Node.js pg (node-postgres)
Java PostgreSQL JDBC
.NET Npgsql
Go pgx v5
Ruby pg gem
Rust tokio-postgres
PHP PDO PostgreSQL

ORMs: SQLAlchemy, Prisma, Drizzle, Sequelize, Hibernate
BI tools: Apache Superset, Metabase, Grafana (standard PostgreSQL driver)

See Client Compatibility Guide for setup examples.

Key Features

pgvector operators — <=> (cosine), <#> (dot product), <-> (L2) auto-translate to IRIS VECTOR_COSINE/VECTOR_DOT_PRODUCT. HNSW indexes give 5× speedup at 100K+ vectors. See Vector Operations.

DDL compatibility — Automatic public ↔ SQLUser schema mapping; strips fillfactor, GENERATED columns, USING btree, IF NOT EXISTS guards, and other PostgreSQL-specific DDL so ORM migrations run cleanly. See DDL Compatibility.

SQL translation — RETURNING emulation, ON CONFLICT, boolean literals, pg_catalog → INFORMATION_SCHEMA rewrites, JSON operators (-> / ->> → JSON_EXTRACT), parameterized queries.

Authentication — SCRAM-SHA-256, OAuth 2.0 (RFC 6749), IRIS Wallet credentials.

Dual backend — Embedded Python (irispython, lowest latency) or external DBAPI (standard TCP connection). Selectable via IRIS_BACKEND env var.

COPY protocol — Bulk load via COPY … FROM STDIN (~600 rows/sec on DBAPI path).

Usage Examples

Parameterized queries (psycopg3)

import psycopg

with psycopg.connect("host=localhost port=5432 dbname=USER user=_SYSTEM password=SYS") as conn:
    with conn.cursor() as cur:
        cur.execute("SELECT * FROM MyTable WHERE id = %s", (42,))
        row = cur.fetchone()

Vector similarity search

query_vector = [0.1, 0.2, 0.3]
with conn.cursor() as cur:
    cur.execute("""
        SELECT id, embedding <=> %s::vector AS score
        FROM vectors
        ORDER BY score
        LIMIT 5
    """, (query_vector,))
    results = cur.fetchall()

Async SQLAlchemy

from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession, async_sessionmaker
from sqlalchemy import text

engine = create_async_engine("postgresql+psycopg://localhost:5432/USER")
SessionLocal = async_sessionmaker(engine, class_=AsyncSession)

async def query():
    async with SessionLocal() as session:
        result = await session.execute(text("SELECT * FROM MyTable"))
        return result.fetchall()

COPY bulk load

with conn.cursor() as cur:
    with cur.copy("COPY MyTable (col1, col2) FROM STDIN") as copy:
        for row in data:
            copy.write_row(row)

Documentation

Guide Description
Installation Docker, PyPI, Embedded Python deployment
Architecture System design, dual backend, request flow
DDL Compatibility PostgreSQL DDL transformations
Vector Operations pgvector syntax, HNSW indexes
Client Compatibility Per-language setup and caveats
Deployment Production setup, SSL/TLS, auth
Performance Benchmarks, tuning
Developer Guide Development setup, contribution guidelines

Development

# Install dependencies
uv sync --frozen

# Run unit + contract tests (no IRIS needed)
pytest tests/unit/ tests/contract/ -v

# Run with live IRIS (container must be up)
docker compose up -d
PGWIRE_BACKEND_TYPE=dbapi PGWIRE_POOL_SIZE=1 pytest tests/ -v

# Code quality check
python -m iris_pgwire.quality

Test coverage: 92% (5349 tests)
Code quality: black (formatter), ruff (linter), bandit (security)

Known Limitations

See KNOWN_LIMITATIONS.md for the full list. Key items:

  • IRIS Community Edition: 5-user connection limit — use PGWIRE_POOL_SIZE=1 for dev
  • No native SSL termination — use nginx/HAProxy in front for TLS
  • Kerberos/GSSAPI auth wiring deferred (OAuth 2.0 is the recommended enterprise auth)
  • INFORMATION_SCHEMA only, no pg_catalog tables (translated automatically)

Contributing

git clone https://github.com/intersystems-community/iris-pgwire.git
cd iris-pgwire
uv sync --frozen
docker compose up -d
pytest tests/unit/ tests/contract/ -v

Open an issue or PR on GitHub.

Links

MIT License — see LICENSE

Project links

Key dates

PyPI data
Data sourced directly from PyPI's database.
  • Released:
Latest release

1 maintainer

PyPI data
Data sourced directly from PyPI's database.
Avatar for tomdyar from gravatar.com tomdyar

Credits

Author/Maintainer: Thomas Dyar

License

MIT License (MIT License)

Requires

Python >=3.11

Provides Extra

ai dev iris kerberos test

Tags

async bi-tools database fastapi intersystems iris llm pgvector postgres postgresql rag sql sqlalchemy vector-database wire-protocol

Classifiers

Report project as malware

Download files

Download the file for your platform. If you're not sure which to choose, learn more about installing packages.

Source Distribution

iris_pgwire-1.5.9.tar.gz (344.1 kB view details)

Uploaded Source

Built Distribution

If you're not sure about the file name format, learn more about wheel file names.

iris_pgwire-1.5.9-py3-none-any.whl (405.5 kB view details)

Uploaded Python 3

File details

Details for the file iris_pgwire-1.5.9.tar.gz.

File metadata

  • Download URL: iris_pgwire-1.5.9.tar.gz
  • Upload date:
  • Size: 344.1 kB
  • Tags: Source
  • Uploaded using Trusted Publishing? No
  • Uploaded via: twine/6.2.0 CPython/3.13.12

File hashes

Hashes for iris_pgwire-1.5.9.tar.gz
Algorithm Hash digest
SHA256 2ae44ba516be24954fc01aa4b7404e3e133c9ce036634eac320efd1ff059b7d4
MD5 52559cb32acdee06f3e2fe500c5d9682
BLAKE2b-256 7bfe22e525143d03ee79372cf43a7d09a3f4f2bac03678525e29c2968161d740

See more details on using hashes here.

File details

Details for the file iris_pgwire-1.5.9-py3-none-any.whl.

File metadata

  • Download URL: iris_pgwire-1.5.9-py3-none-any.whl
  • Upload date:
  • Size: 405.5 kB
  • Tags: Python 3
  • Uploaded using Trusted Publishing? No
  • Uploaded via: twine/6.2.0 CPython/3.13.12

File hashes

Hashes for iris_pgwire-1.5.9-py3-none-any.whl
Algorithm Hash digest
SHA256 70b6ddd7d603deb59106cf661f8bfa7c7cc80083ac81ba8aa3050bf5c9ea7568
MD5 801ffa180be89331acf2d0e71501e354
BLAKE2b-256 d3bc2034aadc1acaf181de901b52053025268fa310c40f350fd4a16cc34a73fc

See more details on using hashes here.

Release history Release notifications | RSS feed

This release

1.5.9 This release

2 files

1.5.8

2 files

1.5.7

2 files

1.5.6

2 files

1.5.5

2 files

1.5.4

2 files

1.5.3

2 files

1.5.2

2 files

1.5.1

2 files

1.5.0

2 files

1.4.2

2 files

1.4.1

2 files

1.3.1

2 files

1.3.0

2 files

1.2.34

2 files

1.2.33

2 files

1.2.32

2 files

1.2.31

2 files

1.2.30

2 files

1.2.29

2 files

1.2.28

2 files

1.2.26

2 files

1.2.25

2 files

1.2.24

2 files

1.2.23

2 files

1.2.22

2 files

1.2.21

2 files

1.2.20

2 files

1.2.19

2 files

1.2.18

2 files

1.2.17

2 files

1.2.16

2 files

1.2.15

2 files

1.2.14

2 files

1.2.13

2 files

1.2.12

2 files

1.2.11

2 files

1.2.10

2 files

1.2.9

2 files

1.2.8

2 files

1.2.7

2 files

1.2.6

2 files

1.2.5

2 files

1.2.4

2 files

1.2.3

2 files

1.2.2

2 files

1.2.1

2 files

1.2.0

2 files

1.1.9

2 files

1.1.8

2 files

1.1.7

2 files

1.1.6

2 files

1.1.5

2 files

1.1.4

2 files

1.1.3

2 files

1.1.2

2 files

1.1.1

2 files

1.1.0

2 files

1.0.9

2 files

1.0.8

2 files

1.0.7

2 files

1.0.6

2 files

1.0.5

2 files

1.0.4

2 files

1.0.3

2 files

1.0.2

2 files

1.0.1

2 files

1.0.0

2 files

Supported by

AWS Cloud computing and Security Sponsor Datadog Monitoring Depot Continuous Integration Fastly CDN Google Download Analytics Sentry Error logging StatusPage Status page