🗯️ Let's talk about Appwrite's queries

[Meldiron]

I included "EDIT 1.0" at the end with community suggestions. Didn't touch the original text.

👋 helloWorld()

We noticed some limitations in our current querying system and we wanted to hear your opinions on this topic 😇 We plan to introduce these changes in Appwrite 0.16, which is just around the corner! To learn more about the current implementation, visit our docs: https://appwrite.io/docs/databases#querying-documents

🤖 Too Many Arguments

We plan to remove arguments:

• limit & offset
• cursor & cursorDirection
• orderAttributes & orderType

All of these capabilities will remain in the endpoint but will be achieved by a query inside the queries array attribute. We decided to do this in order to lower the number of arguments (making docs a bit simpler), and also improve code quality in languages that don't support named parameters.

This is what your current JavaScript call might look like:

const database = new Databases(client, "production");

database.listDocuments(
  "profiles",
  [], // Empty array, okay
  10, // What is 10?
  30, // Why 30?!?
  undefined, // Why
  undefined, // Why.. Why..
  ["$updatedAt"], // And how are these two related?!?
  ["ASC"]
);

With a change to parameters, we will move from 9 parameters to only 3 and our query could look like this:

const database = new Databases(client, "production");

database.listDocuments("profiles", [
  Query.limit(10), // Nooow I see what those nubers are
  Query.offset(30),
  Query.orderByAsc("updatedAt"), // Nooow I see what these two weird arrays meant
]);

🧹 Inconsistency Between Endpoints

listDocuments() on databases is the only powerful endpoint with many querying capabilities. listUsers(), for example, doesn’t accept queries, accepts search, and sorting functionality is limited. Team memberships have "get" instead of "list", and function executions can't even be sorted. A bit messy 😬

We plan to remove all of these attributes and make sure to introduce one new attribute queries to all of the internal endpoints too. This will allow us to keep everything consistent and give you much more possibilities with querying internal tables (users, teams, functions...).

Regarding internal collections, we will come up with a list of the most useful queries, and prepare indexes for them - to make them fast. We will also allow unindexed list operations to make sure we are not limiting users in any way. At least not at this stage.

🧹 Inconsistency Between SDKs and HTTP

