deadpool-sqlite: in-memory DB not actually shared?

[rongcuid]

It seems like in-memory databases are not actually connected to the same one. For whatever reason, I don't see the tables created previously when I try to use a connection later. A file-based DB does work.

[rongcuid]

I don't know how to reliably reproduce this, but it seems like if I get from two threads, the second actually returns an empty database.

[rongcuid]

Never mind. It is actually SQLite connection URI's problem: :memory: is actually private to connection: https://www.sqlite.org/inmemorydb.html

I used file:main?mode=memory&cache=shared to get a named, shared, in-memory database that works for the pool.

Obviously, this works only for one process.

[bikeshedder]

That behavior is indeed expected with a :memory: connection. Maybe the deadpool_sqlite::Config should create a warning when trying to use this kind of DB URL as it's very likely a configuration error.

I just checked and rusqlite::Connection is !Sync and therefore can't be shared across threads so a plain Arc<Connection> is not an option either. You either need to use Mutex<Connection> or stick with a temporary file and create multiple "connections" to it.

May I ask what you're actually trying to do? It almost sounds like a full fledged DB (like PostgreSQL) or none at all would be better suited for your use-case.

[rongcuid]

deadpool_sqlite::Config does not expose any SQLite setting at all, despite rusqlite supporting them Or maybe I missed some part of documentation.

I am actually just tryinig to use an in-memory database in an async server. I encountered issue when sometimes the deadpool connection gives an empty DB instead of the one initialized. I understand that this is expected now.

I solved it by using the mentioned URL -- multiple connections to the shared memory URL would use the same in-memory database.

[bikeshedder]

You can modify some of the config via the post_create hook and Connection::set_db_config.

There is currently no way to pass OpenFlags or the vfs parameter.

A PR for this feature would be highly appreciated. 👍

[rongcuid]

I didn't get what post_create does from its type signature. Does it pass in a function that operates on rusqlite::Connection object?

[bikeshedder]

Does it pass in a function that operates on rusqlite::Connection object?

Kind of, yes. You can either pass a Hook::Fn or Hook::AsyncFn (Hook is an enum with those two variants).

You can find an example how it can be used in the tests: https://github.com/bikeshedder/deadpool/blob/master/tests/managed_hooks.rs

Please note that the signature will change in the next release of deadpool: HookError::Continue and HookError::Abort will be removed. In the future errors in post_create hooks will always abort and errors in pre_recycle and post_recycle will continue. It was a design mistake which I will fix in the next major version. If you do encounter an error during post_create you should return an Err(HookError::Abort(...)) as continuing object creation is just nonsensical anyways.

[rongcuid]

OK, I will give it a try. Though I generally just panic!() because the pool is considered initialization anyways.

[bikeshedder]

Closing this as invalid as it is not a deadpool-sqlite bug. :memory: works as expected even though it's questionable at best if a SQLite connection with :memory: should be pooled at all. Unless there is a way to share the same in-memory database with rusqlite it's going to cause weird behavior if used in an actual application.

Maybe future versions should raise an error or log a warning at least when trying to use a :memory: URI.

I opened a new tracking issue for the open flags and vfs parameter:

• Add support for OpenFlags and vfs parameter to deadpool-sqlite #214 214