How are GraphQL APIs generated from a PostgreSQL schema?

What does a GraphQL generator read from a PostgreSQL database?

It reads the system catalogue, the same metadata \d in psql prints. Every GraphQL construct the server exposes is derived from something PostgreSQL already records about the schema, so there is no separate type definition to keep in step with the tables.

Postrust queries pg_catalog directly when it starts, for the schemas named in PGRST_DB_SCHEMAS. It reads pg_class and pg_attribute for tables, views and columns, pg_index for primary and unique keys, pg_constraint for foreign keys, pg_proc for functions, and pg_type and pg_enum for types. Views are followed back to the columns they select from, so a foreign key on a base table can still produce a relationship on the view. The mapping, for a table author and a table article whose author_id references it:

Types with a GraphQL equivalent map to it: int4 and int2 to Int, float8 to Float, bool to Boolean, text and varchar to String. The rest keep their PostgreSQL names as custom scalars (bigint, numeric, uuid, jsonb, timestamptz, geometry), because that is how Hasura names them and a client declares $x: jsonb! against exactly that spelling. Arrays become lists.

Which root fields does each table get?

Each table gets a family of root fields named after it: a list query, a lookup by primary key, an aggregate, and inserts, updates and deletes. For author in Postrust:

• author(where:, order_by:, distinct_on:, limit:, offset:) returns the rows; author_by_pk(id: 1) returns one row or null, and exists only where the table has a primary key.
• author_aggregate(where:) returns aggregate { count sum { … } } and the matching nodes.
• insert_author, insert_author_one (both with on_conflict for upserts), update_author, update_author_by_pk, update_author_many, delete_author and delete_author_by_pk on the mutation root. A mutation naming several of them runs them in one transaction.
• The subscription root mirrors the query root. Each field is a live query: the answer now, then the answer again whenever it changes.

How do where, order_by, limit and distinct_on become SQL?

Each argument maps to one SQL clause. where becomes the WHERE clause, order_by becomes ORDER BY, limit and offset carry over unchanged, and distinct_on becomes PostgreSQL’s DISTINCT ON.

where takes a generated author_bool_exp: one field per column holding typed comparison operators (_eq, _gt, _in, _ilike, _is_null, and the JSON, ltree and PostGIS operators for those types), combined with _and, _or and _not, and able to follow a relationship by naming it. Because operators are typed, a wrong operand is refused during validation rather than by the database. order_by is a list of single-key objects such as [{ created_at: desc_nulls_last }, { name: asc }], because ordering is ordered. A query with no limit is capped at PGRST_MAX_ROWS when that is set. As in Hasura, a distinct_on that does not match the start of order_by is refused, since PostgreSQL would otherwise keep an arbitrary row from each group.

How does a nested GraphQL query become one SQL statement?

The server plans the whole selection before it runs anything, and nests each requested relationship inside the parent query as a subselect. A naive resolver-per-field server runs one query for the authors and then one more per author for their articles (the N+1 problem). A schema-driven server knows the foreign key behind articles, so it can let PostgreSQL do the join. Take this query:

query {
  author(
    where: { name: { _ilike: "%rust%" } }
    order_by: [{ name: asc }]
    limit: 10
  ) {
    id
    name
    articles(where: { published: { _eq: true } }, limit: 5) {
      id
      title
    }
  }
}

Postrust compiles it into a single statement of this shape. It is simplified from what the server generates (aliases shortened, the bound values left as $1 and $2), but the structure is the real one:

SELECT row_to_json(t) FROM (
  SELECT src.*,
         COALESCE((
           SELECT json_agg(row_to_json(e1_j)) FROM (
             SELECT "id", "title"
             FROM "public"."article" AS "e1"
             WHERE "e1"."author_id" = "src"."id"   -- the foreign key, correlated
               AND "e1"."published" = $2
             LIMIT 5                              -- per author, not overall
           ) e1_j
         ), '[]'::json) AS "articles"
  FROM (
    SELECT * FROM "public"."author" AS "pgrst_src"
    WHERE "name" ILIKE $1
    ORDER BY "name" ASC
    LIMIT 10
  ) AS src
) t

