3 ms·

> One of the things I would discard would be the use of an ORM library ... In my opinion, it is better to create some simple methods for each object that implem

by movedx 2y ago

> One of the things I would discard would be the use of an ORM library ... In my opinion, it is better to create some simple methods for each object that implement the CRUD operations and build the SQL statements directly. Have you done this for any complex system? I'd love to see you do this for the AzerothCore: it has 298 tables, 3,010,875 rows across those tables, and one table (quest_template) has 105 columns. Instead I've thrown SQLAlchemy in front of it and now I can query it without writing a single line of SQL. I think tools are tools, and using the right tool at the right time is an important skill I think you've yet to develop.

skydhash 2y ago
If you’re only doing CRUD, you can use any reputable query builder or ORM. But sometimes the best model for business logic and the database table differs, and the methods for persistence are Load, Save or Add, Remove instead. That’s when you want custom SQL where the ORM/query builder is not great. Laravel is great, but that because they have nicely designed escape hatches and their architecture is very modular.
- movedx 2y ago
  SQLAlchemy doesn’t get in the way of anything you might want to do. In fact, you can do a “textual” query and then have the response mapped to classes for you :-)
  - never_inline 2y ago
    You can do that in every ORM including the infamous hibernate.
Daril 2y ago
Yes, I understand your point of view, but in my experience these ORM libraries when you create a class or a structure and then the library build the SQL code behind the scenes can suffer from some relevant issues : 1. you have no control over the generated SQL and because it has to be generic and db agnostic, might not be the best option depending on the database you are currently using 2. when something doesn't work as expected, and it happens, they are difficult to debug (too many layers) and find the issue 3. they are extremely inefficient, because they have to dynamically build every time the code is run the corresponding SQL code : I'm sure most would implement some caching mechanism to prevent this , but in any case it's a waste of resources. This is just anecdotal, but I remember trying SQLAlchemy many years ago for a small Python program I was writing for a RaspberryPi 3 : it was extremely slow. So, I removed the library and used instead the native database binding for MariaDB instead, and the speed improved a lot. For PHP, the situation is the worst because there is no application server (they exist, but not very widely used), but the code is regenerated every time. This is the main problem in any large PHP project, such as Nextcloud. If they would adopt FrankenPHP or RoadRunner, they could improve the performance of the applications a lot.
- movedx 2y ago
  I too used to believe those were valid points not to use an ORM back in the day. That was easily 2013/2014. Since then I’ve never found an ORM that gets in the way letting my just run raw SQL. And not just run raw SQL as complex as I’d like: it’ll also still give you all the magic once the response comes back.
- KronisLV 2y ago
  > 1. you have no control over the generated SQL Depending on the tech in use, there's usually some sort of an escape hatch, such as writing your own native SQL queries that take advantage of the exact functionality you need, while letting you keep the 90% of the rest CRUD based on the automatically generated stuff. Plus, nothing is preventing you from putting complex querying in a DB view and then have a read only mapping for the ORM against that, giving you the best of both worlds - using the DB for what it's good at, keeping things relatively simple and yet powerful on the app side.
bdcravens 2y ago
Writing SQL against systems much larger than that used to be the norm. You are correct that "using the right tool at the right time" is important, and often, that right tool is SQL. Other times it's not. Unfortunately there are many developers who don't really know SQL, so every problem is ORM-shaped.
- 7bit 2y ago
  > used to be the norm. People also "used to" invest radioactive water and used radioactive cremes and toothpastes for health benefits in the 20's and 30's. So what's your point?
  - bdcravens 2y ago
    That any discussion around systems that uses some arbitrary size of tables/rows/etc is empirically disproven. Moreover, any exaggerated example of a bygone time is unrelated, as many SQL-driven systems still exist today. I work on one such system which is much larger than the example given, and not long ago, I increased performance of some ActiveRecord queries 1000x by simply rewriting them in SQL. (No hate against ActiveRecord, I use it regularly. It just takes a lot of discipline once you hit queries of a certain complexity.)
    - movedx 2y ago
      > ... and not long ago, I increased performance of some ActiveRecord queries 1000x by simply rewriting them in SQL. But it was ActiveRecord that got you there in the first place, as a business, and enabled you to even build anything quickly enough to meet market demand and therefore make money. Moving to a few raw SQL statements today to improve performance is called optimising and everyone in every industry does that... _after the fact_. No one should be (pre-)optimising from day 1. That's a good place to start with an ORM. Our industry is about balancing engineering knowledge with business knowledge and market forces: we have to accept that we can't write perfect code today otherwise you won't have a job tomorrow. You have to get up and running now and optimise later, which might look like replacing some parts of an ORM's job with an optimised SQL statement. (And again: no ORM is stopping you from running raw SQL. You can have both. It's foolish to throw out an entire ORM and everything it gives you because, "Remember when I optimised that one statement that one time?")

by movedx 2y ago

skydhash 2y ago

If you’re only doing CRUD, you can use any reputable query builder or ORM. But sometimes the best model for business logic and the database table differs, and the methods for persistence are Load, Save or Add, Remove instead. That’s when you want custom SQL where the ORM/query builder is not great. Laravel is great, but that because they have nicely designed escape hatches and their architecture is very modular.

movedx 2y ago
SQLAlchemy doesn’t get in the way of anything you might want to do. In fact, you can do a “textual” query and then have the response mapped to classes for you :-)
- never_inline 2y ago
  You can do that in every ORM including the infamous hibernate.

