[Feature] Postges backend

[brunnels]

Is your feature request related to a problem? Please describe.
No

Describe the solution you'd like
I would like to host this in my kubernetes cluster that already has a cloudnative postgres instance available. Can you please add postgres to the list of available backend databases?

Describe alternatives you've considered
N/A

Additional context
I don't think this should be a big ask since you're using fastapi, SQLAlchemy, and alembic which all support postgres very well.

[JVKdouk]

Just wanted to add a little bit of reasoning for this feature. In version 3.0 of Romm, SQLite support has been dropped, making MariaDB the only supported Database. I have not yet updated my Romm instance specifically because of this. I am currently self-hosting my personal Romm instance on an Orange PI 5, and there I have PSQL running in a container to serve my other project and services. While I could have two database instances running simultaneously, I do not want to manage two database engines at the same time, since I believe the maintenance, fine-tuning, and overall resource consumption make it not worth it to have two database engines running at the same time. We all how database fine-tuning is a full time job by itself.

Since Romm uses an ORM behind the scenes (please correct me if I'm wrong), I believe adding support to PSQL should be straightforward. Would love to work on this feature, but the time I have to invest right now is extremely limited, so please let me know if someone else can work on this task!

Also let me know if my reasoning for PSQL support above is incorrect, would love to debate this!

[gantoine]

There's an in-progress branch for this work, though it's currently stuck midway in migrations (something about the primary key syntax): https://github.com/rommapp/romm/tree/postgres-db

[brunnels]

I don't know how much movement this has had but I do fastapi and and alembic for my real job so would be happy to help out now that I'm getting some other projects wrapped up.

[gantoine]

@brunnels I haven't made any progress beyond what's in that branch, but we have been discussing database support at a higher-level in the discord.

On postgres support, the idea of support postgres as a backend seems great in theory, however the added complexity of supporting 2 DB systems may not be worth the tradeoff. I haven't seen many other self-hosted tools that support more then 2 (usually mariadb/postgres + something built-in like sqlite) engines.

On the topic of sqlite, we've mused about bringing back support after it was dropped in 3.0.0. One of the biggest issues non-technical users face on first setup is getting romm to connect to mariadb (bad netowrk config, wrong creds, etc.). If we supported sqlite again, we could have it default to it when mariadb isn't setup (no env variables set).

On on the top of build-in databases, recently we discussed embedding mariadb into the romm image, like we do for redis, and offering two different docker images (full and slim).

Would love to hear your thoughts on any of these!

[JVKdouk]

When first installing Romm on my home server, I desperately wanted a PSQL version so that I would not have two database drivers running simultaneously and using my limited resources, so I decided to go for SQLite in order to avoid installing MariaDB. However, with SQLite being dropped in the latest version, I have not yet been able to migrate to 3.0.0, despite really wanting to.

That said, I also understand how maintaining multiple database options could be an issue down the line, adding overhead to testing and feature proposals in the future. However, I also believe that supporting a single database driver is a little bit overkill, with SQLite looking like a suitable middle ground to me.

[gantoine]

I desperately wanted a PSQL version so that I would not have two database drivers running simultaneously

What kind of hardware are you running? I currently have mariadb, postgres, 2 redis instances, elastic and couchdb running on the same system, and collectively they use less CPU and memory then my torrent client.

SQLite looking like a suitable middle ground to me

SQLite is great, but support was dropped due to its inflexibility migrating primary and foreign keys. We can probably get around this by reworking how we relate tables to each other, but that's ongoing work that we don't have the bandwidth to take on.

[davralin]

I'm not going to chime in on wheter or not SQLite is a good idea or not, but from my rather subjective observations, it seems like mariadb is loosing traction, and postgresql is way more used.

the *arr-stack implemented postgresql-support, they have not implemented mariadb, in the k8s-at-home-community, there is a clear overweight of people using postgresql over mariadb/mysql. From my personal, not-going-to-help-with-the-conversion, and i'm-not-a-dba-view, it seems postgresql is a better choice than mariadb.

[brunnels]

IMHO it's sqlalchemy and alembic. It's the whole point of an orm, and these 2 libraries in particular, to be relational database agnostic. If it's supported by those 2 tools it should be as trivial as using a different connection string. If not, then you should re-think how you're implementing them and refactor accordingly. If you're implementing them correctly there's no additional overhead to support any database the libraries support.

[gantoine]

you should re-think how you're implementing them and refactor accordingly

Yeah it's likely we're doing something wrong in our migrations, seeing as they fail when run against a postgres backend (like on first run). There's work that has to be done to "fix" them, or fix the underlying issue that's causing alembic to generate migrations that don't run with postgres.

[adamantike]

On top of the comments you have shared here, I would like to also ask, how not having PostgreSQL support affects you regarding the use of RomM? Have you gone the extra mile and ran a MariaDB service just because of RomM? Are you delaying its use and waiting for this to be implemented just to start using RomM?

I don't agree with the "whole point" of an ORM being to support database agnostic applications. It's not the focus of SQLAlchemy, Alembic, or the reason these libraries are selected in the first place by a project manager (what would you choose, otherwise?)

Being based on those libraries, RomM could already have "unofficial" PostgreSQL support, provided on a best-effort basis, if contributors are willing to refactor some of the existing migrations that are currently breaking for non-MariaDB engines.

But let's be honest, being database-agnostic is not a zero-effort task, and doesn't come for free just because of using SQLAlchemy and Alembic (that's why these libraries provide specific add-ons and utils for each engine). That's the whole point of asking for reasons here, and try to determine benefits vs effort. It requires constant support for users that will come asking for breaking setups from different engines; it requires testing each new version against multiple engines and engine versions; it forces new changes to always find the "lowest common denominator" SQL that is compatible with all supported engines.

[davralin]

On top of the comments you have shared here, I would like to also ask, how not having PostgreSQL support affects you regarding the use of RomM? Have you gone the extra mile and ran a MariaDB service just because of RomM? Are you delaying its use and waiting for this to be implemented just to start using RomM?

I delayed it's use because I had to spin up mariadb, I wouldn't have if it was sqlite.
I would find an embedded mariadb "icky", but that's the developers choice, and there are plenty other containers that are created "wrong", the builtin-redis is also not really following the best practice, but that's more of a "you-problem", than a "me-problem" :)

I did find that I needed to organize my library better, and I did implement this thing on a temporary basis, just to organize it.
I'm probably just going to do it as a one-off-thing, and then remove the installation afterwards, that's because of mariadb.

For me it's more of a operational cost-thing, I can easily backup sqlite, I have good things in place to backup postgresql - I have nothing for mariadb, and this would be the only thing. The first thing is never free to implement.

Being based on those libraries, RomM could already have "unofficial" PostgreSQL support, provided on a best-effort basis, if contributors are willing to refactor some of the existing migrations that are currently breaking for non-MariaDB engines.

Going off of my experience with k8s-at-home, one can use the excellent search over at kubesearch.dev
There's 127 installations of radarr, of those 28 have configured the value controllers.radarr.initContainers.init-db.image.repository, which uses a postgres-init-container that initializes a postgres-database.

I do use postgres with radarr, I have not configured that value, adding one more with likely others too - so at least 22% of that community are using the "less supported/advanced feature" of postgresql.

[alexanderccc]

I'm also interested in this. Recently found the project and would want to give it a try.

I'm running everything in Kubernetes and cloudnative-pg has been a real game changer in terms of managing Postgres databases within the cluster.

Having the option to use Postgres would be great!

[harryjph]

I just spun up a server with Postgres enabled, I uploaded one ROM and it didn't match. When I click "Manual Match", I get the following error:

 (psycopg.errors.UndefinedFunction) operator does not exist: integer = character varying LINE 3: WHERE roms.id = $1::VARCHAR ^ HINT: No operator matches the given name and argument types. You might need to add explicit type casts. [SQL: SELECT roms.id, roms.igdb_id, roms.sgdb_id, roms.moby_id, roms.file_name, roms.file_name_no_tags, roms.file_name_no_ext, roms.file_extension, roms.file_path, roms.file_size_bytes, roms.name, roms.slug, roms.summary, roms.igdb_metadata, roms.moby_metadata, roms.path_cover_s, roms.path_cover_l, roms.url_cover, roms.revision, roms.regions, roms.languages, roms.tags, roms.path_screenshots, roms.url_screenshots, roms.multi, roms.files, roms.crc_hash, roms.md5_hash, roms.sha1_hash, roms.platform_id, roms.created_at, roms.updated_at FROM roms WHERE roms.id = %(id_1)s::VARCHAR LIMIT %(param_1)s::INTEGER] [parameters: {'id_1': '1', 'param_1': 1}] (Background on this error at: https://sqlalche.me/e/20/f405)

[gantoine]

@harryjph did you build your own image? postgres was just merged in an none of the images on docker hub include it...

[harryjph]

Sorry, I should have mentioned that I'm using the rommapp/romm:3.7.0-beta.1 image from docker hub (specifically becuase I wanted to run against postgres). I think this one includes postgres support, it accepts the value postgresql for ROMM_DB_DRIVER. Most functionality works, I was able to initialize the DB (migrations ran OK), create an account, and upload a ROM, but I ran into this error trying to Manual Match the ROM

[adamantike]

@harryjph, thanks for testing and reporting the issue. #1420 will solve it!