The pg_graphql extension

The pg_graphql extension adds a GraphQL API layer directly to your Postgres database. It introspects your SQL schema, tables, columns, relationships, and functions and automatically generates a corresponding GraphQL schema. This allows you to query your database using GraphQL through a single SQL function call, graphql.resolve(), eliminating the need for external GraphQL servers or middleware.

With pg_graphql, you can leverage the flexibility of GraphQL for data fetching while keeping your data and API logic tightly coupled within Postgres. It respects existing Postgres roles ensuring data access remains secure and consistent.

Enable the pg_graphql extension

You can enable the extension by running the following CREATE EXTENSION statement in the Neon SQL Editor or from a client such as psql that is connected to your Neon database.

CREATE EXTENSION IF NOT EXISTS pg_graphql;

Version availability:

Please refer to the list of all extensions available in Neon for up-to-date extension version information.

Core concepts

The graphql.resolve() function

The graphql.resolve() function is the main entry point for executing GraphQL queries against your Postgres database. It acts as a bridge between your SQL schema and the GraphQL API. You pass your GraphQL query string (and optionally, variables and an operation name) to this function. It executes the query against the auto-generated GraphQL schema based on your database structure and returns the result as a JSONB object.

Basic signature:

graphql.resolve(query TEXT, variables JSONB DEFAULT '{}') RETURNS JSONB;

Schema reflection

pg_graphql automatically creates a GraphQL schema from your SQL schema:

• Tables and views: Become GraphQL object types.
• Columns: Become fields on those types.
• Foreign keys: Define relationships between types.
• Primary keys: Essential for a table/view to be included. Each type gets a globally unique nodeId: ID! field.

The Node interface

pg_graphql implements the GraphQL Global Object Identification Specification. Every table type with a primary key implements the Node interface and gets a nodeId: ID! field. This nodeId is a globally unique, opaque identifier for a record, useful for client-side caching and refetching specific objects.

Querying data (Query type)

The Query type is the entry point for all read operations.

Collections

For each accessible table (e.g., Book), pg_graphql creates a collection field (e.g., bookCollection) on the Query type. Collections allow you to fetch multiple records and support pagination, filtering, and sorting.

Basic collection fetch

Create a Book table:

CREATE TABLE "Book" (
  id SERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  author TEXT,
  published_year INT
);

INSERT INTO "Book" (title, author, published_year) VALUES
('The Great Gatsby', 'F. Scott Fitzgerald', 1925),
('To Kill a Mockingbird', 'Harper Lee', 1960),
('1984', 'George Orwell', 1949);

COMMENT ON SCHEMA public IS '@graphql({"inflect_names": true})';

Fetch all books

To fetch all books, use the bookCollection field on the Query type. The result is a connection type with edges and node fields.

Run the following SQL query to fetch all books:

SELECT graphql.resolve($$
  query GetAllBooks {
    bookCollection {
      edges {
        node {
          id
          title
          author
        }
      }
    }
  }
$$);

{
  "data": {
    "bookCollection": {
      "edges": [
        { "node": { "id": 1, "title": "The Great Gatsby", "author": "F. Scott Fitzgerald" } },
        { "node": { "id": 2, "title": "To Kill a Mockingbird", "author": "Harper Lee" } },
        { "node": { "id": 3, "title": "1984", "author": "George Orwell" } }
      ]
    }
  }
}

Pagination

Use first to limit results and after with a cursor for pagination.

SELECT graphql.resolve($$
  query PaginateBooks {
    bookCollection(first: 1) { # Get the first book
      edges {
        cursor # Use this cursor for the 'after' argument next time
        node {
          title
        }
      }
      pageInfo {
        endCursor
        hasNextPage
      }
    }
  }
$$);

{
  "data": {
    "bookCollection": {
      "edges": [{ "node": { "title": "The Great Gatsby" }, "cursor": "<opaqueCursorString>" }],
      "pageInfo": { "endCursor": "<opaqueCursorString>", "hasNextPage": true }
    }
  }
}

To get the next page, you'd take endCursor from the pageInfo and use it as the after argument in a subsequent query: bookCollection(first: 1, after: "opaqueCursorString").

Filtering

Use the filter argument. Filterable fields and operators (eq, gt, lt, contains, and, or, not) are generated based on column types.

Find books by George Orwell published after 1940:

SELECT graphql.resolve($$
  query FilteredBooks {
    bookCollection(filter: {
      and: [
        { author: { eq: "George Orwell" } },
        { publishedYear: { gt: 1940 } }
      ]
    }) {
      edges {
        node {
          title
          publishedYear
        }
      }
    }
  }
$$);

