support for storing in sqlite3 database

[khimaros]

• [x] I have checked the existing issues to avoid duplicates
• [x] I have redacted any info hashes and content metadata from any logs or screenshots attached to this issue

Is your feature request related to a problem? Please describe

deploying an maintaining a PG server is troublesome overhead

Describe the solution you'd like

add support for utilizing an sqlite3 database

[mgdigital]

I just don't think the performance would be good enough for large indexes, though I appreciate it would be convenient in theory. Most apps using a sqlite DB are not dealing with millions of rows. Bitmagnet makes use of many Postgres features not available in sqlite (most significantly tsvectors for search), so it would be difficult or impossible to achieve feature parity, and the maintenance overhead of 2 database backends would be very big.

I'm leaving open for discussion (for now) but it's unlikely to happen. I am considering integrating something like https://github.com/fergusstrange/embedded-postgres that would set up the Postgres DB automatically.

[jxs]

Hi, and thanks for considering this. If I understand correctly your solution using embedded-postgres also achieves the same purpose as sqlite in that we can release a bitmagnet container not dependable on a separate postgres container right? If so it works for me, I can even try to submit a PR addressing this (if you have patience to guide me as my golang skills are non existent)

[mgdigital]

Hi, and thanks for considering this. If I understand correctly your solution using embedded-postgres also achieves the same purpose as sqlite in that we can release a bitmagnet container not dependable on a separate postgres container right?

Hi, that's correct although I haven't tried embedded-postgres so not sure what other issues it might throw up. If you're using a container with docker-compose you're probably best sticking with a separate postgres container, I can't really see much of an advantage of embedded postgres in this case. I see this primarily facilitating native install packages for Mac/Windows/linux flatpak etc without separate dependencies, and I expect a separate postgres would remain the default configuration otherwise.

If so it works for me, I can even try to submit a PR addressing this (if you have patience to guide me as my golang skills are non existent)

Thanks, I'd say it's a non-trivial feature to implement- if you'd still like to give it a go I'll assist where I can.