Use in-memory databases with multiple connections
Opened this issue · 2 comments
Hi folks!
I've seen some of the previous issues where it appears that library's only path to using an in-memory database is to use 1 connection:
Ostensibly this is because, when opening a database against :memory:, each new connection opens a new, completely separate database, which may be confusing to users.
However, SQLite supports allowing connections to share a single in-memoroy database with the shared cache mode. This is accomplished by passing :memory:?cache=shared as the connection string.
Quoting from the docs:
Beginning with SQLite version 3.7.13 (2012-06-11), shared cache can be used on in-memory databases, provided that the database is created using a URI filename. For backwards compatibility, shared cache is always disabled for in-memory databases if the unadorned name ":memory:" is used to open the database. Prior to version 3.7.13, shared cache was always disabled for in-memory databases regardless of the database name used, current system shared cache setting, or query parameters or flags.
Enabling shared-cache for an in-memory database allows two or more database connections in the same process to have access to the same in-memory database. An in-memory database in shared cache is automatically deleted and memory is reclaimed when the last connection to that database closes.
https://www.sqlite.org/sharedcache.html#shared_cache_and_in_memory_databases
Is there anything in ecto_sqlite3 or exqlite or Elixir itself that prevents us from adding this functionality? I'd be happy to take a stab at contributing if enabling this functionality is both possible and desired, as I have use cases for it in my own work (right now I do this in other languages)!
Thank you!
Just as another datapoint, it appears one can pass file::memory:?cache=shared and connect to the in-memory database. I see configured the pool to use 5 connections, and I can see the 5 connections in observer under the db_connection app. I'm trying to verify that each connection is seeing the same db.
There's been some conversations around the db_connection implementation for this here elixir-sqlite/exqlite#192
What you are proposing ties in with that and I do want to support the ability to use :memory: even when not using cache=shared