public inbox for [email protected]
help / color / mirror / Atom feedRe: Stored procedures or raw queries
2+ messages / 2 participants
[nested] [flat]
* Re: Stored procedures or raw queries
@ 2025-08-06 13:42 Dominique Devienne <[email protected]>
2025-08-06 13:54 ` Re: Stored procedures or raw queries Marco Torres <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Dominique Devienne @ 2025-08-06 13:42 UTC (permalink / raw)
To: Simon Connah <[email protected]>; +Cc: [email protected]
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Stored procedures or raw queries
2025-08-06 13:42 Re: Stored procedures or raw queries Dominique Devienne <[email protected]>
@ 2025-08-06 13:54 ` Marco Torres <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Marco Torres @ 2025-08-06 13:54 UTC (permalink / raw)
To: Dominique Devienne <[email protected]>; +Cc: [email protected]
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
>
>
>
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2025-08-06 13:54 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-08-06 13:42 Re: Stored procedures or raw queries Dominique Devienne <[email protected]>
2025-08-06 13:54 ` Marco Torres <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox