4 ms·

Feels like everyone has to go on the journey. ORMs are bad - I’ll just use SQL. Hmm - I need to map these results onto objects I can use. Hmm - wouldn’t it b

Feels like everyone has to go on the journey. ORMs are bad - I’ll just use SQL. Hmm - I need to map these results onto objects I can use. Hmm - wouldn’t it be great if the object tracked changes and could save itself. I need related/child objects - wouldn’t it be great if I could auto fetch them. …

inigyou 3mo ago
ultimately, there is no silver shortcut - you just have to write the damn code
- bartread 3mo ago
  Yeah, exactly. I think the best approach is always to know SQL and know the ORM. Most of the time you’ll be able to simply use the ORM, but every so often you’ll inevitably come up against a situation where a custom query gets the job done better, and you’ll still get the benefits of deserialising to objects that the ORM offers.
  - simondotau 3mo ago
    As long as you restrict yourself to an ORM-compatible schema, you are restricting the power of SQL available to you. Learning SQL properly means learning to model your data correctly, and this usually makes ORMs a non-starter. Without an ORM you have to write a bit more boilerplate code to interact with the database. But by taking advantage of the power of your database engine, you could potentially avoid writing huge amounts of data manipulation logic. In my experience, an ORM is more of a code amplifier than a code simplifier.
    - bartread 3mo ago
      All of this depends on the problems you’re solving though. There is no one size fits all approach to database development.
      - simondotau 3mo ago
        Generally speaking if an ORM is a good fit, the thing you're doing probably isn't database development, it's application state management disguised as database development.
    - win311fwg 3mo ago
      ORM is compatible with any schema, but perhaps you are thinking of something like the active record pattern?
  - datsci_est_2015 3mo ago
    > you’ll inevitably come up against a situation where a custom query gets the job done better In my experience, these are typically best turned into views (or materialized views), because they represent some fundamental relationship or property within the data that’s useful to be able to quickly reference or query directly against. KPI aggregates, for example.
simondotau 3mo ago
I think that journey only feels inevitable if you start from the assumption that the application object model is the centre of the system. An alternative journey: Hmm – I should model the data according to the domain, not according to the shape my application objects happen to want. Hmm – maybe “related objects” are not things to auto-fetch, but relationships the database engine is already built to handle. Hmm – now that my schema matches my domain, complex problems can be solved with a few lines of SQL, saving me hundreds of lines of application code. Hmm – in fact, now I realise that many important operations can be performed without round-tripping the data through application code at all, saving me thousands of lines of application code.
- watwut 3mo ago
  None of your points remove the need to map db values to objects and to fetch related objects.
  - simondotau 3mo ago
    If you just want to store and retrieve objects, and then store and retrieve "related" objects, what you want is an object store, not a relational database. You can use an ORM to shoehorn it into a relational database engine, but don't fool yourself into thinking that's the same thing as using a relational database engine properly. Obsessively cramming tabular data into objects is often unnecessary, and it bloats the code downstream of the database query. It then encourages the bad habit of performing data manipulation in code rather than directly in the database. "Fetch related objects" is a code smell. If any related data was needed, your original query should have already fetched it.
    - Izkata 3mo ago
      Problem is that doesn't work nicely in a one-to-many or many-to-many relationship - fetching it in the original query means deduplicating in the application code, or not fetching it and getting related rows afterwards. And that's one of the things ORMs are really good at.
thr0w 3mo ago
> Hmm - I need to map these results onto objects I can use. What sql client is going to hand you raw text? > Hmm - wouldn’t it be great if the object tracked changes and could save itself. Lost me there.
- bogdan 3mo ago
  Entities
- Izkata 3mo ago
  > > Hmm - I need to map these results onto objects I can use. > What sql client is going to hand you raw text? In python's standard database interface you access columns by index, not name. I figured that's what they're referring to.
atomicnumber3 3mo ago
It's not one or the other, it's both. Sqlalchemy ORM is my favorite, followed sqlc (golang), because they are both there for you for your highs (select * from table order by created at) and your lows ([an inner join followed by 4 left joins with an aggregation function])
gigatexal 3mo ago
With a few extra lines and mapping objects to classes this can be done. To have all this ease of use you give up so much in performance. Most apps and companies never get to the point where performance matters that’s why we have ORMs.
- mekoka 3mo ago
  > you give up so much in performance. Not really. ORMs (memory) and databases (disk) are distant by multiple orders of magnitude performance wise. Skipping the ORM to shave off some cycles is akin to haggling over a few pennies on your thousand dollars bill.
  - gigatexal 3mo ago
    SQLAlchemy vs hand rolled SQL and mapping the results it’s not even close. The overhead if you need it cuz you’re sub scale so be it.
dominotw 3mo ago
its a good abstraction if you build it yourself.
parthdesai 3mo ago
If you're working at any decent scale, the journey is the opposite IMO.
mekoka 3mo ago
You stopped right before the best part. When they decide to create their own ill designed, badly tested, and undocumented mapper.
Philip-J-Fry 3mo ago
I've went on a similar journey and did just end up going to back SQL. Mapping database rows to domain objects really isn't that painful and you only do it once. So not a big deal. LLMs actually make this a non-issue now. I actually realised I like the separation of domain objects and the data layer. It just makes things easier to think about for me. And it means my data layer is completely abstracted from the domain. Makes it easier to implement different storage/caching strategies. And I also realised, if your hot path is needing to get related/child objects then you should probably just write an optimised query as a prepared statement or a stored procedure. It's rare that you actually have that many different ways you want to access the data. They are really useful for speed of development though if something is completely greenfield and you don't know the full picture of how data is going to be accessed.

