The Role of a Database

I have worked on a number of what I would call database-centric applications. A kind way to put this is that these are applications that take full advantage of the features of their chosen DBMS to implement as much as possible of the system. The goal of this is to maintain a monolithic system as long as possible because one system means fewer boundaries. This means fewer degrees of freedom and fewer opportunities for breakage. It also means less integration and change process overhead.

The premise can be attractive, when you have a homogeneous and overstretched workforce. We need something to happen every 5 minutes, but we don't have anyone who knows how to write a console app or a Windows service or a Linux daemon. Fortunately SQL Server has a job system for running periodic T-SQL tasks, so no need to learn anything or hire anyone.

As someone who is comfortable writing console apps and Windows services, I am decidedly uncomfortable writing periodic tasks as SQL jobs unless the goal is solely to do database-y things like project normalized data into a reporting schema or stage data for an ETL pickup. I said as much to a coworker not too long ago and he challenged me on why I wanted nothing but data in the database, when the tools were capable of so much more. Schema can model relationships, constraints, and invariants. Tasks can be scheduled. Stored procedures can encapsulate implementation details. Triggers can be used for eventing.

I had to think a bit in order to put it in terms that weren't dogmatic. I had to consider why I dislike programming inside the database. Here are some of my reasons:

  • The languages and libraries are spartan and hard to work with.
  • While not inevitable, databases are commonly viewed as monolithic from the inside. Subsystem design and responsibility segregation have huge upsides, but are exceedingly rare inside the bubble.
  • Testing and deployment tooling is clunky and slow where it exists at all.
  • External controls are generally unavailable. Everything is an admin interaction, or occasionally an exceedingly clunky script in a proprietary language.

I think you'd be hard-pressed to find someone comfortable both in and out of the database who disagrees with most of these, or who couldn't probably add a bullet of their own to the list.

What this all adds up to is that database-centric processes and applications are harder to build, harder to change, and harder to migrate, than the equivalents built outside the database. This is especially true if the change being considered is to migrate logic out of the database engine. Whereas if you started off building outside the database, the biggest hurdle to moving something into the database, say for performance reasons, is whether the crucial features exist at all inside the database bubble.

The universal constant of programming is that change happens. If the tools and the skills are available, the tools that are better supportive of change should be preferred. And that is why I prefer my databases not to multi-task. I want them to store data. I want them store it in ways that are efficient to update and query in necessary ways. And then I want them to get out of the way.