article

REST access to SQL data is core to mobile, integration and web apps.  Business requires both agility, and Enterprise depth ? a seeming contradiction.  Here are 3 game-changing technologies that make it possible to create Enterprise-class APIs with near-instant speed.

REST is the architecture of choice.

REST is a scalable, networked protocol for read/write access to resources, such as data in a database.  It works over HTTP with any language, and it?s simple.  It?s become the defacto standard for Web Services.

For information systems, it?s a required technology for mobile and integration, since JDBC/ODBC is not possible.  And, its model of Nested Documents (think: order with items) is perfect for exchanging complex transactions, and reducing latency by replacing multiple calls with a single call.

It?s even great for Web Apps, since it?s language independent and so can address heterogenous technologies.

SQL Pass-through is easy.  Enterprise REST, not so much.

There are lots of alternatives for ?Restifying? SQL data ? essentially a networked version of JDBC/ODBC.  But that?s just the protocol.

The real work is under the surface ? Enterprise-class services to scale, integrate and enforce:

• Scale ? your API will require pagination to address large result sets.  It needs to return Nested Documents to reduce latency.  It needs to support optimistic locking.  These are not provided in a simple SQL Pass-through.
• Integrate ? a wizard can produce an API from schema objects, but it cannot address multiple databases, or integrate non-SQL data sources such as ERP, other RESTful services, or NoSQL.
• Enforce ? our API needs to enforce our security (down to the row level), and the integrity of the data.  These are significant tasks, which are sadly often placed in client buttons where they cannot be shared.

So, providing the Enterprise class services takes significant time, expertise and expense.  Business Agility is reduced.

Instant Enterprise REST

So we have a business need ? Agility ? and a rather demanding requirement ? Enterprise-class services.  One would think you?d have to choose between agility and depth.

The current best of breed approach ? Wizard to get you started, then ?your code goes here? ? does not really address significant issues such as business logic enforcement.  There are lot of code to go ?there?.

We can deliver our Enterprise-class API, with business agility, with 3 key technologies.  Let?s briefly examine them.

Technology 1: Enterprise Pattern Automation

There are well known patterns in the data domain, describing data structure and access via SQL.  There are also well-known patterns for managing SQL data in the context of RESTful services.

Well known patterns can be automated.  Let?s imagine a service (say, a server accessed via a browser) that automates these patterns, as described below, just by connecting the service to a database:

• Schema Discovery ? tables, views, stored procedures: The system can create a complete (default) API for each schema object.  Note this should include Stored Procedures, which often represent a significant investment.
• Enterprise Pattern Automation: the resultant API can provide well-known services for Filter, Sort, Pagination, Optimistic Locking, handling Generated Keys and so forth.

So, the service has provided a default Enterprise-class API, instantly.  Not enough, not done, but a great start.

Technology 2: Declarative

Declarative is the key (?what, not how?).  It has had striking impacts on domains where there are well-understood underlying patterns.  Max Tardiveau has put it well:

Whatever can be declarative, will be declarative.

For example, spreadsheets are declarative ? and they gave birth to the PC industry.  And SQL is declarative ? itself an industry.  Two game-changers.

So, the challenge is to apply the spirit of declarative to REST integration and enforcement.  The stakes are high ? success can deliver breathtaking agility.

Declarative Integration: Multi-Database Custom API, Point and Click

Enterprise Pattern Automation provides a good start, but the API is not rich.  It is a flat, single-table API, really just ?restified? SQL.  What we really need is

• Nested Documents ? returning multiple types (e.g., an Order, a list of Items, and a list of contact names) in a single call can reduce latency (vs. a separate call for each type).  REST is perfect for this.
• Multi-database APIs ? a RESTful server provides the opportunity to integrate multiple databases in single call, shielding clients from underlying complexity.

Nested Documents are easy: define them by simply selecting tables (via a User Interface or Command Line).  Foreign Keys can be used to default the joins.  Add the ability to choose / alias columns, and we?re on the way to a pretty good API.

But what about databases that have no Foreign Keys?  Or multi-database APIs?

Leveraging the schema does not mean we are limited to it.  All we need to do is:

• Provide a means to define ?Virtual? Foreign Keys for the service (i.e., stored outside the schema)
• Extend this to Foreign Keys between databases

We now have a rich, multi-database API.  Defined declaratively, no code required.  Should be running in minutes.

Declarative Enforcement: Integrity Logic, with spreadsheet-like rules

So now consider enforcement, specifically database integrity.  A very significant portion of any project is the multi-table validations and computations that define how the data is processed.

?Your code goes here? means, well, a lot of code.  We need a more powerful, more declarative, paradigm.

