4 ms·

I've found that the problem with not using an ORM is that you can end up writing a lot of repetitive CRUD queries. Every model ends up with the same basic read,

I've found that the problem with not using an ORM is that you can end up writing a lot of repetitive CRUD queries. Every model ends up with the same basic read, insert, update, etc. function, but because you're writing untested, un-typechecked SQL queries, they still need to be carefully reviewed, tested, etc. You can write some helper functions to abstract over the repetitive parts, but then you start creeping into home-grown ORM territory, which is much worse than using a standard battle-tested ORM. If I'm using a ORM, I can be pretty confident that the method to read my model using an ID is going to work without fuss. For a small service with just a few basic queries, skipping the ORM and going straight to SQL is pretty nice. And there are situations where you need custom SQL, like complex reports or dashboards. But for typical CRUD in a team setting, I've found that ORMs can be worth the overhead.

Joker_vD 4y ago
Well, even when you use ORM, you still need to write tests to check that you use it correctly: e.g., you didn't omit any needed fields, excluded all unneeded fields, etc.
- KyeRussell 4y ago
  Are you arguing against this comment?
goodoldneon 4y ago
Plus some ORMs have nice schema migration tools. Engineers should still verify what the auto-generated migration is doing, but that's much better than writing the migration by hand
deltarholamda 4y ago
So much of workaday SQL stuff is handled nicely by ORMs it's hard to pass on the convenience. I've found that using the ORM in conjunction with stored procedures works well. For the basic stuff, the ORM handles it well enough. As soon as the ORM request starts to look like spaghetti, it's time to think about making a stored procedure and using it. Years ago I also went down the road of "use stored procedures for everything," which also works, but you have to get deep into the gnarly SQL weeds. And, if DB portability is one of your goals, you more or less have to give up on your dreams. It makes a lot of sense if your project has many fingers in the pie and you have access to a talented-if-grumpy DBA (beard optional). I liked it for the small project I did, but I will admit that it was more work than was optimal.
- Izkata 4y ago
  Or views. Django at least lets you point a model at a view and say "managed = False", then just keep using the ORM for SELECT queries.
- bb88 4y ago
  At a deeper level ORMs exist because SQL isn't what people want most of the time. I think it's ironic that with databases and hardware description languages, we have been stuck in the 1990s with just one or two selections. In regular software development we have maybe 500 languages that large systems have been built from. ORM's are language abstractions that compiles down to SQL. With HDL's we have VHDL and Verilog. The other HDL's that are built on Python/Scala compile down to VHDL and Verilog, just like the ORM's compile down to SQL.
  - deltarholamda 4y ago
    >At a deeper level ORMs exist because SQL isn't what people want most of the time. Definitely true. ORMs allow an on-rails experience for, I dunno, 80%? 85%? of what people want a DB for. The RDBMS is mostly treated as a durable storage system. The real magic of an RDBMS only comes through when you get really, really complicated, and then you need SQL to manage these complex relationships. ORMs try to make that easier, but beyond a certain level it isn't less complex, just differently complex. The ORM written in your preferred language means less context switching during development. Great idea, and a great tool.
    - bb88 4y ago
      I think SQL is good at what it was meant to do back in the 1980s. Kind of the same way C was for systems programming, and is still good for many things today. In those two instances I mentioned, there's this weird ideology usually in the form of "just learn SQL/Verilog/thing here" -- while ignoring basic inefficiencies of those languages. No one bothers to say "Just learn C and all your problems will be solved" because I think everyone understands all the inefficiencies that C has. Though there are other proponents of other languages/frameworks that take this tack.
ac130kz 4y ago
Try out Django Rest Framework's generic views, it's such a beauty for making complete CRUDs with a few lines.
valenterry 4y ago
No. Classical fallacy: not using an ORM does NOT mean writing raw SQL strings. You can still use a library that helps will all the SQL specifics and even checks your schema for you at compile-time and help you abstract over common logic/operations.
- flashgordon 4y ago
  This. In golang gorm has been nicely covering this area. It still feels like sql so you don't start abusing it as a nosql store but has good abstractions to apply common operations across several models instead of depending on too much magic!
