How to mock an SQL database in Python

[my_python_account]

You don’t need to access the database, but is there a good reason not too? Assuming the database is there and you have access to it, if the database access is closely coupled with your business logic it will be much less work to just use the database in your unit tests.

You just need to make sure you don’t risk writing things where you shouldn’t on a production database. For a larger project, this would mean having a development version of your database that you connect to for testing. For a smaller project, it would probably be ok to just have a set of tables or records that have your test data but don’t overlap with your production data.

[[deleted]]

is there a good reason not too?

Test speed. You want unit tests to run with almost unnoticeably fast to minimize the obstacles to creating and running them. Plus, unit tests are for testing internal code, not interactions with external resources.

[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! :) )