Reflection

SQL tables are reflected into GraphQL types with columns and foreign keys represented as fields on those types.

Naming

By default, PostgreSQL table and column names are not adjusted when reflecting GraphQL type and field names. For example, an account_holder table has GraphQL type name account_holder and can be queried via the account_holderCollection field of the Query type.

In cases, like the previous example, where the SQL name is snake_case, you'll likely want to enable inflection to re-case names to match GraphQL/Javascript conventions e.g. AccountHolder and accountHolderCollection.

Table, column, and relationship type and field names may also be manually overridden as needed.

Type Conversion

Connection Types

Connection types hande pagination best practices according to the relay spec. pg_graphql uses keyset pagination to enable consistent retrival times on every page.

Example

The following is a complete example showing how a sample SQL schema translates into a GraphQL schema.

-- Turn on automatic inflection of type names
comment on schema public is '@graphql({"inflect_names": true})';

create table account(
    id serial primary key,
    email varchar(255) not null,
    created_at timestamp not null,
    updated_at timestamp not null
);

-- enable a `totalCount` field on the `account` query type
comment on table account is e'@graphql({"totalCount": {"enabled": true}})';

create table blog(
    id serial primary key,
    owner_id integer not null references account(id),
    name varchar(255) not null,
    description varchar(255),
    created_at timestamp not null,
    updated_at timestamp not null
);

create type blog_post_status as enum ('PENDING', 'RELEASED');

create table blog_post(
    id uuid not null default uuid_generate_v4() primary key,
    blog_id integer not null references blog(id),
    title varchar(255) not null,
    body varchar(10000),
    status blog_post_status not null,
    created_at timestamp not null,
    updated_at timestamp not null
);

type Account {
  id: Int!
  email: String!
  createdAt: DateTime!
  updatedAt: DateTime!
  blogCollection(
    """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: BlogFilter

    """Sort order to apply to the collection"""
    orderBy: [BlogOrderBy!]
  ): BlogConnection
}

type AccountConnection {
  edges: [AccountEdge!]!
  pageInfo: PageInfo!

  """The total number of records matching the `filter` criteria"""
  totalCount: Int!
}

type AccountDeleteResponse {
  """Count of the records impacted by the mutation"""
  affectedCount: Int!

  """Array of records impacted by the mutation"""
  records: [Account!]!
}

type AccountEdge {
  cursor: String!
  node: Account
}

input AccountFilter {
  id: IntFilter
  email: StringFilter
  createdAt: DateTimeFilter
  updatedAt: DateTimeFilter
}

input AccountInsertInput {
  email: String
  createdAt: DateTime
  updatedAt: DateTime
}

type AccountInsertResponse {
  """Count of the records impacted by the mutation"""
  affectedCount: Int!

  """Array of records impacted by the mutation"""
  records: [Account!]!
}

input AccountOrderBy {
  id: OrderByDirection
  email: OrderByDirection
  createdAt: OrderByDirection
  updatedAt: OrderByDirection
}

input AccountUpdateInput {
  email: String
  createdAt: DateTime
  updatedAt: DateTime
}

type AccountUpdateResponse {
  """Count of the records impacted by the mutation"""
  affectedCount: Int!

  """Array of records impacted by the mutation"""
  records: [Account!]!
}

scalar BigInt

"""
Boolean expression comparing fields on type "BigInt"
"""
input BigIntFilter {
  eq: BigInt
  gt: BigInt
  gte: BigInt
  lt: BigInt
  lte: BigInt
  neq: BigInt
}

type Blog {
  id: Int!
  ownerId: Int!
  name: String!
  description: String
  createdAt: DateTime!
  updatedAt: DateTime!
  blogPostCollection(
    """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: BlogPostFilter

    """Sort order to apply to the collection"""
    orderBy: [BlogPostOrderBy!]
  ): BlogPostConnection
  owner: Account
}

type BlogConnection {
  edges: [BlogEdge!]!
  pageInfo: PageInfo!
}

