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.

[eyesnack]

Whoever decides take on this task, can we also consider supporting SQLite databases that can reside on NFS/SMB shares? The reasoning behind this is that it would facilitate configurations where grimmory/booklore data is already on a network share. (Right now grimmory already supports ebooks on a network share) For my specific use case I keep grimmory data (books) on a NAS NFS share that is easily backed up / snapshot. I would like to do the same for the database.

This would also maintain parity with the current existing model where we use a relational database like Maria DB that can be on a different VM/LXC/container/etc on the network.

Some data points:

• It would appear CalibreWebAutomated supports SQLite on network shares. See Deploying on Network Shares (NFS/SMB) on https://github.com/crocodilestick/Calibre-Web-Automated/blob/main/README.md
• Some additional documentation: See https://sqlite.org/useovernet.html

I'm not an expert but it seems like disabling Write-Ahead-Logging (WAL) is what would be required. WAL could be (conditionally) disabled for SQLite databases that are on a network share, and enabled (more performant) for configurations that keep it local. Maybe some of the experts can chime in on pitfalls and feasibility of this.

Ideally this could also be specified/configured via the docker compose or .env file at container creation time with a (separate?) environment variable, similar to how you can specify that your e-books are on a network share via DISK_TYPE=NETWORK. Although perhaps this variable would have to be renamed or made more granular for configurations where one might want to keep the SQLite database local, but keep the e-books on the network?

I would propose something like DATABASE_LOCATION=(LOCAL|NETWORK) and BOOK_LOCATION=(LOCAL|NETWORK)

[mmomjian]

In my experience with other projects, using a database (even sqlite) over a network connection contributes to significant database corruption and data loss. If a networked database is required, a "real" DB like Postgres should be deployed and connection made across the LAN to that DB residing on the host storage.

[Edo78]

I think this is worth approaching carefully, because PostgreSQL/SQLite support is probably more than just adding another JDBC driver.

The current migration model appears to be Flyway SQL migrations written primarily for MariaDB/MySQL. If the project keeps using vendor-specific Flyway SQL while supporting multiple databases, every future schema change would likely
need to be written and reviewed once per database backend.

That has a few long-term costs:

• contributors would need to understand MariaDB, PostgreSQL, and eventually SQLite DDL differences;
• every schema PR would need parallel migrations;
• migrations could drift between backends over time;
• CI would need to validate every backend for every schema change;
• even simple changes can become vendor-specific because of identity columns, ALTER COLUMN syntax, JSON types/functions, indexes, defaults, constraints, and timestamp behavior.

So while keeping Flyway SQL may be the smallest short-term change, it could make the project harder to maintain once more than one database is officially supported.

A cleaner long-term direction might be to treat this as a breaking schema change rather than trying to extend the existing MariaDB-centric migration history.

For example:

• close the current Flyway SQL migration stream;
• introduce a new clean schema baseline;
• replace Flyway SQL with declarative Liquibase changelogs as the single source of truth for future schema evolution;
• keep MariaDB supported, but stop making MariaDB-specific SQL the canonical schema definition;
• add PostgreSQL support against the same Liquibase-managed schema;
• potentially use the same approach later for experimental SQLite support, if that becomes desirable.

This would be a breaking change. Existing users would not be able to simply start the new version against the old database. A separate migration/export/import tool would likely be needed, even for users staying on MariaDB, because the
schema itself would have changed.

Conceptually that migration path could be:

old MariaDB/Flyway schema -> export normalized data -> import into new Liquibase-managed schema

That avoids trying to do a complex in-place SQL migration from the old MariaDB-specific schema to a new database-neutral schema.

This is obviously a larger change than simply adding PostgreSQL support, but it may be the more maintainable path if the goal is to support multiple database backends without making every future schema migration a multi-dialect Flyway
SQL task.

[fdw]

I don't think it's quite so dramatic. Let's split your points up:

The current migration model appears to be Flyway SQL migrations written primarily for MariaDB/MySQL.

