Creating a REST Server for a CRUD Web Application from a very Light Stack

• Download source - 1 MB

Introduction

This article is for anyone starting to develop browser-based single page apps to manage simple objects in a database, but who has not yet built a server to implement the REST service. There is a lot to learn in creating a proper REST service, which can be frustrating if what you really want to do is quickly get on to experimenting with with JQuery or AngularJS. So I’ve developed a REST server from the smallest code stack that I could find so you don’t have to experience what I did. You can skim this article, download the sample code and get on with hacking the Javascript, or follow along as I introduce each component gently.

Background

When I started experimenting with AngularJS I did not have a ready-made server that returned JSON data for my web applications to consume. So I had to find a suitable server technology and research the conventions and code to implement a REST server that returned JSON. When the development was completed, figuring out how to deploy the server on another machine machine took more time. What I really wanted to do was get on with writing the Javascript and continuing to investigate AngularJS.

The LightREST server, included in the download code and constructed in the article, was the software I was looking for then, but never found.

The Sample Service.

The sample service I am developing allows a social website to manage a set of different types of badges or awards for its members. Inspiration for the badge idea is take from stackoverflow. A badge is given a title, a description of what to do to earn it and a level corresponding High/Medium/Low prestige. The service will enable you to add, modify or delete badges as well as fetch a single badge from its Id. It’s a contrived example and you can replace the badge object with any small objects in your system that you simply need to keep a persistent list of.

The Stack

Each component below was selected because it does not depend on anything else, he only pre-requisite being .NET4. This means fewer snags and unpleasant surprises and I hope means that you can deploy your service from your development box to an Intranet server without all the usual trouble.

• REST server: Nancy
• Database: SQLite
• Micro-ORM: PetaPoco

You may consider that there are better alternatives from Microsoft: WebAPI, Sql Express and Entity Framework. I wouldn’t argue, but I’ve tried all these and they are not so straightforward to program, install, configure and develop in if you are not used to them. I'm assuming that my intended audience is more interested in the browser code than the server code.

We’ll start by creating the lightest REST web server imaginable in Nancy, then use SQLite to create a database file to store the badge data, add PetaPoco to read the database and finally combine all these elements together for the completed badge server

A Light Server

