I’m happy to report that phx.new support for the sqlite3 adapter is now merged into Phoenix!

Starting with the next Phoenix release, you will now be able to generate a sqlite3 repo for your Phoenix application by specifying the --database sqlite3 flag in your call to phx.new.

@wanton7 and @tj0 I recent went through and fixed the memory leak issue, or rather the perceived leak issue.

I could not reproduce the leak, but, after reading through mailing lists and what not, the issue seemed to stem from the garbage collector not destructing the prepared statements in a timely manner. So instead, when ecto closes the connection we handle it by also releasing the underlying resource (the prepared statement) and then the destructor will run later and free the last remaining bytes that was a pointer to the prepared statement.

Overall this should suffice and would love to see if anyone who was having issues with memory growing like crazy during high load to give the latest release a try.

My upcoming library will hide that from you because it will do pooling on the Rust side – the pool size will be configurable either as a project-wide config or when opening the DB.

I’ve done extensive tests (although I am not sure they are very scientific just yet). Opening 50 separate sqlite3 databases each with 20 “connections” (as we know, they are not really connections but file descriptors) and the test program barely consumed a single digit MBs of RAM.

The main challenge for such scenarios would be environments with severely reduced file descriptor limit (part of all Linux installs and a good chunk of the container cloud hosting platforms) so one has to be very judicious in picking a pool size with sqlite3. I suggest you put a pool size equaling the average expected amount of parallel tasks that will read from your DB. But honestly, for most projects a size of 2-3 should be quite fine – sqlite3 is extremely fast so taking a “connection” from the pool even if all “connections” are busy should be a matter of just a few milliseconds.

(Sorry that I haven’t pinged you to help me with the library yet; I’ll be at a lot of doctors in the next 1-2 months so I am keeping quiet since I barely have any free time.)

It’s quite interesting and I’ve used it once – it works just fine (which is something I find myself automatically taking for granted in the Elixir libraries! ).

Nice!

We discussed it at one point and decided against it in the ecto_sqlite3 code repository itself (at the time), primarily so we can more easily detect issues around handling of database connections and transaction errors. If we had each test in our code repository be async via this mechanism, we would likely have missed some issues when we were in the midst of ironing out some trickier edge cases around inter-connection issues (e.g., not rolling back a failed write transaction correctly, locking the database for the next test!)

However, for downstream uses, I don’t see any reason why this cannot be supported, eventually. I think at the very least having some documentation on the adapter for this approach could be very useful, if you would like to open a PR adding some

Some other thoughts:

1. We may be able to remove the need for the structure dump existing if we update the exqlite driver to expose the SQLite3 de/serialization APIs.
2. We may be able to eliminate the need for this extra code in the MyApp.Repo by creating a new SQLite3 flavored sandbox, Ecto.Adapters.SQLite3.Sandbox that does this (or the de/serialization approach) under the hood, forking the connection into a memory-version. This would allow downstream users to choose their sandbox strategy by specifying either Ecto.Adapters.SQL.Sandbox or Ecto.Adapters.SQLite3.Sandbox for the transaction-isolation or connection-isolation approach, respectively.

For this talk of data resiliency, SQLite3 itself provides pretty great guarantees as mentioned. Of course, there is always the possibility of losing the entire host machine in some disaster, and thus the single-node aspect of SQLite3 can work against you. Of course, this applies equally to simple Postgres setups as well.

One library I am eagerly following GitHub - benbjohnson/litestream: Streaming S3 replication for SQLite., which can more or less tail the WAL into S3 or any other object storage, allowing one to recover nearly all data in the case of a total node failure, only losing a couple of seconds worth of data at most. This is much better than hourly or daily backups.

I haven’t played with it yet, but it seems like it is as simple as setting up a systemd service with the S3 storage config and the location of the local database file and it “just works”.