Testing FastAPI CRUD operations with SQLModel instead of SQLAlchmey

[timwilson]

First Check

• I added a very descriptive title here.
• I used the GitHub search to find a similar question and didn't find it.
• I searched the SQLModel documentation, with the integrated search.
• I already searched in Google "How to X in SQLModel" and didn't find any information.
• I already read and followed all the tutorial in the docs and didn't find an answer.
• I already checked if it is not related to SQLModel but to Pydantic.
• I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

• I commit to help with one of those options 👆

Example Code

# conftest.py
import pytest
import sqlite3

from starlette.testclient import TestClient

from app.auth import settings
from app.main import app


@pytest.fixture
def ro_client():
    client_instance = TestClient(app)
    client_instance.headers["X-API-KEY"] = settings.ro_api_key
    return client_instance


@pytest.fixture
def rw_client():
    client_instance = TestClient(app)
    client_instance.headers["X-API-KEY"] = settings.rw_api_key
    return client_instance


@pytest.fixture(autouse=True, scope="session")
def in_memory_db():
    # Connect to an in-memory database
    memory_conn = sqlite3.connect(":memory:")
    memory_cur = memory_conn.cursor()

    # Attach the on-disk database to the in-memory database connection
    memory_conn.execute(
        f"ATTACH DATABASE '{settings.db_path}{settings.db_name}' AS ondisk"
    )

    # Get the list of tables from the on-disk database
    memory_cur.execute("SELECT name FROM ondisk.sqlite_master WHERE type='table'")
    tables = memory_cur.fetchall()

    # Copy each table's data from the on-disk database to the in-memory database
    for table in tables:
        table_name = table[0]
        memory_cur.execute(
            f"CREATE TABLE {table_name} AS SELECT * FROM ondisk.{table_name}"
        )

    yield memory_conn  # This connection will be used in your tests

    # Cleanup: Close the in-memory database connection
    memory_conn.close()

# test_models.py

def test_create_location(rw_client):
    endpoint = "/v1/locations"
    response = rw_client.post(
        endpoint,
        json={
            "name": "Archery Club",
            "slug": "archery-club",
            "city": "Anytown",
            "state": "Archeryville",
        },
    )
    assert response.status_code == 200, response.text
    data = response.json()
    assert data["name"] == "Archery Club"
    assert data["slug"] == "archery-club"
    assert data["city"] == "Anytown"
    assert data["state"] == "Archeryville"
    assert "id" in data

### Description

I've built a FastAPI project that utilizes SQLModel with SQLite. Now I'm working on beefing up the tests for the CRUD operations for my various endpoints, but I've got a small problem with creating a proper in-memory SQLite database. My intent is to copy my production SQLite database into an in-memory SQLite database for testing which will then disappear after my tests are run.

When I run the `test_create_location` test above, the new record is created in my production database.

I believe the `autouse=True` parameter of the fixture should ensure that the fixture gets used, but it's not clear to me how my test functions would know to connect to the in-memory database. I'm sure I'm missing something obvious, but I haven't found a good example emphasizing a SQLModel approach. Everything I can find assumes that the developer is using the SQLAlchemy ORM directly.

Can anyone weigh in?

### Operating System

macOS

### Operating System Details

Running FastAPI inside a docker container via Docker Desktop.

### SQLModel Version

0.0.8

### Python Version

3.12.0

### Additional Context

_No response_

[gazpachoking]

You have a fixture that's yielding a connection to an in memory database, but you aren't doing anything with it. You'll have to override how your app interacts with the database to tell it to use the in memory database rather than the normal one. There is an example how to do that here https://sqlmodel.tiangolo.com/tutorial/fastapi/tests/