Gelly 

Gelly is Gadget's data access language, powering advanced features in Gadget like Filtered Model Permissions and Computed fields. It’s simple to learn, powerful, and 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 query in Gelly declares a bunch of expressions and returns them. Gelly queries are very similar to GraphQL queries or SQL SELECT statements -- they submit what data they want and then the platform returns it all in a predictable shape. A Gelly query can select fields from the underlying models, and expressions on those fields to manipulate or aggregate them, as well as control which records are returned.

Models are referenced in queries using a plural of the model name, e.g. model post will be available as posts in Gelly. Namespaces are navigated using the . operator, e.g. if the blog models are in a blog namespace, blog/post model can be referenced as blog.posts in Gelly.

As of right now, there's currently no way to execute a raw Gelly query in Gadget, but that will change soon.

Selecting fields of models 

Queries can select fields from the available models by listing each field between curly brackets (a selection set) from the plural name of that model. For example, this query selects the id and title fields from the first page of a Blog model:

select the id and title field from a post model
gelly
Copy
field on blog {
  posts {
    id
    title
  }
}

This query will return a JSON document containing the selected fields for each blog post that looks like this:

json
Copy
{
  "posts": [
    { "id": 1, "title": "Hello World" },
    { "id": 2, "title": "Thoughts on Software Development" }
  ]
}

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 allows related data for each record to be selected alongside the containing record. To select related data, add an inner set of curly brackets (a nested selection set) to a root selection.

For example, this query selects the id and title of the first page of blog posts, as well as the id and body of the first page of comments for each of the posts:

gelly
Copy
field on blog {
  posts {
    id
    title
    comments {
      id
      body
    }
  }
}

This query returns a JSON document where each element of the posts array will have a comments key holding another array of objects with the fields for each comment:

json
Copy
{
  "posts": [
    {
      "id": 1,
      "title": "Hello World",
      "comments": [
        {
          "id": 1,
          "body": "Nice post!"
        },
        {
          "id": 2,
          "body": "Cool blog!"
        }
      ]
    }
  ]
}

Relationship fields can be deeply nested, so you can select relationships of relationships. For example, if Blog has many Comments, and then Comments has many Comment Upvotes, you can select all three models at the same time like so:

gelly
Copy
field on blog {
  posts {
    id
    title
    comments {
      id
      body
      commentUpvotes {
        id
        createdAt
      }
    }
  }
}

Expressions 

Gelly allows selecting arbitrary expressions of fields to ask the server to transform records before returning results to the client. 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.

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

gelly
Copy
model on shop {
  customers {
    id
    fullName: concat([firstName, " ", lastName])
  }
}

This query would produce a result like the one on the right.

json
Copy
{
  "customers": [{ "fullName": "Jane McGee" }, { "fullName": "Joe Schmoe" }]
}

Expressions are selected right alongside other fields and must be separated from each other with whitespace characters. Expressions can refer to other fields of the record in question, as well as use literals like strings and numbers to do useful work. Expressions can be simple literals, which return value the literal's value.

gelly
Copy
field on model {
  1
}

json
Copy
{
  "1": 1
}

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

gelly
Copy
model on post {
  comments {
    id
    isSpam: author.markedAsSpammer || contains(lower(title), item: "spam") || author.spamScore < (endsWith(author.email, "@gmail.com") ? 10 : 5)
  }
}

Aliasing fields and expressions 

You can name an expression (or rename a field) when selecting it by prefixing the selection with a name and a colon. For example, to select the title field of a Blog model but have the resulting JSON blob use the key humanName to store the field's value, you can run this query:

gelly
Copy
field on blog {
  posts {
    humanName: title
  }
}

This would produce a JSON result like:

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

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

gelly
Copy
field on blog {
  posts {
    screamingTitle: upcase(title)
  }
}

This would produce a JSON result like the one on the right.

