I want to add Custom Fields support for tables/entities in my plugin (PHP + Vue.js) and plan to use a Morphing Database Schema approach: a settings page where users can define custom fields for supported entities. Every custom field will be added to the table itself. The column names will be prefixed with custom_ to avoid conflicts with regular fieldnames.
Pros:
Fast queries, easy filtering (even with many simultaneous filters)
Good cross-database-engine support
no complex subqueries or joins
Easy foreign key/relation support
Two concerns came up in discussion with a colleague:
He argued that users or larger providers might configure Nextcloud so the DB user cannot run ALTER TABLE. Is this common? I’d assume not, since that would break regular migrations too.
He also considers this bad practice in the Nextcloud community. I’m only modifying plugin-owned tables, not core ones.
I considered this alternatives (but do not like to use them, if possible):
JSON columns: ruled out due to inconsistent support across SQLite, MySQL, and PostgreSQL.
I am not aware of any other app using a morphing database schema.
I think your colleague is reasonable to call database schema changes outside the regular migrations bad practice in NC, but it’s your app and you are welcome to disagree and do it anyway
I develop two apps, that use EAV (biblio (currently paused, but development will restart very soon), which is using EAV with advanced filtering and groupfolder_tags which is using rather basic EAV).
And i know of at least one other app using EAV, which is tables, which I would assume is the most advanced implementation of EAV in any nextcloud app.
I disagree. EAV performance is a lot better in my experience than people think. It’s not easy to implement those complex JOINs, sure, but the result is actually quite fast.
Valid point.
One additional concern I have about going with the morphing scheme is multi tenancy: Those columns will have to be defined for the whole instance, so no way to do multi tenancy without also creating tables dynamically.
I can only agree with @TessyPowder: EAV is used in Nextcloud apps so far.
The sleeping of the database schema is actually discouraged in Nextcloud also to my understand and reading. This might have multiple reasons, from the front of my head these come to my mind:
Transactions do not work with commands to alter the scheme. This, you must be super careful and do manual locking to keep everything in shape during modifications.
You are sort of restricted during migrations to only do incremental changes. You never know the exact schema that will be present in the actual database.
This makes debugging probably harder if errors happen only on users instances.
Thinking of the future, we do not know what the core might bring up in terms of optimization techniques related to the DB. I think they assume everything is piped through the DB wrapper using migrations. If you break with that assumption, it might break in the future.
I hope you get this sorted out. Just wanted to mention tables as well as it sounds that you want to implement something similar. Maybe a cooperation might be thinkable?
thank you very much for the reply. I appreciate this.
Would you say the lack of multi tenancy support is a showstopper for publishing to the Nextcloud app store?
The software will contain sensitive data, and I would rather not suggest to use it in such an environment. Also I fear the complexity, that would come with it. (KISS principle)
I really think about switching to EAV. Maybe I’ll have a look into how tables is managing them. That’s a good hint. I cannot really see an advantage of switching to “tables”. But please correct me. I’ll also have a look into your biblio and group_folders plugins. Thanks a lot.
The plugin that I build is an IT Assset Management Plugin - a software aimed at small and medium sized businesses. At the moment it comes with 9 entities, and It will have more with coming features. Especially the main entities “devices” and “software_licenses” will heavily use the related entities.
You can see the entities / menu points here on the screenshot on the left side menu (sorry, development parts are still not translated to englisch language):
For the App Store? No, absolutely not.
But I think it is a general expectation of a Nextcloud user. As sharing based workflow is central to most apps permissions. Resources are generally owned either by a user (like calendars) or by the system (like groupfolders) and shared to others from there.
Global state accessible to all users is pretty rare (Collaborative tags (systemtags) being a notable exception).
If you decide for your app, that it does not make sense to host it multitenant thats fine, just make that clear in the app description.
A middleground could be if you allow limiting access to certain groups/users (not sure if you already planned such a feature or if your app is compatible with the “Limit app to groups” feature).
