Postgres/SQLite Support

[afairgiant]

What's Your Idea?

Have support for postgres database instead of Mariadb or just as another option

Why Would This Be Helpful?

Lots of people already run postgres databases and would like to be able to connect to an existing instance of postgres instead of spinning up mariadb.

Anything Else? (Optional)

No response

Want to Help Out?

Yes! I'd love to help implement this

Have You Considered Any Alternatives? (Optional)

No response

Before Submitting

• I've searched existing issues and confirmed this feature hasn't been requested yet

[bjw-s]

I would argue maybe even sqlite could be sufficient for an application like this. That would simplify deployment a lot as well.

[afairgiant]

The sqlites problem is that there are a lot of concurrent writes designed into the app and sqlite serializes all writes through a single writer meaning operations would queue up and possibly time out with SQLITE_BUSY. Based on what I've seen when looking through the code for the actions it would affect library scanning, book processing, metadata fetching, kobo sync etc since those are all designed to be parallel background tasks. So if any of those are running nothing else could be done on the app.

This also would affect multi-user since multiple users wouldn't be able to read simultaneously since saving progress would have to fight over who is writing on every save.

So sqlite is possible but I think it would hamper a lot of the app functionality.

Just my 2cents :)

[armond-avanes]

Although there might be some technical challenges for supporting SQLite but I believe that it's totally worth it! For self-hosted applications like this with very limited amount of load/users (and most of the time just one user!), it makes perfect sense to support and even change the default to SQLite.

With apps like HomeAssistant which are usually dealing with at least hundreds of MBs of data (if not GBs) and support multiple users and much higher loads, and their default/recommended database is SQLite, I don't think it's too far off for Grimmory.

[epetousis]

I'm not even really sure what was blocking this apart from the original maintainer's stubbornness. Isn't it just a matter of allowing a user to enable the PostgreSQL or SQLite JDBC driver? The code already uses Hibernate...

EDIT: wrote this before seeing that the database migrations are all in plain SQL. Not exactly a trivial change, although booklore-multidb seems to have hacked in Liquibase just fine.

[airlie]

The BookLore Multi-DB repo may be helpful to look at. It has both PostgreSQL and SQLite support, although the README says it isn't tested with MariaDB.
https://github.com/binarymelon/booklore-multidb

[afairgiant]

I'm not even really sure what was blocking this apart from the maintainer's stubbornness. Isn't it just a matter of allowing a user to enable the PostgreSQL or SQLite JDBC driver? The code already uses Hibernate...

There is a lot of mariadb/sql specific code that needs to be updated to work with postgres and sqlite. The db migrations also probably need to get updated.

[afairgiant]

Although there might be some technical challenges for supporting SQLite but I believe that it's totally worth it! For self-hosted applications like this with very limited amount of load/users (and most of the time just one user!), it makes perfect sense to support and even change the default to SQLite.

With apps like HomeAssistant which are usually dealing with at least hundreds of MBs of data (if not GBs) and support multiple users and much higher loads, and their default/recommended database is SQLite, I don't think it's too far off for Grimmory.

Fair point. I actually did not know HomeAssistant used SQLite. Learned something new today. If HomeAssistant can do it, it should be doable like you said, just need to figure it out :)

[failshell]

if the idea is to support SQLite, then libSql should be considered as well.