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.

Learn more about the advantages of Gelly.

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.

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 Post model:

select the id and title field from a post model
gelly
Copy
1query {
2  posts {
3    id
4    title
5  }
6}

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

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

As many fields as needed can be added to the selection set, and a query can select from more than one model at once if needed. This query selects the first page of Blog Post ids, titles, and publish dates, as well as the current user's name and email:

gelly
Copy
1query {
2  posts {
3    id
4    title
5  }
6  currentUser {
7    name
8    email
9  }
10}

The underlying Gelly implementation will efficiently query these two models in parallel and return the data for both as a JSON object like this:

json
Copy
1{
2  "posts": [
3    { "id": 1, "title": "Example A" },
4    { "id": 2, "title": "Example B" }
5  ],
6  "currentUser": {
7    "name": "Jane McGee",
8    "email": "[email protected]"
9  }
10}

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 commentBody of the first page of comments for each of the posts:

gelly
Copy
1query {
2  posts {
3    id
4    title
5    comments {
6      id
7      commentBody
8    }
9  }
10}

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
1{
2  "posts": [
3    {
4      "id": 1,
5      "title": "Hello World",
6      "comments": [
7        {
8          "id": 1,
9          "commentBody": "Nice post!"
10        },
11        {
12          "id": 2,
13          "commentBody": "Cool blog!"
14        }
15      ]
16    }
17  ]
18}

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

gelly
Copy
1query {
2  posts {
3    id
4    title
5    comments {
6      id
7      commentBody
8      commentUpvotes {
9        id
10        createdAt
11      }
12    }
13  }
14}

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
1query {
2  customers {
3    id
4    fullName: firstName + " " + lastName
5  }
6}

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
query {
  1
}

json
Copy
{
  "1": 1
}

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

gelly
Copy
1query {
2  comments {
3    id
4    isSpam: author.markedAsSpammer || contains(lower(title), "spam") || author.spamScore < (endsWith(author.email, "@gmail.com") ? 10 : 5)
5  }
6}

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 Post model but have the resulting JSON blob use the key humanName to store the field's value, you can run this query:

gelly
Copy
query {
  posts {
    humanName: title
  }
}

This would produce a JSON result like:

json
Copy
1{
2  "posts": [
3    { "humanName": "Hello World!" },
4    { "humanName": "Thoughts on Software Development" }
5  ]
6}

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

gelly
Copy
query {
  posts {
    screamingTitle: upcase(title)
  }
}

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

json
Copy
1{
2  "posts": [
3    { "screamingTitle": "HELLO WORLD!" },
4    { "humanName": "THOUGHTS ON SOFTWARE DEVELOPMENT" }
5  ]
6}

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
query {
  posts {
    upcase(title)
  }
}

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

