← Back to context

Comment by t-writescode

3 days ago

Hand-rolling SQL inside another programming language comes with some unpleasantness, like protecting against SQL injection and making sure the SQL is valid, especially when hand-constructing the query based on input parameters: “sort ascending? Descending? Filter all but things in this group? etc.”

Parameter management in some languages are unpleasant, like how JDBC only has positional arguments; and any time you do string concat in a language, you start getting in danger of misformed SQL.

Ultra basic ORMs, like Exposed (Kotlin), are well-tested frameworks that do exactly what I want. Want a single value in the =? Or want it to be an “in”? Or what if it’s null? Handled. No special string management. Want parameters? Handled.

When I see pure ORM’d code, I can feel safe expecting it to be protected from injection and formatting issues. It’s reduced cognitive load and greater safety.

When I see raw string SQL management, I have to put another layer of care and attention to try and make sure (and maybe still fail) there’s no application-crashing mistakes in that part of code.

It’s kinda like working with typed and compiled code. Greater protection from error.

25 comments

t-writescode

Reply
webstrand  2 days ago

It sounds like you're describing a query builders which, unlike true ORMs, don't attempt to mask the object-relational boundary. They only help you build queries in a convenient way and sometimes type-safe way. Query builders are great.

ORMs are not query builders. The problem with ORMs is that they hide the query logic from you. It's not clear what's getting joined in, how its getting joined in, or if its actually 1 or N queries. The further you get from a straight query builder, too, the fewer SQL features you have access to, such as parameterized joins, CTEs, window functions, etc. Sometimes you can hack those into the ORM, but often you have to resort to string concat and build the parameterized query and arguments manually.

I've never used Exposed, but from what I can tell it's kind of a hybrid? the query builder parts look great, but I'd still be wary of the ORM parts.

I've never had a good experience debugging performance issues in ORM-generated queries. Maybe I haven't used the right ones, but the libraries I've used have gone out of their way to hide how the query is actually being executed and only have obtuse levers to tweak that execution. Sure you can see the raw logs of what queries the ORM executed, but you can't easily figure out why its chosen a particular strategy. Or you can't easily make it stop using a pathological join ordering.

  • prmph  2 days ago

    If one can update the underlying Db, I've developed an ORM pattern that I use in my projects that works very well.

    They keys is to encapsulate most of the (possibly complex) CRUD logic in Db functions (for retrieval these would be table-valued functions) and access these from the application side as virtual tables.

    I also have capable but easy to use filtering/sorting/paging operators and combinators in the ORM that are translated into SQL where/sort/limit clauses.

    Because the heavy lifting is already done by the db functions (which you have full control of to use whatever SQL you need), the pattern is actually quite powerful but easy to use.

    You can define virtual read only tables, several virtual tables that access the same actual table in different way, custom operators that transcend SQL, etc

  • andoando  2 days ago

    I HATE ORMs. I end up spending an hour or two trying to figure out why something isnt working for what should be a simple query.

    Theyre also seem quite restrictive to what raw sql can do.

monkeyelite  3 days ago

You’re arguing against a straw man. All major language sql libraries are not based on string manipulation and provide things like escaping, arguments, etc out of the box.

  • kaoD  2 days ago

    Query builders are still an antipattern (what we traditionally think of when we say query builders) because they are still a DSL that (1) you have to learn along with SQL and (2) never map cleanly to actual SQL, so you always have to resort to `.raw` calls when you need anything more complex than a simple SELECT+JOIN.

    Even for simple SELECTs, I lost count of how many times I had to enable runtime DEBUG logging in my query builders to get a query that I can copy-paste into my DB client for debugging, data exploring, etc. I should be able to copy-paste my query from code and run it in `psql` with minimal changes.

    Raw SQL is the way to go[0]. Period. You just need a thin wrapper on top of it that adds escaping, arguments, type-safety and other QoL.

    [0] https://gajus.medium.com/stop-using-knex-js-and-earn-30-bf41...

  • ameliaquining  2 days ago

    Only for parameterization over scalar values. If you want to do any kind of composition more sophisticated than that, you're either stitching together strings or using some kind of more heavyweight abstraction like an ORM.