Issues with SQLite and network shares

[onedr0p]

Hi 👋🏼

I saw that you originally had Postgres and removed it in favor of SQLite, which makes sense to have no external deps for people using your app. However I am curious if you are interested in adding multiple database support? At least being able to support SQLite and Postgres only.

Using postgres gives from flexibility over SQLite which has a long a sordid past especially with trying to store the database on a network file-system.

Sonarr/Sonarr#1886
https://www.sqlite.org/useovernet.html

[kieraneglin]

Hey there! Thank you for another detailed report - makes it much easier for me to get up to speed!

As you alluded to, I switched away from PG for two main reasons:

• Simplicity. I use unraid and it doesn't have official support for Docker compose yet (iirc: their container management method predates compose. Why they haven't since added support is a mystery, but that's how it goes with legacy decisions). Practically speaking, this means that I have to be able to create one Docker container that encapsulates all functionality of the app and the simpler, the better
• Portability. In an unraid-like setup, sqlite makes it very simple to backup your full app configuration for the purposes of disaster recovery or moving to a new machine. Of course that's all doable with PG, but I would say sqlite has the edge here

I'll be perfectly honest and say I'm not planning on introducing support for external databases. The maintenance burden of having to test and confirm every new feature on multiple database platforms is a lot for one dev to handle for a project I work on in my spare time. Even if the ORM I'm using (Ecto) was able to perfectly translate the queries between database engines, there are still some non-trivial indexes, triggers, and search-related queries that would all require an alternate version.

That said, I'm open to other solutions!

• First and foremost, I would recommend storing the configuration folder on a local directory if at all possible. I know that's kind of a non-solution, but I do my best to compress everything and keep the footprint of config as small as possible while still being useful
• I understand that isn't always possible, so would simply changing the journaling behavior make a difference? It seems like the WAL specifically is the problem and Ecto natively supports a host of journaling options (link). Since this app is self-hosted, we can be a little more lenient with performance if it improves stability. Can you confirm that my understanding here is correct? Edit: specifically, I think I would need to set the journaling mode to :delete

Thanks again!

[onedr0p]

Agreed with all your points 👍🏼

There's no need to rush into implementing postgres but I bet some people might find it useful if given the option. I would probably use it because then I could make pinchflat "stateless", meaning I can deploy the pinchflat container without any "config" volumes attached to it, only the data volume. My backup/restore process would already be set too because I use barman.

[kieraneglin]

As of #145 you can now set the following env var to change your SQLite journal mode: JOURNAL_MODE=delete. This should alleviate SQLite issues on network-attached shares.