Skip to main content

Pytest plugins and helpers for tests using a Postgres database.

Project description

Clean PostgreSQL Databases for Your Tests

The following is a summary of the complete pytest_pgsql docs, which are available on ReadTheDocs.

What is pytest_pgsql?

pytest_pgsql is a pytest plugin you can use to write unit tests that utilize a temporary PostgreSQL database that gets cleaned up automatically after every test runs, allowing each test to run on a completely clean database (with some limitations).

The plugin gives you two fixtures you can use in your tests: postgresql_db and transacted_postgresql_db. Both of these give you similar interfaces to access to the database, but have slightly different use cases (see below).

Sample Usage

You can use a session, connection, or engine - the choice is up to you. postgresql_db and transacted_postgresql_db both give you a session, but postgresql_db exposes its engine and transacted_postgresql_db exposes its connection:

def test_orm(postgresql_db):
    instance = Person(name='Foo Bar')
    with postgresql_db.engine.connect() as conn:

def test_connection(transacted_postgresql_db):
    instance = Person(name='Foo Bar')

    transacted_postgresql_db.connection.execute('DROP TABLE my_table')


The following is a non-exhaustive list of some of the features provided to you by the database fixtures.

Manipulating Time

Both database fixtures use freezegun to allow you to freeze time inside a block of code. You can use it in a variety of ways:

As a context manager:

with postgresql.time.freeze('December 31st 1999 11:59:59 PM') as freezer:
    # Time is frozen inside the database *and* Python.
    now = postgresql_db.session.execute('SELECT NOW()').scalar()
    assert ==, 12, 31)
    assert ==, 12, 31)

    # Advance time by 1 second so we roll over into the new year

    now = postgresql_db.session.execute('SELECT NOW()').scalar()
    assert ==, 1, 1)

As a decorator:

@pytest_pgsql.freeze_time(datetime.datetime(2038, 1, 19, 3, 14, 7))
def test_freezing(postgresql_db):
    today = postgresql_db.session.execute(
    assert today.year == 2038
    assert ==, 1, 19)

And more!

General-Purpose Functions

postgresql_db and transacted_postgresql_db provide some general-purpose functions to ease test setup and execution.

  • load_csv() loads a CSV file into an existing table.

  • run_sql_file() executes a SQL script, optionally performing variable binding.

Extension Management

Since version 9.1 Postgres supports extensions. You can check for the presence of and install extensions like so:

>>> postgresql_db.is_extension_available('asdf')  # Can I install this extension?
>>> postgresql_db.is_extension_available('uuid-ossp')  # Maybe this one is supported...
>>> postgresql_db.install_extension('uuid-ossp')
>>> postgresql_db.is_extension_installed('uuid-ossp')

install_extension() has additional arguments to allow control over which schema the extension is installed in, what to do if the extension is already installed, and so on. See the documentation for descriptions of these features.

Schemas and Tables

You can create table schemas by calling create_schema() like so:

postgresql_db.create_schema('foo')          # Create one schema
postgresql_db.create_schema('foo', 'bar')   # Create multiple ones

To quickly see if a table schema exists, call has_schema():

>>> postgresql_db.has_schema('public')

Similarly, you can create tables in the database with create_table(). You can pass SQLAlchemy Table instances or ORM declarative model classes:

# Just a regular Table.
my_table = Table('abc', MetaData(), Column('def', Integer()))

# A declarative model works too.
class MyORMModel(declarative_base()):
    id = Column(Integer, primary_key=True)

# Pass a variable amount of tables to create
postgresql_db.create_table(my_table, MyORMModel)


Sorry, this library is not compatible with Python 2. Please be sure to use pip3 instead of pip when installing:

pip3 install pytest-pgsql

Contributing Guide

For information on setting up pytest_pgsql for development and contributing changes, view CONTRIBUTING.rst.

Project details

Download files

Download the file for your platform. If you're not sure which to choose, learn more about installing packages.

Source Distribution

pytest-pgsql-1.1.2.tar.gz (41.2 kB view hashes)

Uploaded source

Built Distribution

pytest_pgsql-1.1.2-py3-none-any.whl (33.9 kB view hashes)

Uploaded py3

Supported by

AWS AWS Cloud computing and Security Sponsor Datadog Datadog Monitoring Fastly Fastly CDN Google Google Download Analytics Microsoft Microsoft PSF Sponsor Pingdom Pingdom Monitoring Sentry Sentry Error logging StatusPage StatusPage Status page