State of SQLite support

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?

bubelov,

I’m thinking about moving from MySQL (MariaDB) to SQLite. I haven’t started yet, but @pmik76 seems to be running NC with SQLite without a problem:
https://help.nextcloud.com/t/nextcloud-and-sqlite/34304

@tflidd also commented on that thread regarding potential for file collisions.

Good luck!

buzz

Thanks for the link! Here is what the doc says about file collision issues:

https://docs.nextcloud.com/server/19/admin_manual/configuration_files/files_locking_transactional.html

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.

SQLite is only recommended for testing and very minimal instances:

https://docs.nextcloud.com/server/19/admin_manual/installation/system_requirements.html?highlight=sqlite#server

  • SQLite ( only recommended for testing and minimal-instances )

https://docs.nextcloud.com/server/19/admin_manual/configuration_server/occ_command.html?highlight=sqlite#database-conversion

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.

https://docs.nextcloud.com/server/19/admin_manual/installation/installation_wizard.html?highlight=sqlite#database-choice

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!

buzz

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.

Interesting. Are you aware of any examples?

Nextcloud app developer docs suggest that there must be a database abstraction layer between an app and the platform:

https://docs.nextcloud.com/server/stable/developer_manual/app/storage/migrations.html

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 =)