I have made a small project to try and learn to implement SQLite-DBs into my projects. For the DB part, I use sqlmodel and followed their tutorial. For the creation of the engine however, they use a module variable. This turned out to be a problem for me when trying to implement proper tests (see stackoverflow question). The summary is: Because of the fact, that engine is a module variable, it gets created on import. Because of that, I can't import db.py to change the url variable to a temporary one, because the engine which I want to use gets created before I change the SQLite-URL.

Here are snippets from my test:

import sqlite3

from typer.testing import CliRunner

from projects import db
from projects.app_typer import app

def temp_db(path):
    db.sqlite_url = f"sqlite:///{path}/db.db"

runner = CliRunner()

def test_url_replacement(tmp_path):
    assert db.sqlite_url == f"sqlite:///{tmp_path}/db.db"

def test_add_item_to_db(tmp_path):
    result = runner.invoke(app, ["add", "public", "-n", "Project", "-p", "00-00"])
    con = sqlite3.connect(f"{tmp_path}/db.db")
    cur = con.cursor()
    db_entry = cur.execute("SELECT * FROM project").fetchone()
    assert db_entry == {} # will be adjusted later


import typer

app = typer.Typer(add_completion=False)

@app.callback(invoke_without_command=True, no_args_is_help=True)
def main():

app.add_typer(add.app, name="add", help="Add a project to the DB.")


from sqlmodel import Field, Session, SQLModel, create_engine, select

sqlite_url = "sqlite:///database.db"

engine = create_engine(sqlite_url)

def create_session_and_db():

# vvvv Table definitions and methods to operate on DB vvvv

I guess the solution would be to put my DB into a class? Would something like this work? But still, how would I change out the database url properly in testing?

from sqlmodel import Session, SQLModel, create_engine

class DB:
    def __init__(self):
        self.engine = None

    def init(self, url):
        self.engine = create_engine(url)

    def get_projects(self):
        with Session(self.engine) as session:
  • Your DB class has two "init" methods, which highlights your confusion. Instead, only create a DB instance when you can fully initialize it, passing the URL via the constructor. You might create one DB object in your main(), a different one in a test fixture. The more difficult question is how to do "dependency injection", passing that DB object to all functions that need it. Using function arguments for dependencies quickly gets tiring, but Typer offers a Context object. I have also used Python's contextvar module as a kind of safer global variable, similar to how Flask works.
    – amon
    Dec 1, 2023 at 18:57
  • Well you could also call the second method connect or sth similar. The idea is, that I can seperate object instantiation from the actual database connection. In that way, I can still assign the instance of the DB class to a module variable, which would make it available as such, but could postpone the actual connection to the db to a later point. This could allow me to interfere with the url for testing before the connection is made? But to sum it up, yes, I am confused about this. Imo there should be an obvious way to set things up to enable easy use and testing, right?
    – Jan
    Dec 2, 2023 at 11:50


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.