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)

wozniak.ca

Post reply on HN
Print view View original

441–450 of 654 posts

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

#441

Post by wvenable »

Animats wrote: post #428

Rather than having a full ORM, a SQL system that didn't put in variables by embedding strings would be useful. You can almost do this. SQL has variables, but they're more persistent than needed for this. SELECT a,b,c FROM tab WHERE a=@mysearchkey; An API should look something like result = sql->command("SELECT a,b,c FROM tab WHERE a=@mysearchkey;", {"@mysearchkey": val }) and the result should be a key/value form. In…

Almost every single native RDBMS API provides parameterized queries / prepared statements where the parameters are sent separately from the query text. Here's one from MySQL:

https://dev.mysql.com/doc/refman/5.7/en/mysql-stmt-bind-para...

String escapes should have been dead a few decades ago -- I don't think any modern platform requires it; they all support parameterized queries natively.

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

#442

Post by JesseAldridge »

kls wrote: post #375
JesseAldridge wrote: post #227

This was my position for a while. ORMs introduce a layer of magic which obscures what's actually going on under the hood. I decided I would just make raw SQL queries and handle mapping data explicitly. I quickly ended up with a lot of duplicated code. So then I thought, "Well ok, I should add a bit of abstraction on top of this..." I started coding some simple functions to help map the tabular data to objects. One th…

