Blog Post

Azure Integration Services Blog
4 MIN READ

Expose your data from Azure Cosmos DB or Azure SQL through a GraphQL API with Azure API Management

akamenev's avatar
akamenev
Icon for Microsoft rankMicrosoft
Jun 13, 2023

Today, we’re announcing the public preview of two GraphQL resolver types for Synthetic GraphQL in Azure API Management: Azure Cosmos DB and Azure SQL resolvers. If you ever faced the challenge of exposing your database through GraphQL, you’re aware of the complexities involved. It typically involves writing numerous lines of code, dealing with resolvers, and figuring out the nuances of database access. But is there a simpler way? 

 

Enter Azure Cosmos DB and Azure SQL data types in Synthetic GraphQL for Azure API Management. Upload your GraphQL schema and expose desired data using familiar database concepts – a seamless and straightforward process. In this blog post, I will walk you through the process of adding a GraphQL resolver for a Cosmos DB backend and demonstrate how to test it. 

 

An example: The To-do List Demo 

Let’s look at the to-do list application as an example. Every to-do item has an id, title, and status. I already have some of the items in Azure Cosmos DB, which has the following structure, and want to enable a list of simple CRUD operations on it. 

 

{ 
    "id": "1", 
    "title": "Test1", 
    "completed": false 
} 

 

Create a GraphQL Schema and a Synthetic GraphQL API

I can model this to-do item application by creating the following GraphQL schema:

 

type TodoItem { 
  id: ID! 
  title: String! 
  completed: Boolean! 
} 

input CreateTodoItemInput { 
    id: ID! 
    title: String! 
    completed: Boolean! 
} 

input ReplaceTodoItemInput { 
  id: ID! 
  title: String! 
  completed: Boolean! 
} 

type Query { 
  todoItems: [TodoItem] 
  todoItem(id: ID!): TodoItem 
} 

type Mutation { 
  createTodoItem(input: CreateTodoItemInput!): TodoItem! 
  replaceTodoItem(input: ReplaceTodoItemInput!): TodoItem! 
  deleteTodoItem(id: ID!): Boolean 
} 

 

To create this GraphQL API within the Azure API Management service: 

  • Open the Azure portal in your browser 
  • Select your existing Azure API Management service (or create a new one) 
  • Select the APIs blade 
  • Select GraphQL to create a new GraphQL API 
  • Fill in the form: 
    • Choose a Display name (Todo-GraphQL) 
    • The name field will auto-fill with a suitable name 
    • Select Synthetic GraphQL 
    • Select the file that contains the GraphQL Schema 
    • Choose an API URL suffix (todogql) 
  • Select Create to create the API

 

Create a resolver 

With the addition of Azure Cosmos DB and Azure SQL, you can now select what data source you want to use when you’re configuring your resolvers – HTTP API, Azure SQL or Azure Cosmos DB. Let’s create a Cosmos DB resolver for a 'todoItem' query: 

  • Select the Resolvers tab and then select + Create 
    • Set the name for a resolver, type, and field to target for this resolver 
    • Select a data source type you want to use – Azure Cosmos DB 

When you choose a specific data source, the resolver policy editor gets a pre-populated XML document that defines how to query a database and retrieve the data we need. For the todoItem query I need to get an element by ID, so I create the following policy: 

 

<cosmosdb-data-source> 
    <connection-info> 
        <connection-string>”replace-cosmos-db-connection-string”</connection-string> 
        <database-name>tododemo</database-name> 
        <container-name>todo</container-name> 
    </connection-info> 
    <read-request> 
        <id>@(context.GraphQL.Arguments["id"].ToString())</id> 
        <partition-key>@(context.GraphQL.Arguments["id"].ToString())</partition-key> 
    </read-request> 
</cosmosdb-data-source> 

 

Note: For simplicity, I put a connection string directly into the policy. However, you should not store secrets as plain text. Instead, you should use Named Values connected to a secret in a Key Vault.

 

This is a relatively simple resolver. I specified my connection information for an Azure Cosmos DB including connection string, database name and a container name. In the following <read-request> section I take an id argument from the GraphQL request context and use it to perform a query against the database. You can use read, write, and delete requests, which hide some of the complexity of interacting with the database, but you can also write your own queries with the <query-request /> policy section. 

 

Test the API 

Now when I uploaded the schema and created a resolver for a query, I can test it in a built-in GraphQL test console: 

  • Select the Test tab 
  • Modify the GraphQL query 
  • Select Send to send a request 

Having successfully obtained the necessary data from Azure Cosmos DB using a GraphQL API, we can now generate additional resolvers – one for each query and mutation following the same process that we used before. Check out the documentation for more information. 

 

Next steps 

Azure Cosmos DB and Azure SQL resolvers are in public preview, give it a try and let us know what you think in the comments below! 

Updated Jun 13, 2023
Version 1.0

