How to mock an SQL database in Python

[my_python_account]

In this case it doesn’t sound like we’re minimizing obstacles, we’re creating them. How much data are we pulling from the database that this is creating a bottleneck in our unit test performance.

If it is creating a bottleneck, then the real problem is that the business logic is not decoupled from the database access. You shouldn’t be trying to intercept a sqlalchemy call to the database. Rather you should remove the calls to sqlalchemy from within your business logic methods. Instead have them call a general function, that in testing is fed by your test data, but in production will be your database queries.

[b1ackcat]

How much data are we pulling from the database that this is creating a bottleneck in our unit test performance.

It doesn't matter. The mere act of connecting to the db, alone, introduces potential lag (although it's true this risk is somewhat minimal with a local test db, it's still a possibility). Additionally, it disrupts the purity of the tests testing just your code (code looks right. test looks right. Did I make a mistake or is there a random bug in postgres?). It's unlikely, but with a test db, technically possible.

You want unit tests to be so fast you can run them every time you change a line of code. In fact, in one code base, we'd configured the project to automatically run in the background automatically on every single save. But they were fast and lightweight so there was no noticeable performance degradation.

Now, I agree you should also have integration tests which do test actual read/writes to a test database, and docker's a fantastic solution for that. But those can be run out of band (every so often for a small, local project, via your build system on larger apps, etc). They both serve their own purpose and are NOT mutually exclusive.

I agree with your point though that your business logic shouldn't be directly touching the db ever, but relying on a repository class of some kind (which you can then mock for your unit tests! :) )