article

Restify your SQL Database

Your schema can be used as a model to create a RESTful service, in minutes.  And you can decorate it to declare Multi-Table Endpoints, Row/Column Security, and Business Logic with Reactive Programming rules and JavaScript.  A fully functional API, ready for mobile apps, application integration.  Here's how.

Abstract

REST has become the accepted approach for providing network-based API access for mobile and web apps, cloud-based partnerships, and application integration.  But the Reference Implementation - assemble REST, JSON and SQL frameworks - is slow and complex.  And that?s just the start - the API needs to enforce logic and security, requiring significant additional time and cost, and design expertise.

A prudent blend of schema discovery, declarative specifications, and JavaScript can help:

• Schema Discovery can be used to create default APIs for base/view tables, including Stored Procedures to leverage this important legacy investment
• Declarative Interfaces can specify custom, document-oriented APIs, including critical security to the row and column level
• JavaScript, based on an automatically created Object Model, can provide the power to tackle arbitrarily complex problems

This article will provide details on this approach, including a real-world example in which a partner order is processed as a RESTful POST, regulatory reporting is provided, and a partner-specific account summary response is returned.

Why RESTify your SQL database

REST has become the accepted approach for providing network-based API access, addressing content from weather to social to documents.  This has lead up to a rather remarkable surge in published REST APIs over prior technologies like SOAP:

But what about database access?  JDBC/OBDC has been the standard database access protocol for quite some time.  But these are neither network accessible, nor language neutral.  These are serious issues in the age of mobile devices and cloud-based partnerships.

REST/JSON is well-suited to efficient, network-oriented, language neutral database access:

• Web Services: REST is a great way to provide a common data access layer that can be accessed from any language
• Network enabled (unlike jdbc/odbc)
• Low latency: reduces network calls with Multi Row-Type Results
• Tool friendly: REST provides for discovery, which can enable generic software tools to process results - display them, and navigate the network of related resources (e.g., the Product in an Order)
• Simple: unlike earlier protocols such as SOAP, REST/JSON is simple.  REST is based on familiar HTML GET, POST, PUT and DELETE, and JSON is a simple protocol as shown below

Example of a REST API

Imagine we?ve somehow built a RESTful server called http://myRESTServer, which contains a Resource Endpoint called orderResourceEndPoint.  That means one can issue a GET on http://myRESTServer/v1/orderResourceEndPoint/1, where

• v1 is a version for the API (we?ll take versioning as a given, and won?t pay much attention to it in this article)
• /1 is the primary key of the order.  Other URL arguments provide for more general filtering, sorting and so forth.

The GET returns this JSON response as shown in the snippet below:

How REST is used

Mobile Apps: apps require efficient network access.  And, JavaScript is particularly friendly - JSON results are JavaScript objects and so require no translation layer.

Application Integration: modern approaches to integration utilize real-time information access and sharing instead of ETL (extract, transform and load).  REST provides an excellent vehicle, particularly since JSON naturally supports Multi Row-Type Results.  

B2B: business to business exchanges of data and transactions are well addressed by REST, provided a mapping layer to translate between sender/receiver names.

Common Data Access Logic: over time, it is common for different technologies (.NET, J2EE, Ruby, etc) to be used for database access, and that each embed the business logic in buttons.  Replicating logic between applications is a well-known anti-pattern (?fat client? is possible in any technology), but terribly common.  REST provides an excellent opportunity to centralize and share the business logic, enforced by the language-neutral API.

Access from Office Applications: REST has become so popular that office applications like Excel and Google Spreadsheet provide REST access to corporate data.  This reminds us that security is a critical requirement for the REST API.

 

Building REST Server - A Conventional Approach

Every platform has its frameworks for building the REST server.  Let?s look at this depiction of the Java approach (often called the ?Reference Implementation?):

It is not trivial to build a REST server.  In this reference implementation, there is significant infrastructure code to deal with REST and SQL:

• Jersey provides listeners for REST requests, invoking methods with arguments supplied
• You code the database access - connections and SQL, perhaps using an ORM such as JPA
• Use Jackson for parsing JSON requests and building responses

In addition to infrastructure code, one must supply business logic code for security, performance optimizations (such as pagination or optimistic locking), and integrity.  Taken together, these require significant time and expertise.

Alternative: a RESTFul service

This paper describes how a RESTful service in the cloud (no install hassles) can provide significant reduction in time and complexity.

Referring to the diagram above, the service saves us lot of time by pre-building the patterns for RESTful SQL access:

a. Connect - just by providing database credentials (whether in the cloud of behind your firewall), you get a full RESTful API to your Base Tables, View Tables and Stored Procedures

b. Declare - define custom, multi-table Resource Endpoints, and add security to control acces to endpoints, including row and column level security based on a users' role

c. Business Logic - server side JavaScript Events and Expressions provide full control, building on an Object Model automatically created on Schema Discovery

Let?s look at these steps in more detail.

 

