Application and Database DesignLesson 1
Where Business Logic Lives: Procs vs ORM vs App Code
14 minutes
One of the most consequential architecture decisions in any database-backed system is where the business rules live. In the stored procedures of the database? In an ORM model layer? In hand-written application service code? The three camps each have decades of evidence behind them and the choice has long-term consequences for testing, deployment, performance and team structure. This lesson is about making the call deliberately.
Why this matters
Teams that drift into a position rather than choose one end up paying the costs of all three approaches without getting the benefits of any. Knowing the trade-offs lets you decide which camp your system belongs in and stick with it consistently. This is where senior architecture decisions get made.
The three camps
1
Logic in stored procedures
The application is thin; the database holds the business rules. INSERT and UPDATE go through procedures that enforce validation, cascading and authorisation. Every consumer (web app, mobile app, ETL job, ad-hoc script) calls the same procedures and gets the same behaviour.
2
Logic in application code
The database is mostly a relational store. Business rules live in application services, written in C# / Java / Python / etc. The database does referential integrity (FKs) and not much else. ORMs translate between objects and tables.
3
Hybrid (the actual default in most production systems)
Some logic in procedures (high-throughput hot paths, safety-critical operations), most logic in application code (typical CRUD and business workflows). Picking which is which is the architecture work.
The stored-procedure camp
Why it winsConsistent enforcement. Every caller goes through the same procedure; the rule is enforced once, regardless of who is writing the client. Network efficiency. One round trip per business operation regardless of how many statements run inside. Permission isolation. Grant EXECUTE on procedures; deny direct table access. The procedure is the contract. Performance. Cached query plans, lower compilation cost, set-based operations close to the data.
Where it hurtsVersioning and testing. Stored procedures live in the database, not in source control by default. Tooling (sqlproj, Flyway, Liquibase) helps but adds friction. Refactoring is slow. Renaming a column may touch dozens of procedures. Talent pool. Strong T-SQL developers are harder to hire than strong C# or Python developers. Tied to the engine. Stored-procedure-heavy systems are difficult to migrate between SQL Server, PostgreSQL and Oracle.
When to chooseHigh-throughput OLTP where round-trip cost matters. Systems with many heterogeneous clients (multiple apps, multiple integrations). Industries with strong audit requirements where 'the database is the contract' is a compliance answer. SA banks, insurers, and large corporates often run this way.
The application-code camp
Why it winsStandard developer workflow. Business rules live next to the code that calls them, in the same language, with the same version control, IDE, testing and CI/CD as everything else. Portability. Less coupled to a specific database engine. Migration between engines is feasible. Testability. Unit tests against pure functions are easier than tests that need a database. Talent supply. Application developers are abundant; the rules look like ordinary code.
Where it hurtsConsistent enforcement is hard. Two different services hitting the same table may each have their own version of the rule, drifting apart over time. Network chatter. A business operation often becomes 5-20 separate SQL statements, each with its own round trip. Permissions are coarse. The application typically has full table access; the rules are enforced before the database is hit. A bypassed application path bypasses the rules.
When to chooseGreenfield web applications. Microservice architectures where each service owns its own data. SaaS products that prioritise developer velocity. Cloud-native and serverless designs. The default for most modern teams.
The hybrid (the reality)
Most production systems land in the middle. The pragmatic patterns:
1
Procedures for hot paths and safety-critical operations
Order placement (with stock check + transaction). Payment processing. KYC validation. Any operation where consistency matters more than developer convenience.
2
Application code for everything else
Typical CRUD. Reports. User-facing workflows that change frequently. Anything where 'I want to test this in five seconds' is more important than the database enforcing the rule.
3
Database constraints as the safety net
Foreign keys, CHECK constraints, UNIQUE indexes and triggers catch the cases where application code makes mistakes. The first line is the application; the database catches errors when the first line fails.
4
Read-only views as the application contract
Application code reads via views, writes via either direct DML or procedures. Views can hide schema changes; the application contract is stable.
A practical decision framework
When you are deciding where a new rule belongs:
| Question | Lean toward stored procedure | Lean toward application code |
|---|---|---|
| How many consumers will write to this table? | More than one application; ETL jobs; integrations | Just this one service |
| How critical is consistency? | Money, regulatory data, audit-bound | User preferences, content, ephemeral state |
| How often does the rule change? | Rarely; stable business invariant | Frequently; product-driven iteration |
| What is the team strongest at? | T-SQL or PL/SQL fluent | Application language (C#, Python, JS) |
| How portable does it need to be? | Engine is fixed for the next decade | May want to migrate engines or change databases |
The cardinal rule
Pick a model and apply it consistently
The worst system is the one where some business rules are in stored procedures, some in triggers, some in application services and some in ORM model methods, with no consistent rule about which goes where. When a new engineer joins they cannot find where any specific rule lives. Pick the camp (or the hybrid pattern) and document it. Code review enforces it. New rules go in the right place. Over time the system stays understandable.
The SA enterprise reality
Most large SA enterprises (the banks, the insurers, the medical schemes, the government departments) run substantially in the stored-procedure camp. The reasons are historical (a lot of these systems were built when stored procedures were the standard pattern) and contractual (audit requirements, vendor lock-in around SQL Server or Oracle, integration with legacy systems that call procedures by name).
SA startups and modern SaaS companies lean strongly toward the application-code camp, often via ORMs like Entity Framework, Hibernate or Prisma. Greenfield cloud-native designs almost always start there.
The hybrid is where most teams end up after they have actually been in production for a few years. The dogma softens; the database earns some procedures; the application earns most of the logic.
Key Takeaways
- Three camps: procs, application code, hybrid. The hybrid is the most common production reality.
- Stored procedures win on consistency, network efficiency and permission isolation. Lose on testability, refactoring, talent supply and portability.
- Application code wins on developer velocity, testability and portability. Loses on cross-service consistency and round-trip cost.
- Pick the camp explicitly and document it. The worst system is the one where the rule for where rules live is "depends on who wrote it".
- Database constraints are the safety net regardless of camp. FKs, CHECKs and UNIQUEs catch what application code misses.