SQlite in memory for testing

[przwr]

Hi! I'm new here :)

I'm trying to migrate to Piccolo but when trying to run tests with SQLiteEngine(path=":memory:")

I got sqlite3.OperationalError: no such table: users

I initialize db like this:

tables = Finder().get_table_classes()
create_db_tables_sync(*tables)

and it seems that when I put the path to file instead of ":memory:" it works but I want to use in-memory db for speed and easier cleanup.

Do you know what an issue could be here?

Best,
Przemek

[dantownsend]

With SQLite's in-memory database, it only lasts as long as a single connection stays open.

So with Piccolo you need to use transactions to make sure that every query uses the same connection - here's an example:

from piccolo.engine.sqlite import SQLiteEngine
from piccolo.table import create_db_tables, Table
from piccolo.columns import Varchar


DB = SQLiteEngine(path=":memory:")


class MyUser(Table, db=DB):
    name = Varchar()


async def main():
    async with DB.transaction():
        await create_db_tables(MyUser)

        user = MyUser(name="Bob")
        await user.save()

        print(await MyUser.select())


if __name__ == '__main__':
    import asyncio
    asyncio.run(main())

Even using file based storage for SQLite is super fast, so you could consider using that instead if you can't use the approach above.

[dantownsend]

From the SQLite docs, it also looks like you can do file::memory:?cache=shared, to make the in-memory DB accessible from multiple connections:

https://www.sqlite.org/inmemorydb.html#sharedmemdb

I haven't tested this though.

[przwr]

Thank You for a quick reply :)

SQLiteEngine(path=":memory:?cache=shared")

just works.

I guess tortoise TestCase did something like this under the hood.

[przwr]

I just realized that it was working only because it was creating file ":memory:?cache=shared". I guess I will just use file for testing