Unit testing with in memory db

[farridav]

Hi,

Im trying to get my test suite to run using an in memory sqlite db, as its very slow using one on the file system, but im finding failures, and not sure if i have things setup properly, or if there is an issue with the persistence of the connection, heres an excerpt from my setup:

# conftest.py

from typing import Any, AsyncGenerator
import pytest
from pydbantic import Database

from myapp.models import MyModel


@pytest.fixture
async def setup_db() -> AsyncGenerator[Database, Any]:
    db_url = "sqlite:///:memory:"
    db = await Database.create(
        db_url,
        tables=[MyModel],
        cache_enabled=False,
        testing=True,
    )
    yield db
    db.metadata.drop_all(db.engine)



async def test_thing(setup_db: Database) -> None:
    shop = await MyModel.create(name="test")

    assert shop is not None

And i seem to get ERROR tests/test_thing.py::test_thing - sqlite3.OperationalError: no such table: DatabaseInit

Which looks like the database session is not being reused, ive tried various things like sqlite:///:memory:?cache=shared to no avail.

Do you see anything immediately wrong in my approach ?

[codemation]

Hi @farridav I had a look at what is causing it, looks like its a known issue from https://github.com/encode/databases/issues/75 - the library partly responsible for connections in pydbantic. I would suggest the workaround of using a file DB perhaps in a temporary location such as /tmp/ which should be much faster than disk.

[farridav]

Thanks for looking into this for me, its a shame.. I'm trying to switch over from sqlmodel to pydbantic, to do away with all the sqlalchemy connection handling and boilerplate, here are the timings between the two implementations:

pydbantic (sqlite, anywhere in file system)

16 passed in 22.74s

sqlmodel (sqlite in memory db)

16 passed in 1.37s

Hopefully this PR gets in: https://github.com/encode/databases/pull/451 which might allow a workaround

[farridav]

Im told that support is now available for this in the databases library, see https://github.com/encode/databases/pull/561

Though i dont think pydbantic passes through the values that we need to pass through to take use of this ..