valhuber avatar image
valhuber posted

Mobilize and Restify your SQL Data

Full-featured back-office Mobile apps in minutes, with RESTful APIs for front-office apps.  Declarative logic and security - simple and powerful as a spreadsheet.  JavaScript for complex logic and integration.  Here?s two new technologies that make it possible.

Current Approaches

The promise of accelerating delivery time has motivated much good work over many years:

  • Approaches such as Model Driven Architecture[1] and Model Driven Engineering[2] achieved some success with building components, but fell short of building fully executable systems.  

  • Convention over Configuration[3] builds complete web apps, but the UIs are incomplete (no master/detail, updatable grids etc. - see below), and provide little support for multi-table business logic.

A new category of MBaaS (Mobile Backend as a Service) is focused on cloud technology, such as REST / JSON.  Products like DreamFactory and SlashDB create a complete RESTful API for your SQL schema, with convenient table browser User Interfaces.  

Proposed Approach

A great start, but the real promise is achieved with full multi-table applications, and declarative logic and security.  This is now possible, given the following technologies:

  • Executable Schemas

Using just an existing schema, create a REST/Cloud service that pre-supplies the common behavioral patterns for Multi-Table UI and API.  An instantly executable system, out of the box.

  • Declarative, Business-oriented Behavior

Provide declarative support for API extensions (such as multi-table resources), and for logic / security behavior.  The real value is delivering these behaviors at a very high level of business abstraction, more like requirements than code - but executable.

  • Fully integrated imperative language support in a standard language

Provide for procedural events integral to the model, using a standard language such as JavaScript.

This empowers the development team to operate - deliver - at a near-business level of abstraction, with meaningful increases in speed.  They can translate BDD stories into running software to confirm understanding, and uncover the next set of behaviors.  And they can do this not only with speed, but with dramatically reduced miscommunication since stakeholders are seeing running software, and can read the ?code?.

The following sections explore these notions in greater detail.

Executable Schema - just by connecting

If we are to meet the business demands for instant results, we need to be running right out of the box.  That is, just by connecting our system to a schema, we should have a completely executable API and Application as described below.

Default Application

It is quite possible to create a richly functional mobile-ready back-office app, directly from a schema.  The presumption here is that while ?front office? apps require a handcrafted UI, there is a large class of data maintenance apps that follow a set of well-known patterns, and that these can be created from the schema.  And both can use the same underlying RESTful API for access, logic and security.

The sample screens below illustrate these common patterns for a fully functional data maintenance application:

  • Search / Filter, with paginated List / Form view (e.g., right/left panes below, with sortable grid headers, etc.)

  • Foreign Keys drive Multi-Table application behavior:

    • Master/Detail (Customer / Purchaseorders)

    • Drill-down Navigations (the zoom button at the lower right transitions to Screen 2 ? Purchaseorder with LineItems)

    • Automatic Joins: show Product Name, not Number

  • Update support, including updatable grids, lookups (on Screen 2, the up-arrow button to select a product for the LineItem), and error handling

The join functionality is interesting, because it is quite common.  In the example above, consider the LineItem Name column.  The Product foreign key is actually a Product Number, not Name - a very common pattern.  In the ideal case, the system would recognize this case (numeric foreign keys), and automatically join in a more suitable column (the Name), automatically.  We would have not only an executable schema, but an instantlyuseful one as well.

Default REST API

The architecture of choice for cloud / mobile apps is a RESTful API.  Such an API not only provides mobile access, but also network capable Web Services suitable for data integration.  And, it is a proper place to enforce logic and security.

And the mapping from database to REST is straightforward:

  • Create a REST Endpoint for each base/view table, even Stored Procedure

  • Provide Get/Put/Post/Delete support (familiar CRUD operations), with support for

    • filtering and sorting

    • well-known patterns such as pagination, discovery for related data, and optimistic locking

The result is a ?flat?, relational API.  A good start, but we?ll need to extend it as described below.

Declarative Behavior

While we are proposing that the model not replicate the schema, there are clearly requirements to extend it for the key behaviors below.

Rich REST API: Multi-Table Resources, Alias, Projection

Our ?flat? API is a good start, but a richer ?document oriented? API is required.  This requirement is both as a convenient API model for mobile developers, and to minimize latency due to multiple queries.

Such objects are somewhat like relational views, with support for aliasing and projection.  Unlike views, they are not flat - they support nested ?Sub Resource? objects, such as an Order object with LineItems and Shipment information.

Such ?multi-table views? might be defined via point and click, such as:

Logic: Spreadsheet-like Reactive

