directive @dbquery

If the SQL statement defined by table or query returns rows then the result's columns are mapped to fields in the field's type by name. This requires that the returned column names are valid GraphQL identifiers. With query the AS clause can be used to transform column names, for example SELECT e-mail AS email ... FROM USER WHERE ....

The annotated field's type can be a singleton, e.g. User, a list [User], or a pagination connection type (UserConnection).

A singleton will select a single row and transform it into a GraphQL object, a list will transform all returned rows.

A pagination connection type is handled as the standard GraphQL Cursor Connections Specification (see Pagination section).

If the SQL statement defined by query does not return rows (for example an UPDATE statement) then the field's type must be a singleton object type of type DMLResult:

Support for field of type DMLResult varies by database type:

• postgresql - only rowsAffected is supported.

• mysql

  • rowsAffected is supported.

  • lastInsertId supported for INSERT SQL statements with an AUTO_INCREMENT column.

Note that some database types support a RETURNING (or equivalent) clause for SQL data-change statements, with the clause returning the inserted or changed row(s). This tends to be a more natural approach for GraphQL schemas, so that the mutation can return the new or modified object(s).

For example database type postgresql supports RETURNING clause allowing definitions such as the following which will return a Timesheet object with the database auto-generated columns filled in for fields id and created_at:

type Timesheet {
  id: ID!
  created_at: DateTime!
  day: Date!
  hours: Float!
  comment: String
  reviewed: Boolean
}

type Mutation {
  createTimesheet(day:Date! hours:Float! comment:String):Timesheet
  @dbquery(
    type:"postgresql"
    query: "INSERT INTO timesheet(day, hours, comment) VALUES($1, $2, $3) RETURNING *"
    configuration:"pg"
  )
}

Filtering is supported through a standard method using a filter argument. This argument dynamically establishes filter conditions based on its value at the time of selection.

If the annotated field has an argument named filter with a type that is an input object or list of input objects then the generated SQL has a WHERE clause based upon the value of filter.

Each input field in a filter input object type results in predicates in the generated SQL WHERE clause. The type of an input field is another input object that declares the allowable predicate operators.

The input objects must be declared by the schema, they are not provided by default. This is because the allowable predicate operators are typically application specific, for example only allow a range on a Date field.

For example, this filter argument definition allows returning User objects that have a specific e-mail address or have been users since a given date or optionally within a date range.

input UserFilter {
  email: StringEqFilter
  since: DateRangeFilter

  or: UserFilter
  and: UserFilter
}
input StringEqFilter {
  eq: String
}
input DateRangeFilter {
  ge: Date!
  lt: Date
}
extend type Query {
  users(filter:UserFilter):[User] @dbquery(type:"postgresql" table:"USERS" configuration:"userdb")
}

A value of filter:{email:{eq: "alice@example.com"} since:{ge:"2000-01-01"}} would result in the WHERE clause including the equivalent of email = $1 AND since >= $2.

If the filter argument is not required and not set then it does not contribute predicates to any SQL WHERE clause.

Any required field in the filter type, or in a predicate operator type enforces that the schema must always filter on that field. In the above example since is optional, but if there is a filter on since it must include a starting date since ge is required.

Conversely any optional field allows optional filtering.

Multiple fields within the field argument filter input type and multiple operations in the allowable predicate operators types are treated as conjunctions (with AND).

Field names and, or, not and nor are reserved for logical operations, typically the input type of the logical field is the same as its enclosing type (nullable) to allow arbitrary complex filters using AND and OR.

• and - introduces a new filter input object value whose elements are a conjunction (AND)

• or - introduces a new filter input object value whose elements are a disjunction (OR)

Logical examples (using GraphQL literals):

• filter: {or: {email:{eq: "alice@example.com"} since:{ge: "2000-01-01"}}}

  • SQL: email = ? OR since >= ?

• filter: {or: {since:{ge: "2000-01-01" lt: "2005-12-31"}}}

  • SQL: since >= ? OR since < ?

• filter: {email: {like: "%@example.com"} or: {since: {ge: "2000-01-01" lt: "2005-12-31"}}}

  • SQL: email LIKE ? AND (since >= ? OR since < ?)

The supported predicate operators are:

• eq - equal - SQL =

• ne - not equal - SQL <>

• gt - greater than - SQL >

• ge - greater than or equal - SQL >=

• lt - less than - SQL <

• le - less than or equal - SQL <=

• like - SQL LIKE operator - the operator value is not modified, thus name:{like:"Bob%"} maps to a predicate of name LIKE 'Bob%'

• ilike - case insensitive variant of like

These predicate operators become field names in the allowable predicates input object type to enable specific predicates. The type of these fields must match the unwrapped type of the field being filtered. For example:

input IntFilter {
  eq:Int
  lt:Int
  gt:Int
}

can be used to filter a field that has type Int or Int! allowing =, < and > predicates.

Input objects used for filtering can be used with filter arguments on different fields, for example DateRangeFilter may also be used on a field that returned movies filtered against their release date.

Standard GraphQL Cursor Connections Specification pagination is supported.

When the annotated field's type is a pagination connection type and has field arguments that classify it as a pagination field, the annotated field must include arguments named first and after and the type of the node field in the "Edge Type" must have at least one sortable field.

The SQL generated when the field is resolved will include the required ORDER BY and pagination clauses (for example LIMIT and OFFSET). Pagination clauses are based upon the field's first and after arguments.

Pagination ordering defaults to a consistent ordering, based upon the node type's field and their types.

type Customer {
  id:ID!
  name:String
  email:String
}
# Connection types for Customer, which has at least one sortable field
type CustomerConnection {
  edges: [CustomerEdge]
  pageInfo: PageInfo!
}
type CustomerEdge {
  node: Customer
  cursor: String
}

# Paginated field against the customer database table
extend type Query {
  customers(first:Int!=10 after:String!=""): CustomerConnection
  @dbquery(
  type:"postgresql"
  table: "customer"
  )
}

Pagination ordering can be specified in a GraphQL request by using the custom directive @sort. The ordering is specified by the ordering of the direct selection against the node field.

Here is an example of a request using pagination with ordering specified in the GraphQL request, in this case the pagination will be ordered by email, followed by name.

query Customers($f:Int! $a:String!) {
  customers(first:$f after:$f) @sort {
    edges {
      node {
        email
        name
      }
      cursor
    }
  }
  pageInfo {
    hasNextPage
  }
}

Filtering and pagination can be combined, for example:

# Paginated and filtered field against the customer database table
extend type Query {
  customers(first:Int!=10 after:String!="" filter:CustomerFilter): CustomerConnection
  @dbquery(
  type:"postgresql"
  table: "customer"
  )
}

Filtering is executed prior to pagination, ensuring the request pages through email addresses that match the specified criteria. In this case, it retrieves the first five customers with email addresses in the example.com domain, ordered by customer name.

query {
  customers(first:5 filter:{email:{like:"%@example.com"}}) @sort {
    edges {
      node {
        name
        email
      }
      cursor
    }
  }
  pageInfo {
    hasNextPage
  }
}

Type System: FIELD_DEFINITION