As of right now, you build a query in SDK using Query.equal("nick", ["Meldiron", "Eldad"]), but over HTTP query is sent as nick.equal("Meldiron, "Eldad"). This means you not only need to learn SDK syntax, but you also need to learn HTTP syntax if you ever want to use that. Terrible! 😔

We plan to keep the same syntax on both SDK and HTTP. You noticed how SDK can either do 1 option (equal("nick", "Meldiron")) or multiple as array (equal("nick", ["Meldiron", "Eldad"]))? We plan to build the same syntax support into HTTP too - so you can use both syntaxes with HTTP communication.

Here are some examples of what it will look like:

SDK	HTTP
Query.equal("nick", "Meldiron")	equal("nick", "Meldiron")
Query.equal("age", [15,16,17])	equal("age", [15,16,17])

🤨 What Is lesser?!?

We looked at some terms we used and decided to rename them to proper English:

Before	After
lesser("age", 15)	less("age", 15)
lesserEqual("age", 100)	lessEqual("age", 100)
greater("likes", 1000)	greater("age", 15)
greaterEqual("likes", 500)	greaterEqual("age", 100)
search("content", "Deno")	search("content", "Deno")

Shiny new query methods

Do you remember we removed some arguments from the list endpoint? This is how you achieve the same logic with new queries:

SDK
Query.orderDesc("name")
Query.orderAsc("$createdAt")
Query.limit(10)
Query.offset(50)
Query.cursorAfter("myid1234")
Query.cursorBefore("myid1234")

Some important terms:

• Query method is a database operation you do during data selection
• Filter query methods are those that pick results to return (in SQL it would be WHERE)
• Order query methods are those that change the order of results (in SQL it would be ORDER BY)

What we did:

• If you provide more versions of methods, it will use the last one (like limit(10) and then limit(15))
• If you provide more versions of the order method (orderAsc/orderDesc), they will all be used in order of how you provided them
• If you provide more versions of the filter method, (lesser,greater...) , they will all be used with AND in between

A possibility is to also start introducing aliases when they become necessary. A good example could be a pagination query:

Query	Alias
[ Query.limit(10), Query.offset((pageToLoad-1)*10) ]	Query.page(pageToLoad, 10)

Another candidate for aliases are filter queries, since they can get pretty long:

Query	Alias
Query.less("age", 22)	Query.lt("age", 22)
Query.lessEqual("age", 22)	Query.lte("age", 22)
Query.greater("age", 22)	Query.gt("age", 22)
Query.greaterEqual("age", 22)	Query.gte("age", 22)

📚 Update All Resources

Documentation needs to be updated to reflect this. First of all, the "queries" section in "databases" should now only talk about how queries affect indexes and via-verse.

Next, we should have the "queries" section where we explain the syntax and all possibilities. Of course, with code examples. If we decide to implement some aliases, all of them must be documented and what they are aliases of.

We should also look at "ordering" docs, and explain how to do compound sorting since the new syntax is different.

Following is a list of all query methods and expected parameters:

type Value = string | number | boolean;

class Query {
  equal(attribute: string, value: Value | Value[]): string;
  search(attribute: string, value: Value | Value[]): string;

  less(attribute: string, value: Value | Value[]): string;
  lessEqual(attribute: string, value: Value | Value[]): string;
  greater(attribute: string, value: Value | Value[]): string;
  greaterEqual(attribute: string, value: Value | Value[]): string;

  orderDesc(attribute: string): string;
  orderAsc(attribute: string): string;

  cursorAfter(attribute: string): string;
  cursorBefore(attribute: string): string;

  limit(amount: number): string;
  offset(amount: number): string;

  // Aliases
  page(page: number, limit: number);
  gt(attribute: string, value: Value | Value[]): string;
  gte(attribute: string, value: Value | Value[]): string;
  lt(attribute: string, value: Value | Value[]): string;
  lte(attribute: string, value: Value | Value[]): string;
}

Above is just a pseudo-code

🔮 Future of Queries

As seen from recent updates, we are trying to improve each and every service of Appwrite. We take all of your suggestions into consideration and actively talk about them. Although we can't promise a release date yet, we find it important to always think about your suggestions.

In the future, we plan to see how join could be introduced into Appwrite. Here is a sneak peek of what a join query might look like using new Appwrite query methods:

database.listDocuments("books", [
  Query.leftJoin("author", [
    Query.equal("authorId", ["authors.$id"]),
    Query.less("myAttribute", ["joinedTable.joinedAttribute"]),
  ]),
  Query.leftJoin("publisher", [Query.equal("publisherId", ["published.$id"])]),
  Query.equal("author.banned", false),
]);

We also actively think about geo queries being built into Appwrite. Such a geo query could look like a simple filter query method:

SDK
Query.geoSphere("userGeoPoint", 5) // in meters
Query.geoPolygon("storeGeoPoint", [cityPoint1, cityPoint2, cityPoint3])
Query.geoPolygon("storeGeoPolygon", [cityPoint1, cityPoint2, cityPoint3])

Then, we looked at responses and decided a new type of query method might be useful in the future:

• Select query methods are those that change the content of results (in SQL it would be SELECT)

You could not only specify what attributes you are interested in, but also could do operations above groups of data:

const response = database.listDocuments("books", [
    Query.select("name"),
    Query.select("email"),
    Query.select(["$id", "verified"]),
    Query.average("averageCreate", "$createdAt"),
    Quey.sum("totalSum", "$id"),
    Query.distinct(["firstName", "lastName"]),
]);

Last but not least, we thought about a small improvement to NULL queries, and were thinking about adding a new filter query method:

SDK
Query.equalNull("score")
Query.equal("score", null)

---

Now it's your time to shine! ✨

Do you have any feedback regarding these topics? Do you find new query syntax cool or complex? Do you agree with all of our ideas, or dislike some? Can you see some use cases that are not covered by this implementation?

Whatever is on your mind, let's chat about it.

---

EDIT 1.0

• Query.equal("name", null) seems to be more developer-friendly than Query.equalNull("name")
• Regarding aliases, we missed eq() as equal() shorthand

[stnguyen90]

For nulls I prefer Query.equal("score", null) so that the autocomplete for Query doesn't get even longer and it still makes sense.

[fogelito]

If under the hood we can change the syntax to "is null" so no problem.
or have an Operator of Query.isNull or Query.isNotNull?

[Meldiron]

Thanks for the feedback. Good point, noting it down..

[stnguyen90]

What about not equal? 😈

[Meldiron]

Looking at docs seems like we already support that. So we should not have an issue there.

[gnotus]

One to many and many to many is it already possible?

[stnguyen90]

Joins/relationships are currently not supported. Right now, you would manually take care of relationships client side.

[stnguyen90]

How about alias for equal too? eq?

[eldadfux]

Could be a nice idea to have consistent aliases, might make autocompletion longer as you suggested. 🤔

[Meldiron]

100% agree, great catch!

[squallsama]

Would like to have some kind of join filters between collections. Also some aggregations would be nice to have.

[stnguyen90]

We probably won't have this for 0.16, but you can see these things listed in the "Future of Queries" section.

[pspierce]

If "Future of Queries" comes true, it will put to rest my longing for graphQL.

[CordMemescape]

+1 for being able to query where an attribute is null/not null.

[fogelito]

Definitely a good Idea!

[CordMemescape]

By correcting the "Inconsistency Between SDKs and HTTP ", would AND/OR still be the same? For example,

Query.equal("firstname", ["Robert", "Bob"])
Query.equal("surname", ["Smith"])

be the equivalent of WHERE (firstname = 'Robert' OR firstname = 'Bob') AND surname = 'Smith'?

[eldadfux]

Actually, internally we use IN operator which is about ~x3 times faster. You can learn more about that here:
https://stackoverflow.com/questions/782915/mysql-or-vs-in-performance

[CordMemescape]

Thanks for clarifying. Yes, I'm aware of IN and its performance benefits but was just trying to keep my example simple. Glad to hear that you're using IN internally though!

[yumin-chen]

Good point, what about OR?

If you provide more versions of the filter method, (lesser,greater...) , they will all be used with AND in between

[Meldiron]

I might have been misleading in the description, here are some examples:

• [ Query.equal("name", "Matej") ] would be check for name = "Matej"
• [ Query.equal("name", ["Matej", "Eldad"]) ] would be check for name IN ("Matej", "Eldad") (like OR between options)
• [ Query.equal("name", "Matej"), Query.equal("age", 20) ] would be check for name = "Matej" AND age = 20 (like AND between queries)
• [ Query.equal("name", ["Matej", "Eldad"]), Query.equal("age", 20, 50) ] would be check for name IN ("Matej","Eldad") AND age IN (20,50) (like AND between queries, but OR between array of allowed options)

[yumin-chen]

Any examples of how to construct a OR between queries? Something like updatedAt IS NULL OR (updatedAt < NOW() AND updatedAt > NOW() - YEAR(1)) ?

[CordMemescape]

Would Query.equal("surname", ["Smith"]) return just records where the surname is Smith (i.e. exact match), or would it be equivalent to WHERE surname LIKE 'Smith%'?

Also, would case matter when creating a query? i.e. would "Smith" and "smith" be considered different?

[Meldiron]

Regarding MariaDB adapter, find method looks like: https://github.com/utopia-php/database/blob/main/src/Database/Adapter/MariaDB.php#L838
While operator can be: https://github.com/utopia-php/database/blob/f9b2596fec5e90d105ad2749ff0256e9003d88a0/src/Database/Adapter/MariaDB.php#L1409

That means, the query would be WHERE surname = 'Smith'. From the playground I tried it with, looks like it is case-insensitive:

What you try to achieve with LIKE can be done with Query.search(), described in code here: https://github.com/utopia-php/database/blob/f9b2596fec5e90d105ad2749ff0256e9003d88a0/src/Database/Adapter/MariaDB.php#L1393
There the query would be MATCH(surname) AGAINST('Smith*' IN BOOLEAN MODE).

Using the same playground, that also seems to be case-insensitive:

[fogelito]

Correct!
That is because we are using a fulltext index..this is like an autocomplete when we add the * at the end ?
I think it would be nice to have an option of fulltext search without the "*" for full words search?

and yes we are using utf8mb4_general_ci The "I" is for case Insensitive :)

