public inbox for [email protected]  

help / color / mirror / Atom feed

From: Marco Torres <[email protected]>
To: Dominique Devienne <[email protected]>
Cc: [email protected]
Subject: Re: Stored procedures or raw queries
Date: Wed, 6 Aug 2025 07:54:33 -0600
Message-ID: <CAG2LZV7BnYbr=nTZCPdUp-UBknaqkd=Mn5BGr1zs0BYBvm1pPQ@mail.gmail.com> (raw)
In-Reply-To: <CAFCRh-8PjVRFQQScR6zKi82cBhzRcOSbkCX3Grj7o_eqf18AGw@mail.gmail.com>
References: <CAAxq4K1ekV9d3RA-qw2qS1cjBbOhF+4Vhf4BO_YPF0zKBeWH8w@mail.gmail.com>
	<CAFCRh-8PjVRFQQScR6zKi82cBhzRcOSbkCX3Grj7o_eqf18AGw@mail.gmail.com>

In my experience, starting with store procedures in a project might seem
overwhelming. Still, as time passes, and your deliverables output grows, it
becomes easier to maintain and improve your products.

It is just a different paradigm that allows you to focus on improving your
code everywhere. Remember, the database is often neglected until you start
facing problems.

Use indexes and partitions from the beginning. Archiving and replication
might sound unnecessary at the moment; however, dealing with their
implications in production is just a pain.


On Wed, Aug 6, 2025, 7:43 AM Dominique Devienne <[email protected]> wrote:

> 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)

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: <CAG2LZV7BnYbr=nTZCPdUp-UBknaqkd=Mn5BGr1zs0BYBvm1pPQ@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