Ordering and the limit apply inside the innermost subquery, so the ten authors are the first ten by name. Each author’s articles come from a subselect correlated on the foreign key and folded into a JSON array with json_agg. An author with no articles gets [], not null. The child limit applies per parent. A to-one relationship such as article.author is the same subselect with LIMIT 1 and row_to_json instead of an array, and articles_aggregate is a correlated subselect that computes the aggregate. Deeper nesting repeats the pattern inside the child’s select list. The query and everything nested in it run in one transaction, so parent and child rows come from the same snapshot.

Who decides what a caller may see when there are no resolvers?

The database does. Postrust runs every request in a transaction that has switched to the caller’s database role with SET LOCAL ROLE and set the caller’s session variables, so table grants and row-level security policies filter the generated SQL like any other query.

An x-hasura-user-id session variable, from a verified token or from an administrator acting as another role, is readable in a policy as current_setting('hasura.user_id'). A function taking a hasura_session json argument receives the whole session. Policies apply to nested subselects, aggregates and subscriptions alike, because they all run as the same SQL under the same role. For migrations from Hasura, PGRST_GRAPHQL_METADATA can also carry per-role permissions. Each role then gets its own schema, built only from the tables it may read. The guide to row-level security behind an API covers the database side in more depth.

How are the generated names customised?

By default every name is derived from the catalogue. Where that is not what clients expect, PGRST_GRAPHQL_METADATA renames things: a JSON document, or a path to one, that can rename tables, columns, root fields, relationships and computed fields, choose the root a function appears on, and expose a column as ID with column_types without changing its database type:

{
  "tables": {
    "public.products": {
      "name": "Product",
      "column_types": { "id": "ID" },
      "roots": { "select_by_pk": "product" }
    }
  }
}

PGRST_GRAPHQL_TYPE_PREFIX prefixes generated types and root fields, so two Postrust servers over different databases can be composed without their names colliding.

What happens to the GraphQL schema when the database changes?

In Postrust, a migration reaches GraphQL when it says so. With PGRST_DB_CHANNEL_ENABLED set, a migration that ends with NOTIFY pgrst, 'reload schema' makes the server re-read the catalogue and rebuild the GraphQL schema from it, beside the REST schema cache, without a restart. The new schema is swapped in only once it is built, so a request is answered wholly from the old schema or wholly from the new one, and a schema that cannot be built leaves the previous one serving. Subscriptions already open stay on the schema they started with. Without the setting, the schema is the one read at start-up.

Data changes are a different matter. A live subscription re-runs its query when a trigger on the table it reads sends a NOTIFY, and also every PGRST_SUBSCRIPTION_REFRESH seconds (30 by default) to catch what a trigger cannot see, such as a view. It sends a message only when the answer differs from the last one.

Can a generated schema be an Apollo Federation subgraph?

Yes, as an opt-in. With PGRST_GRAPHQL_FEDERATION=true, Postrust serves its API as an Apollo Federation v2 subgraph. It adds _service and _entities, makes each table’s primary key its @key, and names the root types Query, Mutation and Subscription instead of query_root and its siblings. With the setting off, the generated SDL is unchanged. The GraphQL documentation covers shared entities and what is not supported yet.

How do Hasura, PostGraphile and pg_graphql approach the same idea?

All three generate GraphQL from a PostgreSQL schema and compile requests to SQL. They differ in dialect and in where configuration lives. Hasura tracks tables and relationships in its own metadata and defines permissions there. PostGraphile introspects the schema, exposes Relay-style connections, and relies on PostgreSQL roles and row-level security. pg_graphql, from Supabase, is an extension that resolves GraphQL inside the database through a SQL function. Postrust reflects the schema the way PostGraphile does, but answers in Hasura’s dialect, so existing Hasura clients work unchanged. See Postrust vs Hasura and Postrust vs PostGraphile. The same server also serves REST from the same schema, covered in building an API from a PostgreSQL database.