operational error when testing my CLI app with a SQLite DB

Question

So I wanted to add tests to my CLI app, but I am struggling to replace my db with a temporary one. So far I have used SQLModel for database access, but for the app testing I want to assert the db values using raw SQL statements. However, I get a connection error when trying to access my temporary DB.

sqlite3.OperationalError: unable to open database file

My plan so far is the following: In my test file I replace the sqlite uri with one directing it to a sqlite db in a temporary dictionary (using pytest tmp_path). My assumption was, that if I create that DB with SQLModel, I'd be able to access it with sqlite3 later on. Below you'll find the code. (At the moment, I haven't added an assert yet. That'll be added of course as soon as the DB connection works)

Also, is there a better way to replace my actual db, or is it fine what I am doing here?

My test file:

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):
    temp_db(tmp_path)
    assert db.sqlite_url == f"sqlite:///{tmp_path}/db.db"


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

Excerpt from db.py:

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


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

engine = create_engine(sqlite_url)


def create_session_and_db():
    SQLModel.metadata.create_all(engine)

Excerpt from app_typer.py:

import typer

app = typer.Typer(add_completion=False)


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

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

Edit

So I fixed the URI for sqlite3 thanks to @emilio-silva. However I bumped into a new problem: the database sqlmodel creates seems to be somewhere else than in the temporary directory I specified with tmp_path.

If I open the folder after running pytest, db.db exists, but is empty (Checked with DB Browser for SQLite). This makes me assume, that sqlite3 actually created a new DB. I can't figure out though, why sqlmodel doesn't put the db in the correct location. When setting echo=True for the engine (sqlmodel/sqlalchemy), I get the following output:

INFO     sqlalchemy.engine.Engine:base.py:2689 BEGIN (implicit)
INFO     sqlalchemy.engine.Engine:base.py:1848 PRAGMA main.table_info("project")
INFO     sqlalchemy.engine.Engine:base.py:1848 [raw sql] ()
INFO     sqlalchemy.engine.Engine:base.py:2695 COMMIT
INFO     sqlalchemy.engine.Engine:base.py:2689 BEGIN (implicit)
INFO     sqlalchemy.engine.Engine:base.py:1848 INSERT INTO project (name, project_number, offer_number, project_type, classification) VALUES (?, ?, ?, ?, ?)
INFO     sqlalchemy.engine.Engine:base.py:1848 [generated in 0.00037s] ('Project', '00-00', None, 'public', 'internal')
INFO     sqlalchemy.engine.Engine:base.py:2695 COMMIT
INFO     sqlalchemy.engine.Engine:base.py:2689 BEGIN (implicit)
INFO     sqlalchemy.engine.Engine:base.py:1848 SELECT project.id, project.name, project.project_number, project.offer_number, project.project_type, project.classification      
FROM project
WHERE project.id = ?
INFO     sqlalchemy.engine.Engine:base.py:1848 [generated in 0.00055s] (15,)
INFO     sqlalchemy.engine.Engine:base.py:2692 ROLLBACK

For completeness the error I get from sqlite3: sqlite3.OperationalError: no such table: project

And the directory of the tmp_path: C:\Users\**redacted**\AppData\Local\Temp\pytest-of-**redacted**\pytest-16\test_add_item_to_db0

Edit 2

Okay I figured out part of the solution.

Even though I change the database uri, the sqlmodel engine still connects to the original database, not the temporary one. My guess is, that the create_engine command runs at the point when I import db.py, not when I run my test. And since I change the database string after the import, it's too late to feed it to the engine.

My guess is, that the solution would be to somehow implement the database creation/binding with sqlmodel differently, can anyone point me to a fitting approach?

Answer 1

If you are connecting directly to the sqlite DBI driver, you should pass the filename directly:

con = sqlite3.connect(f"/{tmp_path}/db.db")

The sqlite: URL scheme is used by generic drivers, such as the SQLAlchemy backend of sqlmodel.

Following your execution flow, it seems that from projects import db associates the engine with the initial engine.

You can bypass this by importing sqlmodel and changing your temp_db() to:

db.engine = sqlmodel.create_engine(f"sqlite:///{path}/db.db")