storage_status/1 should return :up for in-memory database

[sntran]

Currently, the function checks if the database file exists. This returns false for in-memory.

Which in turn fails Phoenix.Ecto.CheckRepoStatus during development.

I would like to see if we can make the function return true instead.

[warmwaffles]

@sntran unfortunately with how the connections are pooled, your in memory database will always be empty because each new connection opens its own database. This is mostly an issue with how db_connection handles pooled connections and long lived connections.

Although you are right, we should return :up whenever an in memory database is used.

[japplegame]

unfortunately with how the connections are pooled, your in memory database will always be empty because each new connection opens its own database.

If you use cache=shared option, then all connections will share the same in-memory database.

rc = sqlite3_open("file:memdb1?mode=memory&cache=shared", &db);

This allows separate database connections to share the same in-memory database. Of course, all database connections sharing the in-memory database need to be in the same process. The database is automatically deleted and memory is reclaimed when the last connection to the database closes.

SQLite docs: In-memory Databases And Shared Cache

But I ran into a very strange error when I was trying to use an in-memory database. Everything works fine until you try to perform two parallel transactions, one of which performs a write operation.
The error is
database table is locked
Please note, not database is locked, not database is busy, but database table is locked.
I have no idea what that means.

[warmwaffles]

I haven't had time to really dive into this lately, but I don't know how the shared cache is going to work when the DB is on a different machine than the one trying to access it. Could be an unfounded fear. Unsure yet.

[warmwaffles]

I'm still not sure if this is something that we should support. DBConnection is pretty entangled into ecto and I don't think we can guarantee connections being in the same process due to it.

[AlexR2D2]

I ran into the same problem with Phoenix.Ecto.CheckRepoStatus

I have the number of simple Phoenix Apps for Admin. Each time when an application start it opens the in-memory sqlite DB, when run migration to create user/token tables and register only one user with the Admin username and password from the ENV variables. So, this way I have the login functionality for simple Admin tool with completely autogenerated code for authentication (mix phx.gen.auth). All I have written is code to run migration and register user at app startup. Very handy and securely. :)

[rhcarvalho]

tldr: storage_status/1 incorrectly returns down for database paths using SQLite's URI format file:/path/to/db.

---

I was trying to use an in-memory database using the memdb VFS:

# config/dev.exs

config :phoenix_app, PhoenixApp.Repo,
  database: "file:/db?vfs=memdb",
  pool_size: 5,
  stacktrace: true,
  show_sensitive_data_on_connection_error: true

https://www.sqlite.org/forum/forumpost/c1004073ca76117f
https://sqlite.org/inmemorydb.html
https://sqlite.org/releaselog/3_36_0.html

Realized the error from Phoenix.Ecto.CheckRepoStatus in development is due to how storage_status/1 is implemented and tracked here.

Further, I decided to drop the ?vfs=memdb and point to a writable path -- like so:

config :phoenix_app, PhoenixApp.Repo,
  database: "file:/tmp/data.db",
  pool_size: 5,
  stacktrace: true,
  show_sensitive_data_on_connection_error: true

Which was also problematic, but possibly a different issue?! Even though the "file:" prefix is a valid part of SQLite URI, it makes ecto_sqlite3 think the database doesn't exist:

[info] Running PhoenixAppWeb.Endpoint with cowboy 2.10.0 at 127.0.0.1:4000 (http)
[info] Access PhoenixAppWeb.Endpoint at http://localhost:4000
[error] #PID<0.497.0> running Phoenix.Endpoint.SyncCodeReloadPlug (connection #PID<0.496.0>, stream id 1) terminated
Server: localhost:4000 (http)
Request: GET /
** (exit) an exception was raised:
    ** (Phoenix.Ecto.StorageNotCreatedError) the storage is not created for repo: PhoenixApp.Repo. Try running `mix ecto.create` in the command line to create it
        (phoenix_ecto 4.4.2) lib/phoenix_ecto/check_repo_status.ex:49: Phoenix.Ecto.CheckRepoStatus.check_storage_up!/1
        (phoenix_ecto 4.4.2) lib/phoenix_ecto/check_repo_status.ex:32: anonymous fn/3 in Phoenix.Ecto.CheckRepoStatus.call/2

[warmwaffles]

@rhcarvalho I'll take a look at this soon, we should handle this.

We are also in an experimental phase changing how the connection is pooled.

• rw conns exqlite#256
• replace db_connection with rw lock exqlite#255

https://www.sqlite.org/forum/forumpost/c1004073ca76117f

This is an incredibly interesting conversation.

[rhcarvalho]

We are also in an experimental phase changing how the connection is pooled.

I didn't know about it, thanks for the pointers, exciting developments 🚀