One thing that irks me beyond all reason is Robert Martin’s seething dislike for databases. In every presentation he’s ever given, the one thing he’s sneered at is people who “write their code around a database.” In one of his lectures he says, “I don’t want to see a database in your design. I want to see the objects you’ll use, and I want their names and locations in your project file to reflect how you’ll use them.”

This is probably the lousiest piece of advice he’s ever given. Because let me say this once and simply:

SQL is a programming language, not a storage mechanism.

In one of Uncle Bob’s presentations, he talks a lot about a payroll system, and in his one biggest example he talks about a function that calculates how much someone should get paid for travel expenses. The problem with the function is manyfold; it embodies in compiled code business rules about how much someone is allowed to be reimbursed (literally encoding dollar values into the code), it calculates the difference between expensed costs and reimbursed costs, and it finishes by printing out a report of one an employee’s expenses, with reimbursements and a flag that shows when the amount reimbursed is less than the amount expensed because the amount went over those hard coded maximums.

One of Uncle Bob’s mantras is that “the design of a system, and the layout of its code, should reflect the purpose and architecture of its domain, not its framework.” In short, a system about Payroll should talk about Payroll.

So take a look at the number one, top example on Google of Martin’s Payroll demo. Specifically, scroll down to the section labeled “My Implementation.”

I see “entities,” “use_cases,” “adapters,” “controllers,” “views,” “boundaries,” and so forth. You know what I don’t see?

I don’t see Payroll mentioned anywhere.

I see a framework. A framework in the dictionary sense: “a basic structure underlying a system, concept, or text.” That is exactly what Clean Code is. This example is in the “Uncle Bob’s Framework,” because it exists only in the minds of Uncle Bob’s minions, and is imposed ad-hoc by his loyal followers..

The Travel Expenses example is a precious one, because it exposes a lot of the tedious assumptions within Clean Code. The number one assumption is that, if you can encapsulate all of the functionality of a subsystem within a class declaration, this is somehow “better” than if the functionality is distributed across a couple of different files.

As I said in my previous example, an awful lot of code is nothing more than formatting data into human-readable results. So let me set out my thesis simply:

The difference between class Expenses { ... } and CREATE TABLE Expenses ( ... ) exists only in the minds of Clean Code fans.

Let's look closely at Uncle Bob's Travel Expenses example. In detail, the problem set is this:

Given a list of categorized expenses made by a user during a period of time, write a function that prints out a list of those expenses along with the category and type, the amount reimbursed, and highlighting those expenses which went over the maximum reimbursed amount.

So, we need the following objects in our system: User, Expense Category, Expense Type, and Expenses. And we can formulate this in the following way: “Expense types have an Expense category; Expenses have an Expense Type; Users have Expenses.” You can even diagram that out, but it’s the most basic Entity Relationship Diagram in the world.

What would that look like in SQL? Well:

