GraphQL is now available in Supabase

2022-03-29

10 minute read

🆕 pg_graphql has undergone significant enhancements since this announcement. Here is what is new:

GraphQL support is now in general availability on the Supabase platform via our open source PostgreSQL extension, pg_graphql.

pg_graphql enables you to query existing PostgreSQL databases using GraphQL, either from within SQL or over HTTP:

From SQL:


_15
select graphql.resolve($$
_15
{
_15
accountCollection(first: 1) {
_15
edges {
_15
node {
_15
id
_15
firstName
_15
address {
_15
countryCode
_15
}
_15
}
_15
}
_15
}
_15
}
_15
$$);

or over HTTP:


_10
curl -X POST https://<PROJECT_REF>.supabase.co/graphql/v1 \
_10
-H 'apiKey: <API_KEY>'\
_10
-H 'Content-Type: application/json' \
_10
--data-raw '
_10
{
_10
"query":"{ accountCollection(first: 3) { edges { node { id } } } }"
_10
}'

Schema Reflection

GraphQL types and fields are reflected from the SQL schema:

  • Tables become types
  • Columns become fields
  • Foreign keys become relations

For example:


_10
create table "Account" (
_10
"id" serial primary key,
_10
"email" varchar(255) not null,
_10
"createdAt" timestamp not null,
_10
"updatedAt" timestamp not null
_10
);

Translates to the GraphQL base type


_10
type Account {
_10
id: Int!
_10
email: String!
_10
createdAt: DateTime!
_10
updatedAt: DateTime!
_10
}

And exposes bulk CRUD operations on the Query and Mutation types, complete with relay style keyset pagination, filters, and ordering and (optional) name inflection.


_26
type Query {
_26
accountCollection(
_26
first: Int
_26
last: Int
_26
before: Cursor
_26
after: Cursor
_26
filter: AccountFilter
_26
orderBy: [AccountOrderBy!]
_26
): AccountConnection
_26
}
_26
_26
type Mutation {
_26
insertIntoAccountCollection(
_26
objects: [AccountInsertInput!]!
_26
): AccountInsertResponse
_26
_26
updateAccountCollection(
_26
set: AccountUpdateInput!
_26
filter: AccountFilter
_26
atMost: Int! = 1
_26
): AccountUpdateResponse!
_26
_26
deleteFromAccountCollection(
_26
filter: AccountFilter
_26
atMost: Int! = 1
_26
): AccountDeleteResponse!

For a complete example with relationships, check out the API docs.

Security

An advantage to embedding GraphQL directly in the database is that we can lean on PostgreSQL's built-in primitives for authentication and authorization.

Authentication

The GraphQL types exposed by pg_graphql are filtered according to the SQL role's INSERT/UPDATE/DELETE permissions. At Supabase, each API request is resolved in the database using the role in the request's JWT.

Anonymous users receive the anon role, and logged in users get the authenticated role. In either case, pg_graphql resolves requests according to the SQL permissions. The introspection schema is similarly filtered to limit exposed types and fields to those that the user has permission to access. That means we can serve multiple GraphQL schemas for users of differing privilege levels from a single endpoint!

Authorization

Another nice side effect of making PostgreSQL do the heavy lifting is that GraphQL queries respect your existing row level security policies right out-of-the-box. No additional configuration required.

Performance

To squeeze the most out of limited hardware we had to make a few significant optimizations:

GraphQL queries are always transpiled into exactly one SQL query

The SQL queries select and aggregate requested data into the shape of the GraphQL JSON response. In addition to solving the N+1 query problem, a common issue with GraphQL resolvers, GraphQL queries requiring multiple joins typically produce significantly less IO due to reduced data duplication.

For example, when selecting all comments for a blog post:


_10
select
_10
blog_posts.title,
_10
comments.body as comment_body
_10
from
_10
blog_posts
_10
join comments on blog_posts.id = comments.blog_post_id;

a SQL response would duplicate all data from the blog_posts table (title).


_10
| title | comment_body |
_10
| ---------- | ------------------------------ |
_10
| F1sRt P0$T | this guy gets it! |
_10
| F1sRt P0$T | you should re-write it in rust |
_10
| F1sRt P0$T | 10% off daily vitamin http:... |

Compared to the equivalent GraphQL response.


_22
{
_22
"blogPostCollection": {
_22
"edges": {
_22
"node":
_22
"title": "F1sRt P0$T"
_22
"commentCollection": {
_22
"edges": [
_22
"node": {
_22
"body": "this guy gets it!"
_22
},
_22
"node": {
_22
"body": "you should re-write it in rust"
_22
},
_22
"node": {
_22
"body": "10% off daily vitamin http:..."
_22
}
_22
]
_22
}
_22
}
_22
}
_22
}
_22
}

