PostgreSQL Tables

PostGraphile automatically adds a number of elements to the generated GraphQL schema based on the tables and columns found in the inspected schema.

An example of a PostgreSQL table is:

CREATE TABLE app_public.users (
  id serial PRIMARY KEY,
  username citext NOT NULL unique,
  name text NOT NULL,
  about text,
  organization_id int NOT NULL
    REFERENCES app_public.organizations ON DELETE CASCADE,
  is_admin boolean NOT NULL DEFAULT false,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

For a table like this, PostGraphile will:

  • Create a GraphQL type, User, for the table, named in UpperCamelCase & singularized (inflector: tableType).

  • Add to related table types:

  • Add to the root Query type:

    • An allUsers connection field with pagination, filtering, and ordering (inflector: allRows)
    • A number of userByKey(key: ...) fields (e.g. userById, userByUsername), one for each of the unique constraints on the table (inflector: rowByUniqueKeys)
    • A foo(nodeId: ID!) field to get the row by its nodeId
    • Add CRUD Mutations to the root Mutation type

    * Remember these fields can be simplified by loading the @graphile-contrib/pg-simplify-inflector plugin.

    Read more about relations, connections, filtering and CRUD Mutations.

    Permissions

    If you're using --no-ignore-rbac or ignoreRBAC: false (highly recommended) then PostGraphile will only expose the tables/columns/fields you have access to. For example if you perform GRANT UPDATE (username, name) ON users TO graphql_visitor; then the updateUser mutations will only accept username and name fields - the other columns will not be present.

    Note that the --no-ignore-rbac (or ignoreRBAC: false in the library) inspects the RBAC (GRANT / REVOKE) privileges in the database and reflects these in your GraphQL schema. As is GraphQL best practices, this still only results in one GraphQL schema (not one per user), so it takes the user account you connect to PostgreSQL with (from your connection string) and walks all the roles that this user can become within the database, and uses the union of all these permissions. Using this flag is recommended, as it results in a much leaner schema that doesn't contain functionality that you can't actually use.

    * NOTE: We strongly advise against using column-based SELECT grants with PostGraphile. Instead, split your permission concerns into separate tables and join them with one-to-one relations.