Databases

The Databases Service allows you to store your application and users' data and fetch it using different supported queries. Using your Appwrite Databases, you can create multiple databases, organize your data into collections and documents using the Appwrite REST API. You can also use the Appwrite Realtime API to subscribe to live changes in your collections and documents. In addition, the Databases 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 Databases

Appwrite's Databases Service allows you to create multiple databases. Each database can contain many collections and can be backed by a different database adaptor in future versions.

You can create your database by adding it to your Appwrite project's dashboard. Access the Databases Service settings from your project's left-hand navigation panel. To create a new database, click the Add Database button. Name your new database, and optionally provide a custom database ID.

You can also create databases with the Appwrite CLI or the Appwrite Server SDKs.

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.

To add a collection to a database, first navigate to the desired database's dashboard. In the database's dashboard, 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 the Appwrite CLI or one of Appwrite Server SDKs. You can manage documents with both the Server and Client SDKs.

Permissions

Appwrite provides permissions to restrict access to documents at two levels, document and collection level. When a user has the appropriate type of access permissions granted at either the document or the collection level, they will be able to access or change the document. If the permission field is left empty, Client SDKs cannot access the document.

Document Level Permissions

Document level permissions grant access to individual documents. Document level permissions are only applied if Document Security is enabled in the settings of your collection.

Collection Level Permissions

Collection level permissions apply to every document in the collection.

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:

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 { Client, Databases } from "appwrite";
  
  const client = new Client()
      .setEndpoint('https://[HOSTNAME_OR_IP]/v1')
      .setProject('[PROJECT_ID]');
  
  const databases = new Databases(client);
  
  const promise = databases.createDocument(
      '[DATABASE_ID]',
      '[COLLECTION_ID]',
      ID.unique(),
      {}
  );
  
  promise.then(function (response) {
      console.log(response);
  }, function (error) {
      console.log(error);
  });

• Flutter

  import 'package:appwrite/appwrite.dart';
  
  void main() async {
      final client = Client()
          .setEndpoint('https://[HOSTNAME_OR_IP]/v1')
          .setProject('[PROJECT_ID]');
  
      final databases = Databases(client);
  
      try {
          final document = databases.createDocument(
              databaseId: '[DATABASE_ID]',
              collectionId: '[COLLECTION_ID]',
              documentId: ID.unique(),
              data: {}
          );
      } on AppwriteException catch(e) {
          print(e);
      }
  }

• Android

  import io.appwrite.Client
  import io.appwrite.services.Databases
  
  suspend fun main() {
      val client = Client(applicationContext)
          .setEndpoint("https://[HOSTNAME_OR_IP]/v1")
          .setProject("[PROJECT_ID]")
  
      val databases = Databases(client)
  
      try {
          val document = databases.createDocument(
              databaseId = "[DATABASE_ID]",
              collectionId = "[COLLECTION_ID]",
              documentId = ID.unique(),
              data = mapOf("a" to "b"),
          )
      } catch (e: Exception) {
          Log.e("Appwrite", "Error: " + e.message)
      }
  }

• iOS

  import Appwrite
  import AppwriteModels
  
  func main() async throws {
      let client = Client()
          .setEndpoint("https://[HOSTNAME_OR_IP]/v1")
          .setProject("[PROJECT_ID]")")
  
      let databases = Databases(client)
  
      do {
          let document = try await databases.createDocument(
              databaseId: "[DATABASE_ID]",
              collectionId: "[COLLECTION_ID]",
              documentId: ID.unique(),
              data: [:]
          )
      } catch {
          print(error.localizedDescription)
      }
  }

Querying Documents

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 { Client, Databases, Query } from "appwrite";
  
  const client = new Client()
      .setEndpoint("https://[HOSTNAME_OR_IP]/v1")
      .setProject("[PROJECT_ID]")")
  
  const databases = new Databases(client);
  
  let promise = databases.listDocuments(
      "[DATABASE_ID]"
      "[COLLECTION_ID]",
      [
          Query.equal('title', 'Avatar')
      ]
  );
  
  promise.then(function (response) {
      console.log(response);
  }, function (error) {
      console.log(error);
  });
  

• Flutter

  import 'package:appwrite/appwrite.dart';
  
  void main() async {
      final client = Client()
          .setEndpoint("https://[HOSTNAME_OR_IP]/v1")
          .setProject("[PROJECT_ID]")")
  
      final databases = Databases(client);
  
      try {
          final documents = await databases.listDocuments(
              databaseId: '[DATABASE_ID]',
              collectionId: '[COLLECTION_ID]',
              queries: [
                  Query.equal('title', 'Avatar')
              ]
          );
      } on AppwriteException catch(e) {
          print(e);
      }
  }

