Hello, my question is about handling temporary SQLite databases (as described here: https://www.sqlite.org/inmemorydb.html) with yoyo, SQLite and SQL Alchemy.

I have an SQL Alchemy application, which uses yoyo for DB migration stuff. I first prepare the connection URL, then I apply DB migrations using yoyo using that URL, and then I use the same URL to create an SQL Alchemy connection which is used by the application. For regular usage, it works okay.

But for unit testing I try to create a temporary database (passing an empty database name to SQLite). In SQL Alchemy I can create a temporary database by passing the URL "sqlite://". But first of all, passing this URL to get_backend() function of yoyo gives an error like this:

>> from yoyo import get_backend
>> get_backend("sqlite://")

C:\Work\VirtualEnvs\___\lib\site-packages\yoyo\connections.py:99: in get_backend
    return backend_class(parsed, migration_table)
C:\Work\VirtualEnvs\___\lib\site-packages\yoyo\backends.py:164: in __init__
    self._connection = self.connect(dburi)
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

self = <yoyo.backends.SQLiteBackend object at 0x0000020B7F2F9A90>
dburi = DatabaseURI(scheme='sqlite', username=None, password=None, hostname=None, port=None, database=None, args={})

    def connect(self, dburi):
>       conn = self.driver.connect(
            dburi.database, detect_types=self.driver.PARSE_DECLTYPES
        )
E       TypeError: expected str, bytes or os.PathLike object, not NoneType

C:\Work\VirtualEnvs\___\lib\site-packages\yoyo\backends.py:658: TypeError

Secondly, even if passing this would succeed, it would remove the temporarily created database as soon as yoyo was done applying the migrations, so opening a temporary database for SQL Alchemy afterwards would result in an empty database.

How should I approach this problem? Is there a way to create temporary SQLite databases in yoyo? Can I share that temporary database somehow with SQL Alchemy?

Creating a temporary random SQLite file on disk would probably solve the problem, should I use that approach instead? I am not a unit test guru so maybe creating the database in-memory is actually not the best approach at all?

Yes, seems to be working. I had other errors, but they are probably related to a bug in my code.

When I try to use get_backend() function with a connection string which creates in-memory SQLite databases, I get errors. This is important for us since we use in-memory SQLite databases in our unit tests.

A minimal code to reproduce this is:

from yoyo import get_backend
connection_str = 'sqlite://'
get_backend(connection_str)

The error I get is:

Traceback (most recent call last):
  File "<input>", line 3, in <module>
  File "C:\Work\VirtualEnv\lib\site-packages\yoyo\connections.py", line 99, in get_backend
    return backend_class(parsed, migration_table)
  File "C:\Work\VirtualEnv\lib\site-packages\yoyo\backends.py", line 164, in __init__
    self._connection = self.connect(dburi)
  File "C:\Work\VirtualEnv\lib\site-packages\yoyo\backends.py", line 658, in connect
    conn = self.driver.connect(
TypeError: expected str, bytes or os.PathLike object, not NoneType

Python: 3.8

yoyo-migrations: 7.3.1