6 Comments

  • javasparrow796's avatar
    javasparrow796
    Copper Contributor

    Hi Andrei

     

    I am currently trying to use the SQL Database Resolver.

    I referred to the following URL.

    https://learn.microsoft.com/en-gb/azure/api-management/sql-data-source-policy

     

    I have defined the following schema and resolver.

    Schema

    type Query {
      getUsers: UserList!
    }
    
    type User {
      id: ID!
      name: String!
      age: Int!
    }
    
    type UserList {
      items: [User!]!
    }

     

    Resolver

    <sql-data-source>
        <connection-info>
            <connection-string use-managed-identity="true">
                Server=tcp:my-sqlserver.windows.net,1433;Initial Catalog=my-sqldb;
            </connection-string>
        </connection-info>
        <request>
            <sql-statement>
                SELECT
                    u.id
                    u.name
                    u.age
                FROM
                    users u
            </sql-statement>
        </request>
        <response />
    </sql-data-source>

     

    When I run the test, the following response is returned.

    {
      "errors": [
        {
          "message": "Error trying to resolve field 'getUsers'.",
          "locations": [
            {
              "line": 2,
              "column": 2
            }
          ],
          "path": [
            "getUsers"
          ],
          "extensions": {
            "code": "FILE_NOT_FOUND",
            "codes": [
              "FILE_NOT_FOUND"
            ]
          }
        }
      ],
      "data": null
    }

     

    The following error message was output in the trace information.

    Could not load file or assembly 'Microsoft.Data.SqlClient, Version=5.0.0.0, Culture=neutral, PublicKeyToken=xxxxxxxxxxxxxxxx' or one of its dependencies. The system cannot find the file specified.

     

    Do you know what the cause is?

  • sajins2005's avatar
    sajins2005
    Copper Contributor

     I got it working by mapping items, using set-body.

     

     

    <response>
    <set-body> 
    @{ var response = context.Response.Body.As<JObject>();
     return response["items"].ToString();
     }
     </set-body> 
    </response>

     

     

     

    If you are not using response element, Resolver try to map entire response from cosmos db and it fails as schema defined is looking for an array. Query can return paginated result, so below fields need to be mapped as well .I don't think _rid has any relevance here.

     

     

     

      _count, endCursor, hasNextPage 

     

     

    Another option is  setting schema like below.

     

     

    type Books{
        items: [Book]
        endCursor: String
        hasNextPage: Boolean
    
     }
    
    type Query{
       books: Books
        book(id: ID!): Book 
    }

     

     

     

    Ryan_Langford

  • Ryan_Langford's avatar
    Ryan_Langford
    Copper Contributor

    Sorry, I should have been more specific.
    TLDR: Question is, when a query returns a collection are we expected to map it out of the response ourself?

    Long Version:
    I can make parameterized query requests, and I know they work, because if I change the return type of a query into String I can debug it and see that the request works.
    For example:
    Modified Debugging Schema:

    type Query{
    books(id: Int!): String
    }


    Resolver:

    <query-request>
    <sql-statement>select * from c where c.author_id = @code</sql-statement>
    <parameters>
    <parameter name="@code">@(context.GraphQL.Arguments["id"].ToString())</parameter>
    </parameters>
    </query-request>

    Query:

    query {
    books(id: 1)
    }

    Response:

    {
    "data": {
    "books": "{\"_rid\":\"RpMIAKjTVEQ=\",\"items\":[{\"id\":\"1\",\"title\":\"THe Light Fantastic\",\"genre\":\"Comedy\",\"author_id\":\"1\",\"_rid\":\"RpMIAKjTVEQBAAAAAAAAAA==\",\"_self\":\"dbs\\/RpMIAA==\\/colls\\/RpMIAKjTVEQ=\\/docs\\/RpMIAKjTVEQBAAAAAAAAAA==\\/\",\"_etag\":\"\\\"0400d01b-0000-1a00-0000-649262e90000\\\"\",\"_attachments\":\"attachments\\/\",\"_ts\":1687315177}],\"_count\":1,\"endCursor\":\"\",\"hasNextPage\":\"False\"}"
    }
    }


    (That is the corrects data from the Cosmos DB)

    When I put the query return type back to what I want it to be...
    Query:

    type Query{
    books(id: Int!): [Book]
    }


    Resolver: Same as before
    Query: Same as before
    Result:

    {
    "errors": [
    {
    "message": "Error trying to resolve field 'books'.",
    "locations": [
    {
    "line": 2,
    "column": 2
    }
    ],
    "path": [
    "books"
    ],
    "extensions": {
    "code": "JSON_READER",
    "codes": [
    "JSON_READER"
    ]
    }
    }
    ],
    "data": {
    "books": null
    }
    }

    Am I meant to be using the <set-body> tag to map the "items" array out the response myself?
    Is there a way to make it automatically just return the items array, or am I meant to be caring about the "_rid" value for some reason?

  • Ryan_Langford's avatar
    Ryan_Langford
    Copper Contributor

    Hi Andrei

     

    Loved the post, and have managed to get an equivalent result myself using read requests, however I have run into some trouble when using query-requests.

     

    Would you be able to provide an example of this?

    I cannot seem to find any working examples online, but since it is so new that is not surprising haha.

     

    Regards

    Ryan