6 ms·

Hand-rolling SQL inside another programming language comes with some unpleasantness, like protecting against SQL injection and making sure the SQL is valid, esp

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.

monkeyelite 1y 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.
- ameliaquining 1y 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.
  - monkeyelite 1y ago
    That’s because the composition is supposed to be inside sql. Views, functions, etc. This is another reason why the ORM is a leaky abstraction - it hides all the best features from you.
    - ameliaquining 1y ago
      I suspect the biggest reason those aren't more popular is that they usually have to be stored as state in the database, which isn't what you want when developing an application. You want all of your query logic to be versioned with your application code.
      - monkeyelite 1y ago
        In most organizations a database is broader than any individual application - both in lifecycle and scope. So it makes sense that this state exists in a different way. I suspect it’s because people never learned to use them, but they did learn to use the ORM.
        ameliaquining 1y ago
        The textbooks all say that, but is it really true in practice? I suspect it's a bit of holdover conventional wisdom from an earlier era of software. Usually, when I've created a database for a particular app, it was never used by anything else besides that app (and manual queries, for which versioning is mostly not relevant). You might choose to have a set of views and functions that are versioned separately from your app, for the same reasons you might choose to set up a microservice. But as with microservices, it should be a deliberate decision that you make because you're getting something out of it that outweighs the complexity costs and version-skew headaches; it should not be the default way to do composition.
        baq 1y ago
        It’s absolutely true. Nobody should be building a microservice architecture for a product without a proven market fit, monolithic development is just faster at this stage, and when you get around to scaling it once you outgrow it (which should be much later than conventional microservice wisdom agrees) the database isn’t the bottleneck anyway and hence stays monolithic (don’t confuse with adding shards).
        monkeyelite 1y ago
  - branko_d 1y ago
    > Only for parameterization over scalar values. ADO.NET has full support for table-valued parameters.
    - ameliaquining 1y ago
      That's part of .NET Framework and therefore legacy, right? Do the database libraries from recent versions of .NET do this? In any case, it's just one framework; previous comment said "all major languages". And it's useful to be able to abstract and compose over expressions and predicates and such, not just data values, which this still doesn't help with.
      - branko_d 1y ago
        > That's part of .NET Framework and therefore legacy, right? Do the database libraries from recent versions of .NET do this? ADO.NET is available both in the legacy Windows-only .NET Framework and in the new cross-platform .NET (previously known as .NET Core). > In any case, it's just one framework; previous comment said "all major languages". Well, you are not implementing a piece of code in "all major languages" - you can pick the one that fits the problem best. > And it's useful to be able to abstract and compose over expressions and predicates and such, not just data values, which this still doesn't help with. You can do that via LINQ - there is even special query-like syntax built right into C# for that that looks like this: var companyNameQuery = from cust in nw.Customers where cust.City == "London" select cust.CompanyName; This does NOT load the entire table in memory just to filter on City. It actually transpiles to SQL which does the filtering on the server. But anything non-trivial is much better done in SQL proper, IMO. Most of the time, at least for OLTP, you'll be using static SQL - that is you will not need to change the text of the SQL query, just parameters. But dynamic SQL is a thing and can be very useful on occasion - which is string concatenation with all the problems that might bring.
- kaoD 1y 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-bf410349856c https://gajus.medium.com/stop-using-knex-js-and-earn-30-bf41...
  - esafak 1y ago
    That thin wrapper is a query builder.
    - kaoD 1y ago
      > antipattern (what we traditionally think of when we say query builders)
      - deleted 1y ago
        [deleted]
  - monkeyelite 1y ago
    I'm with you.
webstrand 1y 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 1y 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
  - webstrand 1y ago
    Doesn't this break the object-relation mapping the ORM would be trying to do? Since the views would be producing resultsets that do not map to one single object? Or do you just define a new object type with virtual relations?

monkeyelite 1y 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.

ameliaquining 1y 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.
- monkeyelite 1y ago
  That’s because the composition is supposed to be inside sql. Views, functions, etc. This is another reason why the ORM is a leaky abstraction - it hides all the best features from you.
  - ameliaquining 1y ago
    I suspect the biggest reason those aren't more popular is that they usually have to be stored as state in the database, which isn't what you want when developing an application. You want all of your query logic to be versioned with your application code.
    - monkeyelite 1y ago
      In most organizations a database is broader than any individual application - both in lifecycle and scope. So it makes sense that this state exists in a different way. I suspect it’s because people never learned to use them, but they did learn to use the ORM.
      - ameliaquining 1y ago
        The textbooks all say that, but is it really true in practice? I suspect it's a bit of holdover conventional wisdom from an earlier era of software. Usually, when I've created a database for a particular app, it was never used by anything else besides that app (and manual queries, for which versioning is mostly not relevant). You might choose to have a set of views and functions that are versioned separately from your app, for the same reasons you might choose to set up a microservice. But as with microservices, it should be a deliberate decision that you make because you're getting something out of it that outweighs the complexity costs and version-skew headaches; it should not be the default way to do composition.
        baq 1y ago
        It’s absolutely true. Nobody should be building a microservice architecture for a product without a proven market fit, monolithic development is just faster at this stage, and when you get around to scaling it once you outgrow it (which should be much later than conventional microservice wisdom agrees) the database isn’t the bottleneck anyway and hence stays monolithic (don’t confuse with adding shards).
        monkeyelite 1y ago
- branko_d 1y ago
  > Only for parameterization over scalar values. ADO.NET has full support for table-valued parameters.
  - ameliaquining 1y ago
    That's part of .NET Framework and therefore legacy, right? Do the database libraries from recent versions of .NET do this? In any case, it's just one framework; previous comment said "all major languages". And it's useful to be able to abstract and compose over expressions and predicates and such, not just data values, which this still doesn't help with.
    - branko_d 1y ago
      > That's part of .NET Framework and therefore legacy, right? Do the database libraries from recent versions of .NET do this? ADO.NET is available both in the legacy Windows-only .NET Framework and in the new cross-platform .NET (previously known as .NET Core). > In any case, it's just one framework; previous comment said "all major languages". Well, you are not implementing a piece of code in "all major languages" - you can pick the one that fits the problem best. > And it's useful to be able to abstract and compose over expressions and predicates and such, not just data values, which this still doesn't help with. You can do that via LINQ - there is even special query-like syntax built right into C# for that that looks like this: var companyNameQuery = from cust in nw.Customers where cust.City == "London" select cust.CompanyName; This does NOT load the entire table in memory just to filter on City. It actually transpiles to SQL which does the filtering on the server. But anything non-trivial is much better done in SQL proper, IMO. Most of the time, at least for OLTP, you'll be using static SQL - that is you will not need to change the text of the SQL query, just parameters. But dynamic SQL is a thing and can be very useful on occasion - which is string concatenation with all the problems that might bring.
kaoD 1y 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-bf410349856c https://gajus.medium.com/stop-using-knex-js-and-earn-30-bf41...
- esafak 1y ago
  That thin wrapper is a query builder.
  - kaoD 1y ago
    > antipattern (what we traditionally think of when we say query builders)
    - deleted 1y ago
      [deleted]
- monkeyelite 1y ago
  I'm with you.

webstrand 1y 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 1y 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
- webstrand 1y ago
  Doesn't this break the object-relation mapping the ORM would be trying to do? Since the views would be producing resultsets that do not map to one single object? Or do you just define a new object type with virtual relations?