Caveat With Model Modules and SQLAlchemy 2+

The Context

A few months ago, I was working on a simple REST API to acquire the skills of Python backend development using Flask 3, and SQL Alchemy 2.

The app I was building was a little timesheet tool where I could record the time spent on projects and tasks.

I had 3 models (excluding the base model):

the Project model
the Task model
the Time Record model

While I started to code the API, I defined the models into a single file models.py that I imported on a file main.py where I had this:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35


import os
from sqlalchemy import create_engine
from dao.models import Model

def init_engine(base_dir: str):
    """Load the engine

    Args:
        base_dir (str): The base directory where the database is stored

    Returns:
        object: The engine
    """
    db_file_name = f"sqlite:///{base_dir}{os.sep}..{os.sep}db{os.sep}sqlalchemy.db"
    engine = create_engine(db_file_name, echo=True)
    return engine

def reset_database(base_dir: str):
    """Reset the database by dropping all tables

    Args:
        base_dir (str): The base directory where the database is stored
    """
    Model.metadata.drop_all(init_engine(base_dir))

def init_database(base_dir: str):
    """Initialize the database by creating the tables that needs to be created.
    It doesn't try to recreate what already exists.

    See https://docs.sqlalchemy.org/en/20/core/metadata.html#sqlalchemy.schema.MetaData.create_all

    Args:
        base_dir (str): _description_
    """
    Model.metadata.create_all(init_engine(base_dir))

It defines 3 methods. In the app.py, I defined a simple logic to drop everything only in development when starting the server:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49


import os

from flask import Flask
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, scoped_session
from dotenv import load_dotenv

from constants.environment_vars import EnvironmentVariable

# from dal.main import init_engine
from dal.main import init_database, reset_database, init_engine

load_dotenv()

env = os.getenv(EnvironmentVariable.ENVIRONMENT)

# Create the Flask application instance
app = Flask(__name__)
BASE_DIR = os.path.dirname(os.path.abspath(__file__))

# Create the database engine (dependency injection)
app.config[EnvironmentVariable.DATABASE_ENGINE] = init_engine(BASE_DIR)
# Create a session maker using the injected engine
SessionLocal = scoped_session(
    sessionmaker(
        autocommit=False,
        autoflush=False,
        bind=app.config[EnvironmentVariable.DATABASE_ENGINE],
    )
)
app.config[EnvironmentVariable.SESSION_LOCAL] = SessionLocal

if env == "dev":
    print("Environment is dev")
    # TODO: drop the database
    print("drop database...")
    reset_database(BASE_DIR)
    print("dropped database!")
    # TODO: and recreate it
    print("create database...")
    init_database(BASE_DIR)
    print("created database!")

if env == "production":
    print("Environment is production")
    # TODO: create
    print("create database...")
    init_database(BASE_DIR)
    print("created database!")

The Problem

Once I completed the Project endpoints, I moved on to the Task endpoints and I wanted to split in individual files the models.

While it didn’t prevent from adding projects after updating the imports, it broke the database reset…

When I ran the request to create a project right after restarting the server, I notice the error about the project’s name unique constraint.

Then I notice that after the last server restart, the terminal didn’t log the usual SQL code ran by SQLAlchemy on initialization of the database after the reset.

Why

In Python, a file is a module and with the code of main.py above, the only model left is the base Model that does nothing to change the database.

Therefore Model.metadata.drop_all() does nothing…

How to Use Split Files and Retain the Functionality

Simply don’t. I think this is a habit of developing n .NET for 15 years. But in Python programming, and if you use the ORM SQLAlchemy, keep the code-first database schema in a single file called database.py or entities.py or whatever you wish.

Also, one other limitation to split files is that using the ORM feature that allows retrieving related items in a relationship between two models will cause you a bit of trouble.

I like Python and how it works.

For more on Python, browse to the tag.

Credit: Photo by Pixabay. .