🚀 Feature: Maps / Nested Documents / Relationships support

[locohost]

🔖 Feature description

We need "Map" attributes and arrays of "Map" attributes to be true NoSQL.

🎤 Pitch

I'm a long time Firebase/Firestore developer. I used Firebase before Google bought it. I love how quickly FB/FS allows me to put together my project backend and quickly build data models that make sense, do not need data in related tables and therefore have super fast read speeds.

I found Appwrite from a Reddit article. It sounded very interesting, so here I am! :-) My recent Appwrite adventures came about from my desire to support open source projects and the fear of future Google/Firebase bills I cannot afford. We all dream about our apps becoming popular some day right?😉

My Appwrite impediments last few weeks have been all about the missing "Map" attribute. I have very little interest in moving away from NoSQL databases. The missing Map attrib is forcing me down many complex database redesign tasks, lots of model redesign and ultimately being forced to convert back to relational database thinking.

Not having Map attrib means I have to JSON.stringify all of my Map attribs, and MUCH worse arrays of stringified models that then may have arrays of stringified models/attribs and so on. This is crazy complicated. This then means I need code in either sync functions (I need to get an http response back with remodeled data) or another REST Api in front of Appwrite to reformat my data back to the models my client UI is expecting. Right now I'm building the REST Api facade. While this is a fun exercise (I love building REST Apis), it is super time consuming and taking me away from my Flutter development.

We need Map (doc) attributes and also, arrays of Maps (docs) to be a true "Firebase alternative" :-)

👀 Have you spent some time to check if this issue has been raised before?

• I checked and didn't find similar issue

🏢 Have you read the Code of Conduct?

• I have read the Code of Conduct

[koodeau]

Is it what you're looking for?

The way it sends a response is pretty much okay for all my needs.

[locohost]

@koodeau Thanks for the reply! Well, I don't think that's what I'm looking for. I'd be happy to be wrong about that :-)

I have "arrays" of Strings and Numbers in my data but I'm really talking about "Map" attribs. Some people call these "nested docs" or "subdocs".

For example...

const person = { "name": "Mark", "phone": "123-456-7890", "address": { "street": "123 Main St", "P.O. Box": "23a", "city": "Hagerstown", "state": "MD" }, "email": "[email protected]" }

The "address" attribute is a Map. We can't do this in an Appwrite document. And also arrays of Maps are needed. You can do these in all the other popular NoSQL dbs so I'm hoping it finds it way here :-)

[eldadfux]

@koodeau Thanks for the reply! Well, I don't think that's what I'm looking for. I'd be happy to be wrong about that :-)

I have "arrays" of Strings and Numbers in my data but I'm really talking about "Map" attribs. Some people call these "nested docs" or "subdocs".

For example...

const person = { "name": "Mark", "phone": "123-456-7890", "address": { "street": "123 Main St", "P.O. Box": "23a", "city": "Hagerstown", "state": "MD" }, "email": "[email protected]" }

The "address" attribute is a Map. We can't do this in an Appwrite document. And also arrays of Maps are needed. You can do these in all the other popular NoSQL dbs so I'm hoping it finds it way here :-)

Right now we support a flat design. This means that instead of storing the address as a map you would store in multiple fields. For example: addressStreet and addressCountry. We'll be looking to add more complex types including relations, but before that is done we have to make sure our solution is reliable and scaleable.

[locohost]

@eldadfux thanks for the reply. I guess the comparisons to Firebase confused me and led me to immediately assume NoSQL. I admit to not really knowing much about MariaDb.

Is there an Appwrite roadmap anywhere we can see/follow?

[eldadfux]

@locohost currently this is the closest thing we have to the roadmap: https://github.com/appwrite/appwrite/projects?type=beta. You can also view Github issues that are marked as backlog, waiting for release or work in progress.

We do plan to sync our internal issues on Github project, it's just a bit tricky to execute the process and might take a while.

[anandsubbu007]

any update?

[alilosoft]

I'm lucky to see this before investing lot of time trying to use Appwrite, as for now I think I will go with supabase. I will come back when this issue is closed.

[MichalNemec]

Hello, appwrite ticks all of the boxes for my project, but one is missing.. relations, my project will grow really big and i dont want any duplicated data in database, when can i expect this feature? Does anybody know about alternative that has relations, realtime, sdk, rbac? Thanks!

[radvansky-tomas]

