Database testing in python, postgresql

Question

How do you unit test your python DAL that is using postgresql.

In sqlite you could create in-memory database for every test but this cannot be done for postgresql.

I want a library that could be used to setup a database and clean it once the test is done.

I am using Sqlalchemy as my ORM.

Answer 1

pg_tmp(1) is a utility intended to make this task easy. Here is how you might start up a new connection with SQLAlchemy:

from subprocess import check_output
from sqlalchemy import create_engine

url = check_output(['pg_tmp', '-t'])
engine = create_engine(url)

This will spin up a new database that is automatically destroyed in 60 seconds. If a connection is open pg_tmp will wait until all active connections are closed.

Answer 2

Have you tried testing.postgresql?