Restify your SQL Database
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.
- 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
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
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
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.
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.
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.
- 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):
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:
1 - Request Received
PUT/POST/DELETE requests are received, with one or more resource objects in the request
Objects can be
2- Resource / Object Mapping
The system maps each Resource row onto the underlying Object Model rows, and performs optimistic locking checks.
Provides for logic re-use (see discussion below)
3 - Business Logic
Such change propagation is a key pattern of business logic
4 - Persistence
Persistence is automatic, including caching and Transaction Management - a commit is issued when all request rows are processed, or a rollback occurs if an exception is thrown.
- 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.
- 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)
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):
- The row variable (supplied by the service) is an instance of a type within our Object Model, here a purchaseorder
- 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.
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:
- Dependency Management: detect what changed (in this case, changes to order’s amount_total or paid), and, if so...
- SQL: to access the customer (possibly from cache) and adjust its balance, and
- 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
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.
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.
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
- 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.