type BlogDeleteResponse {
  """Count of the records impacted by the mutation"""
  affectedCount: Int!

  """Array of records impacted by the mutation"""
  records: [Blog!]!
}

type BlogEdge {
  cursor: String!
  node: Blog
}

input BlogFilter {
  id: IntFilter
  ownerId: IntFilter
  name: StringFilter
  description: StringFilter
  createdAt: DateTimeFilter
  updatedAt: DateTimeFilter
}

input BlogInsertInput {
  ownerId: Int
  name: String
  description: String
  createdAt: DateTime
  updatedAt: DateTime
}

type BlogInsertResponse {
  """Count of the records impacted by the mutation"""
  affectedCount: Int!

  """Array of records impacted by the mutation"""
  records: [Blog!]!
}

input BlogOrderBy {
  id: OrderByDirection
  ownerId: OrderByDirection
  name: OrderByDirection
  description: OrderByDirection
  createdAt: OrderByDirection
  updatedAt: OrderByDirection
}

type BlogPost {
  id: UUID!
  blogId: Int!
  title: String!
  body: String
  status: String!
  createdAt: DateTime!
  updatedAt: DateTime!
  blog: Blog
}

type BlogPostConnection {
  edges: [BlogPostEdge!]!
  pageInfo: PageInfo!
}

type BlogPostDeleteResponse {
  """Count of the records impacted by the mutation"""
  affectedCount: Int!

  """Array of records impacted by the mutation"""
  records: [BlogPost!]!
}

type BlogPostEdge {
  cursor: String!
  node: BlogPost
}

input BlogPostFilter {
  id: UUIDFilter
  blogId: IntFilter
  title: StringFilter
  body: StringFilter
  status: StringFilter
  createdAt: DateTimeFilter
  updatedAt: DateTimeFilter
}

input BlogPostInsertInput {
  id: UUID
  blogId: Int
  title: String
  body: String
  status: String
  createdAt: DateTime
  updatedAt: DateTime
}

type BlogPostInsertResponse {
  """Count of the records impacted by the mutation"""
  affectedCount: Int!

  """Array of records impacted by the mutation"""
  records: [BlogPost!]!
}

input BlogPostOrderBy {
  id: OrderByDirection
  blogId: OrderByDirection
  title: OrderByDirection
  body: OrderByDirection
  status: OrderByDirection
  createdAt: OrderByDirection
  updatedAt: OrderByDirection
}

enum BlogPostStatus {
  PENDING
  RELEASED
}

input BlogPostUpdateInput {
  id: UUID
  blogId: Int
  title: String
  body: String
  status: String
  createdAt: DateTime
  updatedAt: DateTime
}

type BlogPostUpdateResponse {
  """Count of the records impacted by the mutation"""
  affectedCount: Int!

  """Array of records impacted by the mutation"""
  records: [BlogPost!]!
}

input BlogUpdateInput {
  ownerId: Int
  name: String
  description: String
  createdAt: DateTime
  updatedAt: DateTime
}

type BlogUpdateResponse {
  """Count of the records impacted by the mutation"""
  affectedCount: Int!

  """Array of records impacted by the mutation"""
  records: [Blog!]!
}

"""
Boolean expression comparing fields on type "Boolean"
"""
input BooleanFilter {
  eq: Boolean
  gt: Boolean
  gte: Boolean
  lt: Boolean
  lte: Boolean
  neq: Boolean
}

scalar Cursor

scalar DateTime

"""
Boolean expression comparing fields on type "DateTime"
"""
input DateTimeFilter {
  eq: DateTime
  gt: DateTime
  gte: DateTime
  lt: DateTime
  lte: DateTime
  neq: DateTime
}

"""
Boolean expression comparing fields on type "Float"
"""
input FloatFilter {
  eq: Float
  gt: Float
  gte: Float
  lt: Float
  lte: Float
  neq: Float
}

"""
Boolean expression comparing fields on type "Int"
"""
input IntFilter {
  eq: Int
  gt: Int
  gte: Int
  lt: Int
  lte: Int
  neq: Int
}

scalar JSON

