On: PostgREST – REST API from any PostgreSQL database

[abrookewood]

Some counter-points I've heard made: 1) Performance. Stored procedures are fast, meaning it will be longer before you need to scale out. 2) Security. If you only use stored procs, you're a lot less exposed to SQL injections etc.

I don't really have a firm opinion either way, but it's not as clear cut as you are making out.

[batou → abrookewood]

Counter-counter-points:

1) Stored procedures aren't that much faster than issuing plain SQL over the connection. The main performance bottlenecks in a RDBMS are cache and IOPS. Regardless of where you execute those, they are all inside that black box after the query is parsed. You also get the added pain of cached query plans which fuck up performance when the query optimiser makes assumptions about table statistics and stores them with a stored procedure. (SQL Server and Oracle at least are guilty of this).

2) The only place I've had SQL injection attacks in the last few years is where people have used dynamic SQL inside stored procedures (sp_executesql) and not escaped them properly. Outside of that, both ORM and proper parameter binding make this entirely irrelevant.

It's completely clear cut IMHO.

[untog → abrookewood]

Performance. Stored procedures are fast, meaning it will be longer before you need to scale out.

That was once the case, but every major DB now caches execution plans for commonly run queries.

[FLUX-YOU → abrookewood]

>If you only use stored procs, you're a lot less exposed to SQL injections etc.

How does that help you vs. prepared statements in any typical language?

I've seen SQL statements in SPs that are concatenated (|| in oracle) to varchar fields from a table and I thought that would be just as vulnerable?