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.
Experimental feature
Appwrite Relationships is an experimental feature. The API and behavior are subject to change in future versions.
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.
Tradeoff
Consider using relationships when the same information is found in multiple places to avoid duplicates. However, relationships come with the tradeoff of slowing down queries. For applications where the best read and write performance is important, it may be acceptable to tolerate duplicate data.
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.
Learn how to load relationships with queries
Directionality
Appwrite relationships can be one-way or two-way.
| Type | Description |
One-way | The relationship is only visible to one side of the relation. This is similar to a tree data structure. |
Two-way | The relationship is visible to both sides of the relationship. This is similar to a graph data structure. |
Types
Appwrite provides four different relationship types to enforce different associative rules between rows.
| Type | Description |
One-to-one | A row can only be related to one and only one row. |
One-to-many | A row can be related to many other rows. |
Many-to-one | Many rows can be related to a single row. |
Many-to-many | A row can be related to many other rows. |
On-delete
Appwrite also allows you to define the behavior of a relationship when a row is deleted.
| Type | Description |
Restrict | If a row has at least one related row, it cannot be deleted. |
Cascade | If a row has related rows, when it is deleted, the related rows are also deleted. |
Set null | If a row has related rows, when it is deleted, the related rows are kept with their relationship column set to null. |
Creating relationships
You can define relationships in the Appwrite Console, or using a Server SDK
You can create relationships in the Appwrite Console by adding a relationship column to a table.
- In your project, navigate to Databases > Select your database > Select your table > Columns > Create column.
- Select Relationship as the column type.
- In the Relationship modal, select the relationship type and pick the related table and columns.
- Pick relationship column key(s) to represent the related table. Relationship column keys are used to reference the related table in queries, so pick something that's intuitive and easy to remember.
- Select desired on delete behavior.
- Click the Create button to create the relationship.
Here's an example that adds a relationship between the tables movies and reviews. A relationship column with the key reviews is added to the movies table, and another relationship column with the key movie is added to the reviews table.
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*.
You can create both the parent and child at once in a relationship by nesting data.
Edge case behaviors
- If a nested child row is included and no child row ID is provided, the child row will be given a unique ID.
- If a nested child row is included and no conflicting child row ID exists, the child row will be created.
- If a nested child row is included and the child row ID already exists, the child row will be updated.
If the child rows are already present in the related table, you can create the parent and reference the child rows using their IDs. Here's an example connecting reviews to a movie.
Queries
Queries are currently not available in the experimental version of Appwrite Relationships but will be added in a later version.
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.