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

What ORMs have taught me: just learn SQL (2014)

woz.posthaven.com

Post reply on HN
Print view View original

301–310 of 360 posts

Re: What ORMs have taught me: just learn SQL (2014)

#301

Post by hodgesrm »

craigvn wrote: post #28
blaisio wrote: post #14

I think saying "Just use SQL" is probably a bad idea. You'll most likely end up implementing an ORM anyway, or you will end up with your model code mixed up everywhere with your views. I do think a lot of people use ORMs as a crutch, which sucks. Also, ORMs often provide too much abstraction, forcing people who actually know SQL to relearn how to do everything the way the ORM happens to like it. I should not have to…

> You'll most likely end up implementing an ORM anyway, This is a really good point. Many people start with the "no ORM" philosophy, realize their application needs some way to map the SQL to the code, time passes..., they have implemented their own half-baked ORM.

A more positive spin is that you'll have an "ORM" that's exactly adapted to your application. Many apps (1) don't need to work with multiple DBMS types and (2) don't use even close to the full panoply of SQL features.

In a language like Java that has a generic DBMS API you can get along just fine with a few classes that handle CRUD operations and transaction management. Somebody familiar with JDBC and SQL can write the bridge classes in about a day, while keeping the overall application vastly simpler.

Either way somebody needs to make an informed choice about ORM vs. direct SQL. It seems as some people get in trouble because they skip that part of the design process.

Re: What ORMs have taught me: just learn SQL (2014)

#302

Post by keredson »

Regarding the article's point about managing your schema, there are only two real options IMHO:

1) "The database schema is the official definition."

Programmatically generate what ever ORM objects (at build time) in a 1-to-1 fashion from a schema dump. This is the approach DKOs use: https://github.com/keredson/DKO As long as the code generation step is done as part of the build process, you'll have none of the normal code generation headaches, and your build will fail if you've made a code incompatible schema change.

2) "Your ORM objects are are the official definition."

And generate the schema definition automatically. The common process of this is that most "generate schema" functions are stupidly lazy, and drop the work of calculating the diff from an existing schema on the developer (forcing them to write migrations). This is unacceptable in my eyes, just as it would be if my version control software wanted me to write my own diffs by hand in order to make a commit. I strongly prefer automatically generated diffs, like in https://github.com/keredson/peewee-db-evolve. So you can do non-destructive schema changes. It's a model I've re-implemented for any new ORM I wind up using.

Re: What ORMs have taught me: just learn SQL (2014)

#303

Post by [deleted] »

atsjie wrote: post #83

The most cringeworthy thing I heard about ORM's actually happened two weeks ago when I explained our use of a query builder rather than an ORM. The new senior developer was talking about speed (??? uhm… k...) and the benefit of being able to switch between PostgreSQL and... MongoDB. I just cringed up, didn't know what to say. Using the same domain model in an RDMBS as a Document Store? I really didn't know how to res…

[deleted]

Re: What ORMs have taught me: just learn SQL (2014)

#304

Post by sergiosgc »

scarface74 wrote: post #202
erikpukinskis wrote: post #79

Earlier quoted context omitted.

Ok, let’s break this down: > Good ORMs are there to automate the repetitive tasks of composing largely boilerplate DML statements, facilitating query composition, providing abstraction for database-specific and driver-specific quirks None of that requires an ORM. A simple query builder will suffice and it will be much easier to debug and much less error prone than an ORM. > providing patterns to map object graphs to…

So the easiest way to avoid using an ORM is to create your own ORM? What AI do you need? You map your tables to objects and relationships between objects via FK relationships.

> What AI do you need? You map your tables to objects and relationships between objects via FK relationships.

That is only true in a one-to-many entity relationship (and even so, it is debatable). A one-to-one relationship can be modeled in the two objects, in one of them, or delegated to a third entity. A many-to-many entity relationship can also be handled, in OO, in various different ways. Idem for a ternary relationship or, basically any higher order relation between objects.

This is known, borrowing a term from electrical engineering, as an impedance mismatch between the two models, and it's not an easy problem by any measure.

Re: What ORMs have taught me: just learn SQL (2014)

#305

Post by lmm »

oceanswave wrote: post #240
scarface74 wrote: post #204

Earlier quoted context omitted.

Why is it an either or? EF can do that but then you loose the benefits of a type safe language.

If type safety is so great, why isn’t sql statically type checked

Because SQL predates a lot of modern techniques. A ground-up replacement written today probably would be statically type checked.

Re: What ORMs have taught me: just learn SQL (2014)

#306

Post by cwbrandsma »

keredson wrote: post #302

