Gelly

Gelly is Gadget's data access language, powering advanced features in Gadget like filtered model permissions, computed fields, and computed views.

Gelly offloads a lot of the traditional performance burdens of managing complex queries (via SQL) off of your plate and onto ours.

This guide complements the Gelly reference which provides specific details about Gelly types, operators, and functions.

Query structure

A Gelly query is a declarative way to specify the data you want to retrieve from your models.

Similar to GraphQL queries or SQL SELECT statements, Gelly queries allow you to specify exactly what data you want, and returns it in a predictable shape.

Queries can specify fields on models and built-in operators can be used to transform and aggregate the returned data.

Starting a query

Currently, Gelly can be executed in one of two ways: computed fields or computed views.

When creating a Gelly query for computed fields, start the query with field on <model>. When working with computed views, you start the query with view.

Most examples in this guide will use the view syntax. You can learn more about defining computed fields here.

Selecting fields

Queries can select fields from the available models by listing each field between curly brackets, also known as a selection set.

For example, this query selects the title field from a blog model:

select the title field from a blog model
gelly
view {
  blogs {
    title
  }
}

This on it's own is not exactly useful, as we already have access to the title field through blog. However, we can create more complex expressions here, which can extract more meaningful information.

For example, from the customer model, we could save the fullName:

concatenate the customer's first and last name
gelly
view {
  customers {
    fullName: concat([firstName, " ", lastName])
  }
}

Fields in a selection must be separated by at least one whitespace character like a space, a tab, or a newline. Model fields are selected using the API Identifier for that field.

The fields you select are entirely up to you: none are mandatory and none require other fields to be selected in order to be selected themselves.

Selecting relationships

Gelly queries can also be made across relationships between models. To select related data, add an inner set of curly brackets, or a nested selection set, to a root selection.

For example, a blog model has many posts. If the post model has title and id fields, which can be read with the following Gelly query:

select the id and title field from a post model in a blog
gelly
view {
  blogs {
    name
    posts {
      id
      title
    }
  }
}

This query will return an array of blogs, each with a posts array containing the selected fields for each post:

the result of selection across a relationship
json
{
  "blogs": [
    {
      "posts": [
        { "id": 1, "title": "Hello World" },
        { "id": 2, "title": "Thoughts on Software Development" }
      ]
    }
  ]
}

Relationships can be navigated through multiple levels too. For instance, if blog has many post and post has many comments, then you may access fields of both post and comments simultaneously.

This query selects the id and title of the first page of posts related to the current blog, as well as the id and body of the first page of comments for each of the posts:

Gelly can select across multiple nested relationships
gelly
view {
  blogs {
    name
    posts {
      title
      comments {
        id
        body
      }
    }
  }
}

This query returns an array. Each element will represent a post object, which itself will store an array of comment objects:

the result of a selection across multiple nested relationships
json
{
  "blogs": [
    {
      "name": "my blog",
      "posts": [
        {
          "title": "Hello World",
          "comments": [
            { "id": 1, "body": "Nice post!" },
            { "id": 2, "body": "Cool blog!" }
          ]
        },
        {
          "title": "Thoughts on Software Development",
          "comments": []
        }
      ]
    }
  ]
}

Expressions

Gelly is an expression language: Gelly queries are made up of expressions that evaluate to a single value.

These expressions can be literals or they can apply transformations to records. Transformations can be simple string operations or math, complicated expressions with boolean logic, or aggregations that summarize a whole set of records into one value. Gelly contains a rich set of built-in functions to help with these transformations.

For example, we can select the full name for each record of a customer model by adding the firstName and lastName fields together.

transform data using built-in Gelly functions, like concat
gelly
view {
  customers {
    concat([firstName, " ", lastName])
  }
}

Expressions must be separated from each other with whitespace characters. They can refer to other fields of the record in question, as well as use literals like strings and numbers to do useful work.

For example, an expression can be a simple literal and return the literal's value:

a literal expression for a computed field
gelly
field on model {
  1
}

the result of a literal expression
json
{
  "fieldName": 1
}

Expressions can also be complicated, and call functions or use boolean logic:

a complex Gelly expression that combines multiple functions and boolean logic
gelly
view {
  comments {
    id
    isSpam: author.markedAsSpammer || contains(lower(title), item: "spam") || author.spamScore < (endsWith(author.email, "@gmail.com") ? 10 : 5)
  }
}

Read the Gelly reference to learn about the built-in Gelly functions that can be used to build expressions.