I dont undestand this part too...I mean its presented as sort of nosql db, but we cant have schema-less documents. Its using sql db instead but we dont have relations, counts even update/deletion based on query.

I dont really understand why, decide to go one way and optimise it that way OR even go both ways and make connectors do different DB types and let users decide what they prefer

[MichalNemec]

@radvansky-tomas exactly. I mean, by default in docker i see mariadb, mariadb has relations, its bummer, that its not available.

[MichalNemec]

@radvansky-tomas
@fellahi-ali
i was missing relations as you know and i made a function (had issues to actually trigger any functions, big help from Drake on discord)

this is prototype, im not 100% sure about performance and quality, but good start:

const sdk = require("node-appwrite");

/*
  'req' variable has:
    'headers' - object with request headers
    'payload' - object with request body data
    'env' - object with environment variables

  'res' variable has:
    'send(text, status)' - function to return text response. Status code defaults to 200
    'json(obj, status)' - function to return JSON response. Status code defaults to 200

  If an error is thrown, a response with code 500 will be returned.
*/

module.exports = async function (req, res) {
  const client = new sdk.Client();


  /**
   * PAYLOAD STRUCTURE
   */

  /*
  //initial collection
  //queries
  //limit
  //offset
  /relations array for mapping
  //type (just to divide it in the future)
  //relationfrom is initial collection column
  //relation collection id (target)
  //relation to is targeted column
  //includeUser (bool to trigger fetching user )
  //userIdField (if includeUser true -> based on initial collection field)
  {
    "collectionId": "629b079abda80ad2cc34",
    "queries": [{ "type": "search", "field": "Title", "for": "test" }],
    "limit": 0,
    "offset": 0,
    "relations": [
      {
        "type": "collection",
        "relationFrom": "owner",
        "relationCollectionId": "629b041faa12c12bd76b",
        "relationTo": "owner",
        "includeUser": true,
        "userIdField": "owner"
      }
    ]
  } 
  */

  let payload = JSON.parse(req.payload);



  if (
    !req.env['APPWRITE_FUNCTION_ENDPOINT'] ||
    !req.env['APPWRITE_FUNCTION_API_KEY']
  ) {
    console.warn("Environment variables are not set. Function cannot use Appwrite SDK.");
  } else {
    client
      .setEndpoint(req.env['APPWRITE_FUNCTION_ENDPOINT'])
      .setProject(req.env['APPWRITE_FUNCTION_PROJECT_ID'])
      .setKey(req.env['APPWRITE_FUNCTION_API_KEY'])
      .setSelfSigned(true);
  }

  let database = new sdk.Database(client);
  let users = new sdk.Users(client);

  let properQueries = [];
  payload.queries.forEach((q) => {
    switch (q.type) {
      case 'search':
        properQueries.push(sdk.Query.search(q.field, q.for));
        break;
      case 'eq':
        properQueries.push(sdk.Query.equal(q.field, q.for));
        break;
    }
  });

  //getting collection
  let documents;
  if (payload.limit && payload.offset) {
    documents = await database.listDocuments(
      payload.collectionId,
      properQueries,
      payload.limit,
      payload.offset
    );
  } else {
    documents = await database.listDocuments(
      payload.collectionId,
      properQueries,
    );
  }


  for (let relation of payload.relations) {
    //extacting relation fields
    fields = documents.documents.map((d) => d[relation.relationFrom]);

    if (relation.type == "collection") {
      //getting relation data
      let relatedData = await database.listDocuments(
        relation.relationCollectionId,
        [
          sdk.Query.search(
            relation.relationTo,
            fields,
          ),
        ],
      );

      //processing them
      for (let data of relatedData.documents) {
        if (relation.includeUser) {
          data["user"] = await users.get(data[relation.userIdField]);
        }
        let foundDocumentsToMapTo = documents.documents.filter((d) => d[relation.relationFrom] == data[relation.relationTo]);
        foundDocumentsToMapTo.forEach((f, i) => {
          documents.documents[i]["relations"] = [];
        })
        foundDocumentsToMapTo.forEach((f, i) => {
          documents.documents[i]["relations"].push({ relation: relation.relationTo, data: data });
        })
      }
    }
  }

  res.json({
    documents,
  });
};

[ppulwey]

What is the current approach to deal with relations?
Storing the relation ID's in a string array field and to load them afterwards is the only solution I came up with.

[tessamero]

@locohost Just an update that we have seen your issue and had discussions for the last couple of weeks around this. It looks like it is for sure on the roadmap, but we don't have an exact date yet.

