mirror of
https://github.com/advplyr/audiobookshelf.git
synced 2026-05-30 23:40:40 +02:00
[Enhancement]: PostgreSQL support #1340
Assignees
adam (Adam Melkus)
Clear assignees
adam (Adam Melkus)
No Assignees
Notifications
Due Date
No due date set.
Dependencies
No dependencies set.
Reference: starred/audiobookshelf#1340
Reference in New Issue
Block a user
Delete Branch "%!s()"
Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?
Originally created by @adocampo on GitHub (Sep 1, 2023).
Describe the feature/enhancement
It would be great to have PGSQL to speed up things and be more consistent. SQLite is fine for development, but it has many issues for production: it's slow and prone to corruption easily.
@advplyr commented on GitHub (Sep 1, 2023):
There have been a few discussions around this on Discord and #1584 #1202
Those were back when Abs was still using JSON files as a db. Basically we need an embedded database so that users aren't required to setup their own db. Once the new ORM (sequelize) is fully in place and the old data models get phased out we can work on supporting external databases like postgres.
Currently the migration to SQlite & sequelize is in-progress
@adocampo commented on GitHub (Sep 1, 2023):
Setting up a RDB is a breeze... I mean, if a user is able to manage docker to set up ABS, probably can set up a PGSQL o MYSQL docker... besides that, a docker-compose can handle it for the user itself, and leave it as transparent as is now. But I understand you need to phase out the current model, so I will patiently wait and cross my fingers hoping the current sqlite database won't get corrupted and all the hours I've spent organizing and inserting metadata for authors, collections, and other stuff will not lose in the meantime.
@advplyr commented on GitHub (Sep 1, 2023):
Make backups. Docker is not the only install method. I don't think requiring an external db is a good UX
@adocampo commented on GitHub (Sep 1, 2023):
Certainly I do backups always I have to work with a sqlite DB, but I don't even trust them, I faced in the past the backups weren't working either, even doing them with the official method.... anyway, my suggestion is NOT to force the user to use any DB, but let them select (like nextcloud or home-assistant does, for example, which by default uses SQLite, so the user can just jump that step, or configure the external database if they want speed and reliability).
Speaking of UX, using a proper DB engine will boost the UX :P
EDITED: just to make clear. I'm not complaining of the engine or any other piece of software selection, I think ABS is a wonderful software, and I have no words of how grateful am I for using it. All my respect for you, developers, and many thanks for working in this, you're awesome!
@FreedomBen commented on GitHub (Nov 10, 2023):
I manage Postgres databases and huge distributed web apps for my day job, and I think SQLite is a much, much better UX for the vast number of users who aren't going to scale horizontally. Having the option for postgres would be cool, but SQLite should stay the default. Just my opinion of course. Even for people needing to serve a lot of users, you can vertically scale incredibly far with a SQLite database, and backups are as simple as an rsync.
@adocampo commented on GitHub (Nov 13, 2023):
I can agree with all you say. Many people will find SQLite more than enough and probably won't use another engine and will be annoyed if they are forced to configure anything additional.
But I have a question regarding when you say UX. That means "User eXperience", and I can't see why a fastest SQL engine can degrade the user experience. Because PGSQL provides excellent performance thanks to its advanced indexing, query optimization/planning, parallelization, caching mechanisms, and multi-core scaling capabilities.
If the "U" in "UX" you're speaking is about the people that is setting up ABS, or the devs they are working developing it, then yes, it requires more job: it needs a working PGSQL and configure ABS to point to that server. And then they need some proper backup mechanisms rather than a simple rsync. But if that "U" means "end User" (which is what I think of when I see "UX" written), then, there's no contest: all users will prefer a page which loads and draws all the images way more fast. Especially when there are plenty of concurrently connected users.
I'm not a DBA, but I've solved a LOT of issues in the past with SQLite applications by migrating to a proper engine. And I told my devs coworkers SQLite is completely fine for development purposes, but not for production environments.
I will be more than happy if there is an option (not by default) to configure a PGSQL or even a MySQL besides using the default SQLite for those who want it.
@advplyr commented on GitHub (Nov 13, 2023):
User experience includes getting the software up and running.
@taxilian commented on GitHub (Jun 6, 2024):
Is there a place where efforts to finish that migration are being tracked? I'd love to be able to use mysql or pgsql (or mongodb for that matter) instead of needing to use the embedded database. It would provide a much better experience both for me and the users of my server ;-) since it would let me use existing infrastructure (which is already set up for backups, etc) and allow me to have a highly available setup so if I need to reboot a server I don't disconnect everyone.
@relrod commented on GitHub (Sep 2, 2024):
For me, not having postgres/mysql support is a blocker for getting the software up and running, so I can concretely say that my user experience is made worse by not having this.
My homelab, where I want to deploy this, is a kube cluster, and storage is backed by NFS. Since you explicitly tell people not to use a NAS with sqlite (which I agree with), then in order to deploy this I need some alternative approach.
I think the thing being glossed over here is that nobody is suggesting to drop sqlite support, but simply to consider adding some alternative that users can configure if they need it.
@advplyr commented on GitHub (Sep 2, 2024):
Just to clarify again on this, it has been the intention from the initial Sequelize ORM migration to support the option to use other db engines.
Not at all a priority, but is still a planned feature.
@xlanor commented on GitHub (Dec 1, 2024):
+, however, I just want to point out that you can use localstorage first and pin via node affinity. It's not ideal, but given that the sequelize migration is being actively worked on it might be a good holding approach.
Its what I am currently doing
@luis-echegaray commented on GitHub (Jan 9, 2025):
^^ currently doing as @xlanor described after seeing there was no postgres/mysql support. works really well but would switch to a full database given a chance :)
@wuast94 commented on GitHub (Jan 17, 2025):
i am running a docker swarm cluster backed by a cephfs storage and sqlite and network storage of any kind will break, its just a matter of time sadly. I dont understand all the UX topics in this thread becouse docker compose can simply mainstream setup and dev envs. but sure its an extra step to make more or less. and as other mentioned, nobody says drop sqlite, "just" add proper db as an option "advanced" users can use :)
But its nice to hear that it is on the roadmap and also i want to say thanks for this wonderfull piece of OSS <3
@HansenRene commented on GitHub (Feb 1, 2025):
+1 on this enhancement. Keep SQLLite default, it will be fine for many use cases, add postgre as an option for even mildly advanced users.
configuring a single postgresql db for ABS is 14 lines of text to docker compose.
@iconoclasthero commented on GitHub (Mar 7, 2025):
I'm also interested in migrating from sqlite to pgsql, though for me the latter is systemd/bare metal, not docker.
@mayzyo commented on GitHub (Mar 16, 2025):
+1 on pgsql support. Looks like an amazing OSS project and would love to try once it's available. Leaving a comment to track it.
@kristiandrucker commented on GitHub (May 3, 2025):
same here. I'm tracking down services in my homelab to move to a central PSQL server and this would greatly help
@aserper commented on GitHub (Aug 26, 2025):
+1 PSQL is the way to go
@Hideloop commented on GitHub (Oct 16, 2025):
+1 for external DB support
@telnetdoogie commented on GitHub (Dec 9, 2025):
another +1 from me.
@migol commented on GitHub (Jan 11, 2026):
+1 from me as well. SQLite makes it really hard to run ABS in Kubernetes, and running DB of network storage is REALLY slow.
@iconoclasthero commented on GitHub (Jan 12, 2026):
What's with @kusold and the thumbs down? lol (thums downs?)
It's not that bad a feature request.
@kusold commented on GitHub (Jan 12, 2026):
@iconoclasthero It's a feature request I support, and signified my support for by adding a thumbs up reaction on the top level issue along with the 38 other people. That is the common way of voting for GitHub issues that you find important.
The comments I gave a thumbs down to don't add anything meaningful to this discussion. When people add "Me too" or "+1" as comments, it emails everyone that is subscribed to this issue.