Regarding the article's point about managing your schema, there are only two real options IMHO: 1) "The database schema is the official definition." Programmatically generate what ever ORM objects (at build time) in a 1-to-1 fashion from a schema dump. This is the approach DKOs use: https://github.com/keredson/DKO As long as the code generation step is done as part of the build process, you'll have none of the normal…

There is a 3rd. You define your own data structure (in JSON as an example), and generate everything else off of that.

I've done this a couple times (as well as the two items you listed), and this has worked the best for me.

Re: What ORMs have taught me: just learn SQL (2014)

#307

Post by lmm »

zimpenfish wrote: post #188
petre wrote: post #176

Earlier quoted context omitted.

A query builder aids you at constructing queries, while an ORM builds queries for you, runs them and maps the output to objects. It's more sophisticated than a query builder.

> A query builder aids you at constructing queries ... that you understand and can be sure are sensible. > while an ORM builds queries for you ... that you have to hope are sensible. That's one of the biggest flaws of the ORM for me - you have limited visibility of what it's doing to your DB.

Isn't that the same reasoning as "I don't like using high-level languages because they limit my visibility of what's running on my processor"? Do you write all your code in assembly?

Re: What ORMs have taught me: just learn SQL (2014)

#308

Post by lmm »

3riverdev wrote: post #73

(Bias: I'm one of the Hibernate ORM committers.) Hibernate (and presumably any ORM) was never intended to be a complete abstraction of anything-SQL. Like others have mentioned here, an understanding of SQL must be had before using an ORM. The ORM is one piece to the puzzle, not a shield to prevent you from having to touch SQL. One pattern I typically use is a take on CQRS: Hibernate for writing/updating/fetching/dele…

> Hibernate (and presumably any ORM) was never intended to be a complete abstraction of anything-SQL. Like others have mentioned here, an understanding of SQL must be had before using an ORM.

Disagree. You need to understand the relational model, but you don't need to understand SQL-the-language. I've written plenty of successful systems using hibernate without needing to touch SQL, and am much happier for it.

Re: What ORMs have taught me: just learn SQL (2014)

#309

Post by _ea1k »

zzzeek wrote: post #43

> If you're using an RDBMS, bite the bullet and learn SQL. If this person spent all that time using Hibernate and then SQLAlchemy, and all that time did not know SQL, then their suffering and bad experiences make complete sense. You absolutely need to know SQL if you're going to use an ORM effectively. Good ORMs are there to automate the repetitive tasks of composing largely boilerplate DML statements, facilitating q…

Exactly. It is amazing how many bad ORMs that I've seen in systems from people who "just used SQL" instead of an ORM.

I've found that the people who know SQL pretty well can actually do good work with or without an ORM. But the maintenance is a lot easier when the abstractions are consistent with the abstractions that are used by a popular ORM.

Re: What ORMs have taught me: just learn SQL (2014)

#310

Post by Mouse47 »

mattmanser wrote: post #269
Mouse47 wrote: post #263

Earlier quoted context omitted.

>Lazy Loading can also screw you, but again, it's just wonderful when you use it right. That's funny - the first thing I do when starting a new project is turn off lazy loading globally. I find it hides poorly-performing code until it's causing problems; the equivalent code without lazy loading usually just throws an exception. Granted I've only been in the industry for 3 years, so /shrug Also, question: you mention…

We have far more problems with too many .Includes causing terribly performing queries with bad JOINs than lazy loading problems. Granted this code base is in a bit of a state and we have a complex order structure that can go like 10 layers deep, and ideally we're looking to go even deeper with complex pricing. You do an include with all of that and you're going to get a terrible query. It's generally very cheap to do…

>We have far more problems with too many .Includes causing terribly performing queries with bad JOINs than lazy loading problems. Granted this code base is in a bit of a state and we have a complex order structure that can go like 10 layers deep, and ideally we're looking to go even deeper with complex pricing. You do an include with all of that and you're going to get a terrible query.

Wait - are you "Include"ing things you don't need? If not...assuming a sane query plan, shouldn't the single query (e.g. "Include" version) outperform the deconstructed series-of-queries that brings back the same data?

E.g., Included:

    context.Orders.Where(x=>x.OrderId = 5).Include(x=>x.OrderItems)
which translates roughly to

    select * from Orders o join OrderItems i on o.OrderId = i.OrderId
    where o.OrderId = 5
vs.

    var order = context.Orders.Where(x=>x.OrderId = 5);  
    var items = order.OrderItems;
which translates roughly to

    select * from Orders o where o.OrderId = 5;
    select * from OrderItems where OrderId = 5;
As far as I understand the former will outperform the latter, even if you don't take into account the additional connection overhead. If the second version was faster...wouldn't SQL just compile down to a series-of-queries automatically?
Post reply on HN