Relationships_

Relationships describe how rows in different tables are associated, so that related rows can be read, updated, or deleted together. Entities in real-life often associate with each other in an organic and logical way, like a person and their dog, an album and its songs, or friends in a social network.

These types of association between entities can be modeled in Appwrite using relationships.

Relationship columns

Relationships are represented in a table using relationship columns. The relationship column contains the ID of related rows, which it references during read, update, and delete operations. This column is null if a row has no related rows.

When to use a relationship

Relationships help reduce redundant information. For example, a user can create many posts in your app. You can model this without relationships by keeping a copy of the user's information in all the rows representing posts, but this creates a lot of duplicate information in your database about the user.

Benefits of relationships

Duplicated records waste storage, but more importantly, makes the database much harder to maintain. If the user changes their user name, you will have to update dozens or hundreds of records, a problem commonly known as an update anomaly in tablesDB. You can avoid duplicate information by storing users and posts in separate tables and relating a user and their posts through a relationship.

Opt-in loading

By default, Appwrite returns only a row's own fields when you retrieve rows. Related rows are not automatically loaded unless you explicitly request them using query selection. This eliminates unintentional payload bloat and gives you precise control over performance.

Directionality

Appwrite relationships can be one-way or two-way.

Types

Appwrite provides four different relationship types to enforce different associative rules between rows.

On-delete

Appwrite also allows you to define the behavior of a relationship when a row is deleted.

Creating relationships

You can define relationships in the Appwrite Console, or using a Server SDK

Creating rows

If a table has relationship columns, you can create rows in two ways. You create both parent and child at the same time using a nested syntax or link parent and child rows through references*.

Queries

You can use filter queries directly against relationship columns using dot notation. This lets you filter rows based on the values of their related rows, such as filtering posts by an author's name or filtering orders by a product's category.

Use the format relationshipKey.field to reference fields on related rows.

All filter queries are supported on relationship fields, including equal, notEqual, greaterThan, lessThan, between, contains, and other comparison operators.

Update relationships

Relationships can be updated by updating the relationship column.

Delete relationships

Unlink relationships, retain rows

If you need to unlink rows in a relationship but retain the rows, you can do this by updating the relationship column and removing the ID of the related row.

If a row can be related to only one row, you can delete the relationship by setting the relationship column to null.

If a row can be related to more than one row, you can delete the relationship by setting the relationship column to an empty list.

Delete relationships and rows

If you need to delete the rows as well as unlink the relationship, the approach depends on the on-delete behavior of a relationship.

If the on-delete behavior is restrict, the link between the rows needs to be deleted first before the rows can be deleted individually.

If the on-delete behavior is set null, deleting a row will leave related rows in place with their relationship column set to null. If you wish to also delete related rows, they must be deleted individually.

If the on-delete behavior is cascade, deleting the parent rows also deletes related child rows, except for many-to-one relationships. In many-to-one relationships, there are multiple parent rows related to a single child row, and when the child row is deleted, the parents are deleted in cascade.

Permissions

To access rows in a relationship, you must have permission to access both the parent and child rows.

When creating both the parent and child rows, the child row will inherit permissions from its parent.

You can also provide explicit permissions to the child row if they should be different from their parent.

When creating, updating, or deleting in a relationship, you must have permission to access all rows referenced. If the user does not have read permission to any row, an exception will be thrown.

Limitations

Relationships can be nested between tables, but are restricted to a max depth of three levels. Relationship column key, type, and directionality can't be updated. On-delete behavior is the only option that can be updated for relationship columns.