Connecting a Db2 database

You can use a Db2 database as a data source for your GraphQL API by extending your GraphQL API with the @dbquery directive.

Any Query or Mutation field in your GraphQL schema can be annotated with the @dbquery directive to connect to a database backend.

@dbquery (type: String!, query: String, dml: enum, table: String, configuration: String!)

For more information on the @dbquery directive, see Directives.

Use the arguments in the following sections when you want to connect to a Db2 database as a data source for your GraphQL API.

`type`

Required. This argument specifies the type of database to query. Supported values are mysql, postgresql, mssql, trino, presto, singlestore, db2, and snowflake.

`table`

Optional. The value of this argument is the name of the database table to be queried. While this value is optional, one of either table or query must be specified.

Using the table argument is the equivalent of writing select * from [table]. The field names of the GraphQL type of the annotated field must match the column names of the underlying database table. Thus, if the table has a NAME column, it will populate the NAME field of the GraphQL type.

If the annotated field has arguments, they are used to construct the WHERE clause of the SQL query. For example, let's look at the following annotated field:

customerById (id: ID!): Customer
  @dbquery (
    type: "db2"
    table: "customers"
    configuration: "db2_config"
  )

This example passes the following database query to the database specified by the db2_config configuration (See Managing backend connections for more information about configurations).

SELECT "id", "name", "email", "creditCard" FROM "customers" WHERE "id" = ?

where id, name, email and creditCard are the columns of the Db2 customers table that match the fields of the Customer type.

If the annotated field has multiple arguments, they are combined in the SQL WHERE clause with an AND.

Note: Db2 comparisons are case-sensitive, so be careful when naming the arguments to match the Db2 columns.

query

Optional. The value of this argument is the SQL query whose results are used to populate the sub-fields of the annotated field. While this value is optional, one of either table or query must be specified.

The query argument is useful when you need to perform a complex query, or when the table column names and GraphQL type fields do not match. For example:

customerById (id: ID!): Customer
  @dbquery (
    type: "db2",
    query: 'SELECT "id", "full_name" AS "name", "email" FROM "customer" WHERE "id" = ? AND "creditCard" is not NULL',
    configuration: "db2_config"
  )

The directive executes the specified SQL query on the database specified by the db2_config. The SQL query both renames full_name to name so it matches the field name in the GraphQL type Customer, and retrieves only those customers who have a credit card.

Note: Unquoted column names in Db2 are converted to lower case, so make sure that you match the referenced columns in the specified query to the Db2 columns.

`configuration`

Required. This argument identifies which configuration in the config.yaml file should be used to connect to the database.

A Db2 database configuration contains the url for connecting to your database, and will look similar to this:

configurationset:
  - configuration:
      name: db2_config
      url:  jdbc:db2://<host>:<port>/<database>:user=<user>;password=<password>;sslConnection=<true/false>;
currentSchema=<schema>

In this example, db2_config is the named configuration that will be referenced by the configuration property of @dbquery as configuration: db2_config.

To learn more about the configuration settings for connecting to your Db2 database, see Db2 configuration.

`dml`

Optional. Use this argument when the annotated field is a mutation. Its value is an enum that specifies the type of mutation being performed. Valid values are INSERT and DELETE (do not enclose the value in quotation marks because it is an enum).

The following is an example of a mutation with a annotated field whose dml argument value is INSERT:

type Mutation {
  addCustomerById(id: ID!, name: String!, email: String!): Customer
    @dbquery(
      type: "db2"
      table: "customer"
      dml: INSERT
      configuration: "db2_config"
    )
}

The selection of the addCustomer field of this mutation results in the execution of an insert statement followed by a select statement in the database Backend, adding the customer and using the inserted values to populate the returned GraphQL Customer type:

SELECT "id", "name", "email" FROM FINAL TABLE (INSERT INTO "customer"("id", "name", "email") VALUES (?, ?, ?))

Next, let's look at an example of using DELETE:

type Mutation {
  removeCustomerById(id: ID!): Customer
    @dbquery(
      type: "db2"
      table: "customer"
      dml: DELETE
      configuration: "db2_config"
    )
}

The selection of the removeCustomerById field of this mutation results in the execution of a select statement followed by a delete statement in the Db2 backend, resulting in the removal of the customer with the specified id and using the deleted values to populate the returned GraphQL Customer type:

SELECT "id", "name", "email" FROM OLD TABLE (DELETE FROM "customer" WHERE "id" = ?)