3 ms·

I'm not sure if ORM is a great example, as they tend to work for the vast majority of use cases. If you're not using an ORM, you're either working on something

I'm not sure if ORM is a great example, as they tend to work for the vast majority of use cases. If you're not using an ORM, you're either working on something where performance is critical, or you're making a mess.

abound 3y ago
I tend to either use just plain SQL [1] or sqlc [2] [3]. With sqlc, you write plain SQL and it generates the (in my case, Go) boilerplate bindings for you. I don't find these approaches to be a mess, but both linked projects are admittedly small. I've used a similar approach on (non-OSS) codebases with thousands of DB functions/SQL statements though. I don't disagree with you, ORMs can be a great time-saver for your average CRUD app, where the SQL queries tend to be straightforward SELECT/UPDATE/DELETEs on primary keys, assuming you don't mind the overhead of learning the DSL. But as your querying requirements get more complicated, you either have to: - Keep the DB queries simple and do the complicated stuff (joining, aggregating, etc) in the business logic, or - Learn how to express your complicated SQL in the ORM's DSL, or - Drop into the ORM's "raw" mode and write plain SQL The first option might be fine for non-performance-sensitive stuff, but otherwise isn't making good use of the database, and the latter two options reveal the SQL behind the curtains, at which point the abstraction of the ORM is more or less totally broken. [1] Raw SQL: https://github.com/bcspragu/stronk/blob/main/db/sqldb/sqldb.go https://github.com/bcspragu/stronk/blob/main/db/sqldb/sqldb.... [2] sqlc: https://github.com/bcspragu/logseq-sync/blob/main/db/sqlite/query.sql https://github.com/bcspragu/logseq-sync/blob/main/db/sqlite/... [3] https://sqlc.dev/ https://sqlc.dev/
- keltex 3y ago
  Well written ORMs let you express queries without the operations happening in the business logic. For example C# and Linq. You can express pretty much any query using the linq language and that's then converted into SQL automatically.
deleted 3y ago
[deleted]
WinstonSmith84 3y ago
Well, to me it is. I've been working on a relatively large projects based on Node (Typescript), various Lambdas, some doing server stuff only, other serving Client ... It's a pretty complex and large application overall and the whole architecture was really well designed from the ground up, except one thing: typeorm. It was fine at the beginning when doing simple things, but it got really tricky after. Then, I've had always very complex "migrations" to add sometimes just a field. And eventually I've had an issue I logged in 2019, and it's not been resolved yet. Well there is no solution due to the abstraction layers of typeorm, and I've solved (i.e. workarounded) my problem by executing a proper SQL query instead .. I mean there are 2k+ issues open https://github.com/typeorm/typeorm/issues/ https://github.com/typeorm/typeorm/issues/ - yes, not all of them are bugs and limitations, but if you filter the bugs, that's still more than 1000 ...
- BarryMilo 3y ago
  Okay fair point, you need a good ORM!