[fogelito]

There is a difference if you have more the 1 terms in search attributes for example "Donald Duck"
With Appwrite search if you query for "Duck" you will find the Document, with "like%" you will not!

[BenMemescape]

Is it possible to drop the requirement to have an index set up for every possible combination of attributes that you might ever want to query? I don't understand why that's necessary, and it's a major annoyance and must surely be slowing down document create/update operations, for little gain if a particular combo is only rarely used. It should surely be sufficient to have indexes on each individual attribute, except in the case that you want to enforce a unique key across a combo.

[eldadfux]

While sometimes annoying, these limitations also help to encourage you to write very well-performing queries, but as always, this is not perfect for any use-case, and we're big believers in flexibility and keep Appwrite as un-opinionated as we can.

We're researching ways to make this requirement more flexible. Because the default Appwrite setup offers a shared database, we have to force those indexes to protect other tenants/projects from bad-performing queries that can affect the general performance of the instance.

Besides researching how this can become more flexible with the latest Appwrite release (0.15), we prepared the ground to have multiple DB adapters. This would potentially allow us to help devs connect dedicated and managed DBs instances for each project. We could allow more freedom for both queries and indexes when using a dedicated instance.

[fogelito]

This could be waste of resource since a lot of attributes are just for information and not for queries.

[BenMemescape]

