Go Limitless with New Appwrite Queries

Appwrite is an open-source backend-as-a-service that abstracts all the complexity involved in building a modern application by providing you with a set of REST APIs for your core backend needs. Appwrite handles user authentication and authorization, real-time databases, cloud functions, webhooks, and much more!

What’s even more amazing is that we recently became number 1! 🥳 Long-awaited 1.0 release brings amazing features to make web and app development even more FUN. One of them being a new query syntax that brings numerous possibilities, but first, let’s understand what it means to query.

🔍 What Is a Query?

The database is a critical part of any application. It’s a place where all the information generated by users is stored. Storing data is important but only useful if you can read the data. In the end, who would use an app where they can create a profile but never look at it? 🤔

It’s as simple as that! Querying is the process of reading the data of your application. Queries can be as simple as “Give me all you got”, but can also get robust with many conditions, sorting operations, or pagination.

Show me what you got meme

Let’s see a query in action! For this example, I will use SQL (Structured Query Language), which lets us read data from a database by writing a human-like sentence.

Let’s start with a simple query to get all JavaScript frameworks in the world:

SELECT * FROM frameworks;

This query could serve all of our needs but would get extremely slow as our database starts to grow. Let’s improve the query by filtering the results and only get popular frameworks:

SELECT * FROM frameworks WHERE popularity > 0.9;

We might still be getting thousands of results, but we never show that on the website, right? Let’s only get the first 10 results:

SELECT * FROM frameworks WHERE popularity > 0.9 LIMIT 10;

Finally, let’s make sure to show the most popular frameworks first:

SELECT * FROM frameworks WHERE popularity > 0.9 ORDER BY popularity DESC LIMIT 10;

We just built a basic query! 💪 Let’s now take this knowledge and build some queries that we can use to read data from Appwrite Database.

🖋️ Appwrite Query Syntax

Let's start by taking a look at filter queries. These queries are meant to reduce the amount of results by checking if a condition is met regarding each document. Appwrite supports many filter queries that let you compare against a word, text, number or booleans. Following is a table of all available filter queries and an example of how such a query could look like in SQL world to make understanding easier:

Appwrite Query	SQL Query
Query.equal("role", "Developer")	WHERE role = 'Developer'
Query.equal("role", [ "Developer", "Designer" ])	WHERE role = 'Developer' OR role = 'Designer'
Query.notEqual("category", "Flutter")	WHERE category != "Flutter"
Query.lessThan("age", 100)	WHERE age < 100
Query.lessThanEqual("age", 100)	WHERE age <= 100
Query.greaterThan("balance", 4.99)	WHERE balance > 4.99
Query.greaterThanEqual("balance", 4.99)	WHERE balance >= 4.99
Query.search("content", "phone number")	WHERE MATCH(content) AGAINST('phone number' IN BOOLEAN MODE)

Next you can take advantage of sort queries that lets you order results of a query in a specific way. You can see this feature on all eshops that let you sort products by popularity, price or reviews. Following queries are available in Appwrite:

Appwrite Query	SQL Query
Query.orderAsc("price")	ORDER BY price ASC
Query.orderDesc("$createdAt")	ORDER BY createdAt DESC

Last but not least, you can write pagination queries. There are different ways of paginating over your database that you can learn more in our Database Paginations article. In short, we could do offset pagination using limit and offset queries, or cursor pagination using limit and cursor. All of that is possible in Appwrite using following queries:

Appwrite Query	SQL Query
Query.limit(10)	LIMIT 10
Query.offset(30)	OFFSET 30
Query.cursorAfter("documentId15")	WHERE id > 15
Query.cursorBefore("documentId50")	WHERE id < 50

🧰 Querying Any Appwrite Service

If you used Appwrite before, you might have noticed that all of the above features were already available, just in a different syntax. So… Why the change?

✨ Consistency ✨

These features were only available in some services! 😦 Our mission was to implement queries into all list methods (where it makes sense), but that would be pain for you to learn, and pain for us to maintain. Thanks to Queries syntax Appwrite now offers a consistent interface to query any resource in an exactly the same way. Let’s see it in action!

import { Client, Databases, Query } from 'appwrite';
const client = new Client();
client
    .setEndpoint('https://[HOSTNAME_OR_IP]/v1')
    .setProject('PROJECT_ID]');
const database = new Databases(client);

// Get products from eshop database that are published and cost below 50$. Ordered by price to get most expensive first on third page, getting 10 items per page
const productsList = await database.listDocuments('eshop', 'products', [
    Query.equal("published", true),
    Query.lessThan("price", 49.99),
    Query.limit(10),
    Query.offset(20),
    Query.orderDesc("price")
]);

// Get up to 50 disabled collections in eshop database
const collectionsList = await database.listCollections('eshop', [
    Query.equal("enabled", false),
    Query.limit(50)
]);

// Get database by name
const databasesList = await database.list([
    Query.equal("name", 'eshop'),
    Query.limit(1)
]);

Let’s see a few more examples with different services 😇

const storage = new Storage(client);

// Get all JPEG or PNG files ordered by file size
const filesList = await storage.listFiles('productPhotos', [
    Query.equal("mimeType", [ "image/jpeg", "image/png" ]),
    Query.orderAsc("sizeActual")
]);

const functions = new Functions(client);

// Get failed executions that were triggered by http request and lasted at least 5 seconds
const executionsList = await functions.listExecutions('createOrder', [
    Query.equal("status", "failed"),
    Query.equal("trigger", "http"),
    Query.greaterThanEqual("time", 5) // in seconds
]);

const teams = new Teams(client);

// Get 5th biggest team that has at least 100 members
const teamsList = await teams.list([
    Query.greaterThan("total", 100),
    Query.orderDesc("total"),
    Query.limit(1),
    Query.offset(5)
]);

const users = new Users(client);

// Find all Johns that verified their email address
const usersList = await users.list([
    Query.equal("emailVerification", true),
    Query.search("name", "John")
]);

As you can see, possibilities are limitless! 🤯 What’s more, the new syntax opens doors for new exciting features such as join, specific selects, subqueries, and new operators. We will continue working and making the Appwrite database more flexible and closer to the under the hood database being used, whether it’s MySQL, MariaDB, or in the future, MongoDB.

👨‍🎓 Conclusion

New Appwrite queries syntax introduced in 1.0 brings long-awaited consistency across all Appwrite services. This not only eases a learning curve of Appwrite, but also introduces new possibilities to allow you to create even more amazing applications!

📚 Learn more

You can use the following resources to learn more and get help:

Go Limitless with New Appwrite Queries

Comments

More from this blog

I Found Perfect CMS after Years of Trial and Error

Auth UI, Universal Auth Service

Appwrite Loves Open Source: Why I Chose To Sponsor Offen

Appwrite Hand-In-Hand with Svelte Kit (SSR)

🔍 What Is a Query?

🖋️ Appwrite Query Syntax

🧰 Querying Any Appwrite Service

👨‍🎓 Conclusion

📚 Learn more

Command Palette

Comments

More from this blog

🔍 What Is a Query?

🖋️ Appwrite Query Syntax

🧰 Querying Any Appwrite Service

👨‍🎓 Conclusion

📚 Learn more