Business Logic is a significant element of most systems.  It is tedious to code, consisting of significant amounts of change detection and propagation logic, sql (with caching for performance), etc.

The reasonable assumption is that such logic is domain specific (true), and that it must therefore be coded in an imperative, procedural language (false).  A declarative approach for such logic would be a significant advance in accelerating software delivery and maintenance.  Reactive technology provides exactly such a solution.

Reactive is currently getting attention in handling UI events (e.g., sync model to view).  But the most common use is the spreadsheet - cell formulas that reactto changes in referenced values by recomputing themselves, which of course can chain.  While simple enough for non-programmers, spreadsheets provide sufficient power to address non-trivial complexity.

Reactive fits very well for database transaction logic:

  1. Declare expressions to derive database columns, or ?virtual columns? (these become part of the model for transaction processing, but are not physically stored).  These are exactly analogous to spreadsheet cell formulas.

  2. RESTful Put/Post/Delete operations are watched for changes in referenced values, which trigger reactions (e.g., adjustments) to the referencing value

  3. Validations are addressed as well (balance < creditLimit).

Most interesting transactions are multi-table, so it follows that our expressions be able to reference related data (customer.balance := sum(orders.amount_total where paid = false).  This enables - requires - the system to take responsibility for not only the change detection, but also persistence (reading / writing related data, caching, optimizations, etc).  

Note that encapsulating logic to columns results in inherent re-use.  In the example above, our declarative system watches, and reacts to, changes in the orders? amount_total or paid flag.

Declarative logic is by definition un-ordered.  So, the system must deduce the execution order, for example via a dependency graph.  This has substantial value in maintenance - automatic ordering means you add / change logic without worrying about the details of execution order.

By eliminating the dependency management, propagation and SQL handling, meaningful advances in conciseness and readability are realized.  Meaningful here means in excess of an order of magnitude, where the following logic - virtually a requirements spec - being directly executable:

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

There is, of course, no silver bullet.  Not all logic can be declarative, so critical provisions are required for proper integration of procedural logic.  These are described below.


Another key requirement of most systems is security.  Beyond familiar endpoint access, the system must enforce security at the row and column instance level.  For example, you might want folks to see orders only in their own region.

Many systems provide such functionality in views, but that leads to a proliferation of views to define and maintain.  A better approach is to encapsulate the security into the table as a role-based permission (predicate, below), and ensure it is reused across Multi-Table Resources.

The notation @{current_employee} is meant to suggest mechanisms to associate a database row with a user, so that its columns (e.g., employee_id) can be as parameters in filter expressions.

Procedural Extensions

Executable Schemas and declarative behavior can provide value, but only if they are integrated with familiar procedural extensions so there are no boundaries.

Server-side JavaScript

It?s straightforward to use the schema to create a JavaScript row type for each table, with accessors for columns and related data.  Much like Ruby Active Records, these are persistence-aware, and logic-aware: they ensure the execution of the proper reactive logic as updates are processed.

In addition, they can publish events you can handle in server-side JavaScript.  Events are a familiar model (before insert, before update), and provide full access to JavaScript (send messages, start processes, etc).  JavaScript is an excellent language choice, since it?s a language that?s part of virtually every system, so is reasonably familiar to most developers.

UI Creation

Extensibility must also be addressed on the client side.  Default UIs are powerful and useful, but most appropriate for back office applications.  For front-office applications, there must be provisions to develop any User Interface.

There are two reasonable approaches.  The Default UI can be forward engineered (code generated) into your favorite platform / IDE, so you can use it as a starting point.

Alternatively, it is reasonable to turn the model inside out, and craft the default UI objects as components, that can be snapped into a larger app as parameterized iFrames.  This retains the model linkages, avoiding the ?you own it? implications of code generation.

Custom APIs

Finally, declarative API creation in no way obviates the capacity and need to build custom APIs.


We have outlined here a realistic approach for:

  • Instant creation of a mobile app and a default RESTful API, from an existing schema

  • Extending the API to define multi-table hierarchical resources

  • Adding behavior using declarative ?reactive? expressions to govern update logic and row access, with the simplicity and power of a spreadsheet

  • Integrating server-side JavaScript for complex behavior

Our company  has been executing on this vision, and now provide most (not all) of the elements described above.  If you?d like to check out this technology, we offer a free eval with zero install (it?s a service) that you can try on your own database.  And we?re always eager to exchange views about the technology - get in touch!


[1] The OMG effort

[2] Model Driven Engineering (see this,  this and this)

[3[ Convention Over Configuration

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