Which has no duplication of data.

The difference in payload size is negligible in this case, but as the number of 1-to-many joins grows, data duplication in the SQL response grows geometrically.

supameme.png

Queries are cached as prepared statements

After a GraphQL query is transpiled to SQL, it is added to the prepared statement cache so subsequent requests with the same structure (think pagination) can skip the transpilation step.

Using prepared statements also allows PostgreSQL to skip the overhead of computing a query plan. For small, on-index, queries, the query planning step can take several times as long as the query's execution time, so the saving is significant at scale.

All operations are bulk

Finally, all reflected query and mutation fields support bulk operations to nudge users towards consuming the API efficiently. Batching similar operations reduces network round-trips and time spent in the database.

Result

As a result of these optimizations, the throughput of a “hello world” equivalent query on Supabase free-plan hardware is:

  • 377.4 requests/second through the API (mean)
  • 656.2 queries/second through SQL (single connection, mean)

Getting Started

To enable GraphQL in your Supabase instance, enable pg_graphql from the dashboard.

Or create the extension in your database


_10
create extension pg_graphql;

And we're done!

The GraphQL endpoint is available at: https://<project_ref>.supabase.co/graphql/v1

Example app: Build a HN clone with Postgres and GraphQL

graph-ql-example-app.png

We're excited to have worked with The Guild to show you how to use pg_graphql and their tools to build a HackerNews clone.

The demo application showcases:

  • CRUD (Query + Mutation Operations). Data is fetched from the GraphQL layer auto-generated via pg_graphql.
  • Cursor Based Pagination. pg_graphql generates standardized pagination types and fields as defined by the GraphQL Cursor Connections Specification.
  • Authorization / RLS. GraphQL requests made include Supabase authorization headers so that Row Level Security on the Postgres layer ensures that viewers can only access what they are allowed to — and authenticated users can only update what they should.
  • Code generation. Introspect your GraphQL schema and operations to generates the types for full backend to frontend type-safety.
  • Postgres Triggers and Functions. Recalculate the feed scoring each time someone votes.
  • Supabase UI. Use Auth widget straight out the box to handle logins and access tokens.

graph-ql-example-app-2.png

Now instead of using the Supabase PostgREST API to query your database ...


_10
// using Supabase PostgREST
_10
_10
const { data, error } = await supabase
_10
.from('profile')
_10
.select('id, username, bio, avatarUrl, website')

... all data fetching and updates are done using the same GraphQL operations you know and love! 🤯


_15
// using GraphQL
_15
_15
query ProfilesQuery {
_15
profileCollection {
_15
edges {
_15
node {
_15
id
_15
username
_15
bio
_15
avatarUrl
_15
website
_15
}
_15
}
_15
}
_15
}

🎁  Get the code on GitHub here: github.com/supabase-community/supabase-graphql-example

Supabase + The Guild

graphql-supabase-guild.png

This is just the start of what we hope to be a close collaboration with the Guild, whose expertise of the GraphQL ecosystem will guide the development of Supabase's GraphQL features. The Guild and Supabase share a similar approach to open source - we both favor collaboration and composability, making collaboration easy and productive.

Be sure to visit The Guild and follow them to stay informed of the latest developments in GraphQL.

Limitations & Roadmap

Our first general availability release of pg_graphql supports:

  • Full CRUD on table columns with scalar types
  • Read only support for array types
  • Extending types with computed fields
  • Configuration with SQL comments

In the near term, we plan to fully support array and json/b types. Longer term, we intend to support views and custom mutations from user defined functions.

Didn't see the feature you're interested in? Let us know

More pg_graphql

Share this article

Build in a weekend, scale to millions