Connect - API By Discovery

You create an API by specifying a database connection: a database URL and connection information.

The system discovers your base tables, view tables and Stored Procedures from the database?s schema, and provides full REST GET/POST/PUT/DELETE services.  This eliminates the tedious coding to build all the major elements noted in the Reference Implementation:

• REST listeners are provided for each database object, with appropriate parameters for filter, sort
• SQL handling is provided, including key services for optimistic locking and pagination

Ideally, this is not a code generator. The API automatically evolves as your database schema changes.  Ideal implementations would support Resources that span databases.

The service should also provide a mechanism for testing your API, such as the Rest Console from Google.

So, the imagined service has produced a decent API in seconds, just by Schema Discovery.  It?s perhaps useful for some simple Admin apps (list of states etc), but we can?t stop and rest now - we need to provide for:

• Multi-table Resources, with mapping facilities for aliasing column names
• Logic and Security
• Access to other services

Declare - Custom API and Security

If your objective is quick time to market, an ideal approach is to specify/declare what is desired with a simple point and click interface, and rely on the service to handle the low level details.  The sections below describe exactly how you can create

• Custom API
• Enforce Security

Custom API - Multi-table Resources, alias/projection

If the Schema Discovery process finds Foreign Keys, then the service should be able to provide a simple point and click interface for building multi-table resources with full join automation, like this:

Ideal support would provide for schemas without foreign keys.

A Custom API requires a mapping layer to alias table/columns names as shown below:

Enforce Security - row/col granularity

Security is our first task - quickly building an API that does not enforce security is close to a liability.  An accepted approach is to enable users to play one or more roles, and define security over those roles.

The first precaution is to hide default Endpoint access.  Here, the Sales Rep role does not have default visibility to tables or views - it must be explicitly granted:

Once you have locked down the defaults, you can grant Endpoint access for the specific tables like this:

And finally, you should be able to specify row and column level security:

• Sales Reps are only allowed to see the designated columns - any others are not delivered in the REST response.
• Sales Reps can only see rows where their id matches the salesrep_id.

Observe that these security provisions are encapsulated into the table, so are re-used by all Custom Resources defined on that table.  This reduces the ?proliferation of views? that often occurs in relational systems where security only applies to the view, not the rows.

Business Logic

To review, the service we?ve discussed so far has provided an ?instant? API from Schema Discovery, with declarative specifications for Custom Resources providing Multi Row-Types and aliased names, and row/column security.  For read access, we?re in pretty good shape.

But no rest for the weary - updates require critical business logic for derivations and validations, and integration to deal with other systems.  If an API is to provide POST, PUT and DELETE operations, such business logic is surely its responsibility.

Objectives: Reuse, Concise, Standards Based

Logic is surely not the responsibility of client apps, who might enforce things inconsistently.  We?ve all seen enough of this truly evil anti-pattern.  The situation becomes even more obvious when partners submit transactions, who cannot possibly even know the required logic.  Clearly, the logic needs to centralized (shared) in the the REST server.

The underlying idea here is re-use - factor logic so it?s only stated once, and can be easily read and altered.  Ideally, logic and security is not only factored out of clients, but also out of Custom Resources, by centralizing logic on the underlying tables.

Next, logic should be as clear and concise as possible.  The ideal would be an executable requirements specification: readable by Business Users, and executable.

But what language?  Surely not a new one!  JavaScript adoption is exploding, and there is ample support for server-side execution.  Seems like a good choice.

 

Automated Object Model

The first thing you need is an Object Model: a framework for persistence (reading and writing data), with provisions for introducing domain-specific logic.  You are probably used to this in various architectures: active records, ORMs and so forth.  

Ideally such a model is constructed automatically from the Schema, without code generation so that it remains in-sync with schema changes.  So, the system can provide a full JavaScript Object Model, automatically, based on schema discovery:

• Object Types are provided for each Base Table, with attributes for columns (orders.amount_total)
• Object accessors are provided for related data (order.customer, or orders.lineitemList)
• Persistence is provided, both via Object Accessors, and CRUD APIs (Create, Read, Update, Delete) with full transaction caching and transaction management (each request is a transaction)
• Logic is provided by (further detail below):
• 1. JavaScript Update Events for Inserts, Updates or Deletes.  Object instances are passed to events for data access.
  2. JavaScript Derivation / Validation Reactive Expressions that automatically react to changes in the requested objects

Execution Model promotes logic re-use

We now build on the Object Model to introduce an Execution Model that promotes re-use, and is familiar.  In the diagram below, light blue boxes represent object built from Schema Discovery (A), and green boxes represent objects explicitly defined by a developer (B, C).  Red represents runtime execution of a POST, PUT or DELETE request:

Runtime Execution is shown (in red) above:

Key Points:

1. Resource/Object Mapping (step 2) is particularly critical: it enables centralization of business logic into the Object Model, re-using it over all the Custom Resource Endpoints you might define.
2. Change Propagation (step 3) is built into the execution model.  Virtually all business transactions include such logic (a client change to an lineitem?s quantity affects related data: inventory stock, order totals, customer balances and so forth)

