Improvements to Administering Collections

[stnguyen90]

The database changes as part of 0.12 have been great and I definitely see a lot of flexibility and potential with the new queries. One thing that has become much more burdensome has been managing collections.

One hurdle is with creating indexes (and sometimes attributes). If an index or attribute fails to create successfully, there's no information provided via the UI about what was wrong. You would have to look at the appwrite-worker-database logs to see what's wrong. This is going to be a major problem for cloud if people don't have access to the docker logs.

Another big challenge is that attributes (and indexes) can't be modified. Instead, they must be deleted and recreated. This is a huge problem for apps in production with a large amount of data because it would be really difficult to try and export all the data, delete the attribute, create a new attribute, and reimport all the data while trying to reduce downtime.

Are there any plans to address these?

[eldadfux]

We were planning to add error log information on the index response model object if available. Editing indexes and attribute hasn't been a priority so far, but definitely something we can look into.

[gepd]

I think indexes are not so critical as attributes, specially enums, if you create an enum with x items and then you add a new one, you have to remove the entire collection to add it. As you can imagine, you shouldn't be taking that risk in production

[krzkz94]

@gepd I went ahead and tried this method with 0.12.1 and removing/re-creating an attribute (even with the same type) will remove it's data as well so it's definitely not a feasible option.

[stnguyen90]

Another thing I'd like to bring up is the requirement to have an exact matching index for an API call with queries. Suppose I have an app that lists shirts and I want to let users filter by type, color, and/or size. I would need to create 6 indexes to cover all the combinations of filters they would provide. N! indexes would be annoying to create.

[kodumbeats]

Database indexes try to be smart at times, so I don't think N! indexes is accurate, though this gets increasingly harder to explain in documentation.

Say I have the following index: type ASC, color ASC, size ASC
Implicitly, this compound index also provides the following indexes: type ASC, color ASC and type ASC.

Since this strict query matching is already controlled by a boolean flag, we could expose this as an environment variable, but if developers see bad performance, they'll see Appwrite as the cause, not the database/query design.
https://github.com/utopia-php/database/blob/ecc143f2cfe16b23675407035c6b5375ba263285/src/Database/Validator/Queries.php#L96-L97

[evoTecc]

Not sure if there's any developments on this issue so wanted to ask.

I'm writing a classifieds app and was hoping to create a robust search function without the need to create an index for every combination of queries users may use; I don't understand the limitations of MariaDB very much.

I currently have a collection with the following attributes in an index:
price (ASC), date_posted (ASC), year (ASC), make (ASC), model (ASC), body (ASC), drive_type (ASC), transmission (ASC), fuel_type (ASC), title_type (ASC), vin (ASC), mileage (ASC), island (ASC)

However its possible the user only wants to query one or two of those as in stnguyen90's example above

Is there any possible way to achieve this in AppWrites current state without creating every individual index? Was hoping a recommended solution existed such as feeding wildcard queries to attributes users didn't select to search; At this point though I'd be happy with a workaround.

[eldadfux]

We are actively exploring this issue. @christyjacob4 and @fogelito have dived into this specific limitation today. @fogelito will explore how we can add more flexibility while taking the adapter (MariaDB) specific limits and capabilities into account and still providing devs good protection from inefficient queries.

[Aviortheking]

@eldadfux was there any advancement on this feature?

[stnguyen90]

Indexes are no longer required as of 1.3

[RA9]

There is no straightforward way to update an existing attribute, imagine a case you want to change the attribute type from a string to an email type. Currently, you will have to delete the attribute and recreate it with the preferred type. My fear is what if this is in production and you happened to delete that attribute will the values assigned to the attribute be deleted as well?

[gepd]

I understand the reason you give @kodumbeats but do you disscused something related to have a replacement to this with the team? losing this feature make feel like if appwrite is not prepared to production, and have a lack to something that should be a "must to have" feature

[eldadfux]

I can definitely understand the need for this. We'll circle back and see what solutions could be possible. A few might be to explore non-blocking ways to update attributes or migrating table structure, locking the table could also be an option if a dev is aware of the potential results.

The database has gone some significant changes and many improvments and we're continuing to iterate and tackle the issues that have popping up as a result. Thank you for all the great feedback.

[eldadfux]

Diving into this a bit deeper, we can definitely try to achieve some more capabilities some of them easily, some might be a bit more tricky.

• Attribute name - we need to test whether MariaDB/MySQL are preforming a table lock. We can also use a unique ID for the table and cast the JSON response with the Appwrite attribute name and have no effect on performance, update could also take zero time. Data structure might be messy.

• Required - Can't think of a major problem updating this part

• Validation (min, max, length, etc...) - we can allow to change it, but should we re-validate existing data or just keep it as is, and focus on user input coming in

• Default - should be pretty easy to update

• Type / Size / Array / Elements - might be the most complex things to update, could lock the collection/table and might be hard to find a consistent way to convert the existing data. Even if we don't do this one, I think allowing to edit the other properties will have a lot of value.

