Skip to content

Rationale

When interacting with PostgreSQL databases in Python, developers typically choose between using an Object-Relational Mapper (ORM) like SQLAlchemy or Django ORM, or working directly with a database driver like psycopg.

Object-Relational Mappers (ORMs)

ORMs offer significant advantages:

  • Abstraction: They hide the complexities of SQL, allowing developers to work with Python objects and methods.
  • Type Safety: Data interaction often benefits from static analysis and type checking.
  • Editor Convenience: Features like autocompletion and refactoring are greatly enhanced.
  • Reduced Boilerplate: Common database operations are simplified.

However, ORMs also come with drawbacks:

  • Learning Curve: Learning the ORMs specific syntax and behavior adds overhead and can result in having to resort hacky workarounds in some places.
  • Limited Flexibility: Complex or highly optimized SQL queries can be difficult or impossible to express through the ORMs abstraction.
  • Obscured SQL: It can be hard to predict or debug the exact SQL generated by the ORM.
  • Performance: The abstraction layer can introduce performance overhead, especially for complex queries.

Direct SQL (via psycopg) provides:

  • Full Control: Direct access to the full power and flexibility of SQL.
  • Universality: SQL is a standard language, widely understood and applicable across different database systems.
  • Performance: Ability to write highly optimized queries without abstraction layers.
  • Flexibility: PostgreSQL-specific features and optimizations can be fully utilized.

But this approach often involves:

  • Verbosity: Writing raw SQL and handling connections, cursors, and data fetching can be verbose and repetitive.
  • Lack of Type Safety: Mapping database results to Python types often requires manual effort and lacks build-time checks.
  • Limited Editor Support: Editor assistance for SQL strings within Python code is often minimal.

Python Postgres

Among many excellent choices already out there, Python Postgres offers another take on balancing the trade-offs outlined above.

It seeks to provide a developer experience that combines the type safety and editor convenience typically associated with ORMs, while retaining the power, flexibility, and universality of writing direct SQL.

It achieves this by:

  1. It's just SQL: You write standard PostgreSQL queries, ensuring full control and leveraging existing SQL knowledge.
  2. Pydantic: Results can be automatically parsed into Pydantic models, providing data validation, type hints, and enhanced editor support (like autocompletion for result attributes). You can also pass Pydantic models as query parameters.
  3. Reducing Boilerplate: Simplifying connection handling and common query patterns compared to using psycopg directly.

The goal is to offer a pragmatic middle ground: keep the SQL, but enhance the Python development experience around it, making database interactions more convenient, and less verbose without sacrificing the underlying power of SQL.