article

REST Easy with SQL/NoSQL Integration and CQRS Pattern implementation

New demands are being put on IT organizations everyday to deliver agile, high-performance, integrated mobile and web applications. In the meantime, the technology landscape is getting complex everyday with the advent of new technologies like REST, NoSQL, Cloud while existing technologies like SOAP and SQL still rule everyday work. Rather than taking religious side of the debate, NoSQL can successfully co-exist with SQL in this ?polyglot? of data storage and formats.  However, this integration also adds another layer of complexity both in architecture and implementation.  This document offers a guide on how some of the relatively newer technologies like REST can help bridge the gap between SQL and NoSQL with an example of a well known pattern called CQRS. This document is organized as follows:

• Introduction to SQL development process
• NoSQL
• Do I have to choose between SQL and NoSQL?
• CQRS Pattern
• How to implement CQRS pattern using REST services

Introduction to SQL development process

Developers have been using SQL Databases for decades to build and deliver enterprise business applications. The process of creating tables, attributes,and relationships is second nature for most developers.  Data architects think in terms of  tables and columns and navigate relationships for data.  The basic concepts of delivery and transformation takes place at the web server level which means the server developer is reading and ?binding? to the tables and mapping attributes to a REST response.

Application development lifecycle meant changes to the database schema first, followed by the bindings, then internal schema mapping, and finally the SOAP or JSON services, and eventually the client code.  This all costs the project time and money.  It also means that the ?code? (pick your language here) and the business logic would also need to be modified to handle the changes to the model.

NoSQL

NoSQL  is gaining supporters among many SQL shops for various reasons including:

• Low cost
• Ability to handle unstructured dataa
• Scalability
• Performance

The first thing database folks notice is that there is no schema.  These document style storage engines can handle huge volumes of structured, semi-structured, and unstructured data. The very nature of schema-less documents allows change to a document structure without having to go through the formal change management process (or data architect).

The other major difference is that NoSQL (no-schema) also means no joins or relationships.  The document itself contains the embedded information by design.  So an order entry would contain the customer with all the orders and line items for each order in a single document.

There are many different NoSQL vendors (popular NoSQL databases include MongoDB, Casandra) that are being used for BI and Analytics (read-only) purposes.  We are also seeing many customers starting to use NoSQL for auditing, logging, and archival transactions.

Do I have to choose between SQL and NoSQL?

The purpose of this article is to not get into the religious debate about whether to use SQL or NoSQL. Bottom line is both have their place and are suited for certain type of data ? SQL for structured data and NoSQL for unstructured data. So why not have the capability to mix and match this data depending on the application.

This can be done by creating a single REST API across both SQL and NoSQL databases. Why a single REST API?  The answer is simple ? the new agile and mobile world demands this ?mashup? of data into a document style JSON response.

CQRS (Command Query Responsibility Segmentation) Pattern

There are many design patterns for delivery of high performance RESTful services but the one that stands out was described in an article written by Martin Fowler, one of the software industry veterans. He described the pattern called CQRS that is more relevant today in a ?polyglot? of servers, data, services, and connections.

?We may want to look at the information in a different way to the record store, perhaps collapsing multiple records into one, or forming virtual records by combining information for different places. On the update side we may find validation rules that only allow certain combinations of data to be stored, or may even infer data to be stored that?s different from that we provide.? ? Martin Fowler 2011

In this design pattern, the REST API requests (GET) return documents from multiple sources (e.g. mashups). In the update process, the data is subject to business logic derivations, validations, event processing, and database transactions.  This data may then be pushed back into the NoSQL using asynchronous events. With the wide-spread adoption of NoSQL databases like MongoDB and schema-less, high capacity data store; most developers are challenged with providing security, business logic, event handling, and integration to other systems.

MongoDB; one the popular NoSQL databases and SQL databases share  many similar concepts. However the MongoDB programming language itself is very different from the SQL we all know.

How to implement CQRS pattern using a RESTFul Architecture

