Sqlalchemy in-memory database for MSSQL

I’m trying to setup tests for my project and want to use an in memory database for those tests. Based on some examples I found online I’ve been able to get an in memory sqlite database working…

class TestService:
    def setup_method(self) -> None:
        sqlite_shared_name = "test_db_{}".format(
            random.sample(string.ascii_letters, k=4)
        )
        engine = create_engine(
            "sqlite:///file:{}?mode=memory&cache=shared&uri=true".format(
                sqlite_shared_name
            ),
            echo=True,
        )
        self.session = Session(engine)
        Base.metadata.create_all(engine)

The problem I have is my models are all based around MSSQL (that’s what the ‘real’ code is using) so I get

def do_execute(self, cursor, statement, parameters, context=None):
  cursor.execute(statement, parameters)

E sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) unknown database “dbo”

I also have some specific dialect things brought in for my models

from sqlalchemy.dialects.mssql import UNIQUEIDENTIFIER, DECIMAL, BIGINT

Is there any way to get an in memory sqlalchemy engine that works with all these MSSQL functions?

Thanks!

Answer

To do this your SQL statements would need to changed per-database or made compatible with both. Note that in SQL Server if you omit the schema name, it will default to the user’s default schema, which is typically ‘dbo’.