Critical Technology: Separation of database reads from writes

More than once I have found myself in discussions regarding the correct way to architect web applications. In a recent discussion the concepts of MVC and REST were in the mix as the discussion focused on mobile devices. In this previous post I spoke of using REST as the preferred way to build the middle tier; particularly, when the application is targeted toward mobile devices. My main rationale for this is how mobile apps are mostly stateless and have less requirements for interactions to be transactional.

CRUD (Creates, Reads, Updates & Deletes)
This previous post generated some interesting discussion from within Google+ and got me thinking about being really clear in how you architect a mobile application using REST / SOA / MVC. To start it is important understand the MVC pattern and n-tier applications and how these two fit in regards to designing and architecting a web / mobile application. Particularly important is to think about is how you architect your CRUD (Creates Reads Updates and Deletes). These need thought because not all web / mobile activities are equal from a transactional and security perspective. So lets consider each of these functions from the web / mobile application development perspective;

CREATE - When creating (adding) data to a database particularly from a web page or mobile device performing this through a RESTful API would not be preferred, particularly when a development language / environment is available. Not to say you couldn't do it through an http POST, it's just that a fully formed development language is going to provide transactional qualities and a more robust set of language features for creating data. Also important when creating data is security and ensuring that context and permissions are enforced when creating data. Ensuring security is also easier to do with fully formed development language.
READ - When reading (fetching) data from a database this is where the most options exist as reading data is a simple operation from a transactional, security and state perspective. And if the reads are open to the public this would really be well implemented in REST. Reads are simple from a transactional perspective for they require no record locking or transactional requirements for they are reads (of course there could be exceptions to this, but in general reads are simple from a database perspective). Reads are simple from a security perspective for databases are good at securing things particularly from a read-only perspective. And if there is private data this can be easily secured at the database level. Reads in there nature are stateless as once the data has been retrieved there is no need to maintain a connection (tightly coupled or otherwise) to the data on the source.
UPDATE - updates are similar to CREATES in they effect change on the database and could require record locking, have transactional qualities or reference to existing data. From a RESTful perspective an UPDATE could be done through a PUT, but if the UPDATE is complicated it would be better done through the use of a robust development language / environment, rather than a RESTful approach.
DELETE - if you are deleting a small discreet amount of data that has no dependencies a DELETE could be done through a RESTful call. This is seldom the case with web or mobile applications. Data often has other related data in the database and sometimes it is better to mark data as deleted rather than delete it completely. The status of a DELETE action is also important as it needs to complete or may leave the database in a false state. Implementing the DELETE with the ability to finalize the action by receiving a status would ensure the data remains accurate.

Action	SQL	REST	Best Practice
CREATE	INSERT	POST	Use robust programming environment rather than use of RESTful calls. Inserting data requires confirmation of success allowing the software to continue appropriately.
READ	SELECT	GET	Reads are the actions which could best be implemented using a RESTful approach, particularly if the reads can service multiple needs and access approaches.
UPDATE	UPDATE	PUT	Again, use a robust programming environment. Updates can be complicated and require reference to existing data.
DELETE	DELETE	DELETE	Use REST only when deleting discreet amounts of data.

All this discussion regarding CRUD and REST leads to a couple of simple principles when deciding what goes where from a RESTful API perspective;

Reads / Reporting of public data are the best candidates for REST.
Creation / Updates of small / discreet amounts of data which fit well within a single concise message can be candidates can be candidates for REST.
Larger amounts of data with some complexity are best left within a robust development environment.

Separate the reads from the writes

Most web applications (mobile or otherwise) display a massive amount of information. Getting this information from its data storage via code (software) to be displayed is where most of the activity happens. The two activities of reading data and writing (creating, updating and deleting) data are fundamentally different. With reading, the application is fetching data with the assistance of indexes, joins and optimized execution paths. When writing, the application is allocating storage, performing duplicated activities (transaction logging), assigning locks to data elements, confirming data integrity, cascading activities, updating indexes and other activities. It could be considered that database reads and database writes are opposites. And the needs of the physical servers and the application software need to be optimized for these two opposite database activities. This can be done by physically separating the databases that service these two different activities. All the writes go to a single database server, which then replicates the written data to the database server(s) dedicated to servicing the reads. The code written for the reads can also be further optimized with caching techniques.

Make a RESTful API available
To add functionality, enable the ability for people to use your data in new and interesting ways, to build traffic and to increase your partner access it is a good idea to publish data via a RESTful API. Of course you need to be mindful to what data is made available via the REST approach. If you are going to make data public be sure it should be read by the public. This kind of data is vast and could be all public government data, all product data, social information, etc. In general, all data available in a RESTful API is read data. As described above in the CRUD description, read data is most appropriate for REST.

Thursday, October 06, 2011

Separation of database reads from writes