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:

Get Set Up

Before you start, ensure you have the following:

Seed your Database from a SQL Server 2019 Express Database

If you already have your own pre-filled database, you can follow our instructions using your own database. If you don't have data in your database, you'll need to seed it with data first, by using this script:

CREATE TABLE authors (
  id int IDENTITY (1,1) PRIMARY KEY,
  name varchar(45) NOT NULL,
  isPseudonym tinyint DEFAULT '0',
);

CREATE TABLE books (
  id int IDENTITY (1,1) PRIMARY KEY,
  name varchar(45) NOT NULL,
  originalPublishingDate date NOT NULL,
  authorID int DEFAULT NULL,
);

INSERT INTO authors
VALUES
('Agatha Christie',0),
('Sir Arthur Conan Doyle',0),
('Edgar Allan Poe',0);

INSERT INTO books
VALUES
('Murder on the Orient Express','1934-01-01', 1),
('The Mysterious Affair at Styles','1920-10-01', 1),
('The Adventure of the Three Garridebs','1924-10-25', 2),
('The Murders in the Rue Morge','1841-04-04', 3);

Let's Connect to MSSQL

Create a file called config.yaml in the root of your project. Ensure you add config.yaml to your .gitignore to keep it from being committed, since it will contain your DSN, username, and password to your database.

The contents of your config.yaml should look like this:

configurationset:
  - configuration:
      name: MSSQL_config
      dsn: sqlserver://USERNAME:PASSWORD@HOSTNAME:PORT?database=DB_NAME

Note: The information that populates USERNAME, PASSWORD, HOSTNAME, PORT and DB_NAME comes from your DSN. Ensure you place it in the format shown above.

This file provides StepZen the information that it needs to connect to your database.

GraphQL Files

StepZen APIs are configured using GraphQL SDL, which is GraphQL's standard language for defining a schema.

Start by creating a book.graphql file in your root project directory. Define a basic Book type with properties and two queries on that type.

  • The first query takes an id and returns a single instance of a Book.
  • The second returns an array of book results that are filtered by their originalPublishingDate. This argument is required as indicated by the !.
type Book {
  id: ID!
  name: String!
  originalPublishingDate: Date!
  authorID: ID!
  author: Author
}
type Query {
  book(id: ID!): Book
  books(originalPublishingDate: Date!): [Book]
}

At this point, your Book type exists but isn't connected to any data. Let's connect it to the MSSQL database.

Start by updating the book query. We'll use StepZen's @dbquery directive to tell StepZen:

  • The query comes from an MSSQL database.
  • The database is populated from the books table.
  • The configuration property tells StepZen to use the MSSQL database configuration you named MSSQL_config in your config.yaml.
book(id: ID!): Book
  @dbquery(type: "mssql", table: "books", configuration: "MSSQL_config")

That's all that is needed for this query. Since the column names match the properties in the Book type, StepZen can figure out the rest. It effectively runs the query: SELECT * FROM books WHERE id = {id}.

The next step is to create an Author type following the same flow as in Books. Create an author.graphql file in the root of your project with the following contents:

type Author {
  id: ID!
  name: String!
  isPseudonym: Boolean!
}
type Query {
  author(id: ID!): Author
    @dbquery(type: "mssql", table: "authors", configuration: "MSSQL_config")
  authors(isPseudonym: Boolean!): [Author]
    @dbquery(
      type: "mssql"
      query: "SELECT * FROM authors WHERE isPseudonym = ?"
      configuration: "MSSQL_config"
    )
}

Connect Types

You now have two types: Book and Author. You can query each, but neither type knows about the other. To fix that, use another StepZen custom directive called @materializer.

Open book.graphql and add an author property that returns the Author type you just created. Use the @materializer directive to tell StepZen how to connect the types. In this case:

  • Name the query author.
  • arguments indicates that you need to pass the authorID from Book as the id argument to the query.
author: Author
  @materializer(
        query: "author"
        arguments: [{ name: "id" field: "authorID"}]
  )

The finished book.graphql file must look like this:

type Book {
  id: ID!
  name: String!
  originalPublishingDate: Date!
  authorID: ID!
    @materializer(
      query: "author"
      arguments: [{ name: "id", field: "authorID" }]
    )
}
type Query {
  book(id: ID!): Book
    @dbquery(type: "mssql", table: "books", configuration: "MSSQL_config")
  books(originalPublishingDate: Date!): [Book]
}

@materializer can also accept field arguments. What do we mean by this? Let's learn by doing and creating an argument on the book.graphql query:

booksPublishedSince(since:Date!): [Book]
        @dbquery(type: "mssql",
            query: "SELECT * FROM books WHERE originalPublishingDate > ? ",
            configuration: "MSSQL_config")

This defines a query that returns all of the books published after the variable is passed in.

In author.graphql, add the value to the variable in a new @materializer directive:

  booksPublishedAfter(publishedAfter:Date!="1900-01-01") : [Book]
  @materializer(
    query: "booksPublishedSince"
    arguments: [
      {name: "since", argument: "publishedAfter"}
    ]
  )

In the arguments section:

  • since is the name of the argument in the query referenced by @materializer.
  • publishedAfter is a field argument annotated by @materializer. At runtime, the value of publishedAfter is used as the value for since.

Now queries look like this:

query MyQuery {
  booksPublishedSince(since: "1920-12-12") {
    authorID
    id
    name
    originalPublishingDate
  }
}

Queries will now return properly-filtered results!

{
  "data": {
    "booksPublishedSince": [
      {
        "authorID": "1",
        "id": "1",
        "name": "Murder on the Orient Express",
        "originalPublishingDate": "1934-01-01"
      },
      {
        "authorID": "2",
        "id": "3",
        "name": "The Adventure of the Three Garridebs",
        "originalPublishingDate": "1924-10-25"
      }
    ]
  }
}

Connect the Schema Files

Every StepZen schema requires an index.graphql file, that tells StepZen how to assemble your schema from the .graphql files.

In this case, you need to include two files: author.graphql and book.graphql. Create an index.graphql file in the root of your project with the following contents:

schema @sdl(files: ["author.graphql", "book.graphql"]) {
  query: Query
}

Deploy and Test Your Schema

You can use the StepZen CLI to deploy and test your API. In your terminal, run this command to deploy to StepZen:

stepzen start

It will ask you to name your endpoint. You can define a name in the [FOLDER_NAME]/[ENDPOINT_NAME] format, or use the default provided. StepZen will open a Schema Explorer that enables you to query your newly-deployed API.

Try running the following query:

{
  book(id: 1) {
    name
    author {
      name
    }
  }
}

You should get a result that pulls data from the MSSQL database, combining both books and authors.

{
  "data": {
    "book": {
      "name": "Murder on the Orient Express",
      "author": {
        "name": "Agatha Christie"
      }
    }
  }
}

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.