Business Logic in the Database: Trade-Offs

## Why this question is asked It is a judgement question with no universally right answer, and the interviewer is checking whether you can argue both sides rather than repeat a slogan ('logic belongs in the app' / 'the database is the only truth'). A good answer separates kinds of logic and gives a rule for each. ## The case for the database **Round trips and data volume.** Every statement from an application costs a network round trip plus parse and plan work. A loop that reads a row, decides, and writes it back can turn a one-second set-based statement into minutes of latency-bound work. Moving the loop server-side collapses the round trips and lets the engine work in sets. The stronger version of this argument is data reduction: if a rule needs to examine a million rows to produce one number, shipping a million rows to the application to compute it is pure waste. **Atomic multi-statement work.** A procedure runs inside the database's transaction, so a sequence of statements is naturally all-or-nothing without a client holding a transaction open across a network it does not control. Client-held transactions are how you get idle sessions pinning locks and blocking cleanup while an application waits on an external call. **One enforcement point.** This is the strongest argument and the most situational. If the schema has exactly one writer - your service - then the service is already a single enforcement point and the database adds nothing. If it has many writers (a legacy app, a new service, an ETL job, a data team with write access, a support script), then rules living in any one application are advisory. Logic in the database is the only thing every writer must pass through. **Latency-critical, data-heavy operations.** Some workloads - reconciliation, matching, bulk state transitions - are genuinely cheaper and simpler expressed close to the data. ## The case against **Testability.** Application code can be unit-tested in milliseconds with dependencies substituted. Procedure code cannot: you need a real engine, real schema, and real data setup. Tooling exists (SQL-native test frameworks, container-based integration tests), but the feedback loop is slower and the tests are heavier. Teams that put substantial logic in procedures and do not invest in this end up with untested business rules. **Versioning and deployment.** Procedures are state, replaced wholesale, not a diff applied to a file. Keeping the authoritative source in version control and deploying it through migrations is doable and mandatory, but it has sharp edges: rollback means re-applying the previous body (and if that body's contract changed, undoing data effects too); a rolling deploy has both old and new application versions talking to one procedure version, so any signature or semantic change must be backward compatible for the window; and there is no branch isolation - one database, one body. **Scaling economics.** The primary database is typically the least horizontally scalable tier: one writable node, replicas that cannot take write-path work, and a failover story you would rather not exercise. Application servers are stateless and multiply for money. Putting CPU-heavy business computation on the database spends the scarcest resource, and it does so in a shared way - one heavy procedure degrades latency for every other query on the instance. Where licensing is per core, that CPU is also literally the most expensive you own. **Lock-in.** Procedural dialects are mutually incompatible, so a large procedural estate is one of the heaviest anchors against changing engines - much heavier than portable SQL. **Tooling and developer experience.** Weaker debugging, profiling, static analysis, dependency management, and code review compared with a mainstream application language, plus a smaller hiring pool. **ORM friction.** ORMs assume they own the write path - dirty checking, optimistic locking, identity maps, cascades. Logic that mutates rows behind the ORM's back invalidates its in-memory state, and calling procedures usually means dropping to native queries and hand-mapping results, giving up much of the framework's value. ## The synthesis to offer Split logic by kind. **Declarative integrity** - keys, uniqueness, referential integrity, check constraints - always in the database; nothing else can enforce it under concurrency and multiple writers. **Data-reduction and set-based work** - filtering, aggregation, joins, bulk transitions - in the database, expressed as SQL, because that is what the engine is for. **Business policy and orchestration** - pricing rules, workflow, calls to other systems - in the application, where it is testable, versionable, and horizontally scalable. Then name the honest exception: with many uncontrolled writers, or a hard latency or data-volume constraint, moving specific rules into the database is the right call, and you accept the testing and deployment discipline that comes with it.