Would love to get more feedbacks and ideas.

[stnguyen90]

related issue: #3328

[barracus84]

Diving into this a bit deeper, we can definitely try to achieve some more capabilities some of them easily, some might be a bit more tricky.

* Attribute name - we need to test whether MariaDB/MySQL are preforming a table lock. We can also use a unique ID for the table and cast the JSON response with the Appwrite attribute name and have no effect on performance, update could also take zero time. Data structure might be messy.

* Required - Can't think of a major problem updating this part

* Validation (min, max, length, etc...) - we can allow to change it, but should we re-validate existing data or just keep it as is, and focus on user input coming in

* Default - should be pretty easy to update

* Type / Size / Array / Elements - might be the most complex things to update, could lock the collection/table and might be hard to find a consistent way to convert the existing data. Even if we don't do this one, I think allowing to edit the other properties will have a lot of value.

Would love to get more feedbacks and ideas.

Hi @eldadfux , could you kindly update us regarding the progress of these implementations? Had you think about feasibility and, in case, any release date?

[XanManZA]

Guys we are running an Appwrite solution in production - seeing the updating of attributes just hanging here, with the v1.0.0-RC release pending makes me a bit worried.

How is this not one of the top priorities? There isn't even an officially supported to way to circumvent this issue. Not being able to edit an attribute is something that is a non-negotiable for a production solution.
People aren't perfect and can't predict the future - schemas change, constantly.

We have for example a situation where a FK should now be nullable, but because the attribute can't be edited we have to fill it with something fake/invalid (so bad).

I want to clarify, I'm very grateful for Appwrite as it saved a lot of time in getting a solution up and running.
However if we want serious solutions to be built on top of this, not being able to update a column in your Database is a slow death to data integrity.

[Maxbrt18]

Same problem for me, do you have any idea if any solutions are coming? Is there any way to modify an attribute directly in the database with Appwrite turned off?

[XanManZA]

Hey @Maxbrt18 we eventually moved the project to https://directus.io/ - way happier and saner design decisions in my opinion. Good luck

[fransyozef]

@XanManZA oeee ... this looks good. So it's like appwrite?

[XanManZA]

It's not exactly like Appwrite, but it also functions as a BaaS. Instead of for example "cloud functions" they have "extensions".
I think if you have backend experience Directus is going to speak better to you way more.
My guess is Appwrite will be more directed to app developers with minimum backend knowledge and as a Firebase alternative.
Even then Appwrite wouldn't have been mature enough. (Case and point this discussion and the missing functionality necessary).

[fransyozef]

@XanManZA I see .. yeah I know what you mean. Things like a max cap on database attributes(why, but I can live with it) and this(not able) changing of an attribute is a big no no. Thanks!

[barracus84]

For anyone on hold with this, waiting for a solution from appwrite team, I am scripting attribute update in this way (using Python SDK).

It lacks any control about data integrity, data type (eg: should check if old attr is the same type of new attr)...and results check.

from appwrite.client import Client
from appwrite.services.databases import Databases

def setup_client() -> Client:
  client = Client()

  (client
    .set_self_signed()
    .set_endpoint('https://localhost/v1') # Your API Endpoint
    .set_project('633...b44') # Your project ID
    .set_key('d78...28a') # Your secret API key
  )

  return client

def main():

  DATABASE_ID = '633...e37'
  COLLECTION_ID = '633...edd'
  OLD_ATTRIBUTE_KEY = 'name1'
  NEW_ATTRIBUTE_KEY = 'barbapapa'

  # setup client
  client = setup_client()

  databases = Databases(client)

  # list documents
  documents = databases.list_documents(DATABASE_ID, COLLECTION_ID)

  # scroll documents, update document new attribute value with old attribute value
  for doc in documents['documents']:
    document_id = doc['$id']
    old_attribute_value = doc[OLD_ATTRIBUTE_KEY]
    # preapre new object
    obj_update = {
      NEW_ATTRIBUTE_KEY: old_attribute_value
    }
    # update docs
    databases.update_document(DATABASE_ID, COLLECTION_ID, document_id, obj_update)

  # delete attribute
  databases.delete_attribute(DATABASE_ID, COLLECTION_ID, OLD_ATTRIBUTE_KEY)

if __name__ == "__main__":
    main()

[merabtenei]

Adding my comment here.
We run into a situation where we just need to increase the length of a String attribute and we are shocked to see that there is nothing we can do about this without deleting the attribute and creating a new one. We can understand that changing the type maybe problematic but, changing the length for String, the min and max for integers for example shouldn't be a issue.
This should be a priority as this clearly prevents Appwrite from being production ready.

[fransyozef]

bump ... any progess on this?

[stnguyen90]

Closing since, as of 1.3, indexes are no longer required and there have been improvements to updating attributes.

[samuel-prz]

Can you tell me how can I update an attribute? For example, I just need to increase the length of a String attribute.

[mokamhawy]

Can you tell me how can I update an attribute? For example, I just need to increase the length of a String attribute.

Me too