Postgresql Testing

Simple Postgres helpers for testing with Python - no docker, brew, apt, etc - uses postgresql-binaries. The interface is simple, but close enough to the metal that you can use it to eg. have a new database per testing thread/use a fresh database from a TEMPLATE or archive.

pip install 'postgresql-testing[15]'

Then to use with pytest, eg:

import postgresql_testing

@pytest.fixture(scope="session")
def db() -> Iterator[str]:
    config = postgresql_testing.DatabaseConfig.default("testing-db")
    postgresql_testing.initdb(config.directory, on_existing="use")
    with postgresql_testing.serve(config):
        postgresql_testing.ensure_user(config)
        postgresql_testing.create_database(config, on_existing="replace")
        yield config.dsn

There are various useful flags and things - the source code is short enough to just dive in.

You can spin up a fresh db for immediate testing with:

postgresql-testing-serve postgres://testing:@localhost:8421/my-db

Bundled for good measure is a local-first copy of explain.dalibo.com

Get your query plan by:

EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON) SELECT ...

Then copy and:

pbpaste | postgresql-testing-explain

There are a couple of helpers for creating/using template dbs and archives. I have some vague long term plan for some kind of "Docker layers for migrated databases" with clever caching (or not), but I'm not quite sure what it looks like yet.

Some benchmarking:

On macos using a ramdisk, it is slightly quicker:

See claims/advice from planet Go.