Aliasing fields and expressions

You can name an expression or rename a field by prefixing the selection with a name and a colon.

For example, to select the title field from a post but have the resulting JSON blob use the key coolName to store the field's value, you can run this query:


gelly
view {
  posts {
    coolName: title
  }
}

This would produce an array result like:


json
{
  "posts": [
    { "coolName": "Hello World!" },
    { "coolName": "Thoughts on Software Development" }
  ]
}

Aliasing works on expressions as well. To output an uppercase version of the title field as screamingTitle, the expression can be aliased by prefixing the selection with an alias identifier and a colon.


gelly
view {
  posts {
    screamingTitle: upper(title)
  }
}

This would produce a array result like the one below.


json
{
  "posts": [
    { "screamingTitle": "HELLO WORLD!" },
    { "screamingTitle": "THOUGHTS ON SOFTWARE DEVELOPMENT" }
  ]
}

If you don't alias an expression, the expression's source Gelly snippet is used as the output field name.

For example, querying for upper(title) will return the uppercase title under a field named upper(title)


gelly
view {
  posts {
    upper(title)
  }
}


json
{
  "posts": [
    { "upper(title)": "HELLO WORLD!" },
    { "upper(title)": "THOUGHTS ON SOFTWARE DEVELOPMENT" }
  ]
}

You cannot alias a computed field that does not access data across a relationship field. This is because the computed field already has a name, and to give it an alias would be redundant. For example, if we have a computed field called fullName on author, we cannot alias it like this:

