Why I Keep Business Logic Out of SQL

Should I default to pushing logic into the database—using views, procedures, functions—or should I default to keeping logic inside the application code (including SQL statements)?

It has been a long-discussed topic, though less so in the last 15 years. It seems that common wisdom has largely settled.

TL;DR

I tend to keep as much of my code inside the application as possible and treat the DB as a dumb data store—with as little logic there as is reasonably possible.

Unexpressive SQL Language

SQL and its vendor dialects were designed to work with row-based data—many rows at a time—and the relations between them. That’s where the language shines. But applications that use a relational database also contain rich business logic. Is SQL a good candidate for implementing such logic?

Despite vendor-specific extensions that provide a type system (e.g. Oracle PL/SQL), I’d argue no. Modeling a class hierarchy or object composition in SQL is clumsy and verbose. SQL doesn’t do well when working on a single row at a time. You can do it, but with less expressiveness than in mainstream programming languages like TypeScript, Java, C#, Haskell, Python, or JavaScript.

So the limitation is SQL itself. It’s simply the wrong language for writing rich business logic. Try this example yourself as an exercise!

Deployment of Changes

Deploying application code is generally easier, though this is nuanced—it depends on your process. Modern CI/CD systems make application deployments and rollbacks almost a button click. Database deployments, however, usually face a higher bar:

• Require database access with elevated privileges
• Often need coordination with DBAs in corporate environments
• Are frequently subject to stricter security policies
• Have trickier rollbacks, especially when schema changes are involved (though this applies to apps too)

You may or may not work in a company with such restrictions, but they’re common.

Other Disadvantages

There are some “soft” downsides to keeping code inside the DB:

• Even greater vendor lock-in than normal
• Tooling exists, but the ecosystem is still smaller compared to application languages

Performance

Be mindful of the N+1 query problem when orchestrating DB access in the application code! The more you process in the database, the less likely you’ll run into it. In a fair comparison this is an argument for placing logic in the database. Still, I wouldn’t optimize preemptively before hitting actual bottlenecks.

Summary

All things considered, SQL as a language is my biggest reason for writing business logic in a “normal” programming language. This is why I usually recommend keeping as much code in the application as possible.