Nancy (http://nancyfx.org) is ideal for the purpose. It is designed to be lightweight, but does a very neat job of providing the basic plumbing for a server of HTML or JSON. It will run self-hosted or under IIS. As the goal of the exercise is absolute minimum dependencies we will create a self-host server.

In VS 2010 or 2012 create a console application called RestServer. Download and add references to Nancy.dll and Nancy.Hosting.Self.DLL to the project. The easiest way is via Nuget, but if you do not have this installed then download the Nancy source code from GitHub (https://github.com/NancyFx/Nancy), extract and build the solution and add references to the two DLLs from the build folder of the Nancy.Hosting.Self project. Alternatively download the code that accompanies this article and find the DLLs there.

Implement the basic server in RestServer by adding code to bootstrap Nancy and start up the server in a console window. Note: the article and code use a port number of 8088, but the choice of port number is your own.

class Program
{
    const string DOMAIN = "http://localhost:8088";
    static void Main(string[] args)
    {
        // create a new self-host server
        var nancyHost = new Nancy.Hosting.Self.NancyHost(new Uri(DOMAIN));
        // start
        nancyHost.Start();
        Console.WriteLine("REST service listening on " + DOMAIN);
        // stop with an <Enter> key press
        Console.ReadLine();
        nancyHost.Stop();
    }
}
public class Bootstrapper : Nancy.DefaultNancyBootstrapper
{
    protected virtual Nancy.Bootstrapper.NancyInternalConfiguration InternalConfiguration
    {
        get
        {
            return Nancy.Bootstrapper.NancyInternalConfiguration.Default;
        }
    }
}

The code is very simple to read. If you want to dig deeper into how it works then seek out the documentation on the Nancy web site. For now I’ll assume you want to press on. Build and run. You should see this message:

The Nancy self host was unable to start, as no namespace reservation existed for the provided url(s).
Please either enable CreateNamespaceReservations on the HostConfiguration provided to the
NancyHost, or create the reservations manually with the (elevated) command(s):

netsh http add urlacl url=http://+:8088/ user=Everyone

For the curious refer to http://msdn.microsoft.com/en-us/library/ms733768.aspx for more information. For the impatient open a command window and paste in the line from above commencing netsh http add urlacl … When you restart the server you should see a command window confirming the server is running and echoing the base URL. Try pasting the base URL into a browser. It should display a 404 error. We need to enhance the server to return content in response to this and other URL requests.

Add two class files to implement classes that will return the content. One will return barebones HTML and is designed so that you can “ping” your service from a browser. The other is the stub for a proper REST Uri.

Add a class file HomeModule.cs that contains the following code:

using System;
namespace RestServer
{
    public class IndexModule : Nancy.NancyModule
    {
        public IndexModule() 
        {
            Get["/"] = parameter => { return IndexPage; };
        }

        const String IndexPage = @"
            <html><body>
            <h1>Yep. The server is running</h1>
            </body></html>
            ";
    }
}

The Nancy server detects all classes inherited from Nancy.NancyModule and adds all the routes specified in the constructor to its route resolver. This class defines the response for GET / so it will return the hard-coded HTML when you type in the base Url.

Add a class BadgeModule.cs with the following code:

namespace RestServer
{
    public class BadgeModule : Nancy.NancyModule
    {
        public BadgeModule() : base("/Badges")
        {
            // http://localhost:8088/Badges/99
            Get["/{id}"] = parameter => { return GetById(parameter.id); };
        }

        private object GetById(int id)
        {
            // fake a return
            return new {Id = id, Title="Site Admin", Level=2};
        }
    }
}

This module represents the Badges resource and at present only implements a pretty standard URL for returning a resource given an Id. This API route is defined in the constructor on the line starting with GET[“/{id}]… Note how the {id} will be extracted from the URL and added as a property to the object represented by parameter passed into the anonymous function that calls an implementation method. The implementation is a stub that returns the same details whatever Id is submitted, but we’ll fix that later. There is more Nancy magic as no JSON serialisation is necessary in the module code. Nancy infers what the format of the response content should be from the request headers and does the object serialisation for you.

With these two additions the server is ready to build and run. Now you should get HTML content when you enter the http://localhost:8088/ into a browser. However you may still get an error when you try http://localhost:8088/Badges/1. On IE 10 you get a 500 error, the gist of which is that if Nancy is going to serve raw data it requires an Accept header to indicate which format it should return the data in. Sure enough if you switch from IE to Fiddler (http://fiddler2.com) and add the header:

Accept: application/JSON

to the request, the stubbed data is returned as JSON. If you don’t want to trouble yourself with Fiddler then there is a simple console project, RestTester, in the download that you can use instead. Below is a transcript from using the RestTester program to send requests to the server.

K:\>resttester GET http://localhost:8088
OK
<html><body>
<h1>Yep. The server is running</h1>
</body></html>

K:\>resttester GET http://localhost:8088/Badges/1
OK
{"Id":1,"Title":"Site Admin","Level":2}

K:\>resttester GET http://localhost:8088/Badges/2
OK
{"Id":2,"Title":"Site Admin","Level":2}

K:\>resttester GET http://localhost:8088/Badges/3
OK
{"Id":3,"Title":"Site Admin","Level":2}

K:\>resttester DELETE http://localhost:8088/Badges/1
The remote server returned an error: (405) Method Not Allowed.
for: http://localhost:8088/Badges/1

The same data is returned whatever Id is in the URL and an error for the unimplemented delete method. This all looks promising so we’ll leave the server for now and examine the data-storage side.

A Light Data Storage Engine 

You may already be familiar with SQL Server and wish to use that as the storage engine. If so then skip this section and proceed to the ORM section, where there will be a description of how to alter the code to use SQL Server or Sql Express. Otherwise we’ll use the widely known standalone database engine SQLite. Before introducing it to the Nancy server it is well worth getting familiar with it in isolation so we’ll create a separate project, get that working and then add in the PetaPoco ORM.

In VS 2010 or 2012 create a console application called SqliteBootstrap (or open it from the source). Download and add a reference to the SQLite ADO.NET data provider from the SQLite download site or search for "SQLite ado.net 4.0 provider download" if the link has changed. Be careful which download to select as a lot of them are raw C++ and others require that you have the VC++ runtime libraries installed. Search instead for the static builds; for example look for a section headed "Precompiled Statically-Linked Binaries for 64-bit Windows (.NET Framework 4.0)" and download from there. The DLL to add as a reference is System.Data.SQLite.DLL. Alternatively just take the version from the sample code download.

Now add code the Program.cs source file. The main module sets the database file to badges.db in the My Documents folder and invokes a method to create the file.

static void Main(string[] args)
{
    String folder = Environment.GetFolderPath(System.Environment.SpecialFolder.MyDocuments);
    String fileName = Path.Combine(folder, "badges.db");
    SeedDatabase(fileName);
}

SeedDatabase() looks like this:

private static void SeedDatabase(string fileName)
{
    String dbConnection = String.Format("Data Source={0}", fileName);
    String sql = @"
create table [Badges] (
[Id] INTEGER PRIMARY KEY ASC,
[Title] varchar(20) ,
[Description] varchar(255),
[Level] int)";
    ExecuteNonQuery(dbConnection, sql);

    sql = @"insert into Badges ([Title], [Description], [Level]) 
             values ('Site MVP', 'Awarded to members who contribute often and wisely', 2);";
    ExecuteNonQuery(dbConnection, sql);
}

The method creates the database connection string then creates a table (additionally creating the database file if it is not already present) and inserts a single sample record into the table. Be careful with the primary key field definition as it helps when we add the ORM layer. The ExecuteNonQuery method is a helper method extracted from a general purpose helper class (SQLiteDatabase in SqliteHelper.cs), which is included in the download.

public static int ExecuteNonQuery(string dbConnection, string sql)
{
    SQLiteConnection cnn = new SQLiteConnection(dbConnection);
    try
    {
        cnn.Open();
        SQLiteCommand mycommand = new SQLiteCommand(cnn);
        mycommand.CommandText = sql;
        int rowsUpdated = mycommand.ExecuteNonQuery();
        return rowsUpdated;
    }
    catch (Exception fail)
    {
        Console.WriteLine(fail.Message);
        return 0;
    }
    finally
    {
        cnn.Close();
    }
}

Build and run this once to create the database file Badges.db in you documents folder. Feel free to adjust the location and name of the file to suit your purposes.

A Light ORM

You could use the SQLiteDatabase class directly to access the database, but a much neater solution is to use an Object-Relational-Mapper (ORM) to manage the data conversion from object to database. The ORM reduces your coding effort and will abstract the database layer so you can switch database engines later. This will be illustrated below when describing we describe how to switch from SQLlite to SQLExpress. I have selected PetaPoco because it provides pretty decent basic ORM capabilities in a single source file. This is great for this project because a. there is no additional component to worry about installing or deploying and b. it is very easy to trace into to figure out what is going on.

Get PetaPoco from GitHub (https://github.com/toptensoftware/PetaPoco). Search for Petapoco.cs in the downloaded files and transfer it to the SqliteBootstrap project. You will need to add a reference to System.configuration to the project as PetaPoco has a method that reads the connection string from App.config (though this is not used here).

An ORM maps data from database tables to model objects in the code. Below is the model object class for the Badges table.

[PetaPoco.TableName("Badges")]
[PetaPoco.PrimaryKey("Id")]
 public class Badge
 {
       public Int64 Id {get; set;}
       public String Title {get; set;}
       public String Description {get; set;}
       public Int32? Level {get; set;}
 }

PetaPoco will automatically map database columns to object properties as long as you follow the conventions (see the manual page). The Badge object slightly breaks convention in naming the table with a plural and so there is an attribute to override the convention that all object names have a direct correspondence to the database table and field names. Thought we needn’t have defined the primary key with an attribute, I think it is safer to do so. Remember to use nullable types for date and number fields that can be null in your database table, as shown with the Level property above. Add this code to the project either in the Program.cs module or a separate class file.

Now we can add a method to query the database. The nice thing about PetaPoco is that it leaves the SQL to you and you can trace directly into its source code to see exactly what is going on.

private static void QueryDatabaseOrm(string fileName)
{
    // create a database "context" object
    String connectionString =  String.Format("Data Source={0}", fileName);
    DbProviderFactory sqlFactory = new System.Data.SQLite.SQLiteFactory();
    PetaPoco.Database db = new PetaPoco.Database(connectionString, sqlFactory);
    
// load an array of POCO for Badges
    String sql = "select * from Badges";
    foreach (Badge rec in db.Query<badge>(sql))
    {
        Console.WriteLine("{0} {1} {2}", rec.Id, rec.Title, rec.Description);
    }
}
</badge>

And alter Main() to call it

static void Main(string[] args)
{
    String folder = Environment.GetFolderPath(System.Environment.SpecialFolder.MyDocuments);
    String fileName = Path.Combine(folder, "badges.db");
    if (!File.Exists(fileName))
       SeedDatabase(fileName);
    QueryDatabaseOrm(fileName);
}

As the seeding method added a sample row to the database running SqliteBootstrap now should display a single line with data from that row.

Swapping SQLite for Sql Server or Sql Express

If you have access to and already are familiar with Sql Server/Sql Express I’d recommend using it in preference to SQLite. f you create the table using SQL with the following script the model class above will be suitable.

create table [Badges] (
[Id] int identity(1,1) NOT NULL ,
[Title] varchar(20) NOT NULL ,
[Description] varchar(255),
[Level] int NOT NULL ,
CONSTRAINT [PK_Badges] PRIMARY KEY CLUSTERED ([Id]) 
)

In the code QueryDatabaseOrm() method change the way the PetaPoco.Database object is constructed slightly:

// Replacing SQLite with SQL Server
// String connectionString =  String.Format("Data Source={0}", fileName);
// DbProviderFactory sqlFactory = new System.Data.SQLite.SQLiteFactory();
// PetaPoco.Database db = new PetaPoco.Database(connectionString, sqlFactory);

// define a standard SQL Server ADO connection string
String conStr = @"data source=MyServer;initial catalog=MyDb;Trusted_Connection=yes;";
// Add the SQL Server type to the constructor. PetaPoco will find the provider factory.
PetaPoco.Database db = new PetaPoco.Database(conStr, "System.Data.SqlClient");

That’s all. Upgrade complete! If you wish to place the connection string in an App.config file all you need to do is select an alternative constructor for the PetaPoco.Database.

Putting it together

Having introduced the individual elements of the stack it’s time to bring them together to make the LightREST Badge service. Copy the RestServer project to a new folder and rename it LightREST. Add references to Nancy.DLL, Nancy.Hosting.Self.DLL and System.Data.SQLite.DLL and add the Petapoco.cs file. Files Program.cs and HomeModule.cs need no changes, but BadgeModule.cs needs developing to support the full CRUD REST API for the Badges object; ie. GET to fetch, POST to add, PUT to update and DELETE. Extend the constructor with the following URL routes, forwarding to methods that actually implement them.

public BadgeModule() : base("/Badges")
{
    Get["/{id}"] = parameter => { return GetById(parameter.id); };

    Post["/"] = parameter => { return this.AddBadge(); };

    Put["/{id}"] = parameter => { return this.UpdateBadge(parameter.id); };

    Delete["/{id}"] = parameter => { return this.DeleteBadge(parameter.id); };
}

Each implementation method will check parameters, transfer data to and from a database layer and return a response with the appropriate status code and data. The POST implementation below will act as an example of the coding required.

// POST /Badges
Nancy.Response AddBadge()
{
    Badge badge = null;
    try
    {
        // bind the request body to the object via a Nancy module.
        badge = this.Bind<Badge>();

        // check exists. Return 409 if it does
        if (badge.Id > 0)
        {
            string errorMEssage = String.Format("Use PUT to update an existing Badge with Id = {0}", badge.Id);
            return ErrorBuilder.ErrorResponse(this.Request.Url.ToString(), "POST", HttpStatusCode.Conflict, errorMEssage);
        }

        BadgeContext ctx = new BadgeContext();
        ctx.Add(badge);

        // 201 - created
        Nancy.Response response = new Nancy.Responses.JsonResponse<Badge>(badge, new DefaultJsonSerializer());
        response.StatusCode = HttpStatusCode.Created;
        // uri
        string uri = this.Request.Url.SiteBase + this.Request.Path + "/" + badge.Id.ToString();
        response.Headers["Location"] = uri;

        return response;
    }
    catch (Exception e)
    {
        String operation = String.Format("BadgesModule.AddBadge({0})", (badge == null) ? "No Model Data" : badge.Title);
        return HandleException(e, operation);
    }
}

Points to note in this code are:

1. Use of the Bind method to automatically populate an object from the JSON POSTed by the request. This is more Nancy magic.
2. Strict enforcement of the REST rule that POST means add. It means that if the posted data contains a non-zero Id then the caller might be trying to update, which should instead have used a PUT verb. The method returns a recommended HTTP status code to indicate that the operation did not complete.
3. The database layer is accessed through the BadgeContext class. The term Context appears in other ORM framework code. BadgeContext is a thin wrapper around PetaPoco.
4. If the Badge is added then the status return is set to 201 (Created) rather than the usual 200 success code).
5. In addition to the success code also returned is the resource created, including its newly assigned Id and the URL that can be used to access it via this service. This is done when the JSON response is created with the Badge object.
6. All exceptions are caught and return a 500 (internal server) error to the caller, but should log as much detail about the error as necessary onto the server (decent error logging is extremely important, but is beyond the scope of this article).

The final step is to implement the BadgeContext class. This is a thin wrapper around the PetaPoco ORM, but is kept separate to make it easy to swap to an alternative ORM (Entity Framework or NHibernate).

public class BadgeContext
{
    public Badge GetById(int id)
    {
        String sql = "select * from Badges where Id =" + id.ToString();
        return BadgeContext.GetDatabase().FirstOrDefault<Badge>(sql);
    }
    public void Add(Badge badge)
    {
        BadgeContext.GetDatabase().Insert(badge);
    }
    internal void update(Badge badge)
    {
        BadgeContext.GetDatabase().Update(badge);
    }
    internal void delete(Badge badge)
    {
        BadgeContext.GetDatabase().Delete(badge);
    }
    private static PetaPoco.Database GetDatabase()
    {
        // A sqlite database is just a file.
        String fileName = Path.Combine(Environment.GetFolderPath(System.Environment.SpecialFolder.MyDocuments), "badges.db");
        String connectionString = "Data Source=" + fileName;
        DbProviderFactory sqlFactory = new System.Data.SQLite.SQLiteFactory();
        PetaPoco.Database db = new PetaPoco.Database(connectionString, sqlFactory);
        return db;
    }
}

Remember to use the SQL Server provider and connection string if you have gone down that route. Database errors will be caught by the routines using the BadgeContext class.

That’s all the code there is.

Testing the service.

Before launching into your JQuery or AngularJS development I’d recommend learning to use Fiddler for testing. Alternatively if you are in a hurry use the RestTester application in the download. Both can be used to make POST and PUT requests so long as the format of the data you POST or PUT is a correct JSON representation of a Badge object. [ Top tip. The easiest way to see what the correct format is to get an existing record (remember the SQLite database is seeded with a single record) then edit the returned data ]. Remove the Id property from the JSON and either save it to a file (when using RestTester) or paste the string into the Fiddler content window. Below is a transcript of a RestTester console session to demonstrate what I mean.

[a] K:\>resttester GET http://localhost:8088/Badges/1 >json.txt

[b] K:\>notepad json.txt
[make edits to json.txt and save]

[c] K:\>resttester POST http://localhost:8088/Badges json.txt
Created
{"Id":20,"Title":"Watcher","Description":"Todo: add a description","Level":3}
[make another edit to json.txt and save]

[d] K:\>resttester PUT http://localhost:8088/Badges/20 json.txt
No Content

[e] K:\>resttester GET http://localhost:8088/Badges/20
OK
{"Id":20,"Title":"Watcher","Description":"You have visited many times, but never posted","Level":3}

[a] Request Badge with Id = 1 and pipe the data into a text file
[b] Edit the text file to make a new Badge.
[c] POST the data to the server (no Id in the URL)
[d] Edit the data file to replace the “Todo” with a proper description. Note the id on the new Badge and add it to the URL when putting the changes
[e] Get the new Badge to very that the change was saved on the server.

Getting it off your machine.

As I’m not a build and deployment engineer by profession, the LightREST server is designed for XCOPY deployment; ie no msi files, no registry keys, no required system files, no services configured just so that must be running, etc. Just four assemblies from the LightREST build folder and (optionally) the SqliteBootStrap assembly to create the database; plus the RestTester application. The download project includes a folder named Deployment into which the assemblies are copied when you build in release mode. The folder also includes detailed steps for what to do when you have copied these files onto another machine. It should give you a frictionless experience if you need to set up a server to demonstrate your super AngularJS single page web application.

Conclusion

It was a long haul, but by now you have discovered:

• You do not need the might of ASP.NET, WCF etc. to build a basic REST server designed to interact with Javascript AJAX libraries.
• You can use a true standalone database engine which requires no special installation or configuration when deployed to another machine.
• A single-file micro-ORM is an awesome concept, abstracting so much of the tedium of persisting simple C# objects to a database, using code that can be traced into and followed reasonably easily.

The project is not suitable to be used for a production system. As I stated in the introduction it was designed to give you a fast path to implementing a REST server to provide data for a CRUD web application run in the browser using Javascript. Once you have your proof of concept application working on this stack it should be quite straightforward to substitute each element for a more industrial strength component. It is left to the reader to add features such as: security, logging and API versioning.