GraphQL API for SQL Database in .NET

You’ve probably read tons of articles regarding GraphQL and already know all the pros and cons of this technology for APIs as an alternative to REST APIs. However, let's shortly recall what GraphQL is, its main purpose, and how we can use it in real life.

Short Info Regarding GraphQL

GraphQL was released in 2015 by Facebook and is positioned as an alternative to well-known RESTful architectural style. Instead of hundreds web API methods (which can obviously have different versions), you have only one Web API endpoint that allows you to get all information regarding the fields (a type of field, required or not, etc.). Essentially, GraphQL accepts the query — which is sort of JSON-formatted data — and tries to parse it to the previously defined schema.

You may post two types of queries:

• Query — for getting multiple data and only those fields that are defined in a query
• Mutation — uses for creating, updating, or deleting data

Before using GraphQL in the project, we should define the schema that contains all possible entities and entity fields and resolve where we define how and from where we’re getting or updating data.

Overview of Get Data Approaches

Actually, lots of requests from the client page are just Get Data from DB (or another data source like Elasticsearch), so let's briefly examine the methods we can use for getting data and clarifying possible pitfalls for each approach.

The first one — and I'd say the best-known — is using Entity Framework (EF). In my opinion, EF is a bit sophisticated, especially if we are just talking about getting data from DB. Moreover, in case if the database table was previously updated we should not forget to update defined models in our project.

The second approach is based on lightweight ORM NReco.Data which has a wide range DB-adapters (allowing connection to any popular DB — MSSQL, PostgreSQL, MySQL, Elasticsearch, ect.). However, we still have an issue with updating schemes in the case when our table was changed. Let's look at a component that allows us to define all schemas (even relations) in a JSON-formatted file — GraphQL.Net API to SQL-db

Let's configure the schema and run a couple of queries for .NET Core Web Application step-by-step:

1. In the beginning, let's set up a database configuration string and schema definition in Startup.cs

public void ConfigureServices(IServiceCollection services) {
  services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_1);
  services.AddSingleton<IDbFactory, DbFactory>((servicePrv) => {
    // db-provider specific configuration code:
    return new DbFactory(Microsoft.Data.Sqlite.SqliteFactory.Instance) {
      LastInsertIdSelectText = "SELECT last_insert_rowid()"
    };
  });
  services.AddSingleton<IDbCommandBuilder, DbCommandBuilder>((servicePrv) => {
    var dbCmdBuilder = new DbCommandBuilder(servicePrv.GetRequiredService<IDbFactory>());
    dbCmdBuilder.SelectTemplate = "SELECT @columns FROM @table@where[ WHERE {0}]@orderby[ ORDER BY {0}] @recordcount[LIMIT {0}] @recordoffset[OFFSET {0}]";
    // initialize dataviews here:
    return dbCmdBuilder;
  });

  services.AddScoped<IDbConnection>((servicePrv) => {
    var dbFactory = servicePrv.GetRequiredService<IDbFactory>();
    var conn = dbFactory.CreateConnection();
    conn.ConnectionString = String.Format("Filename={0}", Path.Combine(ApplicationPath, "../Data/northwind.db"));
    return conn;
  });

  services.AddScoped<DbDataAdapter>();
  // configure schema via json-file
  services.AddScoped<IGraphqlAdapter>((servicePrv) => {
    var dbAdapter = servicePrv.GetRequiredService<DbDataAdapter>();
    var graphqlAdapter = new GraphqlDbAdapter(
      dbAdapter, 
      JsonConvert.DeserializeObject<GraphqlConfiguration>(
        System.IO.File.ReadAllText(Path.Combine(ApplicationPath, "schemaDbDefinition.json"))
      )
    );

    return graphqlAdapter;
  });
}

2. The second step is defining a "schemaDbDefinition.json" file where we have the next properties:

• Options — it’s a nonobligatory property and might be omitted; contains a filter, pagination, sort, etc.
  • Pagination by default is enabled and is used to define sort arguments (sortBy, direction) in the query
  • Sort, by default, is enabled and is used to define sort arguments (sortBy, direction) in a query
  • Filter, by default, is enabled and is used to define filter arguments ("filter" by default) in the query
• SchemaObjects — an array of defined GraphQL objects based on data tables from DB
  • SingleName — the name of a single object in GraphQL query
  • ListName — the name of a list of objects in GraphQL query
  • Table — database table name
  • Description — optional field; appears in GraphiQL IDE and contains a short description
  • Fields — all fields that will be available in a GraphQL query
  • RelatedObjects — an array of related objects; can be used only by objects that are defined in the schema
    • Relex — string expression (read more info NReco.Relex) where all field values are taken from parent schema objects and can be used in a condition.

Eventually, it should be something like this:

{
  "Options": {
  "Pagination": {
  "ArgumentNameForFirst": "limitItems"
  }
  },
  "SchemaObjects": [
    {
      "SingleName": "customer",
      "ListName": "customers",
      "Table": "Customers",
      "Description": "Customer table - contains data about customer",
      "Fields": [
      {
        "Name": "id",
        "Column": "CustomerID",
        "DataType": "string",
        "Description": "Unique identificator - using also to connect with orders"
      },
      {
        "Name": "CompanyName",
        "DataType": "string"
      },
      {
        "Name": "ContactName",
        "DataType": "string"
      }
      ],
      "RelatedObjects": [
        {
        "Name": "order", // refers to the name of related object
        "Relex": "orders(CustomerID=\"id\":var)[*]" // query used to load related object
        },
        {
        "Name": "orders",
        "Relex": "orders(CustomerID=\"id\":var)[*]"
        }
      ]
    },
    {
      "SingleName": "order",
      "ListName": "orders",
      "Table": "Orders",
      "Fields": [
        {
          "Name": "orderId",
          "Column": "OrderID",
          "DataType": "int32"
        },
        {
          "Name": "customerId",
          "Column": "CustomerID",
          "DataType": "string"
        },
        {
          "Name": "OrderDate",
          "DataType": "datetime"
        },
        {
          "Name": "Freight",
          "DataType": "decimal"
        }
      ]
    }
  ]
}

3. Let's add a GraphQL adapter to the API method’s controller:

public class GraphQLRequest {
  public string OperationName { get; set; }
  public string Query { get; set; }
  public JObject Variables { get; set; }
}

[HttpPost]
public async Task<IActionResult> PostAsync([FromBody]GraphQLRequest queryRequest, CancellationToken cancellationToken) {
  var graphqlResult = await _graphqlAdapter.ExecuteToJsonAsync(
  new GraphQLQuery {
    Query = queryRequest.Query,
    OperationName = queryRequest.OperationName,
    Variables = queryRequest.Variables.ToInputs()
  },
  cancellationToken
);

return Ok(
graphqlResult
);
}

4. Let's get all customers with their orders:

5. Or get a particular customer's data with the last 2 orders:

6. Apply filter and get all orders with Freight more than 870:

Summary

With NReco.GraphQL defining schemas, data fetching can be extremely easy and requires just a few steps. You no longer need to create classes to define GraphQL schemes and rebuild the whole solution after updates — all you need to do is slightly change a JSON-file. Another great thing about NReco.GraphQL is that this DB adapter can be easily connected to any popular database.

Let us know your thoughts in the comments.