public inbox for [email protected]  

help / color / mirror / Atom feed

From: Dominique Devienne <[email protected]>
To: Simon Connah <[email protected]>
Cc: [email protected]
Subject: Re: Stored procedures or raw queries
Date: Wed, 6 Aug 2025 15:42:37 +0200
Message-ID: <CAFCRh-8PjVRFQQScR6zKi82cBhzRcOSbkCX3Grj7o_eqf18AGw@mail.gmail.com> (raw)
In-Reply-To: <CAAxq4K1ekV9d3RA-qw2qS1cjBbOhF+4Vhf4BO_YPF0zKBeWH8w@mail.gmail.com>
References: <CAAxq4K1ekV9d3RA-qw2qS1cjBbOhF+4Vhf4BO_YPF0zKBeWH8w@mail.gmail.com>

On Wed, Aug 6, 2025 at 2:04 PM Simon Connah <[email protected]> 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

view thread (2+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Stored procedures or raw queries
  In-Reply-To: <CAFCRh-8PjVRFQQScR6zKi82cBhzRcOSbkCX3Grj7o_eqf18AGw@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox