DEV Community

Cover image for #30DaysOfAppwrite : Appwrite Database
Christy Jacob for Appwrite

Posted on • Updated on

#30DaysOfAppwrite : Appwrite Database

Intro

Appwrite is an open-source, self-hosted Backend-as-a-Service that makes app development easier with a suite of SDKs and APIs to accelerate app development. #30DaysOfAppwrite is a month-long event focused on giving developers a walkthrough of all of Appwrite's features, starting from the basics to more advanced features like Cloud Functions! Alongside we will also be building a fully-featured Medium clone to demonstrate how these concepts can be applied when building a real-world app. We also have some exciting prizes for developers who follow along with us!

Appwrite Database

Welcome to Day 15 👋. Today is finally the day to cover storing data in the Appwrite database. Appwrite offers an easy-to-use, document-based database API for storing your app's data. We built our NoSQL interface on top of MariaDB, inspired by Wix, who did the same. MariaDB provides its battle-tested stability and performance, and you can manage Appwrite using existing, familiar database tools like MySQLWorkbench, phpMyAdmin, and more. Collections, documents, attributes, and permissions can all be managed with the Appwrite console or with our SDKs. There's a lot to cover, so let's dive in.

A database refactor is underway to improve overall performance and add support for a variety of databases, including PostgreSQL, MongoDB, and more!

Glossary

Each database comes with its own set of technical jargon - before we get too far, let's go over ours.

  • Collection: A group of documents. Each collection has attributes to define its document structure and permissions for read and write.
  • Document: A structured JSON object of keys and values, belonging to a collection. Keys and their types are defined in a collection attribute.
  • Attributes: The definition of each document attribute. Each attribute has key, attribute type, default, required and array properties, which define the type and structure of expected data. Think of them as columns in a traditional relational database.
  • Permissions: Array of strings that define the access control to documents, collections, and files in storage.

Now, let's review each in more detail.

Collections and Documents

In short: collections hold documents. If you're a SQL veteran, you might know these better as tables and rows (and internally, that's technically correct). Each collection is identified by collectionID and holds many similarly formatted documents where each document is a piece of data. The kind of data accepted by Appwrite is governed by the attributes defined for the collection.

The ID for a collection or a document can be custom or randomly generated. For custom IDs, simply pass in a string ID of your choice. For randomly generated IDs, you can pass in the string unique() to indicate that an ID should be unique and randomly generated.

Attributes

Simply put, attributes outline what your documents should look like. With this approach, Appwrite's rule validators ensure the data going into your database is the exact format you expect. So, for each key-value pair of our document, we provide:

Property Description
key Name of the attribute.
type Data type of the attribute.
default Default value of the attribute.
required If the attribute is required.
array If the attribute is an array.

Here are the validators available for attribute types:

Attribute Type Description
String Any string value.
Integer Any integer value.
Float Any float value.
boolean Any boolean value.
url Any valid URL.
email Any valid email address.
ip Any valid IPv4 or IPv6 address.
enum Any enum defined by you.

Permissions

To control access to resources, Appwrite offers developers a flexible permission system that is aware of Appwrite users and teams. Let's cover the most used permissions:

Permission Description
role:all Wildcard permission. Grants anyone read or write access.
user:[userID] Grants access to a specific user by userID.
team:[teamID] Grants access to any member of the specific team. Note: the user must be the team owner or have accepted a team invite to grant this access.
team:[teamID]/[role] Grants access to any member who possesses a specific role in a team. Roles can be assigned on invite.
member:[memberID] Grants access to a specific member of a team, only while they are still a member of the team.
role:guest Grants access to any guest user who isn't logged in.
role:member Grants access to any logged-in user (a user with a valid session). Logged in users don't have access to role:guest resources.

Note: documents don't inherit permissions from their parent collection if the collection's permission level is set to Document Level.

Query Building

After building Indexes for your collection, you can query these indexes using any of our client side SDKs or server side SDKs. Each SDK comes packaged with a Query class which allows you to build query statements. The Query class will turn the provided queries into a string. You can write query strings directly if you're not using an SDK, but we will be using the Query class in our posts.

Let's start with a simple example:

Here's an example which finds movies with the title Avatar or Lord of the Rings from years after 1999:

sdk.database.listDocuments('movies', [
    Query.equal('title', ['Avatar', 'Lord of the Rings']),
    Query.greater('year', 1999)
]);
Enter fullscreen mode Exit fullscreen mode

Here's the same example with Dart/Flutter:

import 'package:appwrite/appwrite.dart';

void main() async {
    final client = Client();
    final database = Database(client);
    try {
        final docs = await database.listDocuments(
            collectionId: 'movies',
            queries: [
                Query.equal('title', ['Avatar', 'Lord of the Rings']),
                Query.greater('year', 1999),
                ]);
        print(docs.toMap());
    } on AppwriteException catch(e) {
        print(e);
    }
}
Enter fullscreen mode Exit fullscreen mode

Appwrite supports seven types of query operations:

Operator Description
equal Equal to.
notEqual Not equal to.
lesser Lesser than.
lesserEqual Less than or equal to.
greater Greater than.
greaterEqual Greater than or equal to.
search Requires a Fulltext Index.

When passed into listDocuments(), an AND operation is applied to the list of query operations. For OR behavior, pass in an array of values into an operator.

Putting it all together

As an example, let's create a collection of books in Appwrite. While some projects require creating collections programmatically, others are easier to create with the Appwrite console.

Create collection

A book has a title, author, and year it was published. Let's add those, starting with title using the text rule type:

Add Title Rule

If you see, new attributes are not required by default. Let's make title required:

Make Title Required

Now, we can do the same for author and published, using the numeric rule type for publication year, so we now have:

Add Published Rule

Permissions, by example

Now that our Books collection has the necessary attributes, we can create documents and restrict access as necessary. Check out the following code:

let sdk = new Appwrite();
sdk
    .setEndpoint('https://<HOSTNAME_OR_IP>/v1') // Your API Endpoint
    .setProject('5df5acd0d48c2') // Your project ID
;

let promise = sdk.database.createDocument(
    '609bdea2f0f99', // collectionID for Books
    'unique()', // unique() will create a random ID
    {'title': 'The Great Gatsby', 'author': 'F. Scott Fitzgerald', 'published': 1925},
    ['role:member'],
    ['team:5c1f88b87435e/owner', 'user:6095f2933a96f']);
Enter fullscreen mode Exit fullscreen mode

In this example, the new book from createDocument can be read by any logged in user, but only the owner of Team 5c1f88b87435e and User 6095f2933a96f have the permissions to write (or update).

Credits

We hope you liked this write-up. You can follow #30DaysOfAppwrite on Social Media to keep up with all of our posts. The complete event timeline can be found here

Feel free to reach out to us on Discord if you would like to learn more about Appwrite, Aliens or Unicorns 🦄. Stay tuned for tomorrow's article! Until then 👋

Top comments (2)

Collapse
 
yahalomsguy profile image
Guy (Yahalom)

Should be fixed!
As of version 0.12 nested documents are no longer supported.

The only way to use nested collections are to create an array of JSON Decoded string a document.

Did I understand the reason it was eliminated? no really
Did I like it? even less...
But it is definitely working and possible.

Collapse
 
gewenyu99 profile image
Vincent Ge

We're working to update these guides as I write to you. We eliminated nesting documents because it promoted DB schemas that just performed awful.

We'll update this post with a new suggested approach, stay tuned!