pytest-postgresql

8.0.0 · active · verified Thu Apr 09

pytest-postgresql is a pytest plugin that enables you to test code relying on a running PostgreSQL database. It provides fixtures for managing both the PostgreSQL process and client connections, automatically cleaning up temporary databases after tests. The current version is 8.0.0, and it is actively maintained.

Warnings

breaking Version 8.0.0 has dropped support for Python 3.9 (which reached End-of-Life) and now only supports PostgreSQL versions 14 and up.
Fix: Ensure your test environment uses Python >= 3.10 and PostgreSQL >= 14.
breaking The minimum supported `pytest` version has been bumped to 8.2. Older `pytest` versions might exhibit flaky behavior with fixture chaining on Python 3.12-3.13 when used with `xdist`.
Fix: Upgrade `pytest` to version 8.2 or newer: `pip install -U pytest`.
breaking `psycopg` (version 3) is now a mandatory requirement. Previously, `psycopg2` was optional.
Fix: Install `psycopg` alongside `pytest-postgresql`: `pip install pytest-postgresql psycopg`.
gotcha Using `COMMIT` statements within tests relying on the `transacted_postgresql_db` fixture can lead to non-deterministic bugs. This fixture assumes tests run within a single transaction and will reset the database, but explicit commits break this isolation.
Fix: Avoid `COMMIT` in `transacted_postgresql_db` tests. If `COMMIT` is strictly necessary, use the `postgresql_db` fixture instead, which provides full isolation by dropping and recreating the database after each test, though this is slower.

Install

pip install pytest-postgresql psycopg Install pytest-postgresql and its mandatory psycopg dependency

Imports

postgresql
```
def test_something(postgresql):
```
The primary fixture providing a psycopg.Connection object.
postgresql_proc
```
def test_something_with_proc(postgresql_proc, postgresql):
```
A session-scoped fixture managing the PostgreSQL server lifecycle.
factories
```
from pytest_postgresql import factories
```
Used for creating custom PostgreSQL client and process fixtures.

Quickstart

Demonstrates a basic test using the `postgresql` fixture to interact with a temporary PostgreSQL database. The fixture provides a `psycopg.Connection` object, automatically handling database setup and teardown for isolation.

import pytest
from psycopg import Connection

def test_example(postgresql: Connection):
    """Check main postgresql fixture."""
    with postgresql.cursor() as cur:
        cur.execute("CREATE TABLE test (id serial PRIMARY KEY, num integer, data varchar);")
    postgresql.commit()

    # Optional: Verify data insertion
    with postgresql.cursor() as cur:
        cur.execute("INSERT INTO test (num, data) VALUES (1, 'test_data');")
    postgresql.commit()

    with postgresql.cursor() as cur:
        cur.execute("SELECT num, data FROM test WHERE id = 1;")
        result = cur.fetchone()
        assert result == (1, 'test_data')

view raw JSON →