This is very true and the main problem. One solution would be, similar to what you describe, to replace all of these with vendor-neutral SQL, but breaking all existing installations. Not ideal, but doable.
In the old discussion on BookLore, someone (I don't remember their handle unfortunately) also showed that it's possible to add vendor-specific migration scripts. So we could keep all the current scripts and add ones for Postgres and/or SQLite.

If the project keeps using vendor-specific Flyway SQL

Flyway doesn't force you into vendor-specific SQL; it is the same as Liquibase in that regard. So from now on, it'd be sensible to add only vendor-neutral SQL migrations. But no need to change Flyway for Liquibase as well.

[steveskarn]

This is the only thing I would love to see to switch to Grimmory. I dont want to setup another database & backup system for a single app, when all my other containers are using postgres already.

[webysther]

Doing now is better than in one year. For me calibre is always the better example even full text search use sqlite.

[Edo78]

The current migration model appears to be Flyway SQL migrations written primarily for MariaDB/MySQL.

This is very true and the main problem. One solution would be, similar to what you describe, to replace all of these with vendor-neutral SQL, but breaking all existing installations. Not ideal, but doable. In the old discussion on BookLore, someone (I don't remember their handle unfortunately) also showed that it's possible to add vendor-specific migration scripts. So we could keep all the current scripts and add ones for Postgres and/or SQLite.

I'm not saying that isn't doable but that, in my experience, asks contributor to know or learn multiple SQL dialects to support multiple DBs even for a single PR is going to drop significantly the amount of collaborations to the project. It's true that not every PR require a migration but the ones that does so are gonna be much more a PITA to write, check and maintain.

If the project keeps using vendor-specific Flyway SQL

Flyway doesn't force you into vendor-specific SQL; it is the same as Liquibase in that regard. So from now on, it'd be sensible to add only vendor-neutral SQL migrations. But no need to change Flyway for Liquibase as well.

On this I can concede the point I simply know Liquibase and I'm not really familiar with Flyway so it's my fault for answering before doing a proper reasearch.

Keeping flyway would made the transition of the codebase more linear but the actual DB schema must be transalted in something neutral (if we want to support multiple DBs) as a starting point. A pretty big breaking change for the whole userbase.

[balazs-szucs]

Hi,

Apologies for not keeping up with issues. As far as the persistence/data layer goes, we are (finally) vendor-neutral there. It was not an easy process, but so far no one has complained about ReadingSessions, which was probably the biggest offender. It did a bunch of datetime manipulation via SQL using MySQL-specific TZ queries.

@Edo78, I think we are essentially speaking the same language, and I couldn't agree more. Right now, the official stance of the team is that we put together a smaller, viable proof-of-concept for both Liquibase and the "adding more migrations" approach, and see how the two compare. That said, i am personally leaning more towards writing migrations that convert the MySQL-specific statements into general, vendor-neutral ones.

With Liquibase, there are also some license concerns. The project migrated from Apache 2.0 to FSL, which is obviously their right, but it does make me somewhat anxious that they are heading down the same path as Redis. Liquibase's move is admittedly milder, given the two-year reversion clause, but that clause hasn't kicked in yet for the current version (AFAIK), so I'd rather not take the risk unnecessarily. Obviously, if Liquibase is genuinely the better technical choice long-term, I am more than willing to concede on the licensing concern and move forward with it.

On the ETA/Deadline, i am afraid i cannot give out any guarantees, however large portion of the work, has been already done, so at this point this is actually just conversation on "Which migration is better" and besides that, there are no blockers, meanwhile in the old project, the old maintainer was right that was a huge amount work to convert those queries... It was more or less 1 whole week of work for me, and other people helped out, so wasn't even doing it alone.

Hope that clears up the air about this issue.

[balazs-szucs]

Forgot to say, that many thanks to anybody contributed to the technical conversation with ideas/arguments, these stuff are generally, very helpful for me, and for us.

[balazs-szucs]

Whoever decides take on this task, can we also consider supporting SQLite databases that can reside on NFS/SMB shares? The reasoning behind this is that it would facilitate configurations where grimmory/booklore data is already on a network share. (Right now grimmory already supports ebooks on a network share) For my specific use case I keep grimmory data (books) on a NAS NFS share that is easily backed up / snapshot. I would like to do the same for the database.

I also agree with the comment after this, but consider that the team has grown from 1 maintainer to 6 members, and we are still unable to adequately keep up with support, bug fixes, and similar tasks. We cannot, and most likely will not, take on any DB-related tasks that would considerably impact deployment stability, and by extension, our triage, support, and related backlog.

This is also why I am somewhat averse to explicitly supporting SQLite. While some commenters above consider it adequate, based on what I have seen in bug trackers of similar self-hosted applications, it is far from problem-free in practice. Komga is a good example, as they have documented users experiencing SQLite-related bottlenecks and filesystem compatibility issues over the years. SQLite's tendency to interact poorly with certain filesystem configurations leads to all sorts of hard-to-diagnose issues, which is exactly the kind of support burden we are trying to avoid. We do not have the bandwidth for that.