Out of curiosity what platform and tech where you using? I am making the assumption of a predominately OO one based on the virtues of ORM. I have always found that when I try to solution back end or middleware based platforms with OO dominate languages (read Java, C#, et. al.) that there quickly becomes an impedance mismatch and any communication with the database becomes a monster of mapping OO philosophy to relatio…

It was a Flask app using SqlAlchemy (so Python). I'm not sure functional programming would have changed the situation much. I imagine there would still be repeated patterns involving reading and writing to the database in slightly different ways, and it would still make sense to use some sort of library. But I haven't used functional languages much, so I can't say for sure either way.

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

#443

Post by JohnBooty »

ct520 wrote: post #354
scarface74 wrote: post #338

Earlier quoted context omitted.

One of the most popular Micro ORMs for C# is Dapper which is used by Stack Overflow. There is no real abstraction. You write standard SQL and it maps your recordset to an object. You know exactly what code is running. There are extensions that will take a POCO object and create an insert statement and I believe updates, but where ORMs usually get obtuse and do magic are Selects. It’s hard to generate a suboptimal Ins…

So.. pattern I see emerging. Use orm for the common stuff and execute sql for complicated queries (like reports)

Ruby on Rails’ ActiveRecord, for all its heft, is excellent at this. You can use raw SQL any time you like. It was an explicit design goal from day 1.

There are times I dislike things about it, and it can be quite heavy, but it’s very easy to mix and match ActiveRecord ORM code and raw SQL even within a single model class.

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

#444

Post by minitech »

i_am_nomad wrote: post #370
gernak wrote: post #349

Earlier quoted context omitted.

>You can (and should) use them for simple queries. This is not a very compelling argument to use ORMs. It is saying "it makes easy things easier". This doesn't really buy you much value. The simple things are already simple. Bringing in a very large, complicated external dependency to make simple things simpler, is not a good idea. >If you're loading data into objects then you're just creating your own personal ORM a…

This kind of doctrinaire thinking, this sort of broad and bold declaration, is the stuff of high-traffic blog posts but not good advice for real world developers. Django, just as an example, does a magnificent job with its built-in ORM. Millions of developers use it, and they are not all fools. A fool is someone who would set out to build a simple-to-intermediate CRUD web app by writing SQL.

> This kind of doctrinaire thinking, this sort of broad and bold declaration, is the stuff of high-traffic blog posts but not good advice for real world developers.

> A fool is someone who would set out to build a simple-to-intermediate CRUD web app by writing SQL.

\u{1f644}

SQL works great for simple-to-intermediate CRUD web apps too.

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

#445

Post by wvenable »

_bxg1 wrote: post #166

In my opinion this whole problem is one of the strongest condemnations of OOP. Even if OOP really were a great way to model your data, to express relationships and property types and such (setting aside all the questionability of that claim), it's all just going to end up in a database anyway . Unless you're using a denormalized database like Mongo, or the bulk of your application state is non-persistent, your perfec…

> Even if OOP really were a great way to model your data, to express relationships and property types and such, it's all just going to end up in a database anyway.

Of course. But when you operate on it. When you end user is typing into a screen. When you're validating the contents. You're doing in the application, not in the database. RAM is where the data lives when it's being acted on.

It's like saying your Word processor should have no internal data structures different from how the file stored on disk.

OOP is a great way to model data sotred in RAM and operated on. And an ORM is a great tool for persisting that structure to disk as needed.

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

#446

Post by quickthrower2 »

Roboprog wrote: post #42
dang wrote: post #11

Thread from 2017: https://news.ycombinator.com/item?id=15949144 2016: https://news.ycombinator.com/item?id=11981045 Discussed at the time: https://news.ycombinator.com/item?id=8133835

I know, this is a recurring discussion. Both sides continue to distrust the other, though.

No sides as afar as I am concerned. I just pick the best tool for the job. I love ORMs. I love plain SQL, be that via an ORM "jail break" or a lower layer.

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

#447

Post by wvenable »

gernak wrote: post #349
wvenable wrote: post #30

Earlier quoted context omitted.

You don't use ORMs for gnarly queries -- that's not what they are for! They are for making manipulating the entities easier -- reading the data out of the database in a way that makes easy to modify. You can (and should) use them for simple queries. You have a list of entities you want to query and filter, that's going to be fine. Joins are fine. But if you're doing some complex analysis, an ORM is the wrong tool. Th…

>You can (and should) use them for simple queries. This is not a very compelling argument to use ORMs. It is saying "it makes easy things easier". This doesn't really buy you much value. The simple things are already simple. Bringing in a very large, complicated external dependency to make simple things simpler, is not a good idea. >If you're loading data into objects then you're just creating your own personal ORM a…

> It is saying "it makes easy things easier".

No it makes simple things easy. Simple things in raw SQL are bloody complicated. Even just getting data, manipulating it, and saving it is at least twice as difficult in maintainability and lines of code than using an ORM.

> An ORM converts one paradigm into a completely different paradigm, which is why it fails and is a terrible idea.

I'm not sure where people get the idea that ORMs fail at their job. They really don't. They do it very well and we're all quite happy.

All the anti-ORM arguments here about how ORMs fail at completely different jobs other than mapping objects to RDBMS operations. Well duh. Nobody complains about cars that can't fly and planes that can't fit on the highway but when ORM can't cook bacon it doesn't fit the paradigm.

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

#448

Post by didibus »

JesseAldridge wrote: post #442
kls wrote: post #375

Earlier quoted context omitted.

Out of curiosity what platform and tech where you using? I am making the assumption of a predominately OO one based on the virtues of ORM. I have always found that when I try to solution back end or middleware based platforms with OO dominate languages (read Java, C#, et. al.) that there quickly becomes an impedance mismatch and any communication with the database becomes a monster of mapping OO philosophy to relatio…

It was a Flask app using SqlAlchemy (so Python). I'm not sure functional programming would have changed the situation much. I imagine there would still be repeated patterns involving reading and writing to the database in slightly different ways, and it would still make sense to use some sort of library. But I haven't used functional languages much, so I can't say for sure either way.

Well, the difference is that in a data oriented language, you do not need to map Objects to relations. You can get the data back in the relational format and use it as is in your app. So you don't need an ORM. You might still have a library to help you build dynamic SQL queries, but no object-relational mapping needed.

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

#449

Post by arendtio »

JesseAldridge wrote: post #227

This was my position for a while. ORMs introduce a layer of magic which obscures what's actually going on under the hood. I decided I would just make raw SQL queries and handle mapping data explicitly. I quickly ended up with a lot of duplicated code. So then I thought, "Well ok, I should add a bit of abstraction on top of this..." I started coding some simple functions to help map the tabular data to objects. One th…

ORMs seem to be a typical example of over-engineering. Often you don't need all that complexity they come with and when you do, you are probably better of understanding exactly what you are doing.

So maybe building a minimal API, wrapping your SQL queries isn't such a bad idea after all.

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

#450

Post by jasonkester »

I don't know. I've been building database backed stuff for 25 odd years now, and I've never experienced this Object/Relational Impedance Mismatch that everybody talks about in any of my designs. I sometimes wonder if it's just the approach I take that has ended up dodging that bullet somehow.

My initial design is always done in the database. Whether it's a little feature or a green field new project on a blank sheet of paper, that sheet of paper is the Schema Designer of my db (or a schema.sql if I'm in postge/mysql land).

Once the schema is nailed down, the object structure flows out easily. You can follow foreign keys, many-to-many tables and non-identity primary keys to figure out your children, relations, and inheritance. It's so well defined that for my own stuff I just point my code generator at it to get a pile of base classes and stored procedures for all the CRUD. (And re-run it at build time to ensure that everything always matches up.)

So when it comes to pulling down records, modifying them, saving them, grabbing sets of them to spin through, etc. There's never any mismatch because what you get from the db will naturally look just like what you need. Because you designed it to be that way.

I may hazard a guess as to why so many people do run into issues, and it's because I notice that nearly every ORM I've seen in the wild expects you to define your schema someplace other than the database. It'll have some wacky XML config file that you're supposed to keep up to date, with even wackier "migrations" for when that changes. And it'll then either build your db schema for you or expect you to have something in place that matches what it wants.

But that's silly.

There's already a perfectly good place to keep your schema. In the database.

And I guess it follows that if you don't design your system with a sensible relational data model in mind, you might find that your object structure doesn't in fact fit in a database correctly. Could it be that that's what people are describing when they talk about Impedance Mismatch?

Post reply on HN