{"id":2805,"library":"testing-postgresql","title":"Testing PostgreSQL","description":"testing.postgresql is a Python library that automatically sets up a PostgreSQL instance in a temporary directory for testing purposes and destroys it afterward. The current version is 1.3.0. Its release cadence appears to be slow, with the last major update in 2016.","status":"maintenance","version":"1.3.0","language":"en","source_language":"en","source_url":"https://github.com/tk0miya/testing.postgresql","tags":["testing","postgresql","database","integration-testing","temporary-database"],"install":[{"cmd":"pip install testing.postgresql","lang":"bash","label":"Install stable version"}],"dependencies":[{"reason":"Requires the PostgreSQL server binaries (e.g., `initdb`, `postgres`) to be present in the system's PATH. This is an external, non-Python dependency.","package":"PostgreSQL server","optional":false},{"reason":"Used internally for database connection to create the test database (since v1.2.0).","package":"pg8000","optional":true},{"reason":"A dependency for utility methods (since v1.3.0).","package":"testing.common.database","optional":false}],"imports":[{"symbol":"Postgresql","correct":"from testing.postgresql import Postgresql"},{"note":"Used for creating cached PostgreSQL instances to speed up tests.","symbol":"PostgresqlFactory","correct":"from testing.postgresql import PostgresqlFactory"}],"quickstart":{"code":"import testing.postgresql\nfrom sqlalchemy import create_engine\nimport psycopg2\n\n# Basic usage with a context manager\nwith testing.postgresql.Postgresql() as postgresql:\n    # Connect using SQLAlchemy\n    engine = create_engine(postgresql.url())\n    with engine.connect() as conn:\n        result = conn.execute(postgresql.text(\"SELECT 1\")).scalar()\n        print(f\"SQLAlchemy: Connected to {postgresql.url()}, result: {result}\")\n\n    # Connect using psycopg2 (requires psycopg2 to be installed)\n    try:\n        conn_psycopg = psycopg2.connect(**postgresql.dsn())\n        cursor = conn_psycopg.cursor()\n        cursor.execute(\"CREATE TABLE my_table (id SERIAL PRIMARY KEY, name VARCHAR(255))\")\n        cursor.execute(\"INSERT INTO my_table (name) VALUES (%s)\", ('test_name',))\n        cursor.execute(\"SELECT name FROM my_table WHERE id = 1\")\n        name_result = cursor.fetchone()[0]\n        print(f\"psycopg2: Inserted and retrieved: {name_result}\")\n        cursor.close()\n        conn_psycopg.close()\n    except Exception as e:\n        print(f\"psycopg2 connection/query failed: {e}\")\n\n# Example of using PostgresqlFactory for faster tests (e.g., in a test suite setup)\ndef custom_init_handler(pg_instance):\n    \"\"\"An optional handler to run SQL on a newly initialized DB.\"\"\"\n    conn = psycopg2.connect(**pg_instance.dsn())\n    cursor = conn.cursor()\n    cursor.execute(\"CREATE TABLE users (id SERIAL PRIMARY KEY, username VARCHAR(255))\")\n    conn.commit()\n    conn.close()\n    print(\"Factory DB initialized with 'users' table.\")\n\nPostgresqlFactory = testing.postgresql.PostgresqlFactory(\n    cache_initialized_db=True, \n    on_initialized=custom_init_handler\n)\n\nwith PostgresqlFactory() as postgresql_cached:\n    conn = psycopg2.connect(**postgresql_cached.dsn())\n    cursor = conn.cursor()\n    cursor.execute(\"INSERT INTO users (username) VALUES (%s)\", ('cached_user',))\n    conn.commit()\n    cursor.execute(\"SELECT username FROM users WHERE id = 1\")\n    user_result = cursor.fetchone()[0]\n    print(f\"Factory usage: Inserted and retrieved: {user_result}\")\n    conn.close()\n","lang":"python","description":"This quickstart demonstrates launching a temporary PostgreSQL instance using `testing.postgresql.Postgresql` within a context manager. It shows how to connect to the instance using both SQLAlchemy and psycopg2, execute basic queries, and ensures the PostgreSQL server is properly terminated and cleaned up. It also includes an example of `PostgresqlFactory` which caches the initialized database to speed up repeated test runs, optionally with a custom initialization handler."},"warnings":[{"fix":"Ensure that your system has PostgreSQL installed and that its binary directory (e.g., `/usr/bin/`, `/usr/local/bin/`) is included in your PATH environment variable.","message":"This library requires a pre-installed PostgreSQL server in your system's PATH environment variable. It uses binaries like `initdb` and `postgres` directly. Without these, the library cannot function.","severity":"breaking","affected_versions":"All versions"},{"fix":"Evaluate alternatives like `pytest-postgresql` for more actively developed solutions, especially if you require modern features, community support, or encounter compatibility issues with newer Python or PostgreSQL versions.","message":"The `testing-postgresql` project appears to be minimally maintained, with the last PyPI release in 2016 and limited GitHub activity since 2017. Users seeking active development, support for newer PostgreSQL features, or compatibility with recent Python versions might find it lacking.","severity":"gotcha","affected_versions":"1.x"},{"fix":"For faster repeated tests, use `testing.postgresql.PostgresqlFactory` with `cache_initialized_db=True`. This initializes the database only once and copies it for subsequent instances. You can also use the `copy_data_from` option to provide a pre-existing data directory.","message":"By default, `testing.postgresql.Postgresql` invokes `initdb` on every instantiation. This can be a slow operation, especially when creating many test databases for individual test cases, leading to increased test suite execution time.","severity":"gotcha","affected_versions":"All versions"},{"fix":"Always use `testing.postgresql.Postgresql()` within a `with` statement to ensure proper cleanup. If manual management is necessary (e.g., in `setUp`/`tearDown` methods of `unittest.TestCase`), ensure `postgresql_instance.stop()` is called in `tearDown`.","message":"Failure to explicitly call `.stop()` on a `Postgresql` instance or neglecting to use it within a `with` statement (context manager) can leave lingering PostgreSQL processes and temporary data directories, consuming system resources and potentially causing conflicts.","severity":"gotcha","affected_versions":"All versions"}],"env_vars":null,"last_verified":"2026-04-10T00:00:00.000Z","next_check":"2026-07-09T00:00:00.000Z"}