<code class="sourceCode sql"><a title="1" class="sourceLine" id="cb1-1"><span class="kw">create</span> <span class="kw">table</span> users (</a>
<a title="2" class="sourceLine" id="cb1-2">  <span class="kw">id</span> <span class="dt">int</span> <span class="kw">primary</span> <span class="kw">key</span> <span class="kw">generated</span> <span class="kw">by</span> <span class="kw">default</span> <span class="kw">as</span> identity,</a>
<a title="3" class="sourceLine" id="cb1-3">  username text <span class="kw">not</span> <span class="kw">null</span>);</a>
<a title="4" class="sourceLine" id="cb1-4"></a>
<a title="5" class="sourceLine" id="cb1-5"><span class="kw">create</span> <span class="kw">table</span> expense_categories (</a>
<a title="6" class="sourceLine" id="cb1-6">  <span class="kw">id</span> <span class="dt">int</span> <span class="kw">primary</span> <span class="kw">key</span> <span class="kw">generated</span> <span class="kw">by</span> <span class="kw">default</span> <span class="kw">as</span> identity,</a>
<a title="7" class="sourceLine" id="cb1-7">  expense_category text <span class="kw">not</span> <span class="kw">null</span>);</a>
<a title="8" class="sourceLine" id="cb1-8"></a>
<a title="9" class="sourceLine" id="cb1-9"><span class="kw">create</span> <span class="kw">table</span> expense_types (</a>
<a title="10" class="sourceLine" id="cb1-10">  <span class="kw">id</span> <span class="dt">int</span> <span class="kw">primary</span> <span class="kw">key</span> <span class="kw">generated</span> <span class="kw">by</span> <span class="kw">default</span> <span class="kw">as</span> identity,</a>
<a title="11" class="sourceLine" id="cb1-11">  expense_type text <span class="kw">not</span> <span class="kw">null</span>,</a>
<a title="12" class="sourceLine" id="cb1-12">  maximum money,</a>
<a title="13" class="sourceLine" id="cb1-13">  category_id <span class="dt">int</span> <span class="kw">references</span> expense_categories);</a>
<a title="14" class="sourceLine" id="cb1-14"></a>
<a title="15" class="sourceLine" id="cb1-15"><span class="kw">create</span> <span class="kw">table</span> expenses (</a>
<a title="16" class="sourceLine" id="cb1-16">  <span class="kw">id</span> <span class="dt">int</span> <span class="kw">primary</span> <span class="kw">key</span> <span class="kw">generated</span> <span class="kw">by</span> <span class="kw">default</span> <span class="kw">as</span> identity,</a>
<a title="17" class="sourceLine" id="cb1-17">  user_id <span class="dt">int</span> <span class="kw">references</span> users,</a>
<a title="18" class="sourceLine" id="cb1-18">  event <span class="dt">timestamp</span> <span class="kw">not</span> <span class="kw">null</span>,</a>
<a title="19" class="sourceLine" id="cb1-19">  type_id <span class="dt">int</span> <span class="kw">references</span> expense_types,</a>
<a title="20" class="sourceLine" id="cb1-20">  amount money <span class="kw">not</span> <span class="kw">null</span></a>
<a title="21" class="sourceLine" id="cb1-21">);</a></code>