json
Copy
1{
2  "posts": [
3    { "upcase(title)": "HELLO WORLD!" },
4    { "upcase(title)": "THOUGHTS ON SOFTWARE DEVELOPMENT" }
5  ]
6}

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
query {
  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
1query {
2  posts {
3    id
4    title
5    recent: createdAt < (now() - interval("60 days")) ? createdAt : "Old"
6  }
7}

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
1query {
2  posts {
3    id
4    title
5    [limit 5]
6  }
7}

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 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
Copy
1query {
2  posts {
3    id
4    title
5    [where published]
6  }
7}

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
1query {
2  posts {
3    id
4    title
5    [where published && score > 10]
6  }
7}

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
1query {
2  posts {
3    id
4    title
5    comments {
6      id
7      body
8      [order by score desc limit 3]
9    }
10  }
11}

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
1query {
2  posts {
3    id
4    title
5    [where published limit 3 order by createdDate desc]
6
7    comments {
8      id
9      body
10      [order by score desc limit 3]
11    }
12  }
13}

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.

[limit <number>]

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

1query {
2  posts {
3    title
4    [limit 5]
5  }
6}

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.

1query {
2  posts {
3    title
4    [limit 5]
5    comments {
6      body
7      [limit 3]
8    }
9  }
10}

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.

1query {
2  posts {
3    title
4    score
5    [order by score desc limit 10]
6  }
7}

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.

[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].

1query {
2  posts {
3    title
4    [order by createdDate desc]
5  }
6}

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

1query {
2  posts {
3    title
4    [order by author.scoreWeight * post.score]
5  }
6}

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.

[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:

1query {
2  posts {
3    title
4    [where score > 0]
5  }
6}

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.

1query {
2  posts {
3    title
4    score
5    author.isSpam
6    [where (!author.isSpam && score > 50) || (author.isSpam && score > 100)]
7  }
8}

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.

1query {
2  posts {
3    title
4    score
5    [where !author.isSpam order by score desc limit 10]
6  }
7}

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 <expression> <, ...expression> <, ...expression>]

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.

1query {
2  posts {
3    count(id)
4    authorId
5    [group by authorId]
6  }
7}

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.

1query {
2  authors {
3    id
4    name
5    count(posts.id)
6  }
7}

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.

1query {
2  posts {
3    count(id)
4    scoreBucket: score % 10
5    [group by scoreBucket]
6  }
7}

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.

1query {
2  posts {
3    postCount: count(id)
4    authorId
5    [group by authorId order by postCount desc]
6  }
7}

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.

1query {
2  posts {
3    count(id)
4    authorId
5    [
6      group by authorId
7      where !isSpam
8    ]
9  }
10}

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.

1query {
2  posts {
3    count(id)
4    # will cause an error because the field is not being aggregated
5    score
6    [group by authorId]
7  }
8}

Fragments 

There's often a need to re-use specific parts of Gelly queries in different places, in the same way you might re-use a JavaScript function as part of a larger piece of code, or re-use part of an HTML template in different outer contexts. Gelly supports fragments to facilitate composition and re-use among queries. The Gadget platform also uses fragments to allow Gadget developers to co-operate with the automatic schema generation and extend it. Fragments in Gelly work similarly to GraphQL, but don't have an exact analog in SQL.

Fragments can only be declared at the top level, and are declared using the fragment keyword. They are defined with a name that is then used to refer to them later, followed by the type of the object they are selecting fields from. Fragments can contain field selections, nested field selections, and relational commands. For example, we could use a fragment to specify which fields want to select from the posts model. We use the fragment keyword to start a fragment definition, give it a name, and then use the same field selection syntax you might use in a query.

gelly
Copy
1fragment PostDetails on Post {
2  id
3  title
4}
5
6query {
7  posts {
8    ...PostDetails
9    [where published]
10  }
11}

Queries can apply fragments using the spread operator, which is the .... Spreading a fragment can be thought of as copy-pasting the fragment's source code into the location of the spread.

In order to support re-usability of relational commands too, fragments support embedded relational commands. When spread, these fragments apply those commands to the query, merging the query's commands with their own. For example, we could have a PublishedPostDetails fragment that we re-use in several queries that selects some fields as well as filters the posts that are returned. Queries that spread fragments can add more filters, or add other relational commands, and still select other fields.

1fragment PublishedPostDetails on Post {
2  id
3  title
4  [where published]
5}
6query {
7  posts {
8    ...PostDetails
9    author {
10      id
11    }
12    [limit 10]
13  }
14}

Gelly doesn't allow both a fragment and a query that spreads it to both specify a group by relational command or an order by relational command. Only the fragment or the query can use these commands, but not both.

Fragment variables

Gelly fragments can accept arguments and then be spread with different values more than once. Variables can be used within the fragment to change behavior, and are really useful for re-using a particular fragment more than once with slightly different specifics in each place. Fragment variables are also used for configuring Gadget's permissions system with custom logic that can change behavior based on the $currentUser variable.

Fragment variables are defined the same way as query variables using parentheses after the name of the fragment definition. Fragments can then be spread with different values for these variables in any context where a fragment would normally be valid.

1fragment PostsForAuthor($authorId: ID!) on Post {
2  id
3  title
4  [where authorId = $authorId]
5}
6query {
7  posts {
8    ...PostsForAuthor(authorId: "123")
9    author {
10      id
11    }
12    [limit 10]
13  }
14}

Fragments can take many variables and can use the variables within field selections or relational commands without issue. Fragment variables can also be passed values from outer query variables or arbitrary expressions.

1fragment PostDetails($onlyInteresting: Boolean) on Post {
2  hotRightNow: $onlyInteresting ? score > 100 : false
3  [where $onlyInteresting ? score > 10 : true]
4}
5query($full: Boolean) {
6  posts {
7    id
8    title
9    ...PostDetails(onlyInteresting: $full)
10    [limit 10]
11  }
12}

1# the $onlyInteresting variable won't be available in this fragment unless we declare it at the top of the fragment
2fragment PostDetails($onlyInteresting: Boolean) on Post {
3  hotRightNow: $onlyInteresting ? score > 100 : false
4  [where $onlyInteresting ? score > 10 : true]
5}
6query($onlyInteresting: Boolean) {
7  posts {
8    id
9    title
10    # the $onlyInteresting variable must be passed into the fragment, even if it has the same name in the query context
11    ...PostDetails(onlyInteresting: $onlyInteresting)
12    [limit 10]
13  }
14}

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.