I’m relatively new to Nextcloud and I self-host it from a Raspberry Pi 4. It’s not the fastest hardware in the world but it gives me enough performance for my home-use workloads and occasional public shares.
Recently, I was migrating from Raspbian to Ubuntu 20.04 and since I use a separate SSD with self-contained Nextcloud deployment (Docker Compose file and all of the data), I was hoping to just plug in my SSD and do a “docker-compose up -d” and that would be it. Well, it mostly worked but the Postgres container failed because Raspbian is 32 bit but my Ubuntu installation is 64 bit. Of course, dumping the database and restoring from that dump on a new machine worked like a charm but it makes me thinking that I wouldn’t have to do this extra step if I used SQLite and do I really need all of that added complexity.
The problem is, I can’t find any reliable and consistent information on the state of SQLite support in Nextcloud. I’m pretty sure that SQLite by itself can handle a lot more than a few users so it couldn’t be a bottleneck here. What I’m not sure about is how well Nextcloud supports SQLite and what are it’s future prospects on that platform? I searched this forum as well as GitHub and I saw a few pretty disturbing comments where people say that SQLite isn’t reliable and its usage often leads to file locking issues. Is it still true?
File locking is enabled by default, using the database locking backend. This places a significant load on your database.
It looks like most of the necessary protections are enabled by default. The documentation doesn’t say anything about SQLite in particular so it gives me an impression that SQLite should be as good as any other database backend in terms of sync reliability.
Since there are no clarifications on that except occasional complaints (which might be due to misconfigured instances), I guess the only way to find out if Nextcloud works with SQLite is to run it for a few months. I’ll try that.
The SQLite database is good for testing, and for Nextcloud servers with small single-user workloads that do not use sync clients, but production servers with multiple users should use MariaDB, MySQL, or PostgreSQL. You can use occ to convert from SQLite to one of these other databases.
SQLite is the default database for Nextcloud Server and it is good only for testing and lightweight single-user setups without client synchronization. Supported databases are MySQL, MariaDB, Oracle 11g, and PostgreSQL, and we recommend MySQL/MariaDB.
Those mentions are relevant but unfortunately they leave a lot of space for interpretation. Minimal instance is a vague term. I consider my self-hosted Nextcloud with one concurrent user a minimal instance, am I good to go with SQLite? This “only for testing” thing is particularly suspicions, does it imply that Nextcloud is expected to be less reliable with SQLite?
It’s not clear at all if those mentions warn about scalability or correctness issues (or both). If it’s only about scalability/performance, then it’s not an issue at all. If it’s about correctness, then it’s a deal breaker.
I concur with bubelov’s observation regarding the term “minimal instance.”
I run the server on my LAN where I’m the sole user. I assume this is a minimal instance. Based on the documentation, it appears that an instance with more than one user is too much for SQLite. Ultimately, I suppose it depends on how the server is used.
@bubelov: Please update us with your experience using NC with SQLite!
Some apps don’t support or work with SQLite. You can run into trouble if you try to use them with this kind of database. Minimal means minimal, literally.
That makes sense, no one wants any of the popular apps to be Postgres-only or MariaDB-only. It seems like SQLite should work out of the box with zero effort from the app developers, unless they try to circumvent the database abstraction layer and do something fancy =)
@buzz I’ve been running NC instance based on SQLite for many months now and it works like a charm. Nextcloud apps aren’t tied to particular database and if they do, well, it’s better to find better apps. I didn’t notice any performance issues too, which makes sense, because usually database isn’t a bottleneck, PHP is. SQLite can be even faster on home instances because it avoids network overhead. Needless to say, backing up SQLite is much easier and less error prone, which makes it a perfect choice for home instances.
I have Home Assistant for several years already. It collects data from dozens of sensors. As DB it uses SQLite and I don’t see any issue with it. The size of the DB file is around 250 MB already.
Therefore it should be totally fine to use SQLite with NextCloud for personal use.