SQLModel

As already touched on, SQLModel is a library for interacting with SQL databases from Python code with Python objects. SQLModel is intuitive, easy to use, highly compatible, and robust. From my adventures, this library seems the most logical approach for working with databases in FastAPI.

See https://sqlmodel.tiangolo.com/

Install

Install sqlmodel using pip and remember to freeze this to the requirements files.

pip install sqlmodel

Add it to the non_ver_reqs.txt file:

fastapi
uvicorn[standard]
pydantic-settings
sqlmodel

And freeze the requirements:

pip freeze > requirements.txt

SQLite

Add the path to the SQLite database file in the projects core settings:

config/settings.py

CONFIG_MAIN_APP_SQLITE: str = "sqlite:///mnt/db/exampleforyou.sqlite3"

When working with SQLite, you work with a single local file. This SQLite file will live in a volume mount location. Create a new directory external to your project directory to store the database, for example:

mkdir -p ~/volumes/db

As already demonstrated when adding certificates, moving forward with this project, any external volume that needs to be mounted will be done under a project directory called mnt. If not already done, create this directory in the root of you project:

mkdir mnt

When working locally, create a soft link:

ln -s ~/volumes/db mnt/db

Use tree -I venv to list the structure of your project via the command line. The project tree should look something like this:

.
├── api
│   └─ health_api.py
├── main.py
├── mnt
│   └── db -> /home/USERNAME/volumes/certs
│   └── certs -> /home/USERNAME/volumes/db
├── non_ver_reqs.txt
├── requirements.txt
└── settings
    └── config.py

Now create a project level directory for database Python code:

mkdir db

Add the following file and code to handle creating the database and session:

db/db.py

from sqlmodel import create_engine, Session

from config.settings import settings

engine = create_engine(
    settings.CONFIG_MAIN_APP_SQLITE,
    connect_args={"check_same_thread": False},
    echo=True
)


def get_session_db():
    with Session(engine) as session:
        yield session