Announcing the Appwrite OSS Fund, Learn More! 🤑
Docs

Database

The Database Service allows you to store your application and users' data and fetch it using different supported queries. Using your Appwrite Database, you can organize your data into collections and documents using the Appwrite simple REST API. You can also use the Appwrite Realtime API to subscribe to live changes in your collections and documents. In addition, the Database Service provides built-in validation to check the integrity of your incoming data, custom indexing for query performance, as well as a flexible permissions mechanism to allow you to easily segment data between different users, teams, and roles.

Create Your Collections

Appwrite uses collections as containers of documents. The terms collections and documents are used because the Appwrite JSON REST API resembles the API of a traditional NoSQL database. That said, internally, Appwrite can support both SQL and NoSQL database adapters like MariaDB, MySQL, or MongoDB. When working with an SQL adapter, Appwrite will treat your collections as tables and documents as rows on native SQL tables.

You can create your collection by adding it to your Appwrite project's dashboard. Access your Database settings from your project's left navigation panel. Click the Add Collection button and choose your collection's name. For convenience, you can also set a custom ID for your collection instead of an auto-generated ID.

You can manage your collections programmatically using one of Appwrite's Server SDKs. You can manage documents with both the Server and Client SDKs.

Permissions

Once you create your collection, you will reach your collection's settings page, where you can choose the permission model for your collection. There are two types of permissions models available, each one with different pros and cons designed to be flexible to best match your use case.

Document Level Permissions

With this permission model, you have granular access control over every document, and users will only be able to access documents for which they have explicit permissions. Document permissions are required in this permission model, and collection permissions are optional.

Collection Level Permissions

With collection-level permissions, you assign permissions once for every document in the collection's settings - users with "read" access to the collection can see all documents in that collection. This permission model requires you to set collection permissions on the collection settings page. Document permissions are optional and will not be evaluated even when set.

Active Session Required

For security purposes, an active Account session is required to create resources. So, even if wildcard write permissions are set, only logged-in users can create documents in a collection. If you require this behavior for your app, create an anonymous session first. An active session helps Appwrite enforce rate-control limits and protect your projects from intensive write operations.

Create Attributes

Once you choose your permission model, navigate to your collection's Attributes tab. Attributes are used to define the structure of your documents and help the Appwrite API validate your users' input. Add your first attribute by clicking the Add Attribute button. You can choose between the following types:

Attribute Description
string String attribute.
integer Integer attribute.
float Float attribute.
boolean Boolean attribute.
enum Enum attribute.
ip IP address attribute for IPv4 and IPv6.
email Email address attribute.
url URL attribute.

If an attribute must be present in all documents, set it as required. If not, a default value might be handy. Additionally, decide if the attribute should be a primitive or array of values.

When adding or removing attributes, your requests are processed in the background, indicated by the attribute's status. Depending on your collection's size and other factors, this could take anywhere from a few seconds to a few minutes to complete. You are able to create a document while your attributes are still being processed, however you are not able to use the attributes on the documents until they are available

Create Documents

Navigate to the Documents tab of your collection and click the Add Document button, or add a document programmatically:

  • Web

    import { Appwrite } from "appwrite";
    
    const sdk = new Appwrite();
    const promise = sdk.database.createDocument('[COLLECTION_ID]', 'unique()', {});
    
    promise.then(function (response) {
        console.log(response); // Success
    }, function (error) {
        console.log(error); // Failure
    });
                
  • Flutter

    import 'package:appwrite/appwrite.dart';
    void main() async {
        final client = Client();
        final database = Database(client);
        try {
            final doc = database.createDocument(collectionId: '[COLLECTION_ID]', documentId: 'unique()', data: {});
            print(doc.toMap());
        } on AppwriteException catch(e) {
            print(e);
        }
    }
                
  • Android

    import androidx.appcompat.app.AppCompatActivity
    import android.os.Bundle
    import kotlinx.coroutines.GlobalScope
    import kotlinx.coroutines.launch
    import io.appwrite.Client
    import io.appwrite.services.Database
    
    class MainActivity : AppCompatActivity() {
        override fun onCreate(savedInstanceState: Bundle?) {
            super.onCreate(savedInstanceState)
            setContentView(R.layout.activity_main)
    
            val client = Client(applicationContext)
                .setEndpoint("https://[HOSTNAME_OR_IP]/v1") // Your API Endpoint
                .setProject("5df5acd0d48c2") // Your project ID
    
            val database = Database(client)
    
            GlobalScope.launch {
                val response = database.createDocument(
                    collectionId = "[COLLECTION_ID]",
                    documentId = "unique()",
                    data = mapOf( "a" to "b" ),
                )
                val json = response.body?.string()
            }
        }
    }
  • iOS

    import Appwrite
    
    func main() async throws {
        let client = Client()
          .setEndpoint("https://[HOSTNAME_OR_IP]/v1") // Your API Endpoint
          .setProject("my_project") // Your project ID
    
        let database = Database(client)
        let document = try await database.createDocument(
            collectionId: "[COLLECTION_ID]",
            documentId: "unique()",
            data: [:]
        )
        print(document.toMap())
    }