Daril 2y ago

Yes, I understand your point of view, but in my experience these ORM libraries when you create a class or a structure and then the library build the SQL code behind the scenes can suffer from some relevant issues : 1. you have no control over the generated SQL and because it has to be generic and db agnostic, might not be the best option depending on the database you are currently using 2. when something doesn't work as expected, and it happens, they are difficult to debug (too many layers) and find the issue 3. they are extremely inefficient, because they have to dynamically build every time the code is run the corresponding SQL code : I'm sure most would implement some caching mechanism to prevent this , but in any case it's a waste of resources. This is just anecdotal, but I remember trying SQLAlchemy many years ago for a small Python program I was writing for a RaspberryPi 3 : it was extremely slow. So, I removed the library and used instead the native database binding for MariaDB instead, and the speed improved a lot. For PHP, the situation is the worst because there is no application server (they exist, but not very widely used), but the code is regenerated every time. This is the main problem in any large PHP project, such as Nextcloud. If they would adopt FrankenPHP or RoadRunner, they could improve the performance of the applications a lot.

movedx 2y ago
I too used to believe those were valid points not to use an ORM back in the day. That was easily 2013/2014. Since then I’ve never found an ORM that gets in the way letting my just run raw SQL. And not just run raw SQL as complex as I’d like: it’ll also still give you all the magic once the response comes back.
KronisLV 2y ago
> 1. you have no control over the generated SQL Depending on the tech in use, there's usually some sort of an escape hatch, such as writing your own native SQL queries that take advantage of the exact functionality you need, while letting you keep the 90% of the rest CRUD based on the automatically generated stuff. Plus, nothing is preventing you from putting complex querying in a DB view and then have a read only mapping for the ORM against that, giving you the best of both worlds - using the DB for what it's good at, keeping things relatively simple and yet powerful on the app side.

bdcravens 2y ago

Writing SQL against systems much larger than that used to be the norm. You are correct that "using the right tool at the right time" is important, and often, that right tool is SQL. Other times it's not. Unfortunately there are many developers who don't really know SQL, so every problem is ORM-shaped.

7bit 2y ago
> used to be the norm. People also "used to" invest radioactive water and used radioactive cremes and toothpastes for health benefits in the 20's and 30's. So what's your point?
- bdcravens 2y ago
  That any discussion around systems that uses some arbitrary size of tables/rows/etc is empirically disproven. Moreover, any exaggerated example of a bygone time is unrelated, as many SQL-driven systems still exist today. I work on one such system which is much larger than the example given, and not long ago, I increased performance of some ActiveRecord queries 1000x by simply rewriting them in SQL. (No hate against ActiveRecord, I use it regularly. It just takes a lot of discipline once you hit queries of a certain complexity.)
  - movedx 2y ago
    > ... and not long ago, I increased performance of some ActiveRecord queries 1000x by simply rewriting them in SQL. But it was ActiveRecord that got you there in the first place, as a business, and enabled you to even build anything quickly enough to meet market demand and therefore make money. Moving to a few raw SQL statements today to improve performance is called optimising and everyone in every industry does that... _after the fact_. No one should be (pre-)optimising from day 1. That's a good place to start with an ORM. Our industry is about balancing engineering knowledge with business knowledge and market forces: we have to accept that we can't write perfect code today otherwise you won't have a job tomorrow. You have to get up and running now and optimise later, which might look like replacing some parts of an ORM's job with an optimised SQL statement. (And again: no ORM is stopping you from running raw SQL. You can have both. It's foolish to throw out an entire ORM and everything it gives you because, "Remember when I optimised that one statement that one time?")