Missing Support for SQLAlchemy Dialect in PSQLPy

[dmastapkovich]

Problem

Currently, PSQLPy lacks a dedicated SQLAlchemy dialect, making it impossible to use PSQLPy as a backend with SQLAlchemy for seamless database interactions. This is a significant limitation for users who rely on SQLAlchemy's ORM and query-building capabilities alongside PSQLPy's performance and type safety benefits.

Expected Behavior

A new SQLAlchemy dialect should be implemented to allow PSQLPy to work as a backend for SQLAlchemy, supporting asynchronous connections and utilizing PSQLPy's unique features such as:

• Native support for prepared statements.
• Efficient handling of PostgreSQL data types like json, jsonb, and interval.
• Compatibility with create_async_engine and SQLAlchemy's connection abstractions.

Example Usage

from sqlalchemy.ext.asyncio import create_async_engine

engine = create_async_engine(
    "postgresql+psqlpy://user:password@host:port/database",
    pool_size=10,
    echo=True
)

References

• SQLAlchemy PostgreSQL Dialects
• Asyncpg Dialect Source

[chandr-andr]

@dmastapkovich Awesome idea!

If you can start researching it, it would be awesome!