directive @dbquery

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
}
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 filter type and multiple operations in the allowable predicate operators types are treated as conjunctions (with AND).

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%'

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
  }
}

names the configuration entry that contains the connection information for the database to be used.

The named configuration may also contain values for schema, table and/or query which override the corresponding arguments supplied to the directive. Typically this is only used to set schema to allow switching between development, test and production schemas.

specifies the type of DML SQL statement to be generated when the annotated field is in Mutation and table is specified.

query defines the SQL statement that will be issued when the field is executed. SQL parameters correspond to the GraphQL arguments. The parameter order is the argument order in the annotated field. For example, in a SQL query with two "?" parameters and a field with arguments name:String!, email:String!, the first "?" will receive the value of name and the second the value of email.

When the annotated field returns a pagination Connection type, the SQL query must include ORDER BY and limit & offset clauses. For postgresql and mysql types, the pagination must be specified in the SQL using LIMIT and OFFSET. The parameter marker corresponding to field argument first is used as the LIMIT value, and the parameter marker corresponding to field argument after is used as the OFFSET argument.

type Customer {
  id:ID!
  name:String
  email:String
}
# Connection types for Customer
type CustomerConnection {
  edges: [CustomerEdge]
  pageInfo: PageInfo!
}
type CustomerEdge {
  node: Customer
  cursor: String
}

# MySQL example
type Query {
  customers(first:Int!=10 after:String!=""): CustomerConnection
  @dbquery(
    type:"mysql"
    query: "SELECT * FROM customer ORDER BY name LIMIT ? OFFSET ?"
  )
}

# PostgreSQL example
type Query {
  customers(first:Int!=10 after:String!=""): CustomerConnection
  @dbquery(
    type:"postgresql"
    query: "SELECT * FROM customer ORDER BY name LIMIT $1 OFFSET $2"
  )
}

All of the field's declared arguments are always passed as SQL parameters, if a value is null then its corresponding SQL parameter will be explicitly set to NULL.

schema optionally specifies the database schema of table for databases that support schemas within a database.

For type:"postgresql"

• schema can also be used with query to specify the database schema used to resolve any unqualified table names.

• setting schema will override any setting of search_path in the PostgreSQL URI.

table specifies the table to be queried or modified.

When table is specified, the SQL statement is dynamically generated based on the field's arguments, as well as the values of schema and dml.

Only one of arguments query or table can be set.

Fields in the root operation type Query are used to select rows from the table. Optionally, they can include filtering and pagination, which are inferred from the field's declarative definition.

Fields in the root operation type Mutation modify the specified table based upon the setting of the dml argument.

type defines the database or backend type.

Supported values for type include:

• mssql (Microsoft SQL Server)

• mysql (MySQL)

• postgresql (PostgreSQL)

• presto (Presto SQL Query Engine)

• singlestore (SingleStore)

• snowflake (Snowflake)

• trino (Trino SQL Query Engine)