Use in-memory databases with multiple connections

[ckampfe]

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:

• Issue with ":memory:" value exqlite#237
• Cannot run migrations on in-memory db #75
• Raise if using an in memory database with pool_size != 1 #128

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!

[ckampfe]

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.

[warmwaffles]

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