We also plan on working on a public roadmap to share with others to be extra transparent with the Appwrite community.

[eldadfux]

Update: @fogelito has started research on different ways this could be implemented internally in Appwrite. @Meldiron will also provide some help in designing the way this will be exposed to the REST API and different SDKs. Once we have some more information we will share it here and in a GitHub discussion before starting actual implementation.

[linus1703]

Hey, any updates on this? Been awhile since the last update. Sadly, only this keeps me from using appwrite.

[merabtenei]

Hey, any updates on this? Been awhile since the last update. Sadly, only this keeps me from using appwrite.

Same here, I really wanted to use appwrite for my next project but it can't handle any sort of relationship between objects. I guess I'll give supabase or even pocketbase.io a try.

[ppulwey]

Even though it needs a bit more configuration, parse seems to be a very good alternative in the meantime.

[shawnbaughcodes]

Nesting or relationships are really needed. This is a great competitor to firebase along with Supabase and others. I hope you're able to give an update on this in the coming months.

[bUxEE]

If nesting will be applied queries need to be adpated to be able to use nested documents properties like with mongoDB

[charlestati]

The content-types builder of Strapi is exactly what I would love to see:

https://strapi.io/features/content-types-builder

[chrisnurse]

I agree with many comments here about the essential nature of relationships. We are all aware of the flexibility of nested types, but relating entities by ID is an important way of ensuring centralised data changes are reflected in all areas of the data model. If we edit the mobile phone number of a user, we don't want to have to find all documents the mobile was stored in. When we read the graph of the user, we want their contacted details as an up to date nested object, for example. This might be solved in the monodb stage of Appwrite's development, or perhaps through GraphQL, which might be a proxy for referential integrity / relationships. BTW, I am a big fan of Hasura (GraphQL) and Postgres. I've seen other low code platforms just build on top of these.

[dhust]

This is more than a bit frustrating. How could I go about setting up the classes array information (id, name, description) without using a map? I was hoping this would be a part of 1.10.

teacher: {
  id:
  name:
  classes: [
     {
         id:
         name:
         description:
     },
     {
         id:
         name:
         description:
     }
  ]
}

[merabtenei]

@dhust, same here, what I ended up doing is use a String field and store a JsonEncoded map and decoding it after loading the object. It works quite well but you have no control on queries related to specific fields inside the map. But if you just want to store data this should work for the moment.

There is a lot of limitations with the Appwrite database, no grouping, no aggregations Sum and Count even though it's an SQL database, and we don't even have safe ways to create these Sum and Count values as metadata because there's no way to prevent race condition on those values at the moment.

[ppulwey]

Any progress here?

[gewenyu99]

Any progress here?

No dates, but we plan to add relations (so it's on the roadmap)

[ciprian-marius]

At the moment, although i really want, i have a hard time using appwrite on any serious project.
relational database is the only missing piece of the puzzle. I personally hope you can give us the ability to go in advanced mode and use mariadb. Not everyone is afraid of SQL and im sure there are plenty of developers out there willing to use appwrite who know how indexing works and the dangers of full table scans.

But if you guys really want to police database queries (i really dont understand why?!), please do it in a way that's sensible. I want to be able to do complex queries based on indexed data using all types of joins available in mysql/mariadb.

Thank you. I hope you will consider my request.

[rhengles]

Although Appwrite released their own GraphQL API, I'm currently writing my own (not from scratch, but I'm adapting an GQL API I already had into Appwrite, so that's why I don't just simply use the native one.) It's not a joined query, but it gets an ID from a row from Table A and then gets this ID from Table B. It's working, I'm currently writing the mutations to create many related rows in a single GQL mutation query.

All this to say, I can already write my own server layer that fetches these related rows and returns them in a single API endpoint. I don't know the performance difference from having a native SQL join yet. But I want to say that I very much appreciate the SDKs that Appwrite provides, they made my work very easy and I didn't find anything similar elsewhere. So, kudos to Appwrite team 🏆

[gewenyu99]

@ciprianm11 @rhengles Thank you for your input!

