Martin Fowler has written an article titled “Domain Logic and SQL” on the topic in February 2003, the same year Eric Evans published “Domain Driven Design – Tackling Complexity in the Heart of Software”. Recently we were asked (again) where we would see our position. Obviously, we favour an architecture where the relational database holds as much logic as possible, but why? And how does Sclable come around the common pitfalls?

OOP (Object Oriented Programming) versus SQL

The debate is largely run by technicians divided into two camps: OOP and SQL developers. Unfortunately they’re often divided by organisational and cultural splits. The OOP party isn’t allowed to apply changes to a database as they require, while SQL architects refuse to write helpful views for maintainability and performance reasons.

Though living on both sides, Fowler himself admits to be biased towards OOP (or at least, was in 2003), but he states in his article: “By hiding SQL, application developers are excluding a powerful tool”. To give an impression on how intense discussions already became back then, Fowler mentions that he has been run out of a company by a former client’s OO expert group because he was a “data modeler”.

In this post I roughly follow the route Fowler takes in his article. While written in 2003 and a lot of people still referring to it, many things have changed. I’ll show how Sclable takes advantage of the new world of RDBMS.

Complex Queries

Many application developers would still shy away from even minimal complex SQL queries. This is absolutely understandable, as SQL differs in many ways from programming languages. And when it comes to  JOINs over ten or more tables along with calculations, aggregations and windows, nobody would say, it’s maintainable. How would you be able to react on domain model changes?

Sclable’s concept is called “Selections” and puts an end to this dilemma. Selections can be built extremely fast with a visual query builder generating user permission aware (!) views. Change your domain model and your Selections morph with your requirements.

Transaction Script, Domain Model and Logic in SQL

Fowler compares three approaches to query data and apply domain logic: A procedural pattern called “Transaction Script” described in his book P of EAA with the advantage of having business logic and loading separated in your code, the object oriented domain model with the advantage of a reusable data loading and a more separated yet encapsulated business logic. Both have the downside of requiring multiple queries to lift more data into the memory as required for the logic. The third approach is written in pure SQL. It is quite obvious that the latter is the most powerful, performant and efficient way to go for.

Looking at Performance

Fowler sees more priority in writing maintainable, reusable code than in thinking of performance first. This sounds reasonable, but he also admits that his SQL query example performs 20 times faster in the test setup of his “little laptop” than the two other approaches. His suggestion is to enhance OO code later on, where necessary with more complex SQL queries. Obviously you’ll end up having both: maintainable but unperformant code at large and a growing base of SQL painfully hard to maintain.

Sclable models the domain directly in SQL and adds APIs giving you the benefits of all three approaches: You can extend your business logic in an OO manner in the language of your domain, you get an automatically generated metadata mapping to achieve that and all will be built on top of the full power of SQL.

Modifiability

Fowler sees modifiability as the main reason why developers put business logic in memory rather than into the database. He sees two limits in the capabilities of SQL: You can’t use non-standard extensions if you want portability and you might need some business logic for your session data before persisting it in the database. The next five sections will go into detail about this.

Understandability

This is a topic we have been dealing a lot with at Sclable. Modelling relational database structures and writing business logic are often two different disciplines, since databases are seen as a persistence layer while business logic is made to work on data. Database architects refuse to tackle domain logic, and on top of that, programmers have a hard time to understand SQL to an extent required for writing business applications.

At Sclable we aim to visualize structural as well as logical concepts so anybody, even the end user will be able to understand. Furthermore, we provide APIs on every level of the platform. Our Low-Level SQL-API translates domain logic to our PHP-API which translates it to your application front end. The generator component of the Sclable Core Engine keeps your domain logic moving and morphs all dependant layers with it. Think of it as WYSIWYG on application scale.

Duplication

Fowler states that duplication easily can be avoided in an OO domain model approach. To avoid duplication of SQL code, you would have to create views in the database. In some environments, this would move it out of the scope of the application developers. Well, we use views in Sclable extensively. A fairly large Sclable Business Domain with more than a hundred entities can easily have about the same amount of calculation and aggregation functions and four times as many views to produce domain logic specific results. And all of it only in the database in SQL.

Instead of avoiding a powerful tool to prevent duplication, we are generating all required views and the Sclable Core Engine with its builder component modifies them upon domain model changes, so they are free of maintenance while your domain model always stays DRY.

Encapsulation

A common approach in OOP design is to encapsulate logic. The application layer is having modules interacting via interfaces to simplify their complexity on the outside. Yet the database is seen as one big module a layer underneath. Fowler argues that changes in the data structure otherwise would cause a large ripple effect across a system.

At Sclable, instead of separating the application from its data, we move the domain logic into the database. We still encapsulate logic in modules, but give them a place of its own beside their data. The system overcomes the painful round of editing across the application by listening to the model changes and executing necessary changes automatically.

Database Portability

A big point to not utilize the database all too much is portability. Using the strengths and powers of SQL makes your business application depending on a specific database vendor.

This is true. But as with every business application it depends on the project if this issue has to be taken into account all too much. How likely will it be that you will need to change your database vendor at all? Sclable ships with PostgreSQL on board. And that’s for more than one reason.

Testability

Although it is common practice in SQL not to test, at Sclable we have written our own SQL testing framework we named “Sclunit”. Using the advantages of transactions, we finally are able to run tests directly in the database, with the business data and its logic in a completely non destructive way.

Summing Up

Fowler suggests to go for the development team’s personal comfort. And he is still true, when he states that a lot of people find SQL terribly cryptic. For any critical performance issues you might have to write queries nevertheless. Our experience is that it will be very likely that you’ll end up with an incomplete domain model having parts of your domain logic in SQL as soon as your model gains complexity.

At Sclable we made it our goal to put an end to the debate whether to go for in memory only or use the power of SQL (structured query love). We’ve been set out to deliver a Business Application Development Platform that gives you the best of all. For the majority of business logic you’ll actually never need to write SQL at all, Sclable’s Core Engine does that for you.
Sclable’s development stack also allows to seperate modelling from coding. Coders do not need to bother the system’s architects when it comes to write boring, repetitive SQL and the system architects can focus on the domain model.