Building SQL REST API back-end for Angular apps

  • Article

JSON support in SQL Server/Azure SQL is designed to make integration between the relational database models and the modern single-page JavaScript apps easier than ever. SQL Server/Azure SQL enables you to easily format results of SQL queries as JSON text and return it to your JavaScript apps. In this post you will see how easily you can build back-end REST API for Angular sample app using JSON functionalities in SQL Server.

Angular is JavaScript frameworks designed to help you to build modern single page apps. The easiest way to learn how to use Angular is to follow the official Tour of Heroes tutorial on Angular Docs site. This is an app created with Angular framework that enables user to list and update heroes, as shown in the following figure:

You can also find the source code of this tutorial on Johnpapa’s Angular - Tour of Heroes GitHub repository.

Traditionally, these demo apps don’t use some data storage and use only in-memory JavaScript data source or some static JavaScript files stored on server that simulate read-only REST API responses.

In this post, I will explain how to build real back-end API for this sample app that works with SQL Server database.

Implementing SQL Server/.Net backed

In order to implement REST back-end for Angular app, you need to do the following steps:

  1. Identify REST API calls that are sent from Angular app to the back-end REST API service.
  2. Model database – create database model with some initial data that will be used by REST API.
  3. Implement REST API service in some language – in this example .Net web app.

Identify REST API endpoints

The first step that you need to do is to analyze what REST API calls are sent from Angular app to back-end service. Tour of Heroes app uses the following REST endpoints:

  1. GET: app/heroes – to get the lit of all heroes from the data source.
  2. GET: app/heroes?name=<<name>> - to get list of heroes by name from the data source.
  3. GET: app/heroes/<<id>> – to get the single hero with id <<id>> from the data source.
  4. PUT: app/heroes/<<id>> – to update hero details.
  5. POST: app/heroes – to create a new hero.
  6. DELETE: app/heroes/<<id>> - to delete a hero from the data source.

Model the data store

The second step is to analyze data structures that Angular application needs to show and create database tables that will contain data that will be shown.

In this case, we have a simple model where each hero has only a name. Therefore, we need a simple table that has id and name of the heroes:

 create table Hero (
       id int primary key identity,
       name nvarchar(40)
);

Then we need to populate data using some script like:

 declare @heroes nvarchar(4000) = N'[{ "name": "Mr. Nice" },{ "name": "Narco" },{ "name": "Bombasto" },{ "name": "Celeritas" },{ "name": "Magneta" },{ "name": "RubberMan" },{ "name": "Dynama" },{ "name": "Dr IQ" },{ "name": "Magma" },    { "name": "Tornado" } ]';

insert into Hero(name)
select name
from openjson(@heroes) with (name nvarchar(40));

In this example, I'm using JSON array to populate the table in order to show you how easily you can transform JSON object to a table.

The code that creates and populates the database is placed on SQL Server GitHub repository.

Implement REST API

Finally, we need to create .Net web app that will provide data from SQL Server database when Angular app calls some REST API.

SQL Server provides FOR JSON PATH query clause that returns data formatted as JSON text instead of tabular result. If you need to return all heroes from database using the following query:

 select * from Hero where name like @name

You can easily change the query to return results formatted as JSON:

 select * from Hero where name like @name FOR JSON PATH

This clause will return an array of JSON objects – one object for each row returned by the query. If you know that you are returning only single object, you can add WITHOUT_ARRAY_WRAPPER clause to omit array brackets form the output:

 select * from Hero where id = @id FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

There is also OPENJSON function that parses JSON objects sent by Angular apps and transform them into tabular format so you can directly load JSON into database table. This way you can directly exchange JavaScript/JSON objects between JavaScript app and database with minimal proxy between them.

This is good match for implementing REST API in web apps, because with few lines of code, you can turn your T-SQL queries into REST API endpoints.

Here is an example of the endpoint that returns a single hero:

 // GET app/heroes/5
[HttpGet("{id}")]
public async Task Get(int id)
{
    await SqlPipe.Stream("select * from Hero where id = "+ id +" FOR JSON PATH, WITHOUT_ARRAY_WRAPPER", Response.Body, "{}");
}

You can just put the query in Stream method and the result will be sent to ASP.NET Response.Body object as a result. I’m not using parameters because ASP.NET code will use strong cast of input parameter to int so there is no risk of SQL injection. However, the good practice is to always parametrize your queries like in the following example.

Combined endpoint that returns all heroes or heroes by name is also simple:

 // GET: app/heroes[?name=<<name>>]
[HttpGet]
public async Task Get(string name) {
  if(string.IsNullOrEmpty(name))
    await SqlPipe.Stream("select * from Hero for json path", Response.Body, "[]");
  else {
    var cmd = new SqlCommand(@"select * from Hero where name like @name for json path");
    cmd.Parameters.AddWithValue("name", "%"+name+"%");
    await SqlPipe.Stream(cmd, Response.Body, "[]");
  }
}

As you can see with a few lines of code per REST API endpoint you can expose your data from the database and implement REST API for Angular application.

Get the code

The code used in this post is placed on SQL Server GitHub repository. You can take entire repository or just this sample by using the following PowerShell code:

 git clone -n https://github.com/Microsoft/sql-server-samples .\sql-server-samples
cd sql-server-samples
git config core.sparsecheckout true
echo samples/features/json/angularjs/dotnet-tour-of-heroes/* | Out-File -append -encoding ascii .git/info/sparse-checkout
git checkout

Make sure that you restore nuget/npm packages as described in README file to make sure that application is working.

Conclusion

JSON support in SQL Server enables you to easily format results of your queries as JSON and implement REST API for any JavaScript client code. In this post you could see how easily you can extend standard Angular sample app and create backed in .Net/SQL Server using JSON functions to create working end-to-end example.

As you can see in this example, implementing REST API with SQL Server/Azure SQL Database is easy as using NoSQL databases/collections. Few lines of code per REST API endpoint are enough to take your data from database and return it to client app without C# models or ORMs.

With JSON functions in SQL Server/Azure SQL Database you can combine simplicity of NoSQL with power and strong consistency of relational database.