pytest-pgsql

What it is and what it does

pytest-pgsql is a pytest plugin that automates database fixture management for tests that need PostgreSQL. It provides two main fixtures—postgresql_db and transacted_postgresql_db—each exposing a SQLAlchemy session and either an engine or connection object. The plugin automatically creates a fresh temporary database for each test and cleans it up afterward, ensuring test isolation without manual teardown.

The package integrates freezegun for time manipulation within tests, allowing you to freeze and advance time in both Python and the database. It also provides utility methods for common test setup tasks: loading CSV files into tables, executing SQL scripts with variable binding, managing PostgreSQL extensions, and creating schemas and tables from SQLAlchemy definitions. Both fixtures rely on testing.postgresql to handle temporary instance provisioning.

Use it for:

• Unit testing ORM code that requires a real database without manual setup or teardown between tests.
• Testing time-dependent database logic by freezing and advancing time within test blocks.
• Loading test data from CSV files or SQL scripts into a clean database for each test run.
• Testing PostgreSQL extension availability and installation within test fixtures.
• Verifying schema and table creation logic in database migration or initialization code.

Worth the install?

AI-flagged interpretation of the facts on this page — verify before relying

A pytest plugin that provides fixtures for running tests against temporary, auto-cleaned PostgreSQL databases, with support for time freezing and common database operations.

Yes, with conditions. The package is stable and production-ready for projects that need isolated PostgreSQL test databases. However, adopt it only if you can accept that maintenance is minimal—the last release was 2020-05-13, and there is no active development roadmap. Verify compatibility with your PostgreSQL version and Python version before committing, and monitor the repository for any breaking changes in dependencies like testing.postgresql.

Install

pytest-pgsql on PyPI

pip install pytest-pgsql

uv add pytest-pgsql

poetry add pytest-pgsql

Installing pytest-pgsql

Before you install

Low install friction with a pure-Python wheel. However, the package is aging—last release was 2020-05-13 and the last commit 2025-08-20, suggesting minimal active maintenance despite the repository not being archived.

License in practice

Licensed under a permissive BSD license (Copyright Clover Health, Inc.), which imposes no significant restrictions on use or redistribution in most contexts.

Quickstart

pip install pytest-pgsql

import pytest
from pytest_pgsql import postgresql_db

def test_example(postgresql_db):
    postgresql_db.session.execute('CREATE TABLE test (id INT)')
    postgresql_db.session.commit()

Requires a PostgreSQL server accessible to the test environment; testing.postgresql (a runtime dependency) handles temporary instance setup but still needs PostgreSQL binaries available on the system.

Verify before relying

• Whether the package works reliably with modern PostgreSQL versions beyond those released around 2020.
• Compatibility with Python versions beyond 3.6 (classifiers only list up to 3.6, but requires_python is unspecified).
• Current state of the testing.postgresql dependency and whether it remains actively maintained.

Package facts

Evidence: pytest_pgsql-1.1.2-py3-none-any.whl

Tags

More Testing packages