4 ms·

Use an object-relational mapper and leave the boring stuff to a library.

tehlike 4y ago
Sadly the choice of sql tends to make underlying framework very complicated and sometimes incapable. Some things got easier to express with SQL after Json_agg in postgres became a thing (now you can do multi-depth relations without blowing up the result table with Cartesian product) but it's still massive pain in the ass and Json agg has performance overhead too.
- smitty1e 4y ago
  Especially for reporting, I'm just as happy to write a serious piece of SQL and execute it directly. This is the joy of SQLAlchemy: great features when you want to use them; escape hatches when you don't.
  - tehlike 4y ago
    Right, most ORM tools give an escape hatch to execute raw SQL queries. However, even though SQL is turing complete, it's not a great language for retrieval imho. It's probably an artifact of the underlying relational models (and the relational output it has to generate) but still.
    - smitty1e 4y ago
      I was all set to disagree with you, but a bit of googling yields => https://localcoder.org/is-sql-or-even-tsql-turing-complete https://localcoder.org/is-sql-or-even-tsql-turing-complete
      - tehlike 4y ago
        It could have been wrong, but main argument was whether SQL was a good language for retrieval or not, and for me that's a no. It's probably subjective though :)
__MatrixMan__ 4y ago
I hate ORMs. After days of fumbling in the dark you finally isolate the problematic query, post it like "guys I found it," and still nobody speaks up. So you hack the ORM to attach tracebacks as comments to each query, retest everything, and finally git-blame your way to somebody who owns that bit of code and they're always like "wait, the ORM did WHAT?" I'd rather grep code for SQL any day.
- jdmichal 4y ago
  That's why my favorite ORM in Java is MyBatis. It doesn't do SQL generation; it only maps objects to SQL parameters and results to objects.
  - pdimitar 4y ago
    That's no longer an ORM. These are called data mappers.

Sadly the choice of sql tends to make underlying framework very complicated and sometimes incapable. Some things got easier to express with SQL after Json_agg in postgres became a thing (now you can do multi-depth relations without blowing up the result table with Cartesian product) but it's still massive pain in the ass and Json agg has performance overhead too.

smitty1e 4y ago
Especially for reporting, I'm just as happy to write a serious piece of SQL and execute it directly. This is the joy of SQLAlchemy: great features when you want to use them; escape hatches when you don't.
- tehlike 4y ago
  Right, most ORM tools give an escape hatch to execute raw SQL queries. However, even though SQL is turing complete, it's not a great language for retrieval imho. It's probably an artifact of the underlying relational models (and the relational output it has to generate) but still.
  - smitty1e 4y ago
    I was all set to disagree with you, but a bit of googling yields => https://localcoder.org/is-sql-or-even-tsql-turing-complete https://localcoder.org/is-sql-or-even-tsql-turing-complete
    - tehlike 4y ago
      It could have been wrong, but main argument was whether SQL was a good language for retrieval or not, and for me that's a no. It's probably subjective though :)

__MatrixMan__ 4y ago

I hate ORMs. After days of fumbling in the dark you finally isolate the problematic query, post it like "guys I found it," and still nobody speaks up. So you hack the ORM to attach tracebacks as comments to each query, retest everything, and finally git-blame your way to somebody who owns that bit of code and they're always like "wait, the ORM did WHAT?" I'd rather grep code for SQL any day.

jdmichal 4y ago
That's why my favorite ORM in Java is MyBatis. It doesn't do SQL generation; it only maps objects to SQL parameters and results to objects.
- pdimitar 4y ago
  That's no longer an ORM. These are called data mappers.