valhuber avatar image
valhuber posted

How to Build REST Infrastructure for Database Access

In the first two blogs in this series, we tackled the issue of how REST is a solution to a connected enterprise followed by discussions on What is REST. Then we discussed why use REST for database access.

In this blog, we will discuss

  • How to build the REST infrastructure for database access
  • What are the requirements you should expect from your vendors of REST infrastructure

Building REST infrastructure for database access

As discussed in previous blogs, REST is not a standard, but an architectural style. As a result, there are countless ways to build out a REST infrastructure ? some efficient, some not.

building REST infrastructure for database access

Nonetheless, given the operational requirements, the API must provision a common set of services:

  • Basic REST services ? client server communications, to accept and return JSON messages, converting messages to and from JSON, resource naming, routing services
  • Message handling services to take the REST verbs ? GET, PUT, POST and DELETE and translate them into the appropriate method calls
  • Data services that handle the definition of REST resources from database resources as well as the instantiation and persistence of data
  • Security services ? no system is complete without security. You can pass along the database credentials, but they are coarse grained. Table-level access is often way too broad. You need fine grain access control
  • Logic services to handle database updates
  • Performance optimization must be part of the API. These systems must scale and keep up with the usage in an elastic manner. Since mobile is bandwidth constrained, database calls and network traffic must also be minimized

Requirements you should demand from your REST infrastructure vendors

It is possible to manually REST-enable your own databases. However, only the organizations with lot of engineering resources are able to do this effectively. Building the REST-enabled infrastructure is a huge and complex undertaking.

A better approach is to identify your primary data sources ? in most organizations SQL is the most widely used, by far ? and then identify technologies designed to help. Many offerings, ranging from open source utilities to full-featured products, provide varying capabilities. As you evaluate the options there are many elements to consider. Certainly, security and infrastructure are critical elements. The connected enterprise has to be able to react to market opportunities and competition very fast, so the speed of development and maintenance are also critical factor. These need to be considered independently. The ability to change application quickly is as important as the ability to build them in the first place.

You should demand the following capabilities from your vendors:

A. Implementation ? Cloud or On-premise

cloud or on-premise REST services i

There are three possible scenarios for deployment:

  • REST service and database both in the cloud ? this is the ideal situation, because all the infrastructure is managed for you and it provides the scalability needed. It may not be feasible in your case due to security considerations.
  • REST service in the cloud and database on premise ? in order for the REST service to reach your internal database securely, reverse SSH tunneling may be` required. There is free open source technology, which is quick and easy to implement.
  • REST service and database are both on premise ? in which case the REST service is provided as a software appliance. For very secure environments such as in finance and healthcare, this may be a requirement. It does add management overhead for your IT team compared to a SaaS service ? you provide your own backups, load balancing and scaling services.

B. API Requirements

1.  Connect for services

You should be able to connect, introspect the database schema and produce a default database API with REST listeners for each database object including:

  • Tables
  • Views
  • Stored procedures. These enable you to quickly leverage your legacy business logic. In particular, the API must support multiple input and output parameters

2.  Support for all REST functions

  • The API should provide full REST support including GET/POST/PUT/DELETE as well as metadata services.

3.  Enterprise-class API features

The API should provide support for the following by default rather than having to program each of these features

  • Pagination
  • Filtering
  • Ordering
  • Custom parameters to pass to the backend processing logic
  • Optimistic locking
  • SQL handling

Certainly, no system gets out the door without testing. Often developers have to build their own test harnesses. The system should include  mechanism to test the API behavior, using both JSON and an updateable grid view of the data.

4.  Custom REST endpoints

An API for base table is fine for some applications. More often, you need to create a hierarchical REST endpoint that is composed of data that spans multiple tables, and where the data is returned to the calling applications as a compound, document oriented, JSON structure. Typical examples include mobile applications or B2B applications where these resource endpoints provide a mapping layer across multiple input and output JSON data streams.

The ideal REST infrastructure for databases, provides the capability to create these resources ?on the fly? by merely pointing and clicking. The system should have the ability to create resources in the exact shape needed by client applications:

  • Aliasing column names to more friendly names
  • Projection ? selecting only those fields you need in the resource
  • Allowing you to get data for the new custom endpoints from other external databases or systems

C. Security

Building an API fast is great, but if the API does not enforce security, it is a liability. Table level security is rarely sufficient and creating views is tedious and time consuming. A common approach is to assign users to one or more roles, and then to grant security to those roles. The system should be able to define access control to those roles at several levels.

  • Endpoint access control defines what roles are able to see which resources. This can be refined further by describing which roles have authority to read, update, insert and delete
  • Row and column level access control is typically handled within an application and is a significant amount of the application code. The service should provide a user interface that enables staff to define the row/column security, by role, and via a simple online interface.

An aspect of security often overlooked is the requirement to audit all access to sensitive data ? both read and write. The system should provide facilities for selected information to easily create logs describing who accessed, what data and when.

D. Update Logic

Business logic kicks in when you update data with PUT, POST or DELETE operations. The logic is the derivations, if then else, and constraint processing and more that enforces business policy. The most widely used language on the client is now the fastest growing server-side language ? JavaScript.

The service should provide a full JavaScript object model, automatically, based on the schema and the additional resource endpoints defined. The object model should support current.row vs. old.row functionality, akin to the Active Record model found in frameworks such as .Net and Ruby.

Reactive Programming Rules to accelerate Logic development

JavaScript is nice, but it would be so much better if there were easier ways to express business logic. If business and technical users understood the logic, they could come a common understanding of the requirements.

The technology is available and it is reactive programming. The most widely known implementation is the spreadsheet. Espresso?s reactive programming accelerates database development, while creating better applications. These applications are easier to maintain as business requirements change.

E. Built-in Testing and Debugging

  • Certainly, no system gets out the door without testing. Often developers have to build their own test harnesses. The system should include mechanism to test the API behavior, using both JSON and an updateable grid view of the data.
  • The system should also include interactive logging facilities to allow for comprehensive execution logging of JavaScript logic (as well as reactive logic).
  • An interactive debugger must be provided to allow developers to set break points and walk through the server processing.

F. Integrating REST APIs with other services

The REST API is the connectivity platform. It must easily the server to invoke other web services on other platforms.

10 |600

Up to 8 attachments (including images) can be used with a maximum of 1.0 MiB each and 10.0 MiB total.



valhuber contributed to this article