Why I Stopped Reaching for Stored Procedures in App-Driven Code

I used to think stored procedures were a sign of seriousness. Mature engineering. The kind of thing you did when you actually cared about your database. Then I spent two days tracking down a bug in a procedure that had quietly broken a billing calculation for a print management client, and nobody noticed for six weeks because the error was swallowed and the output almost looked right.

That cured me.

What Stored Procedures Actually Offer

Let me be fair before I bury them. Stored procedures in Postgres are genuinely powerful. PL/pgSQL is a real language. You can write loops, raise exceptions, use transactions, call other functions. If you're processing millions of rows and the round-trip overhead between your app and the database is actually your bottleneck, pushing logic into the database can win you real performance.

They also make sense in certain multi-system environments — when several different applications, written in different languages by different teams, all need to share the same business logic and you want one canonical place for it to live. That's a legitimate architectural choice.

But that's not most of what I build. Most of what I build is a Laravel application talking to a Postgres database. One app. One team. Me, maybe a contractor, and a client who wants features shipped.

The Real Problem: Your App Becomes Half the System

Here's how it starts. You write a stored procedure for something genuinely complex — say, calculating tiered pricing or allocating inventory across locations. It works. It's fast. You feel clever.

Six months later, a junior dev (or future-you at 9pm) needs to follow the flow of an order being placed. They grep the codebase. They find the controller, the service class, the model. Everything looks fine. Then the numbers are wrong and it takes an hour to remember that the actual math lives in a function called calculate_order_total_v2 in the database, which was renamed from calculate_order_total at some point, and there's a comment in a migration from 2021 that says "updated logic" with no further detail.

This is the thing that kills you: your application's behavior is no longer fully represented in your application's code.

Git blame doesn't help you. Your IDE doesn't help you. Your test suite, if it's testing the app layer, might not be exercising the procedure correctly. The procedure has its own versioning problem — you're managing migrations to update database functions, and if you're not extremely disciplined, you end up with v2, v3, _old, _backup naming in production.

I've inherited codebases like this. They're miserable.

What I Do Instead

For application-driven logic, I keep everything in PHP. If I need a complex query, I write it as a query — with CTEs, window functions, lateral joins, whatever Postgres gives me. I get the expressive power of the database without the opacity of a procedure.

Here's a real pattern I reach for. Say I need to calculate outstanding balances for a set of accounts, taking into account payments, credits, and disputed charges. In Laravel with a raw query:

$balances = DB::select("
    WITH charges AS (
        SELECT
            account_id,
            SUM(amount) AS total_charged
        FROM invoices
        WHERE status NOT IN ('void', 'disputed')
        GROUP BY account_id
    ),
    payments AS (
        SELECT
            account_id,
            SUM(amount) AS total_paid
        FROM payments
        WHERE status = 'settled'
        GROUP BY account_id
    ),
    credits AS (
        SELECT
            account_id,
            SUM(amount) AS total_credited
        FROM account_credits
        WHERE expired_at IS NULL OR expired_at > NOW()
        GROUP BY account_id
    )
    SELECT
        a.id AS account_id,
        a.name,
        COALESCE(c.total_charged, 0)
            - COALESCE(p.total_paid, 0)
            - COALESCE(cr.total_credited, 0) AS balance_due
    FROM accounts a
    LEFT JOIN charges c ON c.account_id = a.id
    LEFT JOIN payments p ON p.account_id = a.id
    LEFT JOIN credits cr ON cr.account_id = a.id
    WHERE a.active = true
    ORDER BY balance_due DESC
", []);

This lives in a PHP class. It's in source control. It has a line number. I can put a comment above it explaining why the disputed charges are excluded. I can wrap it in a method called getOutstandingBalances() and write a unit test that seeds data and asserts on the output. When the business rule changes — and it always changes — I open a PHP file, make the edit, commit it, and the diff shows exactly what changed and when.

A stored procedure gives me none of that for free. I have to work hard to get even a fraction of it.

The Gotchas That Bit Me

Error handling is a black box. PL/pgSQL exceptions don't propagate the way PHP exceptions do. You can RAISE EXCEPTION inside a procedure, and depending on how it's called, you might get a PDO exception in PHP, or you might get nothing and a partial result. I had a healthcare client where a procedure was silently catching exceptions it shouldn't have — there was a bare EXCEPTION WHEN OTHERS THEN block left over from debugging that someone forgot to remove. Data was wrong. No logs anywhere in the app.

Testing is genuinely harder. I can run PHPUnit against a test database. I can use factories to build fixtures. Testing the interaction with a stored procedure means my PHP tests are now also testing database behavior, which is fine in theory but means your test environment must always have the procedure deployed and at the right version. I've had CI pipelines fail because migrations ran in the wrong order and a procedure depended on a function that didn't exist yet.

Deployments get complicated. When logic lives in app code, deploying a change means shipping new PHP files. When logic lives in the database, deploying a change means running a migration and shipping code changes, and the order matters. Roll back the app? Does the old code still work with the new procedure? Roll back the procedure? Did you write a down migration? Nobody writes the down migration.

Tooling doesn't expect it. Laravel Telescope, Debugbar, query logs — these show you the SQL that was executed. A call to a stored procedure shows up as SELECT * FROM my_procedure($1, $2). You see the call but not what happened inside. For debugging production issues, that's painful.

When I'd Actually Use a Stored Procedure

I'm not religious about this. There are situations where I'd still reach for one.

If I'm doing heavy ETL work — transforming and aggregating millions of rows that are already in the database and the result stays in the database — I'll write a procedure. The performance gains are real and the opacity is acceptable because it's a batch job, not application logic.

If I'm building a system where multiple external clients will connect directly to Postgres (some data warehouse setups, some legacy ERP integrations), a procedure as a stable API surface makes sense.

Triggers are a related category — I use those sparingly but I do use them, mostly for audit logging where I genuinely want the database to enforce the behavior regardless of what the application does. Even then, I keep trigger functions as thin as possible and document them aggressively.

But for the Laravel app that drives a business? The thing with controllers and jobs and queues and a UI? Logic belongs in PHP.

The Version Control Argument Wins Every Time

At the end of the day, the strongest argument against stored procedures in app-driven code is the simplest one: they don't live in your version control in any meaningful way.

Yes, you can put them in migrations. Some shops do. But a migration is a one-way script. You can't diff the current state of a procedure the way you can diff a PHP class. You can't grep your git history for when a specific calculation changed. The procedure in production is the truth, and if it drifts from what's in your repo — which it will, eventually, if enough people have database access — you're flying blind.

I integrated a fairly complex job costing system for a Seattle-area construction firm a few years back. The previous vendor had built the whole thing on stored procedures — probably 40 of them. The documentation was a Word doc from 2018. Three of the procedures were calling functions that had been dropped and recreated under different names, and the originals just happened to still exist in the database doing nothing. We spent a week just mapping what was actually running versus what anyone thought was running.

Keep your logic in your app. Write expressive SQL queries. Use CTEs. Use window functions. Lean on what Postgres is genuinely great at — as a query engine — and let your application layer do what it's great at: being readable, testable, and deployable like normal code.

Stored procedures are not clever. They're a liability you're taking on every time you write one, and the interest compounds.