search

u-wave / core

The core library driving üWave, the self-hosted collaborative listening platform.
MIT License
31 stars 12 forks source link

Change the database #549

Open goto-bus-stop opened 1 year ago

goto-bus-stop commented 1 year ago

We have relational data and I've had to spend a lot of time making Mongo do the things that we need. It's a bad fit for this type of application but it's always seemed more trouble than it's worth to change. However there are more issues than just fit for purpose. Mongo requires an annoying amount of maintenance to keep up-to-date, which we've needed to do because they are still adding stuff that lets you simulate relational models every now and then. There also aren't many affordable managed options for small applications.

If we switch to anything SQL-based, we get many more guarantees, and probably much better performance too. As it is we only have a few 100K medias at most on wlk.yt but loading 1 page of a playlist can already take 500ms because of all the relations we need to look up using fancy mongodb pipelines and whatnot. We could also improve it by changing the data model (we really didn't design a data model for mongo back in the day), but at that point we are already doing a complicated migration, and we might as well make a similar-effort migration for much larger gains.

Maybe 2023 is the year for this. I would like to avoid using an ORM. I think it would be nice if we could even work with sqlite (though i would probably recommend postgres in docs).

goto-bus-stop commented 1 year ago

https://github.com/koskimas/kysely

goto-bus-stop commented 1 year ago

or https://github.com/drizzle-team/drizzle-orm

goto-bus-stop commented 5 months ago

I'm leaning more towards having sqlite as the standard option. My experience shows that you can decently well run an app on sqlite with tables containing millions of rows--realistically, this will never happen for üWave. And the operational work involved in setting up a sqlite-based app is just so much better than node.js + mongodb + redis. (This implies that I'd also move away from redis :). ) With kysely you probably still could use postgres if you wanted to.

The issue with sqlite in a node.js app is that it blocks the main thread, unless there's a thread pool with connections to use.

fawaf commented 5 months ago

lol, didn't realize this issue is a year old. sqlite is a nice "lightweight" option for sure.

goto-bus-stop commented 5 months ago

time flies when you're getting old 😭

goto-bus-stop commented 4 months ago

Node.js now has an experimental built-in sqlite module: https://nodejs.org/api/sqlite.html