@fogelito I presume your comment was about "It should surely be sufficient to have indexes on each individual attribute". I am of course only referring to those used in queries.

@eldadfux I would have thought most people will be using a dedicated Appwrite instance per project, rather than sharing a single one across multiple projects with different tenants. A few may be selling an Appwrite service to others (in which case there are bigger issues about how to isolate users from each other) but surely most are using their own Appwrite instance for their own projects only. Besides which, this "protection from bad-performing queries" backfires when there is a particular combination of attributes that is only queried rarely, because the performance penalty on create/update might exceed the benefit on the query. Therefore the decision to have an index or not should be left to the developer, as it is with every RDBMS.

[XanManZA]

Feedback On Proposals:
When talking about Queries, why would one have the aliases "read" lessThanEqual and greaterThan etc, but have the full signatures be less or greater?
At the end of the day, we're building a API that needs to be consumed, so being more verbose is always the better option. Most users are going to start typing and then tab to autocomplete anyway. I would have the full signatures then be:

class Query {
  ...
  lessThan(attribute: string, value: Value | Value[]): string;
  lessThanEqual(attribute: string, value: Value | Value[]): string;
  greaterThan(attribute: string, value: Value | Value[]): string;
  greaterEqualThan(attribute: string, value: Value | Value[]): string;
  ...
}

Want/Need
For joins, what would the resulting RecordSet or Documents look like? That question is not answered. This is a Document database at the end of the day, so when we say "join" do we mean "aggregate relationships" ?

i.e.

database.listDocuments("books", [
  Query.leftJoin("author", [])
  ]);

should return (1)

{
  "bookId": "string",
  "name": "string",
  "author": {
    "name": "string"
  }
}

not (2)

{
  "bookId": "string",
  "name": "string",
  "author.name": "string"
}

I'm assuming option (1) is going to happen anyway, but it might seem a bit conflicting. From the SQL perspective, a join denormalises the data into the same "table".
What can maybe happen is either rename join queries to Query.aggregate or Query.append. The leftJoin or join language might be confusing for the same reason that Document databases don't use that language. My 2 cents.

[getBoolean]

I agree the aliases are confusing.

Is lessEqual less or equal, or less than equal? It needs to be more verbose, not less.

I don't think aliases should be added for these, it will severely impact readability

[Meldiron]

We had an internal discussion about this exactly before releasing a discussion 😅 You provided good points for why including than makes sense. Will note it down.

Regarding joins, I like your suggestion! @eldadfux what do you think? Do you like it? Should we update our join query with this new syntax, and include possible response example?

[eldadfux]