{
  "data": {
    "bookCollection": { "edges": [{ "node": { "title": "1984", "published_year": 1949 } }] }
  }
}

Sorting

Use the orderBy argument. The orderBy clause takes a list of fields to sort by, each with a direction. Common direction enums are AscNullsFirst, AscNullsLast, DescNullsFirst, and DescNullsLast.

SELECT graphql.resolve($$
  query SortedBooks {
    bookCollection(orderBy: [{ publishedYear: DescNullsLast }]) {
      edges {
        node {
          title
          publishedYear
        }
      }
    }
  }
$$);

{
  "data": {
    "bookCollection": {
      "edges": [
        { "node": { "title": "To Kill a Mockingbird", "publishedYear": 1960 } },
        { "node": { "title": "1984", "publishedYear": 1949 } },
        { "node": { "title": "The Great Gatsby", "publishedYear": 1925 } }
      ]
    }
  }
}

Modifying data (Mutation type)

The Mutation type is the entry point for write operations.

Inserting records

Use insertInto<Table>Collection.

SELECT graphql.resolve($$
  mutation AddNewBook {
    insertIntoBookCollection(
      objects: [{ title: "Brave New World", author: "Aldous Huxley", publishedYear: 1932 }]
    ) {
      affectedCount
      records { # Returns the inserted records
        id
        title
      }
    }
  }
$$);

{
  "data": {
    "insertIntoBookCollection": {
      "records": [{ "id": 4, "title": "Brave New World" }],
      "affectedCount": 1
    }
  }
}

Updating records

Use update<Table>Collection. Requires a filter to specify which records, a set clause for new values, and atMost as a safety limit.

SELECT graphql.resolve($$
  mutation UpdateBookTitle {
    updateBookCollection(
      filter: { id: { eq: 1 } },
      set: { title: "The Great Gatsby (Revised Edition)" },
      atMost: 1
    ) {
      affectedCount
      records {
        id
        title
      }
    }
  }
$$);

{
  "data": {
    "updateBookCollection": {
      "records": [{ "id": 1, "title": "The Great Gatsby (Revised Edition)" }],
      "affectedCount": 1
    }
  }
}

Deleting records

Use deleteFrom<Table>Collection. Requires a filter and atMost.

SELECT graphql.resolve($$
  mutation DeleteBook {
    deleteFromBookCollection(
      filter: { id: { eq: 1 } },
      atMost: 1
    ) {
      affectedCount
      records { # Returns the deleted records
        id
        title
      }
    }
  }
$$);

{
  "data": {
    "deleteFromBookCollection": {
      "records": [{ "id": 1, "title": "The Great Gatsby (Revised Edition)" }],
      "affectedCount": 1
    }
  }
}

Relationships

pg_graphql automatically infers relationships from foreign key constraints.

Example: Authors and Books

