On Wed, Aug 6, 2025 at 2:04 PM Simon Connah <si...@connah.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
