Using REST with the CQRS Pattern to Blend NoSQL & SQL Data

jieforest

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.

jieforest

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.

jieforest

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).

jieforest

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.

jieforest

over.