• Android

  
  import io.appwrite.Client
  import io.appwrite.Query
  import io.appwrite.services.Databases
  
  suspend fun main() {
      val client = Client(applicationContext)
          .setEndpoint("https://[HOSTNAME_OR_IP]/v1")
          .setProject("[PROJECT_ID]")
  
      val databases = Databases(client)
  
      try {
          val documents = databases.listDocuments(
              databaseId = "[DATABASE_ID]",
              collectionId = "[COLLECTION_ID]",
              queries = listOf(
                  Query.equal("title", "Avatar")
              )
          )
      } catch (e: AppwriteException) {
          Log.e("Appwrite", "Error: " + e.message)
      }
  }

• iOS

  import Appwrite
  import AppwriteModels
  
  func main() async throws{
      let client = Client()
          .setEndpoint("https://[HOSTNAME_OR_IP]/v1")
          .setProject("[PROJECT_ID]")
  
      let databases = Databases(client)
  
      do {
          let documents = try await databases.listDocuments(
              databaseId: "[DATABASE_ID]",
              collectionId: "[COLLECTION_ID]",
              queries: [
                  Query.equal("title", "Avatar")
              ]
          )
      } catch {
          print(error.localizedDescription)
      }
  }

The following query methods are currently supported:

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

• Web

  import { Client, Databases, Query } from "appwrite";
  
  const client = new Client()
      .setEndpoint("https://[HOSTNAME_OR_IP]/v1")
      .setProject("[PROJECT_ID]");
  
  const databases = new Databases(client);
  
  databases.listDocuments(
      '[DATABASE_ID]',
      '[COLLECTION_ID]',
      [
          Query.equal('title', ['Avatar', 'Lord of the Rings']),
          Query.greaterThan('year', 1999)
      ]
  );

• Flutter

  import 'package:appwrite/appwrite.dart';
  
  void main() async {
      final client = Client()
          .setEndpoint('https://[HOSTNAME_OR_IP]/v1')
          .setProject('[PROJECT_ID]');
  
      final databases = Databases(client);
  
      try {
          final documents = await databases.listDocuments(
              '[DATABASE_ID]',
              '[COLLECTION_ID]',
              [
                  Query.equal('title', ['Avatar', 'Lord of the Rings']),
                  Query.greaterThan('year', 1999)
              ]
          );
      } on AppwriteException catch(e) {
          print(e);
      }
  }

• Android

  import io.appwrite.Client
  import io.appwrite.Query
  import io.appwrite.services.Databases
  
  suspend fun main() {
      val client = Client(applicationContext)
          .setEndpoint("https://[HOSTNAME_OR_IP]/v1")
          .setProject("[PROJECT_ID]")
  
      val databases = Databases(client)
  
      try {
          val documents = databases.listDocuments(
              databaseId = "[DATABASE_ID]",
              collectionId = "[COLLECTION_ID]",
              queries = listOf(
                  Query.equal("title", listOf("Avatar", "Lord of the Rings")),
                  Query.greaterThan("year", 1999)
              )
          )
      } catch (e: AppwriteException) {
          Log.e("Appwrite", e.message)
      }
  }

• iOS

  import Appwrite
  import AppwriteModels
  
  func main() async throws {
      let client = Client()
          .setEndpoint("https://[HOSTNAME_OR_IP]/v1")
          .setProject("[PROJECT_ID]")
  
      let databases = Databases(client)
  
      do {
          let documents = try await databases.listDocuments(
              databaseId: "[DATABASE_ID]",
              collectionId: "[COLLECTION_ID]",
              queries: [
                  Query.equal("title", ["Avatar", "Lord of the Rings"]),
                  Query.greaterThan("year", 1999)
              ]
          )
      } catch {
          print(error.localizedDescription)
      }
  }

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

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 degradation as you scale up your Appwrite project.

The following indexes are currently supported:

Ordering Results

When querying using the listDocuments endpoint, you can specify the order of the documents returned using the "Query.orderAsc()" and "Query.orderDesc()" query methods.

• Web

  import { Client, Databases, Query } from "appwrite";
  
  const client = new Client()
      .setEndpoint("https://[HOSTNAME_OR_IP]/v1")
      .setProject("[PROJECT_ID]");
  
  const databases = new Databases(client);
  
  databases.listDocuments(
      '[DATABASE_ID]',
      '[COLLECTION_ID]',
      [
          Query.orderAsc('title'),
      ]
  );