This is a topic we've had lots of discussions about and healthy debates about within our team, too! We're trying to hit a sweet spot of not leading users, especially new developers to doing things that are serious malpractice (like you often don't need complex queries, and permissions + local data manipulation is, in fact, better) but we also acknowledge more complex entity-relationships are just necessary.

Anyway! Keep pushing for this to happen and give us feedback. This helps us make smarter product decisions!

[Panth977]

so i have mush simpler problem, i don't need to query or any thing, but i need a map attribute (not subdocument/struct, proper dynamic map) My use-case is quite simple too

customer C wants to buy coffee: 2pack, greenTea: 3pack from vendor V
i have to go about doing, collection/document = {...data}
Orders/{orderID} = {customer: C, vendor: V, order: jsonEncode({[coffeeID]: 2, [greenTeaID]: 3}) }
& while parsing i have to jsonDecode
is there a better way... caz i want order param to be a valid as Map<string, int> formate buy backend, which appwrite will not validate since all it sees is string...

could u create a simple mid-ware that lets dev have more control over string attribute, like this string is json, etc...
Or is there a better way to do this that i dont know yet?

i suggest very simple thing...

create Map-attribute & struct-attribute, appwrite will do encoding & decoding and will store as string...
but for developer, they will send map and receive map.
and this map can have simple key-value pair and value can be validated
like value can be following: (let value be of type MapValue)
value is int (string, enum, etc.,. primitive support)
value is struct (fixed key to value) [and value will be of type MapValue]

this was backend will validate data received, without writing any lambda functions.

[gewenyu99]

so i have mush simpler problem, i don't need to query or any thing, but i need a map attribute (not subdocument/struct, proper dynamic map) My use-case is quite simple too

customer C wants to buy coffee: 2pack, greenTea: 3pack from vendor V i have to go about doing, collection/document = {...data} Orders/{orderID} = {customer: C, vendor: V, order: jsonEncode({[coffeeID]: 2, [greenTeaID]: 3}) } & while parsing i have to jsonDecode is there a better way... caz i want order param to be a valid as Map<string, int> formate buy backend, which appwrite will not validate since all it sees is string...

could u create a simple mid-ware that lets dev have more control over string attribute, like this string is json, etc... Or is there a better way to do this that i dont know yet?

i suggest very simple thing...

create Map-attribute & struct-attribute, appwrite will do encoding & decoding and will store as string... but for developer, they will send map and receive map. and this map can have simple key-value pair and value can be validated like value can be following: (let value be of type MapValue) value is int (string, enum, etc.,. primitive support) value is struct (fixed key to value) [and value will be of type MapValue]

this was backend will validate data received, without writing any lambda functions.

Perfect! Thank you for all the details :)

This will help us a lot as we're writing the RFCs for this topic.

[Panth977]

From #5023

🔖 Feature description

we can see basic use cases like

User/{userDocID} = {
    name: string; 
    address?: {line1: string; line2: string; line3?: string} // is struct
}
Order/{orderDocID} = {
    success: bool;
    customer: { // is struct
        uid: string; 
        name: string; 
        address?: {line1: string; line2: string; line3?: string} // is struct
        phone?: string;
        email?: string;
    };
    order: { [product_id]: int } // is map
    vendor: {
        venderID: string;
        name: string;
    }
}

and converting this into something like

User/{userDocID} = {
    name: string; 
    addressLine1?: string;
    addressLine2?: string;
    addressLine3?: string;
}

would be a bit buggy, because it is possible to have addressLine1 be null & addressLine2 be string. which don't look very good.

🎤 Pitch

We can simply have a Object attribute

Once dev select Object attribute, then it is simple matter of validation check. like

// init sdk, stuff


// User/{userDocID} add "address" attribute
databases.createObjectAttribute(
    '[Database]', 
    'User', 
    'address', 
    -1, 
    false,
 // last parameter is "structure", a simple language to explain structure
    "isStruct({ line1: isString, line2: isString, line3: isNullOr(isString) })"
)
// Order/{orderDocID} add "customer" attribute 
databases.createObjectAttribute(
    '[Database]', 
    'Order', 
    'customer', 
    -1, 
    true,
    `isStruct({
        uid: isUid, // check if is valid uid & exists in project auth pool 
        name: isString, 
        address: isNullOr(isStruct({ line1: isString, line2: isString, line3: isNullOr(isString) })) // structs can have inner structs
        phone: isNullOr(isPhone) // check if is valid phone-number
        email: isNullOr(isEmail) // check if is valid email
// or we can also do
        contact: isEither(isPhone, isEmail, isUid) // isEither will check one by one if given data is valid
})`
)