A REST server should meet certain requirements to support the CQRS pattern. The server should run on-premise or in the cloud and appears to the mobile and web developer as an HTTP endpoint.  The server architecture should implement the following:

• Connections and Mapping necessary for SQL and NoSQL connectivity and API services needed to create and return GET, PUT, POST, and DELETE REST responses
• Security
• Business Logic

Connections and Mapping

There are two main approaches to creating REST Servers and APIs for SQL and NoSQL databases:

• Open source frameworks like Apache Tomcat, Spring/Hibernate
• Commercial framework like Espresso Logic

Open source Frameworks

Using various open source frameworks like Tomcat, Spring/Hibernate, Node.js, JDBC and MongoDB drivers, a REST server can be created, but we would still be left with the following tasks:

• Creation and mapping of the necessary SQL objects
• Create a REST server container and configurations
• Create Jersey/Jackson classes and annotations
• Create and define REST API for tables, views, and procedures
• Hand write validation, event and business logic
• Handle persistence, optimistic locking, transaction paging
• Adding identity management and security by roles

Now we can start down the same path to connect to MongoDB and write code to connect, select, and return data in JSON and then create the REST calls to merge these two different document styles into a single RESTful endpoint.  This is a lot of work for a development team to manage and control and frankly pretty boring and repetitive and is better done by a well designed framework

Commercial Frameworks

Many commercial frameworks may take care of this complexity without the need to do extensive programming. Here is an example from Espresso Logic and how it handles this complexity with a point and click interface:

• Running REST server in the cloud or on-premise
• Connections to external SQL databases
• Object mapping to tables, views, and procedures
• Automatic creation of RESTful endpoints from model
• Reactive business rules and rich event model
• Integrated role-based security and authentication services.
• Point-and-click document API creation for SQL and MongoDB endpoints

In the example below, the editor shows an SQL (customersTransactions) joined with archived details from MongoDB (archivedTransactions).  The MongoDB document for each customer may include transaction details, check images, customer service notes and other relevant account information. This new mashup becomes a single REST call that can be published to mobile and web application developer.

Security

Security is an important part of building and delivery of RESTful services which can be broken down into two parts; authentication and access control.

Authentication

Before allowing anyone access to corporate data you want to use the existing corporate identity management (some call this authentication services) to capture and validate the user.  This identity management service is based on using existing corporate standards such as LDAP, Windows AD, SQL Database.

Role-based Access Control

Each user may be assigned one or more corporate roles and these roles are then assigned specific access privileges to each resource (e.g. READ, INSERT, UPDATE, and DELETE).

Role-based access should also be able to restrict permissions to specific rows and columns of the API (e.g. only sales reps can see their own orders or a manager can see and change his department salaries but cannot change his own).  This restriction should be applied regardless of how or where the API is used or called.

Remember, the SQL database already provides some level of security and access which must be considered when designing and delivering new front-end services to internal and external users.

Business Logic for REST

When data is updated to a REST Server several things need to happen.  First, the authentication and access control should determine if this is a valid request and if the user has rights to the endpoint.  In addition, the server may need to de-alias REST attributes back to the actual SQL column names.

In a full featured business logic server, there should be a series of events and business rules to perform various calculations, validations, and fire other events on dependent tables.  Finally, the entire multi-table transaction is written back to the SQL database in a single transaction. Updates are then sent asynchronously to MongoDB as part of the commit event (after the SQL transaction has completed).

Conclusion

In the real-world of API services, the demand for more complex document style RESTful services is a requirement.  That is, the ability to create ?mashups? of data from multiple tables, NoSQL collections, and other external systems is a large part of this new design pattern.  In addition, the ability to alias attribute names and formats from these source fields has become critical for partners and customers systems.

Using REST with the CQRS pattern to blend MongoDB and SQL seamlessly to your existing data will become a major part of your future mobile strategy. To implement these REST services, one can use open source tools and spend a lot of time or select a right commercial framework. This framework should support cloud or on-premise connectivity, security, API integration, as well as business logic. This will make the design and delivery of new application services more rapid and agile in the heterogeneous world of information.