The terms could be a bit confusing, but the join operation will be the under-the-hood operation we'll be using to achieve this functionally. Our idea is to try and expose as much under-the-hood capabilities of the specific database. Appwrite is using collection/documents/attributes the noSQL terms because those are more compatible when used with the JSON REST API Appwrite exposes.

Internally we adapt all operation to work well with either MySQL, MariaDB or MongoDB (soon) and achieve max performance with native capabilities. We can definitely think more to try and provide agnostic terms, but this might also suggest that all methods will be supported in all database adapters, which might not be true. What would be the best noSQL alternative for a join?

[XanManZA]

I think one direction needs to be chosen. Either the engineer is working with SQL or NoSQL. No use trying to reinvent the wheel or do something hybrid. So if the whole idea in the Appwrite "Database" is to work with "tables" and "SQL" then the terminology needs to reflect that. Meaning it would not be Appwrite collections or documents (which it currently is).

If we choose the NoSQL route, we are going to make the experience much more concise if we reflect what the operations do - Adding a field/attribute to a JSON response is fundamentally not a join operation.
In MongoDB there is $lookup which appends a data field to the json response with data from another collection.

I understand that the lines are a bit blurred because collections in the Appwrite perspective is not fully JSON-compliant objects, but still. When I expect a JSON response (as we do from the Database API) I am "appending" or "aggregating" properties, not joining them unto the main json object.

It's probably up to the community as well at the end of the day. join might be less precise (and maybe even incorrect) but if that's what people understand better it is what it is.

[ctwhome]

why not fully SQL databases and leave out no-sql?. Are there any advantages of using mongo instead of SQL in terms of performance? If the database technology is under the hood, I would just pick one db and optimize that for 1.0. maybe later can be added more database. Btw, I would choose PostgresSQL :)

[eldadfux]

equalNull still feels a bit weird compared to the more common isNull. Anyone has any thoughts about this? @gewenyu99 @Meldiron @fogelito

[fogelito]

if Qeury.equal make more sense.. we can see that somebody is querying for null and under the hood change the query for any syntax we want?
for me Qeury.isNull("attr") makes sense :)

[CordMemescape]

Query.isNull('attribute') and Query.isNotNull('attribute') would make a lot of sense to me but it does break from the convention that's trying to be established with the proposed changes, namely Query.equal('attribute', 'value'). I would be happy to use Query.equal('attribute', 'null') and Query.notEqual('attribute', 'null').

[eldadfux]

I think Query.equal('attribute', 'null') could work, but I would remove the quotes: Query.equal('attribute', null)

[Meldiron]

^ Same was suggested in other comments. I will mark down Query.equal('attribute', null).

[petteremil]

Bit of a random idea, and it is solvable, but could maybe be a nice to have built in; a reverse limit;

Say you order the data by $created asc, and you want the last 50 records added, in asc order.
you could probably get the total count of records, and then configure the offset accordingly with a limit of 50; or do $created desc, get top 50 and reverse the data; but that adds some faffing about, but if would be pretty cool to be able to do a "reverseLimit(50)" or something to start at the other end.

Just a thought, not something I need right now, but I can imagine it could be a useful shorthand for messaging apps, getting updates and so on.

[Meldiron]

My initial thoughts about this:

• Wouldn't it make understanding cursor pagination in this reverseLimit much harder? (what IDs to pass to get next/previous page)
• Can't this be simply achieved client-side by doing const messages = response.documents.reverse();?

[yumin-chen]

Can we support a unified negation interface .not on queries like equal, less, null etc?

Positive	Negated
Query.equal("age", 15)	Query.not.equal("age", 15)
Query.lessThan("age", 100)	Query.not.lessThan("age", 100)
Query.greaterThan("likes", 1000)	Query.not.greaterThan("age", 15)
Query.null("likes")	Query.not.null("likes")

[yumin-chen]

What about putting all the operators for comparison under a group called .is?

Positive	Negated
Query.is.equal("age", 15)	Query.is.not.equal("age", 15)
Query.is.less("age", 100)	Query.is.not.less("age", 100)
Query.is.greater("likes", 1000)	Query.is.not.greater("age", 15)
Query.is.null("likes")	Query.is.not.null("likes")