CREATE TABLE "Author" (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE "Book" (
  id SERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  author_id INT REFERENCES "Author"(id) -- Foreign key
);

INSERT INTO "Author" (name) VALUES ('George Orwell');
INSERT INTO "Book" (title, author_id) VALUES ('1984', 1), ('Animal Farm', 1);

Query books and their author:

SELECT graphql.resolve($$
  query BooksWithAuthors {
    bookCollection {
      edges {
        node {
          title
          author { # Field for related Author
            name
          }
        }
      }
    }
  }
$$);

{
  "data": {
    "bookCollection": {
      "edges": [
        { "node": { "title": "1984", "author": { "name": "George Orwell" } } },
        { "node": { "title": "Animal Farm", "author": { "name": "George Orwell" } } }
      ]
    }
  }
}

Query authors and their books:

SELECT graphql.resolve($$
  query AuthorsWithBooks {
    authorCollection {
      edges {
        node {
          name
          bookCollection { # Collection of related Books
            edges {
              node {
                title
              }
            }
          }
        }
      }
    }
  }
$$);

{
  "data": {
    "authorCollection": {
      "edges": [
        {
          "node": {
            "name": "George Orwell",
            "bookCollection": {
              "edges": [{ "node": { "title": "1984" } }, { "node": { "title": "Animal Farm" } }]
            }
          }
        }
      ]
    }
  }
}

Computed fields

You can add fields that are not directly stored columns.

Postgres generated columns

CREATE TABLE "User" (
  id SERIAL PRIMARY KEY,
  first_name TEXT,
  last_name TEXT,
  full_name TEXT GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED
);

INSERT INTO "User" (first_name, last_name) VALUES ('John', 'Doe');

full_name will automatically appear in the User GraphQL type.

SELECT graphql.resolve($$
  query UserFullName {
    userCollection {
      edges {
        node {
          firstName
          lastName
          fullName # Computed field
        }
      }
    }
  }
$$);

{
  "data": {
    "userCollection": {
      "edges": [{ "node": { "lastName": "Doe", "firstName": "John", "fullName": "John Doe" } }]
    }
  }
}

SQL functions

For more complex logic, create an SQL function that takes the table's row type as input.

CREATE FUNCTION get_user_initials(u "User")
RETURNS TEXT
STABLE LANGUAGE SQL
AS $$
  SELECT substr(u.first_name, 1, 1) || substr(u.last_name, 1, 1);
$$;

This would (by default) add a getUserInitials field to the User type. Naming can be customized.

Example:

SELECT graphql.resolve($$
  query UserInitials {
    userCollection {
      edges {
        node {
          firstName
          lastName
          getUserInitials # Custom field
        }
      }
    }
  }
$$);

{
  "data": {
    "userCollection": {
      "edges": [{ "node": { "lastName": "Doe", "firstName": "John", "getUserInitials": "JD" } }]
    }
  }
}

Configuration via comment directives

Customize pg_graphql behavior using comments on SQL objects. Format: COMMENT ON ... IS '@graphql({"key": "value"})';

Renaming

You can rename tables, columns, and types in the GraphQL schema using the @graphql directive.

COMMENT ON TABLE "Book" IS '@graphql({"name": "Publication"})'; -- Book table -> Publication type
COMMENT ON COLUMN "Book".title IS '@graphql({"name": "headline"})'; -- Book.title -> Publication.headline

SELECT graphql.resolve($$
  query RenamedTypes {
    publicationCollection {
      edges {
        node {
          headline
        }
      }
    }
  }
$$);

{
  "data": {
    "publicationCollection": {
      "edges": [{ "node": { "headline": "1984" } }, { "node": { "headline": "Animal Farm" } }]
    }
  }
}

Descriptions

You can add descriptions to tables, columns, and types using the @graphql directive. This is useful for documentation and introspection.

COMMENT ON TABLE "Book" IS '@graphql({"description": "Represents a literary work."})';

SELECT graphql.resolve($$
  query BookDescription {
    __type(name: "Book") {
      description
    }
  }
$$);

{ "data": { "__type": { "description": "Represents a literary work." } } }

totalCount on collections

Enable the totalCount field on a connection type.

COMMENT ON TABLE "Book" IS '@graphql({"totalCount": {"enabled": true}})';

Now bookCollection will have totalCount.

SELECT graphql.resolve($$
  query BookTotalCount {
    bookCollection {
      totalCount
    }
  }
$$);

{ "data": { "bookCollection": { "totalCount": 2 } } }

Views and foreign tables

Views (and materialized views, foreign tables) can be exposed if they have a "virtual" primary key defined via a comment directive:

CREATE VIEW "NewUsers" AS
SELECT * FROM "User"; -- optional WHERE clause as per the view definition

COMMENT ON VIEW "NewUsers" IS '@graphql({"primary_key_columns": ["id"]})';

Now NewUsers will be queryable via GraphQL.

SELECT graphql.resolve($$
  query NewUsers {
    newUsersCollection {
      edges {
        node {
          id
          firstName
          lastName
        }
      }
    }
  }
$$);

{
  "data": {
    "newUsersCollection": {
      "edges": [{ "node": { "id": 1, "lastName": "Doe", "firstName": "John" } }]
    }
  }
}

Security considerations

pg_graphql fully respects Postgres's native security:

• Role permissions: A user querying via pg_graphql can only see/interact with tables, columns, and functions they have SQL permissions for. If a role lacks SELECT on a table, that table won't appear in their GraphQL schema.
• Row-Level Security (RLS): All RLS policies are automatically applied.

While this guide provides a solid foundation, pg_graphql offers a rich set of advanced features not covered here. For a deeper dive into capabilities like exposing complex SQL functions as queries or mutations, advanced filtering techniques including nested logical operators and array operations, fine-tuning schema generation with more comment directives (e.g., for computed relationships on views or custom naming for all elements), handling transactions, performance optimization strategies, and detailed guides for integrating with client libraries like Apollo and Relay, please refer to the official pg_graphql documentation.

Conclusion

pg_graphql offers an efficient way to generate a GraphQL API directly from your Postgres database. By understanding its schema reflection, the graphql.resolve() function, and basic configuration, you can quickly expose your data for flexible querying without needing an external GraphQL server.

Resources

• pg_graphql official documentation
• GraphQL official documentation