• Flutter

  import 'package:appwrite/appwrite.dart';
  
  void main() async {
      final client = Client()
          .setEndpoint("https://[HOSTNAME_OR_IP]/v1")
          .setProject("[PROJECT_ID]");
  
      final databases = Databases(client);
  
      try {
          final documents = await databases.listDocuments(
              databaseId: '[DATABASE_ID]',
              collectionId: '[COLLECTION_ID]',
              queries: [
                  Query.orderAsc('title')
              ]
          );
      } on AppwriteException catch(e) {
          print(e);
      }
  }

• Android

  import io.appwrite.Client
  import io.appwrite.Query
  import io.appwrite.services.Databases
  
  suspend fun main() {
      val client = Client(applicationContext)
          .setEndpoint("https://[HOSTNAME_OR_IP]/v1")
          .setProject("[PROJECT_ID]")
  
      val databases = Databases(client)
  
      try {
          val documents = databases.listDocuments(
              databaseId = "[DATABASE_ID]",
              collectionId = "[COLLECTION_ID]",
              queries = [
                  Query.orderAsc("title")
              ]
          )
      } catch (e: AppwriteException) {
          Log.e("Appwrite", e.message)
      }
  }

• iOS

  import Appwrite
  import AppwriteModels
  
  func main() async throws {
      let client = Client()
        .setEndpoint("https://[HOSTNAME_OR_IP]/v1")
        .setProject("[PROJECT_ID]")
  
      let databases = Databases(client)
  
      do {
          let documents = try await databases.listDocuments(
              databaseId: "[DATABASE_ID]",
              collectionId: "[COLLECTION_ID]",
              queries: [
                  Query.orderAsc("title")
              ]
          )
      } catch {
          print(error.localizedDescription)
      }
  }

To sort based on multiple attributes, pass multiple attribute IDs into orderAttributes. The order of the attribute IDs indicate their sort precedence. There must be an index with all attributes used to allow sorting with multiple attributes.

• Web

  import { Client, Databases, Query } from "appwrite";
  
  const client = new Client()
      .setEndpoint("https://[HOSTNAME_OR_IP]/v1")
      .setProject("[PROJECT_ID]");
  
  const databases = new Databases(client);
  
  databases.listDocuments(
      '[DATABASE_ID]',
      '[COLLECTION_ID]',
      [
          Query.orderAsc('title'), // Order by title in ascending order
          Query.orderDesc('year'), // Order by year in descending order
      ]
  );

• Flutter

  import 'package:appwrite/appwrite.dart';
  
  void main() async {
      final client = Client()
          .setEndpoint("https://[HOSTNAME_OR_IP]/v1")
          .setProject("[PROJECT_ID]");
  
      final databases = Databases(client);
      try {
          final documents = await databases.listDocuments(
              databaseId: '[DATABASE_ID]',
              collectionId: '[COLLECTION_ID]',
              queries: [
                  Query.orderAsc('title'), // Order by title in ascending order
                  Query.orderDesc('year')  // Order by year in descending order
              ]
          );
      } on AppwriteException catch(e) {
          print(e);
      }
  }

• Android

  import io.appwrite.Client
  import io.appwrite.Query
  import io.appwrite.services.Databases
  
  suspend fun main() {
      val client = Client(applicationContext)
          .setEndpoint("https://[HOSTNAME_OR_IP]/v1")
          .setProject("[PROJECT_ID]")
  
      val databases = Databases(client)
  
      try {
          val documents = databases.listDocuments(
              databaseId = "[DATABASE_ID]",
              collectionId = "[COLLECTION_ID]",
              queries = [
                  Query.orderAsc("title"), // Order by title in ascending order
                  Query.orderDesc("year")  // Order by year in descending order
              ]
          )
      } catch (e: AppwriteException) {
          Log.e("Appwrite", e.message)
      }
  }

• iOS

  import Appwrite
  import AppwriteModels
  
  func main() async throws {
      let client = Client()
        .setEndpoint("https://[HOSTNAME_OR_IP]/v1")
        .setProject("[PROJECT_ID]")
  
      let databases = Databases(client)
  
      do {
          let documents = try await databases.listDocuments(
              databaseId: "[DATABASE_ID]",
              collectionId: "[COLLECTION_ID]",
              queries: [
                  Query.orderAsc("title"), // Order by title in ascending order
                  Query.orderDesc("year")  // Order by year in descending order
              ]
          )
      } catch {
          print(error.localizedDescription)
      }
  }

In the example above, the movies returned will be first sorted by title in ascending order, then sorted by year in descending order.

Pagination

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.