Generating a fully RESTful Api and UI from a database with LLBLGen and ServiceStack (templates on Github and Demo)

In this post, we’re going to explore building a generic RESTful api on top of a database with custom CRUD and query conventions, using the LLBLGen ORM and code generator, a ServiceStack service layer, and ServiceStack Razor html entity browser layer. This API and html entity browser can be generated on top of any database that LLBLGen supports (see here for supported database drivers).

Intro

See the Demo  Get the Source

In this post, we’re going to explore building a generic RESTful api on top of a database with custom CRUD and query conventions, using the LLBLGen ORM and code generator, a ServiceStack service layer, and ServiceStack Razor html entity browser layer. This API and html entity browser can be generated on top of any database that LLBLGen supports (see here for supported database drivers).

Inevitably, someone will ask, why not just use OData? OData is the route Microsoft is pushing for building RESTful data apis. OData is definitely worth learning and taking a look at, you will most likely use it in the future on some project, if you’re not already using it now. You can learn about OData here (www.odata.org/documentation) if it’s new to you, and browse a sample OData feed of Northwind here: services.odata.org/northwind/northwind.svc/. LLBLGen even supports OData. My personal opinion is, if OData meets your needs, use it! If you want complete control over your API however, and want the ability to plug in business rules, validation, authentication, authorization, generate UI components, impose some level of control over the queries against your database, want to easily be able to refactor your API, create accompanying documentation, and easily create similar APIs with similar logic and conventions over time across projects, you may want to consider the approach described in this post.

(Sidebar: If you’re new to REST and/or interested in a slidedeck on REST, here’s a pretty good one).

Demo

JUST SHOW ME! Screenshots, demo link, and source on GitHub

• Go here for the demo: northwind.mattjcowan.com
• Go here for the source (hosted on GitHub): https://github.com/mattjcowan/LLBLGenPro_SS_Api_Razor_Templates

RESTful Uri and Protocol Conventions

To discuss and demonstrate the usage of the auto-generated APIs, I’ll use the live demo above as illustration (which is also downloadable from GitHub).

For the most part you’ll have to click on the links to see how the various formatted results look. For XML and JSON, in your AJAX apis, use the “accept” header, but you can force the format using the format parameter.

We’ll use the “Category” and “Employee” entities throughout as samples for our queries.

Parameters are always optional.

Discoverable Entities

The entity API allows a user or application to discover the entities available in the system.

The API gives you an “HREF” property back for each entity that you can store and use to navigate to each particular entity

Querying Entities

Once you have obtained your list of entities, you can browse a specific entity type using a “slug” version of the plural form of an entity name, or just follow the “href” property of the discoverable entity API above.

Querying all instances of an entity type:

Querying a specific instance of an entity type using a primary key:

If the entity has multiple primary keys, just add them one after the other according to their index into the Uri.

Querying a specific instance of an entity type using unique constraints:

If the entity has unique constraints, they are discoverable in the field properties using the “categories/meta” convention.

Unique constraints can be composed of multiple fields, just append each value for each field in the constraint to the URL. You can also use the filter API to achieve the same thing.

Creating/Updating/Deleting entities

A note about security. These create/update/delete methods are protected in the generated code with an Authenticate attribute requiring at minimum to be an authenticated user… You can add additional layers of security as needed with ServiceStack filter attributes on your data contracts, methods, and/or classes, or by plugging into the authorization and validation features built into LLBLGen Pro.

Creating an entity:

To create an entity, simply POST your entity to the entity Uri.

A customer object for example would look like this:

{ "address" : "Obere Str. 57",
  "city" : "Berlin",
  "companyName" : "Alfreds Futterkiste",
  "contactName" : "Maria Anders",
  "contactTitle" : "Sales Representative",
  "country" : "Germany",
  "customerCustomerDemos" : [  ],
  "customerId" : "ALFKI",
  "fax" : "030-0076545",
  "orders" : [  ],
  "phone" : "030-0074321",
  "postalCode" : "12209",
  "region" : ""
}

Updating an entity:

To update an entity, simply POST your entity to the entity Uri.

The format of your entity is the same as in creating an entity. If your server cannot process a PUT request, you can use a POST request with a “/update” at the end of the Uri.

Deleting an entity:

To delete an entity, simply POST your entity to the entity Uri.

Exceptions

Exceptions will typically come back formatted as follows (JSON syntax):

{ "responseStatus" : { "errorCode" : "NullReferenceException",
      "errors" : [  ],
      "message" : "Object reference not set to an instance of an object.",
      "stackTrace" : ""
    } }

Advanced Queries

FILTER parameter: filtering entities

