Domain Logic and SQL (2003)

I wince every time I use application code to do what I know SQL can do faster and easier. ORMs are nice but it's often impossible to translate performant SQL into the ORM's API. SQL is just so concise.

This is why I like transparent sql libraries like <a href="https:&#x2F;&#x2F;www.hugsql.org&#x2F;" rel="nofollow">https:&#x2F;&#x2F;www.hugsql.org&#x2F;</a><p>Just writing sql and maintaining and testing it like regular code is a perfectly reasonable thing to do.<p>Not to mention that having sql be callable as an actual function in the programming language that the rest of your application is written in is just so much more comfortable than having small bits of sql scattered about in strings.

It is worth mentioning that there are some data-process heavy work that can be done much faster and more efficiently in SQL.<p>Real life example for a regulatory batch job:<p>6 tomcat servers + 1 RDS. 30X lines of code + UTs in java. 30+ minutes time.<p>In SQL No tomcat servers + 1 RDS instance. 1X lines of code(SQL) + UTs (in java). 3+ minutes<p>Here is a good book on it: <a href="https:&#x2F;&#x2F;www.amazon.co.uk&#x2F;Relational-Database-Programming-Set-Oriented-Approach&#x2F;dp&#x2F;148422079X&#x2F;ref=sr_1_1?keywords=set+oriented+approach&amp;qid=1582289243&amp;sr=8-1" rel="nofollow">https:&#x2F;&#x2F;www.amazon.co.uk&#x2F;Relational-Database-Programming-Set...</a>

this really depends on the context.<p>Scenario 1: No plans to add additional functionality. Sticking to the e-commerce example in the article, if I am designing a system for use by just one customer who wants to offer the discount for just that one scenario and they don’t have any future needs to offer any other kinds of discount then put logic in theDB. It’s quick and easy.<p>Scenario 2: Plans to expand to add additional functionality. Sticking to the example we have future plans to offer other types of discounts to our customers. Which means we have to develop generic interfaces so it’s easy to add functionality. Have the business logic in the application layer.<p>Scenario 3: plans to expand and additional functionality but also let users configure and add additional functionality without IT intervention. Sticking to the e-commerce example we want business users to create new offers and expose them to our customers. In this case use a rule engine like drools.<p>It really depends on what functionality you are delivering and how you see that evolving in the future

The article was written at a time when &quot;polyglot&quot; wasn&#x27;t common iirc ... to the extent that knowing SQL in addition to a programming language wasn&#x27;t common.<p>Today the thinking &quot;keep all domain logic in code&quot; would take you to crazy places given the diversity of systems we need to build applications.<p>&quot;A foolish consistency is the hobgoblin of little minds&quot; and all that.<p>If anything it seems more sensible to centralize storage of all business rules in the DB.

One thing this leaves out is the danger of concurrency issues, which can sometimes be worsened by using an ORM. Writing correct, race-free SQL can be very hard (with READ COMMITTED transactions) or require complex retry logic (with SERIALIZABLE). Preventing deadlocks can also be a concern.<p>ORMs tend to hide the underlying SQL operations, making it even harder to verify whether operations are concurrency safe.

It is a great tragedy of our day that so many software engineers are poor at SQL. Use tools like Jinja and DBT to modularize it, and Airflow to schedule it; but use of SQL should be maximized wherever possible.

One thing this article doesn&#x27;t mention is that in many cases databases scale poorly, and only vertically. You can throw a bunch of domain logic in complex SQL queries and see significant performance benefits, but at some point you may find that your application has grown and your SQL server is running into IOPS and CPU limits that are difficult to fix. At that point your answer could be, pull logic out of those complex queries and create simpler queries that ask less of the DB and run on the application side so you can scale horizontally.