Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Don't use your ORM entities for everything – embrace the SQL

blackparrotlabs.io

Post reply on HN
Print view View original

21–30 of 48 posts

Re: Don't use your ORM entities for everything – embrace the SQL

#21

Post by mixmastamyk »

jillesvangurp wrote: post #11

My simple rule for databases is that if you are not actively querying on it, it doesn't need to be a separate table or column. Use the YAGNI rule here and you'll be better off. The classic example here is persons that have addresses and phone numbers. Most applications have no requirements to query on most of that: street name, postal code, phone number, etc. Less tables and columns mean simpler joins (or better, no…

Maybe, but not sure you’re getting much from that trade. If you’re not querying often, then performance shouldn’t be an issue. Modern hardware/dbms can definitely handle a couple of joins without blinking. So you’ve mostly lost flexibility.

Also having to redo later will wipe out the time savings of many, many simplifications. Not to mention doc and teaching reqs you’ve added to new devs.

Re: Don't use your ORM entities for everything – embrace the SQL

#22

Post by eddd-ddde »

ta2234234242 wrote: post #20

In the 1980's we had C/C++/Ada/Pascal/Fortran/Assembly/Lisp. Since the 1980's the languages have exploded. But we still only have one language for querying a database. And a kinda not very good one at that. Why is that? https://www.holistics.io/blog/quel-vs-sql/ One could argue that ORM's are the alternative language people are looking for.

Except ORMs are just worse? Unless you mean they are the symptom of longing for other languages where there are none.

I would also love to see a new language for writing declarative queries. I also feel people don't like sql not for the language itself, but the lack of tooling.

Re: Don't use your ORM entities for everything – embrace the SQL

#23

Post by eddd-ddde »

mixmastamyk wrote: post #21
jillesvangurp wrote: post #11

My simple rule for databases is that if you are not actively querying on it, it doesn't need to be a separate table or column. Use the YAGNI rule here and you'll be better off. The classic example here is persons that have addresses and phone numbers. Most applications have no requirements to query on most of that: street name, postal code, phone number, etc. Less tables and columns mean simpler joins (or better, no…

Maybe, but not sure you’re getting much from that trade. If you’re not querying often, then performance shouldn’t be an issue. Modern hardware/dbms can definitely handle a couple of joins without blinking. So you’ve mostly lost flexibility. Also having to redo later will wipe out the time savings of many, many simplifications. Not to mention doc and teaching reqs you’ve added to new devs.

I think they mean that adding a bunch of uncommon columns to your user table is just gonna decrease performance of common queries. In those cases I like offloading uncommon columns to separate tables that are joined only when needed.

Re: Don't use your ORM entities for everything – embrace the SQL

#24

Post by ta2234234242 »

eddd-ddde wrote: post #22
ta2234234242 wrote: post #20

In the 1980's we had C/C++/Ada/Pascal/Fortran/Assembly/Lisp. Since the 1980's the languages have exploded. But we still only have one language for querying a database. And a kinda not very good one at that. Why is that? https://www.holistics.io/blog/quel-vs-sql/ One could argue that ORM's are the alternative language people are looking for.

Except ORMs are just worse? Unless you mean they are the symptom of longing for other languages where there are none. I would also love to see a new language for writing declarative queries. I also feel people don't like sql not for the language itself, but the lack of tooling.

Are they worse? The popularity of them has exploded like programming languages from the 80's to now.

One might say that if SQL was so great there would be no ORMs -- because there would be no need.

The ORMs, if anything, are a symptom of the larger problem that is SQL.

Re: Don't use your ORM entities for everything – embrace the SQL

#25

Post by lcnPylGDnU4H9OF »

ta2234234242 wrote: post #24
eddd-ddde wrote: post #22

Earlier quoted context omitted.

Except ORMs are just worse? Unless you mean they are the symptom of longing for other languages where there are none. I would also love to see a new language for writing declarative queries. I also feel people don't like sql not for the language itself, but the lack of tooling.

Are they worse? The popularity of them has exploded like programming languages from the 80's to now. One might say that if SQL was so great there would be no ORMs -- because there would be no need. The ORMs, if anything, are a symptom of the larger problem that is SQL.

> One might say that if SQL was so great there would be no ORMs

It's not like the ORMs exist without SQL. Their primary purpose is to provide a consistent interface between different flavors of SQL so the application developer doesn't need to care what database they're using.

For example, delimiting object names in Mysql uses ` and in Postgres uses ". The ORM will ostensibly have different adapters which take care of these differences without changing the application code.

Re: Don't use your ORM entities for everything – embrace the SQL

#27

Post by moribvndvs »

I always admired micro ORMs like Dapper. The hard (or at least time wasting or error prone) tasks are:

- mapping results to app entities or projections

- input sanitization

- dynamic query building

- connection pooling and transaction management

- supporting multiple SQL dialects with one code base

After many years of experience with Hibernate, NHibernate, Entity Framework, and a plethora of other full ORMs, I absolutely do not want lazy/eager loading, opaque query DSLs and criteria APIs, and dealing with leaky abstractions for queryables (looking at you, LINQ and JPA), and I’m pretty wary anymore over cascading persistence. These things all look great in tutorials and are nearly magical in your PoC or simple projects, but in my experience, performance problems, unexpected and sometimes hard to diagnose bugs, and lots of ugly yak shaving are inevitable for moderate complexity apps over time.

Of all of them, I enjoyed JOOQ’s SQL builder with or without codegen the most if I need to support multiple dialects or complex dynamic queries.

Speaking of JOOQ, anyone have recommendations for something similar on nodejs? I haven’t been particularly excited by prisma, typeorm, or sequelize.

Re: Don't use your ORM entities for everything – embrace the SQL

#28

Post by eddd-ddde »

lcnPylGDnU4H9OF wrote: post #25
ta2234234242 wrote: post #24

Earlier quoted context omitted.

Are they worse? The popularity of them has exploded like programming languages from the 80's to now. One might say that if SQL was so great there would be no ORMs -- because there would be no need. The ORMs, if anything, are a symptom of the larger problem that is SQL.

> One might say that if SQL was so great there would be no ORMs It's not like the ORMs exist without SQL. Their primary purpose is to provide a consistent interface between different flavors of SQL so the application developer doesn't need to care what database they're using. For example, delimiting object names in Mysql uses ` and in Postgres uses ". The ORM will ostensibly have different adapters which take care of…

Not even that works painlessly. You commonly run into incompatibilities so an ORM won't necessarily make your code database agnostic.

Re: Don't use your ORM entities for everything – embrace the SQL

#29

Post by lcnPylGDnU4H9OF »

eddd-ddde wrote: post #28
lcnPylGDnU4H9OF wrote: post #25

Earlier quoted context omitted.

> One might say that if SQL was so great there would be no ORMs It's not like the ORMs exist without SQL. Their primary purpose is to provide a consistent interface between different flavors of SQL so the application developer doesn't need to care what database they're using. For example, delimiting object names in Mysql uses ` and in Postgres uses ". The ORM will ostensibly have different adapters which take care of…

Not even that works painlessly. You commonly run into incompatibilities so an ORM won't necessarily make your code database agnostic.

It lessens the pain, still. It's rare that you'd use common ORM methods and run into something that's supported for one RDBMS and not another. Doing stuff that's less common and, of course, writing raw SQL will still be pain points but they'll be fewer than if one was doing everything in raw SQL.

Re: Don't use your ORM entities for everything – embrace the SQL

#30

Post by mixedCase »

dkersten wrote: post #18
lcnPylGDnU4H9OF wrote: post #16

Comments here make me wonder if I've just been spoiled by ActiveRecord. Not that I use it for all queries but 1) it's rare that I have to resort to raw SQL and 2) it kindly gets out of the way when I do.

> resort to raw SQL I'm the opposite, I would rather write SQL than "resorting to" ORM queries, which is why my favourite libraries are aiosql[1] in Python, Hugsql[2] in Clojure and similar: write the queries as SQL in .sql files, which then get exposed as functions to your code. [1] https://nackjicholson.github.io/aiosql/ [2] https://www.hugsql.org/

Those tend to be great until two words come into play:

Dynamic SQL.

Post reply on HN