I see a lot of push back against this approach. And since it is something I've been experimenting with recently, this is pretty interesting stuff. Clearly it has issues with query planning getting messed up, which is not something I had been aware of since my DB size I've been experimenting with is still only in the 10s of thousands of rows. But...

Using raw SQL file addresses:

  1. Very difficult for devs to expose SQL injection vulnerabilities because you need to use parameters.

  2. Having all available filtering dimensions on a query makes it very clear what the type of filtering is for that particular query.

  3. Easy debugging where you can just throw your query into an SQL client and play around with the parameters.

  4. Very clear what the total query footprint of you application is (e.g. files all neatly listed in a dir).

  5. Super readable and editable.

  6. Code for running the SQL is pretty much: here is my query, here are my params, execute.

  7. Etc?

So the amount of good you can get our of this approach is very high IMO.

So an open question to anybody who is more familiar with DBs (and postgres in particular) than myself. Is there a reliable way to address the issue with this approach to querying that you all are flagging as problematic here. Because beyond the query planning issues, raw SQL files (with no building/templating) just seems to me like such a better approach to developing a db access layer.