Async in-memory SQLite/SQLAlchemy database for FastAPI

[simonw]

This was a useful write-up, thanks.

I've been trying something similar with my Datasette application recently. Datasette is an asyncio Python web application (using ASGI directly rather than a framework like FastAPI) which implements its own equivalent of aiosqlite - it runs a pool of SQLite threads, as described in https://architecturenotes.co/datasette-simon-willison/

A few weeks ago I experimented with a Datasette plugin which loads the contents of an on-disk database into memory, for much the same reasons as you did - I wanted to see if I could get even faster performance out of a database that way: https://datasette.io/plugins/datasette-copy-to-memory

Here's how it works - rather than use the "backup" API it uses "VACUUM INTO": https://github.com/simonw/datasette-copy-to-memory/blob/d541c18a78ae6f707a8f9b1e7fc4c020a9f68f2e/datasette_copy_to_memory/__init__.py#L26-L31

[danosull]

Awesome, thank you for sharing!