pg_graphql: Postgres functions now supported

2023-12-12

•

7 minute read

Supabase GraphQL (pg_graphql) 1.4+ supports the most requested feature: Postgres functions a.k.a. User Defined Functions (UDFs). This addition marks a significant improvement in GraphQL flexibility at Supabase, both as a novel approach to defining entry points into the Graph and as an escape hatch for users to implement custom/complex operations.

As with all entities in Supabase GraphQL, UDFs support is based on automatically reflecting parts of the SQL schema. The feature allow for the execution of custom SQL logic within GraphQL queries to help support complex, user defined, server-side operations with a simple GraphQL interface.

Minimal Example

Consider a function addNums for a basic arithmetic operation:

create function "addNums"(a int, b int default 1)
returns int
immutable
language sql
as $$
select a + b;
$$;

when reflected in the GraphQL schema, the function is exposed as:

type Query {
addNums(a: Int!, b: Int): Int
}

To use this entry point, you could run:

query {
addNums(a: 2, b: 3)
}

which returns the JSON payload:

{
"data": {
"addNums": 5
}
}

Supabase GraphQL does its best to reflect a coherent GraphQL API from all the information known to the SQL layer. For example, the argument a is non-null because it doesn't have a default value while b can be omitted since it does have a default. We also detected that this UDF can be displayed in the Query type rather than the Mutation type because the function was declared as immutable, which means it can not edit the database. Of the other function volatility categories, stable similarly translates into a Query field while volatile (the default) becomes a Mutation field.

Returning Records

In a more realistic example, we might want to return a set of an existing object type like Account. For example, lets say we want to search for accounts based on their email address domains matching a string:

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

insert into "Account"(email)
values
('a@foo.com'),
('b@bar.com'),
('c@foo.com');

create function "accountsByEmailDomain"("domainToSearch" text)
returns setof "Account"
stable
language sql
as $$
select
id, email
from
"Account"
where
email ilike ('%@' || "domainToSearch");
$$;

Since our function is stable, it continues to be a field on the Query type. Notice that since we're returning a collection of Account we automatically get support for Relay style pagination on the response including first, last, before, after as well as filtering and sorting.

type Query {
accountsByEmailDomain(
domainToSearch: String!

"""
Query the first `n` records in the collection
"""
first: Int

"""
Query the last `n` records in the collection
"""
last: Int

"""
Query values in the collection before the provided cursor
"""
before: Cursor

"""
Query values in the collection after the provided cursor
"""
after: Cursor

"""
Filters to apply to the results set when querying from the collection
"""
filter: AccountFilter

"""
Sort order to apply to the collection
"""
orderBy: [AccountOrderBy!]
): AccountConnection
}

To complete the example, here's a call to our user defined function:

query {
accountsByEmailDomain(domainToSearch: "foo.com", first: 2) {
edges {
node {
id
email
}
}
}
}

and the response:

{
"data": {
"accountsByEmail": {
"edges": [
{
"node": {
"id": 1,
"email": "a@foo.com"
}
},
"node": {
"id": 3,
"email": "c@foo.com"
}
]
}
}
}

While not shown here, any relationships defined by foreign keys on the response type Account are fully functional so our UDF result is completely connected to the existing Graph.

It’s worth mentioning that we could have supported this query using the default accountCollection field that pg_graphql exposes on the Query type using an ilike filter so the example is only for illustrative purposes.

i.e.:

query {
accountCollection(filter: { email: { ilike: "%foo.com" } }, first: 2) {
edges {
node {
id
email
}
}
}
}

would give the same result as our UDF.

Limitations

The API surface area of SQL functions is surprisingly large. In an effort to bring this feature out sooner, some lesser-used parts have not been implemented yet. Currently functions using the following features are excluded from the GraphQL API:

  • Overloaded functions
  • Functions with a nameless argument
  • Functions returning void
  • Variadic functions
  • Functions that accept a table/views's tuple type as an argument
  • Functions that accept an array type

We look forward to implementing support for many of these features in coming releases.

Takeaways

If you're an existing Supabase user, but new to GraphQL, head over to GraphiQL built right into Supabase Studio for your project to interactively explore your projects through the GraphQL API. User defined function support is new in pg_graphql 1.4+. You can check your project's GraphQL version with:

select *
from pg_available_extensions
where name = 'pg_graphql';

To upgrade, check out our upgrade guide.

For new Supabase users, creating a new project will get you the latest version of Supabase GraphQL with UDF support.

If you're not ready to start a new project but want to learn more about pg_graphql/Supabase GraphQL, our API docs are a great place to learn about how your SQL schema is transformed into a GraphQL API.

Launch Week Supabase Launch Week X icon
11-15 Dec
Main Stage
Build Stage
Share this article

Build in a weekend, scale to millions