inigyou 3mo ago

ultimately, there is no silver shortcut - you just have to write the damn code

bartread 3mo ago
Yeah, exactly. I think the best approach is always to know SQL and know the ORM. Most of the time you’ll be able to simply use the ORM, but every so often you’ll inevitably come up against a situation where a custom query gets the job done better, and you’ll still get the benefits of deserialising to objects that the ORM offers.
- simondotau 3mo ago
  As long as you restrict yourself to an ORM-compatible schema, you are restricting the power of SQL available to you. Learning SQL properly means learning to model your data correctly, and this usually makes ORMs a non-starter. Without an ORM you have to write a bit more boilerplate code to interact with the database. But by taking advantage of the power of your database engine, you could potentially avoid writing huge amounts of data manipulation logic. In my experience, an ORM is more of a code amplifier than a code simplifier.
  - bartread 3mo ago
    All of this depends on the problems you’re solving though. There is no one size fits all approach to database development.
    - simondotau 3mo ago
      Generally speaking if an ORM is a good fit, the thing you're doing probably isn't database development, it's application state management disguised as database development.
  - win311fwg 3mo ago
    ORM is compatible with any schema, but perhaps you are thinking of something like the active record pattern?
- datsci_est_2015 3mo ago
  > you’ll inevitably come up against a situation where a custom query gets the job done better In my experience, these are typically best turned into views (or materialized views), because they represent some fundamental relationship or property within the data that’s useful to be able to quickly reference or query directly against. KPI aggregates, for example.

simondotau 3mo ago

I think that journey only feels inevitable if you start from the assumption that the application object model is the centre of the system. An alternative journey: Hmm – I should model the data according to the domain, not according to the shape my application objects happen to want. Hmm – maybe “related objects” are not things to auto-fetch, but relationships the database engine is already built to handle. Hmm – now that my schema matches my domain, complex problems can be solved with a few lines of SQL, saving me hundreds of lines of application code. Hmm – in fact, now I realise that many important operations can be performed without round-tripping the data through application code at all, saving me thousands of lines of application code.

watwut 3mo ago
None of your points remove the need to map db values to objects and to fetch related objects.
- simondotau 3mo ago
  If you just want to store and retrieve objects, and then store and retrieve "related" objects, what you want is an object store, not a relational database. You can use an ORM to shoehorn it into a relational database engine, but don't fool yourself into thinking that's the same thing as using a relational database engine properly. Obsessively cramming tabular data into objects is often unnecessary, and it bloats the code downstream of the database query. It then encourages the bad habit of performing data manipulation in code rather than directly in the database. "Fetch related objects" is a code smell. If any related data was needed, your original query should have already fetched it.
  - Izkata 3mo ago
    Problem is that doesn't work nicely in a one-to-many or many-to-many relationship - fetching it in the original query means deduplicating in the application code, or not fetching it and getting related rows afterwards. And that's one of the things ORMs are really good at.

thr0w 3mo ago

> Hmm - I need to map these results onto objects I can use. What sql client is going to hand you raw text? > Hmm - wouldn’t it be great if the object tracked changes and could save itself. Lost me there.

bogdan 3mo ago
Entities
Izkata 3mo ago
> > Hmm - I need to map these results onto objects I can use. > What sql client is going to hand you raw text? In python's standard database interface you access columns by index, not name. I figured that's what they're referring to.

atomicnumber3 3mo ago

It's not one or the other, it's both. Sqlalchemy ORM is my favorite, followed sqlc (golang), because they are both there for you for your highs (select * from table order by created at) and your lows ([an inner join followed by 4 left joins with an aggregation function])

gigatexal 3mo ago

With a few extra lines and mapping objects to classes this can be done. To have all this ease of use you give up so much in performance. Most apps and companies never get to the point where performance matters that’s why we have ORMs.

mekoka 3mo ago
> you give up so much in performance. Not really. ORMs (memory) and databases (disk) are distant by multiple orders of magnitude performance wise. Skipping the ORM to shave off some cycles is akin to haggling over a few pennies on your thousand dollars bill.
- gigatexal 3mo ago
  SQLAlchemy vs hand rolled SQL and mapping the results it’s not even close. The overhead if you need it cuz you’re sub scale so be it.

dominotw 3mo ago

its a good abstraction if you build it yourself.

parthdesai 3mo ago

If you're working at any decent scale, the journey is the opposite IMO.

mekoka 3mo ago

You stopped right before the best part. When they decide to create their own ill designed, badly tested, and undocumented mapper.

Philip-J-Fry 3mo ago

I've went on a similar journey and did just end up going to back SQL. Mapping database rows to domain objects really isn't that painful and you only do it once. So not a big deal. LLMs actually make this a non-issue now. I actually realised I like the separation of domain objects and the data layer. It just makes things easier to think about for me. And it means my data layer is completely abstracted from the domain. Makes it easier to implement different storage/caching strategies. And I also realised, if your hot path is needing to get related/child objects then you should probably just write an optimised query as a prepared statement or a stored procedure. It's rare that you actually have that many different ways you want to access the data. They are really useful for speed of development though if something is completely greenfield and you don't know the full picture of how data is going to be accessed.