directive @dbquery
The @dbquery
directive specifies the database server for a Query
or Mutation
field.
When the field is resolved, a SQL statement is executed against the database server, and the resulting result set or update count is used to populate the annotated field's type.
The @dbquery
directive can only annotate fields in type Query
or Mutation
that return an object type (regardless of wrapping).
The @dbquery
directive enables you to connect to following databases:
Microsoft SQL Server SQL query engine:
type : "mssql"
Configuration:
name
- Configuration entry's namedsn
-"sqlserver://username:password@host:port?database=dbname"
Features:
Supports data manipulation operations
Supports filtering, pagination, and sorting capabilities
MySQL SQL query engine:
type : "mysql"
Configuration:
name
- Configuration entry's namedsn
-"username:password@tcp(a.b.c.d:port)/dbname"
Features:
Supports data manipulation operations
Supports filtering, pagination, and sorting capabilities
PostgreSQL SQL query engine:
type : "postgresql"
Configuration:
name
- Configuration entry's namedsn
-"postgresql://username:password@address:port/dbname"
Features:
Supports data manipulation operations
Supports filtering, pagination, and sorting capabilities
Supports Twig syntax, array handling, and JSON types
Presto SQL query engine:
type : "presto"
Configuration:
name
- Configuration entry's namedsn
-"protocol://username:password@account_identifier[:port]/catalog/schema"
Features:
Presto operates in read-only mode
Supports filtering, pagination, and sorting capabilities
SingleStore distributed SQL database:
type: "singlestore"
Configuration:
name
- Configuration entry's namedsn
-"username:password@tcp(hostname)/database?tls=true"
Features:
SingleStore operates in read-only mode
Supports filtering and pagination capabilities
Snowflake SQL query engine:
type: "snowflake"
Configuration:
name
- Configuration entry's namedsn
-"username:password@account_identifier/database/schemaname?warehouse=warehousename"
Features:
Snowflake operates in read-only mode
Supports filtering, pagination, and sorting capabilities
Trino SQL query engine:
type: "trino"
Configuration:
name
- Configuration entry's namedsn
-"protocol://username@hostname[:port]?catalog=<catalog_name>&schema=<schema_name>"
Features:
Trino operates in read-only mode
Supports filtering, pagination, and sorting capabilities
Mapping database columns to GraphQL fields
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
- onlyrowsAffected
is supported.mysql
rowsAffected
is supported.lastInsertId
supported forINSERT
SQL statements with anAUTO_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
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, thusname:{like:"Bob%"}
maps to a predicate ofname 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.
Pagination
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
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
}
}
Arguments
configuration: String
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.
dml: DBStatement
specifies the type of DML SQL statement to be generated
when the annotated field is in Mutation
and table
is specified.
query: String
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: String
schema
optionally specifies the database schema of table
for databases that support schemas within a database.
For type:"postgresql"
schema
can also be used withquery
to specify the database schema used to resolve any unqualified table names.setting
schema
will override any setting ofsearch_path
in the PostgreSQL URI.
table: String
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: String!
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)
Locations
Type System: FIELD_DEFINITION