How to Mock PostgreSQL with Pytest and pytest-postgresql

Is Mocking PostgreSQL a Good Idea?

PostgreSQL License and copyright notice at bottom of article.

Minimum Working Example

docker run -e POSTGRES_USER=postgresUser -e POSTGRES_PASSWORD=postgresPW -e POSTGRES_DB=postgresDB -d --rm postgres
docker exec -it <container_id> bash
# Install dependencies.
apt update ; apt install -y python3 pip vim
pip3 install ipdb pytest-postgresql psycopg psycopg2-binary pandas SQLAlchemymkdir /app
cd /app
import pandas as pddef get_drivers(db_url):
"""Return rows from the driver table."""
sql = "SELECT * FROM car_schedule.drivers"
results = pd.read_sql_query(sql=sql, con=db_url)
if len(results) > 1:
# Do logic here.
return results
CREATE SCHEMA car_schedule;CREATE TABLE car_schedule.drivers (
license_number VARCHAR(100) NOT NULL
INSERT INTO car_schedule.drivers (name, license_number)
('Jeff', 'abc123'),
('Dan', 'def456');
import pandas as pd
import pytest
from app import get_drivers@pytest.fixture()
def database(postgresql):
"""Set up the mock DB with the SQL flat file."""
with open("test.sql") as f:
setup_sql =
with postgresql.cursor() as cursor:
yield postgresqldef test_example_postgres(database):
drivers = get_drivers(db_url=database)
assert len(drivers) == 2
assert set(drivers["name"]) == {"Dan", "Jeff"}
su postgres -c pytest
root@f45ca72980ea:/app# su postgres -c pytest
============ test session starts ============
platform linux -- Python 3.9.2, pytest-7.1.2, pluggy-1.0.0
rootdir: /app
plugins: postgresql-4.1.1
collected 1 item . [100%]============ 1 passed in 1.87s ============

What Did We Just Do?

  1. define the database/table schema in test.sql
  2. in the test file, spin up the Pytest fixture database
  3. test the application function using database as the connection parameter

An Alternate Approach

def query_db():
res = pd.read_sql_query(sql="SELECT * FROM drivers", con=DB_URL)
return res

PostgreSQL License (to repost image)




Ryan Duve is a Data Scientist at Corvus Insurance. He's also a physicist, Linux hacker and a bad barista.

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Reflections on Women Who Code Seoul X FOSSASIA Summit 2022

Women Who Code X FOSSAISA Summit 2022 Poster

Why Coding? How’d I Get Here?

OCaml Continuation Explained

Making OSX Work with a Cheap Mechanical Keyboard

Transactions and concurrency control simplified

Algorithms — Quick Sort

Practical Tips on Data Load to Synapse using Polybase

Copy Command vs Polybase Comparison

Use FATE Client to Build Jobs in Jupyter Notebook

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Ryan Duve

Ryan Duve

Ryan Duve is a Data Scientist at Corvus Insurance. He's also a physicist, Linux hacker and a bad barista.

More from Medium

Python on AWS Lambda using Docker images

FastAPI, Docker and Postgres

LeetCode 238. Product of Array Except Self — Python Solution

Python Data Model

scissors beats paper, paper beats rock and rock beats scissors