Connecting a Presto database

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

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

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

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

Use the arguments in the following sections when you want to connect to a Presto 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, snowflake, and presto.

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: "presto"
    schema: "any_schema"
    table: "customers"
    configuration: "presto_config"
  )

The directive passes the following database query to the database specified by the presto_config configuration (See configuration for more information).

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

where, id, name, email and creditCard are the columns of the Presto table customers 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: Presto's identifiers are case-sensitive, so be careful to match the exact case when naming arguments to correspond with Presto identifiers.

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: "presto",
    schema:"any_schema",
    query: """SELECT "id", "full_name" AS "name", "email" FROM "customer" WHERE "id" = ? AND "creditCard" IS NOT NULL""",
    configuration: "presto_config"
  )

The directive executes the specified SQL query on the database specified by the presto_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.

configuration

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

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

configurationset:
  - configuration:
      name: presto_config
      dsn: "http://presto:presto@localhost:8080/?catalog=memory&schema=default"

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

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

PrestoDB connector capabilities

The PrestoDB connector in API Connect for GraphQL supports the following capabilities:
  • Pagination: Supported for table queries.
  • Filter: Supported for table queries.
  • Sort: Supported for table queries.

For explanation about how to use pagination and filtering with the @dbquery directive, see Using @dbquery for pagination and filtering.