In a spreadsheet, you assign expressions to cells.  Whenever the referenced data is changed, the cell is updated.  Since the cells references can chain, a series of simple expressions can solve remarkably complex problems.

What if we did the same for database data?  We could assign derivation expressions to columns, and validation expressions to tables.  Then, the API could ?watch? for requests that change the referenced column, and recompute (efficiently) the calculated column.  Just as in a spreadsheet, support for chaining and proper ordering is required and implicit.

To address multi-table logic, such expressions would need to address references to related tables.  It?s only at this point that the logic becomes seriously powerful.

Let?s take an example.  To check credit in a Customer  / Purchaseorder / Lineitem application, we could define spreadsheet-like expressions such as:

Customer validation: balance < credit_limit
Customer.balance := sum(purchaseorder.amount_total where paid = false)
Purchaseorder.amount_total := sum(lineitem.amount)
Lineitem.amount := qty_ordered * product_price
Lineitem.product_price := copy(product.price)

There is actually a sub-branch of declarative that addresses this: Reactive Programming.  We?ve denoted that with the := symbol, indicating the expression reacts to changes in referenced data.  Here it?s declarative,since you don?t need to code a Observer handler.

The result is that the logic above can be fully executable.  No need to code Change Detection / Change Dependency ? it?s enforced automatically by the API in reaction to RESTful updates.  SQL handling is also implicit, including underlying optimizations (caching, pruning etc).

The impact can be massive ? the 5 expressions above express the same logic as hundreds of lines of code.  And quality goes up, since the rules are applied automatically.

Declarative Enforcement: Security, filter expressions for role/table

We can provide an analogous approach to security: define filter expressions for roles (like SalesRep), so that when a table is accessed by the role, the API adds the filter.  That way, a user with that role sees only the rows for which they are authorized.

Technology 3: Standards-based Extensibility

Declarative is great, but you?re probably thinking ?ok, but you can?t solve every problem declaratively?.  And you?re dead right.

Business Value requires that we integrate a declarative approach with a procedural one that is familiar, standards-based, and enables us to integrate existing software.

JavaScript Object Model

The first phase of many projects is to build an ORM for natural programmatic access to data: JPA, Hibernate, Entity Framework.  It?s not a small project, and cumbersome to maintain as changes occur.

In fact, the Object Model can be created directly from the schema.  So, you?d have an object type for Purchaseorder, for Lineitem, and so forth.  The model provides access to attributes and related data, and persistence services.  You could then use it like this:

auditRow = PersistenceManager("OrderAudit");     // create a new row
auditRow.name = orderRow.customer.name;  // orderRow type is automatic
auditRow.orderNumber = orderRow.orderNumber;
auditRow.orderDate = row.orderDate;
... more similar assignments
PersistanceManager.save(auditRow);

JavaScript seems like the best language choice: reasonable across technology bases (everybody uses JavaScript), and its dynamic nature eliminates code generation hassles.

JavaScript Events

In addition to accessors and persistence, the JavaScript objects are Logic Aware.  That is, the save operation above executes any rules associated with OrderAudit (e.g., updated-by), and JavaScript Events.

The logic above is, in fact, an Event on the Purchaseorder object.  So, each entity encapsulates its logic, both rules, and events.

Extensible Logic

Note the [?] code above.  It can become lengthy, and auditing is a common pattern.  It should be possible to solve this once in a generic manner, then re-use it (e.g, to audit employees, orders and so forth).

So, Instant Enterprise REST should enable you to provide Extensible Logic ? load your own JavaScript code, and invoke it.  So, the code above could become:

where auditFromTo creates an instance of OrderAudit, sets the foreign key, sets like-named attributes, and saves it.

Pluggable Authentication

Most organizations have existing data stores that identify users and their roles, such as Active Directory, LDAP, OAuth, etc.  Security should integrate with such systems as a function of enforcing row/column access.

Standard deployment

Finally, the system should deploy in a familiar manner: available on the cloud, or an on-premise virtual appliance or war file.

Instant Enterprise REST: Business Agility

Many regard a typical system as half User Interface, and half back-end (data access, integration, and enforcement).  So, just by responding to our Enterprise Requirements, we have far more than a thin API providing data access.  We have half a typical system, created declaratively for agility, that?s extensible.  And we have it a tenth the time.

And that?s a Game Changer.  It offers the real prospect of eliminating the ?3-6 months to add a field? that is so frustrating to the both the business and IT.  We can now create important portions of our software in largely business terms, rather than technical terms.

We take this vision seriously at Espresso Logic.  And we love discussing it.  Check it out, and get in touch!