JSON functionalities that are added in Sql Server 2016 and Azure SQL Database enable application developers to easily expose relational data from database tables and create Rest Api that will provide data to modern JavaScript single-page applications. In this post you will see how can Json simplify your REST API development.

Single page app architecture – end-to-end

Most of the modern web applications, and especially single-page applications, follow the 3-tier pattern architecture shown on the following figure:

Client-side code may be written using various frameworks/libraries such as Angular, React, JQuery, D3, etc. The code is executed in browsers and communicate with the middle/application tier via some REST API that provides data formatted as JSON or accepts some data formatted as JSON object or HTTP parameter collection. REST API may be written in different languages such as ASP.NET, NodeJS, Azure Functions, etc. Application layer converts JSON requests obtained from the client-side and creates some SQL queries to retrieve or store data in some database.

The main problem in web application development is the data format mismatch between client-side layer that works with some JavaScript/JSON objects, and relational tables that exist in the database layer.

In the modern single-page apps where a lot of the logic is written in the client-side code, most of the code written in the app layer deals with conversion between relational model retrieved from a database and JSON objects that are sent or retrieved from the client-side JavaScript code.

If you want to get the data from SQL tables and provide it to your client-side code, you would need to add a number of sub-layers in the applications layer. You would need some data access component or object relational mapper to execute the query and fetch the data. Then you have some kind of “view model” or “data transfer object” that will be populated with the results of the query. Then the objects would be serialized in JSON format in order to be sent to the client-app, like it is shown on the following picture:

SQL Server JSON support simplifies that process and enables you to easily expose your SQL data via REST API. Using FOR JSON clause, you can instruct SQL Server or Azure SQL Database to return query results in JSON format instead of the classic tabular format. App layer can get the data in JSON format and this JSON response can be directly sent to your single page apps without any conversion. Application layer is still needed in this architecture because it represents a proxy between the client-side code and database that handles authentication, caching, etc.

Examples of .NET and NodeJS REST APIs that expose results of T-SQL queries are shown in the following examples:

ASP.NET

public async Task Report()  {
  await Sql.Stream(
@"SELECT color AS x, AVG(price) AS y
FROM product
GROUP BY color
FOR JSON PATH",
Response.Body, '[]');
}

NodeJS

router.get('/Report', function (req, res) {
    req.sql("select Color, AVG(price) FROM Product GROUP BY color FOR JSON PATH")
       .into(res, '[]');
});

As you can see, exposing results from any query via REST API becomes one-liner.

Persisting JSON models in database

Many modern web applications maintain state on the client side. There are many frameworks such as redux that enable developers to maintain state of the application on the client side. Once the client application needs to save the state, model object is sent to the app layer via some REST service.

Traditionally, in order to save the state provided by client-side app, you would need to parse JSON text sent from the client, create data transfer objects and use some ORM to save data transfer objects into the database.

Even if your framework supports implicit de-serialization of JSON data as domain model, you would still need to create separate classes that represent domain model and some code that persists domain model to the database via ORM or some custom code.

JSON support also simplify persisting JavaScript model objects into database. SQL Server enables you to send this JSON model object directly to database, parse it using built-in native OPENJSON function that parses JSON as tabular data, and insert results directly into the database table. You can either create T-SQL stored procedure that will accept JSON object, parse it and insert it into a table, or you can execute the query from REST API:

[HttpPost()]
public async Task Post() {
       string product = new StreamReader(Request.Body).ReadToEnd();
       var cmd = new SqlCommand(
 @"insert into Product (title, price)
       select title, price from openjson(@product) with (title nvarchar(40), price float)");
       cmd.Parameters.AddWithValue("product", product);
       await Sql.ExecuteNonQuery(cmd);
}

Note that depending on your architecture, you might want to re-locate this core in some data access layer or repository.

Conclusion

Built-in JSON functions in SQL Server 2016 and Azure SQL Database, enable you to simplify your application layer code and work with database using the format that is already used on your client-side apps. With built-in JSON functions you can expose data from your database with a few lines of code, which simplifies you back-end REST API development and enables you to focus on you single-page apps.

You can find many examples of we applications built using SQL Server JSON functions and ASP.NET, NodeJS on the official SQL Server GitHub samples repository.