[aswerty]

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.

[shkkmo]

This is the kind of anti-pattern that can work on toy or small projects but doesn't scale well to larger projects or groups.

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

You should use parameters either way.

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

Code is easier to document well than a SQL query

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

Query builders will give you a query you can do the same thing with.

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

This seems like a design/organization choice that is separate from whether those files are query or code.

> 5. Super readable and editable.

Doesn't scale as a project grows, you end up with massive unwieldy queries or a bunch of duplicated code across a bunch of files.

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

It is pretty much the same with a query builder, in either case the 'execute' is calling a library where all the actual stuff happens.

If you know your project is gonna stay small with simple queries and your scope won't creep, raw SQL files might the right choice, but they will create technical debt as the project grows. It's worth the time in the long run to get comfortable with a query builder.

[mattrighetti]

Thanks for summing this up! I'm also in the thousands of rows space at the moment and that's probably why I've fallen in the query planning trap that many pointed out.

[lelanthran]

That's basically what I did. No problems on even complex queries when I can use either CTEs or stored procedures if a single statement is sufficient.