Querying Documents

Indexes Required

You can only query correctly indexed queries. You can easily add new indexes from both the Appwrite console or one of the server SDKs. Appwrite uses this limitation to enforce optimized queries for maximum performance and scalability of your collection. You can learn more about it on the Appwrite Indexes section.

To find specific documents in a collection, pass an array of query strings as a parameter to the listDocuments endpoint. The SDKs provide a Query class to make query building simpler:

  • Web

    import { Query } from "appwrite";
    
    sdk.database.listDocuments('movies', [
        Query.equal('title', 'Avatar')
    ]);
    
  • 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')]);
            print(docs.toMap());
        } on AppwriteException catch(e) {
            print(e);
        }
    }
    
  • Android

    import androidx.appcompat.app.AppCompatActivity
    import android.os.Bundle
    import kotlinx.coroutines.GlobalScope
    import kotlinx.coroutines.launch
    import io.appwrite.Client
    import io.appwrite.services.Database
    import io.appwrite.Query
    
    class MainActivity : AppCompatActivity() {
        override fun onCreate(savedInstanceState: Bundle?) {
            super.onCreate(savedInstanceState)
            setContentView(R.layout.activity_main)
    
            val client = Client(applicationContext)
                .setEndpoint("https://[HOSTNAME_OR_IP]/v1") // Your API Endpoint
                .setProject("5df5acd0d48c2") // Your project ID
    
            val database = Database(client)
    
            GlobalScope.launch {
                val response = database.listDocuments(
                    collectionId = "[COLLECTION_ID]",
                    queries = [Query.equal('title', 'Avatar')],
                )
                val json = response.body?.string()
            }
        }
    }
  • iOS

    import Appwrite
    
    func main() async throws{
        let client = Client()
          .setEndpoint("https://[HOSTNAME_OR_IP]/v1") // Your API Endpoint
          .setProject("my_project") // Your project ID
    
        let database = Database(client)
        let list = try await database.listDocuments(
            collectionId: "[COLLECTION_ID]",
            queries: [Query.equal('title', 'Avatar')]
        )
        print(list.toMap())
    }

The following operators are currently supported:

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.

Each query string is logically separated via AND. For OR logic, pass multiple values, separated by commas:

  • Web

    sdk.database.listDocuments('movies', [
                Query.equal('title', ['Avatar', 'Lord of the Rings']),
                Query.greater('year', 1999)
            ]);
            
  • 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);
        }
    }
  • Android

    import androidx.appcompat.app.AppCompatActivity
    import android.os.Bundle
    import kotlinx.coroutines.GlobalScope
    import kotlinx.coroutines.launch
    import io.appwrite.Client
    import io.appwrite.services.Database
    import io.appwrite.Query
    
    class MainActivity : AppCompatActivity() {
        override fun onCreate(savedInstanceState: Bundle?) {
            super.onCreate(savedInstanceState)
            setContentView(R.layout.activity_main)
    
            val client = Client(applicationContext)
                .setEndpoint("https://[HOSTNAME_OR_IP]/v1") // Your API Endpoint
                .setProject("5df5acd0d48c2") // Your project ID
    
            val database = Database(client)
    
            GlobalScope.launch {
                val response = database.listDocuments(
                    collectionId = "[COLLECTION_ID]",
                    queries = [
                        Query.equal('title', ['Avatar', 'Lord of the Rings']),
                        Query.greater('year', 1999),
                    ],
                )
                val json = response.body?.string()
            }
        }
    }
  • iOS

    import Appwrite
    
    func main() async throws {
        let client = Client()
          .setEndpoint("https://[HOSTNAME_OR_IP]/v1") // Your API Endpoint
          .setProject("my_project") // Your project ID
    
        let database = Database(client)
        let list = try await database.listDocuments(
            collectionId: "[COLLECTION_ID]",
            queries: [
                Query.equal('title', ['Avatar', 'Lord of the Rings']),
                Query.greater('year', 1999)
            ]
        )
        print(list.toMap())
    }

When performing a query against multiple attributes, a single index with all queries attributes is required. In the example above, a single index with both title and year is queried.

Indexes

Indexes are used by databases to quickly locate data without having to search through every document for results. To ensure the best performance, Appwrite requires an index for every query. You can create an index by navigating to the Indexes tab of your collection or by using your favorite Server SDK. If you plan to query multiple attributes in a single query, you will need an index with all queried attributes.

It should be noted that Appwrite's database was designed to protect your queries from performing a full-table scan as this is a footgun and could cause catastrophic performance degregation as you scale up your Appwrite project.

The following indexes are currently supported:

Type Description
key Plain Index to allow queries.
unique Unique Index which not allows duplicates.
fulltext For searching within string attributes

Appwrite has full support for pagination to better optimize and scale up your applications built on Appwrite. Detailed documentation on pagination and how to implement it can be found on the pagination guide.