Database – Why are SQL databases still used with ORM

Databases are out of process - they run on a different server usually. So even if you had an API, it would need to send something across the wire that represents your query and all of its projections, filters, groups, subqueries, expressions, joins, aggregate functions etc. That something could be XML or JSON or some proprietary format, but it may as well be SQL because that is tried, tested and supported.

It is less common these days to build up SQL commands yourself - many people use some sort of ORM. Even though these ultimately translate into SQL statements, they may provide the API you are after.

ORMs provide a few benefits besides the primary purpose of mapping object data to relational data. A traditional ORM and common 3rd party ORMs may not particularly fit what you are doing, and that is a good starting point for a rationale for explaining why one was not employed, but there are other benefits of ORMs that might want to consider or reconsider.

• ORMS promotes or enforces the sharing of common interfaces at a distinct layer and system boundary

  When encountering ORM specific types, one should be very close to leaving the realm of application code, and entering persistence code. This should be the case even in the event that you roll up and put in place your own custom ORM over or in preference to a 3rd party's ORM. Ideally, the structure of an application should be that dropping and replacing one ORM and an entire persistence mechanism with a competing or alternative ORM and persistence mechanism without a relative great deal of effort in the form of fundamental change should be relatively painless.

  Some applications go to the trouble of making component replacement a particularly high priority and employ dependency injection to provide the actual ORM provider. In this case, a purely academic setting, DI for the ORM provider is probably not necessary. In fact, most projects would see that is quite beyond unnecessary. For some though, that indirection and flexibility are very valuable as it allows different bits to be plugged in that provide notable situational benefits depending on which benchmarks or features they are chasing. Forum software is one such class of software that might be a good candidate for that kind of flexibility, especially if targeted as a mass-market product.

  Is there a clear distinction between domain behavior and persistency concerns? Does that distinction occur in the same way across the entire or majority of the application?

  Are queries first class citizens, or are they merely presented as formatted strings to a data source? What kind of mechanism is in place to extend or modify a query? Can queries be extended or modified through parameter assignment? How are constraints managed? Can the sorting strategy be easily changed programmatically during run-time? Do not answer those questions here, just consider them as you further evaluate what it might mean to use an ORM or to employ the more prominent if understated features of an ORM.

  Are there any behaviors within the persistence layer that are fundamentally the same and could benefit from further common abstractions?

• ORMs often provide caching

  Why do we use caching? To keep needed frequently read, but infrequently written data, closer. The only way to know definitively if caching would or would not help is through testing and profiling. However, there are times where it just becomes obvious that some caching might help.

  For instance, promoting a transient variable from a local block scope to a more global scope can be considered a form of caching and can significantly improve performance in the right circumstances. That strategy, not unlike any other strategy has a tradeoff. The tradeoff for promoting a variable's scope is we remove some inherent protections that come with that shorter lifetime and reduce the degree of encapsulation.

  Forum software as a class of software is often a phenomenal candidate for caching. Usually the bulk of forum operations is reading data many more often than writing data, especially considering the many forums have popular topics that are read and reread perhaps many times relative to the number of times new posts that extend the topic are added. The forum software in question may not realize a great benefit as a purely academic project because it may not experience near real world conditions that real world scenarios of many users hammering away at the site would produce.

  Since at this point you are not relying on a third party ORM, your potential caching mechanism and strategy is a bit more flexible. You can use a common provider to cache both post-processed pages and datasets.

There are more benefits to ORMs that aren't mentioned. But as this is academic, I don't want to take all the pleasure out of the research process. Take a closer look what ORMs provide either as a direct, advertised benefit, or as a beneficial consequence or side-effect that often accompanies their use.