Tests with FastAPI and PostgreSQL

I'm developing a backend app with FastAPI connected to a PostgreSQL database and I'm a bit stuck and lost with the tests good practices.
I read a lot stackoverflow posts and blogs but I'm not really used to backend development and I still doesn't really understand what is the best practice.

Knowing that I use SQLModel, it is suggested in the documentation to perform the tests with a DB SQLite in memory. The problem is that when I follow the explained approach, I am struck by the non-compatibility between PG and SQLite (about schemas). The point is that I am consuming an existing DB with several schemas and not just a public schema. So, when I run my tests, I encounter the error "schema pouetpouet does not exist".

Finally, the question is: What should I do to test my app ?

1. Find a way to setup the compatibility between my prod Postgres DB and an in-memory SQLite DB ?
2. Apply my tests on a preprod Postgres DB and try to cleanup the added/removed items ? (what I did actually but I don't think it is a really good practice)
3. Setup a local Postgres server inside a Docker container ?
4. Mock a DB with kind of a Dict in the pytest test file ?
5. Use a third lib like testcontainers for exemple ?
6. Don't do tests ?

After all, I'd like to do unit and integration tests so maybe there is not only one solution about my needs.

Here is a really simplified version of my project:

The architecture of my project: (Consider that there is an __ init__.py file in each folder)

app/
├── api/
│   ├── core/
│   │   ├── config.py   #get the env settings and distribute it to the app
│   │   ├── .env
│   ├── crud/
│   │   ├── items.py    #the CRUD functions called by the router
│   ├── db/
│   │   ├── session.py  #the get_session function handling the db engine
│   ├── models/
│   │   ├── items.py    #the SQLModel object def as is in the db
│   ├── routers/
│   │   ├── items.py    #the routing system
│   ├── schemas/
│   │   ├── items.py    #the python object def as it is used in the app
│   ├── main.py         #the main app
├── tests/
│   ├── test_items.py   #the pytest testing file

In the crud/items.py:

from fastapi.encoders import jsonable_encoder
from sqlmodel import Session, select
from api.models import Item
from api.schemas import ItemCreate


def get_item(db_session: Session, item_id: int) -> Item:
    query = select(Item).where(Item.id == item_id)
    return db_session.exec(query).first()


def create_new_item(db_session: Session, *, obj_input: ItemCreate) -> Item:
    obj_in_data = jsonable_encoder(obj_input)
    db_obj = Item(**obj_in_data)
    db_session.add(db_obj)
    db_session.commit()
    db_session.refresh(db_obj)
    return db_obj

In the db/session.py:

from sqlalchemy.engine import Engine
from sqlmodel import create_engine, Session
from api.core.config import settings

engine: Engine = create_engine(settings.SQLALCHEMY_DATABASE_URI, pool_pre_ping=True)


def get_session() -> Session:
    with Session(engine) as session:
        yield session

In the models/items.py:

from sqlmodel import SQLModel, Field, MetaData

meta = MetaData(schema="pouetpouet")  # https://github.com/tiangolo/sqlmodel/issues/20


class Item(SQLModel, table=True):
    __tablename__ = "cities"
    # __table_args__ = {"schema": "pouetpouet"}
    metadata = meta

    id: int = Field(primary_key=True, default=None)
    city_name: str

In the routers/items.py:

from fastapi import APIRouter, Depends, HTTPException
from sqlmodel import Session
from api.crud import get_item, create_new_item
from api.db.session import get_session
from api.models import Item
from api.schemas import ItemRead, ItemCreate

router = APIRouter(prefix="/api/items", tags=["Items"])


@router.get("/{item_id}", response_model=ItemRead)
def read_item(
    *,
    db_session: Session = Depends(get_session),
    item_id: int,
) -> Item:
    item = get_item(db_session=db_session, item_id=item_id)
    if not item:
        raise HTTPException(status_code=404, detail="Item not found")
    return item


@router.post("/", response_model=ItemRead)
def create_item(
    *,
    db_session: Session = Depends(get_session),
    item_input: ItemCreate,
) -> Item:
    item = create_new_item(db_session=db_session, obj_input=item_input)
    return item

In the schemas/items.py:

from typing import Optional
from sqlmodel import SQLModel


class ItemBase(SQLModel):
    city_name: Optional[str] = None


class ItemCreate(ItemBase):
    pass

class ItemRead(ItemBase):
    id: int
    class Config:
        orm_mode: True

In the tests/test_items.py:

from fastapi.testclient import TestClient
from api.main import app

client = TestClient(app)

def test_create_item() -> None:
    data = {"city_name": "Las Vegas"}
    response = client.post("/api/items/", json=data)
    assert response.status_code == 200
    content = response.json()
    assert content["city_name"] == data["city_name"]
    assert "id" in content

ps: not being very experienced in backend development, do not hesitate to bring constructive remarks about my code if you notice something strange. It will be very well received.

Testing is always a confusing matter when you begin with it, with many opinions and few things set in stone.

That said your questioning approach seems correct and thought out so I will try to answer (keep in mind that this comes from personal preference and experience on writing tests for a multitude of applications so far, so it will be mostly opinionated):

1. I wouldn't do that because it may be too much work and result in very brittle tests. If you work around the compatibility issues, if/when things break you will have trouble finding out what the real issue is.
2. This is a valid approach: You can implement a set of integration tests with predictable results, deploy your code in a Staging environment and run your suite against that. You can even run these tests in a step of your CI/CD pipeline for automation.
3. That is a valid approach for local DEV: Use this for your local development and testing of new implementations. You can use this for Unit Testing, but you must cleanup after each run (probably write scripts that automate the cleanup on teardown) to avoid residues and your tests failing or passing unexpectedly.
4. That is a valid approach but doesn't work that well for testing the DB. This I am usually using for testing the API and "postprocessing" methods on data that are fetched from the DB. In other words, just because the mocks work, doesn't mean that the end result will. Read an interesting article about this btw.
5. This is a valid approach: testcontainers provides a containerized DB that spawns for testing, we can add schemas and tables to it without fear (if something breaks here, means that the testing caught an issue early) and then it gets despawned afterward. I would use this approach for this case! (here is a basic example of using it with pytest)
6. Fear leads to not using tests, not using tests leads to many sleepless nights, and many sleepless nights lead to more issues. This is the path to the dark side (and many lost days and even weekends).

Finally, keep in mind that there are no "silver bullets" here so different approaches can be used together.

For example in your case, I would go with testcontainers for local and pre-commit testing and then run an integration test suite in a Staging environment before deploying to PROD.

<hr>

Your approach to the code seems good at a glance.<br/>
A minor remark here: Since you are using SQLModel you can avoid the separate SQLAlchemy models & Pydantic schemas that are used for the "traditional" FastAPI approach with SQLAlchemy.
You can use the SQLModels directly as schemas instead as demonstrated in the documentation

Hope this helps you to find a good solution for your testing