- birdstheword5 4y ago
  Hello! I've been looking for a library like this for C# and another for Python - I'm not sure which terms to Google to find them!
  - yellowapple 4y ago
    For C# that's basically LINQ, no?
    - birdstheword5 4y ago
      No, usually linq is used in combination with an OEM (e.g. entity Framework) to avoid writing raw sql. From the parent comment, I thought he meant something more like sqlx for rust
  - nicwolff 4y ago
    For Python, it's SQLAlchemy Core, the SQL-generation part of the SQLAlchemy ORM. https://docs.sqlalchemy.org/en/20/core/ https://docs.sqlalchemy.org/en/20/core/
  - threid 4y ago
    Have a look at Dapper: https://github.com/DapperLib/Dapper https://github.com/DapperLib/Dapper

Joker_vD 4y ago

Well, even when you use ORM, you still need to write tests to check that you use it correctly: e.g., you didn't omit any needed fields, excluded all unneeded fields, etc.

KyeRussell 4y ago
Are you arguing against this comment?

goodoldneon 4y ago

Plus some ORMs have nice schema migration tools. Engineers should still verify what the auto-generated migration is doing, but that's much better than writing the migration by hand

deltarholamda 4y ago

So much of workaday SQL stuff is handled nicely by ORMs it's hard to pass on the convenience. I've found that using the ORM in conjunction with stored procedures works well. For the basic stuff, the ORM handles it well enough. As soon as the ORM request starts to look like spaghetti, it's time to think about making a stored procedure and using it. Years ago I also went down the road of "use stored procedures for everything," which also works, but you have to get deep into the gnarly SQL weeds. And, if DB portability is one of your goals, you more or less have to give up on your dreams. It makes a lot of sense if your project has many fingers in the pie and you have access to a talented-if-grumpy DBA (beard optional). I liked it for the small project I did, but I will admit that it was more work than was optimal.

Izkata 4y ago
Or views. Django at least lets you point a model at a view and say "managed = False", then just keep using the ORM for SELECT queries.
bb88 4y ago
At a deeper level ORMs exist because SQL isn't what people want most of the time. I think it's ironic that with databases and hardware description languages, we have been stuck in the 1990s with just one or two selections. In regular software development we have maybe 500 languages that large systems have been built from. ORM's are language abstractions that compiles down to SQL. With HDL's we have VHDL and Verilog. The other HDL's that are built on Python/Scala compile down to VHDL and Verilog, just like the ORM's compile down to SQL.
- deltarholamda 4y ago
  >At a deeper level ORMs exist because SQL isn't what people want most of the time. Definitely true. ORMs allow an on-rails experience for, I dunno, 80%? 85%? of what people want a DB for. The RDBMS is mostly treated as a durable storage system. The real magic of an RDBMS only comes through when you get really, really complicated, and then you need SQL to manage these complex relationships. ORMs try to make that easier, but beyond a certain level it isn't less complex, just differently complex. The ORM written in your preferred language means less context switching during development. Great idea, and a great tool.
  - bb88 4y ago
    I think SQL is good at what it was meant to do back in the 1980s. Kind of the same way C was for systems programming, and is still good for many things today. In those two instances I mentioned, there's this weird ideology usually in the form of "just learn SQL/Verilog/thing here" -- while ignoring basic inefficiencies of those languages. No one bothers to say "Just learn C and all your problems will be solved" because I think everyone understands all the inefficiencies that C has. Though there are other proponents of other languages/frameworks that take this tack.

ac130kz 4y ago

Try out Django Rest Framework's generic views, it's such a beauty for making complete CRUDs with a few lines.

valenterry 4y ago

No. Classical fallacy: not using an ORM does NOT mean writing raw SQL strings. You can still use a library that helps will all the SQL specifics and even checks your schema for you at compile-time and help you abstract over common logic/operations.

flashgordon 4y ago
This. In golang gorm has been nicely covering this area. It still feels like sql so you don't start abusing it as a nosql store but has good abstractions to apply common operations across several models instead of depending on too much magic!
birdstheword5 4y ago
Hello! I've been looking for a library like this for C# and another for Python - I'm not sure which terms to Google to find them!
- yellowapple 4y ago
  For C# that's basically LINQ, no?
  - birdstheword5 4y ago
    No, usually linq is used in combination with an OEM (e.g. entity Framework) to avoid writing raw sql. From the parent comment, I thought he meant something more like sqlx for rust
- nicwolff 4y ago
  For Python, it's SQLAlchemy Core, the SQL-generation part of the SQLAlchemy ORM. https://docs.sqlalchemy.org/en/20/core/ https://docs.sqlalchemy.org/en/20/core/
- threid 4y ago
  Have a look at Dapper: https://github.com/DapperLib/Dapper https://github.com/DapperLib/Dapper