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 will 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:
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 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 { 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) } }
-
Apple
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) } }
-
GraphQL
mutation { databasesCreateDocument( databaseId: "[DATABASE_ID]", collectionId: "[COLLECTION_ID]", documentId: "[DOCUMENT_ID]", data: "{}" ) { _id _collectionId _databaseId _createdAt _updatedAt _permissions data } }
Querying Documents
Indexes Required
You can only query indexed attributes. You can easily add new indexes from both the Appwrite console or any 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 in 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 { 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) } }
-
Apple
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) } }
-
GraphQL
query { databasesListDocuments( databaseId: "[DATABASE_ID]", collectionId: "[COLLECTION_ID]" queries: ["equal(\"title\", [\"Avatar\"])"] ) { total documents { _id data } } }
The following query methods are currently supported:
Query Method | SDK Method Example | Description |
---|---|---|
equal | Query.equal("title", ["Iron Man"]) | Returns document if attribute is equal to any value in the provided array. Applies to any indexed attribute. |
notEqual | Query.notEqual("title", ["Iron Man"]) | Returns document if attribute is not equal to any value in the provided array. Applies to any indexed attribute. |
lessThan | Query.lessThan("score", 10) | Returns document if attribute is less than the provided value. Applies to any indexed attribute. |
lessThanEqual | Query.lessThanEqual("score", 10) | Returns document if attribute is less than or equal to the provided value. Applies to any indexed attribute. |
greaterThan | Query.greaterThan("score", 10) | Returns document if attribute is greater than the provided value. Applies to any indexed attribute. |
greaterThanEqual | Query.greaterThanEqual("score", 10) | Returns document if attribute is greater than or equal to the provided value. Applies to any indexed attribute. |
search | Query.search("text", "key words") | Searches string attributes for provided keywords. Applies to any string attribute with a full-text index. |
orderDesc | Query.orderDesc("attribute") | Orders results in descending order by attribute. Attribute must be indexed. Pass in an empty string to return in natural order. |
orderAsc | Query.orderAsc("attribute") | Orders results in ascending order by attribute. Attribute must be indexed. Pass in an empty string to return in natural order. |
limit | Query.limit(25) | Limits the number of results returned by the query. Used for pagination. |
offset | Query.offset(0) | Offset the results returned by skipping some of the results. Used for pagination. |
cursorAfter | Query.cursorAfter("62a7...f620") | Places the cursor after the specified resource ID. Used for pagination. |
cursorBefore | Query.cursorBefore("62a7...a600") | Places the cursor before the specified resource ID. Used for pagination. |
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) } }
-
Apple
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) } }
-
GraphQL
query { databasesListDocuments( databaseId: "[DATABASE_ID]", collectionId: "[COLLECTION_ID]" queries: ["equal(\"title\", [\"Avatar\", \"Lord of the Rings\"])", "greaterThan(\"year\", 1999)"] ) { total documents { _id data } } }
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:
Type | Description |
---|---|
key | Plain Index to allow queries. |
unique | Unique Index to disallow duplicates. |
fulltext | For searching within string attributes |
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) } }
-
Apple
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) } }
-
GraphQL
query { databasesListDocuments( databaseId: "[DATABASE_ID]", collectionId: "[COLLECTION_ID]" queries: ["orderAsc(\"title\")"] ) { total documents { _id data } } }
To sort based on multiple attributes, simply provide multiple 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'), // 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) } }
-
Apple
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) } }
-
GraphQL
query { databasesListDocuments( databaseId: "[DATABASE_ID]", collectionId: "[COLLECTION_ID]" queries: ["orderAsc(\"title\")", "orderDesc(\"year\")"] ) { total documents { _id data } } }
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.