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.

Solution

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

Comments

[viru]

I dont have a PSQL server running on the app server - how do i connect to a PSQL server say using JDBC connection ?

[match]

This question is all about testing postgres with python, and my answer is about setting up a temporary psql server using python. Your issue is about connecting to postgres using JDBC, which isn't really related. I suggest starting a new question rather than adding a comment here.

[viru]

is there means of using testing.postgres to point to a non local server?

[match]

testing.postgres starts a local postgres server - you don't point it at anything - I'm not sure I understand the question?

[viru]

I dont have a local PostGres server running. Based on the docs - I believe testing.posgresql needs a local psql server running on the test enviroenment for it to run tests against. I wonder if there is a hack to point this to a non-local psql server installation. I use AWS RDS and dont have any local server running.

[match]

testing.postgres is a tool that starts a temporary postgres server. You need to have postgres installed, but NOT running, as testing.postgres will do this part.

[niid]

The library hasn't been updated in almost three years. Is it still the way to go?

[match]

I'm still using it daily in a lot of projects - haveb't looked through the issues on github though to see if it's quiet because it's 'complete' or because it's stalled.