StepZen supports MySQL, Postgres, MSSQL, and Snowflake databases. In this tutorial, we'll see how to connect to a Snowflake database:
See also the GraphQL directives Reference.
Extending Your GraphQL API
Any Query
or Mutation
field in your StepZen 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!)
@dbquery
enables you to connect a MySQL, PostgreSQL, MSSQL, or Snowflake database. For more, see the GraphQL directives Reference.
Directive Arguments
The available arguments to the @dbquery
directive are:
type
This argument is required, and specifies the type of database to query. Supported values are mysql
, postgresql
, mssql
, and snowflake
.
table
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]
. StepZen assumes that the column names of the underlying database table will match the field names of the GraphQL type of the annotated field. 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: "snowflake" table: "customers" configuration: "snowflake_config" )
The above directive passes the following database query to the database specified by the snowflake_config
configuration (See below for more about configurations)
SELECT "id", "name", "email", "creditCard" FROM "customers" WHERE "id" = ?
where, id
, name
, email
and creditCard
are the columns of the Snowflake 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: Snowflake's comparisons are case-sensitive, so care must be given to the naming of arguments to match the Snowflake columns.
query
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: "snowflake", query: 'SELECT "id", "full_name" AS "name", "email" FROM "customer" WHERE "id" = ? AND "creditCard" is not NULL', configuration: "snowflake_config" )
The above directive executes the specified SQL query
on the database specified by the snowflake_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 Snowflake are converted to upper case, so care must be given to match the referenced columns in the specified query to the Snowflake columns.
configuration
This argument is required and identifies which configuration in the config.yaml
file should be used to connect to the database. A Snowflake database configuration contains the dsn
for connecting to your database, and will look similar to this:
configurationset: - configuration: name: snowflake_config dsn: "username:password@account_identifier/database/schemaname?warehouse=warehousename"
In this example, snowflake_config
is the named configuration that will be referenced by the configuration
property of @dbquery
as configuration: snowflake_config
.
To learn more about the configuration settings for connecting to your Snowflake database, see Snowflake Configuration.
dml
This argument is optional and is used 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
. Cannot be set when query
is set.
Note: Ensure you declare the value without surrounding it in quotes, since
dml
is an enum and not a string.
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: "snowflake" table: "customer" dml: INSERT configuration: "snowflake_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:
INSERT INTO "customer"("id", "name", "email") VALUES (?, ?, ?) SELECT "id", "name", "email" FROM "customer" WHERE "id" = ? AND "name" = ? and "email" = ?
Next, let's look at an example of using DELETE
:
type Mutation { removeCustomerById(id: ID!): Customer @dbquery( type: "snowflake" table: "customer" dml: DELETE configuration: "snowflake_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 Snowflake 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 "customer" WHERE "id" = ? DELETE FROM "customer" WHERE "id" = ?