"""
Boolean expression comparing fields on type "JSON"
"""
input JSONFilter {
  eq: JSON
  neq: JSON
}

"""The root type for creating and mutating data"""
type Mutation {
  """Deletes zero or more records from the collection"""
  deleteFromAccountCollection(
    """Restricts the mutation's impact to records matching the critera"""
    filter: AccountFilter

    """
    The maximum number of records in the collection permitted to be affected
    """
    atMost: Int! = 1
  ): AccountDeleteResponse!

  """Deletes zero or more records from the collection"""
  deleteFromBlogCollection(
    """Restricts the mutation's impact to records matching the critera"""
    filter: BlogFilter

    """
    The maximum number of records in the collection permitted to be affected
    """
    atMost: Int! = 1
  ): BlogDeleteResponse!

  """Deletes zero or more records from the collection"""
  deleteFromBlogPostCollection(
    """Restricts the mutation's impact to records matching the critera"""
    filter: BlogPostFilter

    """
    The maximum number of records in the collection permitted to be affected
    """
    atMost: Int! = 1
  ): BlogPostDeleteResponse!

  """Adds one or more `AccountInsertResponse` records to the collection"""
  insertIntoAccountCollection(objects: [AccountInsertInput!]!): AccountInsertResponse

  """Adds one or more `BlogInsertResponse` records to the collection"""
  insertIntoBlogCollection(objects: [BlogInsertInput!]!): BlogInsertResponse

  """Adds one or more `BlogPostInsertResponse` records to the collection"""
  insertIntoBlogPostCollection(objects: [BlogPostInsertInput!]!): BlogPostInsertResponse

  """Updates zero or more records in the collection"""
  updateAccountCollection(
    """
    Fields that are set will be updated for all records matching the `filter`
    """
    set: AccountUpdateInput!

    """Restricts the mutation's impact to records matching the critera"""
    filter: AccountFilter

    """
    The maximum number of records in the collection permitted to be affected
    """
    atMost: Int! = 1
  ): AccountUpdateResponse!

  """Updates zero or more records in the collection"""
  updateBlogCollection(
    """
    Fields that are set will be updated for all records matching the `filter`
    """
    set: BlogUpdateInput!

    """Restricts the mutation's impact to records matching the critera"""
    filter: BlogFilter

    """
    The maximum number of records in the collection permitted to be affected
    """
    atMost: Int! = 1
  ): BlogUpdateResponse!

  """Updates zero or more records in the collection"""
  updateBlogPostCollection(
    """
    Fields that are set will be updated for all records matching the `filter`
    """
    set: BlogPostUpdateInput!

    """Restricts the mutation's impact to records matching the critera"""
    filter: BlogPostFilter

    """
    The maximum number of records in the collection permitted to be affected
    """
    atMost: Int! = 1
  ): BlogPostUpdateResponse!
}

"""Defines a per-field sorting order"""
enum OrderByDirection {
  AscNullsFirst
  AscNullsLast
  DescNullsFirst
  DescNullsLast
}

type PageInfo {
  endCursor: String
  hasNextPage: Boolean!
  hasPreviousPage: Boolean!
  startCursor: String
}

"""The root type for querying data"""
type Query {
  """A pagable collection of type `Account`"""
  accountCollection(
    """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

  """A pagable collection of type `Blog`"""
  blogCollection(
    """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: BlogFilter

    """Sort order to apply to the collection"""
    orderBy: [BlogOrderBy!]
  ): BlogConnection

  """A pagable collection of type `BlogPost`"""
  blogPostCollection(
    """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: BlogPostFilter

    """Sort order to apply to the collection"""
    orderBy: [BlogPostOrderBy!]
  ): BlogPostConnection
}

"""
Boolean expression comparing fields on type "String"
"""
input StringFilter {
  eq: String
  gt: String
  gte: String
  lt: String
  lte: String
  neq: String
}

scalar UUID

"""
Boolean expression comparing fields on type "UUID"
"""
input UUIDFilter {
  eq: UUID
  neq: UUID
}