Re: Stored procedures or raw queries

On Wed, Aug 6, 2025 at 2:04 PM Simon Connah <simon(at)connah(dot)dev> wrote:
> My main question is whether I should use stored procedures / functions
> or whether I should embed raw SQL queries in my backend? I understand
> that procedures are faster as it cuts down on the round trip speed and
> the database can optimise it better.
>
> On the other hand raw SQL is much easier to manage as you just change
> the query in your bankend code without having to apply changes to the
> database at deployment time of your backend.

That depends. Our backend is configured on the fly using code and the
test's fixture, since our schemas are dynamically generated from
higher-level logical constructs. Even if you use .sql files, you can
run those yourself at test time. Creating/dropping a DB is fast, it's
just a folder after all. In this manner, client-side vs server-side
stored-proc for your code matters much less, as both are "dynamic" any
time you run. And FWIW, we started having tons of SQL in the
client-code, and are migrating to server-side for some of it, but for
privilege escalation via SECURITY DEFINER reasons (because it's a
2-tier system), which doesn't sound like it applies to your use case.
Sprinkling RAISE NOTICE (or similar) in the server-side code helps
with debugging it, when it grows more complex, be sure to install a
notice-handler to get them client-side. But if you want to keep things
simple, sure, keep the SQL client-side. Another use-case for
server-side is to cut down on round-trips, which matters to us, since
2-tier, and the client-side can be "far" away from the server, but in
a web-app scenario, that's unlikely, so again, doesn't apply to you
I'm guessing. FWIW. --DD