Rolling back database transactions in SQLAlchemy tests with PostgreSQL

Question

I am building a Pyramid web application which is built on the top of SQLAlchemy and solely relies PostgreSQL as its database backend.

What would be a way to have the unit tests structure so that

To speed up tests, database transactions are rolled back at the teardown(), or other clean up hook of the test suite
Other tricks to speed up tests could be used, e.g. if SQLAlchemy and PostgreSQL has anything corresponding SQLite's :in:memory: database
It is possible to choose a custom test runner á la py.test if a specific features outside the standard library unittest framework makes it easier to write test cases.

Related: http://stackoverflow.com/questions/29507792/creating-databases-in-sqlalchemy-tests-with-postgresql — Mikko Ohtamaa, Apr 08 '15 at 07:00
A good trick is to create a "template" database with all the tables and data you need for your test, and do a create database newdb template testtemplate; and use the testtemplate db and when you're done with it just drop it. — Scott Marlowe, Apr 10 '15 at 01:53

score 2 · Accepted Answer · edited May 23 '17 at 12:22

Since this question is a bit similar to your other question, I'll copy the relevant part of my answer here:

All test case classes should be subclassed from a base class, which defines a common tearDown method:

class BaseTest(unittest.TestCase):

    def setUp(self):
        # This makes things nicer if the previous test fails
        # - without this all subsequent tests fail
        self.tearDown()

        self.config = testing.setUp()

    def tearDown(self):
        testing.tearDown()
        session.expunge_all()
        session.rollback()

score 1 · Answer 2 · answered Apr 08 '15 at 19:42

For tests you can initialize PostgreSQL data directory on a RAMdisk (you can create a directory under /dev/shm/, which is mounted as tmpfs in modern Linux distributions). The you can run Postgres on non-standard port.

PGTEMP=`mktemp -d /dev/shm/pgtemp.XXXXXX`
initdb -D "$PGTEMP"
postgres -D "$PGTEMP" -k "$PGTEMP" -p 54321

Rolling back database transactions in SQLAlchemy tests with PostgreSQL

2 Answers2

Linked