If you want to run test queries against a Postgres db with pytest, I recommend using testing.postgresql (this requires having Postgres’ initdb in your PATH) and setting up fixtures in your conftest.py similar to:

import pytest
from typing import Iterator
import testing.postgresql

import stepping as st

def postgres_db() -> Iterator[str]:
    with testing.postgresql.Postgresql(port=8421) as postgresql:
        yield postgresql.url()

def postgres_conn(postgres_db: str) -> Iterator[st.ConnPostgres]:
    with st.connection_postgres(postgres_db) as conn:
        yield conn
        with conn.transaction():

def clean_postgres_tables(conn: st.ConnPostgres) -> None:
    conn.execute(f"DROP SCHEMA public CASCADE")
    conn.execute(f"CREATE SCHEMA public")

Example usage can be seen in the stepping tests.