search

sysadminsmedia / homebox

A continuation of HomeBox the inventory and organization system built for the Home User
https://homebox.software
GNU Affero General Public License v3.0
1.17k stars 62 forks source link

Support multiple database types #2

Open tankerkiller125 opened 5 months ago

tankerkiller125 commented 5 months ago

What is the problem you are trying to solve with this feature?

This is more of a long term goal, but we should support more than just SQLite as a database option.

PostgreSQL, MySQL, and SQLite support would most likely cover the most options possible.

What is the solution you are proposing?

Long term find a go ORM that can support the 3 database types and convert existing code over.

Progress on this can be tracked in the mk/vnext-multi-db branch.

tankerkiller125 commented 5 months ago

Doing some further digging into this, it should already be mostly possible with minimal code changes. Will need to add testing and what not though. And validate against different types of database engines.

userbradley commented 5 months ago

I'm happy to write documentation for this once implemented, I'm terrible at programming so it's best for everyone I avoid it

tankerkiller125 commented 4 months ago

Started digging into this feature and it's MUCH more complicated than I originally thought it would be, partially because of the fact that the ORM and the Migrator are apparently two entirely different libraries that take in SQL connection strings differently and other issues.

Still working on this, but it's going to take longer than I had hoped.

snesbittsea commented 3 months ago

A question I would ask is the existing data model in good enough shape to warrant the effort to support multiple databases? Or should the multi db support be part of a review and redesign of the data model?

I haven't looked at the code or db design, but my usage today suggested the trifecta of item, location and label seemed awkward - not very well defined with some interesting and potentially troublesome overlaps and ambiguities and inherent inefficiencies. I most certainly could be wrong, but it didn't strike me as a robust foundation.

mcarbonne commented 2 months ago

Maybe have a look at https://github.com/go-gormigrate/gormigrate (combined with https://gorm.io/). Note: I've never used it, but I've seen it in another project and it seemed relatively convenient.

tankerkiller125 commented 2 months ago

Currently on our list libraries to compare includes the goorm you linked, as well as https://xorm.io/ and others I think I'm currently leaning more towards goorm (it seems more polished and is much more similar to Laravel's Eloquent which I'm more used to using). The biggest thing is making sure the existing databases continue to work after the migration to the new ORM.

Regardless this is going to be a very big undertaking to complete, we're working on building out a roadmap we hope to share with everyone soon so that everyone knows where we stand development wise and what the plans are for the project longer term.

adiov commented 2 months ago

I understand this is a long term goal, but could you please expand a bit more on why this is needed?

From my point of view, SQLite is more than sufficient for this use case. Popular open source software like Home Assistant have significantly more complex data structure, hundreds of thousands of records in a typical install, and a user base orders of magnitude larger than Homebox, yet they've been managing very well with SQLite as the primary database engine. They do have the option for changing engines, but only a very small minority of users take it.

In my opinion, this is a feature that will be useful for an exceedingly small number of users of Homebox. Don't get me wrong, I love having more options, but software development is about prioritization and making difficult resource allocation decisions. The time is probably better spent on enhancing Homebox for the 99% of users.

tankerkiller125 commented 2 months ago

We plan to review the existing database schema, optimizations, and relationships regardless, the idea is that while we're there doing that we might want to also invest some time into supporting multiple database types. Allowing us to provide a comprehensive solution that works for more people.

In addition, while Sqlite does do well in many open source projects and with thousands of records in complex data structures, the one major area is significantly falls flat is in high availability installs such as Kubernetes, and in general any high availability install. While these installs likely aren't a majority, there are still a significant number of users who would like to have a properly functioning HA k8s install. And many other users who would like to be able to backup one sole source of databases instead of many different containers for data.

userbradley commented 2 months ago

Chiming in to add I am a user who would run this in k8's with a central database backup script that doesn't need me to update a yanl file, so for me sqlite is a deal breaker (yes I know, open source don't like it go away haha!)

mcarbonne commented 2 months ago

While digging into the code, I noticed an ORM was already in place (ent) combined with atlas for migrations. Isn't it enough ? It seems to support most major databases.

tankerkiller125 commented 2 months ago

@mcarbonne We are still reviewing if switching ORM is really needed. Something note though is that ent has a different connection string format than atlas, which makes things a bit complicated in terms of making it easy to use for end users. We would either need to choose a format, break it down into pieces, and rebuild it for whatever the other system is, or have the user enter the same information twice slightly differently.

This was the main issue I ran into when I was trying to create a PR for this feature a few months ago.

gclawes commented 1 month ago

I understand this is a long term goal, but could you please expand a bit more on why this is needed?

From my point of view, SQLite is more than sufficient for this use case. Popular open source software like Home Assistant have significantly more complex data structure, hundreds of thousands of records in a typical install, and a user base orders of magnitude larger than Homebox, yet they've been managing very well with SQLite as the primary database engine. They do have the option for changing engines, but only a very small minority of users take it.

Home assistant is typically run on embedded hardware like a Raspberry Pi or Home Assistant Yellow, where local storage is a given.

In containerized environments that is often far less of a given, especially in Kubernetes. The most popular form of network storage in homelab-style k8s clusters is probably NFS, which is very unfriendly to sqlite. Apps like sonarr/radarr/etc have added the option to use PostgreSQL recently and in my experience that's been a vast improvement for kubernetes environments, as the DB can be placed somewhere separate with durable storage.

It would also be helpful for cloud deploys where things like RDB/CloudSQL are available.

tankerkiller125 commented 3 weeks ago

This feature is almost implemented in the PR that's linked, I just need to finish up some migration stuff, and then do some hardcore testing and validation.

MySQL support will not be coming with this, I've been running around in circles with it for hours, and simply cannot sort it. If someone else wants to take a crack now that framework will exist, they are more than welcome to do so.

tankerkiller125 commented 2 weeks ago

This feature has been merged into the vnext branch and is working, however there are still some bugs, so I'm leaving this issue open as the parent for any bugs we discover along the way.