Update Events

Here is an event for the purchaseorder table, invoked on all updates (either directly by REST, or by Reactive Programming such as a lineitem change):

1. The row variable (supplied by the service) is an instance of a type within our Object Model, here a purchaseorder
2. logicContext (also supplied by the service) provides Persistence services (see lines 2 and 8)

This seems like clear code, in a familiar model and language.

JavaScript Reactive Expressions

Significant improvements in re-use and conciseness are provided with JavaScript Reactive Programming.  Though the term may be unfamiliar, you?ve used Reactive Programming before - it?s the model of a spreadsheet.  A spreadsheet cell formula A=B+C reacts to changes in C by recomputing A.

Reactive can be naturally adapted to database, by accepting JavaScript expressions that specify how table columns are derived, and how they are validated in response to PUT, POST, and DELETE requests.  Here are Reactive Expressions that compute customer balances and ensure they are within their credit limits:

lineitem.amount = row.product_price * row.qty_ordered




lineitem.product_price = copy(row.product.price)




orders.amount_total = sum(lineitem.amount)




customer.balance = sum(orders.amount_total where paid = false)




validate customer as row.balance <= row.credit_limit

So, let?s evaluate this, relative to our re-use / conciseness objectives.

Re-use is optimal: our customer.balance rule applies to any change to the data, so separate (nearly identical) Event logic is not required for adding orders, changing orders, deleting orders, paying orders? these are all addressed by the one rule.  So, not only is logic factored out of clients and resources, it?s also factored out of Events.  In fact, the ?business idea? is down to 1 line: one place to read and maintain.  You can read more about re-use here .

Conciseness is also excellent.  Reactive means the system ?reacts? to orders changes:

1. Dependency Management: detect what changed (in this case, changes to order?s amount_total or paid), and, if so...
2. SQL: to access the customer (possibly from cache) and adjust its balance, and
3. Propagation: execute its logic (check balance vs. credit_limit).  

So, logic is not cluttered up with code for dependency management, SQL, or propagation.  The result is both executable, and understandable - even to business users.

The impact is meaningful.  The expressive power of these 5 rules is equivalent to 200 lines of database triggers, or 500 lines of Java.  There are several interesting elements of reactive programming (e.g., ordering, performance, complexity handling) which are beyond the scope of this paper.  Reactive is an exciting technology - find out more about reactive here .

Example Usage Scenarios

The combination of connect / discovery automation, declarative, and JavaScript can offer real power.  Consider the following familiar examples.

 

Basic Read Access - customized, secure

The simplest example is basic read access, perhaps to access remote data, or provide corporate data to Power Users.  This is well addressed with Resource support for a mapping layer to alias table/columns, along with the security provisions.

 

API Mashup

JavaScript has full access to other RESTful services, SQL, message sending, and so forth.  Here is an event illustrating a simple API mashup, where the OrdersSvc sub resource is materialized by JavaScript (not automated SQL), and calls another REST service (line 7):

B2B Transactions

In this example, an order is POSTed from a partner.  Whether received in that manner or entered interactively, the system has regulatory responsibilities to report purchases of controlled substances.

Processing the POST event requires conformance to partner names for objects and attributes.  Resource/Object Mapping addresses that nicely - just define a resource matching our partner?s names, and the REST service automatically maps these names onto our database names as a normal part of POST processing.

The notification to authorities is handled by the table event shown below.  Our service provides a key api getResource (line 6), which returns the JSON (again, a Custom Resource is defined to match the Regulatory Agency specifications).  Here the JSON is printed to the debug log; it could also be transmitted as a secure message.

Summary

So, we?ve imagined a RESTful service, enabling us to:

• Create a default API from the Schema Discovery
• Create a rich Custom API with a Multi Row-Types and projected / aliased columns
• Add row/column security by providing filters
• Use an automated JavaScript Object Model for logic centralization / re-use, wherein
• • We add logic at the abstraction level of a spreadsheet with Reactive Programming JavaScript expressions
  • We add JavaScript Events to address complexity and domain-external integration with other systems, email, workflow, etc.
  • The system provides elegant handling of logic propagation to related data, whether by (explicit) event code, or (implicit) reactive logic

We not only have a rich REST API that enforces logic and security, we?ve done better - we have optimized:

• Conciseness - by removing all repetitive code for REST/JSON handling, SQL handling, transaction management, dependency management and performance tuning, our logic is so clean it can be read by business users
• Re-use - so we can quickly understand and change our logic and security

As a result, we can focus on the business level problems, at a level of abstraction more like a requirements document than like low level code.  We have an executable cocktail napkin.  Surely, we?ve earned a rest.

Well, not quite.  Our company has progressed a bit past the imagination stage - we have product / documentation you can explore that provides most of the capabilities described above.