// Order/{orderDocID} add "order" attribute
databases.createObjectAttribute(
    '[Database]', 
    'Order', 
    'order', 
    -1, 
    true,
   "isMap(isInt)"
)
// you get the point

for validation syntax

basically once object attribute is selected all you have to do is validate if incoming object is correct or not based upon presented structure (syntax of structure can have more complex validator, such as isUid, isEmail, etc.,. this validator are side-view/optional).
mainly have following validator to make it useable

minimum Require validator

• isNullOr($nonNull-validator) // it checks if it is null or validator passed in argument
• isString // it checks if it is string
• isInt // check is int
• isNumber // check is number
• isBool // check is bool
• isStruct({ [field: string]: $validator }) // for each mentioned field a unique validator
• isMap($validator) // in key-value pair, all values must pass same validator
• isList($validator) // all elements must pass same validator

Optional validator

• isEnum("val1", "val2", ...) // corresponding field value must be one of arguments passed in isEnum
• isEmail // checks if is email
• isPhone // checks if is phone
• isUid // checks if is uid & validate from project user pool
• isEither($validator1, $validator2, ...) // checks one by one if value validates one of the given validator in passed arguments
• satisfies($validator1, $validator2, ...) // checks one by one if value validates all of the given validator in passed arguments

Read, Create, Update, Delete

For Read/Write: this attribute will be stored as jsonEncode(incoming-object) & while sending parse by jsonDecode(outgoing-object) (of-course choose encode as you prefer, but the point is, it happens behind the seen)

creating specific inner field updates (like firestore) is fairly possible, but i have a much simpler alternative:
if developer wants to update certain field within object then i think they should bring the field out side (i.e., create field's own document attribute, don't put it inside object. OR create object as document in some other collection.)
this way entire object type attribute's value will be created/update/delete (i.e., set operation) no need to update a specific field within object.

Index

indexing any of the inner fields should be easy,
if index is brand new, then scan all doc & parse/decode attribute with type object (if needed), get field & create query
then every time any document within this collection is updated, simply update index, as incoming write data will be in decoded formate.

[ghost]

Not having relationship modelling capabilities is a huge deal breaker.
It's a pity because Appwrite provides so much value out of the box.
Sadly, I'm not convinced the platform is ready for serious production grade deployments yet.

I might be missing something, but it feels like this also defeats the purpose of having GraphQL when collection schemas are always flat. You have to reconstruct the model manually and notoriously update IDs everywhere they are referenced.

I'm genuinely curious what were the main challenges around delivering relationship mapping sooner?

Thanks in advance!

[mikekok]

I was almost convinced that appwrite was what I was looking for, for my current project, as it included everything I needed out of the box, until I came across this issue.

Appwrite not supporting relationships is a big no for me, I guess I'll still have to go with supabase instead, at least until appwrite becomes more mature for serious projects.

[bronte2k7]

I have been following the project for a very long time. The project is really interesting, but without a relationship system it is impossible to work with Appwrite. Unfortunately, over the past year, the relationship function has not appeared.

I don't know how the appwrite validates incoming data, but jsonschema can be used for complex structures and types.

[eldadfux]

I can finally confirm that work on relationships / nested documents has officially began! Transaction are probably one of the next features just after that.

[nimos-dev]

@eldadfux Can you say something about the time frame when you think it might be implemented? (Just very rough, 1 month, 3 months 6 months...) (I am sitting with some data structures that would be significantly simpler if the functionality coming soon)

Otherwise very satisfied so far with Appwrite especially that everything can be written in Dart (am a flutter developer)!

[anasfik]

I can finally confirm that work on relationships / nested documents has officially begun! Transaction is probably one of the next features just after that.

can you estimate a release date of the relationships feature for databases, and will it leverage the same MariaDB relationships structures, or we should expect nested collections/sub-collections?

[adityaoberai]

The RFC for database relationships is now available. Check out this discussion thread for more details: #5179

[eldadfux]

We’re trying to fit this feature to the next release. It will be released as beta.

[erik-seifert]

This is the last thing to make appwrite to my favorite BaaS

[hortigado]

I join the request thread, good project. Greetings

[nev-21]

Appwrite is great! we are waiting for relationships! Greetings

[Vedsaga]

Appwrite is great! we are waiting for relationships! Greetings

@nev-21 it just got merged... and live

[TorstenDittmann]

I am happy to announce that 1.3.0 is now released with Relationship support 👏🏻

You can find more informations in this blog post and our docs.