json
Copy
{
  "posts": [
    { "screamingTitle": "HELLO WORLD!" },
    { "humanName": "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 upcase(title) will return the uppercase title under a field named upcase(title)

gelly
Copy
field on blog {
  posts {
    upcase(title)
  }
}

This would produce a JSON result like the one on the right.

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

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
Copy
field on model {
  isTenGreaterThanFive: 10 > 5 ? "yes" : "no"
}

Which would produce a result JSON like so:

json
Copy
{
  "isTenGreaterThanFive": "yes"
}

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

gelly
Copy
field on blog {
  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
Copy
field on blog {
  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 expressions that use data from the model being queried. For example, if the Blog 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
Copy
field on blog {
  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
Copy
field on blog {
  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
Copy
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 top three comments for each post, and only select posts-with-comments for posts that are published, and only return the most recent 3 posts:

gelly
Copy
field on blog {
  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.

The limit command 

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. The limit command must be passed one fixed integer, and can't use dynamic expressions to choose how many rows are returned. 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.

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

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.

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.

The limit command supports optional offset parameter. It takes one fixed integer which 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.

The order by command 

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. Most, but not all data types that can be ordered by.

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

The expression can a rich computation on any available data instead of just a simple field reference.

The where command 

The where command filters the stored records down to only return some of them. where expects to be passed a boolean expression and will include records where the expression evaluates to true.

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

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 50 by a spammy author.

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

The group by command 

The group by command allows aggregating individual, smaller buckets of the total records available to return one aggregate result per group. group by can be used to answer questions like "the top score by city" or "the count of comments made each day for the last 30 days". [group by] in Gelly is very similar to GROUP BY in SQL.

group by expects to be given the expressions to group the incoming records. The expression (or expressions) are evaluated for each row in the set being grouped, and then a group is formed for each unique value (or combination of values) for the expressions. Any aggregate functions in the selection set are then evaluated independently for each group instead of across the whole incoming set of records. 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 count(id) counting the number of posts. Be sure to explicitly select the authorId field in order to know which count is for which authorId in the results.

The same query above using group by authorId can instead be written to use an inline aggregation function. This switches the response to be oriented around the authors model, which allows for getting things like the author's name field easily, instead of just the counts.

group by can group by arbitrary expressions on fields as well. We could count the number of posts by a score bucket, where we use the modulus operator to round each post score to a multiple of 10. Note that we want to select the scoreBucket expression so we can associate which count is for which bucket in the results.

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.

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.

All selected expressions must aggregate the input rows when grouped

Like GROUP BY in SQL, the group by command will error if you try to select fields that aren't part of the group 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. To fix this issue, use an aggregate function on each field you want to select which defines how to process the field for the group, or switch the orientation of your query to select from an outer relation where you can select anything you want, and then group a different inner relation.

Collecting unique values 

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

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.

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

Why a different language? 

Gelly is used to efficiently perform computations at scale in Gadget applications. JavaScript works well for adding functionality to Gadget applications when implementing behavior, but some applications have more complex computational needs. Computations in Gelly are declarative such that Gadget can pre-compute or re-compute the values of Gelly snippets very efficiently across a high number of rows. Currently, Gadget compiles Gelly into high performance SQL to execute inside the database with a custom caching layer on top, which makes Gelly fast while remaining consistent. Gadget does this in order to power features like conditional permissions that decide if a row is visible using data from related rows. Neither of these is 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 for SQL lovers 

Gelly is similar to, and inspired by, SQL! A Gelly query is also an upfront, declarative way to ask for some data based on relational algebra, but it's different in that it allows relationship traversal, fragments, and more ergonomic expressions. Gelly eliminates a few annoyances from plain old SQL such as having to manually specify join conditions all the time, or having to execute multiple queries or complicated json_agg functions to retrieve related data, and provides quality-of-life improvements such as not requiring trailing commas or a specific statement order.

Gelly for GraphQL lovers 

Gelly is similar to GraphQL and extends it with the ability to do things you can't do with normal GraphQL APIs. Gelly snippets can contain arbitrary computations that build new expressions with normal-looking code, as well as universally available commands to filter, sort, or aggregate a list of data.

GraphQL differences 

Even though Gelly and GraphQL are similar, there are some key differences between the two systems:

• Gelly queries can contain arbitrary expressions which are evaluated on the server side

This is so the frontend can push as much work to the server as possible, and so that they don't need to manually add new, bespoke API endpoints for each separate thing they want to do. Sometimes it is simpler to just ask the server to upcase a string, filter on an expression, or group by an arbitrary field that the user has selected. So, Gelly has math, function calls, aggregations, and relational commands built in.

• Gelly has relational commands available on every relation instead of some GraphQL fields having arguments

In Gelly, the relational commands [where], [order by], [limit] and [group by] are universally available on every relation. This allows developers to work quickly and leverage the automatic query compilation and execution that Gadget does for each Gadget application. Some GraphQL APIs expose similar facilities to Gelly's relational commands, but as bespoke field arguments that often require backend work to set up and make performant. Aggregations in Gelly aren't exposed as other strange fields in a schema and are instead available as aggregate functions

• Gelly doesn't have Relay style Connections, and instead has a formal idea of a relation

Gelly is a syntax around a fundamentally very powerful concept called relational algebra. Just like in algebra, relations in Gelly are things you can manipulate, and so we don't work with them as just more types in the system, and instead bake the idea in deeply. There are no edges { node { ... }} selections in Gelly, there are just subselections of relations. We have big plans for enhancing Gelly with support for the full relational algebra including joins as well as a relational chaining operator for implementing subqueries.

• Gelly doesn't support Mutations

GraphQL supports writes and Gelly does not (yet). If you're building a Gadget app, you can use the GraphQL API to execute writes to your application.

• Gelly doesn't support Subscriptions or @live queries

Some GraphQL servers support subscribing to a specific field, and some even support asking for arbitrary changes to a query result over time, both in a push style. Gelly doesn't yet support these facilities.

• Gelly null works like SQL NULL, not like JavaScript's null, where null == null gives null.

Gelly is powered by SQL underneath the hood, and so inherits SQL's null semantics. This means that null == null returns null in Gelly, not true. To check if something is null, you should use the isNull function.