Now, just look at the amount of domain-driven knowledge we get for free! All because we chose a language that has the relational algebra and its set-theoretic basis at its heart, we get: guaranteed uniqueness of users, guaranteed uniqueness of expenses, categories, and types; guaranteed non-empty strings for documentation purposes, and we get a built-in money type that’s absent from just about all damned languages. (Haskell and C# have money types, just to cut you off if you’re going to “actually…” at me.) No references to users can exist that point to non-existent users. An expense that references a non-existent expense type can never happen, and the expense types can be updated without having to recompile an entire module. In a general-purpose language like Java or C#, all of those guarantees would require hand-coding, and the writing of tests, and the checking of assertions. One of the best things about modern languages like Haskell or SQL, or like well-written Rust, is that not only do you get those guarantees for free, you can't even write tests for those guarantees because violations are impossible to generate in those languages.

A system like this requires a lot more to keep track of the changes made to the expense accounting system, but the history of changes and decisions made about how expenses are to be tracked and monitored is better done within the system, rather than being some developer’s casual annotation in the body of some version control system somewhere.

Given that this is what we have (and it emulates Uncle Bob’s code exactly), what would the expense report function look like? We have these relationships that we would like to assemble together into a report. That report is simple:

<code class="sourceCode sql"><a title="1" class="sourceLine" id="cb2-1"><span class="kw">CREATE</span> <span class="kw">VIEW</span> expense_report <span class="kw">AS</span> <span class="kw">SELECT</span></a>
<a title="2" class="sourceLine" id="cb2-2">  users.<span class="kw">id</span> <span class="kw">AS</span> user_id,  expense_types.<span class="kw">id</span> <span class="kw">as</span> expense_type_id, </a>
<a title="3" class="sourceLine" id="cb2-3">  expense_categories.<span class="kw">id</span> <span class="kw">as</span> expense_categories_id,</a>
<a title="4" class="sourceLine" id="cb2-4">  username, event, expense_type, expense_category, amount,</a>
<a title="5" class="sourceLine" id="cb2-5">  maximum,</a>
<a title="6" class="sourceLine" id="cb2-6">  <span class="cf">CASE</span></a>
<a title="7" class="sourceLine" id="cb2-7">     <span class="cf">WHEN</span> maximum <span class="kw">IS</span> <span class="kw">NULL</span></a>
<a title="8" class="sourceLine" id="cb2-8">       <span class="cf">THEN</span> amount</a>
<a title="9" class="sourceLine" id="cb2-9">       <span class="cf">ELSE</span> <span class="fu">least</span>(amount, maximum) <span class="cf">END</span></a>
<a title="10" class="sourceLine" id="cb2-10">     <span class="kw">AS</span> reimbursed,</a>
<a title="11" class="sourceLine" id="cb2-11">  <span class="cf">CASE</span> <span class="cf">WHEN</span> maximum <span class="kw">IS</span> <span class="kw">NULL</span></a>
<a title="12" class="sourceLine" id="cb2-12">    <span class="cf">THEN</span> <span class="st">' '</span></a>
<a title="13" class="sourceLine" id="cb2-13">    <span class="cf">ELSE</span></a>
<a title="14" class="sourceLine" id="cb2-14">      <span class="cf">CASE</span> <span class="cf">WHEN</span> amount <span class="op">></span> maximum</a>
<a title="15" class="sourceLine" id="cb2-15">        <span class="cf">THEN</span> <span class="st">'*'</span></a>
<a title="16" class="sourceLine" id="cb2-16">        <span class="cf">ELSE</span> <span class="st">' '</span></a>
<a title="17" class="sourceLine" id="cb2-17">      <span class="cf">END</span></a>
<a title="18" class="sourceLine" id="cb2-18">    <span class="cf">END</span> <span class="kw">AS</span> <span class="kw">over</span></a>
<a title="19" class="sourceLine" id="cb2-19"><span class="kw">FROM</span> expenses</a>
<a title="20" class="sourceLine" id="cb2-20">   <span class="kw">INNER</span> <span class="kw">JOIN</span> expense_types</a>
<a title="21" class="sourceLine" id="cb2-21">     <span class="kw">ON</span> type_id <span class="op">=</span> expense_types.<span class="kw">id</span></a>
<a title="22" class="sourceLine" id="cb2-22">   <span class="kw">LEFT</span> <span class="kw">JOIN</span> expense_categories</a>
<a title="23" class="sourceLine" id="cb2-23">     <span class="kw">ON</span> category_id <span class="op">=</span> expense_categories.<span class="kw">id</span></a>
<a title="24" class="sourceLine" id="cb2-24">   <span class="kw">JOIN</span> users</a>
<a title="25" class="sourceLine" id="cb2-25">     <span class="kw">ON</span> user_id <span class="op">=</span> users.<span class="kw">id</span>;</a></code>

A couple of things of note here: the two CASE statements are meant to fulfill strictly illustrative features of the report: if the maximum was exceeded, show that only the maximum will be reimbursed, and put a star next to that value. These two functions are independent of the SELECT and could be isolated into their own PSQL functions, and also note that they are independent of the values on which they operate: they do not need to be recompiled, or even touched, if the company decides to change the maximum values of a reimbursement.

Also note that this is a VIEW, that is, a separate function that only runs if we trigger it; this table does not exist, and the contents are only generated on-demand.

Want to know the expenses for a given user?

<code class="sourceCode sql"><a title="1" class="sourceLine" id="cb3-1"><span class="kw">SELECT</span> username, event, expense_type, expense_category, amount, maximum,</a>
<a title="2" class="sourceLine" id="cb3-2">    reimbursed, <span class="kw">over</span></a>
<a title="3" class="sourceLine" id="cb3-3"><span class="kw">FROM</span> expense_report</a>
<a title="4" class="sourceLine" id="cb3-4"><span class="kw">WHERE</span> user_id <span class="op">=</span> <span class="dv">1</span></a>
<a title="5" class="sourceLine" id="cb3-5"><span class="kw">ORDER</span> <span class="kw">BY</span> event;</a></code>

And then there’s that expense summary:

<code class="sourceCode sql"><a title="1" class="sourceLine" id="cb4-1"><span class="kw">WITH</span> expense_summary <span class="kw">AS</span></a>
<a title="2" class="sourceLine" id="cb4-2">(</a>
<a title="3" class="sourceLine" id="cb4-3">  <span class="kw">SELECT</span> user_id, username, expense_category, </a>
<a title="4" class="sourceLine" id="cb4-4">    <span class="fu">SUM</span>(amount) <span class="kw">AS</span> total_amount,</a>
<a title="5" class="sourceLine" id="cb4-5">    <span class="fu">SUM</span>(reimbursed) <span class="kw">AS</span> total_reimbursed</a>
<a title="6" class="sourceLine" id="cb4-6">  <span class="kw">FROM</span></a>
<a title="7" class="sourceLine" id="cb4-7">    expense_report</a>
<a title="8" class="sourceLine" id="cb4-8">  <span class="kw">GROUP</span> <span class="kw">BY</span></a>
<a title="9" class="sourceLine" id="cb4-9">    user_id, username, expense_category)</a>
<a title="10" class="sourceLine" id="cb4-10"><span class="kw">SELECT</span></a>
<a title="11" class="sourceLine" id="cb4-11">  username, expense_category, total_amount, total_reimbursed</a>
<a title="12" class="sourceLine" id="cb4-12"><span class="kw">FROM</span></a>
<a title="13" class="sourceLine" id="cb4-13">  expense_summary </a>
<a title="14" class="sourceLine" id="cb4-14"><span class="kw">WHERE</span></a>
<a title="15" class="sourceLine" id="cb4-15"> user_id <span class="op">=</span> <span class="dv">1</span> <span class="kw">AND</span></a>
<a title="16" class="sourceLine" id="cb4-16"> total_reimbursed <span class="op">></span> <span class="st">'$0.0'</span>;</a></code>

And that’s the whole thing. Four statements describing the shape of objects, and three functions describing how those objects should be processed to make it easy for humans to understand them. That’s all that matters.

A completely runnable version of this code, for Postgres, is available at Github. And the output is pretty!

<code> username  |        event        | expense_type | expense_category | amount  | maximum | reimbursed | over 
-----------+---------------------+--------------+------------------+---------+---------+------------+------
 Uncle Bob | 2020-01-08 04:05:06 | Dinner       | Dining           |  $45.00 |  $50.00 |     $45.00 |  
 Uncle Bob | 2020-01-09 09:05:06 | Breakfast    | Dining           |  $12.00 |  $10.00 |     $10.00 | *
 Uncle Bob | 2020-01-09 16:05:06 | Air Travel   | Travel           | $250.00 |         |    $250.00 |  
 Uncle Bob | 2020-01-09 19:05:06 | Taxi         | Travel           |  $27.00 |         |     $27.00 |  
 Uncle Bob | 2020-01-09 21:05:06 | Dinner       | Dining           |  $53.00 |  $50.00 |     $50.00 | *
 Uncle Bob | 2020-01-09 22:05:06 | Other        | Other            | $127.00 |   $0.00 |      $0.00 | *</code>

I’ve met all of Bob’s requirements: every object has a single responsibility, and the messages it takes reflect that reality. Every object is OPEN for extension, but CLOSED for modifiability. We’ve avoided subtypes, going for composition instead. Our interfaces are appropriately segregated, and we are completely dependent upon the interfaces here, not the implementations.

We don’t even know what the implementations are. And we don’t care.

So we’re SOLID, as far as SOLID goes. The ‘D’ in SOLID is a deceit: interfaces are language-specific, and to claim that C++, or C#, or Java, or whatever languages, has “more pure interfaces” than SQL is to buy into that deceit. My UML is the only truly abstract description of the system, and SQL implements the full interface described in the UML because, again, SQL is a programming language and not a storage mechanism.

Use it as such.