CuriouslyC 3y ago
There are a lot of queries that are just harder to represent in an ORM, or don't fit into the table -> line of business object mapping. ORMs are more a crutch and rails for people that don't know SQL well than a true improvement over sql.
seabrookmx 3y ago
Hard disagree. ORM's tend to cater to the lowest common denominator functionality of relational databases so if you want to for instance, make use of jsonb in postgres, you have to use weird workarounds or drop into SQL anyways. SQL is a high level, declarative language. You need to use good libraries that ensure queries can be safely composed and arguments passed, but you are not automatically "making a mess" if you're writing SQL unless you or your tooling is incompetent. Personally, I really like Dapper (C#) as it solves the "object mapping" and "query composition" cases in a performant and ergonomic way while letting me write the (exact) query I want.
- neonsunset 3y ago
  (EF Core has been allowing this for quite some time too with .FromSql method which uses string interpolation API to turn an interpolated string into a parametrized query behind the scenes mitigating injection risks. Before that there were FromSqlInterpolated and FromSqlRaw which served the same purposed)
  - seabrookmx 3y ago
    I've heard this and been meaning to try it out. Linq-to-SQL really bit me a decade ago and pushed me to Dapper but I have always liked the way EF did migrations.

abound 3y ago

I tend to either use just plain SQL [1] or sqlc [2] [3]. With sqlc, you write plain SQL and it generates the (in my case, Go) boilerplate bindings for you. I don't find these approaches to be a mess, but both linked projects are admittedly small. I've used a similar approach on (non-OSS) codebases with thousands of DB functions/SQL statements though. I don't disagree with you, ORMs can be a great time-saver for your average CRUD app, where the SQL queries tend to be straightforward SELECT/UPDATE/DELETEs on primary keys, assuming you don't mind the overhead of learning the DSL. But as your querying requirements get more complicated, you either have to: - Keep the DB queries simple and do the complicated stuff (joining, aggregating, etc) in the business logic, or - Learn how to express your complicated SQL in the ORM's DSL, or - Drop into the ORM's "raw" mode and write plain SQL The first option might be fine for non-performance-sensitive stuff, but otherwise isn't making good use of the database, and the latter two options reveal the SQL behind the curtains, at which point the abstraction of the ORM is more or less totally broken. [1] Raw SQL: https://github.com/bcspragu/stronk/blob/main/db/sqldb/sqldb.go https://github.com/bcspragu/stronk/blob/main/db/sqldb/sqldb.... [2] sqlc: https://github.com/bcspragu/logseq-sync/blob/main/db/sqlite/query.sql https://github.com/bcspragu/logseq-sync/blob/main/db/sqlite/... [3] https://sqlc.dev/ https://sqlc.dev/

keltex 3y ago
Well written ORMs let you express queries without the operations happening in the business logic. For example C# and Linq. You can express pretty much any query using the linq language and that's then converted into SQL automatically.

deleted 3y ago

[deleted]

WinstonSmith84 3y ago

Well, to me it is. I've been working on a relatively large projects based on Node (Typescript), various Lambdas, some doing server stuff only, other serving Client ... It's a pretty complex and large application overall and the whole architecture was really well designed from the ground up, except one thing: typeorm. It was fine at the beginning when doing simple things, but it got really tricky after. Then, I've had always very complex "migrations" to add sometimes just a field. And eventually I've had an issue I logged in 2019, and it's not been resolved yet. Well there is no solution due to the abstraction layers of typeorm, and I've solved (i.e. workarounded) my problem by executing a proper SQL query instead .. I mean there are 2k+ issues open https://github.com/typeorm/typeorm/issues/ https://github.com/typeorm/typeorm/issues/ - yes, not all of them are bugs and limitations, but if you filter the bugs, that's still more than 1000 ...

BarryMilo 3y ago
Okay fair point, you need a good ORM!

CuriouslyC 3y ago

There are a lot of queries that are just harder to represent in an ORM, or don't fit into the table -> line of business object mapping. ORMs are more a crutch and rails for people that don't know SQL well than a true improvement over sql.

seabrookmx 3y ago

Hard disagree. ORM's tend to cater to the lowest common denominator functionality of relational databases so if you want to for instance, make use of jsonb in postgres, you have to use weird workarounds or drop into SQL anyways. SQL is a high level, declarative language. You need to use good libraries that ensure queries can be safely composed and arguments passed, but you are not automatically "making a mess" if you're writing SQL unless you or your tooling is incompetent. Personally, I really like Dapper (C#) as it solves the "object mapping" and "query composition" cases in a performant and ergonomic way while letting me write the (exact) query I want.

neonsunset 3y ago
(EF Core has been allowing this for quite some time too with .FromSql method which uses string interpolation API to turn an interpolated string into a parametrized query behind the scenes mitigating injection risks. Before that there were FromSqlInterpolated and FromSqlRaw which served the same purposed)
- seabrookmx 3y ago
  I've heard this and been meaning to try it out. Linq-to-SQL really bit me a decade ago and pushed me to Dapper but I have always liked the way EF did migrations.