How to Connect to a MSSQL Database

How to use a MSSQL database as a data source for your GraphQL API

StepZen supports MySQL, Postgres, and MSSQL databases. In this tutorial, we'll see how to connect to an MSSQL database:

See also the GraphQL directives Reference.

Getting started

To generate your GraphQL on a MSSQL backend via stepzen import mssql, see Getting started with a MySQL Database documentation. Note: There are minor differences between connecting to an MSSQL and MySQL database, as you can read below in the configuration properties for type and configuration.

Extending Your GraphQL API

Any Query or Mutation field in your StepZen GraphQL schema can be annotated with the @dbquery directive that helps you write, extend of customze the API you've built on a database backend.

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

@dbquery enables you to connect a MySQL, PostgreSQL, or MSSQL 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. Currently, supported values are mysql, postgresql, and mssql.

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: "mssql"
    table: "customers"
    configuration: "mssql_config"
  )
}

This passes the database query:

SELECT * FROM customers WHERE id = [VALUE OF ID ARGUMENT]

to the databased specified by the mssql_config configuration (See below for more about configurations). If the annotated field has multiple arguments, they are combined in the SQL WHERE clause with an AND.

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: "mssql",
    query: "SELECT id, full_name AS name, email FROM customer WHERE id = ? AND creditCard is not NULL",
    configuration: "mssql_config"
  )

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

This argument is required and identifies which configuration in the config.yaml file should be used to connect to the database. A MSSQL database configuration contains the dsn for connecting to your database, and will look similar to this:

configurationset:
  - configuration:
      name: mssql_config
      dsn: "sqlserver://username:password@host:port?database=dbname"

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

To learn more about the configuration settings for connecting to your MSSQL database, see MSSQL 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.

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: "mssql"
      table: "customer"
      dml: INSERT
      configuration: "mssql_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 ($1, $2, $3)
SELECT id, name, email FROM customer WHERE id = $1 AND name = $2 and email = $3

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

type Mutation {
  removeCustomerById(id: ID!): Customer
    @dbquery(
      type: "mssql"
      table: "customer"
      dml: DELETE
      configuration: "mssql_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 PostgreSQL backend, resulting in the removal of the customer wiht the specified id and using the deleted values to populate the returned GraphQL Customer type:

SELECT id, name, email FROM customer WHERE id = $1
DELETE FROM customer WHERE id = $1

This site uses cookies: By using this website, you consent to our use of cookies in accordance with our Website Terms of Use and Cookie Policy.