These operators are different from limit, order, offset, etc.. They're used for different purposes, and only the comparison operators are negatable. I think it'd make sense to group them together.

Plus, this kind of API works well with IDE's auto-completion -- When you type in Query.is., it'd list all operators under is.

[CordMemescape]

I don't really see the point of introducing a .not() or .is.not(). If there is already a Query.equal() and a Query.notEqual(), surely this is already covered? Where you're comparing something as being .is.not.less() or .is.not.greater() surely you can just invert the query? So Query.is.not.less('age', 100) could be written as Query.greaterEqual('age', 100), for example. For me, the latter is easier to read and understand whereas the former seems less clear.

@yumin-chen, can you help me understand what I might be missing in your suggestion?

[yumin-chen]

OK, I didn't see notEqual method being covered that's why I raised this. Yeah, a notEqual should suffice.

[yumin-chen]

What about search? That's a negatable query, too.

Can we have a negatad form of search too? noSearch? searchNotFound? I was thinking of Query.is.found and Query.is.not.found.

[yumin-chen]

Surely you can just invert the query? So Query.is.not.less('age', 100) could be written as Query.greaterEqual('age', 100), for example. For me, the latter is easier to read and understand whereas the former seems less clear.

I find the .is.(not) syntax more expressive and flexible. The .not.less is just an alias for greater. It'd be nice to offer this option, as the context in English might make more sense in the negative form in some cases. Could we still have .is and .is.not interfaces as aliases though?

[CordMemescape]

@appwrite Team

Missing from the Query class description above (copied below)

type Value = string | number | boolean;

class Query {
  equal(attribute: string, value: Value | Value[]): string;
  search(attribute: string, value: Value | Value[]): string;

  less(attribute: string, value: Value | Value[]): string;
  lessEqual(attribute: string, value: Value | Value[]): string;
  greater(attribute: string, value: Value | Value[]): string;
  greaterEqual(attribute: string, value: Value | Value[]): string;

  orderDesc(attribute: string): string;
  orderAsc(attribute: string): string;

  cursorAfter(attribute: string): string;
  cursorBefore(attribute: string): string;

  limit(amount: number): string;
  offset(amount: number): string;

  // Aliases
  page(page: number, limit: number);
  gt(attribute: string, value: Value | Value[]): string;
  gte(attribute: string, value: Value | Value[]): string;
  lt(attribute: string, value: Value | Value[]): string;
  lte(attribute: string, value: Value | Value[]): string;
}

is the notEqual(attribute: string, value: Value | Value[]): string; definition. It is described in the Appwrite documentation but didn't make it into this updated class. I think this might be why there are so many questions about the 'not equal to' use cases.

[damalo93]

I think OR query is not fully supported .

For example if I want to make this query: SELECT * FROM Products WHERE Title = 'abc' OR Price < 100

I can't do this by only one request.

[a7mdragab]

Waiting for the join query. I hope you finish it as soon as possible.
May I could help with that if I have the chance.
I have sent an application.

[pitcairn1987]

Hi, Is it possible to search document by Query.search in two attributes, something like in sql:
Select * from document where attr1 like '%xxx%' or attr2 like '%xxx%'

[tuvaergun]

@Meldiron Is there an estimated time for the new version? It would be great if I could at least have some idea.
There is a great need for new future queries and filters.

Only if I can get a time estimate for the new version will I plan accordingly in my projects.

Thanks for the good planning

[conanak99]

Will we support GROUP BY/SUM/COUNT operations too? If not, what is the proper way to aggregate data in AppWrite?

[malikdoksoz]

For example, how do I list the distance between 2 locations (user location and product location)?

[Dosbodoke]

I wished listing could retrieve more than 100 items, some times it's necessary. Any thoughts on this?

[step135]

There is no description how to use queries in REST API !

[getBoolean]

its described here https://appwrite.io/docs/products/databases/queries#query-class

[SunnyMudhar]

I have a strange error, when I run Query.orderDesc("attribute") I get the following error code:

Invalid query: {"method":"orderDesc","attribute":"$createdAt"}

[ng9000]

Is there no query to get a specific amount of random documents from a collection?
if not is there a good way to get a random amount of documents?