postgresql - testing postgres db python

Question

I don't understand how to test my repositories.

I want to be sure that I really saved object with all of it parameters into database, and when I execute my SQL statement I really received what I am supposed to.

But, I cannot put "CREATE TABLE test_table" in setUp method of unittest case because it will be created multiple times (tests of the same testcase are runned in parallel). So, as long as I create 2 methods in the same class which needs to work on the same table, it won't work (name clash of tables)

Same, I cannot put "CREATE TABLE test_table" setUpModule, because, now the table is created once, but since tests are runned in parallel, there is nothing which prevents from inserting the same object multiple times into my table, which breakes the unicity constraint of some field.

Same, I cannot "CREATE SCHEMA some_random_schema_name" in every method, because I need to globally "SET search_path TO ..." for a given Database, so every method runned in parallel will be affected.

The only way I see is to create to "CREATE DATABASE" for each test, and with unique name, and establish a invidual connection to each database.. This looks extreeeemly wasteful. Is there a better way?

Also, I cannot use SQLite in memory because I need to test PostgreSQL.

Answer 1

The best solution for this is to use the testing.postgresql module. This fires up a db in user-space, then deletes it again at the end of the run. You can put the following in a unittest suite - either in setUp, setUpClass or setUpModule - depending on what persistence you want:

import testing.postgresql

def setUp(self):
    self.postgresql = testing.postgresql.Postgresql(port=7654)
    # Get the url to connect to with psycopg2 or equivalent
    print(self.postgresql.url())

def tearDown(self):
    self.postgresql.stop()

If you want the database to persist between/after tests, you can run it with the base_dir option to set a directory - which will prevent it's removal after shutdown:

name = "testdb"
port = "5678"
path = "/tmp/my_test_db"
testing.postgresql.Postgresql(name=name, port=port, base_dir=path)

Outside of testing it can also be used as a context manager, where it will automatically clean up and shut down when the with block is exited:

with testing.postgresql.Postgresql(port=7654) as psql:
    # do something here