The syntax for filtering looks similar to Active Directory queries, but the AND clause is represented by a “^” character, and the OR clause is represented by a “|” character.

A basic clause consists of three parts: {fieldName}:{filterOperator}:[{filterValue1},{filterValue2,…{filterValue(n)}]

Here’s a sample clauses to filter categories according to their id:
/categories?filter=categoryid:eq:1

Some operators accept arrays. This clause filters for all categories with either id = 1, 2, 3, or 4:
/categories?filter=categoryid:eq:1,2,3,4

To specify multiple clauses in an AND structure, you would do that as follows:
/categories?filter=(^(categoryid:eq:1,2,3,4)(categoryname:eq:condiments))

To specify the same clause in an OR structure, you would do that as follows:
/categories?filter=(|(categoryid:eq:1,2,3,4)(categoryname:eq:condiments))

You can now combine the AND and OR structures to create compound clauses. This example fetches (all categories with id = 1, 2, 3, or 4 AND where the category name is “condiments”) OR (where the description contains the word ‘and’)
/categories?filter=(|(^(categoryid:eq:1,2,3,4)(categoryname:eq:condiments))(description:lk:”*and*”))

The generated API supports the following operators (with examples):

SELECT parameter: selecting specific fields

You can ask the api for specific fields instead of the entire object graph, which can considerably improve performance.

Categories have embedded images, so we wouldn’t want to constantly be returning all that data. So, in this example, we will fetch all categories restricting the query to only the category name and the description (the PK of the entity is always returned!).

Sample select query: /categories?select=categoryname,description  [ HTML version | JSON version | XML version ]

INCLUDE parameter: including related items

You can ask the api to return related objects upfront, which improves performance by preventing you from having to come back and get the objects iteratively.

In this example, we’ll return the “Reports To” and “Employee Territory” fields as a single GET for the employee with ID = 1. As you can see also in the two results below, querying using a filter parameter gives you a response as part of an array, whereas, querying the specific entity gives you the entity directly (not within an array).

/employees?filter=employeeid:eq:1&include=reportsto,employeeterritories [ HTML version | JSON version | XML version ]
OR
/employees/1?include=reportsto,employeeterritories [ META version | JSON version | XML version ]

RELATION parameter: querying on related items

Support for querying on related items is still in the works. LLBLGen provides not only a deep-fetch api, but also a very flexible API to construct almost any SQL you want, including creating filters on items N deep by traversing relationships in the database. The creators of LLBLGen have spent considerable time figuring out how to optimize the SQL for performance, it’s trustworthy. This feature is one of the big reasons I prefer LLBLGen over the Entity Framework. I’ll try to add this as a feature to this generated API in the near future.

PAGING parameters: paging data

Unless otherwise specified, by default, paging is set to 10 items at a time. In this example we’ll query for the 2nd page of customer data using a page size of 5. The HTML interface uses it’s own paging mechanism, so this example showcases paging using the JSON and XML formats.

/customers?pageSize=5&pageNumber=2 [ JSON version | XML version ]

One thing you’ll notice is the “Paging” object that is returned as part of the response. The paging object has everything that’s needed to construct good paging capabilities on the client-side.

{ ...,
  "paging" : { "firstItemOnPage" : 6,
    "hasNextPage" : true,
    "hasPreviousPage" : true,
    "isFirstPage" : false,
    "isLastPage" : false,
    "lastItemOnPage" : 10,
    "pageCount" : 19,
    "pageNumber" : 2,
    "pageSize" : 5,
    "totalCount" : 91
  }
}

SELECT parameter: selecting specific fields

You can easily sort your data, and sort on multiple fields as well.

In this example, we will sort customer data by country in ascending order, city in ascending order, then company name in descending order. If you don’t specify a sort operator for a field, it’s assumed to be ‘ascending’.

/customers?sort=country,city:asc,companyname:desc [ JSON version | XML version ]

Using C# and the ServiceStack Client to call the Query service(s)

Check some of the basic unit tests for samples, it’s ServiceStack out-of-the-box: ConnectivityTests.cs, BasicQueryTests.cs

Using javascript / jQuery to call the Query service(s)

There is nothing fancy going on here. Simply use the standard $.getJSON() or *.ajax() jQuery apis, they work out of the box.

Generating the Api

You can follow along with this tutorial simply by downloading the LLBLGen templates from GitHub, and reproducing the steps I’m going over in this section.

nuget update -self
mkdir packages
nuget install .\ServiceGeneric\packages.config -OutputDirectory .\packages
nuget install .\ServiceSpecific\packages.config -OutputDirectory .\packages
nuget install .\Hosts\ConsoleHost\packages.config -OutputDirectory .\packages

Your API and website is LIVE!