alias will not work, not accessing related model data
gelly
field on author {
  newName: concat([firstName, " ", lastName"])
}

Ternary expressions

Gelly supports if-then-else statements using the ? ternary expression, similar to JavaScript. Put a boolean expression first followed by a ? symbol, and then two expressions separated by a :. When the boolean expression is true, the first expression is selected, otherwise, the second expression is selected.

For a simple example, we can select the string "yes" if 10 is greater than 5:


gelly
view {
  greaterThan10: 10 > 5 ? "yes" : "no"
}

Which would produce a single value result, such as greaterThan10: "yes".

Ternary expressions can also be used within selections of relations alongside other selections just fine.


gelly
view {
  blogs {
    posts {
      id
      title
      recent: createdAt < (now() - interval("60 days")) ? createdAt : "Old"
    }
  }
}

Unlike SQL, ternary expressions don't need to return the same data type from the truthy and falsy expressions on either side of the colon.

Relational commands

The list of records passed through selection sets in Gelly can be changed using relational commands like where, limit, order by and group by. Relational commands are always enclosed in square brackets within a selection set and only affect the returned records for that level of the selection.

For example, we can limit the list of records returned with the [limit 5] relational command:


gelly
view {
  posts {
    id
    title
    [limit 5]
  }
}

Relational commands change the data returned by the query, but don't change the data stored in the database. Relational commands are able to use fields from the model being queried. For example, if the post model had a published boolean field on it, then we can use the where relational command to filter the list of returned posts down to only those that are published.


gelly
view {
  posts {
    id
    title
    [where published]
  }
}

We can also use expressions to filter returned records. For example, we could return only posts with a score above a certain threshold that are also published:


gelly
view {
  posts {
    id
    title
    [where published && score > 10]
  }
}

Relational commands can be used within inner, nested selection sets without affecting the outer selection set. For example, we can select the top three comments for each post sorted by comment score:


gelly
field on blog {
  posts {
    id
    title
    comments {
      id
      body
      [order by score desc limit 3]
    }
  }
}

We can also issue multiple relational commands to inner and outer selection sets simultaneously. For example, we can select the three most recent posts which have been published, and select the top three comments of each post:


gelly
view {
  posts {
    id
    title
    [where published limit 3 order by createdDate desc]

    comments {
      id
      body
      [order by score desc limit 3]
    }
  }
}

The order of relational commands within the [...] stanza doesn't matter and the position of the command list among all the fields in a selection set also doesn't matter.

Relational commands are very similar (and in fact powered by) the SQL query clauses of the same name, and tend to work the same way as their SQL counterparts. If you are familiar with SQL, you can use the relational commands like you would use the same keywords in SQL!

`limit`

The limit relational command ensures a maximum number of results are returned for the selection set. A selection set containing [limit 10] will return a maximum of 10 records. If the underlying model has fewer records than the limit, or if a where command filters them out, up to the limit records will be returned.

[limit <number>]

For example, we could select up to 5 posts with the limit command.

limiting to a count
gelly
view {
  posts {
    title
    [limit 5]
  }
}

The limit command can be used more than once, on both an outer selection and an inner selection. For example, we could select 5 posts and 3 comments for each post with two limit commands.

limiting at different nesting
gelly
view {
  posts {
    title
    [limit 5]
    comments {
      body
      [limit 3]
    }
  }
}

The limit command is often combined with the order by command in order to get the top records by some criteria. For example, we can get the top 10 scoring posts ever by combining the two commands.

combining limit and order by
gelly
view {
  posts {
    title
    score
    [order by score desc limit 10]
  }
}

The limit command supports the optional offset parameter. It takes one fixed integer that denotes a position in the list of results to start from. The query will return up to limit number of results starting from the offset position. This can be used to "page" through a long list of results, the limit reflects the size of the page and the offset reflects the position where the page should start.

getting second page of 5 posts
gelly
view {
  posts {
    title
    [limit 5 offset 5]
  }
}

`order by`

The order by relational command sorts the returned records by the specified expression in either ascending or descending order. The default sort direction is ascending, where the record with the lowest value will be first, and the record with the highest value will be last.

[order by <expression> <asc|desc>]

For example, order by can ensure the most recent records for a table are returned by using [order by createdDate desc].

ordering by a date field
gelly
view {
  posts {
    title
    [order by createdDate desc]
  }
}

The order by expression is not limited to simple queries. It can also perform rich computations on any available data:

ordering by an expression
gelly
view {
  posts {
    title
    [order by author.scoreWeight * post.score]
  }
}

`where`

The where command filters the stored records down based on some conditions. where expects to be passed a boolean expression and will include records where the expression evaluates to true.

[where <expression>]

For example, you can find only the posts that have a score greater than 0 with the where command within the posts selection set:

a basic where expression example
gelly
view {
  posts {
    title
    [where score > 0]
  }
}

where command expressions can be arbitrarily complicated. For example, you can query for posts that have a score over 50 by a trusted author, or posts which have a score over 100 by a spammy author.

a more complex where expression
gelly
view {
  posts {
    title
    score
    author.isSpam
    [where (!author.isSpam && score > 50) || (author.isSpam && score > 100)]
  }
}

Like all relational commands, the where expression can be combined with other relational queries. For example, we can select the top 10 highest scoring posts where the author isn't spammy by using where, limit, and order by.

combine where with other relational commands
gelly
view {
  posts {
    title
    score
    [where !author.isSpam order by score desc limit 10]
  }
}

`group by`

The group by command is very similar to the GROUP BY query in SQL. This command will group records into smaller buckets, and then solve aggregations based on these groupings.

[group by <expression> <, ...expression> <, ...expression>]

group by expects to be given expressions and fields to group the incoming records. The records will first be sorted into 'buckets' using these fields and expressions. Once the groups have been made, then any aggregate functions in the selection set are evaluated for each group.

For example, we could group post records by their author id to count the number of posts each author has made. This query would return one row per unique authorId value, holding the authorId and the number of posts, as counted by count(id). Be sure to explicitly select the authorId field to know which count is for which authorId in the results.

post count, grouped by author
gelly
view {
  posts {
    count(id)
    authorId
    [group by authorId]
  }
}

group by is not limited to fields, and can also be used on expressions on fields. For instance, we could alias a field scoreBucket, which rounds each post score to a multiple of 10.

This temporary field could then be used in a group by expression:

post count by score bucket
gelly
view {
  posts {
    count(id)
    scoreBucket: score % 10
    [group by scoreBucket]
  }
}

Like all relational commands, group by can be combined with other related commands. limit and order by affect the group results, and queries are able to order by fields produced by the grouping.

For example, we could order the post-counts-by-author results by the resulting post count:

ordering aggregate results
gelly
view {
  posts {
    count(id)
    authorId
    [group by authorId order by count(id) desc]
  }
}

group by can be combined with where commands as well. Gelly groups records after they have been filtered by a where command. This allows you to group only records you are interested in.

For example, we can compute the count of posts by author where the post was not marked as spam:

spam count, grouped by author
gelly
view {
  posts {
    count(id)
    authorId
    [
      group by authorId
      where !isSpam
    ]
  }
}

Like GROUP BY in SQL, the group by command will cause an error if you try to select fields that aren't part of the group or aggregation expressions. This is because the output rows from a group by must be aggregated, and Gelly doesn't know what to do with fields that you don't specify an aggregate function for.

For example, if we group posts by authorId, and then try to select the count of posts, and each post's score, it is unclear what to do with the score of each post to form the group:

ungrouped fields cause errors
gelly
view {
  posts {
    count(id)
    # will throw an error because the field is not being aggregated
    score
    [group by authorId]
  }
}

Collecting unique values

The group by command can be used to collect unique values of a given field.

collecting unique post authors
gelly
view {
  postAuthors: posts {
    authorId
    authorName: author.name
    [group by authorId, authorName]
  }
}

Since group by can be used in nested sub-selections as well, it is also possible to collect unique values from related models. And since each subselection can be grouped independently, you can collect multiple unique value sets from related models in the same query.

collecting unique comment authors for each post
gelly
view {
  posts {
    title
    commentAuthors: comments {
      authorId
      authorName: author.name
      [group by authorId, authorName]
    }
  }
}

If you are only interested in counts of unique values, the countDistinct() function provides more efficient way to obtain those.

Alternatives to `group by`

group by in Gelly tends to be used less than in SQL. There's nothing wrong with using it, but in Gelly, there's often a simpler way of executing the same query without using group by that most consider clearer, specifically when grouping the related records of a parent record. If you're grouping a set of records by the same key of a relationship, you can potentially reconfigure your query to use a related model aggregation instead!

For example, a query that counts the number of posts per author could be rewritten using an aggregation function. This switches the response to be oriented around the author model rather than the post, which allows for getting things like the author's name:

an alternative to group by: authors with count of posts
gelly
view {
  authors {
    id
    name
    count(posts.id)
  }
}

Aggregating data

Gelly has built-in aggregation functions that can be used to perform computations across a number of records to extract meaningful data and trends.

Most aggregate functions accept a where argument, which is a boolean expression that can be used to filter existing records before applying the aggregation.

For example, you could count the number of posts that have titles beginning with "Software":

a simple count aggregation
gelly
view {
  posts {
    count(id, where: startsWith(title, prefix: "Software"))
  }
}

Average

Averages can be calculated with the avg aggregation. This function accepts a numerical field, such as likes on post.

For example, we could calculate the average cost of items bought per order:

calculating an average
gelly
view {
  orders {
    avg(items.price)
  }
}

We could expand this query to include the where argument, and calculate the average price of items bought per order where more than one of the item was purchased:

calculating an average with a "where" relational command
gelly
view {
  orders {
    avg(items.price, where: items.count>1)
  }
}

Every

every can be used to determine if all values in a field meet specified condition. This function accepts boolean values or functions that return boolean values, and will return true if all values evaluate to true, and false otherwise.

The simplest example of this would be to check if all orders have been fulfilled for a store, where fulfilled will be a boolean value. If all orders have been fulfilled, then the function will return true:

check if ALL orders have been fulfilled
gelly
view { allFulfilled: every(orders.fulfilled) }

A more complex example would involve an expression. For instance, we could check if all orders are being shipped to Ontario:

check if ALL orders are being shipped to Ontario
gelly
view {
  orders {
    allOntario: every(province=="Ontario")
  }
}

Some

some will return true if any values in a field meet the specified condition. false will be returned if no values meet the condition.

For example, we could check if any orders have been fulfilled for a store, where fulfilled is a boolean value. If any order has been fulfilled, then the function will return true:

check if ANY orders have been fulfilled
gelly
view {
  orders {
    someFulfilled: some(fulfilled)
  }
}

Count

count can be used to count the number of non-null values across an aggregated set. This function, unlike other aggregate functions, accepts any field type.

For example, we could count the number of orders that have been fulfilled:

count fulfilled orders
gelly
view {
  orders {
    fulfilledCount: count(id, where: fulfilled)
  }
}

Maximum

max can be used to find the maximum value of a number field.

For example, we can determine the largest price of an item in each order:

find the highest item price in each order
gelly
view {
  orders {
    maxPrice: max(items.price)
  }
}

Minimum

min can be used to find the minimum value of a number field.

For example, we could determine the smallest price of an item in each order:

find the lowest item price in each order
gelly
view {
  orders {
    minPrice: min(items.price)
  }
}

Sum

sum can be used to add the values of number fields across records.

For example, we could determine the total price of items ordered to be shipped to Ontario:

sum the price of items shipped to Ontario
gelly
view {
  orders {
    total: sum(items.price, where: province=="Ontario")
  }
}

Examples of Gelly queries

For the following examples, we will use an employee management system database that has two models: employee and department. department has many employees, which is captured in the employeeList field on department.

To fetch the id of employees in a department, we would use this query:


gelly
field on department {
  employee {
    id
  }
}

To fetch the employees with a last name beginning with A, we could use this query:


gelly
field on department {
  employeeList {
    id
    [where startsWith(lastName, prefix: "A")]
  }
}

To ensure that capitalization does not matter when finding employees with a last name beginning with A, we could use the query:


gelly
field on department {
  employeeList {
    id
    [where startsWith(upper(lastName), prefix: "A")]
  }
}

To fetch the top three employees with the highest salary, we could use this query:


gelly
field on department {
  employeeList {
    firstName
    lastName
    [limit 3 order by salary desc]
  }
}

To fetch employees that are managers or regular employees who earn above $100,000, we can use this query:


gelly
field on department {
  employeeList {
    firstName
    lastName
    [where (role=="manager") || (role!="manager" && salary >100000)]
  }
}

To count the total number of employees by role, we can use group by like so:


gelly
field on department {
  employeeList {
    role
    numberOfEmployees: count(id)
    [group by role]
  }
}

Why Gelly?

While JavaScript is fantastic for implementing business logic and behavior in your app, it's not designed for complex data computations. For applications that require complex queries and aggregations, Gelly is the optimal choice.

Gelly is specifically built for:

Efficient computations on large datasets.
Declarative expressions that allow Gadget to optimize and cache results for better performance.
High scalability, making it ideal for use cases like reporting, dashboards, and data-driven decision-making.

Under the hood, Gelly is compiled into high-performance SQL queries, allowing operations to be executed inside the database. A custom caching layer exists on top which helps to further speed up Gelly operations.

Gadget does this in order to power features like conditional permissions that decide if a row is visible using data from related rows, an operation that is not possible at scale if the computation is defined in JavaScript because Gadget would have to run the computation for every row in the database to know which ones to return.

Gelly vs SQL

Gelly draws inspiration from SQL, but it introduces several improvements:

Simplified syntax: Gelly has a more readable and developer-friendly syntax compared to SQL.
Automatic joins: Relationship traversal is built into Gelly so you don't have to manually specify JOIN conditions.
Less boilerplate: You can retrieve related data with minimal effort, eliminating the need for complex SQL statements like json_agg or managing multiple queries.

While SQL is still essential for relational databases, Gelly abstracts the complexity and makes it easier to work with large datasets without sacrificing performance.

Gelly vs GraphQL

Gelly and GraphQL share similarities but serve different purposes:

Gelly allows you to perform complex aggregations, filtering, and computations on the server without requiring additional API endpoints for each query.
GraphQL is more suited for querying data in a flexible way but lacks built-in support for complex computations like aggregations or mathematical operations. Gelly handles these efficiently, directly within the query.

Key differences:

Arbitrary Expressions: Gelly allows you to include mathematical and relational expressions within queries.
Relational Commands: Gelly provides built-in support for relational commands like [where], [order by], and [group by], which are available on every relation, making it faster than GraphQL for complex data manipulations.
No Mutations or Subscriptions: Unlike GraphQL, Gelly doesn't handle mutations or live subscriptions. For those, you should continue using GraphQL.

Understanding Gelly terminology

Gelly is built around relational algebra and shares many of the same concepts as SQL. Here are the key terms you need to know:

Views: A Gelly query that performs operations like filtering, aggregation, or transformation of data.
Fields: Attributes in your models that you can work with in Gelly queries.
Models: The primary data structures in your app, similar to tables in SQL.

These concepts allow you to express complex data transformations concisely and efficiently.

Accessing data with Gelly

You can extend your app's API by using computed fields and computed views to perform complex data operations.

Computed Fields: These are read-only fields that store predefined Gelly queries, allowing you to efficiently calculate values across your data.
Computed Views: High-level Gelly queries that allow for complex aggregation, filtering, and transformations of your data.

Inline Gelly

Inline Gelly queries allow you to define ad-hoc computations directly in your API calls. This is useful for dynamic queries where you don't need to define a reusable view in advance.

Example of an inline Gelly query:

JavaScript

const result = await api.view(`{ count(customers) }`);

Inline Gelly queries are ideal for situations where you need to run a custom query based on user input or dynamically generated criteria.