SQLite support

[bemyak]

By default, Nextcloud uses SQLite as its database. It has two benefits:

• It's the only DB backend that allows you to switch to a different one later
• It's easy to put the SQLite file into an arbitrary directory

The last point is especially relevant if you're hosting in a cloud. With SQLite, you can plug in a "block volume", mount it to /srv, and configure datadirectory => '/srv'. Doing so is a good idea regardless of the DB configuration.

With MariaDB or PostgreSQL, you have two options:

1. Change their configurations to store data in the mount point
2. Use managed databases

Both options require additional setup and are more error-prone.

So, I would go as far as suggesting SQLite be the default database backend. It's fit for individuals, and for more advanced use cases, one can always switch to a DB of their choice.

[aalaesar]

Hello there ! Thank you @bemyak for the suggestion.

I agree that adding support for sqlite will make the role more complete regarding supporting the same backends than nextcloud.

Regarding the point to put the DB data into another location is probably out of the scope of this role, as a system config subject. There are lots of ways to implement this; some requires more or less changes and/or system knowledge of the system.

for example, mounting an external block storage volume to the default data directory of the DB server, before installation, would also work without changing configuration .. or binding the DB data folder to another one located inside a external volume, mounted somewhere else in the system. etc.

Also as you said, wanting to do anything than default requires additional setup, testing and fixing. On a devops perspective, this is the way. :smiley:

So, I would go as far as suggesting SQLite be the default database backend. It's fit for individuals, and for more advanced use cases, one can always switch to a DB of their choice.

That's an interesting point. @staticdev @wiktor2200 what do you think ?

On my side, I'm referring to Nextcloud's doc stating that sqlite is a low performance, non prod focused DB backend. It recommends to use mysql/mariadb, that is the role's default and probably the most widely used DB backend for nextcloud. ( it would be nice to have the server survey result to get a better estimation)

Regards. Aal.

[bemyak]

Regarding the point to put the DB data into another location is probably out of the scope of this role, as a system config subject.

I completely agree. This is out of the scope of this role. My point is that SQLite makes it easier to achieve this setup in its own role.

I would leave any database installation out of scope, ship SQLite as default and if a user wants something else, they should install it beforehand the way they want and supply this role with endpoint and credentials. Kind of like we do with SSL certs at the moment.

But I understand the vision of having everything out of the box, just expressing my humble opinion.

[wiktor2200]

That's interesting point. SQLite is extremely slow and from my perspective good for simple use cases. Nextcloud's usage generates lots of data in DB, so even small instance with few GB of data can grow to hundreds of MBs in DB. I would leave MySQL as it is right now, because it's the most common solution. But to have it as complete solution we could try to add SQLite as option to role in the future.

[bemyak]

It's pretty easy to get it working already. I had to install php{{ php_ver }}-sqlite3 package and set nextcloud_db_backend: sqlite role variable.

[bemyak]

SQLite is extremely slow

Slow in what sense? It shouldn't affect the transfer rate, right? Only UI responsiveness.

[wiktor2200]

It's pretty easy to get it working already. I had to install php{{ php_ver }}-sqlite3 package and set nextcloud_db_backend: sqlite role variable.

It sounds great :)

Slow in what sense? It shouldn't affect the transfer rate, right? Only UI responsiveness.

I meant that usage of Nextcloud is very limited with SQLite and as documentation said it's good only for testing and lightweight single-user setups without client synchronization, so mostly for testing purposes, because one of the main features is client sync. :) What is more adding any app (calendar, contacts, media etc.) probably will make whole instance too slow to use.

But still I agree that it would be good to add it as option for those who would be interested :)

[staticdev]

I have nothing against having the option, the support will be proportional to the usage. @bemyak do you have interest in creating a PR maybe? Looks like you already figured out most of the job.

[bemyak]

I don't know how to install an unpublished ansible collection. I can submit a PR, but I'll only test it on 1.8.0.

[staticdev]

@bemyak we are in a transition phase. I think 2.0.0 will come soon enough so you can use it as base.